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%

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.

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.

"Chuck W" wrote:

> 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%
>
>

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.

Chuck,

Why not just rearrange your formula

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

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Chuck W" <(E-Mail Removed)> wrote in message
news:0c4c01c5186b$91deeb40$(E-Mail Removed)...
> 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%
>