Lezione del 26 - 09- 2014 - 5 D Sia - La scheda di magazzino con EXCEL

Una scorta eccessiva di prodotti in magazzino può essere sintomo di inefficienza di gestione per un’impresa per almeno due ordini di motivi.

Da un lato infatti, l’acquisto di prodotti da fornitori che alimentano il magazzino può essere tradotto, in termini finanziari, in un impiego “infruttifero” di capitale (se si dispone di liquidità) o in un esborso monetario per pagare gli interessi sulla somma presa a prestito dalle aziende di credito; dall’altro, in special modo per le imprese che operano con prodotti facilmente deperibili o dal “ciclo di vita” breve, aumenta il rischio di commercializzazione.

=> Leggi come gestire al meglio il magazzino


Pertanto, tranne casi eccezionali (quali, ad esempio, l’ottenimento di uno sconto maggiore per l’acquisto di una quantità consistente), è buona regola monitorare i movimenti di magazzino per cercare di ridurre, ove conveniente, la scorta dei prodotti.

Il modello proposto in questo tutorial consente di avere sotto controllo le movimentazioni delle scorte di singoli prodotti e di ottenere informazioni sui principali indicatori di magazzino (consistenza media, indice di rotazione e giorni di giacenza media).

=> Leggi come gestire la contabilità di magazzino con Excel



Scarica il modello
Come costruire il modello proposto

La scheda di magazzino prevede la possibilità di utilizzare fino ad un massimo di quindici movimenti (acquisti o vendite o meglio “carico” e “scarico”) per prodotto e fornisce, di volta in volta, informazioni sulle quantità disponibili in magazzino (Esistenza) e sulla “opportunità” o “esigenza” di riordinare il prodotto in esaurimento (Info); nella parte bassa della scheda è possibile ottenere informazioni sui principali indicatori di magazzino.

Figura 1. La scheda magazzino



Nella prima tabella del modello dovrà essere indicato il “livello di riordino” e la “scorta di sicurezza” (oltre che, naturalmente la descrizione e l’eventuale codice del prodotto).

Il “livello di riordino” indica le quantità al di sotto delle quali è necessario provvedere ad effettuare una nuova ordinazione, mentre la “scorta di sicurezza” indica le quantità al di sotto delle quali si “rischia” di non essere in grado di evadere un’ordinazione per mancanza di prodotti in magazzino.

=> Vedi anche la gestione ordini con Excel con magazzino limitato

I due indicatori dipendono dalla tipologia di prodotto, dai tempi necessari per completare una procedura di acquisto, dalla frequenza di vendita e dalle quantità che mediamente vengono vendute di volta in volta.

Nella seconda tabella del modello, dopo aver indicato le quantità disponibili inizialmente (F9) e la data di inizio (A9) e fine (A25) periodo contabile, dovranno essere trascritte, nell’intervallo A10:E24, le singole operazioni di carico/scarico di magazzino.

Analizziamo ora le formule utilizzate nel modello. Nella cella F10 è stata trascritta la formula:

=SE(A10=”";0;F9+D10-E10)

Essa consente di calcolare il “saldo” delle quantità (F9+D10-E10) se la cella che dovrà contenere la data non è vuota (A10). La formula è stata successivamente copiata fino alla riga 24.

Nella cella G9 si è utilizzata la formula seguente che contiene una nidificazione della funzione SE:

=SE(A9=”";0;SE(A10=”";$A$25-A9+1;A10-A9))

=> Approfondisci la nidificazione della funzione SE

La formula può essere letta in questo modo: se la cella A9 è vuota (ovvero il prospetto non è stato ancora utilizzato) trascrivi uno zero (0) nella cella G9, altrimenti se la cella A10 è vuota (ovvero non ci sono ulteriori movimentazioni di magazzino) trascrivi la differenza tra la cella A25 (contenente la data di fine periodo contabile) e la cella A9 (contenente la data di inizio periodo) e aggiungi uno (1) per comprendere nel calcolo dei giorni entrambe le date), altrimenti (ovvero, se la riga successiva non è vuota e quindi esiste un ulteriore movimento di magazzino) trascrivi la differenza tra la data del successivo movimento (A10) e la data di inizio periodo (A9).

La formula è stata successivamente copiata fino alla cella G23. Nella cella G24 è stata utilizzata la formula:

=SE(A24=”";0;$A$25-A24+1)

Essendo difatti, l’ultima movimentazione che è possibile trascrivere nel prospetto, è sufficiente subordinare il calcolo dei giorni ($A$25-A24+1) alla sola esistenza di un valore nella prima cella della stessa riga (A24).

La colonna H contiene la moltiplicazione, riga per riga, tra le giacenze in magazzino (Esistenza) e i giorni.

La colonna I indica, operazione per operazione, se la scorta di un prodotto è al di sotto del livello di riordino (nel qual caso appare l’etichetta “In esaurimento”) o addirittura al di sotto della scorta di sicurezza (nel qual caso appare l’etichetta “Sottoscorta”).

Nella cella I9 è stata utilizzata la formula:

=SE(O($C$5=”";$C$6=”";F9<=0);”";SE(F9<=$C$6;”Sottoscorta”;SE(F9<=$C$5;”In esaurimento”;”")))

Attraverso la funzione SE, nidificata al suo interno due volte, si chiede ad Excel di testare se almeno una tra le celle che contengono gli indicatori di scorta (C5 e C6) è vuota e, contemporaneamente, se la cella che contiene la giacenza di magazzino (F9) è minore o uguale a zero (O($C$5=”";$C$6=”";F9=0).

=> Scopri come gestire ordini e scorte di magazzino con Excel

Se questa condizione si verificha, il programma restituisce una cella vuota; altrimenti (ovvero, se il prospetto è stato compilato correttamente) si chiede ad Excel di fare un ulteriore test per verificare che la scorta in magazzino (F9) sia inferiore o uguale alla scorta di sicurezza (C6) e di restituire, in caso affermativo, l’etichetta “Sottoscorta”.

Se quest’ultima verifica ha esito negativo si controlla che la scorta in magazzino (F9) sia pari o inferiore al livello di riordino (C5); se tale condizione si verifica, si chiede di restituire l’etichetta “In esaurimento”, altrimenti ancora una etichetta vuota (le doppie virgolette).

La formula, costruita con i riferimenti assoluti, è stata successivamente copiata verso il basso fino alla cella I24.

La cella D25 contiene il totale delle quantità entrate in magazzino, la cella E25 quello delle quantità uscite dal magazzino, la cella G25 il totale dei giorni tra i vari movimenti (che coincidono alla differenza tra i periodi contabili) e nella cella H25 la somma tra le moltiplicazioni tra le quantità in magazzino per i giorni di giacenza.

Quest’ultimo dato diviso per il totale giorni, consente di calcolare la “Consistenza media del periodo”; pertanto, nella cella I26 è stata inserita la formula:

=SE(G25=0;0;H25/G25)

L’indice di rotazione del magazzino si calcola dividendo il totale della merce uscita dal magazzino nel periodo (scarico) e la consistenza media del periodo; pertanto, nella cella I27 si è utilizzata la formula:

=SE(I26=0;0;E25/I26)

Infine, i giorni di giacenza media si calcolano dividendo i giorni del periodo contabile di riferimento per l’indice di rotazione; pertanto, nella cella I28 è stata utilizzata la formula:

=SE(I27=0;0;G25/I27)

=> Leggi gli altri tutorial Excel

Personalizzazione del modello

Il modello proposto può essere personalizzato per consentire ulteriori movimenti di magazzino: è sufficiente inserire nuove righe all’interno del prospetto e ricopiare le formule; inoltre, per rendere il prospetto maggiormente leggibile è possibile nascondere le colonne G e H poiché queste ultime servono esclusivamente per il calcolo degli indici di magazzino e non forniscono informazioni utili ai fini del controllo delle scorte di magazzino.