Bottom line, up front: This article explains how to build a simple manufacturing budget using the Fast Excel Development Template. We’ll calculate material, machine, and manpower cost requirements for several demand scenarios and compare available work center hours with the workload. On top of that, I’ll share a few new features recently added to the FEDT, along with the companion tool described in the article.
Intro: the business and the use case
It’s common to estimate manufacturing needs using a few different demand scenarios when planning the budget for the next year or reviewing it mid-year. This approach helps to assess the financials and operational constraints under various market assumptions.
This is based on a true work story of mine, but, for obvious non-disclosure reasons, the product, the business and the data are fictitious.
Fabulous Scooters Ltd. an electric scooter producer, sells highly customized items online, their webpage offers adults and kids, city and country, long range and regular range versions. There are 5 base colors applied to the sixth, black, and every customer can choose an almost infinite final decoration, managed as variants.
We will take into account the variants simply as an additional decoration phase in the routings of the final products and leave the almost infinite variants out.
The company is buying the components of the scooters, imagine a something like the imagine below.
The frame, the fenders and the handle are firstly painted and assembled afterward together with battery, power unit and all the other components.
The final production step is the decoration according to the variant configured by the customer. We do not take into account the variants in the bill of materials, we do calculate the final decoration workload having it as operation in the routings.
The marketing team prepared a few scenarios in the form of daily forecasts for the next year; they used a parametrized random generator to distribute the demand within the 6 base colors and over the year according to the expected seasonality. Of course, I pretended to be the marketing team.
Our job is to calculate material, machine and manpower requirements for the different scenarios in term of costs and to do a rough capacity planning.
The production capacity must be adapted to the demand profile because of the extremely high customization level.
I used a few Fast Excel Development Template new features: let’s introduce them first.
The Fast Excel Development Template and its new features
The Fast Excel Development Template (FEDT) enables the building of fully automated Excel based tools with zero coding.
A set of macros working behind the scenes build the VBA code for you by assembling six worksheet templates:
- Parameter, intended for data stored in teh Excel workbook itself
- Query, the data fetcher, becoming more and more important because of Power Query
- Table, the generic calculator,
- Stack, the traditional and flexibe FEDT table stacker
- Pivot Table, for summarizing and reporting
- ModuleList, the orchestrator of macros and automated workbooks.
I’m excited to share with you the following new feature as technical preview of the upcoming FEDT 4.5 release:
- the RUN statement can now run any macro in the same workbook. So you can pull in any VBA to be included- your custom code or the other standard FEDT macros. This allows you to run any macro using the no-code method from another template sheet- handy if you have everything in one workbook.
- the ModuleList Template can also run any macro in the same workbook, but as part of a routine running other workbooks. In the ModuleList, instead of a workbook name, use a @ runs the macro in the same workbook. This allows you to run any macro using the no-code method from the ModuleList template.
- the OUT command accepts a range name as output folder / filename. So you can output dynamic filenames, with a datetime stamp, for example.
- similarly, the TXT_Int, TXT_Loc, CSV_Int, CSV_Loc now accept as folder/filename parameter a range name and uses its value at runtime (dynamic output filename);
- there is a new row 6 statement, FOR which, together with the NEXT statement, allows for creating a FOR loop. This is powerful if you want the same template sheet to run multiple times. The example below uses this to generate multiple demand scenarios.
The FOR command in row 6 allows you to set a start value, step value and end value for a count. For example, “FOR 1 1 10” will start the counter at 1 and go up to 10 in steps of 1. FOR loops are a a kind of iteration, a key part of programming and this feature allows iteration without needing to code it in VBA.
Now, if you are new to the FEDT, well these features could be confusing. However, if you attended the recent simulation contents prepared by my good friend and partner at P-S Kien Leong, you are getting immediately the potential of these new features and how much the simplify the development workflow. Here you can find an article on the last template release FEDT 4.4 and the FEDT 4.0 introduces the features that are improved by the upcoming FEDT 4.5 release.
There is an additional minor feature: the FEDT automatically sets a number of keyboard shortcuts when opening the workbook; these partially supersede the Excel standard ones. The FEDT shortcuts are convenient while developing our tools but annoying for the regular users. Well, to disable the FEDT shortcuts go to the PARA worksheet,add a parameter PARA_NoOnKey (column A) and set it to 1 (column B), save, close and reopen the workbook: the FEDT shortcuts are now disabled. To reenable them, set PARA_NoOnKey to 0, save, close and reopen the workbook.
Inputs
- Demand: 4 different scenarios, in the form of two dimensional tables; we will need to normalize them to one dimension tables; furthermore the forecasts are expressed by day with a lot of zeros: we can dramatically reduce the MRP calculations by removing the zero quantities and by summarizing the demand by week, as we are considering a full year.
- Calendars: the available hours for each work-center considering one, two or three shifts. This is very convenient to adapt the capacity to the demand profile. The calendars require normalization as well: two-dimensional tables, headers split on the first two rows. Again, I’m using the Power Query functions to make the calendars useful for processing.
- BOM: the classic Parent-Component-Qty Per bill of materials; we’ll need to process it into the Product BOM format of Product – Parent-Component-Qty Per, where the Product is the item with independent requirements;
- Items: basic data about the part numbers, particularly the lead-times
- Routings: the production steps, linking a processing time to a work-center
- Price List: the cost per unit of the sourced material
- Work centers: the work centers info, manpower and equipment related hourly costs, number of operators required, number of resources that can work in parallel
- Inventory: the quantities on hand were all left at zero, as it’s common practice to keep inventory levels unchanged when processing the manufacturing budget.
Here are some tips for you.
Power Query Pivot and Unpivot transformations
In Power Query, the Pivot and Unpivot transformations are powerful tools for reshaping data to suit analysis needs.
Pivoting takes values from a column and turns them into new column headers, effectively summarizing data by categories—for example, converting a list of monthly sales into separate columns for each month.
Unpivoting does the opposite: it takes multiple columns and transforms them into attribute-value pairs, which is especially useful for normalizing wide tables into a tidy, columnar format.
Together, these operations allow you to switch seamlessly between wide and long data structures: Pivoting makes it easier to prepare datasets for reporting and visualization, while Unpivoting prepare datasets for further calculations (e.g. merging with other tables).
Other Power Query – FEDT integration tips
I wrote some useful tips on the integration of the Fast Excel Development Template in this article. I warmly recommend to read it if you are not familiar with Power Query.
The recursive BOM processor mentioned in the above linked article was improved in terms of performances by cleaning the M code and removing some useless steps. I generally prefer to use Power Query from its graphical editor which makes very easy to understand what is going on, but in this particular case, to understand how the module works in details you will need to dive into the M language.
On limiting Excel calculations complexity
When working in Excel, it’s important to avoid overly detailed or unnecessary calculations, as they can slow down performance and make spreadsheets harder to manage.
Streamline your approach by simplifying formulas, using helper columns to break down tasks into smaller steps.
Also replace repeated calculations with static values when possible: when using properly the FEDT, this is done on your behalf automatically behind the scenes.
Aggregating data before analysis and leveraging built-in tools like Power Query and PivotTables can also reduce the computational load.
By focusing only on the calculations that are truly needed, you not only improve Excel’s speed but also make your workbook more efficient, easier to audit, and less prone to errors.
Performance considerations
All the files are in the Excel compressed binary format .xlsb : this makes them smaller in size and faster to load.
The process flow
If it was a MRP/CRP calculated on one single demand dataset only we would:
- prepare the ProductBOM (product-parent-component_qty per);
- explode the normalized demand through the ProductBOM taking into account the inventory on hand and the lead times;
- use the purchasing price list to evaluate the purchased material expenses when the there is a net demand;
- explode the net requirements of the internally processed products through the routings and use the work center rates for equipment and manpower to calculate their costs.
In our case, first we run once the product BOM calculations and afterwards we repeat the remaining steps for each demand scenario.
I already shared a BOM 20 levels deep Power Query based processor calculating the ProductBOM and recursive version of it one year ago. You will find in the free download zip the recursive version: I recently improved its performances and now it is faster than the old 20 levels one.
Also, I shared in the past an MRP module with our community: I’m reusing it too.
Both the BOM processor and the MRP are great examples of modularity; I’m using them like Lego bricks, which really sped up development since they’re both quite complex. I used the ModuleList Template to run them, though another option is to use the row 6 RUN statement in the worksheet where the external workbook calculations are used as input.
Outputs
Once the calculations are over, we have a few reports:
- the scenarios comparison by month in the DemandReport;
- the purchasing cost comparison by month in the BuyReport and BuyChart;
- the internal manufacturing cost comparison by month in the MakeReport and in the MakeChart;
- the capacity vs. workload comparison in the RCCPReport and RCCPChart.
On side note, the new RUN capability is shown by running a non-FEDT macro that shows for two seconds the iteration number.
Potential further implementations
We can build more on this solid base, the most relevant opportunities from the top of my mind are:
- to increase the time granularity of the reports, e.g. with week buckets: I made them summarized by month, but I kept the weekly granularity of the information; at the cost of a significant performance drop of the tool, you can even work with daily buckets by removing the weekly summarization step;
- to study an inventory replenishment logic for sourced materials, especially those with long lead times, a Min-Max or DDMRP approach could be useful. Likewise, a decoupling strategy might work well for base color final products and internally painted components, though in this case, internal component and product costs would also be needed;
- to integrate a finite capacity leveling for the work center with the highest utilization and drive the other production steps by the lead-times;
- to calculate the costs based on one or both the purchasing and manufacturing time profiles resulting from the two improvements above;
- to evaluate the inventory levels projection along the year, in terms of quantities and costs.
Tool download and conclusion
To download the tool fill the form below, you will receive a download link.
Once downloaded, unzip the compressed zip file under C:.
You will have a folder named C:\P-S_Budget with the Fast Excel Development Template I used for building the tool, the P-S_Budget_v2.xlsb workbook, which is the tool, and a subfolder Data, containing the input txt files.
In particular, you find in the Data subfolder:
- the 4 scenarios named Demand1.txt, Demand2.txt, Demand3.txt, Demand4.txt
- Items.txt
- PriceList.txt
- BOM.txt
- Routings.txt
- WorkCenters.txt
- Calendars.txt
Please note that the subfolder Data is also used to exchange data during the tool runtime, therefore after the first run you will find more text files in it.
Also, in the C:\P-S_Budget folder your find:
- ProductBOMpqRec_v2.xlsb, the improved recursive BOM processor, launched by the main tool
- MRPpq_v6.xlsb, the MRP calculator, launched by the main tool
- P-S_Budget_Data.xlsb, the workbook I used to generate the dataset: if you want to change anything, edit the workbook, copy the tables in it and paste them in Notepad, then save as txt files in the C:\P-S_Budget\Data folder using the same name of the worksheet (e.g. worksheet Items save as Items.txt )
The C:\P-S_Budget\Data folder is storing not only the input dataset but also the txt files generated by the processing.
You can change the working folder by changing the content of Menu!B10.
We learned how to build a complete budgeting tool with zero coding.
Now you can build your own specific budget tool or adapt this one to your use case without coding.
And of course, get in touch with me by email for any doubt or concern (gabriele at production-scheduling.com).