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.

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.