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.
See example system of Calculation of historical or real costs
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
Adaptation is required.
Included in Management Control Service or ODOO & CONTROLLING
More information about Controlling Excel Tools
- The 3 Key Models to avoid the Economic Crisis in Companies
- Free Excel Template Break Even Point Calculation
- Download Example of Excel thermometer chart
- Excel Template for Performing a Net Worth Analysis
- Benefits of Using an Excel Dashboard Speedometer Template
- What is Contract Management Excel Template and download a spreadsheet
- Pipeline CRM Excel spreadsheet
- Project Management Excel Templates
- What is an Excel Action Plan template and how can it help you?
- How to use an organizational chart KPI Excel template to improve your business performance
- Excel Financial Scorecard or Dashboard. What you need to know
- What is a Decision Matrix and Example Excel Template
- Advantage cost calculation with Excel in companies
- Planning and Control Projects Costs and Profitability Excel FREE Template M15
- Employees Best Practices Improvements Requests M2 Free Excel Template
More Videos about Management Accounting
Industrial company financial manager
Dani is helping us to use ODOO more efficiently, we are rapidly leveling up with the ERP. It is also preparing us very useful analysis and control reports and outstanding management tools. Always available by phone or email, willing to help and collaborate in everything that is proposed. Very professional and fast work. A key service for our company.
Controller ODOO ERP
Plantillas Excel Controlling Gratis