In breve: questo articolo spiega come creare un budget di produzione semplice utilizzando il modello di sviluppo rapido di Excel. Calcoleremo i requisiti di costo di materiali, macchinari e manodopera per diversi scenari di domanda e confronteremo le ore di lavoro disponibili per centro di lavoro con il carico di lavoro. Inoltre, condividerò alcune nuove funzionalità aggiunte di recente al FEDT, insieme allo strumento complementare descritto nell’articolo.
Introduzione: il business e il caso d’uso
È comune stimare il fabbisogno produttivo utilizzando diversi scenari di domanda quando si pianifica il budget per l’anno successivo o lo si rivede a metà anno. Questo approccio aiuta a valutare i vincoli finanziari e operativi in base a diverse ipotesi di mercato.
Questo articolo si basa su una mia vera storia lavorativa, ma, per ovvie ragioni di riservatezza, il prodotto, l’attività e i dati sono fittizi.
Fabulous Scooters Ltd., produttore di monopattini elettrici, vende online articoli altamente personalizzati. Il suo sito web offre modelli per adulti e bambini, da città e da campagna, a lunga e media autonomia. Sono disponibili 5 colori di base, ai quali si aggiunge il sesto, il nero, e ogni cliente può scegliere una personalizzazione finale pressoché infinita, gestita come varianti.
Considereremo le varianti semplicemente come una fase di decorazione aggiuntiva nei percorsi dei prodotti finali, tralasciando le varianti pressoché infinite.
L’azienda sta acquistando i componenti degli scooter, immaginate qualcosa di simile a quello che vedete qui sotto.
Il telaio, i parafanghi e il manubrio vengono prima verniciati e poi assemblati insieme alla batteria, all’unità di potenza e a tutti gli altri componenti.
La fase finale della produzione è la decorazione secondo la variante configurata dal cliente. Non teniamo conto delle varianti nella distinta base, ma calcoliamo il carico di lavoro finale della decorazione inserendolo come operazione nei cicli di lavorazione.
Il team marketing ha preparato alcuni scenari sotto forma di previsioni giornaliere per l’anno successivo; hanno utilizzato un generatore casuale parametrico per distribuire la domanda all’interno dei 6 colori base e nell’arco dell’anno in base alla stagionalità prevista. Naturalmente, ho finto di essere il team marketing.
Il nostro compito è calcolare i requisiti di materiali, macchinari e manodopera per i diversi scenari in termini di costi e di effettuare una pianificazione approssimativa della capacità.
A causa dell’elevatissimo livello di personalizzazione, la capacità produttiva deve essere adattata al profilo della domanda.
Ho utilizzato alcune nuove funzionalità di Fast Excel Development Template: presentiamole subito.
Il Fast Excel Development Template e le sue nuove funzionalità
Il Fast Excel Development Template consente di creare strumenti basati su Excel completamente automatizzati, senza bisogno di alcuna codifica.
Un set di macro che lavora dietro le quinte crea il codice VBA per te, assemblando sei template di fogli di lavoro:
- Parameter, pensato per i dati memorizzati nella cartella di lavoro di Excel stessa
- Query, il recuperatore di dati, sempre più importante grazie a Power Query
- Table, la calcolatrice generica
- Stack, il tradizionale e flessibile impilatore di tabelle FEDT
- Pivot Table,, per riepiloghi e report
- ModuleList, l’orchestratore di macro e cartelle di lavoro automatizzate
Sono lieto di condividere con voi la seguente nuova funzionalità come anteprima tecnica della prossima versione FEDT 4.5:
- l’istruzione RUN quando ha 1 solo parametro lo ottiene come nome macro nella stessa cartella di lavoro;
- il modello ModuleList quando nella colonna della cartella di lavoro legge un @ esegue la macro nella stessa cartella di lavoro denominata in base al valore nella colonna della macro;
- il comando OUT accetta un nome di intervallo come cartella di output/nome file e utilizza il suo valore in fase di esecuzione (nome file di output dinamico);
- Allo stesso modo, TXT_Int, TXT_Loc, CSV_Int, CSV_Loc ora accettano come parametro cartella/nome file un nome di intervallo e utilizzano il suo valore in fase di esecuzione (nome file di output dinamico);
- È presente una nuova istruzione nella riga 6, FOR, che, insieme all’istruzione NEXT, consente di creare un ciclo FOR. Questa funzionalità è utile se si desidera che lo stesso foglio modello venga eseguito più volte. L’esempio seguente la utilizza per generare più scenari di domanda.
Il comando FOR nella riga 6 consente di impostare un valore iniziale, un valore di incremento e un valore finale per un conteggio. Ad esempio, “FOR 1 1 10” farà partire il contatore da 1 e aumenterà fino a 10 a incrementi di 1. I cicli FOR sono una sorta di iterazione, una parte fondamentale della programmazione, e questa funzionalità consente l’iterazione senza dover scrivere codice in VBA.
Ora, se non avete familiarità con il FEDT, queste funzionalità potrebbero risultare poco chiare. Tuttavia, se avete partecipato ai recenti contenuti di simulazione preparati dal mio caro amico e socio presso P-S Kien Leong, avrete immediatamente compreso il potenziale di queste nuove funzionalità e quanto semplifichino il flusso di lavoro di sviluppo.
Trovi sul nostro sito un articolo sulla release FEDT 4.4 and the FEDT 4.0 introduce funzionalità che vengono migliorate dalla prossima release FEDT 4.5.
C’è un’ulteriore funzionalità minore: FEDT imposta automaticamente una serie di scorciatoie da tastiera all’apertura della cartella di lavoro; queste hanno parzialmente sostituito quelle standard di Excel. Le scorciatoie FEDT sono comode durante lo sviluppo dei nostri strumenti, ma fastidiose per gli utenti abituali. Bene, per disabilitare le scorciatoie FEDT vai al foglio di lavoro PARA, aggiungi un parametro PARA_NoOnKey (colonna A) e impostalo su 1 (colonna B), salva, chiudi e riapri la cartella di lavoro: le scorciatoie FEDT sono ora disabilitate. Per riabilitarle, imposta PARA_NoOnKey su 0, salva, chiudi e riapri la cartella di lavoro.
Input
- Domanda: 4 scenari diversi, sotto forma di tabelle bidimensionali; dovremo normalizzarli in tabelle monodimensionali; inoltre le previsioni sono espresse per giorno con molti zeri: possiamo ridurre drasticamente i calcoli MRP rimuovendo le quantità zero e riepilogando la domanda per settimana, poiché stiamo considerando un anno intero.
- Calendari: le ore disponibili per ogni centro di lavoro, considerando uno, due o tre turni. Questo è molto utile per adattare la capacità al profilo della domanda. Anche i calendari richiedono una normalizzazione: tabelle bidimensionali, intestazioni divise sulle prime due righe. Anche in questo caso, utilizzo le funzioni di Power Query per rendere i calendari utili per l’elaborazione.
- BOM: la classica distinta base Padre-Componente-Quantità Per; dovremo elaborarla nel formato BOM Prodotto di Prodotto – Padre-Componente-Quantità Per, dove il Prodotto è l’articolo con requisiti indipendenti;
- Articoli: dati di base sui codici dei pezzi, in particolare i tempi di consegna
- Routing: le fasi di produzione, che collegano un tempo di lavorazione a un centro di lavoro
- Listino prezzi: il costo per unità del materiale di provenienza
- Centri di lavoro: informazioni sui centri di lavoro, costi orari relativi a manodopera e attrezzature, numero di operatori richiesti, numero di risorse che possono lavorare in parallelo
- Inventario: le quantità disponibili sono state tutte lasciate a zero, poiché è prassi comune mantenere invariati i livelli di inventario durante l’elaborazione del budget di produzione.
Ecco alcuni suggerimenti per te.
Trasformazioni Pivot e Unpivot di Power Query
In Power Query, le trasformazioni Pivot e Unpivot sono strumenti potenti per rimodellare i dati in base alle esigenze di analisi.
Il Pivoting prende i valori da una colonna e li trasforma in nuove intestazioni di colonna, riassumendo efficacemente i dati per categorie, ad esempio convertendo un elenco di vendite mensili in colonne separate per ogni mese.
L’Unpivoting fa l’opposto: prende più colonne e le trasforma in coppie attributo-valore, il che è particolarmente utile per normalizzare tabelle ampie in un formato ordinato e a colonne.
Insieme, queste operazioni consentono di passare senza problemi da strutture dati ampie a lunghe: il pivoting semplifica la preparazione dei set di dati per la creazione di report e la visualizzazione, mentre il unpivoting prepara i set di dati per ulteriori calcoli (ad esempio, l’unione con altre tabelle).
Altri suggerimenti per l’integrazione di Power Query con FEDT
Ho scritto alcuni utili suggerimenti sull’integrazione del modello di sviluppo rapido di Excel in questo articolo. Consiglio vivamente di leggerlo se non si ha familiarità con Power Query.
Il BOM processor ricorsivo menzionato nell’articolo linkato sopra è stato migliorato in termini di prestazioni ripulendo il codice M e rimuovendo alcuni passaggi inutili. In genere preferisco usare Power Query dal suo editor grafico, che rende molto facile capire cosa sta succedendo, ma in questo caso particolare, per comprendere il funzionamento del modulo nel dettaglio è necessario immergersi nel linguaggio M.
Sulla limitazione della complessità dei calcoli di Excel
Quando si lavora in Excel, è importante evitare calcoli eccessivamente dettagliati o non necessari, poiché possono rallentare le prestazioni e rendere i fogli di calcolo più difficili da gestire.
Ottimizza il tuo approccio semplificando le formule e utilizzando colonne di supporto per suddividere le attività in passaggi più piccoli.
Inoltre, quando possibile, sostituisci i calcoli ripetuti con valori statici: se usi correttamente il FEDT, questo viene fatto automaticamente dietro le quinte per tuo conto.
Anche l’aggregazione dei dati prima dell’analisi e l’utilizzo di strumenti integrati come Power Query e tabelle pivot possono ridurre il carico di elaborazione.
Concentrandoti solo sui calcoli realmente necessari, non solo migliori la velocità di Excel, ma rendi anche la tua cartella di lavoro più efficiente, più facile da controllare e meno soggetta a errori.
Considerazioni sulle performance
Tutti i file sono nel formato binario compresso di Excel xlsb: questo li rende più piccoli e più veloci da caricare.
Il flusso del processo
Se si trattasse di un MRP/CRP calcolato su un unico set di dati della domanda, avremmo:
- preparare il ProductBOM (prodotto-componente-padre_quantità per);
- far esplodere la domanda normalizzata attraverso il ProductBOM tenendo conto dell’inventario disponibile e dei tempi di consegna;
- utilizzare il listino prezzi di acquisto per valutare le spese dei materiali acquistati quando c’è una domanda netta;
- analizzare i requisiti netti dei prodotti lavorati internamente attraverso i percorsi e utilizzare le tariffe dei centri di lavoro per attrezzature e manodopera per calcolarne i costi.
Nel nostro caso, eseguiamo prima una volta i calcoli della distinta base del prodotto e poi ripetiamo i passaggi rimanenti per ogni scenario di domanda.
Ho già condiviso un processore basato su Power Query con 20 livelli di BOM che calcola il ProductBOM.
Inoltre, in passato ho condiviso un modulo MRP con la nostra community: lo sto riutilizzando anch’io.
Sia il processore BOM che l’MRP sono ottimi esempi di modularità; li uso come mattoncini Lego, il che ha notevolmente accelerato lo sviluppo, essendo entrambi piuttosto complessi. Ho utilizzato il modello ModuleList per eseguirli, anche se un’altra opzione è quella di utilizzare l’istruzione RUN alla riga 6 del foglio di lavoro, dove i calcoli della cartella di lavoro esterna vengono utilizzati come input.
Output
Una volta completati i calcoli, abbiamo alcuni report:
- il confronto degli scenari per mese nel DemandReport;
- il confronto dei costi di acquisto per mese in BuyReport e BuyChart;
- il confronto dei costi di produzione interni per mese nel MakeReport e nel MakeChart;
- il confronto tra capacità e carico di lavoro in RCCPReport e RCCPChart.
Nota che la nuova funzionalità RUN viene mostrata eseguendo una macro non FEDT che mostra per due secondi il numero di iterazione.
Ulteriori possibili implementazioni
Su questa solida base possiamo costruire di più, le opportunità più rilevanti che mi vengono in mente sono:
- per aumentare la granularità temporale dei report, ad esempio con bucket settimanali: li ho riepilogati per mese, ma ho mantenuto la granularità settimanale delle informazioni; a costo di un calo significativo delle prestazioni dello strumento, è possibile lavorare anche con bucket giornalieri rimuovendo il passaggio di riepilogo settimanale;
- Per studiare una logica di rifornimento delle scorte per i materiali di provenienza, in particolare quelli con tempi di consegna lunghi, potrebbe essere utile un approccio Min-Max o DDMRP. Allo stesso modo, una strategia di disaccoppiamento potrebbe funzionare bene per i prodotti finali con colori base e i componenti verniciati internamente, sebbene in questo caso sarebbero necessari anche i costi interni dei componenti e del prodotto;
- integrare un livellamento della capacità finita per il centro di lavoro con il massimo utilizzo e guidare le altre fasi di produzione in base ai tempi di consegna;
- per calcolare i costi in base a uno o entrambi i profili temporali di acquisto e produzione risultanti dai due miglioramenti sopra menzionati;
- per valutare la proiezione dei livelli di inventario nel corso dell’anno, in termini di quantità e costi.
Download dello strumento e conclusione
Per scaricare lo strumento compila il modulo sottostante, riceverai un link per il download.
Una volta scaricato, decomprimi il file zip compresso in C:.
Avrai una cartella denominata C:\P-S_Budget con il Fast Excel Development Template che ho utilizzato per creare lo strumento, il file P-S_Budget_v2.xlsb, che è lo strumento, e una sottocartella Dati, contenente i file txt di input.
In particolare, in questa sottocartella Dati trovi:
- i 4 scenari con i nomi Demand1.txt,-Demand2.txt,Demand3.txt,Demand4.txt
- Items.txt
- PriceList.txt
- BOM.txt
- Routings.txt
- WorkCenters.txt
- Calendars.txt
Ti prego di notare che la sottocartella Dati viene utilizzata anche per scambiare dati durante l’esecuzione dello strumento, pertanto dopo la prima esecuzione troverai altri file di testo al suo interno.
Inoltre, nella cartella C:\P-S_Budget trovi:
- ProductBOMpqRec_v2.xlsb, il BOM processor ricorsivo, lanciato dallo strumento principale
- MRPpq_v6.xlsb, il calcolatore MRP, lanciato dallo strumento principale
- P-S_Budget_Data.xlsb, la cartella di lavoro che ho utilizzato per generare il set di dati: se vuoi modificare qualcosa, modifica la cartella di lavoro, copia le tabelle al suo interno e incollale nel Blocco note, quindi salva come file txt nella cartella C:\P-S_Budget\Data utilizzando lo stesso nome del foglio di lavoro (ad esempio, foglio di lavoro Elementi salva come Elementi.txt)
La cartella C:\P-S_Budget\Data memorizza non solo il set di dati di input, ma anche i file txt generati dall’elaborazione.
È possibile modificare la cartella di lavoro modificando il contenuto di Menu!B10.
Abbiamo imparato come creare uno strumento di budgeting completo senza dover scrivere codice.
Ora puoi creare il tuo strumento di budget specifico o adattare questo al tuo caso d’uso senza dover scrivere codice.
E naturalmente, per qualsiasi dubbio o preoccupazione, contattatemi via email (gabriele at production-scheduling.com).