Re: Calculating costs of Bill of Materials (BoM)

Paras_Sood · 06-06-2022 06:35 PM

Hi Community!

I am working on a bill of materials app to calculate our costs of production.

I have two tables, Products and Recipe:

Products schema:

Products table lists all products include Raw Materials, Finished Product and Work In Progress. Each Raw Material has a cost associated to it which I want to manually enter in Cost per kg for RAW MATERIALS ONLY.

BoM Cost represents the cost to company to produce that product based on it's bill of materials. A bill of materials might be made up of raw materials ONLY or it might be made up of a WIP (with it's own Bill of materials) and a raw material. I want to calculate BoM cost for all levels of BoM. After I have calculated the BoM Cost, I want to calculate cost per kg by dividing the BoM Cost by Batch Size.

Recipe Schema:

In recipe table, I have recipe SKU and product SKU to create a link between products in the products list. I enter the quantity of each ingredient required to make a Finish product or Work In Progress Item.

My goal is to use formulas to calculate BoM Cost (For all products with ingredients > 0) and populate cost per kg (manually for raw materials and calculated for WIPs and Finish Products)

How can I do this?

I tried the following to calculate BoM Cost (because it has similarity to the order and order details approach):

SUM(
  SELECT(
    Products[Cost per kg],
    [SKU] = Recipe[Product SKU]
  )
)

But of course this is a simple SQL statement. I believe there needs to be another select statement to get all products linked to a single recipe but I can't wrap my head around how to do it.

Any help appreciated! @TeeSee1

Paras_Sood

UPDATE:

I figured it out! I added two virtual columns to Recipe table

This is to refer to the price in the product list and then calculate the line subtotal by multiplying the price.

Using ingredient cost sub total, I am able to calculate the BoM Cost for a product. When I test my formula, it works but I do not see the updates in the backend (spreadsheet)

As a result, products that have multi-level BoM do not get updated because the BoM Cost is blank in the spreadsheet. How do I fix this?

Paras_Sood

Okay turns out I need to manually go and save each item for the price to get updated in the backend.

But my questions are:

1. How about the Cost per kg? I want to manually enter it if it's a raw material but I want to calculate it if it has a BoM. I don't think that there is an option to manually enter as well as have formulas. Would I have to split this field into two columns and see which one is populated?

2. Should I replace BoM Cost and Cost per kg with virtual columns? Because if I change the raw material cost for one item, I want the prices to be updated for all BoMs where that item is used automatically.

Thank you!

Paras_Sood

I have fixed all my problems, for anyone who faces a similar problem, I realised that adding virtual column for ingredient cost in the recipe table automatically updates all prices, and cost per kg splitting into two fields is the solution.

Paras_Sood

I thought I had solved the problem but with this approach, once the prices change I have to manually go and update all of the products using that item as an ingredient. I tried to change BoM Cost to a virtual column, however the following issue occurs:

When i use the virtual column BoM Cost to calculate Cost per kg (another virtual column with formula = BoM Cost VC/Batch size) then the app does not load.

If I only use BoM Cost VC and do not connect it to the formula which calculates costs per kilo then it works fine.

Is there a solution for this?

Steve

What do you mean by the app does not load? Is there an error? Can you share a screenshot?

Paras_Sood

Let me explain a bit more in detail.

I added two columns to find out cost per kg. One column is a column that I have added to the spreadsheet, "Cost per kg" where I can manually enter the costs if it's a raw material.

I have created a virtual column called "Cost per kg calc" with the following formula:

IF(
  ISBLANK([Cost per kg]),
  [BoM Cost]/[Batch Size],
  [Cost per kg]
)

This checks if I have entered any cost per kg manually (which is the case for any raw materials) and if I have not, then it calculates the cost per kg through the BoM Cost. If the product is not a raw material, there will be some BoM cost associated with it.

Formula for BoM Cost:

SUM(
  SELECT(
    Recipe[Ingredient Cost Sub Total],
    [_THISROW].[SKU]=[Product SKU]
  )
)

This works but the problem is that the BoM cost does not get updated automatically when I change a raw material price. I have to open the product, edit and save (without making any changes) and only then the cost gets updated. So, if I want to update the cost of a raw material, I would have to individually edit and save all the products where that raw material is used as an ingredient.

Alternatively, I tried to use a Virtual Column to calculate BoM Costs instead to get real time updates. It all works upto this point. When I replace the formula for "Cost per kg calc" with this virtual column of BoM Cost, then the app does not load. See screenshot below:

It stays on the syncing page with the progress bar loading slowly but steadily. However, after about 2-3 minutes I get this error:

I believe that it is getting stuck because BoM Cost uses ingredient costs which uses costs per kg calc which uses BoM Cost. But how do I overcome this problem and can update everything in real time?

I have a multi-level BoM (raw materials are used to create WIPs which are further used to create finished products)

Formula for ingredient cost sub total:

[Ingredient Cost per kg]*[Quantity]

Formula for Ingredient Cost per kg:

ANY(
  SELECT(
    Products[Cost per kg calc],
    [_THISROW].[Material SKU]=[SKU]
  )
)

Steve

@Paras_Sood wrote:
I believe that it is getting stuck because BoM Cost uses ingredient costs which uses costs per kg calc which uses BoM Cost. But how do I overcome this problem and can update everything in real time?

That's called a circular reference, which definitely won't work. There's no generic fix for that (beyond "just don't do it"). You're going to need to find a way to fix a value in the reference chain so it isn't a virtual column.

Paras_Sood

I have done that with BoM Cost, however, I am sacrificing real time updates on price changes for cost per kg calculation because when I update the raw material I have to go and individually update all items that use that raw material. Is there anyway I can break the circular reference chain but still get the real time updates?

Steve

That's entirely app-dependent, so there's no general answer.

Ryan_Mortimer

You can also write an action to go and "push" updates either to the parent row or all child rows. I utilise these sorts of actions a lot.

Paras_Sood

Hi Ryan,

Thanks for your reply, sounds like you might be on to something, can you please elaborate or provide me with an example? Your help is much appreciated!

Paras_Sood

I have a new train of thought now.

My only problem right now is that when I update a raw material record it does not automatically compute all values for cost per kg, and I need to manually go, edit and save items that use this raw material. My proposed solution is:

How about I create an action for Product table of type:

Data: execute an action on a set of rows

with a referenced list of rows where this raw material is consumed, and execute an action (still not sure what action this would be) that would ultimately not make any changes to any field but just trigger the Virtual Column to calculate the cost per kg. I can link this action to a workflow that would trigger everytime a form for product with category "Raw Material" Has been saved

Am I heading in the right direction?

Steve

If you update a value used by a virtual column, the virtual column will use the updated value automatically the next time a sync occurs. You shouldn't need to prompt it.

So long as the virtual column's App formula expression refers to its own column, you will have a circular reference.

Paras_Sood

Thanks for your reply, Steve. As I mentioned earlier, I have three fields that are stuck in a circular reference:

1. BoM Cost (I calculate the cost to make this product using the ingredients cost multiplied by the quantity)

SUM(
  SELECT(
    Recipe[Ingredient Cost Sub Total],
    [_THISROW].[SKU]=[Product SKU]
  )
)

2. Ingredient costs (Refers to cost per kg calc below to calculate the ingredient subtotal)

ANY(
  SELECT(
    Products[Cost per kg calc],
    [_THISROW].[Material SKU]=[SKU]
  )
)

3. Costs per kg calc (Virtual Column which reads another field, costs per kg, to check if I have manually entered a price or not which is the case only for raw materials, if i have not entered any price then it will automatically calculate the Cost per Kg using the following formula:

IF(
  ISBLANK([Cost per kg]),
  [BoM Cost]/[Batch Size],
  [Cost per kg]
)

BoM Cost uses ingredient costs which uses costs per kg calc which uses BoM Cost

I have fixed the problem by adding a BoM Cost Virtual Column as well as BoM Cost regular column. To break the circular reference I use BoM Cost regular column to calculate the cost per kg calculation, but like I said this does not give me real time updates if I increase the price of one raw material wherever that raw material is used.

Paras_Sood

While typing out the problem I had another idea which I decided to have a crack at:

Instead of keeping two columns with the same formula "Bom Cost" and "BoM Cost VC", I have created an action which copies the value of BoM Cost VC to BoM Cost. This updates the BoM Cost and hence all the calculations are processed. Therefore currently I have reduced updation of Cost per kg from two steps (opening and saving the form) to one step (just press the action button)

Now what I am trying to do is to somehow trigger this action for all referenced products whenever the price is updated.

I have created two actions:

Action 1

Update cost per kg (Data: Set the values of some columns in this row)

where I set BoM Cost VC to BoM Cost

Action 2:

Update prices

Data: execute an action on a set of rows

Referenced table: Products

Referenced rows: REF_ROWS("Product inline view", "Material SKU")
Referenced action: Update cost per kg

However this does not work.

I want to ultimately connect this to the form saving trigger where whenever the price is updated the actions are triggered.

Any ideas?

Steve

In what way doesn't it work?

Did you use the Test feature in Expression Assistant to see if your REF_ROWS() expression produces the expected results?

Paras_Sood

Thanks Steve, I tried out the test feature and it turned out that my ref rows function was not getting the product data correctly. That's fabulous and I have fixed that as well now.

However, now we run into the perpetual problem of propogating the change through different tiers.

For example:

I use sugar to make chocolate, and I use chocolate to make chocolate peanuts.

If the price of sugar increases, I can use my method to update the cost per kg of chocolate. However, I still have to manually go and click on the action to update the price of chocolate peanuts. Any suggestions to fix?

Paras_Sood

Only way that I can think of is to create an action to select all products and run the row level action to update prices per kg. Takes lots of time and inefficient though but does the job. Ideally I only want updates to items that form a tree.

Paras_Sood

I need a while loop with exit condition that "consumed to make = 0"

do you think I can use group actions to achieve this? I can run the data:execute an action on a set of rows multiple times for each item. Seems far fetched but i'm just thinking out loud.

TeeSee1

I did not have enough energy to read the whole thread...

Here is what I created.

https://www.appsheet.com/portfolio/3401559 called BOM.

The way I did is to add another table called BOM because I did ran into circular references with the original two tables also.

table recipes is a child table to BOM

Products of categories, "F (Finished Goods)" and "WIP", have an entry in BOM. recipes store non F materials and required quantities.

Though I have a bunch of non VCs defined in the spreadsheet, I have mostly done my work using VCs because of refresh timing.

You are welcome to play with it if it looks like what you are looking for..