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
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!