Atelier Excel : coût d'achat d'un appartement.

• Durée : 2x 2 heures + travail personnel.
• Prérequis :
  • Visual Basic (un minimum) pour les macros.
  • Outils mathématiques utilisés en gestion pour comprendre les calculs, résoudre les systèmes d'équation.

Nous allons créer une feuille Excel qui permet de comparer le coût d'achat au coût de location d'un appartement. Pour que notre projet soit réellement utilisable, nous allons prendre en compte de façon réaliste tous les paramètres (montant de l'achat, emprunt, charges, etc.) Vous remarquez ci-contre que toutes les cellules avec du texte en blanc sont des cellules saisies par l'utilisateur (les autres sont des cellules calculées par Excel). Cela permet à l'utilisateur de savoir ce qu'il doit saisir.

• Lancez Excel et réaliser une feuille semblable à celle ci-dessus. Pour l'instant vous ne vous préoccupez que de l'aspect, pas des formules.
  
  
• Pour les boutons d'option type de taux mensuel et les boutons de commande : faites apparaître la barre d'outils qui les contient avec le menu Affichage, Barre d'outils, Formulaires. Une fois que vous les avez placé sur la feuille, liez-les à une cellule quelconque grâce au Menu contextuel > format de contrôle > onglet Contrôle > Cellule liée, mettez par exemple la valeur A100.

• Vous devez renommez toutes les cellules susceptibles d'intervenir dans des calculs. En effet, en lisant un calcul, on comprend plus facilement si c'est "TauxNotaire x MontantAchat" que si c'est "D15 x C7" (attention, c'est long à faire mais important).
  
  Pour renommer : menu Insertion > Nom > Définir....
  

Vous devez saisir les formules dans les cellules calculées, en vous aidant des indications suivantes :

• [Frais de notaire (€)] = [Frais de notaire (%)] * [Montant de l'achat (€)]
  
  
• [Frais dossiers & hypothèque (€)] et [Montant de l'emprunt (€)] : les deux équations suivantes forment un système de deux équations à deux inconnues, que vous devez résoudre mathématiquement avant de les saisir sous Excel :
  • [Frais dossiers & hypothèque (€)] = ([Frais de dossier (%)] + [Frais d'hypothèque (%)]) * [Montant de l'emprunt (€)]
  • [Montant de l'emprunt (€)] = [Montant de l'achat (€)] + [Frais de notaire (€)] + [Frais de dossier et d'hypothèque (€)] - [Apport personnel (€)]
    
    
• Taux mensuel :
  • Dans le cas du taux proportionnel (celui utilisé en France) : [Taux mensuel] = [Taux Annuel] / 12
  • Dans le cas du taux actuariel : [Taux mensuel] = Racine douzième(1 + [Taux Annuel]) - 1
    
    
• Pour la mensualité (d'après les théorèmes sur les suites géométriques), le calcul est le suivant. Vous devriez vous-même décomposer ce calcul dans plusieurs cellules, pour ne pas faire tout d'un coup dans la cellule finale (vous n'arriveriez pas à débuguer) :
  • q (cellule intermédiaire) = 1 + [Taux Mensuel (%)]
  • [Part du capital le premier mois] (cellule intermédiaire) = [Montant de l'emprunt (€)] * (1-q) / (1- (q^[Durée (mois)]))
  • [Part des intérêts le premier mois] (cellule intermédiaire) = [Montant de l'emprunt (€)] * [Taux mensuel (%)]
  • [Part de l'assurance le premier mois] (cellule intermédiaire) = [Assurance (%)] * [Montant de l'emprunt (€)] / 10
  • [Mensualité] = [Part du capital] + [Part des intérêts] + [Part de l'assurance]
    
    
• Date de fin : Calculée à partir de la date de début, à laquelle on ajoute un nombre d'années égal à [Durée (en année)]. Pour cela vous devez :
  • Décomposer la date de début en jour, mois, année grâce aux fonctions JOUR(), MOIS(), ANNEE() ;
  • Recomposer la date de fin avec la fonction DATE(année début + durée ; mois début ; jour début)
    
    
• [Total mensuel] = [Mensualité] + [Taxe Foncière] / 12 + [Autres frais] / 12
  
  
• [Différence] = [Total Mensuel de l'achat (€)] - [Total Mensuel de la location (€)]
  
  
• Pour la location : [Total] = [Loyer] + [Charges] - [ALS] + [Autres frais annuels] / 12

La mise en forme conditionnelle permet de changer certains styles (police de caractère, couleur des caractères, couleur de la cellule), selon des conditions que l'on peut définir.

• Sélectionnez les deux cellules concernées.
  
  
• Allez dans le menu Format > Mise en forme conditionnelle.
  
  
• Remplissez correctement les deux conditions et la mise en forme conséquente :
  • La condition : La formule est ... =Difference > 0
  • La différence est positive --> le fond de la cellule est rouge.
  • La différence est négative ou nulle --> le fond de la cellule est vert.
    
    
• Validez et vérifiez le résultat.

Pour les boutons de commande, vous devez avoir inséré ceux de la barre d'outils formulaire (il y en a également dans la barre d'outils commande, mais qui sont utilisés un peu différemment). On peut très facilement associer une macro à ces boutons de commande, ce qu'on fera plus loin grâce aux menus contextuels.

Le but ici est d'automatiser l'inversion d'une formule mathématique : au lieu de taper des valeurs initiales (montant, apport...) pour obtenir un résultat calculé (Total mensuel), on va donner le résultat (Total Mensuel) et Excel va automatiquement recalculer une des valeurs initiales correspondantes.

Qu'est-ce qu'on veut ?

• L'objectif premier est de faire fonctionner le bouton de commande "Recalculer : apport personnel".
• En temps normal, l'utilisateur saisi un apport personnel et Excel calcule les mensualités correspondantes.
• Là on va "inverser" le calcul : l'utilisateur va dire combien il veut dépenser maximum par mois, et Excel va en déduire l'apport personnel correspondant.
• Tout ceci se fait sans qu'on ait à inverser nos formules mathématiques : on a simplement dit à Excel comment calculer le Total Mensuel en fonction de l'apport, et pas l'inverse ! C'est donc un outil très puissant.

Comment faire ?

• On va suivre plusieurs étapes : apprendre à faire fonctionner les valeurs cible, les stocker dans une macro, puis affecter la macro au bouton de commande.
• Tout d'abord, en guide de test, lancer la commande Outil > Valeur Cible.
  • La cellule à définir (celle dont on va définir la valeur désirée) est le total mensuel.
  • La valeur à atteindre est, par exemple, 1000 (plus tard, on rendra ceci paramétrable)
  • La cellule à modifier est l'apport personnel.
• Vous constatez qu'Excel a bien modifié l'apport personnel pour obtenir un total mensuel égal à 1000 €.
• Refaites la même chose en enregistrant cette démarche dans une macro :
  • lancez la commande Outil > Macro > Nouvelle Macro ;
  • Nommez-là RecalculerApportPersonnel ; puis valider avec OK. Attention ! Tout les manipulations que vous faites à partir de maintenant sont enregistrées dans la macro. Vous devez donc faire le strict minimum (ce qui suit).
  • réexécutez l'outil valeur cible comme tout à l'heure (lors du test),
  • arrêtez l'enregistrement de la macro dès que vous avez fini la valeur cible.
• On veut maintenant remplacer le 1000 par la valeur que l'utilisateur a saisi dans la cellule intitulée "Vous voulez dépenser maximum par mois" :
  • Allez dans l'outil Visual Basic Editor (Outils > Macros... > Visual Basic Editor),
  • Retrouvez, dans un des modules, la procédure que Excel a créé pendant que vous enregistriez votre macro. Cette procédure doit s'appeler Sub RecalculerApportPersonnel () puisque c'est ainsi que vous l'avez nommée.
  • Dans cette procédure, remplacez 1000 par Range("G25"), ou G25 est l'adresse de la valeur "Vous voulez dépenser maximum par mois".
• Il ne reste plus qu'à affecter cette macro, ou cette procédure, au bouton de commande intitulé "Recalculer : apport personnel". Vous pouvez réaliser cela grâce au menu contextuel sur le bouton de commande.

Maintenant, vous devez refaire la même chose avec le bouton de commande "Recalculer : Montant de l'achat".

Qu'est-ce qu'on veut ?

• Grâce aux valeurs cibles, on a pu réaliser nos boutons de commande "Recalculer : Montant de l'achat" et "Recalculer : Apport personnel".
• Ici, on doit faire la même chose à un détail près : la durée du prêt ne peut pas être un nombre à virgule, mais une valeur entière (on ne peut pas faire un prêt sur 5,267 ans !). En conséquent, lorsque l'outil valeur cible aura trouvé le résultat, on devra l'arrondir à l'entier supérieur (6 ans donc).

Comment faire ?

• Commencez de la même façon que pour les deux autres boutons de commande : stocker dans une macro l'outil valeur cible qui permet de recalculer la durée du prêt, modifier la procédure Visual Basic de façon à tenir compte de la cellule "Vous voulez dépenser maximum par mois : ", affectez la macro au bouton de commande.
• Vous devez faire le travail supplémentaire suivant :
  • dans la procédure (sous Visual Basic Editor), après la méthode correspondant à l'outil valeur cible, récupérez dans une variable la durée du prêt ainsi calculée (vous devez donc affecter le contenu de la cellule à une variable) :
         var = feuil1.cells(n° ligne, n° colonne)
    
    
  • Grâce à la fonction Visual Basic Int (qui arrondi un réel à l'entier inférieur), arrondissez la durée à l'entier supérieur :
         var = ...
    
    
  • Stockez la valeur ainsi obtenue dans la cellule où vous l'avez prise :
         feuil1.cells(n° ligne, n° colonne) = var
    
    
• Testez l'ensemble.

Dans une deuxième feuille, vous devez réaliser ce tableau d'amortissement, sur une durée de 15 ans (= 180 lignes). Vous devez faire en sorte que :

• Les formules tiennent compte de tout ce qui a été saisi dans le premier tableau : par exemple, si on change la date de début du prêt, le montant de l'achat, etc., il faut que ce tableau en tienne compte ! Sinon il ne sert à rien.
  
  
• La colonne [Capital Restant dû] arrive à 0 au bout de la durée du prêt, sinon c'est que vos calculs sont faux.
  
  
• Les années soient coloriées, selon si elles sont paires ou impaires (grâce à la mise en forme conditionnelle), comme dans l'exemple ci-dessus.
  
  
• Au-delà de la durée du prêt (si le prêt dure moins de 15 ans), les cellules soient automatiquement laissées vides.

En ce qui concerne les formules, elles sont assez intuitives mais les voici quand même pour rappel :

• Première ligne :
  • [Date] = Date de l'emprunt
  • [Capital dû] = Montant de l'emprunt.
  • [Mensualité] : figure déjà dans le premier tableau.
  • [Dont capital] : a déjà été calculé (en tant que cellule intermédiaire) pour le premier tableau.
  • [Dont intérêt] = a déjà été calculé (en tant que cellule intermédiaire) pour le premier tableau
  • [Dont assurance] = a déjà été calculé (en tant que cellule intermédiaire) pour le premier tableau
  • [Capital restant dû] = [Capital dû] - [Dont capital]
    
    
• Lignes suivantes :
  • [Date] = date de la ligne précédente plus un mois.
  • [Capital dû] = capital restant dû de la ligne précédente.
  • [Mensualité] : constante (= mensualité de la ligne précédente).
  • [Dont capital] = dont capital de la ligne précédente * (1 + [Taux mensuel]). C'est une suite géométrique.
  • [Dont intérêt] : [capital dû] * [taux mensuel].
  • [Dont assurance] : constante.
  • [Capital restant dû] : même calcul que la ligne précédente.

Qu'est-ce qu'on veut ?

• Dans la feuille, on veut insérer insérez le graphe correspondant au graphe ci-contre.
• Sur 15 ans, il montre l'évolution de la part du capital et de la part des intérêts dans les mensualités.
• Il est donc basé sur le tableau d'amortissement.

Comment faire ?

• Choisissez la commande "Insertion --> Graphique" et laissez-vous guider par l'assistant et par l'aide.

Sous les tableurs, on a la possibilité de protéger la feuille, de façon à ce que :

• L'utilisateur ne puisse pas effacer, par inadvertance, une formule de calcul contenue dans une cellule (à priori, l'utilisateur ne sait pas forcément ce qu'il doit saisir et ce qui est calculé, il peut donc saisir au mauvais endroit si les cellules calculées ne sont pas protégées)
  
  
• L'utilisateur ne puisse rien modifier du tout si vous le décider ainsi : ni formules, ni mise en forme... il pourra seulement saisir des valeurs dans les cases appropriées.

Pour cela on dispose des outils suivant :

• Verrouillage des cellules : par défaut, toutes les cellules sont verrouillées. Le principe consiste donc à déverrouiller celles dans lesquelles l'utilisateur final peut saisir des valeurs. Le verrouillage est accessible dans Format > Cellules.
  
  
• Protection de la feuille, sans mot de passe : cette commande active le verrouillage des cellule. Lorsqu'on a protégé la feuille, on ne peut plus saisir dans les cellules qui sont restées verrouillées, ce qui empêche les erreurs de saisie. Disponible dans Outils > Protection.
  
  
• Protection de la feuille, avec mot de passe : non seulement cela active le verrouillage des cellules, mais en plus l'utilisateur final ne pourra pas désactiver cette protection pour modifier des formules ou de la mise en forme.
  
  
• Les deux premières étapes doivent être faites systématiquement lorsqu'on livre une application sous Excel à un utilisateur. La troisième étape sera effectuée si les contraintes de sécurité le justifient (feuille pour établir des bulletins de paie par exemple).

Vous devez donc :

• déverrouiller les cellules saisies par l'utilisateur ;
• protéger la feuille, sans mot de passe.