Rearrange this formula?

Blakeschi · #1 Jul 16, 2008, 06:00 AM

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 · Jul 16, 2008, 06:41 AM

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 · Jul 16, 2008, 06:49 AM

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 · Jul 16, 2008, 07:19 AM

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 · Jul 16, 2008, 07:32 AM

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 · Jul 16, 2008, 07:45 AM

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 · Jul 16, 2008, 07:55 AM

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 · Jul 16, 2008, 07:57 AM

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

Thank heavens you showed up!

Sorry about that, Blake.

Unknown008 · Jul 16, 2008, 08:01 AM

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

Blakeschi · Jul 16, 2008, 05:11 PM

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?



At Ask Me Help Desk you can ask questions in any topic and have them answered for free by our experts. To ask questions or participate in answering them you must register for a free account. By registering you will be able to: Get free answers from experts in any of our 300+ topics. Accept money for answers that you provide. Communicate privately with other members (PM). See fewer ads. Sign Up!