É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 créer adéquatement une cédule d’amortissement linéaire des actifs.
Cédule d’amortissement linéaire des 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
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à?
Découvrez nos formations en modélisation financière dans Excel.
Ping : Modélisation financière dans Exce...