Split yearly figures to months using a distribution curve

[luisen]

Hi all,

I have been learning from this forum for years, and now it is time for my first post. Thanks in advance for your help and for all these years of teaching

I need to convert yearly figures to months, following a given trend, and then automate the workbook so it can be done as automatically as possible. The trend is given by the yearly figures, this is an example:

Y1: 500,000
Y2: 2,000,000
Y3: 3,000,000
Y4: 3,500,000

My attempts:
A) Using excel pre-defined trend functions, normally logarithmic had the best fitting
B) Using spline fittings, with a VBA code that tries to replicate what excel does with the "smoothing" of data lines in graphs

With these formulas, I am able to convert from discrete to continuous and interpolate to 1/12 data points

Once I know the functions, I can get the twelve points between years, and the next step is to convert these into months

However, these points represent the accumulated figures, and not the values of the months

How can I convert these to months?

I have tried to add all them up and get the % of their year that they represent, however the monthly trend then shows "steps" that would not happen in real life:

This is the data using the logarithmic distribution:

Period	Step (1/12)	y	Year	%	Monthly sales
1	1,08	679.295	2	4%	79.987
2	1,17	842.215	2	5%	99.171
3	1,25	993.891	2	6%	117.031
4	1,33	1.135.773	2	7%	133.738
5	1,42	1.269.051	2	7%	149.432
6	1,50	1.394.709	2	8%	164.228
7	1,58	1.513.572	2	9%	178.224
8	1,67	1.626.336	2	10%	191.502
9	1,75	1.733.597	2	10%	204.132
10	1,83	1.835.867	2	11%	216.174
11	1,92	1.933.591	2	11%	227.681
12	2,00	2.027.155	2	12%	238.699
13	2,08	2.116.898	3	7%	208.372
14	2,17	2.203.122	3	7%	216.859
15	2,25	2.286.091	3	8%	225.026
16	2,33	2.366.042	3	8%	232.896
17	2,42	2.443.187	3	8%	240.489
18	2,50	2.517.717	3	8%	247.825
19	2,58	2.589.803	3	8%	254.921
20	2,67	2.659.600	3	9%	261.791
21	2,75	2.727.249	3	9%	268.450
22	2,83	2.792.878	3	9%	274.910
23	2,92	2.856.605	3	9%	281.183
24	3,00	2.918.536	3	10%	287.279
25	3,08	2.978.770	4	8%	265.192
26	3,17	3.037.398	4	8%	270.412
27	3,25	3.094.503	4	8%	275.496
28	3,33	3.150.162	4	8%	280.451
29	3,42	3.204.447	4	8%	285.284
30	3,50	3.257.424	4	8%	290.000
31	3,58	3.309.153	4	8%	294.605
32	3,67	3.359.694	4	9%	299.105
33	3,75	3.409.099	4	9%	303.503
34	3,83	3.457.418	4	9%	307.805
35	3,92	3.504.697	4	9%	312.014
36	4,00	3.550.981	4	9%	316.135

<tbody>
</tbody>

Do you have any suggestion about how to convert these sales figures into months so when we plot the months they do not show these steps?

I know it could be done with solver, changing the coefficients of the curve function so that the point in December is close to that of January of the following year, but that changes the value of the months and I never manage to have all Dec-Jan fitting properly between years.

Many thanks for your help, this is driving me crazy!!

NOTE: if you have a completely different approach that you think that can work, please share it, I will adapt any working solution

I posted this question in OzGrid as well, but it is a bit urgent and I haven't got any answers yet:

Best

Luis

 

[Kenneth Hobson]

Welcome to the forum! Congratulations for figuring out how to post a pic and add the cross-post link in both forums.

I don't see a file attachment at ozgrid. In this forum, you can attach a link to a simple obfuscated file from free shared sites. e.g. dropbox.com

Not sure about your data. A Cdbl() or CDate() might be needed?

 

[luisen]

Hi Kenneth,

Thanks a lot for your help

I continued to search and search, and I found a paid solution:
https://www.youtube.com/watch?v=tHjqW5ic0jM

It is provided by SRS1Software and it is not very expensive. I will try it and let you know

Best

Luis