Excel Previsionnel

:

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

Page 2

NEGOCIA INFORMATIQUE – EXCEL BUDGET PREVISIONNEL

* 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)

Page 3

NEGOCIA INFORMATIQUE – EXCEL BUDGET PREVISIONNEL

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.

Page 4

NEGOCIA INFORMATIQUE – EXCEL BUDGET PREVISIONNEL

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.

Page 5

NEGOCIA INFORMATIQUE – EXCEL BUDGET PREVISIONNEL

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.

Page