Editions ENI Livres | Vidéos | e-Formations
Gestion des immobilisations
Descriptif du projet
Notre but est de créer un outil pour gérer les immobilisations de l’entreprise.
Nous construirons ce projet de deux manières : en totalité sans macro, mais également en totalité avec macro.
Gestion des immobilisations sans macro
Nous devons procéder au suivi des immobilisations et calculer rapidement le montant de l’amortissement pour chaque exercice comptable.
Pour ce faire, dans un classeur, nous allons créer une feuille qui sera le modèle à utiliser dans le cas d’un amortissement en linéaire et une autre feuille qui sera le modèle à utiliser dans le cas d’un amortissement en dégressif. Nous y calculerons :
-
Le taux selon la durée et le type d’amortissement.
-
Le plan d’amortissement adapté.
Pour passer rapidement d’une immobilisation à une autre, nous créerons une page d’accueil qui renverra automatiquement à la feuille désirée.
Enfin, nous déterminerons automatiquement les dotations aux amortissements de l’exercice comptable.
Gestion des immobilisations avec macro
Dans cette partie, nous développerons le programme complet de la gestion des immobilisations qui se présentera comme suit :
Saisie des données dans un formulaire avec possibilité d’annuler ou de valider la saisie.
La validation des données entraînera...
Rappel des calculs d’amortissements
Chaque entreprise se doit de tenir un registre qui devra lister toutes les immobilisations et calculer un plan d’amortissement pour chacune d’entre elles, en indiquant, entre autres, la date d’achat, la durée, le type d’amortissement ainsi que la date et le motif de sa sortie du patrimoine de l’entreprise.
Le plan d’amortissement est fonction de la durée probable de vie d’une immobilisation et du type d’amortissement concerné. Par mesure de simplification, et seulement pour certaines PME, il est possible de se baser sur les durées proposées par l’administration fiscale, dont voici quelques indications :
-
Constructions : 20 à 50 ans.
-
Installations techniques : 5 à 10 ans.
-
Agencements, aménagements : 10 à 20 ans.
-
Matériel de transport : 4 à 5 ans.
-
Matériel de bureau, mobilier : 5 à 10 ans.
-
Ordinateurs : 3 à 5 ans.
Nous nous limiterons, dans notre projet, aux deux types d’amortissements les plus utilisés : le linéaire (amortissement comptable) et le dégressif (amortissement fiscal).
L’amortissement linéaire
Calcul du taux
Le taux est déterminé à partir de la durée en divisant 1 par cette dernière.
Pour une immobilisation à amortir sur 5 ans le taux sera calculé en divisant 1 par la durée soit par exemple 1/5 donne 0.2 soit 20%.
Montant de l’amortissement
L’amortissement linéaire se caractérise par sa valeur constante (même montant chaque année). Il est fonction du montant amortissable et du taux. Il se détermine par la multiplication de ces deux éléments.
Pour une immobilisation dont la valeur à amortir est de 15000 € amortissable au taux de 20%, le montant de l’amortissement...
Les fonctions financières et leur utilité pour notre projet
Les fonctions intégrées d’Excel de la catégorie Financier prévoient le calcul des amortissements. Cependant, elles ne sont pas adaptées à tous les cas et peuvent mêmes, pour certaines, renvoyer un résultat incorrect.
AMORLIN
La fonction AMORLIN renvoie le montant de l’amortissement linéaire pour une valeur donnée. Sa syntaxe se présente comme suit :
AMORLIN(valeur_à_amortir;valeur_résiduelle;durée)
En reprenant l’exemple de notre immobilisation de 15000 € à amortir sur 5 ans, nous aurions :
AMORLIN(15000;0;5) avec pour résultat 3000, ce qui est tout à fait exact.
Cette fonction est-elle pour autant intéressante dans notre projet ? Non car nous voulons bâtir un plan d’amortissement précis et que cette fonction n’apporte qu’une information globale. D’autre part, elle n’est pas plus simple que de faire 15000/5.
Cette fonction devient intéressante si le bien à amortir doit avoir une valeur résiduelle supérieure à zéro à la fin de l’amortissement et que le but est, par exemple, d’évaluer l’intérêt d’un investissement.
AMORLINC
Voici la définition de la fonction extraite de l’aide en ligne d’Excel :
"Renvoie...
Les fonctions utiles à notre projet
Vous l’avez compris, les fonctions financières n’étant pas très adaptées à nos calculs, nous allons arriver à nos fins par divers autres moyens.
Dans cette section, nous allons vous présenter les différentes fonctions qui vous seront utiles.
DATE
Date(Année;Mois;Jour)
Date(2012;04;28) donnera 28/04/2012.
Cette fonction nous sera utile pour calculer les dates de fin d’exercice dans le plan d’amortissement.
JOURS360
JOURS360(date_début;date_fin;méthode)
Date_début : date de début de l’amortissement.
Date_fin : date de fin de la première période comptable.
Méthode : argument facultatif, il a pour valeur Vrai ou Faux. Faux ou omis correspond à la méthode américaine, et Vrai à la méthode européenne.
En supposant que A2 contienne 12/03/2012 et A3 contienne 30/09/2012 : JOURS360(A2;A3) donne 198.
Cette fonction déterminera le nombre de jours entre deux dates afin de calculer le prorata de la première annuité et de la dernière annuité pour le linéaire.
LIGNE
Ligne(réf) ou ligne()
Cette fonction renvoie le numéro de ligne de la cellule indiquée entre parenthèses, ou le numéro de la cellule où elle est saisie.
Ligne(A10) renvoie 10 ; si en B250 vous saisissez =ligne(), vous obtenez...
Les commandes utiles à notre projet
La mise en forme conditionnelle
Dans le plan d’amortissement, nous avons prévu la recopie des formules sur 21 lignes pour gérer une durée de vie allant jusqu’à 20 ans.
Nous utiliserons la mise en forme conditionnelle pour éviter que les bordures n’apparaissent autour des cellules vides. Le quadrillage sera ainsi limité aux lignes nécessaires au plan.
Les liens hypertextes
Notre objectif est de centraliser nos immobilisations dans un classeur, à raison d’un bien par feuille, puis de les lister dans une autre feuille.
Nous affecterons un lien à chaque bien afin d’atteindre directement l’immobilisation désirée.
Nous créerons également un lien pour revenir de n’importe quelle feuille à la liste générale.
Le tableau croisé dynamique avec étiquettes (TCD)
Cet outil nous sera utile pour obtenir rapidement le montant total des dotations en fin d’exercice.
À chaque nouvelle immobilisation, les données sources devront être modifiées.
(le TCD est étudié de manière très détaillée dans le chapitre Suivi des recettes journalières.)
Les macros
Nous nous attacherons dans cette partie à créer plusieurs procédures indépendantes qui seront ensuite appelées à partir d’une procédure globale. Nous écrirons directement le code dans chaque macro.
Nous utiliserons aussi bien des outils qui ont déjà été décrits dans les chapitres précédents, que de nouveaux outils.
Nous reverrons les instructions If... Then... Else, While... Wend, Select case et nous découvrirons de nouvelles instructions comme For... Next, Call, etc.
Les macros seront ensuite affectées à des boutons qui seront soit insérés dans une feuille soit intégrés dans un nouvel onglet du ruban.
Gestion des immobilisations pas à pas sans macro
Création du plan d’amortissement linéaire
Nous allons commencer par construire une fiche d’immobilisation avec un plan d’amortissement basé sur le type linéaire. Pour simplifier, nous partons du principe que les exercices comptables se terminent systématiquement le 31/12 et qu’aucun achat n’a lieu le 1er janvier.
Dans un nouveau classeur, renommez Feuil1 par Linéaire ou bien, si vous préférez, ouvrez le fichier immobilisation_debat.xlsx.
Reproduisez les éléments suivants dans la feuille Linéaire :
Cliquez en C9 (Date début amortissement), saisissez =C6 pour que la date de mise en service soit reportée automatiquement.
Vous obtenez 0, car la cellule C6 ne contient rien.
Modifiez votre formule de manière à ne rien afficher si la date de mise en service n’est pas indiquée, cela donne : =SI(C6="";"";C6)
Inscrivez 25/04/2018 en C6 puis appliquez un format Date à la cellule C9 si vous obtenez 43215.
Cliquez en E10 (taux), saisissez =SI(C10="";"";1/C10) ; C10 correspond à la durée.
Appliquez ensuite le format pourcentage et ajoutez deux décimales à la cellule E10.
Saisissez 5 comme durée en C10: vous obtenez 20.00% en cellule E10.
Saisissez 15000 en C7 (montant à amortir).
Nous allons maintenant nous lancer dans la construction du plan d’amortissement. Nos formules seront saisies dans la première ligne du plan puis recopiées sur les autres lignes. Elles devront en conséquence tenir compte des paramètres propres à chaque ligne.
Cliquez en A16 puis saisissez la formule suivante :
=SI(OU($C$7="";$C$9="";$C$10="");"";SI(LIGNE()=16;DATE(ANNEE($C$9);12;31); SI(LIGNE()-16>$C$10;"";DATE(ANNEE(A15)+1;12;31))))
Vous obtenez 43465 et, une fois le format date appliqué, vous avez 31/12/2018.
Nous allons expliquer en détail le raisonnement et la construction de cette fonction.
Rappelons les objectifs de cette formule :
-
Afficher la date de fin d’exercice sur autant de lignes que nécessaire par rapport à la durée de vie de l’immobilisation. Ce qui signifie que dès que le numéro...
Gestion des immobilisations avec macros
Dans cette section, la gestion des immobilisations sera effectuée en quasi-totalité par programmation.
À partir d’une feuille qui servira de formulaire, nous saisirons les données d’une nouvelle immobilisation. Ces données seront ensuite validées à l’aide d’un bouton auquel sera affectée une macro qui devra :
-
Créer une feuille dédiée à l’immobilisation et calculer le plan d’amortissement correspondant.
-
Ajouter les données saisies dans la liste récapitulative des immobilisations.
Pour gagner un peu de temps nous avons créé les feuilles nécessaires à l’application dans le fichier Immo avec macro début.xlsx.
Construction du formulaire
Ouvrez le fichier Immo avec macro début.xlsx puis activez la feuille Formulaire.
Le formulaire est préparé en partie, il nous reste à créer :
-
Une liste déroulante pour le choix du type d’amortissement.
-
Une zone de saisie du coefficient de majoration.
-
Un bouton pour valider la création.
-
Un bouton pour annuler la saisie.
Validation des données et mise en forme conditionnelle
Pour chaque immobilisation, nous aurons à choisir le type d’amortissement à partir d’une liste déroulante.
En colonne H, nous avons préparé les données devant faire partie de la liste.
Sélectionnez la cellule B15.
Onglet Données - groupe Outils de données- bouton Validation des données
Renseignez la boîte de dialogue comme sur l’écran qui suit :
La zone Source indique la plage de cellules qui contient les éléments de la liste.
Validez par OK.
Cliquez sur le bouton liste qui s’affiche.
Nous allons, grâce à la mise en forme conditionnelle, afficher automatiquement une zone pour saisir le coefficient si le type Dégressif a été sélectionné.
Cliquez en C15, tapez Coef : puis alignez le texte à droite.
Sélectionnez les cellules C15 et D15 puis appliquez-leur la couleur de police blanche.
Sélectionnez la cellule C15, cliquez sur le bouton Mise en forme conditionnelle de l’onglet Accueil puis cliquez sur Nouvelle...
