Blog ENI : Toute la veille numérique !
-25€ dès 75€ sur les livres en ligne, vidéos... avec le code FUSEE25. J'en profite !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. Modélisation financière avec Excel (2e édition)
  3. Modélisation financière avec Excel
Extrait - Modélisation financière avec Excel (2e édition) De l'analyse à la prise de décision
Extraits du livre
Modélisation financière avec Excel (2e édition) De l'analyse à la prise de décision
5 avis
Revenir à la page d'achat du livre

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...

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...

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 :

images/02SOB02.png

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 :

images/02SOB03.png

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.

images/SOB02_1_1N.png

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 images/02SOB05.png, 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 :

images/02SOB06.png

 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.

images/02SOB07.png

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 utiliser la fonction RECHERCHEHou bien la fonction RECHERCHEX.

Avec la fonction RECHERCHEH

La fonction RECHERCHEH est similaire à la fonction RECHERCHEV mais, au lieu de rechercher une valeur dans une plage de manière verticale (V), elle procède de manière horizontale (H).

Sa syntaxe est =RECHERCHEH(valeur cherchée;tableau;n° index de ligne;[valeur proche]).

 Sélectionnez la cellule C3, puis cliquez sur le bouton Insérer une fonction ...

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 :

Mensualité constante = images/eq1.PNG

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 :

images/02SOB15.png

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.

images/02SOB16.png

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...

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.

images/02SOB19.png

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 C8:N8, chiffre_affaires.

Calcul du salaire mensuel du serveur

Le salaire annuel se trouve dans la cellule C29 de l’onglet hypothese. Cette cellule a été nommée salaire. Pour calculer le salaire mensuel, nous allons diviser le montant annuel du salaire du serveur par 12.

 Dans l’onglet cpte de R + trésorerie prev, sélectionnez la plage de cellules C9:N9. Dans la barre de formule, saisissez...

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.

images/02SOB22.png

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 ne doivent y figurer.

Application avec Excel

La structure du tableau de la trésorerie prévisionnelle est la suivante :

images/02SOB23.png

Nous disposons de tous les éléments pour mettre en place notre plan de trésorerie prévisionnelle à l’exception du règlement de TVA à décaisser.

Nous calculerons cet élément ultérieurement.

Vous retrouverez le tableau vierge de cet exemple dans l’onglet cpte...