Come creare uno strumento di analisi delle giacenze completamente automatizzato con ZERO scrittura di codice VBA in Excel.
Ecco un caso di studio di un vero strumento di analisi delle giacenze che ho creato per un cliente.
L’attività manifatturiera di questa azienda consiste nella produzione di contenitori in plastica la cui superficie viene decorata in base alle specifiche del cliente. La filiera è un mix abbastanza articolato di operazioni interne ed esterne, la distinta base è multilivello e c’è un continuo rinnovamento del portafoglio prodotti.
Ho sviluppato lo strumento tramite il Fast Excel Development Template in meno di una settimana, la maggior parte della quale è stata dedicata alla raccolta dei dati e al controllo della coerenza.
Qui di seguito trovate le spiegazioni relative allo sviluppo uno strumento Excel di analisi delle giacenze completamente automatizzato basato sul set di dati del produttore di biciclette di fantasia P_S Cycles Ltd.. Se desideri una copia dello strumento, compila questo modulo e ti invierò via email un link per il download:
Trovi lo strumento di analisi dell’inventario funzionante, un set di dati e il modello FEDT che ho usato. Ho incluso le istruzioni per l’uso alla fine di questo articolo. MA, per favore leggi prima l’articolo perché avrà tutto molto più senso!
Dato che ho usato il Fast Excel Development Template, nessun essere umano ha scritto una sola riga di codice VBA durante la realizzazione di questo strumento di analisi dell’inventario Excel completamente automatizzato.
L’analisi delle giacenze tradizionale si basa sul passato.
Come mai? Poiché gli stessi livelli di inventario attuali sono il risultato di ciò che è accaduto, quindi la tradizionale classificazione basata sul valore ABC (o Pareto) è importante da un punto di vista finanziario ma offre un aiuto molto limitato quando si tratta di decisioni strategia dei livelli di scorta della supply chain
Cosa succede se uno SKU di classe A è solo il requisito del turno successivo? O se viene fornito con un lungo tempo di consegna?
Introduciamo il concetto di giorno di consumo: aggreghiamo i record di consumo passato all’interno di un orizzonte storico (es. gli ultimi 24 mesi) di ogni SKU, calcoliamo il giorno medio di consumo e confrontiamo il consumo medio giornaliero con il livello di inventario attuale.
In questo modo, possiamo esprimere i livelli di inventario in termini di giorni di copertura (conosciuto anche come giorno di fornitura, giorno di consumo). Questo è molto meglio di un valore puramente finanziario: ad esempio, possiamo accettare una copertura di 2 settimane per un componente di provenienza oltre oceano o agire per un prodotto coperto quattro settimane che possiamo ricevere il giorno successivo in cui lo ordiniamo.
Il vantaggio principale di questo approccio è che possiamo considerare la cronologia dei consumi di ogni articolo, indipendentemente dal suo livello di distinta base, quindi i calcoli sono abbastanza semplici anche se potrebbero essere intensivi in caso di un numero elevato di movimenti.
Perché dobbiamo guardare al futuro e come possiamo farlo?
C’è un punto critico: stiamo assumendo che il passato sia una buona rappresentazione del futuro.
Ma cosa succede se una SKU, un top seller in passato, raggiunge la fine del suo ciclo di vita nel prossimo mese? Esistono diversi scenari, come il mercato dei prodotti personalizzati o tecnologici, in cui per qualsiasi motivo il futuro potrebbe essere molto diverso dal passato. Per non parlare dei recenti eventi dirompenti legati alla pandemia.
Possiamo prendere in considerazione il futuro nella nostra analisi dell’inventario e utilizzare allo stesso tempo il concetto di giorni di copertura?
Ebbene, le aziende normalmente hanno un portafoglio ordini di vendita ed in alcuni casi previsioni registrati nell’ERP: possiamo considerare questi dati come la rappresentazione digitale del futuro, la nostra sfera di cristallo.
Queste informazioni sono relative al solo prodotto finito, o alla cosiddetta domanda indipendente: che dire dei semilavorati e delle materie prime?
Abbiamo bisogno di far esplodere la domanda attraverso una distinta base, ma ciò non è ancora sufficientemente preciso: per calcolare la copertura dobbiamo tenere conto del livello delle scorte ad ogni livello della distinta base.
Se un prodotto semilavorato ha un livello di scorte che copre i prossimi sei mesi, i fabbisogni relativi ai suoi componenti nei prossimi sei mesi saranno zero, il che non è quanto risulta da una semplice esplosione della domanda attraverso la distinta base.
Dobbiamo calcolare il fabbisogno lordo di ogni livello della DB e dobbiamo calcolare il fabbisogno netto, il fabbisogno lordo meno il livello di scorte disponibile se maggiore di zero, ed estenderlo al livello inferiore della DB come fabbisogno lordo.
Dovrebbe suonare familiare ai professionisti della supply chain: è l’algoritmo MRP.
Abbiamo calcolato i fabbisogni lordi, o la domanda per ogni articolo in base al agli ordini di vendita o alle previsione, o un loro mix, ad esempio ordini di vendita fino ai primi due mesi futuri e previsione per il successivo terzo e quarto mese .
Una volta deciso l’orizzonte da considerare in futuro, calcoliamo il giorno di domanda come fabbisogno lordo totale diviso per il numero di giorni dell’orizzonte prescelto.
Successivamente possiamo definire le classi di copertura e valutare per ciascuna delle classi il numero di articoli e il relativo valore.
Fantastico, non è vero?
Implementazione tramite Fast Excel Development Template
Ma per quanto riguarda l’implementazione di uno strumento software? L’MRP è un algoritmo abbastanza complesso, se chiedi agli specialisti IT ti preventiveranno giorni e giorni di codifica; in alternativa puoi andare con l’approccio fai da te su Excel, e dopo giorni di duro lavoro ti ritroverai con fogli di calcolo complessi e quindi inaffidabili perché pieni di formule. E la prossima volta dovrai ricominciare dall’inizio.
83 / 5000 Risultati della traduzione Bene, non è affatto complicato per noi di Production-scheduling e i nostri membri:
- abbiamo il Fast Excel Development Template, che a partire dalla release 4 non richiede alcuna capacità di codifica per costruire un processo completamente automatizzato
- abbiamo corsi di formazione su come costruire un sistema MRP personalizzato tramite fogli di calcolo
- abbiamo esempi e modelli disponibili per il download gratuito
Il modello di sviluppo rapido di Excel è una piattaforma di sviluppo software che porta le migliori pratiche di ingegneria del software sul tuo desktop, perché sì, hai Excel lì e ti consente di configurare un software completamente automatizzato senza scrivere una sola riga di codice.
Com’è possibile?
Abbiamo sviluppato nel corso degli anni cinque modelli principali di foglio di lavoro (o template):
- Query template: dove ti colleghi alle origini dati esterne
- Table template: modello di calcolo principale, filtri righe o colonne, esplosione una tabella in un’altra e molto altro ancora
- Stack template: lo strumento che semplifica l’accodamento dei dati di diverse colonne in diversi fogli di lavoro e li normalizza in un’unica tabella
- Parameter template: dove memorizzi i dati che devi conservare nel tuo foglio di calcolo; mantieni l’utilizzo di ciò limitato il più possibile ed esportalo/esegui il backup.
- Pivot template: il principale strumento di reporting e talvolta anche un potente strumento di calcolo
Inoltre, una serie di macro stanno facendo il lavoro di automazione per te dietro le quinte. Una di queste è molto speciale: è una macro che scrive la macro di automazione per tuo conto. Quant’è elegante tutto ciò?
Per coloro che sono molto interessati alla sicurezza: il codice FEDT VBA è sbloccato, quindi è possibile ispezionarlo e volendo anche modificarlo.
Dove puoi imparare a trarre vantaggio dalla FEDT? Ci sono una serie di risorse gratuite per i membri registrati gratuitamente nella nostra comunità o se vuoi accedere alla nostra serie “How to build”, che puoi acquistare ad un costo ragionevole.
Tornando all’argomento, ho costruito lo strumento in meno di una settimana grazie al FEDT, e il processo più importante è stato la raccolta i dati di input e la verifica della loro consistenza, e non lo sviluppo del codice. In effetti, nessun essere umano ha scritto una sola riga di codice: il sistema di automazione FEDT ha fatto tutto per me.
Mi sono ritrovato con uno strumento completamente automatizzato integrato con il database aziendale.
Ecco il processo.
Input
- Ordini di vendita/Previsioni
- Giacenze
- Anagrafica articoli e costi unitari
- Distinta base
- Classi di copertura
Tutti i dati di cui sopra sono stati collegati a fonti esterne tramite il Query template del FEDT.
Elaborazione.
Qualche parola sulla DB prima di approfondire l’algoritmo: possiamo avere sia una DB a livello singolo, detta anche piatta, quando i prodotti finiti sono realizzati con materie prime, sia una DB multilivello, quando i prodotti finiti sono realizzati con uno o più livelli di semilavorati giù fino alle materie prime.
Mentre la distinta base piatta può essere utilizzata in modo semplice ed intuitivo nei fogli di calcolo, una distinta base multilivello richiede un insieme più complesso di formule combinate con istruzioni di ordinamento per propagare i requisiti e tenere conto delle giacenze disponibili.
Il tipico record di distinta base che troviamo nei sistemi ERP è:
Prodotto Componente QtaPer
Per eseguire l’esplosione, abbiamo bisogno di una distinta base del prodotto.
La struttura del record della distinta base di prodotto è:
Livello Prodotto Padre Componente QtaPer OffsetComponente
Noti la differenza? Nella distinta base abbiamo una semplice relazione componente padre, mentre nella distinta base prodotto leggiamo la struttura del prodotto. Un buon esempio è l’articolo FR188. L’Offset del componente nella distinta base di prodotto ci informa quante righe sopra troviamo il genitore a partire dalla riga corrente, in altre parole 1 significa la stessa riga, 2 la riga sopra e così via.
Dobbiamo generare la distinta base del prodotto dalla distinta componenti che riceviamo dall’ERP.
Nell’esempio Excel disponibile per il download calcoliamo la distinta base prodotto in modo progressivo dal livello 0 fino al livello 9, nella mia esperienza più che sufficiente per molte aziende.
Se necessario, i calcoli possono essere facilmente adattati per ospitare più di 9 livelli.
Naturalmente, questi calcoli sono automatizzati, non è richiesta alcuna codifica umana.
In Production Scheduling abbiamo anche un modulo ottimizzato, il BOM Processor, veloce e senza limiti sulla profondità della BOM che viene offerto in bundle quando segui uno dei nostri corsi di formazione.
Per prima cosa, prepariamo un riepilogo degli ordini di vendita entro l’orizzonte che decidiamo (è un parametro nel foglio PARA)
In secondo luogo, solo per gli articoli nell’elenco sopra, prepariamo la distinta base di prodotto.
Terzo, esplodiamo gli ordini di vendita in base alla distinta base del prodotto, che viene eseguita tramite un Table template. Nello stesso Table template vengono elaborati i calcoli MRP: da lì calcoliamo il fabbisogno lordo totale per ogni componente.
Quarto, c’è il calcolo del consumo giornaliero futuro per ogni componente: il giorno di domanda. Questo è completato da un Table template dedicato
Quinto, confrontiamo le giacenze con il giorno di domanda per ciascun articolo: calcoliamo quanti giorni di domanda è il livello di inventario per ogni componente, o in altre parole i giorni di copertura per ogni articolo.
Sesto, in base alle classi di copertura memorizzate in un Parameter Template o importate in un Query Template, associamo gli articoli alle categorie di copertura, otteniamo anche alcune informazioni da Items (l’anagrafica articoli): costo, tipo di articolo, tipo di materiale.
Output
Report di inventario: per ogni classe riportiamo il valore e il numero di articoli; un’altra opzione più utilizzata è quella di tracciare il numero di elementi e il valore. Questo è un modello Pivot.
E un altro pivot template ci fornisce un report per tipo e valore di elemento.
Questo è uno strumento che può evidenziare opportunità di miglioramento:
- Copertura troppo bassa o assente: questo porta a una crisi da rottura di stock
- Copertura eccessiva: variazione della domanda o errori nella gestione del ciclo di vita del prodotto
- Nessun consumo: perché quei materiali sono lì?
Come utilizzare lo strumento di analisi delle giacenze in Excel: PS_InventoryAnalysisPro.xlsm
Scarica il file compilando il form in cima all’articolo. Ti invieremo immediatamente un link per il download via e-mail.
Estrailo direttamente sul tuo disco C. Dovresti avere una cartella simile a questa: C:\P-S_InventoryAnalysisPro. Perché questo? In questo modo non è necessario tenere conto del nome utente, che è diverso per ogni utente.
Troverai una cartella Dati con 5 file txt: questo è il set di dati, l’ultimo modello FEDT (DevTemplate_Professional_v4.1.6.xlsm) e lo strumento di analisi dell’inventario.
Cosa puoi fare con lo strumento:
- studiarlo come un esempio di sviluppo FEDT
- giocarci per vedere cosa succede se cambi i dati
- sostituire i dati fittizi nella cartella Data con i dati reali della tua azienda ed eseguire lo strumento di analisi delle giacenze per la tua azienda (è gratuito)
- usarlo come punto di partenza per analisi più sofisticate, ad es. tempi di consegna e variabilità della domanda
Questo è tutto. Anche se sembra abbastanza complesso, il modello di sviluppo rapido di Excel e le migliori pratiche che abbiamo sviluppato in oltre 20 anni di attività lo rendono robusto, semplice e veloce da utilizzare. Ed è conveniente anche in termini di costi di licenza del software: il Fast Excel Development Template è gratuito ed Excel fa parte della dotazione software standard in molte aziende.