Formula behind goal seek/solver solution

[rstowell]

Goal seek and solver are great to solve this equation, but I need to see the formula for further manipulation. Any ideas? Any help is appreciated.



50,000 revenue
00000 discount
-10,000 cogs
40,000 profit
80% margin
60% target margin

Solve to find maximum discount to achieve target margin

 

[Jon Peltier]

Those formulas are not exposed to the UI nor to VBA. How do you need to manipulate your model further?

 

[rstowell]

Those formulas are not exposed to the UI nor to VBA. How do you need to manipulate your model further?

I need to be able to show the formula to solve the equation (versus using the solver)

So, I guess my question is, what excel formula will calculate the maximum discount allowed to achieve a target margin when:

Revenue-Discount-CostofGoodsSold = Profit
and
Profit/(Revenue-Discount) = Margin

Revenue and Cost of Goods Sold is known, Discount is the variable, and 60% Margin is the Target

 

[Jon Peltier]

It's not a single formula, it's an iterative approach that gets progressively closer to the answer. You might find information about the precise approach used at the website for Frontline Systems (http://www.solver.com/), who programmed the tool for Microsoft.

 

[rstowell]

Thanks Jon. I was just hoping there was an Excel formula, something algebraic, that would arrive at the same result.

rs

 

[Ralajer]

For this particular problem you can solve the two equations algebraically

Revenue-Discount-CostofGoodsSold = Profit --- (1)

Profit/(Revenue-Discount) = Margin --- (2)

I just substituted Profit from (1) into (2) and solved for Discount

Discount = (CostofGoodsSold - Revenue + Margin*Revenue) / (Margin - 1)

I double checked it in MathCad.

Rob

 

[rstowell]

Rob-
Exactly what I was looking for (in the absence of an Excel function.)
Thank you so much!!
rs