• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to split yearly sales data into monthly targets

N

Navenkumr

New Member
Hi,

I am trying to split yearly forecast into monthly sales. Here is what I have:

2014 sales - 100mn
2015 sales - 150mn (forecast)

Now i need to split 150mn into 12 months so that growth trend should be linear for example, Jan 15 - 10mn, Feb 15 - 12mn and so on adding upto 150mn at the end of Dec 15.

Appreciate any help on this!

Thanks!
Naveen
 
Hi Naven,
Welcome to the forum Chandoo.org

You can simply divide your sales by 12. But without sample file it is difficult to guess what you are looking for.

Can you post a sample of your data?

Regards,
 
Hi Khalid,

Thanks for your reply. Dividing by 12 will give me equal splits but i need a split which will have a growing trend. The sample file will have same information as mentioned in my query.
By growing trend I mean, the sales of future months will always be more than prior month. For example, sales of Dec>sales of Nov>sales of Oct and so on.
Finally they all should add upto 150mn. Hope my query is clear now.

Thanks,
Naveen
 
Hi Naveen ,

A straight line has an equation :

y = mx + c

where :

m is the slope of the line

c is the starting value , when x = 0

These are two variables ; to arrive at a line , we need at least one of them , since if you split up the total of 150 million into its constituent parts , it will be as follows :

January Value +
(January Value + Increment) + ' this will be for February
(January Value + Increment + Increment) + ' this will be for March
.
.
(January Value + Increment + ... + Increment) + ' this will be for December

Thus , we have the following total of all the above values :

January Value * 12 + Increment * ( 1 + 2 + 3 + ... + 11 )

= January Value * 12 + Increment * 66

You know this is equal to 150 million.

Since we have 2 variables viz. January Value and Increment , we need to know any one of the two ; if both are unknown , then the above equation cannot be solved unless you use the Solver addin.

Narayan
 
NARAYANK991 said:
Hi Naveen ,

A straight line has an equation :

y = mx + c

where :

m is the slope of the line

c is the starting value , when x = 0

These are two variables ; to arrive at a line , we need at least one of them , since if you split up the total of 150 million into its constituent parts , it will be as follows :

January Value +
(January Value + Increment) + ' this will be for February
(January Value + Increment + Increment) + ' this will be for March
.
.
(January Value + Increment + ... + Increment) + ' this will be for December

Thus , we have the following total of all the above values :

January Value * 12 + Increment * ( 1 + 2 + 3 + ... + 11 )

= January Value * 12 + Increment * 66

You know this is equal to 150 million.

Since we have 2 variables viz. January Value and Increment , we need to know any one of the two ; if both are unknown , then the above equation cannot be solved unless you use the Solver addin.

Narayan
Click to expand...
Hi Narayan,

Thanks for your detailed reply. Suppose we have last year sales figure available. Lets say in 2014 it was 100mn and in 2015 the forecast is 150mn. So is there a way we can use the growth figure (50%) for monthly splits?

So if we assume growth (50%) to be spread evenly throughout the year we get 50/12 = 4.2% growth on a monthly basis. Now we know the increment every month to reach 150 (sum of 12 months) by end of year. Would it be possible to write a formula to get monthly splits now?

thanks for your help!
Naveen
 
Hi Naveen ,

I don't think your suggestion will work , since the 100 , which you are taking as the starting value , is a cumulative for the previous year ; what we need is the value for December 2014 , or as already discussed , the value for January 2015.

Narayan
 
Month 1 = 150/6/13
Month 2 = Month 1 + Month 1
Month 3 = Month 2 + Month 1
.
.
Month 12 = Month 11 + Month 1

Total = 150
 
NARAYANK991 said:
Hi Naveen ,

I don't think your suggestion will work , since the 100 , which you are taking as the starting value , is a cumulative for the previous year ; what we need is the value for December 2014 , or as already discussed , the value for January 2015.

Narayan
Click to expand...
Thanks Narayan!

It seems I have to go with equal split.

Naveen
 
Hui said:
Month 1 = 150/6/13
Month 2 = Month 1 + Month 1
Month 3 = Month 2 + Month 1
.
.
Month 12 = Month 11 + Month 1

Total = 150
Click to expand...
Thanks Hui!

Could you please elaborate the solution? I do not quite get the method you have suggested.

Regards,
Naveen
 
Hi Naveen ,

It is a basic fact that the cumulative figure is the equivalent of the area under a curve , a straight line in this case.

The starting point or the end point will decide the slope of the line ; if neither of these is known , then it is a matter of trial and error. The starting cumulative figure of 100 is irrelevant since it can again result from any starting point , with any slope , for 2014. What is relevant is to know the starting value for one month , which can be either December 2014 or January 2015.

Narayan
 
You want the area of the curve (a straight line) = 150
So if you sum the numbers 1 to 12 = 78 each month will get that proprotion

So Month 1 will get 1/78th of the 150
Month 2 will get 2/78th of the 150
Month 3 will get 3/78th of the 150
.
.
and Month 12 will get 12/78th of the 150

I just simplified the maths in my previous example 1/6/13 = 1, 78th

Of course as Narayan said these must be added to your starting value
 
You must log in or register to reply here.
Back
Top