Project Budget/Forecast sheet

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).
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.
Answers
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.2K Get Help
- 452 Global Discussions
- 155 Industry Talk
- 505 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 81 Community Job Board
- 514 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!