Per realizzare prospetti quantitativi ad elevata automazione è necessario progettare adeguatamente i flussi di dati che dovranno essere elaborati da Excel; tale progettazione richiede, spesso, uno sforzo aggiuntivo quando si devono eseguire calcoli che fanno riferimento a periodi temporali. In questo tutorial proponiamo uno scadenzario per beni in leasing di tipo dinamico (ovvero che viene generato in maniera automatica dopo l’inserimento di pochi dati) per focalizzare l’attenzione su alcune procedure che potranno essere utilizzate anche nella realizzazione di altri prospetti aziendali.
Scarica l’esempio
Come utilizzare il modello
Per utilizzare il modello è sufficiente inserire i dati relativi al bene in leasing; in particolare, nella cella (Data Contratto) va inserita la data del pagamento della prima rata, nella cella B5 il numero di rate complessive, nella cella B6 il numero di rate in un anno (occorre inserire 2 se si tratta di rate semestrali, 3 se si tratta di rate quadrimestrali, 4 se si tratta di rate trimestrali, 6 se si tratta di rate bimestrali, 12 se si tratta di rate mensili) e nella cella B7 l’importo del canone leasing da pagare alle singole scadenze; infine, la cella B3 può essere utilizzata per una breve descrizione del bene acquisito in leasing.
Dopo l’inserimento dei dati vengono generate in automatico le righe della tabella sottostante al box dati formata da sei colonne che riportano, rispettivamente:
- numero della rata (Colonna A)
- scadenza, ovvero la data di pagamento delle singole rate (Colonna B)
- l’esercizio contabile di riferimento in corrispondenza della rata “a cavallo” tra due esercizi (Colonna C)
- importo dei singoli canoni, da usare nel conto CANONI LEASING in Partita Doppia (Colonna D)
- importo di competenza economica dei singoli canoni (Colonna E)
- risconto attivo (Colonna F) da contabilizzare in Partita Doppia per stornare la parte di costo di competenza del futuro esercizio contabile, poiché le rate dei singoli canoni leasing vengono pagate anticipatamente.
=> Leasing: più rapida la deducibilità fiscale
Come si può osservare dai dati riportati nell’esempio, il macchinario acquisito in leasing prevede il pagamento di dodici canoni trimestrali (desumibile dalla cella B6) dell’importo unitario di 2.000 € poiché la data di pagamento del primo canone è il primo di dicembre, viene generato, nell’ultima cella della prima riga, il risconto attivo (1.311 €) che dovrà essere contabilizzato in Partita Doppia per stornare la quota di costo del canone leasing registrato, per intero, il primo dicembre; infine, la tabella si estende fino alla riga 21 nella quale sono riportati i dati relativi all’ultima rata del contratto di leasing.
Come costruire il modello
Vediamo come costruire il modello ed, eventualmente, come personalizzarlo. Nella colonna A deve essere visualizzata una serie numerica pari al numero complessivo delle rate del canone leasing: la serie parte con il numero 0 che indica il canone iniziale e, pertanto, termina con n-1 (nell’esempio riproposto, la serie numerica termina con il numero 11 essendo le rate complessive).
=> Leasing per flotte aziendali: contratto e deducibilità
Nella cella A10 è stata utilizzata la formula:
=SE(O(B4=””;B5=””;B6=””;B7=””);””;0)
La funzione SE, coadiuvata dall’operatore logico O, consente di visualizzare nella cella il numero 0 solo se sono stati inseriti tutti i dati richiesti per generare il prospetto (intervallo B4:B7) in maniera da evitare la comparsa di messaggi di errore a prospetto vuoto.
Nella cella A11 è stata utilizzata la formula:
=SE(O($B$4=””;$B$5=””;$B$6=””;$B$7=””);””;SE($B$5-1>A10;A10+1;””))
La formula, composta da due funzioni SE nidificate, può essere letta in questo modo: SE una delle celle dell’intervallo B4:B7 è vuota RESTITUISCI una cella vuota, ALTRIMENTI SE il numero complessivo delle rate (posto in B5) meno 1 è maggiore del valore inserito nella cella A10, RESTITUISCI il valore della cella A10 aumentato di 1, ALTRIMENTI (ovvero, se abbiamo raggiunto il numero complessivo di rate -1) RESTITUISCI una cella vuota.
=> Come calcolare la rata di un prestito
La formula è stata costruita con dei riferimenti assoluti in maniera da poter essere copiata verso il basso per trascinamento (tutte le formule della tabella sono state inserite fino alla riga 50 del foglio di calcolo).
La colonna B deve visualizzare le date delle scadenze periodiche dei singoli canoni leasing; queste ultime variano in funzione del tipo di rata (semestrale, quadrimestrale, ..) utilizzata (cella B6) e devono arrestarsi in corrispondenza dell’ultima rata riportata nella colonna A.
Nella cella B10 è stata utilizzata la formula:
=SE(A10=””;””;B4)
che consente di trascrivere la data iniziale (richiamata dalla cella B4) solo se la cella A10 contiene un valore.
Nella cella B11 è stata utilizzata la formula:
=SE(A11=””;””;DATA(ANNO($B$4);MESE($B$4)+A11*12/$B$6;GIORNO($B$4)))
La funzione SE consente di evitare il calcolo in essa contenuto (la funzione , per intenderci) se la cella A11 è vuota e, pertanto, quando verrà trascinata nelle celle sottostanti, mostrerà delle date fino a quando esisterà un valore nella serie della colonna A.
Analizziamo brevemente la funzione DATA e le funzioni ANNO, MESE, GIORNO utilizzate all’interno della funzione SE.
La funzione DATA è composta da tre argomenti, rispettivamente, anno, mese e giorno, che vengono “riorganizzati” in formato data; ad esempio la funzione =DATA(2008;12;20) restituisce la data 20/12/2008.
=> Calcolare e comparare il costo di esercizio di un automobile con Excel
La funzione è particolarmente indicata nella realizzazione di serie di date in quanto permette di “traslare” automaticamente i valori dei mesi o dei giorni in eccesso: ad esempio, la funzione =DATA(2008;15;20) restituirà la data 20/03/2009 in quanto trasla i 3 mesi eccedenti (ai 12 che compongono un anno) sull’anno successivo; allo stesso modo la funzione =DATA(2008;12;40) restituirà la data 09/01/2009 in quanto i nove giorni eccedenti (40-31) vengono traslati sui mesi ed, a loro volta, (poiché diventano 13), sull’anno.
Le funzioni ANNO, MESE e GIORNO, utilizzate come argomenti della funzione DATA, restituiscono rispettivamente l’anno il mese e il giorno di un numero seriale o di una data indicata come argomento; ad esempio, la funzione =ANNO(31/12/08) restituirà 2008, la funzione =MESE(31/12/08) restituirà 12 mentre la funzione =GIORNO(31/12/08) restituirà 31.
Torniamo alla formula inserita nella cella B11 e consideriamo gli argomenti della funzione DATA.
Abbiamo utilizzato come primo argomento, la funzione ANNO($B$4) che estrapola l’anno della data relativa alla prima rata (B4).
Come secondo argomento abbiamo utilizzato la formula MESE($B$4)+A11*12/$B$6 che consente di calcolare il totale dei mesi complessivamente trascorsi dalla prima rata (si ricorda che per il meccanismo della traslazione, verranno aggiornati gli anni quando i mesi superano 12).
Come terzo argomento della funzione DATA è stata utilizzata la funzione GIORNO($B$4) che consente di estrapolare il giorno dalla data relativa alla rata iniziale (B4).
La funzione è stata successivamente copiata verso il basso, sempre fino alla riga 50 del foglio di calcolo.
La terza colonna della tabella visualizza la data di chiusura dell’esercizio contabile in corrispondenza del “salto” di date da un anno all’altro; tali valori consentiranno, successivamente, di calcolare il Risconto Attivo da contabilizzare a fine esercizio.
La formula inserita nella cella B10 (e successivamente copiata per trascinamento fino alla cella B50) è:
=SE(B11=””;””;SE(ANNO(B11)>ANNO(B10);DATA(ANNO(B10);12;31);””))
La formula, costruita con due funzioni SE nidificate e con le funzioni ANNO e DATA, può essere letta in questo modo: SE la cella B11 è vuota (ovvero se siamo in corrispondenza dell’ultimo canone leasing) RESTITUISCI una cella vuota, ALTRIMENTI (ovvero se la riga B11 contiene dei dati), SE l’anno della data inserita in B11 è maggiore dell’anno inserito nella data della cella B10 (ANNO(B11)>ANNO(B10)) RESTITUISCI la data del relativa all’anno della data inserita in B10 (DATA(ANNO(B10);12;31)) ALTRIMENTI (ovvero se siamo in corrispondenza di canoni infra-annuali) inserisci una etichetta vuota.
La colonna D visualizza semplicemente gli importi costanti dei canoni periodici di leasing fino alla data dell’ultima rata. In D10, pertanto, è stata utilizzata la formula:
=SE(A10=””;””;$B$7)
che subordina la trascrizione dell’importo del canone (B7) all’esistenza di una serie numerica nella cella A10 (anche in questo caso, la formula è stata copiata fino alla cella D50).
La colonna E indica l’importo del canone leasing di competenza economica dei diversi esercizi: se il canone leasing ha una scadenza infra-annuale è totalmente di competenza dell’esercizio, altrimenti, se il canone leasing si trova “a cavallo” di due esercizi contabili, occorre calcolare soltanto la quota di competenza dell’esercizio, ovvero fino al 31/12. Nella cella E10 è stata, pertanto, utilizzata la seguente formula:
=SE(C10=””;D10;D10*(C10-B10+1)/(B11-B10))
La formula (da copiare, come le altre, fino alla riga 50) può essere letta in questo modo: SE la cella C10 è vuota (ovvero il canone leasing è di tipo infra-annuale) RESTITUISCI il valore intero del canone leasing (inserito nella cella D10), ALTRIMENTI (ovvero, se esiste una data in C10 e quindi ci troviamo a cavallo tra due esercizi contabili) RESTITUISCI il valore del canone leasing (C10) moltiplicato per i giorni che vanno dalla data del pagamento del canone al 31/12 compreso (C10-B10+1) e diviso per l’intero periodo a cui si riferisce il canone (B11-B10).
Infine, nella colonna F viene calcolato il Risconto Attivo in corrispondenza dei canoni a cavallo tra due esercizi; la formula utilizzata nella cella F10, e successivamente copiata fino alla cella F50 è:
=SE(A10=””;””;D10-E10)
La funzione SE consente di calcolare la differenza tra il Canone leasing (D10) ed il canone di competenza (E10) se esiste un valore nella serie numerica della colonna A (A10); da notare che tale valore è pari a zero in corrispondenza dei canoni infra-annuali.
=> Vai agli altri tutorial su Microsoft Excel
Personalizzare il modello
Il modello è stato costruito copiando tutte le formule fino alla riga 50 e, pertanto, consente di accogliere un piano di leasing fino ad un massimo di 41 rate; se si rende necessario utilizzare un numero maggiore di rate è sufficiente selezionare l’intervallo A11:F11 (o uno sottostante) e trascinare il quadratino di riempimento verso il basso fino ad ottenere la lunghezza desiderata; occorre fare attenzione a non trascinare l’intervallo A10:F10 verso il basso in quanto, come abbiamo avuto modo di osservare, alcune formule della prima colonna della tabella sono differenti rispetto a quelle sottostanti.
