I’m getting asked about this more and more recently, as OneStream make a bigger sales push around financial planning. A large proportion of customers turning to planning in OneStream are manufacturing companies.

Before we go into a Bill of Materials (BOM)

When you are doing driver-based planning at a manufacturing company, you would naturally be drawn to some of the more obvious drivers like this:

• Energy prices & volumes
• Raw material prices & volumes
• Labour (hours allocated), variable cost and fixed cost components
• Machine maintenance & repair
• Production line allocation of hours/days to manufacture of specific products

Once you have identified the main calculations ( price * qty ), you then come onto the challenge of how do you calculate the cost of manufacturing each product. This is used for valuation of the movements in inventory.

Inventory Volume

Inventory Valuation (Cost)

Calculating the movements in inventory in terms of volume can be estimated from the effects in the table above. However, how do you value the products in inventory?

You don’t use selling price, because the selling price isn’t the production price.

You have to calculate all the components, energy, labour allocation, raw materials, and other costs used in each step in the production.

A Bill of Materials (BOM) is a list of ‘ingredients’ used to make a completed product ( or part-completed product which may be useful for IC sales of part-completed components).
It’s primary purpose is to ensure engineers use data in the BOM to ensure all the sub-components get sourced correctly, and the assembly is done using the correct ingredients.

Because completed products may have a complex series of manufacturing steps behind them, it is useful to think of them as a tree.

Bill of Materials Concept in the GolfStream Application

For this example we look at the UD2 (Products) dimension in the Golfstream app.

If you want to go into low-level product detail, such as SKU, then I wouldn’t normally put it into a dimension. You don’t want to be changing master data every time SKUs are added or removed, nor manage thousands of members in a dimension if you can help it.  So in that case the SKU table would be a relational table.

But product categories definitely belong in a dimension.

So if we zoom into how these are built, and how do we calculate the production cost for them?

In many manufacturing companies, there would be a bill-of materials that may be maintained in a separate dedicated software package. They would list the ingredients, and often specify a variable cost component and fixed cost component. The bill of materials would show a parent-child relationship, with quantities required to make a sub-component, then how that is made into a larger component, then eventually into a finished product.

SKU Table

You probably need to link the BOM to details about each ingredient. First you need a SKU table (not a cube dimension), which will be used for the lookup of SKU numbers to their description and other properties.

The BOM would describe a tree, which tells what is the correct quantity and mix of sub-components required in each step.

This illustration is a concept only, and only to get an idea.  What you see clearly though is there is a Parent-Child relationship (first two columns), a quantity, fixed cost component, variable cost component , and a unit of measure. Obviously the unit of measure ensures you multiple [price * volume] using consistent measurements, e.g. [price per kg] * [number of kgs].

A real BOM is more sophisticated that what’s shown here, so you really need to spend time understanding the manufacturing process at the specific customer to implement it correctly.

BOM and the Onestream TreeView component

Fortunately there is a Tree View component in OneStream , so you can visualise how components are put together to make a completed product.

Point this tree-view component at query on the BOM table, and now it becomes something much nicer to see…

Putting the BOM to use

By adding up the fixed and variable cost components, quantities, and understanding the measure of units, you can build up a complete cost of producing a finished product.

Because OneStream is able to blend detailed information in tailor-made tables (such as a BOM table like this) with financial cube data, you can quickly see what the effect on energy prices or labour costs would have on each product, and see how the profitability of certain products would vary based on fluctuations in the underlying drivers.

There are many ways in which you can experiment with the effects on costs of each of the ingredients behind a finished product. With the ability to create plans in various what-if scenarios , you can run variance reports on both the financial and operational variances, and be able to confidently explain the differences.