Gross margin index

[Dendras]

Hi,
I've got a tricky question on how to create a formula that calculate indexes from gross margin.

We are entering a landscape where you divide a negative on a negative, and in many cases, it's not an improvment, but a decline, so there you use an abs formula. The tricky part is to a have formula that take the 4 scenarios into considiration:

A. Postive / Postive
B. Postive / Negative
C. Negative / Postive
D. Negative / Negative

I've attached an excel sheet where you can see some dummy figures and the index issues.

If you have -10000 one month and -20000 the next month, the index would be -200%, but excel calculates thats as a +200% index.

I'm after a formula that handles all the four scenarios as stated above

 

[NARAYANK991]

Hi ,

It would help if you could indicate what the correct results would look like for the 4 scenarios :

Positive , Positive : More positive is + , less positive is -

Positive , Negative : clearly -

Negative , Positive : clearly +

Negative , Negative : More negative is - , less negative is +

If this is OK with you , see the attached file.

Narayan

 

[Dendras]

Hmm, after looking into the data a bit more, a saw an error.

If you look at month June, it states that the index is 19%.

June = 913
May = -4801

Infact, the result is greatly improoved by 525%

How would you guys solve that?

 

[NARAYANK991]

Hi ,

How do you arrive at 525 % ?

Narayan

 

[Dendras]

If you have earned 913 $ in June, while you have lost 4801$ in May, the difference between June and May would be 525% in a positive development

 

[NARAYANK991]

Hi ,

That does not answer the question.

Change is always calculated as (Now - Previous)/Previous , irrespective of the magnitudes and signs of the values.

If Previous was -4801 , and Now is 913 , the change is 5714 , which when divided by 4801 gives a change of 119 % , not 19 % as you have posted.

Narayan