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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!