[Blakeschi]

I need help with this formula I created. Right now, it is finding a variable called NPN, which will equate for example to 35 cents.

NPN = (((Cover Price * Sale) * (1-.45)) - ((Sale * Cover Price) * .04) - (Draw * .30) - (Promo Money)) / Sale

What I need to rearrange is to now find an unknown of Sale if I want NPN to equal $1.00. So in other words, what does my sale have to be if I want NPN to equal $1.00. Problem is Sale is used in multiple places that I can't figure out how to isolate it.

Any suggestions?

Thanks!

[shw3nn]

From my initial scan, my thought is:

You can not isolate Sale as in, turn this into an equation where you have Sale = some equation.

You can turn this into a quadratic equation where Sale would be the x and you would have x^2, an x and a constant.

[Blakeschi]

Ok. I'll use any method as long as it works. Right now, I'm coding it into Excel VBA to make it find Sale, but if there is a way to just use a formula instead of VBA code; I'm all for it!

Can you please elaborate? Thanks.

[shw3nn]

Just multiply everything by Sale. You'll end up with Sale and Sale^2 and some constants.

Are you not familiar with quadratic equations?

[Blakeschi]

Thanks, but wouldn't that make Sale equal to two different numbers? That wouldn't work if I am using this formula to calculate a P&L.

Or am I thinking of quadratic equations wrong?

[Unknown008]

Let C=Cover Price, S=Sale, D=Draw, P=Promo Money.

Then

NPN = (((C*S) * (1-0.45)) - ((S*C)*0.04) - (D*0.30) - (P)) / S

S*NPN = (((C*S) * (1-0.45)) - ((S*C)*0.04) - (D*0.30) - (P))

NPN*S = 0.55C*S - 0.04C*S - 0.30D - P

NPN*S - 0.55C*S + 0.04C*S= - 0.30D - P

(NPN - 0.55C + 0.04C)*S = - 0.30D - P

S = (- 0.30D - P)/(NPN - 0.55C + 0.04C)

(simplifying... ) S = (P + 0.30D)/(0.55C - NPN - 0.04C)

Therefore, your Sales will be :
((0.03 * Draw) + (Promo Money))/((0.55*Cover Price) - NPN - (0.04 * Cover Price))

[shw3nn]

Slightly wrong, I think.

It'll be two different possible answers. It won't be two different answers that are both the correct answer. One will probably be negative and you'll know right off the bat that isn't the correct one.

[shw3nn]

Damn, unknown, I was totally seeing those parentheses wrong.

Thank heavens you showed up!

Sorry about that, Blake.

[Unknown008]

Never mind. Constantly doing stuff like quadratic makes you see it everywhere sometimes.

[Blakeschi]

Hey guys. Sorry for the late reply. I tried the formula and it's not correct.

For instance, here are my known variables:

Draw = 33,838
Promo = $36,110
Cover Price = $5.99
NPN = $1.00

Using those variables, I plugged them into the following formula:
((0.03 * Draw) + (Promo Money))/((0.55*Cover Price) - NPN - (0.04 * Cover Price))

And I got 12,648. However, I solved this instance manually and found Sale should have been around 22,600 to equal about a $1.00 NPN.

Any suggestions?

[Unknown008]

NPN = (((C*S) * (1-0.45)) - ((S*C)*0.04) - (D*0.30) - (P)) / S

The bolded part is the one that I'm not sure. Is that 'one minus 0.45'?

[Blakeschi]

I figured it out. I ended up coding it in Excel VBA. For anyone interested, here is what I ended up with:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim NPN As Double, Sale As Long, CoverPrice As Currency, Draw As Long, Promo As Currency, x As Long

x = 2

Do Until x = 20
If x = 10 Or x = 11 Then Go to nextx

NPN = Worksheets("Sheet1").Cells(42, x)
Sale = Worksheets("Sheet1").Cells(32, x)
CoverPrice = Worksheets("Sheet1").Cells(4, x)
Draw = Worksheets("Sheet1").Cells(6, x)
Promo = Worksheets("Sheet1").Cells(41, x)

Select Case Worksheets("Sheet1").Cells(42, x)
Case Is < 1
Do Until NPN >= Worksheets("Sheet1").Range("A44")
Sale = Sale + 1
NPN = (((CoverPrice * (Sale) * (1 - 0.5)) - ((Sale * CoverPrice) * 0.04) - (Draw * 0.3) - (Promo))) / Sale
Loop
Case Is > 1
Do Until NPN <= Worksheets("Sheet1").Range("A44")
Sale = Sale - 1
NPN = (((CoverPrice * (Sale) * (1 - 0.5)) - ((Sale * CoverPrice) * 0.04) - (Draw * 0.3) - (Promo))) / Sale
Loop
End Select
Worksheets("Sheet1").Cells(43, x) = Sale
nextx:
x = x + 1
Loop

End Sub