How to switch forecast from monthly to weekly

VBJBC

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.

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

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.