Gestionnaire de Scénario - Site des étududiants en DESS IGSI ...

L'objectif de cet exercice est d'établir un ensemble de prévisions de vente
à partir d'un ensemble de résultats de vente. Vous devez tout d'abord
construire la feuille de calcul Résultats décrite en figure 1. Cette
feuille contient un ensemble de résultats mensuels de vente constaté sur
les années 2000 à 2003.
[pic]
Figure 1 : Feuille Résultats (1) La construction de cette feuille se passe comme suit : - Saisie du nom d'une feuille de calcul.
Il suffit pour cela de sélectionner le nom de la feuille de calcul en
cliquant sur l'onglet correspondant et de taper le nouveau nom
(Résultats). - Saisie du cadre et des données.
Vous devez saisir le cadre de la feuille (plage de cellules A3:E3),
les mois (plage de cellules A4:A15) et les résultats constatés (plage de
cellules B4:E15). - Mise en forme des cellules.
La mise en forme se déroule en deux temps : sélection des cellules à
mettre en forme puis choix des paramètres de mise en forme. Les
paramètres de mise en forme sont accessibles dans le menu Format/Cellule
ou directement à travers des icônes de la barre d'outils Mise en Forme
que voici : Faites des essais de mise en forme en utilisant les icônes de cette barre
d'outils. Vous devez maintenant compléter la feuille Résultats et spécifier les
formules de calcul qui permettent de déterminer le chiffre d'affaire
réalisé au cours d'une année, les progressions constatées entre deux
années, et l'indice saisonnier. La figure 2 illustre la forme de la feuille
de calcul Résultats à produire. Voici quelques indications pour la mise en place des formules nécessaires à
l'élaboration de cette feuille de calcul. - Formules des chiffres d'affaires. Les chiffres d'affaire annuels sont
la somme des chiffres d'affaires mensuels. Positionnez vous en B16. Tapez
la formule =SOMME(B4:B15). Sélectionnez la plage B16:E16 et déclenchez
l'opération de Recopie à droite dans le menu Edition/Recopie.
- Formules des progressions. Les progressions dépendent des chiffres
d'affaires réalisés au cours de l'année et au cours de l'année
précédente. La formule à utiliser est pour ce calcul est : (CA année n -
CA année n-1) / CA année n-1*100. Positionnez vous en C17 et tapez la
formule =(C16-B16)/B16*100. Sélectionnez la plage C17:E17 et déclenchez
l'opération de Recopie à droite dans le menu Edition/Recopie.
[pic]
Figure 2 : Feuille Résultats (2) - Formules des indices saisonniers. Les indices saisonniers dépendent
des chiffres d'affaire mensuels et annuels réalisés. La formule à
utiliser pour ce calcul est : somme des ventes du mois / somme totale des
ventes. Positionnez vous en F4 et tapez la formule
=SOMME(B4:E4)/SOMME(B$16:E$16). Sélectionnez la plage F4:F15 et
déclenchez l'opération de Recopie en bas dans le menu Edition/Recopie.
Les $ permettent de fixer la ligne 16 pour les cellules B16:E16. Ainsi il
est toujours fait référence aux chiffres d'affaires constatés au cours
des quatre années. Vous pouvez essayer de ne pas mettre ces $ dans la
formule en F4. Déclenchez alors l'opération de recopie et constatez les
résultats.
- Positionner vous enfin en F16 et tapez la formule =SOMME(F4:F15). Il s'agit maintenant de construire la feuille Prévisions. La forme de cette
feuille est décrite en figure 3. Les cellules A3 à A17 sont saisies par
l'utilisateur tandis que les autres cellules sont calculées à partir de la
feuille Résultats comme suit : - Formules des années. Les années sont calculées en fonction des année
du tableau Résultats. Sélectionnez la cellule B3 et tapez la formule
=Résultats!E3+1. Sélectionnez la cellule C3 et tapez la formule =B3+1.
Sélectionnez la plage C3:E3 et recopiez la formule à droite. - Formules des progressions. Les progressions sont égales à la moyenne
des progressions de la feuille Résultats. Sélectionnez la cellule B17.
Tapez la formule =MOYENNE(Résultats!$C17:$E17). Sélectionnez la plage
B17:E17 et recopiez la formule à droite.
[pic]
Figure 3 : Feuille Prévisions - Formules des chiffres d'affaires. Les chiffres d'affaires dépendent du
chiffre d'affaire de l'année précédente et de la moyenne des
progressions. Ils sont déterminés par CA année n-1 * (1 + moyenne des
progressions) / 100. Sélectionnez la cellule B16 et tapez la formule
=Résultats!E16*(1+MOYENNE (Prévisions!B17:E17)/100). Sélectionnez la
cellule C16 et tapez la formule =B16*(1+MOYENNE(B17:E17)/100).
Sélectionnez la plage C1:E16 et recopiez la formule à droite. - Formules des prévisions de vente. Les prévisions de vente dépendent de
l'indice saisonnier du mois et du chiffre d'affaire de l'année
considérée. Sélectionnez la cellule B4 et tapez la formule =Résultats!$F4
*Prévisions!B$16. Sélectionnez la plage B4:E15 et recopiez la formule en
bas et à droite.
On vous demande d'établir sous Excel un plan de remboursement à annuités
constantes pour un capital emprunté C à un taux d'intérêt t pour une durée
n comprise entre 1 et 5 ans. Le plan de remboursement à produire est décrit en figure 4.
Figure 4 : Plan de remboursement Ce plan indique tout d'abord le numéro de la période (mois) de
remboursement. Il donne ensuite, pour chaque période, les informations
suivantes : - Le mois correspondant,
- Le capital restant dû,
- Le montant de l'annuité de remboursement,
- Les intérêts remboursés,
- Le capital remboursé,
- Les intérêts cumulés. Les fonctions AUJOURDUI, DATE, MOIS et ANNEE sont utilisées pour calculer
la date d'une période donnée. La date de la première période est la date de
l'établissement du plan de remboursement. Les dates des périodes suivantes
sont calculées en fonction de la date de la première période et du numéro
de la période considérée. Les fonctions VPM, INTPER et PRINCPER qu'Excel
propose en standard sont utilisées pour calculer respectivement le montant
de l'annuité de remboursement d'une période, la part des intérêts
remboursés au cours d'une période et la part du capital remboursé au cours
d'une période. La syntaxe de ces fonctions est la suivante : - AUJOUDHUI() donne la date du jour ; cette date est affichée selon le
format de la cellule concernée. - DATE(année;mois;jour) donne la date correspondant à l'année, au mois
et au jour passés en paramètre ; cette date est affichée selon le format
de la cellule concernée. - MOIS(date) donne le mois de la date passée en paramètre ; le résultat
est une valeur numérique (comprise entre 1 et 12). - ANNEE(date) donne l'année de la date passée en paramètre ; le
résultat est une valeur numérique. - VPM(taux;nb_periodes ;montant) permet de calculer le montant de
l'annuité d'une période ; taux correspond au taux d'intérêt mensuel,
nb_periodes correspond au nombre de mois, et montant correspond au
montant emprunté. - INTPER(taux ;n°_periode ;nb_periode ;montant) permet de calculer la
part des intérêts payés au cours d'une période ; taux correspond au taux
d'intérêt mensuel, n°_periode correspond à la période considérée,
nb_periodes correspond au nombre de mois, et montant correspond au
montant emprunté. - PRINCPER(taux ;n°_periode ;nb_periode ;montant) permet de calculer la
part du capital remboursé au cours d'une période ; taux correspond au
taux d'intérêt mensuel, n°_periode correspond à la période considérée,
nb_periodes correspond au nombre de mois, et montant correspond au
montant emprunté. On peut noter que le nombre de lignes apparaissant dans le plan de
remboursement correspond exactement au nombre de périodes (mois) de
remboursement. On vous demande de valider le nombre d'années. L'objectif est d'éviter des
erreurs de saisie de la part de l'utilisateur. Vous pouvez pour cela
utiliser le menu Données/Validation. Modifiez enfin votre plan de remboursement afin de considérer des emprunts
de 1 à 20 ans.
On souhaite établir sous Excel un outil d'aide au calcul de l'impôt. Cet
outil se veut simple puisque seuls les éléments courants des déclarations
sont considérés. Ces éléments sont : - Le nombre de parts qui dépend du nombre d'adultes (1 au minimum -le
déclarant- et 2 au maximum -le déclarant et le conjoint-) et du nombre
d'enfants à charges. Si on a 1 adulte, le nombre de parts est 1 plus le
nombre d'enfants. Si on a 2 adultes, le nombre de parts est égal à 2 plus
le nombre d'enfants divisé par 2 plus 0,5. - Les revenus bruts déclarés par chaque adulte. - Les frais relatifs à chaque adulte. Ces frais sont égaux à 10% du
revenu brut ou aux frais réels. Les frais réels correspondent à une
évaluation des coûts occasionnés par les déplacements sur le lieu de
travail. Ils se calculent en se basant sur le barème suivant : | |=11 |
|Moins de 5000 km |2,80 |2,92 |3,05 |3,30 |3,57 |3,98 |
|Entre 5000 et 2000 |2,12 |2,22 |2,54 |2,77 |3,01 |3,29 |
|km | | | | | | |
|Plus de 20000 km |1,85 |1,95 |2,04 |2,20 |2,42 |2,74 | Les frais réels dépendent de la distance parcourue et de la puissance du
véhicule. Ainsi, pour un véhicule de 5 chevaux et une distance de 7000
km, les frais réels sont de 7000 * 2,12. - Les revenus imposables de chaque adulte qui sont égaux aux revenus
bruts moins les frais ainsi que le revenu imposable de la déclaration qui
est égal à la somme des revenus imposables de chaque adulte. - Le quotient familial qui