Con il seguente tutorial proponiamo una metodologia per gestire lo stoccaggio dei prodotti in magazzino (>>vai al tutorial) ma stavolta con spazio limitato, dovendo quindi per evitare che si superi la capacità a disposizione per sistemare tutta la merce.
Con Excel possiamo stabilire, per ciascun prodotto, la quantità ottimale(in termini di costi) da ordinare, tale che non faccia superare la capacità massima di stoccaggio del magazzino.
Apriamo Excel e rinominiamo il Foglio1 in LimitiMagazzino, inseriamo in una tabella apposita la capacità del magazzino in metri cubi e riportiamo nel nostro foglio di lavoro il volume dei prodotti (in metri cubi): volume prodotto 1, volume prodotto 2, volume prodotto 3, etc. Di ciascun prodotto riportiamo:
- Domanda [n° unità di prodotto] – la quantità di materiale richiesta dal mercato per un determinato periodo supponendo che la domanda rimanga costante per tutto il periodo di analisi, per esempio un anno.
- Costi di riordino [€/unità di prodotto] – costo di trasporto/produzione del materiale; se il prodotto viene ordinato da una ditta esterna, viene detto costo di consegna, se viene prodotto dall’impresa stessa, costo di produzione.
- Costo di mantenimento [€/unità*tempo] – dovuto al mantenimento delle scorte in magazzino (per esempio per prodotti conservati in cella frigorifera).
- Dimensione ordine [n°unità di prodotto/ordine] – quantitativo di prodotto da richiedere al fornitore, per ogni ordine.
Introduciamo il coefficiente AC: è un parametro presente nella formula della quantità ottima di prodotto da ordinare, con la cui variazione si può modificare equamente, per ciascun tipo di prodotto, la quantità da ordinare per rimanere nei limiti di spazio del magazzino.
Inizialmente il suo valore è pari a zero. In caso superamento dei limiti di spazio, il suo valore dovrà essere aumentato iterativamente di una unità: questo provoca la riduzione equa della quantità da ordinare per ogni prodotto e, quindi, il rispetto dei limiti di spazio. Inseriamo nella cella D20 il suo valore pari a 0.
Tutte le informazioni sopra-indicate le possiamo riportare sul foglio LimitiMagazzino in questo modo:
Nell’esempio abbiamo supposto che la capacità del magazzino sia di 20 metri cubi. I costi di riordino sono quelli dovuti al trasporto della merce al magazzino: essendo più pesante, il costo di riordino del parmigiano è maggiore rispetto agli altri. Il costo di mantenimento per il vino e formaggio è maggiore perché vengono conservati in un ambiente con temperatura più bassa (x es. una cella frigo).
In base alla domanda annuale di prodotto, vogliamo ora calcolare le quantità ottimali di prodotto da ordinare e verificare se gli ordini di pasta, vino e parmigiano, superano la capacità del magazzino. A tal proposito di fianco, nelle colonne F e G, riproduciamo una seconda tabella:
Inseriamo ora la formula per calcolare la Quantità ottima di prodotti da ordinare.
PRODOTTO 1
Nella cella G3 scriviamo =RADQ((2*D12*D8)/(D16+(D20*D4))).
Supponiamo che il prodotto non venga consegnato in quantità singole ma in lotti (confezioni, scatole, etc…) contenenti ciascuno una quantità di prodotto definita. Scriviamo su G4 il n° delle unità di cui è composto ciascun lotto.
Il numero di lotti da ordinare sarà dato dal rapporto fra la Quantità di prodotto da ordinare calcolata e il n°unità per ciascun lotto. Il numero di lotti deve essere un numero intero (non possiamo ordinare mezza scatola!) quindi, a tal proposito, scriviamo nella cella G5 la formula =ARROTONDA((G3/G4);).
La quantità effettiva di prodotto ordinata che andremo a ordinare sarà data quindi dal prodotto fra numero di lotti da ordinare e il n° di prodotti presenti nel lotto. Scriviamo nella cella G6 la formula =(G5)*G4.
PRODOTTO 2
Nella cella G9 calcoliamo la quantità di prodotto da ordinare=RADQ((2*D13*D9)/(D17+(D20*D5))).
Nella cella G10 inseriamo il n°unità per ciascun lotto.
Nella cella G11 calcoliamo il numero di lotti da ordinare=ARROTONDA((G9/G10);).
Nella cella G12 calcoliamo la quantità effettiva di prodotto ordinata il =G11*G10.
PRODOTTO 3
Nella cella G15 calcoliamo la quantità di prodotto da ordinare=RADQ((2*D14*D10)/(D18+(D20*D6))).
Nella cella G16 inseriamo il n°unità per ciascun lotto.
Nella cella G17 calcoliamo il numero di lotti da ordinare=ARROTONDA((G15/G16);).
Nella cella G18 calcoliamo la quantità effettiva di prodotto ordinata=G17*G16.
Una volta calcolata la quantità da ordinare per ciascun prodotto, dobbiamo verificare se il volume totale dei prodotti è maggiore o meno, della capacità del magazzino. A tal proposito ricreiamo, accanto alle precedenti, la seguente mini-tabella:
Per ciascun prodotto calcoliamo il volume occupato, prodotto moltiplicando la quantità effettiva di prodotto ordinato e il suo volume: nella cella K2 inserisco la formula =G6*D4. Effettuo lo stesso calcolo anche per i restanti prodotti: nella cella K3 inserisco =G12*D5, nella cella K4 inserisco =G18*D6.
Il volume totale dei prodotti sarà dato dalla formula=SOMMA(K2:K4).
A questo punto, ho tutti i dati per effettuare un confronto tra il volume necessario per stoccare i prodotti ordinati e la disponibilità del magazzino.
Devo inserire nella cella una formula che risponde alla seguente domanda: Volume Prodotti>Capacità Magazzino? Nella cella K7 scrivo: =SE(K5>D2;VERO;FALSO). Se nella cella K7 compare la scritta FALSO vuol dire che il quantitativo di prodotti non supera la capacità del magazzino e quindi le quantitàc alcolate nelle celle G6, G12, G18, vanno bene. Se restituisce VERO allora andiamo ad aumentare il coefficiente AC di una unità (1,2,3,4…) sino a quando la cellaK7 non restituisce il valore FALSO. Questa operazione comporta, per ciascun prodotto, la diminuzione della quantità da ordinare in maniera equa per tutti i prodotti.
Cerchiamo di capire meglio il contenuto del nostro foglio di lavoro:
Abbiamo supposto che la pasta venga consegnata in scatole contenenti 20 confezioni da 500g, il vino in casse da 6 bottiglie da 0,7l ciascuna e le forme di formaggio, in confezioni contenenti 4 unità ciascuna. Il calcolo ha restituito per ciascun ordine, una quantità di 480 pacchi di pasta, 192 bottiglie di vino, 100 forme di formaggio. Il volume del prodotto ordinato è di 98 metri cubi, di gran lunga superiore alla capacità del magazzino di 20. Infatti la cella K7 restituisce il valore VERO.
Abbiamo spiegato che per diminuire il volume dei prodotti, bisognava aumentare il coefficiente AC progressivamente di un unità, sino a quando K7 non restituisce il valore FALSO. Ciò provoca la diminuzione delle quantità da ordinare per tutti i prodotti.
Aumentando AC, si osserva che K7 diventa FALSO per un valore pari a 12. Ciò comporta un ordine di prodotto con occupazione del magazzino per 18,4 metri cubi.
Notiamo che le quantità da ordinare sono ora di:
- pasta: 60 pacchi;
- vino:72 bottiglie;
- parmigiano: 20 forme.
Osserviamo che le unità di prodotto da ordinare si sono ridotte notevolmente rispetto al calcolo iniziale ma ciò ha consentito di evitare degli esuberi nel magazzino in questione. Probabilmente, nel caso analizzato, si potrebbe consigliare all’impresario di affittare/acquistare un magazzino più capiente, in modo da poter stoccare tutta la merce e poter soddisfare appieno la domanda.
