question: goal seek/what-if

[usr7501]

I need some advice. I am struggling with a problem where I have a fixed cost and a variable cost for an item and need to find the break even for the item. Is there any good examples you can recommend where I can check?

 

[Hui]

Setup a model like this

or See the attached file
The model breaks even where the Yellow Revenue line crosses the Grey Total Cost line at about 650 units

You can do a Goal Seek on the first Row
see below



which will show that the break even is at exactly 667 units

enjoy

 

[Hui]

You can also solve this from first principles
x = Qty (Unknown)
FC = Fixed Costs
VC = Variable Costs
UC = Unit Costs
UR = Unit Revenue

Profit = Total Revenue - Total Costs
Breakeven when Profit = 0
ie: Total Revenue - Total Costs = 0
Total Revenue = x * UR
Total Costs = FC + VC
Total Costs = FC + x * VC
x * UR - (FC + x * VC) = 0
x * UR - FC - x * VC = 0
x * (UR - VC) - FC = 0
x * (UR - VC) = FC
x = FC/(UR - VC)

in my example above
x = 10000/(25-10)
x = 10000/15
x = 666.67

 

[Hui]

Once you have these formula you can add them to the model
D7: =D2/(D5-D3)
D10: =D9*D5-(D2+D5*D3)


Then add some Data tables to plot the variabilities of key drivers



see attached file