Editions ENI Livres | Vidéos | e-Formations
Modélisation financière avec Excel
Introduction
Si la première partie de cet ouvrage traite d’analyse financière a posteriori, ce chapitre a pour thème la prévision.
Faire une prédiction d’un résultat futur en se basant sur des hypothèses ou des données historiques n’est pas une tâche aisée.
« La prévision est difficile, surtout lorsqu’elle concerne l’avenir » (proverbe danois).
À vrai dire, en matière de prévision, la seule chose qui soit garantie est que nos prévisions soient fausses.
Est-ce que pour autant ce travail est inutile ?
Absolument pas !
Si nos projections basées sur des éléments chiffrés 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 de problèmes pouvant survenir dans l’exploitation future de l’entreprise.
Le premier chapitre de cette partie porte sur la mise en œuvre d’une modélisation financière avec Excel.
À 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.
Le deuxième chapitre de cette partie propose d’aborder différentes méthodes statistiques de prévisions.
Dans ce chapitre, nous tenterons de prévoir le niveau de ventes futur avec différentes méthodes et outils disponibles dans Excel.
Mise en œuvre d’une modélisation financière avec Excel
Dans cette partie, nous allons créer...
Présentation du cas
Monsieur Bob souhaite ouvrir un café/glacier au centre de Strasbourg. Habitant de longue date cette ville alsacienne, il a une bonne idée de l’activité et de la fréquentation notamment touristique que pourrait avoir son établissement.
Un peu inquiet, surtout à l’idée de devoir s’endetter pour mener à bien son projet, il nous sollicite pour l’aider à mesurer la faisabilité économique et financière du futur café, c’est-à-dire pour répondre aux questions suivantes :
-
Quel serait mon résultat probable ?
-
Est-ce que je risque de rencontrer des problèmes de trésorerie ?
Pour réaliser son projet, M. Bob doit acquérir deux machines : une machine à glace et une machine à café.
Il aura un serveur salarié.
Il pense contracter un emprunt auprès de sa banque pour un montant de 30 000 €. Il dispose en outre d’une épargne personnelle de 25 000 € pour compléter les fonds nécessaires à son projet.
Les clients et les fournisseurs paient au comptant.
Pour cette première année d’exploitation, nous ne prendrons pas en compte l’imposition de l’entreprise.
Nous allons partir des hypothèses économiques du projet et pour tenter de répondre aux questions de M. Bob, nous allons créer une synthèse des différents documents dans un seul onglet.
Mise en forme des hypothèses
Vous retrouverez les tableaux vierges de cet exemple dans l’onglet hypothese du fichier model_fi.xlsx, la résolution de cet exemple dans l’onglet hypothese du fichier model_fi_resolu.xlsx.
Sur le niveau des ventes
Scénarios
M. Bob a établi trois hypothèses sur le niveau de ventes journalières du café : une hypothèse pessimiste, une hypothèse de base et une hypothèse optimiste :
Nous allons mettre en place une liste déroulante qui nous permettra de sélectionner un scénario et le niveau de vente correspondant.
Dans la plage de cellules C2:C4 de l’onglet hypothese se trouve le tableau suivant :
Nous allons placer la liste déroulante dans la cellule C2.
Sélectionnez la cellule C2, dans le ruban, sélectionnez l’onglet Données, groupe Outils de données, cliquez sur Validation des données.
La boîte de dialogue Validation des données apparaît à l’écran.
Dans la liste déroulante Autoriser, sélectionnez Liste.
Dans la zone Source,
cliquez sur l’icône 
, sélectionnez la plage de cellules C6:E6 c’est-à-dire
la plage contenant le nom des différentes hypothèses,
puis appuyez sur la touche Entrée.
Le résultat devrait être le suivant :
Cliquez sur le bouton OK.
À présent si vous activez la cellule C2 et que vous cliquez sur l’icône qui apparaît sur la droite de cette cellule, vous aurez la possibilité de sélectionner un des trois scénarios.
Recherche du nombre de glaces et de cafés vendus par jour selon le scénario choisi
Nous allons rechercher le nombre de glaces et de cafés correspondant au scénario sélectionné. Pour cela nous pouvons...
Plan de remboursement de l’emprunt
Pour réaliser son projet, M. Bob pense avoir besoin d’un emprunt de 30 000 €. Après renseignement auprès de son banquier, il s’agirait d’un emprunt à taux fixe de 4 % annuel (soit 0,3333 % mensuel) sur une durée de 120 mois.
Les mensualités seront constantes.
Le calcul du remboursement d’un emprunt à mensualités constantes s’appuie sur une formule mobilisant les intérêts composés :
Ou
K = capital emprunté
T = taux mensuel
N = nombre de mensualités
Application avec Excel
Dans l’onglet emprunt du fichier model_fi.xlsx figure un tableau récapitulant les termes de l’emprunt :
Un peu plus bas se trouve un tableau qui servira à calculer chaque mensualité ainsi que le capital restant dû, les intérêts et les amortissements de l’emprunt.
La résolution de cet exemple se trouve dans l’onglet emprunt du fichier model_fi_resolu.xlsx.
Les zones nommées
La cellule B4 a été nommée MT_PRET.
La cellule B6 a été nommée TAUX_MENSUEL.
La cellule B7 a été nommée DUREE.
Calcul du capital restant dû à la période 0 :
Dans la cellule B11, saisissez la formule =MT_PRET.
Calcul des intérêts à la période 1 :
Nous allons utiliser la fonction INTPER. Cette fonction calcule le montant des intérêts d’un emprunt pour une période donnée.
Sa syntaxe est INTPER(taux; pér; npm; va; [vc];[type]).
Sélectionnez la cellule C11, cliquez sur le bouton Insérer une fonction 
situé dans la barre de formule.
Dans la boîte de dialogue Insérer une fonction, recherchez la fonction INTPER...
Mise en place du compte de résultat prévisionnel
Un compte de résultat prévisionnel vierge se trouve dans la plage de cellules A4:O21 de l’onglet cpte de R + tresorerie prev.
La résolution de cet exemple se trouve dans l’onglet cpte de R + tresorerie prev du fichier model_fi_resolu.xlsx.
Calcul du chiffre d’affaires mensuel
De l’activité Glaces
Le nombre de ventes de glaces par mois se trouve dans la plage C15:N15 de l’onglet hypothese. Pour obtenir le chiffre d’affaires de l’activité Glaces, nous allons multiplier chacune de ces valeurs mensuelles par le prix de vente unitaire H.T des glaces.
Dans l’onglet cpte de R + tresorerie prev, sélectionnez la plage de cellules C6:N6. Dans la barre de formule, saisissez =hypothese !C15*pu_glaces. Utilisez le raccourci-clavier CtrlEntrée pour valider la formule jusqu’à la cellule N6.
Pour calculer le total du chiffre d’affaires de l’activité Glaces, dans la cellule O6, saisissez =SOMME(C6:N6).
Le résultat est 115 125.
De l’activité Café
Sélectionnez la plage de cellules C7:N7. Dans la barre de formule, saisissez =hypothese!C16*pu_cafe. Utilisez le raccourci-clavier CtrlEntrée pour valider la formule jusqu’à la cellule N7.
Pour calculer le total du chiffre d’affaires de l’activité Café, dans la cellule O7, saisissez =SOMME(C7:N7).
Le résultat est 75 000.
Chiffre d’affaires total
Sélectionnez la plage de cellules C8:N8. Dans la barre de formule, saisissez =C6+C7. Utilisez le raccourci-clavier CtrlEntrée pour valider la formule jusqu’à la cellule N8.
Pour calculer le chiffre d’affaires total, dans la cellule O8, saisissez =SOMME(C8:N8).
Le résultat est 190 125.
Nommez la plage de cellules...
Le plan de trésorerie prévisionnel
Dans la gestion quotidienne, particulièrement dans le 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 de résultat, mais si vous manquez de « cash » pour payer votre employé et vos fournisseurs à l’instant T, votre entreprise a un sérieux problème.
Le plan de trésorerie est probablement 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 notre plan de trésorerie, nous allons commencer avec le solde d’ouverture ajouter les encaissements et déduire les décaissements de la période. Nous obtenons un solde de clôture. Ce solde devient le solde d’ouverture de la période suivante.
Le plan de trésorerie ne regroupe que les dépenses décaissées et les recettes encaissées : ni les charges non décaissables (par exemple : les dotations aux amortissements et provisions) ni les produits non encaissables...