Sales Projections Using a Monthly Growth Rate

[EngineerAdam]

Hi Everyone! First post!

I'm working on a sales forecasting model for my startup. I'm trying to figure out how to phase in sales over a period of months starting with the first sale.

For example, let's say the typical sales model for a particular customer type is:
Month 1: 2 units
Month 2: 2 units
Month 3: 6 units
...
Month 12: 10 units

I'd like to be able to use that to calculate monthly sales based on new customers and existing customers per month.

For example:

Month 1, Customer A starts purchasing
Month 2, Customer B starts purchasing

The sales total by month would be:
Month 1: 2 (customer A's month 1 sales)
Month 2: 4 (customer A's month 2 sales + customer B's month 1 sales)
Month 3: 8 (customer A's month 3 sales + customer B's month 2 sales)

I know how to manually make the equations to do that, but that's going to get really messy over 36 months. Is there a more efficient way to do this?

[JeteMc]

Hello EngineerAdam and Welcome to Excel Forum.
Perhaps the setup modeled on Sheet2 will help.
The spreadsheet is set up with months in column B and units in column C (highlighted in green to designate as a user changeable value).
Clients are displayed in row 1.
The formula to calculate units purchased by each client is: =IF(E$1<=$B3,INDEX($C$3:$C$14,MATCH(COUNTIFS(E$2:E2,">0")+1,$B$3:$B$14,0)),0)
Column A sums the total units sold for the month using: =SUM(E3:P3)

[EngineerAdam]

Thanks for the welcome and for the post!

I need to work my way through this formula in more detail. I think this is very close to what I need. One thing I notice is that setting any value to 0 means the rest of the rows following are all 0. I tried changing the ">0" to ">=0". That keeps all of the values after the zero, but it starts dropping rows as it goes across.

[JeteMc]

Please change the formula in cell E3 to read: =IF(E$1<=$B3,INDEX($C$3:$C$14,MATCH(COUNT(E$2:E2)+1,$B$3:$B$14,0)),"")
Once changed drag the fill handle over to cell P3 and then, while E3:P3 are still selected, drag the fill handle down to cell P14.
Let us know if you have any questions.

[EngineerAdam]

Yes! That fixed that issue. I can make this work for what I need. Thank you! I'll try to remember and come back and upload the final version of this.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.