La normativa in materia di imposte prevede la possibilità di escludere dalla contabilità fiscale di magazzino, alcuni beni di scarso valore che incidono in maniera marginale sul costo di acquisto complessivo (es.. i beni di cancelleria). Sono considerati di scarsa rilevanza quei beni il cui costi di acquisto cumulato non superino la percentuale del 20% del totale acquisti effettuati nell’esercizio contabile precedente.
Pertanto, per determinare i beni che possono essere esclusi dall’obbligo fiscale della contabilità di magazzino, è necessario ordinare i beni acquistati durante l’esercizio precedente in base al costo di acquisto per importi crescenti (dal bene meno costoso a quello più costoso), calcolare la percentuale del costo di ogni singolo bene sul totale, la percentuale cumulata sul totale e, infine, individuare i beni la cui percentuale cumulata non superi la soglia del 20%. In questo tutorial proponiamo un modello che consente di ordinare automaticamente i beni in base al costo di acquisto crescente e di effettuare i calcoli necessari richiesti dalla normativa fiscale.
=> Scarica l’esempio
Come utilizzare il modello
Per utilizzare il modello è sufficiente inserire nel primo foglio di calcolo, rinominato DATI, le classi di prodotto e i relativi costi di acquisto senza preoccuparsi di ordinarli per costi di acquisto. Poiché il modello utilizza la funzione CERCA.VERT è necessario evitare di inserire due beni con identico costo di acquisto (ipotesi peraltro poco probabile che possa verificarsi realmente in azienda!).
=> Gestire la contabilità di magazzino con Excel
Nel secondo foglio del modello, rinominato BeniScarsaRilevanza, vengono eseguite in automatico tutte le operazioni richieste e vengono evidenziati con uno sfondo colorato quei beni che possono essere esclusi dalla contabilità fiscale di magazzino.
Nella seconda parte dell’articolo, osserveremo i passaggi necessari a costruire il foglio di calcolo.
Come costruire il modello
Il foglio DATI contiene una tabella destinata ad accogliere i dati necessari per elaborare i calcoli; pertanto non sono presenti formule o funzioni se non nella cella A17 dove viene calcolato il costo complessivo di acquisto attraverso la funzione =SOMMA(A2:A16). Diversamente il foglio BeniScarsaRilevanza, completamente automatizzato, contiene numerose funzioni nidificate che andiamo ad analizzare.
L’ordinamento automatico dei dati
=> Celle Excel su più fogli: come collegarle, la guida
Excel dispone di due funzioni statistiche, PICCOLO e GRANDE, che consentono di ricercare il k-esimo valore più piccolo o più grande da un insieme di dati. Queste funzioni sono molto utili per ordinare automaticamente una serie di dati senza ricorrere ad operazioni manuali attraverso i comandi Ordina del menu Dati. Nel prospetto proposto abbiamo utilizzato la funzione PICCOLO in quanto occorre visualizzare i dati in ordine crescente. La funzione, composta da due argomenti, ha la seguente sintassi:
=PICCOLO(matrice;k)
ArgomentoDescrizione
matriceè l’insieme delle celle contenenti i valori di cui si desidera estrapolare il k-esimo valore più piccolo
kè la posizione nella matrice o nell’intervallo di celle dei dati da restituire (partendo dal più piccolo)
Per visualizzare il costo di acquisto minore nella cella B2 del foglio BeniScarsaRilevanza dovremmo pertanto utilizzare la funzione:
=PICCOLO(DATI!A2:A16;1)
Allo stesso modo, per visualizzare nella cella B3 il secondo valore più piccolo presente nell’intervallo di celle del foglio DATI contenenti i costi di acquisto dei beni, dovremmo utilizzare la funzione:
=PICCOLO(DATI!A2:A16;2)
e così via per le altre celle fino a B16.
Il riferimento al numero di riga
Per copiare la formula nelle celle sottostanti fino alla cella B16 occorre fare in modo che l’argomento k della funzione PICCOLO venga incrementato, di volta in volta, di una unità. Per fare ciò abbiamo utilizzato la funzione RIF.RIGA di Excel, appartenente alla categoria Ricerca e riferimento. La funzione non ha argomenti e pertanto la sua sintassi è:
=RIF.RIGA()
Essa restituisce il numero di riga della cella nella quale viene inserita; quindi, se viene inserita nella cella B2 restituirà il risultato di 2, se inserita nella cella B3 restituirà il risultato di 3 e così via. Pertanto, la funzione PICCOLO inserita da inserire nella cella B2 dovrà essere modificata nel modo seguente per consentire una facile copiatura nelle celle sottostanti:
=PICCOLO(DATI!$A$2:$A$16;RIF.RIGA()-1)
Da notare che è stato aggiunto un riferimento assoluto alle celle A2:A16 del foglio DATI ed è stato sottratto il valore 1 alla funzione RIF.RIGA in maniera da avere nella cella B2 un indice pari a 1, nella cella B3 un indice pari a 2 e così via.
Eliminazione messaggi di errore
La funzione PICCOLO restituisce un messaggio di errore se non riesce a trovare nell’intervallo indicato come primo argomento il k-esimo valore inserito come secondo argomento.
=> Creazione di grafici con Excel
Nel nostro caso, ad esempio, se la tabella contenuta nel foglio DATI viene “utilizzata” fino alla cella A12 (e non A16), verrebbero restituiti dei messaggi di errore nelle celle dell’intervallo B13:B16 contenenti la funzione PICCOLO e ciò non ci consentirebbe di calcolare il totale dei costi di acquisto e le relative percentuali delle colonne C e D.
Per superar tale inconveniente possiamo utilizzare la funzione VAL.ERRORE di Excel abbinata alla funzione logica SE.
La funzione VAL.ERRORE, appartenente alla categoria Informative, ha la seguente sintassi:
=VAL.ERRORE(val)
val indica una formula o una cella contenente una formula.
La funzione VAL.ERRORE restituisce VERO se la cella o la formula contenuta nel suo argomento genera un errore oppure FALSO nel caso opposto. Pertanto, nella cella B2 del foglio BeniScarsaRilevanza possiamo utilizzare la formula seguente:
=SE(VAL.ERRORE(PICCOLO(DATI!$A$2:$A$16;RIF.RIGA()-1))=VERO;0;PICCOLO(DATI!$A$2:$A$16;RIF.RIGA()-1))
La formula può essere letta in questo modo: se il risultato della funzione PICCOLO contiene un errore (=VERO), restituisci nella cella il valore zero (0), altrimenti restituisci il risultato della funzione PICCOLO. La formula così costruita può essere copiata verso il basso fino alla cella B16; nella cella B17 utilizziamo la funzione =SOMMA(B2:B16) per determinare il totale degli acquisti effettuati nell’esercizio contabile precedente.
Richiamo nome prodotti
Nelle celle della colonna A della tabella dovranno comparire i nomi dei prodotti il cui costo di acquisto è pari al valore contenuto nelle celle della colonna B. Per compiere questa operazione dobbiamo utilizzare la funzione CERCA.VERT. Richiamiamone, per completezza espositiva, la sintassi:
=CERCA.VERT(valore;tabella_matrice;indice;intervallo)
- valore è il dato da ricercare nella prima colonna della tabella_matrice (nel nostro caso è dato dal costo di acquisto scaturente dalla funzione PICCOLO inserito nelle celle della colonna B)
- tabella_matrice è l’intervallo di celle contenente la tabella di riferimento (nel nostro caso è dato dall’intervallo A2:B16 del foglio DATI)
- indice indica la colonna della tabella_matrice dalla quale deve essere restituito il valore della cella che si trova sulla stessa riga di “valore” (nel nostro caso è la colonna 2 del foglio DATI, dove sono indicati i nomi dei beni)
- intervallo è un valore logico che può essere pari a VERO oppure a FALSO (nel nostro caso è necessario indicare FALSO in maniera che la formula restituisca un valore solo se c’è una corrispondenza esatta con la tabella del foglio DATI)
Pertanto, nella cella A2 del foglio BeniScarsaRilevanza dovremmo utilizzare la formula:
=CERCA.VERT(B2;DATI!$A$2:$B$16;2;FALSO)
Anche in questo caso, però, in corrispondenza delle celle della colonna B, che non presentano valori (o meglio, che presentano un valore pari a zero visto che le formule sono state costruite con la funzione SE), verrebbe restituito un messaggio di errore. Quindi, come nel caso precedente, possiamo inserire la formula all’interno della funzione SE utilizzando la funzione VAL.ERRORE. Nella cella A2 scriveremo:
=SE(VAL.ERRORE(CERCA.VERT(B2;DATI!$A$2:$B$16;2;FALSO))=VERO;””;CERCA.VERT(B2;DATI!$A$2:$B$16;2;FALSO))
La formula può essere letta in questo modo: se esiste il valore inserito in B2 nella tabella del foglio DATI restituisci il nome del prodotto (colonna 2 della tabella DATI) altrimenti, in caso di errore, restituisci una cella vuota (le doppie virgolette inserite come secondo argomento della funzione SE). La formula così costruita può essere copiata verso il basso fino alla cella A16.
Calcolo percentuali costi di acquisto
La colonna C del prospetto evidenzia la composizione percentuale dei costi di acquisto dei singoli prodotti. Nella cella C2 è stata utilizzata la formula:
=SE($B$17=0;0;B2/$B$17*100)
La formula calcola il rapporto tra il costo del prodotto (cella B2) e il costo complessivo (cella B17) e moltiplica tale valore per 100 solo se la cella B17 contiene un valore diverso da zero.
La formula è stata successivamente copiata per trascinamento fino alla cella C16 mentre, nella cella C17 è stato calcolato il totale delle percentuali (che dovrà essere pari a 100%) attraverso la funzione =SOMMA(C2:C16). Per determinare le percentuali cumulate nella cella D2 è stato inserito il riferimento alla percentuale del prodotto contenuto nella cella C2 (=C2), mentre nella cella D3 è stata utilizzata la formula:
=SE(D2=100;0;D2+C3)
La formula, che viene successivamente copiata verso il basso fino alla cella D16, somma il valore percentuale della cella laterale (C3) con quello contenuto nella cella superiore (D2) fino a quando quest’ultimo valore non è pari a 100(%).
La formattazione delle celle
Per assegnare uno sfondo colorato alla descrizione dei prodotti la cui percentuale cumulata non superi il 20% degli acquisti complessivi, abbiamo infine utilizzato lo strumento di formattazione condizionale di Excel. Dopo aver selezionato l’intervallo A2:A16 della tabella, si è utilizzato il comando Formattazione condizionale posto nel menu FORMATO.
=> Analisi dati contabili con Excel: struttura e subtotali
Nella finestra omonima che appare a video si è selezionata l’opzione “la formula è” dall’elenco a discesa e si è inserito nella casella di testo la formula:
=E(D2>0;D2<=20)
La formula, costruita con l’operatore logico E, indica un intervallo di valori che dovrà avere la cella D2, ovvero compreso tra maggiore di zero e minore o uguale a 20. L’intervallo maggiore di zero consente di evitare la formattazione alle celle che non contengono riferimenti ai prodotti (celle vuote della colonna A del prospetto) e che il riferimento nella formula alla sola cella D2 viene considerato da Excel un riferimento relativo (quindi, la formattazione della cella A3 verrà applicata solo se D3 è compreso tra zero e venti, quella della cella A4, se D4 sarà compreso tra zero e venti, e così via). Infine, attraverso il pulsante Formato si è personalizzato il colore dello sfondo delle celle interessate dalla formattazione condizionale.
Come personalizzare il modello
Il modello è stato predisposto per l’inserimento di un numero massimo di quindici categorie di prodotto. Per aumentare il numero dei prodotti è sufficiente inserire un identico numero di righe all’interno delle tabelle (in maniera che i riferimenti agli intervalli di celle inseriti nelle formule possano essere adattate automaticamente da Excel) e trascinare le formule contenute nella tabella del foglio BeniScarsaRilevanza.
