Project monthly data based on annual growth rate - Microsoft Community

i have data 2016 ex. 100000 sales unit , annual growth rate 40%. on first 6 months of 2016, actual sales unit 60000 (30% growth against first 6 months 2016).

how project , distribute next 6 months sales data 40% growth against 2016.

rp wrote:

i have data 2016 ex. 100000 sales unit , annual growth rate 40%. as on first 6 months of 2016 [sic; 2017?], actual sales unit 60000 (30% growth against first 6 months 2016). how project , distribute next 6 months sales data 40% growth against 2016.

absent other information, there "infinite" (very large) number of ways distribute remaining expected sales in 2017.

i prorate sales in second 6 months of 2017 according monthly proportion of sales in second 6 months of 2016.  demonstrated in following table.

formulas:

b3: =round(c3/(1+d3), 0)

b4: =b2 - b3

c2: =round(b2*(1+d2), 0)

c4: =c2 - c3

c5: =round($c$4*b5/$b$4, 0)

c10: =c4 - sum(c5:c9)

copy c5 c6:c9

formulas weights:

b5: =round($b$4*b13/$b$19, 0)

b10: =b4 - sum(b5:b9)

b13: =rand()

b19: =sum(b13:b18)

copy b5 b6:b9

if total 2016 sales 100,000 , expect 40% growth (d2), total 2017 sales expected 140,000 (c2).

if sales first 6 months of 2017 60,000, sales second months expected 80,000 (c4), remainder of 140,000.

if sales first 6 months of 2017 represents 30% growth (d3) over same period of 2016, sales first 6 months of 2016 46,154 (b3).

therefore, sales second 6 months of 2016 53,846 (b4), remainder of 100,000.

presumably, have month data second 6 months of 2016 (b5:b10), should total 53,846.  not need formulas , the formulas in b13:b19.

absent data in original posting, "invented" distribution of sales second 6 months of 2016 demonstration purposes, based on random weights in b13:b19.

the sales of 80,000 second 6 months of 2017 are distributed each month in same proportion corresponding month of 2016 (c5:c10).  example, month #7:  80,000 * (9,232 / 53,846), rounded integer.

Office / Excel / Windows 10 / Office 2016