Forecasting in Excel

[ahoward303]

[MENTION][/MENTION]Good Afternoon Everyone!

I asked a couple of weeks ago for help with a formula that will forecast a value based on a start and end date. In the table below, the following formula is in D2: =IF(AND($A2<=D$1,D$1<=$B2),$C2/DATEDIF(EOMONTH($A2,-1)+1,EOMONTH($B2,0)+1,"m"),""). This formula is absolutely beautiful at adjusting based on the dates entered. The only problem I have now is when we complete one month and move to another. Ideally, when the month of June ends, the $8400 is frozen, meaning, if someone were to go in and change the start and end date, the value of $8400 stays in June. The funds remaining in July - Oct are the only numbers that can be altered. I know in order to freeze June, I would need to copy and paste the value as a number so the formula would be gone. But now, if I do that, the funds in the months of July - Oct change to continue to match the 42000 value it's calculating on. How is the best way to get around that? I've tried reducing the value by a sum of previous months but that doesn't work. Maybe I need to add another column that takes that into account?

Start	End	Value	Jun-18	Jul-18	Aug-18	Sept-18	Oct-18
Jun-18	Oct-18	42000	8400	8400	8400	8400	8400
Jul-18	Aug-18	35000		17500	17500
Jul-18	Oct-18	10000		2500	2500	2500	2500
Aug-18	Oct-18	20000			6666.67	6666.67	6666.67

<tbody>
</tbody>

 

[jakeman]

Hi there - I don't see how the values would ever stay frozen if after the month ends a user could come and change the Start and End Date. I think you will need to add an additional column, maybe more, to keep a historical record of what the values were once the month ended. This way, if a user were to change the Start and End date, the value wouldn't be changed as well.

Maybe you want to add a historical run that includes the 5 periods listed along the column Headers (Jun-18 to Oct-18), that capture the values that were calculated at a point in time. I would probably update this at the end of every period so that I can do a retrospective look-back at what happened after every period. What would concern me is that users can change the inputs in your formula, without being able to see historical trends.

 

[ahoward303]

That's my concern as well. I'd need some sort of way to see when something has been changed so that I can make an adjustment. I really think I'm expecting a lot out of excel or expecting it to be something simple.