Modélisation financière dans Excel: Cédule d’amortissement linéaire des actifs

À propos de l’auteure

Sophie Marchand, M.Sc., CPA, CGA

Auteure du blogue Mon Cher Watson et du site le CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA et cumule plus de 12 années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultanteformatrice et conférencière.

smarchand@lecfomasque.com
514-605-7112

.
Élaborer une cédule d’amortissement des actifs peut sembler beaucoup plus simple que ce ne l’est en réalité. En effet, il faut non seulement amortir la base d’actifs existants par catégorie d’actifs mais il faut également amortir les nouveaux actifs. Pour une entreprise qui fait des investissements intensifs en CAPEX, le maintien de la cédule d’amortissement des actifs n’est pas toujours évident, surtout lorsque cette cédule d’amortissement des actifs, fait partie d’un modèle financier, qui, par définition, doit se mettre à jour automatiquement lorsqu’il y a des changements à ce niveau. Bref, je vous présente ci-bas une formule fort utile pour calculer adéquatement l’amortissement linéaire. Si ce truc vous intéresse, je vous invite à découvrir ma formation sur les Excel – Modélisation financière (niveau 1), où je couvre notamment ce sujet et encore bien d’autres.

.

Cédule d’amortissement d’actifs

Note: Dans l’exemple ci-dessous, vous verrez que j’ai utilisé des hypothèses peu vraisemblables au niveau des nombres de mois d’amortissement (4, 6 et 8). J’utilise ces données simplement pour fin d’illustration. En clair, j’ai choisi des nombres de mois qui me permettent d’illustrer l’amortissement dans un simple écran d’ordinateur!

Si vous jetez un coup d’oeil à la section d’amortissement linéaire du modèle financier ci-bas, vous noterez que j’ai inséré 3 sections, soit les dépenses en CAPEX, l’amortissement et les actifs nets. J’aurais pu également ajouter une section pour les ventes d’actifs mais j’ai préféré mettre cette notion de côté pour cet exemple. J’ai également inséré une colonne « ouverture » afin d’obtenir les soldes d’ouverture par catégorie d’actifs.

Ainsi, dans la section CAPEX, les cellules grises en pointillés servent à faire l’entrée de données, soit les montants d’investissements en CAPEX par mois.

Dans la section Amortissement, je procède au calcul d’amortissement par catégorie d’actifs, grâce à une formule robuste que j’ai détaillée plus bas.

Dans la section Actifs nets, je présente le solde amorti des actifs, par catégorie, soit une simple somme, suivie d’une soustraction.

En somme, dans l’exemple suivant, on peut changer les nombres de mois d’amortissement des catégories d’actifs, les investissements en CAPEX et les soldes d’ouverture des actifs, et les calculs d’amortissement s’ajusteront automatiquement.

Cliquez sur l’image pour l’agrandir

Amortissement linéaire

.

Formule d’amortissement linéaire

Décortiquons maintenant la formule qui sert à amortir convenablement les actifs, par catégorie, soit:

=SUM(H10:OFFSET(H10,0,-MIN(H$8,$E10-IF(H$8>$E10,1,0))))/$E10 ou SOMME(H10:DECALER(H10;0;-MIN(H$8;$E10-SI(H$8>$E10;1,0))))/$E10

Résolvons d’abord:

A. IF(H$8>$E10,1,0) ou SI(H$8>$E10;1;0)

Cette formule demande à Excel de

  • renvoyer la valeur 1 si le numéro de la colonne (inscrit à la ligne 8) est supérieur au nombre de mois à amortir
  • renvoyer 0 dans le cas contraire

Ainsi, pour tout numéro de colonne supérieur au nombre total de mois à amortir, nous obtenons 1, pour les autres, on obtient 0.

B. MIN(H$8,$E10-IF(H$8>$E10,1,0)) ou MIN(H$8;$E10-SI(H$8>$E10;1;0))

Cette formule demande à Excel de prendre le minimum entre:

  • le numéro de la colonne (inscrit à la ligne 8)
  • et la différence entre le nombre total de mois à amortir et le résultat obtenu en A

Autrement dit, pour les actifs de type 1:

Si je suis dans la colonne 1 (janvier 2013), Excel procède comme suit:

A – Le numéro de la colonne (1) n’est pas supérieur au nombre de mois à amortir (4). Le résultat est donc 0.

B – Minimum entre 1 (numéro de la colonne) et 4 (nombre total de mois à amortir) -0 (résultat obtenu en A) = 1.

Si je suis dans la colonne 5 (mai 2013), Excel procède comme suit:

A – Le numéro de la colonne (5) est supérieur au nombre de mois à amortir (4). Le résultat est donc 1.

B – Minimum entre 5 (numéro de la colonne) et 4 (nombre total de mois à amortir) – 1 (résultat obtenu en A) = 3.

C. OFFSET(H10,0,-MIN(H$8,$E10-IF(H$8>$E10,1,0))) ou DECALER(H10;0;-MIN(H$8;$E10-IF(H$8>$E10;1;0)))

Cette formule demande à Excel:

  • de partir de la cellule où se trouve l’investissement en CAPEX de la période
  • de demeurer sur la même ligne, soit la ligne 10
  • de se déplacer vers la gauche du résultat trouvé ci-dessus, soit 1 pour janvier 2013 et 3 pour mai 2013

D. SUM(H10:OFFSET(H10,0,-MIN(H$8,$E10-IF(H$8>$E10,1,0))))/$E10 ou SOMME(H10:DECALER(H10;0;-MIN(H$8;$E10-SI(H$8>$E10;1,0))))/$E10

Cette formule demande à Excel:

  • De faire la somme à partir de H10 jusqu’à la cellule qui correspond à x cellules vers la droite (x correspond à la réponse trouvée en C)
  • De diviser le résultat par le nombre de mois à amortir

Autrement dit, Excel fait la somme des CAPEX de la période en question et des x périodes précédentes, x étant tout au plus égal au nombre de mois d’amortissement, et divise le tout par le nombre de mois d’amortissement.

.

Vous voulez apprendre d’autres trucs comme celui-là?

Je vous invite à participer à ma formation Excel – Modélisation financière (niveau 1)..

Formations ExcelCalendrier de formations

Pour en savoir davantage sur nos formations ou pour vous inscrire, vous pouvez consulter notre calendrier de formation.

Sophie Marchand, M.Sc., CPA, CGA, MVP
514-605-7112
Experte en modélisation financière et intelligence d’affaires

 

Vous avez des questions?

Posez toutes vos questions sur le forum du CFO masqué.

Tagués avec : , , , , ,
Publié dans Excel, Modèles financiers, Tous les articles
Un commentaire pour “Modélisation financière dans Excel: Cédule d’amortissement linéaire des actifs
1 Pings/Trackbacks pour "Modélisation financière dans Excel: Cédule d’amortissement linéaire des actifs"
  1. […] Élaborer une cédule d'amortissement des actifs peut sembler beaucoup plus simple que ce ne l'est en réalité. En effet, il faut non seulement amortir la base d'actifs existants par catégorie d'actifs mais il faut également amortir les nouveaux actifs. Pour une entreprise qui fait des investissements intensifs en CAPEX, le maintien de la cédule d'amortissement des actifs n'est pas toujours évident, surtout lorsque cette cédule d'amortissement des actifs, fait partie d'un modèle financier, qui, par définition, doit se mettre à jour automatiquement lorsqu'il y a des changements à ce niveau. Bref, je vous présente ci-bas une formule fort utile pour calculer adéquatement l'amortissement linéaire. Si ce truc vous intéresse, je vous invite à découvrir ma formation sur les Meilleures pratiques d'affaires en modélisation financière, où je couvre notamment ce sujet et encore bien d'autres.  […]

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

*


*

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Recevoir les articles par courriel
Abonnement Le CFO masqué
Gagnez une formation en ligne
Concours
Tableau de bord gratuit à l’inscription
Tableau de bord gratuit à l’inscription
Présentation à télécharger
Outils indispensables aux CFOs
Boutique en ligne
Microsoft Most Valuable Professional
Formations Excel
Formations Excel
Activités à venir
  1. Excel – Tableaux de bord (niveau 1)

    septembre 23 @ 9 h 00 min - 16 h 00 min
  2. Excel – Modélisation financière (niveau 1)

    septembre 30 @ 9 h 00 min - 16 h 00 min