Cours : Excel Budget Prévisionnel
| Cours : Excel Budget Prévisionnel Auteur : Négocia Informatique |
I. Problème à résoudre
Réalisation d'un petit budget prévisionnel à des fins de simulation pour une entreprise produisant deux produits; ce budget vise simplement à contrôler la rentabilité de l'activité de l'entreprise au travers de calculs simples.
Nous réaliserons trois grandes masses :
LES VENTES
LES CHARGES FIXES
LES CHARGES VARIABLES
Ces masses nous permettrons de réaliser les calculs de la marge totale, de la marge sur coût variable ainsi que de cumuler les marges trimestrielles pour arriver à une marge annuelle.
II. Réalisation du tableau
A. Entrée des libellés du tableau
Le titre est centré au moyen de l'outil centrer sur plusieurs colonnes de la barre d'icônes. D'autre part, la police du titre est agrandie, le texte mis en gras et le tout encadré avec 3 outils de la barre d'icônes; à vous de les retrouver.
La saisie des intitulés de lignes ou colonnes se fait directement dans les cellules concernées (voir le modèle, sur la page suivante); il suffit de se placer dans une cellule et de taper le texte.
La commande Colonne - Largeur du menu Format vous permet à l'occasion d'élargir les colonnes. Pour ce faire, cliquer sur le bouton de sélection globale (à l'intersection des numéros de Lignes/Colonnes) pour sélectionner toute la feuille et lancez la commande - entrez 14.
Vous allez réaliser cette même opération avec la souris pour ajuster la largeur de votre colonne de libellé en fonction de l'intitulé le plus long; procédez comme nous l'avons déjà vu, c'est-à-dire en cliquant sur les séparateurs de colonnes, et en ajustant visuellement la largeur.
B. Entrée des données non calculées
Entrez les valeurs de départ pour les ventes de A et B au premier trimestre (300 000 et 50 000), ainsi que le détail des coûts fixes, qui comme leur nom l'indique n'ont pas à être calculés.
Saisie du tableau :
C. Entrée des formules de calcul
Voici, données en français, les formules de calcul rencontrées dans le tableau; n'appliquez ces formules que dans la première colonne, les autres colonnes seront obtenues par une recopie ultérieure.
1. Les zones de totalisation
* La zone TOTAL VENTES = VENTES A + VENTES B
* La zone TOTAL COUTS VARIABLES =
SALAIRES VARIABLES
+ ENTRETIEN
+ REPARATIONS
+ MATIERES PREMIERES
* La zone TOTAL COUTS FIXES =
AMORTISSEMENTS
+ SALAIRES FIXES
+ TAXES
+ LOYERS
+ AUTRES CHARGES
* La zone TOTAL COUTS =
TOTAL COUTS FIXES
+ TOTAL COUTS VARIABLES
2. Le calcul des charges variables
On utilisera les formules suivantes pour calculer les charges variables. Ces formules sont bien sûr arbitraires et on peut imaginer que chaque entreprise décide celles dont elle estime qu'elles sont le plus proche de la réalité.
ENTRETIEN = 10% du TOTAL VENTES
REPARATIONS = 12% du TOTAL VENTES
MATIERES PREMIERES = 20% des VENTES A + 15% des VENTES B
SALAIRES VARIABLES :
Si le TOTAL VENTES du trimestre est supérieur à 450 000 €, les salaires supplémentaires sont estimés à 30% de la "frange" qui dépasse 450 000 €, sinon ils sont nuls. exemple : TOTAL VENTES = 500 000 €.
SALAIRES VARIABLES = (500000 - 450000) * 30% = 50000 * 0,3 = 15000 €.
3. Les zones de marge
- MARGE SUR COUTS VARIABLES = TOTAL VENTES - TOTAL COUTS VARIABLES
- RESULTAT DU TRIMESTRE = TOTAL VENTES - TOTAL DES COUTS
- RESULTAT CUMULE = TOTAL DES RESULTATS DE L'ANNEE, CUMULE...
C'est-à-dire RESULTAT DU TRIMESTRE EN COURS + RESULTAT CUMULE DU TRIMESTRE PRECEDENT - Attention, cette formule est impossible au trimestre 1, il vous faut alors trouver autre chose...
4. Détails des calculs
N'oubliez pas de commencer la saisie de vos formules de calcul par la touche "=".
* Pour ce qui concerne les sommes (totalisations), Excel vous propose 3 solutions parmi lesquelles vous allez devoir choisir à chaque fois la plus appropriée.
- Le bouton SOMME AUTOMATIQUE. En cliquant sur celui-ci, Excel met en oeuvre la fonction SOMME(début : fin).
ex: TOTAL DE VENTES est défini automatiquement par B7=SOMME(B5:B6)
Vous pouvez mettre en oeuvre cette fonction vous-même : entrez le signe égal (=) dans la cellule devant contenir votre formule, tapez "SOMME(", puis sélectionnez à la souris ou avec votre clavier la zone à sommer; fermez la parenthèse et validez.
Vous devez savoir, pour utiliser cette fonction, qu'Excel interprète les deux points (":") comme séparateur de zone qui signifie d'une certaine façon "JUSQU'A", mais que vous pouvez utiliser aussi le point virgule signifiant "ET", pour sommer plusieurs zones:
SOMME(DébutZoneA:FinZoneA ; DébutZoneB:FinZoneB ; CelluleUnique ;...) où ":" signifie "jusqu'à" et ";" signifie "et"
Cette "grammaire" est utilisée par toutes les fonctions Excel, il est donc primordial de bien la comprendre. Il est encore possible, voire obligatoire, pour les sommes ne faisant intervenir que des cellules non contigües, d'entrer le signe égal, puis de cliquer sur le premier terme, d'entrer l'opérateur ("+"), puis de cliquer sur le second terme, etc. enfin de valider.
Cette dernière solution permet aussi de calculer les zones de marge, qui sont des soustractions de cellules éloignées.
ex: la MARGE SUR COUTS VARIABLES est définie par vos soins par B22=B7-B20
* La seule véritable difficulté réside dans le calcul des SALAIRES VARIABLES.
Pour cela, nous allons calculer (TOTAL VENTES - 450 000). Le résultat sera NEGATIF si le TOTAL VENTES est inférieur à 450 000 et positif ou nul sinon. Il faut donc calculer 30% de l'expression (TOTAL VENTES - 450 000) si celle-ci est positive.
Pour automatiser ce que nous venons de décomposer, nous allons utiliser la fonction MAX( ) d'Excel qui permet de choisir le plus grand nombre dans une liste de cellules ou de zones. ex: si une cellule contient l'expression = MAX(2;3;7;12;0), Excel affichera 12.
Comme nous venons de le voir, le point virgule signifie "ET" et permet l'énumération d'éléments, mais la fonction MAX( ) accepte aussi des zones délimitées par ":" ("JUSQU'A").
Les éléments de la liste entre parenthèses peuvent être :
- des valeurs (45;8;-9...)
- des références de cellules de la feuilles de travail des références aussi bien Absolues que
Relatives (B12; $D$21...)
- des zones délimitées par ":" (B2:C4...)
- des expressions mathématiques comprenant des chiffres ou/et des références de cellules (E5*4;5+3;$F$2*5...)
Dans notre cas, nous choisirons donc de multiplier le maximum des 2 nombres suivants : TOTAL VENTES - 450000 et ZERO par 0,3 (c.a.d 30%). Cette solution nous garantissant de ne pas obtenir un résultat NEGATIF !
La formule sera donc :
B16=MAX(B7-450000;0)*0,3
* La dernière difficulté réside dans le calcul du RESULTAT CUMULE
- Ce dernier sera calculé en additionnant le RESULTAT CUMULE du trimestre en cours avec le RESULTAT CUMULE du trimestre précédent.
Mais... il faut faire attention, car au trimestre 1 ce calcul est impossible : et pour cause, il n'y a pas de trimestre précédent. Le RESULTAT CUMULE du trimestre 1 est donc calculé comme (et par conséquent égal au) RESULTAT CUMULE du trimestre en cours.
D. Finalisation du tableau
Pour compléter le tableau, il suffit de recopier la colonne B, trois fois vers la droite.
Pour cela, il vous suffit de sélectionner, à la souris ou au clavier la colonne 2 avec tous ses calculs, excepté le RESULTAT CUMULE, ainsi que les colonnes 3, 4, 5, c'est-à-dire la zone B5:E23 ; puis choisissez la commande Recopiez > A droite du menu Edition (raccourci :CTRL + D) : le tour est joué, non seulement les valeurs fixes sont recopiées (VENTES A et B, COUTS FIXES), mais les formules également.
Vous pouvez obtenir le même résultat avec la recopie souris : sélectionnez la première colonne modèle, cliquez sur la poignée inférieure droite de la zone sélectionnée et glissez sans lacher en sélectionnant les trois colonnes vers la droite: la recopie s'effectue de la même façon.
Pour en finir avec les calculs, il convient de régler le cas du RESULTAT CUMULE ; comme nous l'avons vu, dès le deuxième trimestre la formule change. On entrera donc, au deuxième trimestre, la formule consistant à additionner le RESULTAT CUMULE du trimestre précédent avec le RESULTAT DU TRIMESTRE en cours, soit C24=B24+C23. Enfin, vous recopiez cette nouvelle formule vers les deux cellules situées à droite.
Enfin, pour en finir avec les calculs, une simple somme calculée dans la cellule F5 qui est ensuite recopiée vers le bas jusqu'en ligne 23 vous permet de réaliser la colonne CUMUL.
Il ne reste plus alors qu'à indiquer à Excel que les sommes représentent des Euros avec 2 décimales : allez dans le menu Format, commande Cellule, onglet Nombre, type Monétaire et choisissez Euro (€).
Vous allez ensuite, avec le menu Outils, commande Options, onglet Affichage cochez la case
Quadrillage (pour retirer la croix), afin d'ôter les repères quadrillés à l'écran, cela va vous permettre de réaliser les encadrements de votre tableau (menu Format, commande Cellule, onglet Bordure). La commande Format - Cellule, onglet Police vous permet de parfaire la mise en forme de votre tableau selon le modèle donné ci-après.
Vous avez de nouveau l'occasion d'utiliser la barre d'icônes pour réaliser ces mêmes opérations de façon encore plus rapide; les boutons Bordures, Gras, Italique, taille de caractère et les différents alignements vous permettrons de gagner un temps précieux. A voir également pour des mises en forme rapides la commande Mise en forme automatique du menu Format.
Voici maintenant ce que vous devez obtenir :
III. Commandes Excel
Commandes diverses pouvant être utilisées pour la réalisation de l'exercice.
- pour effacer : il suffit d'appuyez sur la touche pour effacer la zone précédemment sélectionnée.
- pour modifier une formule déjà existante : Placez-vous sur la cellule contenant la formule à éditer (modifier) et appuyez sur la touche ; vous pouvez ensuite vous déplacez librement dans la formule et non dans le tableau. La touche agit ainsi comme un interrupteur sur lequel vous appuyez quand vous voulez vous déplacer dans la feuille de travail ou dans une formule à modifier.
- pour obtenir de l'aide : A tout moment, et surtout lorsque vous êtes confronté à un doute (devant une commande inconnue, par exemple...), n'hésitez pas à demander l'aide d'Excel en appuyant sur la touche - cette aide est dite "CONTEXTUELLE", c'est-à-dire qu'elle vous explique la commande que vous êtes en train de mettre en oeuvre et remplace ainsi avantageusement la documentation souvent indigeste du logiciel.
N'oubliez pas également l'outil Aide à l’extrème droite de la barre d'outils, qui permet de faire appel au système d'aide intégré d'Excel.
IV. Utilisation du modèle
Nous allons maintenant utiliser le tableau pour simuler un certain nombre de cas; pour ce faire vous devez mettre en place de nouvelles formules qui n'ont pour but que d'automatiser la simulation.
Formules à mettre en place au départ:
- Augmentation des ventes de B de 5% par trimestre.
- Trouver une formule de calcul pour que les ventes de A soient constantes sur 4 trimestres (c'est à dire que, si l'utilisateur du modèle essaye une nouvelle valeurs pour A au premier trimestre, celui-ci se reporte automatiquement sur les trois autres trimestres).
=> Ces formules doivent être entrées en Trimestre 2 (C5 et C6) et recopiées deux fois vers la droite.
Problèmeà réaliser par une suite d'essais successifs :
Donc, si les ventes de A sont constantes et celles de B progressent de 5% par trimestre, trouvez un couple de valeurs pour les ventes de A et B au trimestre 1 qui assure le point mort au 4e trimestre (RESULTAT CUMULE = 0 en T4, cellule F24).
Conseil: utilisez les repères de séparation de fenêtres à la souris, afin de partager l'écran et ainsi de voir, d'un seul coup d'oeil, vos valeurs d'essais et le résultat, tout en bas à droite.
ATTENTION : Il n'existe pas qu'une solution unique à cette question... La solution qui vous est donnée cidessous est, par exemple, irréaliste dans les faits, alors qu'elle est mathématiquement irréprochable, pourquoi ?
- On peut s'apercevoir, dans la zone de formule, qu'il a fallu en arriver à la troisième décimale (VENTES A au trimestre 1) pour obtenir zéro cents comme point mort en T4
LVR2K3-15/09