Avoiding circular reference on formula

[Chuck W]

Hi,

There is a formula called retail ratio which is Product
Sales divided by the sum of Product Sales and Service
Sales. or RR = P/(S+P). I am trying to create a "What
If" chart which would allow someone to plug in values for
the Retail Ratio. The Service Sales are also known.
What I am trying to do is to solve for what the Product
Sales would be. So RR and S are know but P is not.
Since it is both in the numerator and denomiator, I am
trying to come up with a formula that solves for this.
Here is what my chart looks like. Can someone help?

Thanks,

Chuck

Person Service Sales Product Sales Total Sales RR
100 $20,000.00 40%
200 $30,000.00 40%
300 $40,000.00 40%
400 $50,000.00 40%

 

[Guest]

Hi, Chuck W.;
Not really "plug-in," as I apparently don't remember my Algebra that well,
but Excel's Goal Seek gave me these results:

Person Service Sales Product Sales Total Sales Ratio
100 $20,000.00 $13,307.80 $33,307.80 40%
200 $30,000.00 $19,990.53 $49,990.53 40%
300 $40,000.00 $26,615.61 $66,615.61 40%
400 $50,000.00 $33,330.27 $83,330.27 40%

Total Sales was Service Sales + Product Sales, for Ratio I used Product
Sales / Total Sales, and for Goal Seek, I asked Excel to change the value of
a "Ratio" cell to .4 by changing the value of the corresponding "Product
Sales" cell. I repeated the Goal Seek on the other three "Ratio" cells. Goal
Seek is on the Tools menu.
Hope this helps.
Ian.

 

[Guest]

Hi, Chuck W;
I have noticed that the Goal Seek numbers do not come out exactly correct.
For example, the second Product Sales number should have been $20,000, rather
than $19,990.53. But perhaps it is close enough for your purpose.
Regards,
Ian.

 

[Guest]

Hi again,
I just got Goal Seek to behave better. Tools> Options> Calculation>
Checked "Iteration," left the maximum iterations at 100 and reduced the
maximum change down to 0.000000. Goal Seek results are now:

Person Service Sales Product Sales Total Sales Ratio
100 $20,000.00 $13,333.33 $33,333.33 40%
200 $30,000.00 $20,000.00 $50,000.00 40%
300 $40,000.00 $26,666.67 $66,666.67 40%
400 $50,000.00 $33,333.33 $83,333.33 40%

That's better.
Regards,
Ian.

 

[Earl Kiosterud]

Chuck,

Why not just rearrange your formula

P = (RR * S) / (1 - RR)