Calculating monthly sales figure required to make annual turnover figure

Hi can someone help please?

Im am trying to create a formula that will calulate what sales need to be added per month to achieve a particular total sales total at year end based on the following paramters for example;

i have the 1st month sales figure of say 10,000,
i have the total target figure at year end of say 3,000,000 total sales for the year.
The 10,000 signed up sales in month 1 will continue in month 2, 3 4 and so on.
so for example if 11,000 was signed up in month 2 then the Turnover for month 2 would be 21,000 and that 21,000 will continue through to months 3,4 and so on. Again if 14,000 was signed up in month 3 then the turnover in month 3 will be 10,000 + 11,000 (21,000) +14,000 so 35,000.
I would assume linear growth in the formula.

i attach an excel file that documents lightly what i've outlined above

im near 40 and class myself as relatively excel savvy - this is my first post however on a forum of anytype before so perhaps there's a discussion on that some other time.

thanks

If you're looking at linear growth across 12 months rolling figures then what you're dealing with is, effectively, a compound interest formula. So for each month you can calculate the shortfall in sales as being (annual target / ((1 + months remaining)^growth rate))-current cumulative sales

If we then divide this by the number of months remaining it will tell you the value of sales you need to increase by month-on-month to achieve the target.

So if in cell D3 you put =SUM($C$3:C3) and copy that down for each month that will give you a cumulative total for column C.

Then in cell C17 put the expected growth rate as a month-on-month percentage (say 10%).

Then in cell D3 put the following formula =(($C$15/((1+(12-B3))^$C$17))-D3)/(12-B3) and copy that down then that should be the value that you're looking for.

The formula won't work for month 12, because then there are no sales periods left to increase the sales. If you want the figures not to include the current month then use the formula =(($C$15/((1+(12-B3))^$C$17))-D3)/(13-B3)

Does any of that make sense?

Originally Posted by Andrew-R

If you're looking at linear growth across 12 months rolling figures then what you're dealing with is, effectively, a compound interest formula. So for each month you can calculate the shortfall in sales as being (annual target / ((1 + months remaining)^growth rate))-current cumulative sales

If we then divide this by the number of months remaining it will tell you the value of sales you need to increase by month-on-month to achieve the target.

So if in cell D3 you put =SUM($C$3:C3) and copy that down for each month that will give you a cumulative total for column C.

Then in cell C17 put the expected growth rate as a month-on-month percentage (say 10%).

Then in cell D3 put the following formula =(($C$15/((1+(12-B3))^$C$17))-D3)/(12-B3) and copy that down then that should be the value that you're looking for.

The formula won't work for month 12, because then there are no sales periods left to increase the sales. If you want the figures not to include the current month then use the formula =(($C$15/((1+(12-B3))^$C$17))-D3)/(13-B3)

Does any of that make sense?

Yes thanks it does, however i am not looking for % growth really. In this new attachment i think ive made my requirement clearer - im trying to create a formula that will calculate b2 - that looks at cumulative sales to date - looks at year end target - most importantly considers that sales made in month 1 contribute to total sales by 12, sales made in month 2 contribute to the total sales figure by 11, sales made in month 3 contribute to total sales figure by 10 an so on

See the attached link. I know it's a simple solution using only the sum function. Is there any way you can be more specific as to what else you would like for it to do? The feature I detailed takes into account that sales for week one do factor in for the twelve weeks...that sales for week two only factor in for weeks 2-12 and so on throughout the weeks. Can you be more specific as to what else you would like it to do?