Macro or formula for tracking raw materials

[MartaGH]

Hello,

I am not sure if what I want to create is possible but any assistance or guidance in the right direction will be very much appreciated. I am trying to create a spreadsheet that will allow me to track the aluminum and drywall needed in the fabrication of certain frames that we make.

The aluminum comes in bars that are 157.48 inches long. The drywall comes in sheets that are 4ft x 8ft.

What I am trying to achieve:

Two orders comes in, for example: One is for 8 units that are 18"x48" and the other is for 4 units 12"x12".

I have a row for each order. In the "Quantity" column I would input 8 and 4 respectively. In the "Horizontal Side" column, I would input 18" and 12" respectively and in the "Vertical Side" column, I would input 18" and 12".

Now the fun starts: I would like excel to somehow be able to calculate exactly how much aluminum and how much drywall I need to manufacture those frames. Meaning, the system should "know" that to make the 8 18x48 frames I will need 8 bars of aluminum:
- 2 bars are used for the 18" (18*4=144" out of the 157.48" bar and I am left with 2 pieces of scrap that are 13.48" long)
- 6 bars are used for the 48" (15 pieces of 48*3=144" out of the 157.48" bar, and I am left with 5 pieces of scrap that are 13.48" long plus 1 more 48" piece out of the 6th bar and I am left with a bar 109.48" long).

Then, the formula should capture that there are now 7 pieces that are 13.48" long plus one more piece that is 109.48" long so, when the next order gets calculated, it uses up those pieces of aluminum (if they are big enough) before it starts using up a new aluminum bar.

So, for order #2 , I would require a total of 16 12" pieces. The formula would recognize that there are 7 pieces that are 13.48" long that can be used to make 7 of the 16 12" frames, then realize that the remaining 9 pieces can be made out of the 109.48" bar.

Same idea with the drywall piece... the formula should calculate how many of the 8 18"*48" panels can be cut out of the 4ft x 8ft sheets and recognize that the 2 12"x12" may be cut out of the remaining pieces.

With the help of wwbwb I now have a formula that calculates how many bars are needed to manufacture the frames:

=IF([@Quantity]="","",ROUNDUP(([@Quantity]*[@[Inside Panel Horizontal]]*2)/(157.48-ROUNDDOWN(157.48-ROUNDDOWN(157.48/[@[Inside Panel Horizontal]],0)*[@[Inside Panel Horizontal]],0)),0)+ROUNDUP(([@Quantity]*[@[Inside Panel Vertical]]*2)/(157.48-ROUNDDOWN(157.48-ROUNDDOWN(157.48/[@[Inside Panel Vertical]],0)*[@[Inside Panel Vertical]],0)),0)

I have not been able to figure out how to do the rest and I am coming to you for help... Does any of you believe this formula/macro is possible?

Thank you again in advance!!

Marta

 

[Fluff]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[tcardwell]

Hi MartaGH,

Sounds like what you need is Bill of Material logic that lists all the raw material and labor that will be needed to assemble one unit. If one unit becomes an ingredient in another, higher level assembly then it can be nested inside the higher level BOM. Using Excel tables to accomplish this would be the best approach I think. Do you have any experience with manufacturing software applications that use a bill of material/recipe as a framework to list and organize raw material/ingredients?

TC