How to switch forecast from monthly to weekly

Hi there,

I am trying to build a formula that predicts work completed [%] and can anticipate estimated revenue for said period of time [week] based on a construction schedule. I used the following formula below to get a monthly forecast:

=IF(TODAY() < Start@row, 0, [Total SOV]@row / (MONTH(Finish@row) - MONTH(Start@row) + 1 + IF(YEAR(Finish@row) = 2022, 12, 0) + IF(YEAR(Finish@row) = 2024, 24, 0)) * (MONTH(TODAY()) - MONTH(Start@row) + 1 + IF(YEAR(TODAY()) = 2022, 12, 0) + IF(YEAR(TODAY()) = 2024, 24, 0))) * (1 - $[% Complete]@row)


How can I rebuild this or switch it to a weekly forecast? I need to be able to predict the % of work completed based on the schedule to then calculate the associated cost for that week in the future.


Answers

  • AaronO
    AaronO ✭✭✭

    I'm not sure what you're trying to do. Is it "based on the start and end dates of a task, tell me how much revenue I can expect next week"? Is the % Complete something that you're filling in, or it's something you're calculating in order to come up with the revenue number?

    In other words, if I have a task that will take 30 working days and pay $300, that's $10/day. If I know all five days next week are within that 30 days, I can expect $50. But if the task ends next Wedneday, only 3 days next week are inside the task dates so I can only expect $30. Is that what you want?

    Or are you trying to use the actual % Complete to get something more specific?

    Aaron

  • VBJBC
    VBJBC ✭✭

    Hi Aaron,

    We can update the "% complete" column every day but it doesn't help me forecast for the upcoming weeks/months.

    If I can track it based on the scheduled dates and get the value that would be great. The % completed is more of a way for us to weigh the value and help account for work already completed to date. The problem is we have several if not hundreds of tasks that vary from 3 months to 3 days. I would like to get a sum of the revenue for all the tasks that will be completed or progressed during a week.

    I'm open to either option as long as the forecast is still accurate.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!