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
Adaptation is required.
Included in Management Control Service or ODOO & CONTROLLING
More information about Controlling Excel Tools
- Excel Template Action Plan PRO
- What is an Excel Action Plan template and how can it help you?
- Customer Invoices Free Excel Template
- What are Production Costs
- Jooble Bad Practices and Reviews SEO and Backlinks Strategy
- Excel Templates Financial Dashboards Pack
- Download Example of Excel thermometer chart
- How an Employee Suggestion Program can help improve company processes
- Retail Commerce Profit Analysis Free Excel Template
- Budget Control for Events M1 Excel Template
- Cash Flow Control Excel Spreadsheet
- Employee Absences Free Excel Template
- Budgetary control System with Projections example
- CRM Free Excel Templates
- Cost Accounting. What benefits contribute to the company
- Payments Forecast Control FREE Excel Template
- Projects Control with Tasks Free Excel Template
- How the Budget Control service works
- Excel Template Matrix for Decisions
- Decisions Sale Price Lists Calculation Free Excel Template
- Excel Template Accounts Payable Control
- Excel Spreadsheet for SWOT Analysis
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