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 :

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 :

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

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

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.

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

Application avec Excel

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

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