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

Per calcolare il costo di esercizio di un automobile e compararlo a quello di altri modelli, è possibile utilizzare un foglio di calcolo come Microsoft Excel.

Di seguito forniamo, oltre ad un modello di calcolo già pronto, una guida ragionata all’acquisto di un auto aziendale, riuscendo a definire il modello più conveniente e la sua incidenza sul bilancio d’impresa.

=>Costo auto: modello di calcolo

Il modello di calcolo costi acquisto auto

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)

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.

a cura di Gianluca Tinti