Sales Projections - I'm stumped!

[cpalmer72]

I'm sure I'm overthinking this one, but I just can't seem to figure it out! I am putting together a very basic spreadsheet for sales totals and it includes a column for a projection through the end of the year. Basically, we want to be able to tell the salesperson each month what their monthly and year-to-date totals are, where their goal line is, and what the projection would be through the end of the year based on their sales averages year to date. Any ideas?

Thanks!

[alansidman]

How about this:
=([@[Actual - YTD]]/[@[Goal - YTD]])*Table1[[#Totals],[Goal - YTD]]

[cpalmer72]

No, that projects that the totals are going to be crazy high. I don't believe the solution would utilize the goal total at all, only the monthly total and possibly the year to date. I should also say that I want it to take into account when actual numbers have been entered. For instance, when I input the actual sales number for July, I want the projection for that month to change to the actual total and the future projections through the end of the year to adjust accordingly. I would expect, based on the sample numbers I put in, the projection to end up being a little lower than the goal amount.

[alansidman]

So you are suggesting that you want to use actual ytd and add to that the forecast for those periods in the future?

ie. =[@[Actual - YTD]]+Table1[[#Totals],[Goal - YTD]]-[@[Goal - YTD]]

[cpalmer72]

I'm not quite sure where you want me to plug the formula in. If I put it into E7 (July projected total) I get a value error. If I put it into a cell with a current month value, i.e. E3 (February projected total) it comes up with an astronomical number for the month ($932,699.72). Neither of these would be correct. The February total should look at January and assume that sales would be consistent, coming up with the same number as the actual January total. March, then would look at the average of January and February and estimate that the ytd projection in March would be the sum total of January and February plus an average month-to-date. Carry that on down the line to have the average of all previous months in the year added to the year to date total. Does that make sense? I tend to skip a few steps in my head...

[cpalmer72]

Woohoo! Personal triumph! LOL I figured it out! I used =IF(ISNUMBER([@[Monthly Actual]]),[@[Actual - YTD]],"") in E2 and then used a progressive series in the rest of the column. E3 is =IF(ISNUMBER([@[Monthly Actual]]),[@[Actual - YTD]],SUM(AVERAGE(B2:B3)+C2)) and e4 is =IF(ISNUMBER([@[Monthly Actual]]),[@[Actual - YTD]],SUM(AVERAGE(B2:B4)+E3)) and so on down the line. It works perfectly! Thanks for trying. Sometimes you just have to start explaining it to someone else to get it to click for yourself.