+ Reply to Thread
Results 1 to 7 of 7

how can I 'reverse engineer' an annual sales budget to give me monthly growth targets?

  1. #1
    Registered User
    Join Date
    05-01-2013
    Location
    cardiff
    MS-Off Ver
    Excel 2003
    Posts
    3

    how can I 'reverse engineer' an annual sales budget to give me monthly growth targets?

    I hope someone can enlighten me with this! basically I have a sales total required at the end of the next 12 months, and I need to schedule a monthly sales budget that will deliver month on month growth and take me from where I am today to where I need to be in 12 months - I have a starting point (ie the total of this last 12 months sales and profit) and I have target sum totals for the required total at the end of this next 12 months. What I need is a formula to calculate the monthly totals that will take me from the starting sum to the finishing sum charting steady growth between now and then (based on the working days in each month). I'm guessing this is perfectly doable, but I'm not a very advanced excel user so I'm not sure where to start!

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: how can I 'reverse engineer' an annual sales budget to give me monthly growth targets?

    Hi

    This is extremely simplistic but see how it goes.

    Subtract last year's results from the current year's target to get the increment. Then divide that by 365. (a)
    Divide last year's result by 12. This will give you a simplistic base sales amount for each month. (b)

    Then you take that base amount, and add the number of days in the month x the daily amount (a) and that becomes your monthly target.

    As an example for January you would have b+30*a
    Feb would be b + 28*a

    And so on.

    Or work out the number of working days each month, sum them, and instead of dividing the difference by 365, divide it by the total number of working days.

    Not pretty, but at least it is a straw man to start off discussions.


    rylo

  3. #3
    Registered User
    Join Date
    05-01-2013
    Location
    cardiff
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: how can I 'reverse engineer' an annual sales budget to give me monthly growth targets?

    thanks, I think the problem with that is that that's a flat rate, so say you have a total 10% increase required for the new years sales over last year - Doing it that way means you effectively have to end year 1 at x, then on day one of year 2 your sales have to instantly jump up by 10% and stay there at a constant level, so on a graph it's a flat line. In reality, your sales need to grow steadily, so they may start off in month 1 at only 1% more, then increase on an upward curve, perhaps finishing at 20% higher than the previous year, giving you a cumulative overall increase of the required 10% but on a sliding scale from month 1 to 12 (so an upward curve on a graph). Does that make sense?

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: how can I 'reverse engineer' an annual sales budget to give me monthly growth targets?

    Hi

    I did realise that the approach I gave was going to give a step, then put some working day (or calendar day) adjustments. However, your request didn't give any indication of the rate of growth, or whether it would be a linear growth or exponential (or something in between). In your last post you have at least given me some working paramenters (1% to 20%), but you don't advise the type of growth, or the starting point (ie the monthly result for June). Do you actually have the June monthly result to be able to have say a 1% sales increase for July?

    Better still, do you have the monthly history for the previous year or 2? If so, then you could at least follow the growth trend (assuming that there is one) and put on the incremental to that trend.

    rylo

  5. #5
    Registered User
    Join Date
    05-01-2013
    Location
    cardiff
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: how can I 'reverse engineer' an annual sales budget to give me monthly growth targets?

    Hi Rylo
    thanks for the reply again. I think the growth would need to be linear (keep it simple!) so the way I see it, month 6 would be at the averge increase level for the year, with the preceding months building evenly up to that, and the latter month effectively playing catch-up to give me the annual overall increase that's required. I may have to make a manual adjustment for December as it's a really short month. My start point and 'finish' numbers are (there are two sets)

    Start Finish
    42,848,000 47,194,000
    4,790,000 5,944,000

    Does that help put a bit more context onto it?
    thanks for your help!

  6. #6
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: how can I 'reverse engineer' an annual sales budget to give me monthly growth targets?

    Hello Polite, Please check the column C of attached file.
    Best Regards/VKS
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: how can I 'reverse engineer' an annual sales budget to give me monthly growth targets?

    Polite

    I'm not sure that I can come up with anything really meaningful unless I get a lot more detail. Just having annual numbers, and the broad premise that sales are going to be increasing over the year (especially when you want them adjusted by working days) is, for me, just too vague. Even the example by VKS above is based on the monthly results from the previous year.

    rylo

+ 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