GROUPE ISM
ECOLE DE MANAGEMENT
MBA 2 ING. FIN
Mise en œuvre d’une modélisation financière avec Excel
Si nos projections basées sur des éléments chiffrées en suivant des méthodes financières et/ou
statistiques arrivent à apporter un éclairage, même approximatif, aux décideurs, alors elles
permettront de prendre en amont des décisions plus pertinentes ou d’aller au-devant des
problèmes pouvant survenir dans l’exploitation future de l’entreprise.
A partir d’un projet de création d’entreprise, nous établirons plusieurs documents comptables
prévisionnels dans le but d’estimer la rentabilité et de déceler les éventuels problèmes pouvant
survenir lors de la mise en œuvre du projet.
Dans ce projet, nous allons créer un compte de résultat prévisionnel et un plan prévisionnel de
trésorerie dans le cadre d’un business plan. Un business plan est un ensemble de documents
réalisés lors d’une création d’entreprise. Il est notamment utilisé pour convaincre les
différents partenaires financiers sur la faisabilité du projet.
Il comprend traditionnellement trois parties :
- Une partie juridique : désignation de l’activité de l’entreprise, statut juridique,
désignation des associés….
- Une partie commerciale : étude de marché, niveau de la demande, définition du prix
de vente….
Et, bien entendu, une partie financière.
Dans le but d’étudier la viabilité économique du projet, on tentera d’établir pour un à trois
voire cinq exercices un compte résultat prévisionnel et éventuellement un état de la
trésorerie prévisionnel et/ou un bilan prévisionnel
Ci-après le canevas de travail
GUIDE PRATIQUE
Mise en place du compte de résultat prévisionnel (suite et fin)
1) Calcul du coût des consommables glaces et cafés
- Sélectionner la plage de cellules et saisissez = vente_glace*cu_glace
- Nommer la plage de cellules conso_glace
- Sélectionner la plage de cellules et saisissez = vente_cafe*cu_cafe
- Nommer la plage de cellules conso_glace
2) Calcul du coût total d’exploitation
- Pour chaque mois faire la somme de salaires, loyer, électricité et des consommables
glaces et cafés
3) Calcul de l’excédent brut d’exploitation (EBE)
Rappel : l’EBE traduit la performance économique de l’entreprise indépendamment de
son mode de financement
EBE=Chiffe d’affaires-Achats consommés-Consommation en provenance de tiers +
Subventions d’exploitation-Charges de personnel-Impôts et Taxes
Dans le cadre de notre modèle financier, nous allons tout simplement soustraire la coût
total d’exploitation au chiffre d’affaires total.
4) Calcul des dotations aux amortissements du matériel
- Dans la feuille hypothèse nommez respectivement les cellules E25 et E26 par
amo_machine_glace et amo_machine_cafe
- Depuis la plage de cellules sélectionnée, saisissez
amo_machine_glace+amo_machine_cafe
5) Résultat d’exploitation annuel
Pour obtenir le résultat d’exploitation, nous allons déduire las dotations aux amortissements à l’EBE
6) Intérêts sur emprunt
Les intérêts ont été déjà calculé dans la feuille emprunt. Pour les importer, on procédera comme
suit :
- Sélectionner la plage de cellules C21 : N21
- Saisir =Transpose(emprunt !C11 :C130)
- Appuyer sur Ctrl+Shift+Enter
7) Résultat prévisionnel
Ce résultat prévisionnel est obtenu par la différence entre résultat d’exploitation et les intérêts sur
emprunt
Mise en place du plan de trésorerie prévisionnel
Dans la gestion quotidienne, particulièrement dans la cas d’une nouvelle entreprise, une mauvaise
gestion de la trésorerie peut rapidement mener à la faillite.
Il est possible que l’activité soit rentable d’après le compte résultat, mais vous si vous manquez de
« cash » pour payer vos employés et vos fournisseurs à l’instant T, votre entreprise a un sérieux
problème.
Le plan de trésorerie est certainement le document le plus important d’un business plan financier. Il
permettra de déceler en amont les éventuelles faiblesses du niveau de trésorerie qui pourront
survenir lors du lancement de l’activité, tant en termes quantitatif que temporel, et le cas échéant de
trouver une solution avec le banquier et les financeurs.
Un autre élément qui peut survenir, bien moins grave pour la continuité de l’exploitation de
l’entreprise, est qu’au contraire, l’activité génère un surplus de trésorerie. Il est intéressant de
déceler cet éventuel surplus de trésorerie en amont dans le but de réfléchir à des placements
financiers pour dynamiser la trésorerie
Pour modéliser un plan de trésorerie, il faut commencer avec le solde d’ouverture ajouter les
encaissements et déduire les décaissements de la période. Vous obtenez ainsi le solde de clôture qui
deviendra ainsi le solde d’ouverture de la période suivante
Remarque : Le plan de trésorerie ne regroupe que les dépenses décaissées et recettes encaissées : ni
les charges décaissables ni les produits non encaissables
1) Calcul de la trésorerie à l’ouverture
- Marchandises stockées : Dans la feuille hypothèse, il est mentionné que l’entreprise souhaite
se constituer un stock initial de marchandises d’un montant de 5000 euros
Saisir la formule =-hypothese !C42
- Coût de la machine à café
Saisir la formule =-hypothese !C43
- Coût de la machine à glace
Saisir la formule =-hypothese !C44
- Emprunt bancaire
Saisir la formule = hypothese !C36
- Epargne personnelle
Saisir la formule = hypothese !C37
2) Trésorerie nette à l’ouverture
Dans la cellule B41, saisir la formule =somme(B26 :B40) =5000.
La société démarre donc une trésorerie nette de 5000.
Recopier cette formule jusqu’à la cellule N41
3) Calcul de la trésorerie prévisionnelle mensuelle
- Trésorerie mensuelle à l’ouverture
Il s’agit de reporter la trésorerie de la période précédente.
Sélectionner la plage de cellules C26 :N26 et saisir =B41
4) Flux de trésorerie liée à l’exploitation
En partant du résultat prévisionnel, on corrige ce montant de la TVA à décaisser, des
amortissements et des dotations aux amortissements et provisions
- Copier dans la plage de cellules C28 :N28, le résultat prévisionnel
- Copier dans la plage de cellules C31 :N31, l’amortissement du matériel
5) Amortissement de l’emprunt
- Pour reporter les données concernant l’amortissement mensuel de l’emprunt contenues
dans la feuille emprunt, saisir =-TRANSPOSE(emprunt !D11 :D130) et Appuyer sur
Ctrl+Shift+Enter
6) Calcul de la TVA à décaisser
Pour finaliser le plan prévisionnel de trésorerie nous devons connaitre le montant prévisionnel
mensuel de la TVA à décaisser par l’entreprise.
La TVA est un impôt indirect supporté par le consommateur final et collecté par les entreprises qui
la reversent à l’état.
A la fin de chaque mois, l’entreprise calcul la différence entre la TVA collecté 442 (sur ses ventes) et
la TVA déductible 445 (déductible sur autres biens et services et déductibles sur immobilisations)
Si la TVA collectée est supérieure à la TVA déductible, alors la différence est la TVA à décaisser, elle
devra être réglée le mois suivant
Si la TVA collectée est inférieure à la TVA déductible, alors la différence est un crédit TVA : c’est
l’état qui doit de l’argent à l’entreprise. Ce crédit sera déduit lors du calcul de la TVA à décaisser du
mois suivant
Les cellules B45,B46 et B47 ont été respectivement nommées tx_tva_ventes, tx_tva_abs et
tx_tva_immo
- Calcul TVA collectée= chiffre_affaires*tx_tva_ventes
Selctionner la plage de cellules et saisir la formule ci-dessus
Nommer cette plage tva_collectee
- Calcul TVA déductible sur immobilisation= coût immobilisations*taux
Dans la cellule B51, entrer la formule= (hypothese !C43+hypothese !C44)*tx_tva_immo
- Calcul TVA déductible sur achats de biens et services
Ce calcul de TVA s’opère dans ce cas sur l’achat mensuel de consommables pour l’activité
Glaces et Café et sur l’électricité
Sélectionner la plage C52 :N52 et entrer la
formule=(conso_cafe+conso_glace+electricite)*tx_tva_abs
Nommer la plage tva_achat_biens_services
Calcul de la TVA à décaisser mensuelle
Pour une période donnée, si la TVA collectée est supérieure à la TVA déductible, le calcul de la TVA à
décaisser est TVA collectée – TVA déductible
Sinon, la formule doit renvoyer la valeur 0
Conseil : Utiliser la fonction conditionnelle SI
- Sélectionner la plage C53 :N53
- Saisir =SI((tva_collectée-tva_achat_biens_services)>0 ; tva_collectée-
tva_achat_biens_services ;0)
- Nommer cette plage tva_decaiss_m
Calcul du crédit de TVA mensuel
Il s’agit de reporter le montant de la TVA déductible sur immobilisation à l’ouverture calculé dans la
cellule B51.
Dans la cellule B54, saisir la formuler =B51
Pour chacun des mois de l’année :
Si TVA collectée du mois concernée est inférieure à la TVA déductible, le calcul du crédit de TVA est
TVA déductible-TVA collectée.
Dans les autres cas, la formule doit renvoyer la valeur 0
Sélectionner la plage C54 :N54, entrer la formule=SI((tva_collectee-tva_achat_biens_services)<0 ;
tva_achat_biens_services-tva_collectee ;0)
Nommer cette plage credit_tva_m
Situation de TVA : crédit de TVA
Il s’agit de reporter le montant de la TVA déductible sur immobilisation à l’ouverture calculer dans la
cellule B51.
Dans la cellule B56, entrer la formule =B51
Pour chacun des 12 mois de l’année, le calcul suivant va être effectué :
Si le crédit de TVA du mois précédent est supérieur à la TVA à décaisser, alors la crédit TVA restant
est égal à :
Crédit de TVA du mois précédent + le crédit de TVA mensuel-la TVA à décaisser mensuelle
Dans les autres cas, la formule doit renvoyer 0.
Sélectionner la plage C56 :N56 et entrer la formule :
=SI((B56-tva_decaiss_m)>0 ; (credit_tva_m+B56-tva_decaiss_m) ;0)
Situation de TVA : TVA à décaisser
A partir du moment où le crédit de TVA est épuisé, le calcul à effectuer est :
TVA à décaisser mensuelle- (crédit de TVA + situation de TVA)
La situation de TVA correspond au crédit de TVA de la période précédente.
Dans les autres cas, la formule doit renvoyer 0.
Sélectionner la plage C57 :N57 et entrer la formule :
=SI(C56=0 ;tva_decaiss_m-(credit_tva_m+B56) ;0)
Règlement de la TVA à décaisser
Le règlement de la TVA à décaisser s’effectue le mois suivant son calcul. Il n’y aura donc aucun
règlement de TVA à décaisser à l’ouverture ni au mois de janvier.
Le calcul commencera à partir du mois de février sur la base de la TVA à décaisser mensuelle du mois
de janvier
- Sélectionner la plage de cellules D59 :N59 et entrer la formule =C57
Report de la TVA à décaisser dans le plan de trésorerie prévisionnelle
- Sélectionner la plage de cellules C30 :N30 et entrer la formule =-C59
Dernière étape : SYNTHESE
Pour faciliter la lecture, nous allons placer les éléments principaux du modèle à savoir le chiffre
d’affaires prévisionnel, le résultat prévisionnel et la trésorerie prévisionnel la feuille synthèse
- Pour CA glace saisir dans B6 la formule=’cpte de R+tresorerie prev’ !O6
- Pour CA café saisir dans B7 la formule=’cpte de R+tresorerie prev’ !O7
- Faire le total dans B8
- Dans la cellule B15, saisirla formule=’cpte de R+tresorerie prev’ !O22
- Dans la cellule, B26, saisir la formule=’cpte de R+tresorerie prev’ !N41
Travail à faire : 08 pts
Générer les graphiques interactifs suivants
1- Graphique de l’évolution mensuelle du chiffre d’affaires
2- Graphique de l’évolution mensuelle du résultat
3- Graphique de l’évolution mensuelle de la trésorerie
Définir une stratégie sur la trésorerie selon les différents scénarii
