how many more sales to hit target percentage 2

[damian2013]

Hi I'm trying to use excel to tell me how many more sales are needed to hit the 81% target.
the formula also needs to taken in loss leads as if this increases the more sales required.

sales loss leads month todate target sales needed
13 4 76.47% 81.00%


thanks

 

[SkipVought]

Hi,

What are you saying? Clear as mud!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[damian2013]

ok format went messy

sales target = 81%
current save = 13 (Variable daily)
current losses = 4 (Variable daily)
current save rate = 13 saves / 17 total loss & saves = 76.47%

I need a formula to tell me how many more saves are required againts the total number of saves and loss to hit the target of 81%

 

[damian2013]

so

cells
A1 = saves of 13
B1 = loss of 4
C1 = current save rate = =A2/SUM(A2:B2) = 76.47%
D1 = target 81%
E1= number of saves needed to hit the 81% target

 

[SkipVought]

So do you remember 10[sup]th[/sup] grade algebra?

sales = 13
loss = 4

SalesRate = sales/(sale+loss)

Let
x = the additional sales needed
sales_target = .81

Then

sales_target = (sales+x)/(sales+x+loss)

Solving for x
[tt]
=(NewRate*(sales+loss)-sales)/(1-sales_target)
[/tt]
BTW, I'm using Named Ranges based on the names you have associated with each data element

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[1726]

To calculate the exact no. of additional saves use: =((A1+B1)*D1-A1)/(1-D1)

To round this up to a whole number use: =ROUNDUP(((A1+B1)*D1-A1)/(1-D1),0)

Cheers

 

[damian2013]

Thanks All liked the roundup