Gestione ordini e scorte di magazzino con Excel

Per valutare la frequenza e i volumi di fornitura della merce più richiesta, un’impresa deve tenere conto dei seguenti fattori:

1. Domanda [n° unità di prodotto] – merce richiesta complessivamente dal mercato per un determinato periodo (es.: un anno).
2. Volumi dell’ordine [n°unità di prodotto/ordine] – quantitativo di merce da richiedere al fornitore per ogni ordine.
3. Costi unitari [€/unità di prodotto] – costo di un unità di prodotto.
4. Costi di riordino [€/unità di prodotto] – costo per trasporto e produzione della merce (se viene ordinato da una ditta esterna viene detto “costo di consegna”; se prodotto dall’impresa stessa viene detto “costo di produzione”).
5. Costo di mantenimento [€/unità*tempo] – costo delle scorte in magazzino (ad esempio per prodotti che devono essere conservati in una cella frigorifera).

Tabella Gestione ordini e scorte

Andiamo su Excel e nominiamo il Foglio1 in Dati. Successivamente creiamo una tabella che contenga i valori dei dati elencati di sopra:

Supponiamo che un’azienda di ristorazione abbia una richiesta di 1.000 pacchi di pasta per anno al costo unitario di 0,4 €, con costi di consegna di 0,1€ a prodotto e costi di mantenimento di 0,025 €: inseriamo questi dati nelle rispettive celle:

Partendo dalla cella C7 dello stesso foglio riproduciamo la seguente mini-tabella:

Supponiamo che il prodotto venga consegnato in lotti (confezioni, scatole, etc…) contenenti ciascuno una quantità di prodotto definita (per esempio, scatole da 50 confezioni ciascuna): trascriviamo 50 nella cella C8.

Calcoliamo ora la quantità di materiale da ordinare in ogni periodo in modo da minimizzare i costi. Nella cella C7 inseriamo la formula =RADQ((2*C4*C2)/(C5)).

Il numero di lotti da ordinare sarà dato dal rapporto fra la Quantità di materiale da ordinare in ogni periodo già calcolata e la quantità di prodotto contenuta in un lotto. Il numero di lotti deve essere un numero intero (non possiamo ordinare mezza scatola!) quindi, a tal proposito, scriviamo nella cella C9 la formula =ARROTONDA((C7/C8);).

La quantità effettiva di materiale che andremo a ordinare sarà data quindi dal prodotto fra numero di lotti da ordinare e n° di prodotti presenti nel lotto. Scriviamo nella cella C10 la formula =C9*C8.

Ora possiamo calcolare ogni quanti giorni deve essere effettuato l’ordine della quantità effettiva di materiale già calcolata. All’interno della nostra tabella di Excel troviamo la riga relativa all’Ordine: esso è dato dal rapporto fra Qo effettivo e la Domanda e deve essere valutato in giorni, quindi nella cella C11 scriviamo =ARROTONDA(((C10/C2)*365);).

Interpretando i numeri ottenuti diciamo che l’ordine è da effettuare ogni 37 giorni e che la pasta arriverà per ogni ordine, in 2 scatole da 50 pacchi ciascuna.

Costi

Valutiamo ora i costi del prodotto intesi come costo intimo, costi di consegna, costo di gestione nel magazzino. I costi sono valutati per il periodo a cui fa riferimento la domanda del prodotto, per esempio un anno. Nel foglio Dati riproduciamo la seguente tabella:

Calcoliamo ora:

• I costi fissi: sono dati dal prodotto fra il costo unitario del prodotto e la domanda. Scriviamo nella cella C14 la formula: =C3*C2.
• I costi variabili: Dipendono dalla Quantità di materiale effettiva da ordinare in ogni periodo. Nella cella C15 inseriamo la formula: =((C4*C2)/C10)+((C5*C10)/2).
• I costi totali: Sono dati dalla somma fra i costi fissi e i costi variabili. Nella cella C16 inseriamo la formula =C14+C15.

Otteniamo:

Il costo variabile, è stato minimizzato a 2€/anno.

Riordino

Supponiamo che l’impresa richieda la fornitura del prodotto da una ditta esterna. E’ necessario conoscere il tempo di consegna (LT) ossia il tempo che passa tra il momento in cui si effettua l’ordine dal fornitore e il momento in cui la merce arriva in magazzino. Ovviamente l’ordine deve essere effettuato prima che le scorte in magazzino si esauriscano quindi si deve stabilire un livello di riordino: quando la quantità di prodotto in magazzino, esaurendosi, scende al di sotto di una determinata quantità (il livello di riordino, appunto) bisogna effettuare un nuovo ordine. (per esempio se il livello di riordino del mio magazzino è, nel caso di forme di formaggio, 50 pezzi, quando in magazzino conto 50 forme di formaggio, chiamo il fornitore per effettuare un nuovo ordine di una quantità pari a quella prestabilita).

Il tutto è organizzato per evitare all’impresa di rimanere senza prodotto da poter vendere ai clienti.

Riproduciamo sul nostro foglio Excel la seguente tabella:

Calcoliamo adesso, il valore del tempo di consegna. Per essere confrontabile con la domanda (che è espressa in prodotti/anno) il tempo di consegna deve essere espresso in anni. Se per esempio il tempo di consegna è di 15 giorni, scriviamo nella cella F3: =15/365.

Si possono presentare 2 casi:

1. tempo di consegna < ordine: Il tempo di consegna è minore rispetto al tempo necessario per esaurire le scorte di un ordine. Il livello di riordino è dato dal prodotto fra il tempo di consegna e la domanda. Inserisco, a tal proposito, nella cella F4 la formula =SE((F3*365)<C11;ARROTONDA(F3*C2;);). La condizione ‘SE’ serve per consentire il calcolo solo se è verificata la condizione tempo di consegna < ordine, altrimenti restituisce 0.
2. Tempo di consegna > ordine: Il tempo di consegna è maggiore rispetto al tempo necessario per esaurire le scorte di un ordine. Ciò significa che durante l’utilizzo delle scorte provenienti da un determinato ordine, dovranno essere organizzate più consegne. Definiamo con n il numero di consegne in corso: il suo valore è iterativo e deve essere aumentato di un unità sino a quando il livello di riordino, non diventa minore della quantità di materiale effettivamente ordinata. Ora il livello di riordino è dato dalla seguente formula (da trascrivere nella cella F6): =SE((F3*365)>C11;ARROTONDA(F3*C2-F5*C10;);). Impostiamo la condizione ‘SE’ perché vogliamo che la cella faccia il calcolo solo se il tempo di consegna > ordine.

Torniamo ai calcoli eseguiti in precedenza, questi hanno stabilito che l’ordine deve essere effettuato ogni 37 giorni. Supponiamo, inizialmente, che il tempo di consegna<ordine sia per esempio 20 giorni. Scriviamo nella cella F3: =20/365 per riportare il tempo in anni. Il livello di riordino è di 55 prodotti ossia, quando il livello del magazzino passa dai 100 iniziali a 55, ordino un nuovo lotto da 100 prodotti.

Supponiamo ora che il tempo di consegna>ordine per esempio 40 giorni. Avremo almeno 1 ordine in corso quindi scriviamo 1 nella cella F5. Scriviamo nella cella F3: =40/365 per riportare il tempo in anni. Il livello di riordino passa a 10 prodotti: quando il livello del magazzino passa dai 100 iniziali a 10, ordino un nuovo lotto di prodotti. Non deve sorprendere che il livello di riordino sia sceso rispetto al caso precedente poiché all’interno di un ordine sono comprese 2 consegne.