Contact: 514-605-7112 / info@lecfomasque.com

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

É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)..


NOTRE CATALOGUE DE FORMATIONS


Catalogue de formations

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Pour info: 514-605-7112 ou info@lecfomasque.com

Téléchargez notre catalogue

Sophie Marchand, M.Sc., CPA, CGA, MVP

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA et d’un titre MVP (Most valuable professional) Excel de Microsoft, 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 consultante, formatrice et conférencière.

  • répondre Modélisation financière dans Exce... ,

    […] É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.  […]

    Écrire un commentaire


    • *