Help with a salesperson forecast formula

**funkysnyds** · 09-29-2012, 05:51 PM

Hi. New member, so thx for the help.

Trying to create a formula for a monthly forecast that will return a sale for a specified, repeated interval. If my salesperson starts on specified month, I want to set an interval for sales to occur in each successive X months. For example, I have an input cell for the hire month (say Month 3) for my salesperson and I have a cell that refers to the delay before the first sale is made (say 2 Months). I have created a waterfall to handle when the first sale occurs, in this example, month 6. But, I would like for that salesperson to return a sales from that point to occur based on another input cell (every 4 months), so that the next sale would occur at months 10, 14, 18 etc.

Is that even possible? I have been banging my head on this one because its either extremely complicated or very easy - but, I am not seeing it.

Thanks!

**FDibbins** · 09-29-2012, 06:11 PM

assuming your data is in A1, use this formula...

=DATE(YEAR(A1),MONTH(A1)+C1,1)

C1 is for the variable months (4/10/14 etc). if you want, you could hard-code this by replacing C1 with whatever value you want

let me know if this works for you?

**funkysnyds** · 09-29-2012, 06:13 PM

Thanks! I will definitely try it - I think this gets at it!

**FDibbins** · 09-30-2012, 03:16 PM

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

**funkysnyds** · 10-03-2012, 08:27 PM

Thanks again. I used a workaround instead of a formula as my spreadsheet is set-up as months (1-60) vs. actual dates. I basically created an individual sales forecast and a waterfall which replicated that forecast for each newly hired salesperson. More brute force than formula. I will provide feedback for trying to help me.

I do however have another query, related. I have expenses that I want to start in a particular month (1-60), and then once it starts, I want to grow it by a percent in each subsequent 12 month period. So, if the expense is incurred in month 6, then it would grow by X% starting in month 19, 31, 43 and so on.

Any ideas?