Calculating Percentage Change

[Will85]

Hello,

I am trying to standardize the way my organization calculates % increases for Revenues and Expenses.

Depending on the report, we display it as either a simple increase or decrease OR we display it as favorable or unfavorable.

For example, if Expense in Period 1 is $10 and Expense in Period 2 is $30, Expenses increased $20 (200%) from Period 1 to Period 2. Additionally, if I am reporting in terms of Favorability, Expenses are -$20 (-200%) unfavorable from Period 1 to Period 2 (Expenses got worse, unfavorable!).

It gets tricky when Zero and especially Negatives are involved. I believe I have come up with two formulas, one for computing increase/decrease, and one for computing favorable/unfavorable. But I can't be the only dealing with this problem, is there a better way to calculate?

My solution as follows:

For Increase or Decrease for either Revenue and Expense
1. Calculate the Increase: New-Old
2. Calculate % increase: IF(Old=0, 1*Sign(Increase),IF(Old<0, -1, 1)*((New/Old)-1))


For Favorable or Unfavorable for Revenue and Expense
1. Calculate the Favorability: A or B below (depending on if its Revenue or Expense)
2. Calculate % favorable: IF(Old=0, 1*Sign(Favorability), (Favorability/Old)*Sign(Old))

For Favorable or Unfavorable for Revenue ONLY
A. Calculate the Favorability: New-Old

For Favorable or Unfavorable for Expense ONLY
B. Calculate the Favorability: Old-New

I ran it through all the iterations I believe could happen, and got the results I would expect, thoughts?

OLD NEW Increase % Change Favorability % Change
Revenue ++ 10 30 20 200% 20 200%
Revenue ++ 30 10 -20 -67% -20 -67%
Revenue 0+ 0 30 30 100% 30 100%
Revenue +- 10 -30 -40 -400% -40 -400%
Revenue +- 30 -10 -40 -133% -40 -133%
Revenue -+ -10 30 40 400% 40 400%
Revenue -+ -30 10 40 133% 40 133%
Revenue -- -10 -30 -20 -200% -20 -200%
Revenue -- -30 -10 20 67% 20 67%
Revenue 0- 0 -30 -30 -100% -30 -100%
Revenue 00 0 0 0 0% 0 0%

Expense ++ 10 30 20 200% -20 -200%
Expense ++ 30 10 -20 -67% 20 67%
Expense 0+ 0 30 30 100% -30 -100%
Expense +- 10 -30 -40 -400% 40 400%
Expense +- 30 -10 -40 -133% 40 133%
Expense -+ -10 30 40 400% -40 -400%
Expense -+ -30 10 40 133% -40 -133%
Expense -- -10 -30 -20 -200% 20 200%
Expense -- -30 -10 20 67% -20 -67%
Expense 0- 0 -30 -30 -100% 30 100%
Expense 00 0 0 0 0% 0 0%

 

[Gerald Higgins]

Is this an Excel question ?
Or is it really a question about Accounting policy ?
If the latter, then it's going to come down to whatever you and your organisation decide is appropriate (and IF there are legislative requirements in this respect, then that may have some bearing).

What you have done seems reasonable, but my opinion probably counts for less than those of your organisation.

Also, consider the special cases such as where you have negative figures on one side of the analysis but not both sides, for example row 4 on Revenue . . . 10 -30
Are these really doing what you want ?
If yes, fine.