Project Budget/Forecast sheet

RobH

Hi all,

I am trying to recreate a much simplified version of a Project Budget/Forecasting sheet that I have previously used. I have done a mock up of how this sheet could look (which is copied from the more complex version I have previously used).

Forecast sheet wishlist.pdf

I have attempted to recreate this and get it working but I am not sure what hidden helper columns I am going to need to make the date checking aspect of this sheet to work. The other formulas are fairly straight forward and I think I can sort that out but I always seem to struggle with dates.

Has anyone got any ideas for a sheet similar to this where a project manager can compare actual expenditure to date along with forecasting future expenditure and tracking a project budget over time. I have looked in the solution centre and not found anything that fills my needs yet.

Julio S.

Hi @RobH ,

If I'm understanding correctly, you'd mainly need help with the formulas that calculate whether each of the months are in the past, present of future. If that is so, something along the line of the following for each month should do:

• =IF(MONTH(TODAY()) = 1, "Present", IF(MONTH(TODAY()) > 1, "Past", "Future")) for JAN
• =IF(MONTH(TODAY()) = 2, "Present", IF(MONTH(TODAY()) > 2, "Past", "Future")) for FEB
• =IF(MONTH(TODAY()) = 3, "Present", IF(MONTH(TODAY()) > 3, "Past", "Future")) for MAR
• Etc

Note that these formulas will only work as expected within the current year. Also you may want to use Sheet Summaries to add these formulas if you are intending to make all calculations using Column Formulas.

I hope that this can be of help.

Cheers!

Julio