Need to have Excel calculate profit margin based on variable...stuck!

I am trying to create an excel sheet that will calculate a variable based on a certain profit margin. It's for a manufacturing process where my formula looks like this:

Function 1 (cost)-----[(Material Cost*Part Weight)+(Hourly Labor Wage*CYCLETIME)+ ((CYCLETIME/60)*.75)] * Parts Needed = Cost
Function 2 (profit)----Revenue- Cost= Profit
Function 3 (profit margin)----(Profit/Revenue)*100= Profit Margin

Everything will be given in Function 1 except CYCLETIME. I am trying to have Excel do it tell me what CYCLETIME will be with 10% profit margin and the same for 30% profit margin.

I'm stuck on this and can't figure out how to get all three formulas to tie together!

Hello,

Just would like to know if all variables (excluding CYCLETIME) were given? Including Revenue.

Also, are you just inputting the profit margin, and would like Excel to calculate the CYCLETIME? If it is, seems like it would be just "combining" all 3 formulas

As dluhut says combine all 3 formulas and then use Goal Seek, by setting the Profit margin to 10% or 30%, and setting the by changing cell to the cell that contains the value for CYCLETIME

I have not been using Goal Seek for a very long time. Would appreciate if Gary could show it to us.

Anyway, answering your questions through formula,

First, we have to calculate what's the COST given the user's input of Profit Margin, and ASSUMING THAT, all things are the same, excluding CYCLETIME, since we're calculating it.

Since Profit Margin = (Profit/Revenue)*100 and Profit = Revenue - Cost, THEREFORE, Cost = 1 - ((Profit Margin * Revenue)/100)

Now that we have the value for COST given the user Profit Margin, Inserting the COST, on formula 1, we can thus find CYCLETIME, where the formula is,

CYCLETIME = {60*[(COST/PARTS NEEDED)-(MATERIAL COST*PART WEIGHT)]} / [(60*HOURLY LABOR WAGE)+0.75]

I hope this helps

Originally Posted by dluhut

I have not been using Goal Seek for a very long time. Would appreciate if Gary could show it to us.

Anyway, answering your questions through formula,

First, we have to calculate what's the COST given the user's input of Profit Margin, and ASSUMING THAT, all things are the same, excluding CYCLETIME, since we're calculating it.

Since Profit Margin = (Profit/Revenue)*100 and Profit = Revenue - Cost, THEREFORE, Cost = 1 - ((Profit Margin * Revenue)/100)

Now that we have the value for COST given the user Profit Margin, Inserting the COST, on formula 1, we can thus find CYCLETIME, where the formula is,

CYCLETIME = {60*[(COST/PARTS NEEDED)-(MATERIAL COST*PART WEIGHT)]} / [(60*HOURLY LABOR WAGE)+0.75]

I hope this helps

This looks good, but not quite there. I think that the CYCLETIME equation is slightly off (bolded * sign where + sign was)

CYCLETIME = {60*[(COST/PARTS NEEDED)-(MATERIAL COST*PART WEIGHT)]} / [(60*HOURLY LABOR WAGE)*0.75]

I will try to make an excel sheet that incorporates this formula and see if I can make it work. Be back soon! Thank you!

Originally Posted by djbeatsent

This looks good, but not quite there. I think that the CYCLETIME equation is slightly off (bolded * sign where + sign was)

CYCLETIME = {60*[(COST/PARTS NEEDED)-(MATERIAL COST*PART WEIGHT)]} / [(60*HOURLY LABOR WAGE)*0.75]

I will try to make an excel sheet that incorporates this formula and see if I can make it work. Be back soon! Thank you!

Hello,

The reason that it's (60*HOURLY LABOR WAGE)+0.75 is because from the moving the variables from Formula 1 =)

It seems like you know how to solve it on your own. Remember to mark this thread as solved, if not, you can always ask back. I'm sure, there's experts that can help you out =)