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

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 =)