Interesting revenue spreading question.
I have a project file that shows start date, finish date and revenue (or cost) spread over that time.
Revenue is spread unevenly in each month with different daily revenue depending on project stage and with unknown weightings in each month (each month is a collection of the days with different milestones that a big database calculates and this simplified view is what it outputs for users to play with).
So we need to approximate using the given revenue per month and the days in the month and the shift in revenue across the months (like a weighted average).
/XD][XD=bc:FFFF00|cls:bl bt br bb][/XD][XD=bc:FFFF00|cls:bl bt br bb][/XD][XD=bc:FFFF00|cls:bl bt br bb][/XD][/XR][/RANGE]
I have a project file that shows start date, finish date and revenue (or cost) spread over that time.
Revenue is spread unevenly in each month with different daily revenue depending on project stage and with unknown weightings in each month (each month is a collection of the days with different milestones that a big database calculates and this simplified view is what it outputs for users to play with).
So we need to approximate using the given revenue per month and the days in the month and the shift in revenue across the months (like a weighted average).
- Users enter New start and Finish dates in the Orange Cells and see the New Revenue Spread in the yellow cells for the future forecast months before agreeing to the new dates and submitting via email.
- Dates for start and finish can move both ways, earlier in time and later in time, in any combination (start later, finish earlier, vice versa and both late or both earlier).
| Forecast Predictor - safe sample.xlsx | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
| 1 | No of Days | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | Helper Rows | ||||||||||||||||
| 2 | End of Month | 31 Mar 2021 | 30 Apr 2021 | 31 May 2021 | 30 Jun 2021 | 31 Jul 2021 | 31 Aug 2021 | 30 Sep 2021 | 31 Oct 2021 | Helper Rows | ||||||||||||||||
| 3 | Start of Month | 01 Mar 2021 | 01 Apr 2021 | 01 May 2021 | 01 Jun 2021 | 01 Jul 2021 | 01 Aug 2021 | 01 Sep 2021 | 01 Oct 2021 | Helper Rows | ||||||||||||||||
| 4 | ||||||||||||||||||||||||||
| 5 | Start Date | Finish Date | Total Project Value | Forecast 2021-Mar | Forecast 2021-Apr | Forecast 2021-May | Forecast 2021-Jun | Forecast 2021-Jul | Forecast 2021-Aug | Forecast 2021-Sep | Forecast 2021-Oct | NEW START DATE | NEW FINISH DATE | Forecast 2021-Mar | Forecast 2021-Apr | Forecast 2021-May | Forecast 2021-Jun | Forecast 2021-Jul | Forecast 2021-Aug | Forecast 2021-Sep | Forecast 2021-Oct | Forecast 2021-Nov | Forecast 2021-Dec | |||
| 6 | 27 May 2021 | 03 Aug 2021 | £ 800,000 | - | - | £ 100,000 | £ 400,000 | £ 260,000 | £ 40,000 | - | - | 01 May 2021 | 10 Sep 2021 | |||||||||||||
Sheet3 | ||||||||||||||||||||||||||
