Help with creating a formula to manage cost of materials per month

**chris.gbrwn** · 03-31-2016, 01:26 PM

I am currently working on a cash flow projection template for my business. I would like some help with creating a formula in cell E9 of the first tab (using the data from the 2nd tab) to help me know how much I should expect to spend on raw materials in any given month based upon the amount of belts I project to sell and how much inventory I have remaining.

Assumption: I can make 10 belts from one hide of leather.

Example: I have enough leather to make 20 belts. I currently project that I will sell 35 belts in month 2. I know that I need to purchase two hides since I can only make 10 belts per hide. However, in month 3, I project to sell 15 belts. But I still have left over inventory (5 belts) from the 2nd hide I purchased and want to apply it to the projected sales before I purchase another hide.

If I need to clarify further, please feel free to let me know.

Thanks!

**dflak** · 04-01-2016, 05:29 PM

I think you are missing a piece: you need a bill of materials (BOM) for a belt. In other words, how much of each material goes into a belt. For example 1/10 of a hide, 4 Chicago Screws, 2 Rapid Rivets. 1 Roller Buckle, 1/50 jar of Gum Tragacanth and 1 /100 jar of Carnauba Cream. (I'm not even sure I know what I am talking about

.

I'll make some assumptions and press on.

**dflak** · 04-02-2016, 09:47 AM

I transcribed the bill of materials to Sheet 1. Row 2 has your original stock levels.

Rows 7, 14 and 21 calculate how much of each item you will need to meet the projected order. Basically use per belt times number of belts.

Rows 8, 15, 22 calculates how much stock you would have remaining if you made all the belts in the order. Some numbers may be negative meaning you will have to order more of that stock to fulfill the order.

Rows 9, 16 and 23 give a recommended order number. The formulas here vary a bit.

The sides are rounded to the next highest integer. So if you need 8 belts, it jumps to 1 hide. If you need 12 it jumps to 2. The formula is: =ROUNDUP(IF(C8>0,0,-C8),0).

The screws, rivets and buckles are simple: if you are short X number, you need to order X number.

The two last items, I presume come in jars or some other container and aren’t really discrete amounts. So if you are short 1/50th of a jar, you need to order a whole jar.

Rows 10, 17 and 24 are what you actually order. It may make more sense to order some items like screws, rivets and buckles in lots of 20, 25 or 100 or whatever.

Rows 11, 18 and 25 is the amount of each material you have left over after fulfilling the order. These get forwarded to the next month as starting stock for that item.

I leave it to you to fill in the rest of the months. You can copy rows 20 to 26 and paste it in row 27 and so on. The formulas should copy down correctly.
.