Excel Budget BOM Manufacturing Costs and Margins M15

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.

  1. Calculation of budgeted costs
  2. 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

01 Excel Budget BOM Costs and Margins Manufacturing M15

02 Excel Budget BOM Costs and Margins Manufacturing M15

03 Excel Budget BOM Costs and Margins Manufacturing M15

04 Excel Budget BOM Costs and Margins Manufacturing M15

05 Excel Budget BOM Costs and Margins Manufacturing M15

06 Excel Budget BOM Costs and Margins Manufacturing M15

07 Excel Budget BOM Costs and Margins Manufacturing M15

08 Excel Budget BOM Costs and Margins Manufacturing M15

09 Excel Budget BOM Costs and Margins Manufacturing M15

10 Excel Budget BOM Costs and Margins Manufacturing M15

Premium Management Tool

Adaptation is required.

Included in Management Control Service or ODOO & CONTROLLING

 

More information about Controlling Excel Tools

More Videos about Management Accounting

Industrial company financial manager

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.

Dani Granero

Controlling Consultant
Controller ODOO ERP

Dani Consultor Controller y ODOO

Email: dani@cashtrainers.com

More INFO and ways to contact

Leave a Comment