Excel Budget BOM Manufacturing Costs and Margins M15.
As I have repeatedly mentioned in various posts and videos, any company works mainly with 2 types of cost calculation systems.
- Calculation of budgeted costs
- Calculation of historical or real costs
Each calculation system has a different purpose.
Understanding what is expected to be achieved from each calculation system is the key to a successful implementation.
Let’s start with the calculation of historical or real costs, as its name indicates, we use historical information for its calculation, mainly sales.
Sales are calculated with the quantity and unit sale price, so if we compare the sale price with the product’s sales cost, we obtain the margin of that sale.
To calculate the cost, different evaluation criteria can be used, such as the average cost, fifo, standard cost…
As you may have deduced, this data is available in the ERP or management program.
Note: There are manufacturing companies that incorporate indirect manufacturing costs to the sales costs, the ERPS or management programs do not usually incorporate this functionality, so you will have to do it externally to the ERP.
The preparation of an analysis report of sales margins by products, customers, commercials, sales channels, geographical areas usually is based in historical information stored in the ERP or management program. The reports can be implemented directly from the ERP if it allows it or download the data to Excel for information processing and presentation.
Since we are using historical information, which means that they are transactions that have already occurred, the only thing we can do with this information is analyze what has already happened, therefore, we cannot make decisions to change it.
Yes, we could do exercises or create scenarios that answer the question “What would have happened if”
What we can also do is use this historical information as a basis to generate projections that allow us to make decisions based on what we expect to happen.
But to do this we cannot use the historical or real cost calculation system.
We need to build a budget cost calculation system.
The objective of the budgeted cost calculation system is to use standard costs, that is, budgeted or forecast costs, to make decisions in relation to sales prices, sale volumes, general expenses…
With this calculation system you can create all the scenarios you need and use it as a budget or goal to achieve.
To perform a budgeted cost calculation system, it is normal to use Excel, since it has great flexibility and the ability to quickly adapt to any requirement.
Budgeted cost calculation systems require high customization and adaptation according to the needs of each company.
Do you remember direct, indirect, material costs, staff hours, machine hours, variable costs, fixed costs…? Well, all these concepts we have to put into practice in a system of budgeted costs.
The theory is relatively simple, the difficult part is putting it into practice.
I have recently launched a budgeted cost calculation system for manufacturing companies, the M15 model, a standardized model that requires small changes to be adapted to any company that has production processes.
This tool is offered in any of the CONTROLLING services that I provide to the companies I work with.
Here I let you some images of the Excel calculation of manufacturing costs and budgeted margins M15
Premium Management Tool
Included in Management Control Service or ODOO & CONTROLLING
More information about Controlling Tools
- Wedding Budget Control M1 Free Excel Template
- Control and Justification of Commercial Expenses Excel Free Template
- Excel Manufacturing Standard Costs and Margins M10
- Documents Management Free Excel Template M1
- Travel Expenses Control M1 Free Excel Template
- Restaurants Sales forecast Free Excel Template
- Digital Marketing Dashboard Control M1 Free Excel Template
- Payments Forecast Control FREE Excel Template
- Cash Flow Control M1 Free Excel Template
- Training Courses Control Free Excel Template
- Budget Control for Events M1 Excel Template
- Decisions Sale Price Lists Calculation Free Excel Template
- Budget Control Excel YTD & FYForecast M15
- Excel Template Cash Flow Forecast M15
- Marketing Plan Free Excel Template
- Home Budget M2 Excel Template
- Budget Sales Control Free Excel Template
- Planning and Control Projects Costs and Profitability Excel FREE Template M15
- FREE Excel Balanced Scorecard 2KM15
- Retail Commerce Profit Analysis Free Excel Template
- Customer Invoices Free Excel Template
- Training Management Free Excel Spreadsheet
ERP ODOO Functional Consultant and Controller (Management Control & Controlling)