March 26, 2009

Formula to calculate growth – with positive and negative numbers

Filed under: Definitions,Excel — Tags: — Sandi Mays @ 12:17 am

We track Annualized Revenue, Growth and Cashflow generation on a quarter over quarter and year over year basis to determine the health of the business.  If your calculation has both positive and negative numbers, you will need to use the Absolute Value* function in Excel.

Example 1:

  • 1Q Revenue: $400K
  • 2Q Revenue: $410K
  • The formula is (410-400)/400 = .025 or 2.5% per quarter.  Annualized the growth is 2.5%*4= 10%
  • The excel formula is (1Q-2Q)/ABS(1Q)*4

Example 2:

  • 1Q EBITDA: ($100)K
  • 2Q EBITDA: $50K
  • The formula is (50 – (100))/100 = 1.5 or 150% per quarter.  Annualized the growth is 150%*4=600%
  • The excel formula is (1Q-2Q)/ABS(1Q)*4

Example 3:

  • 1Q EBITDA: $50K
  • 2Q EBITDA: ($100)K
  • The formula is ((100)-50)/100 = -1.5 or (150%) per quarter.  Annualized the growth is (150%)*4=(600%)
  • The excel formula is (1Q-2Q)/ABS(1Q)*4

Using the “ABS” or Absolute value formula in the denominator is necessary for Example 2 to work.

* Absolute value is its numerical value irrespective of its sign. So, for example, 400 is the absolute value of both 400 and (400)

6 Comments »

  1. This seems to work but it doesn’t. Try using a lower negative value in your Q1 and you will see that the % variance actually decreases the further you pull away rather than growing.

    In above mentioned example: Q1 = -100 and Q2 = 50 your output is: 150%. Now if it were -200 instead of -100 then the output becomes:
    125% which is ridiculous. It should be more not less…

    Comment by eric — June 2, 2009 @ 6:06 am

  2. The first explanation worked for me

    Comment by Miss Tee — January 14, 2010 @ 5:15 am

  3. Actually to show growth, the excel formula should be Q2 – Q1, not Q1 – Q2. Eric is correct, which raises the point of how meaningful it is to show growth in % when you move from negative to positive or vice versa. The WSJ just marks it as a P(profit) or L(loss) until the numbers are both positive.

    Jonathan

    Comment by Jonathan — July 12, 2010 @ 6:38 am

  4. What Eric says is true. But that doesn’t solve the problem.

    Comment by usws — October 23, 2010 @ 11:33 pm

  5. In perusing through some of the cost centers within the Talent Chaser Data Repository,I noticed that the Revenue and Expenses variances in some cases were positive and should have been reflected as a negative and vice versa. Is it possible for you to have someone quickly go through the cost centers to ensure this is correct as naturally it will cause the aggregate numbers to appear out of synch

    Comment by sonia — June 23, 2011 @ 8:52 am

  6. Greetings I am so happy I found your website, I really found you by error, while I was looking on Bing for something else, Anyhow I am here now and would
    just like to say cheers for a fantastic post and a all
    round interesting blog (I also love the theme/design), I don’t have time to look over it all at the moment but I have bookmarked it and also included your RSS feeds, so when I
    have time I will be back to read a lot more, Please
    do keep up the great work.

    Comment by mudanzas — January 11, 2013 @ 2:09 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress