+ Reply to Thread
Results 1 to 6 of 6

growth formula to predict sales for this year

  1. #1
    Registered User
    Join Date
    11-24-2007
    Location
    Manchester, UK
    Posts
    9

    growth formula to predict sales for this year

    Hey guys,

    Sorry about this being in the wrong section (if it is). Well excel is never my strong point.

    What I am trying to do is, do a growth formula to predict sales for this year. I currently have previous years sales for 4 years and the annual growth is 3% per year.

    The data values are
    Year 03 - 463
    Year 04 658
    Year 06 450
    Year 07 126

    The idea I had was to use an average formula and then * it by 0.03. But I have been told there are other, better methods. Any suggestions

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,165
    Quote Originally Posted by ipodman
    Hey guys,

    Sorry about this being in the wrong section (if it is). Well excel is never my strong point.

    What I am trying to do is, do a growth formula to predict sales for this year. I currently have previous years sales for 4 years and the annual growth is 3% per year.

    The data values are
    Year 03 - 463
    Year 04 658
    Year 06 450
    Year 07 126

    The idea I had was to use an average formula and then * it by 0.03. But I have been told there are other, better methods. Any suggestions
    Who told you there is a better way without telling you what it is?
    Maybe check out the growth formula in excel help,
    course with your example, there is no growth

  3. #3
    Registered User
    Join Date
    11-24-2007
    Location
    Manchester, UK
    Posts
    9
    Quote Originally Posted by davesexcel
    Who told you there is a better way without telling you what it is?
    Maybe check out the growth formula in excel help,
    course with your example, there is no growth
    One of my friends but i have not had the chance to talk to them. But the number i gave you were forr one item over a period of 4 years.

    the forumula I put down, would that work better?

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,165
    Quote Originally Posted by ipodman
    One of my friends but i have not had the chance to talk to them. But the number i gave you were forr one item over a period of 4 years.

    the forumula I put down, would that work better?
    I am not sure, because as the yrs go by the numbers are decreasing, not really possible to show growth
    but if you want to use the average formula then go
    =average(range)*1.03

  5. #5
    Registered User
    Join Date
    11-24-2007
    Location
    Manchester, UK
    Posts
    9
    Quote Originally Posted by davesexcel
    I am not sure, because as the yrs go by the numbers are decreasing, not really possible to show growth
    but if you want to use the average formula then go
    =average(range)*1.03
    As i do that, would i add a round function to the end to make sure its a full number?

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677
    Growth is an exponential function. Yours is negative, trending down at 26% per year.

    If you plot your revenue versus time on an x-y (scatter) plot, you can add an exponential trendline (right-click on the series, Add trendline) to see the growth curve, and add the equation to see the parameters. An exponential trendline is the best fit of the function y = a * exp(k*x) solved for a and k. The parameters for your data are

    y = 1611 * exp(-0.298 * x)

    That's equivalent to y = 1611 * 0.74 ^ x, which says that each year will have 74% of the prior year's revenue.

    You can use the GROWTH function to predict future revenues:

    Please Login or Register  to view this content.
    ... which yields,
    Please Login or Register  to view this content.
    BTW, did you notice you're missing data for year 5?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1