I am looking for a way to convert annual sales figures to monthly. 
The challenge is to create a smooth growth profile.  So for example if
sales in Year 1 are 120 units and in year 2 they are 240 units, then
the average sale in each year is 10 and 20 units per month
respectively.  But in reality the first and last month of each year
will not be the same, assuming that the business is growing steadily. 
But there is no easy way to take annual figures and growth rates and
calculate the "smooth line" monthly figures that I can think of.  Can
you help?

---

Request for Question Clarification by wonko-ga on 17 Nov 2004 17:43 PST

Would a formula that produced the following results be of interest to
you?  The growth rate within each year is uniform from month-to-month
and is recalculated for each year.  The total sales for each year do
not have to be exact multiples one another, but I have included Year 3
of 360 to show you how the growth rates change each year.

If you would like the formula that achieves these results as the
answer, please let me know.

Sincerely,

Wonko

Month	1	2	3	4	5	6	7	8	9	10	11	12	Total
Year 1	1.54	3.08	4.62	6.15	7.69	9.23	10.77	12.31	13.85	15.38	16.92	18.46	120.00
Year 2	18.70	18.93	19.17	19.41	19.64	19.88	20.12	20.36	20.59	20.83	21.07	21.30	240.00
Year 3	22.64	23.98	25.32	26.65	27.99	29.33	30.67	32.01	33.35	34.68	36.02	37.36	360.00

---

Clarification of Question by robchob-ga on 18 Nov 2004 00:41 PST

This looks exactly like what I am looking for.  I will happily accept
the formula for this equation as an answer to my question

The approach I used was one of an arithmetic gradient.  The idea is
that a constant growth rate is applied to each period within a
particular year, with the growth rate being recalculated for each
year.  Month 1 = 1G, Month 2 = 2G,... Month 12 = 12G.

For Year 1, because we are starting from 0 in Month 0, the gradient =
120/78 or 1.538.  Then, to determine the value for each month in Year
1, simply multiply the number of the month by the gradient factor. 
Month 1 is 1.538, Month 2 is 3.077,...Month 12 is 18.462.

Now, because we want Year 2 to reflect the last value in Year 1 in its
first month, the gradient formula = [240 - (12*18.462)]/78 or 0.2367. 
Then, to determine the value for each month in Year 2, multiply the
number of the month by 18.462 and add the number of the month
multiplied by the new gradient factor to obtain the value.

The procedure we used to determine the values for the months in Year 2
can be applied to subsequent years as well.

If you would like the first month in Year 1 to be locked at a
particular value to start with (such as 3), you can use the formula
[120 - (12*3)]/66 and start with the starting value of 3 in Month 1. 
You can of course replace the 120 and 240 with any value you like as
well.

Sincerely,

Wonko

---

Clarification of Answer by wonko-ga on 18 Nov 2004 09:14 PST

The 78 comes from summing the numbers 1 to 12.  Similarly, the number
becomes 66 if you are locking the first period because the remaining
months only sum the numbers 1 to 11.

It is a violation of the Google Answers Terms of Service for
researchers to contact customers via e-mail.  This is not a problem,
however.  I have uploaded the spreadsheet I used to calculate the
results I provided earlier to the following address, where it is
available for download:
http://68.15.21.151/uploads/researchers/Gradient_Formula.xls.  Since
Google Answers is available to the public, I suggest you remove your
e-mail address to avoid receiving spam and other unwanted
communications.

I think the spreadsheet will greatly aid you in understanding what is
going on, but please request clarification if needed.

Sincerely,

Wonko

---

Request for Answer Clarification by robchob-ga on 18 Nov 2004 09:44 PST

Thank you

---

Clarification of Answer by wonko-ga on 22 Feb 2005 14:59 PST

You are welcome.