Tratto dallo speciale:

Calcolare e comparare il costo di esercizio di un automobile con Excel

di Gianluca Tinti

scritto il

Come calcolare con Excel il costo di esercizio di più modelli di automobile al fine di compararli per un acquisto oculato dell'auto aziendale.

Per calcolare il costo di esercizio di un automobile e compararlo a quello di altri modelli, è possibile utilizzare Microsoft Excel: vediamo come ottenere una guida ragionata all’acquisto di un auto aziendale, riuscendo a definire il modello più conveniente e la sua incidenza sul bilancio aziendale.

=>Scarica L’Excel precompilato

Il modello di calcolo

In questo tutorial migreremo in Excel la formula matematica per il calcolo del costo di esercizio di un automobile, per confrontare i costi di modelli diversi e rendere più semplice la scelta:  ((ASS+BOL)/KM) + ((CCA*CON)/100) + ((MORD+MSTR)/100000) + ((PRZ-VRES)/(KM*T)) + (((1+INT)^(T-1))*INT*PRZ/KM)

Dove:  Sta per:
 ASS  Costi assicurativi dell’automobile
 BOL  Costo tassa di proprietà
 KM  Costo tassa di proprietà
 CCA  Prezzo del carburante in base  all’alimentazione
 CON  Consumo di carburante in litri per 100 km  per percorso “misto”
 MORD  Costi di manutenzione ordinaria (tagliandi nei primi 100.000 km percorsi)
 MSTR  Costi di manutenzione straordinaria per parti meccaniche (pastiglie freni, frizione, ammortizzatori, pneumatici)  in 100.000 km di percorrenza
 PRZ  Costo di acquisto “chiavi in mano”
 VRES  Valore residuo dell’automobile previsto dopo T anni
 T  Tempo di utilizzo previsto in anni
 INT  Tasso di interesse percentuale sul finanziamento acceso per l’acquisto dell’automobile

Ambiente di lavoro in Excel

Dopo aver aperto una nuova cartella di lavoro, rinominiamo tre fogli rispettivamente come AUTO, CKM e GRAFICI.

Nel foglio AUTO riproduciamo la seguente tabella (formattazione di esempio):

Nel foglio CKM riproduciamo questa tabella:

Nel foglio GRAFICI riproduciamo questa tabella:

Automatizzazione Foglio AUTO

Il Foglio AUTO è costituito da due mini-tabelle, nelle quali dovranno essere riportate le informazioni sulle automobili da confrontare. In ciascuna delle due, occorre calcolare in basso il valore residuo delle auto usate, ovvero il loro valore dopo un determinato periodo di tempo di utilizzo. Per calcolare la valutazione di mercato, possiamo assumere, come dato puramente orientativo, che :

  • dopo 1 anno si svaluta del 25% e vale il 75% di quanto pagato;
  • dopo 2 anni si svaluta del 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%;
  • dopo 5 anni di un altro 7,5% e vale il 35%;

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

Scriviamo ora, nella celle A1 ed F1, i nomi delle vetture e nelle celle B2 e G2 il loro costo. A questo punto, se avete impostato correttamente le formule precedenti, verrà calcolato automaticamente il valore residuo. Nelle celle comprese tra B3 e B9 e in quelle comprese tra G3 e G9 scrivete i costi previsti per le due vetture oggetto del confronto, rifacendovi alle informazioni contenute nella tabella1 posta all’inizio di questo tutorial. Al fine di dare la giusta interpretazione ai dati da scrivere, selezioniamo le celle E9 e G9, dopodiché clicchiamo col tasto destro su una delle due e (dal menù contestuale) selezioniamo “Formato celle”. Nella scheda “numero” della finestra omonima scegliete “Percentuale” e clicchiamo su OK. Allo stesso modo alle altre celle (eccezion fatta per B6 e G6) associamo il formato Valuta.

Foglio CKM

Onde evitare confusione riportiamo i nomi delle auto (trascritte nel primo foglio) nelle giuste colonnine della tabella. Per cui nelle cella D1 scriviamo: =AUTO!A1. Nella cella E1 scriviamo: =AUTO!F1. Per chiarezza, diciamo che nella colonna B della tabella sono trascritti i km annui che si prevede di percorrere, nella colonna C invece è riportato il tempo di utilizzo dell’auto (espresso in anni). Utilizzando la formula vista all’inizio di questo tutorial (alla luce del contenuto del foglio AUTO) calcoliamo quanto ci viene a costare a chilometro la nostra auto. I tanti dati che otterremo verranno successivamente riassunti da dei grafici (molto più semplici da leggere e da interpretare). A questo scopo, nella cella D2 ricopiamo: =((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)

Nella cella E2 ricopiamo: =((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 trasciniamole fino alla riga 7. Qui le due formule ci 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)

Sostituite le parti in rosso rispettivamente con C13 e H13, dopodiché col riempimento trascinate fino alla riga 12, dove eseguiremo la stessa sostituzione. In seguito trascinate prima alla riga 17 (… e di nuovo la sostituzione) e poi alla riga 22 dove per l’ultima volta dovrete sostituire i valori sopraindicati. Completate trascinando fino alla riga 26. Nelle ultime cinque righe della tabella invece calcoliamo la media del costo di esercizio delle auto nell’arco dei cinque anni. A tale scopo nella cella D27 scriviamo: =MEDIA(D2;D7;D12;D17;D22)

E nella cella E27 scriviamo: =MEDIA(E2;E7;E12;E17;E22)

Successivamente dopo aver selezionato le due celle trasciniamole fino alla riga 31 col quadratino di riempimento (come già visto in precedenza). Per chiarezza, è possibile associare il simbolo dell’Euro a tutti questi valori impostando un formato di tipo Valuta.

Automatizzare il foglio CKM

Nell’ultimo foglio di lavoro imposteremo una piccola tabella che ci consentirà di visualizzare una serie di grafici riepilogativi dei dati presenti nella tabella CKM. Come prima operazione creiamo un menù a tendina che ci consentirà di scegliere i km che intendiamo percorrere con le auto, dopodiché in base alla scelta effettuata verranno visualizzati i grafici riepilogativi con i costi. Di seguito sono elencate una serie di operazioni che consentiranno di variare il contenuto della tabella a seconda del tipo di grafico che intendiamo visualizzare. Posizioniamoci nella cella B2, attiviamo la barra multifunzione DATI e clicchiamo il pulsante “convalida dati”.

A questo punto utilizzate 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 nostro menu. Appena date l’OK, nella cella B2 avrete la possibilità di usare l’elenco.

Facciamo ora in modo, qualora non venga operata alcuna scelta, di visualizzare un grafico delle spese medie di gestione delle macchine. A tale scopo scriviamo su B3: =SE(B2=0;”Confronto”;””). Nella cella D2 richiamiamo i valori dei costi relativi al chilometraggio selezionato nel menu della cella B2. A tale scopo scriviamo:
=SE($B$2=0;CKM!D27;CERCA.VERT($A2;CKM!$A$2:$E$26;4;FALSO))

Allo stesso modo nella cella E2 scriviamo:
=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), trascinate il quadratino di riempimento fino alla riga6.
Anche qui, per maggiore chiarezza associamo ai valori ottenuti un formato di tipo Valuta.

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

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

Ricopiamo ora la formula appena scritta fino alla cella A6.

Quest’ultima operazione è molto importante, perché consente di visualizzare dei codici (apparentemente incomprensibili) attraverso i quali vengono rintracciati i dati che saranno oggetto del grafico da rappresentare. Proprio per non generare confusione con degli elementi non utilizzabili direttamente dall’utente, nascondiamo la colonna A cliccandoci sopra col tasto destro. Per una migliore comprensibilità nella cella D1 scriviamo =AUTO!A1. Nella cella E1 invece =AUTO!F1. In questo modo riportiamo al posto giusto i nomi delle vetture oggetto del confronto.

Impostare i grafici

A tale scopo selezioniamo le celle comprese tra D1 ed E6, dopodiché attiviamo la barra multifunzione “INSERISCI” e clicchiamo sul pulsante “grafico a linee”.

In alternativa anche gli istogrammi costituiscono una buona scelta. Posizionate il grafico sulla parte destra in modo che non si sovrapponga alla tabella e dimensionatelo a piacere. Adesso nel foglio “GRAFICI” all’interno della cella B2 selezioniamo 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.

I Video di PMI

Incentivi auto ed ecotassa 2019-2021