Une simulation sur bilan fonctionnel

Exercice de simulation de TN sur Excel

Cet exercice a été conçu pour des élèves de terminale STMG. Son objectif est d’évaluer l’impact de la variation du niveau d’activité et des délais de paiement sur la trésorerie nette (TN) grâce à Excel (ou à tout autre tableur). Les simulations seront très simplifiées (pas de TVA…).

Contexte

Un entrepreneur s’est spécialisé dans le négoce de poissons d’aquarium marins. La clientèle est surtout composée d’entreprises qui souhaitent décorer des espaces (hôtels, restaurants…). Son activité augmente régulièrement, mais cette croissance nécessite de financer davantage de stocks et de créances clients. Notre entrepreneur souhaite donc utiliser un tableur afin de mesurer l'impact de différentes hypothèses sur son besoin en fonds de roulement (BFR) et sur sa trésorerie.

On dispose du bilan fonctionnel simplifié suivant, établi au 31 décembre N (en euros).

Si vous débutez sur Excel, vous pouvez vous rendre en page de bordures de cellules pour savoir comment sa mise en forme a été réalisée.

Le chiffre d'affaires annuel (CA) est de 1 200 000 € et les achats de marchandises représentent 840 000 €. Pour simplifier, on considère que l'année comporte 360 jours.

Énoncé

1. Avec le tableur, calculer le fonds de roulement (FR), le besoin en fonds de roulement (BFR) et la trésorerie nette (TN).


2. Établir quelques ratios :
• Poids du BFR dans le chiffre d'affaires : BFR / CA × 100
• Durée moyenne du crédit accordé aux clients : Créances clients / CA × 360
• Durée moyenne de stockage : Stocks / Achats × 360
• Durée moyenne du crédit obtenu des fournisseurs : Dettes fournisseurs / Achats × 360.

Interpréter chacun des quatre résultats obtenus. Que signifie concrètement une durée de crédit client de 30 jours ? Pourquoi un allongement du délai de paiement accordé aux clients augmente-t-il généralement le BFR ? Pourquoi un allongement du délai de paiement accordé par les fournisseurs a-t-il, au contraire, tendance à diminuer le BFR ?


3. Première simulation : une augmentation de l'activité.
   
   Le dirigeant envisage une augmentation de \(20\,\%\) du CA. On suppose que le niveau des stocks, les créances clients et les dettes fournisseurs augmentent également de \(20\,\%.\) Les délais restent inchangés.
   
   Dans Excel, créer une zone « Hypothèses » permettant de modifier le taux de variation de l'activité. Bien sûr, les valeurs du bilan doivent être calculées à partir des cellules d'hypothèses et non saisies directement.
   
   Calculer les nouveaux BFR et TN en supposant le FR constant. Que constate-t-on ? Expliquer pourquoi une augmentation de l'activité peut entraîner une diminution de la trésorerie, même si l'entreprise réalise plus de ventes.


4. Simulation avec nouveaux délais

Modification des formules Excel de façon à envisager d’autres délais de paiement des clients et fournisseurs.


• Créances clients = Chiffre d'affaires × Délai de paiement / 360
• Dettes fournisseurs = Achats × Délai fournisseurs / 360

Dans Excel, créer un petit tableau permettant de comparer les situations suivantes :

Corrigé

1- On peut indiquer à droite du bilan les données supplémentaires de l’énoncé ainsi que les montants demandés. Le FR s’obtient en soustrayant les emplois stables des ressources stables, le BFR = stocks + créances clients – dettes fournisseurs (voir formule dans la capture d’écran ci-dessous) et la TN est égale au FR moins le BFR (le montant se vérifie dans le bilan).

2- Les résultats sont les suivants :

Le poids du BFR dans le CA montre la part de celui-ci qui correspond au besoin de financement lié au cycle d'exploitation. Le fait qu’il s’établisse à \(10\,\%\) n’est pas très parlant en soi car il faudrait soit observer son évolution sur plusieurs années, soit le comparer à la concurrence. Les clients mettent en moyenne 30 jours pour régler leur achat. Plus les clients paient tard, plus l'argent des ventes reste bloqué chez eux, et plus l'entreprise doit trouver de l'argent pour financer son activité : le BFR augmente. Réciproquement, plus l’entreprise tarde à payer ses fournisseurs, plus longtemps elle garde de l’argent pour financer son activité.

3- On peut modéliser l’hypothèse ainsi :

Les emplois et ressources stables ne changent pas. On peut donc écrire, dans les cellules N3 et P3, respectivement =C3 et =E3. Pour le nouveau montant de stock, la formule est =C4*(1+K3), c’est-à-dire que l’on applique un taux de croissance. Même principe pour les créances et dettes d’exploitation, ainsi que le CA. On calcule le total des ressources en additionnant les deux montants qui le composent. Pour le total des emplois, il suffit d’écrire =P7 (on reprend le total des ressources) puis on obtient les disponibilités par différence : =P7-SOMME(N3:N5).

Le BFR s’établit désormais à 144 000 € et la TN à 150 000 (FR inchangé) – 144 000 = 6 000 € (vérifié au bilan ci-dessus).

Constat : la hausse de l’activité entraîne une baisse de la TN ! En effet, elle provoque une augmentation du BFR alors que le FR est resté inchangé. C’est d’ailleurs un phénomène bien connu : les entreprises qui se développent trop vite peuvent se retrouver en cessation de paiement !

4- On peut continuer le tableau de l’énoncé ainsi :

On remplit la première ligne (donc ligne 2 de la feuille de calcul) puis on recopie la formule vers le bas.

Pour le CA, nous avons =$H$6*(1+V2). Comme nous recopions la formule et que le CA de départ ne se trouve que dans la cellule H6, il faut la « figer » dans la formule (avec F4, c’est le plus pratique) puis multiplier ce montant par le taux de croissance, comme nous l’avons déjà fait. Même principe pour le montant des stocks : =$C$4*(1+V2).

Pour les créances des clients, on utilise la formule rappelée dans l’énoncé. Comme on n’utilise que les colonnes précédentes du tableau, aucune cellule n’est figée dans la formule. En l’occurrence : =Y2*W2/360. Pour les dettes fournisseurs c’est différent car on a besoin des achats et le montant figure en H7 (voir corrigé de la question 1). Ce montant est figé et on lui applique éventuellement le pourcentage de hausse d’activité : =$H$7*(1+V2)*X2/360.

Le BFR est très simple à calculer : =Z2+AA2-AB2. La TN soustrait celui-ci du FR, fixe pour tout l’exercice : =$S$2-AC2.

La situation la plus favorable à la trésorerie est celle de l’activité identique mais avec négociation d’un doublement du délai de paiement auprès des fournisseurs. La plus défavorable est celle de l’accroissement d’activité combiné à des délais clients qui s’allongeraient.