Come calcolare il costo operativo di un’auto con Excel

Ci sono molti modi per calcolare il costo operativo di un’auto in modo che un fleet manager possa valutare se una vettura è più conveniente di un’altra. O per calcolare quanto incida il parco veicoli sugli utili aziendali.

Uno dei modi più facili per farlo è tramite un foglio di calcolo di Microsoft Excel. Ossia una tecnologia già in uso da diversi anni nelle piccole, nelle medie o nelle grandi aziende.

Una piccola legenda:
– CAA sta per Costi Assicurativi dell’auto
– BOL sta per bollo (tassa di proprietà)
– PCA sta per prezzo del carburante
– CON sta per Consumo di carburante per 100 km
– CMO sta per costi di manutenzione ordinaria
– CMS sta per costi di manutenzione straordinaria
– PDA sta per prezzo di acquisto
– VRA sta per valore residuo dell’auto
– TEM sta per tempo di utilizzo in anni
– INT sta per tasso di interesse per l’acquisto dell’auto
– KM sta per costo tassa di proprietà

 ((CAA+BOL)/KM) + ((PCA*CON)/100) + ((CMO+CMS)/100000) + ((PDA-VRA)/(KM*TEM)) + (((1+INT)^(T-1))*INT*PDA/KM)

Costo operativo di un’auto in ambiente di lavoro in Excel

Per calcolare il costo operativo di un’auto, occorre prima aprire una nuova cartella di lavoro con 3 fogli. Questi si possono chiamare AUTO, CKM e GRAFICI.

Nel primo foglio (AUTO) si riproduce la seguente tabella.

Il Foglio AUTO è costituito da due mini-tabelle. In queste bisogna inserire i dati delle vetture da confrontare. In ciascuna, nella parte bassa, va calcolato il valore residuo delle auto usate dopo un periodo di utilizzo. Per calcolare la valutazione di mercato, si può fare questo semplice calcolo orientativo.

• Dopo 1 anno si svaluta del 25% e vale il 75% di quanto pagato.
• 2 anni dopo l’acquisto si svaluta di un ulteriore 15% e vale il 60%.
• Dopo 3 anni si svaluta del 10% e vale il 50%.
• Dopo 4 anni si svaluta del 7,5% e vale il 42,5%.
• 5 anni dopo l’acquisto perde un altro 7,5% e arriva a valere il 35%.

Cosa scrivere nelle celle del foglio AUTO

Attenendoci a queste informazioni, nella cella C13 del foglio AUTO si imposta la questa formula: =$B$2*D13.  Nella cella H13 quest’altra formula: =$G$2*I13. Bisogna utilizzare il quadratino di riempimento per copiare le formule rispettivamente fino alla cella C17 e H17.

Nelle celle A1 ed F1 bisogna scrivere i nomi delle vetture mentre nelle celle B2 e G2 il loro costo. Se tutto è corretto, verrà calcolato automaticamente il valore residuo dei veicoli. Nelle celle comprese tra B3 e B9 e in quelle comprese tra G3 e G9 bisogna inserire i costi previsti per le vetture oggetto del confronto in base alle informazioni contenute nella legenda.

Bisogna poi selezionare le celle E9 e G9 e cliccare col tasto destro su una delle due e (dal menù contestuale) selezionare “Formato celle”. Nella scheda “numero” della finestra omonima scegliere “Percentuale” e cliccare su OK. Allo stesso modo alle altre celle (eccezion fatta per B6 e G6) associamo il formato Valuta.

Nel secondo foglio (CKM) ci va questa tabella

Calcolo del costo operativo di un’auto: il foglio CKM

Onde evitare confusione occorre riportare i modelli delle auto (trascritte nel primo foglio) nelle giuste colonnine della tabella. Per cui nelle cella D1 va scritto: =AUTO!A1. Nella cella E1: =AUTO!F1.

Nella colonna B della tabella sono trascritti i km annui che si prevede di percorrere. Nella colonna C invece sono riportati gli anni di utilizzo dell’auto. Utilizzando la formula vista all’inizio (alla luce del contenuto del foglio AUTO) si calcola quanto venga a costare a chilometro un’auto.

I dati che si ottengono verranno successivamente riassunti da dei grafici (molto più semplici da leggere e da interpretare). A questo scopo, nella cella D2 va inserito: =((AUTO!$B$3+AUTO!$B$4)/$B2)+((AUTO!$B$5*AUTO!$B$6)/100)+((AUTO!$B$7+AUTO!$B$8)/100000)+((AUTO!$B$2-AUTO!$C13)/$B2*$C2)+(((1+AUTO!$B$9)^($C2-1))*AUTO!$B$9*AUTO!$B$2/$B2)

Cosa scrivere nelle celle del foglio CKM per calcolare il costo operativo di un’auto

Nella cella E2: =((AUTO!$G$3+AUTO!$G$4)/$B2)+((AUTO!$G$5*AUTO!$G$6)/100)+((AUTO!$G$7+AUTO!$G$8)/100000)+((AUTO!$G$2-AUTO!$H13)/$B2*$C2)+(((1+AUTO!$G$9)^($C2-1))*AUTO!$G$9*AUTO!$G$2/$B2)

Dopo aver selezionato le celle comprese fra D2 ed E2, col puntino di riempimento occorre trascinarle fino alla riga 7. Qui le due formule appariranno così:

=((AUTO!$B$3+AUTO!$B$4)/$B7)+((AUTO!$B$5*AUTO!$B$6)/100)+((AUTO!$B$7+AUTO!$B$8)/100000)+((AUTO!$B$2-AUTO!$C18)/$B7*$C7)+(((1+AUTO!$B$9)^($C7-1))*AUTO!$B$9*AUTO!$B$2/$B7)

=((AUTO!$G$3+AUTO!$G$4)/$B7)+((AUTO!$G$5*AUTO!$G$6)/100)+((AUTO!$G$7+AUTO!$G$8)/100000)+((AUTO!$G$2-AUTO!$H18)/$B7*$C7)+(((1+AUTO!$G$9)^($C7-1))*AUTO!$G$9*AUTO!$G$2/$B7)

Sostituire le parti in rosso con C13 e H13. Dopodiché, col riempimento, trascinare fino alla riga 12, dove bisogna eseguire la stessa sostituzione.

In seguito trascinare prima alla riga 17 (… e di nuovo la sostituzione) e poi alla riga 22 dove per l’ultima volta si dovrà sostituire i valori sopraindicati. Completare trascinando fino alla riga 26. Nelle ultime cinque righe della tabella invece si calcola la media del costo di esercizio delle auto nell’arco di cinque anni. A tale scopo nella cella D27 bisogna scrivere: =MEDIA(D2;D7;D12;D17;D22)

Nella cella E27: =MEDIA(E2;E7;E12;E17;E22)

Successivamente, dopo aver selezionato le due celle, occorre trascinarle fino alla riga 31 col quadratino di riempimento.

Automatizzare il foglio CKM

Nell’ultimo foglio di lavoro si deve impostare una piccola tabella che consentirà di visualizzare una serie di grafici riepilogativi dei dati presenti nella tabella CKM. Come prima operazione si crea un menù a tendina che consentirà di scegliere i km si presume percorrere con le auto. Dopodiché, in base alla scelta effettuata, verranno visualizzati i grafici riepilogativi con i costi.

Di seguito l’elenco di una serie di operazioni che consentono di variare il contenuto della tabella a seconda del tipo di grafico che si vuole visualizzare.

Per finire: bisogna posizionarsi nella cella B2, attivare la barra multifunzione DATI e cliccare sul pulsante “convalida dati”.

A questo punto utilizzare la finestra omonima per impostare il menu “consenti” a “Elenco” e per scrivere nello spazio “origine”: “10000;20000;30000;40000;50000”, ovvero le opzioni di scelta del menu.

Appena dato l’OK, nella cella B2 si avrà la possibilità di usare l’elenco.

Come visualizzare un grafico delle spese medie

Ora bisogna fare in modo, qualora non venga operata alcuna scelta, di visualizzare un grafico delle spese medie di gestione delle macchine.

A tale scopo si scrive su B3: =SE(B2=0;”Confronto”;””). Nella cella D2 si richiamano i valori dei costi relativi al chilometraggio selezionato nel menu della cella B2. A tale scopo occorre scrivere:
=SE($B$2=0;CKM!D27;CERCA.VERT($A2;CKM!$A$2:$E$26;4;FALSO))

Allo stesso modo nella cella E2:
=SE($B$2=0;CKM!D27;CERCA.VERT($A2;CKM!$A$2:$E$26;5;FALSO))

Al termine, dopo aver selezionato le due celle (D2:E2), trascinare il quadratino di riempimento fino alla riga6.
Anche qui, associare ai valori ottenuti un formato di tipo Valuta.

Nella cella A2, a completamento di quanto fatto:
=SE($B$2=0;””;$B$2+1)

Nella cella A3:
=SE($B$2=0;””;A2+1)

Fino alla cella A6 bisogna ricopiare la formula appena scritta.

Quest’ultima operazione è molto importante, perché consente di visualizzare dei codici attraverso i quali rintracciare i dati che saranno oggetto del grafico da rappresentare.

Proprio per non generare confusione con degli elementi non utilizzabili direttamente dall’utente, va nascosta la colonna A cliccandoci sopra col tasto destro. Per una migliore comprensibilità nella cella D1, va scritto =AUTO!A1. Nella cella E1 invece =AUTO!F1. In questo modo si riportano al posto giusto i nomi delle vetture oggetto del confronto.

Nel foglio Grafici va inserita quest’altra tabella

(Scopri i costi delle auto Euro 7)

Come impostare i grafici

A tale scopo occorre selezionare le celle comprese tra D1 ed E6, attivare la barra multifunzione “INSERISCI” e cliccare sul pulsante “grafico a linee”.

In alternativa anche gli istogrammi costituiscono una buona scelta. Posizionare il grafico sulla parte destra in modo che non si sovrapponga alla tabella e dimensionarlo a piacere. Adesso nel foglio “GRAFICI” all’interno della cella B2 selezionare i km annui di percorrenza previsti.

Il grafico cambierà a seconda dei dati visualizzati. Invece lasciando vuota la cella B2 il grafico visualizzerà i costo medio delle autovetture nell’arco di 5 anni di utilizzo.

(Scopri l’aumento dei prezzi delle auto nel giro di 20 anni).