Project Budget/Forecast sheet

Options
RobH
RobH ✭✭✭✭
edited 02/27/23 in Formulas and Functions

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

  • Julio S.
    Julio S. Moderator
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!