Hi all
I am trying to forecast values by month and year based on their project schedule completion date (Finish_Date) and estimated annualised budget savings (Revenue_Benefit). If a project completes half way through a year, 50% of the annual saving should be profiled onwards from that point that year and 100% for all following years. The end result will be a line/area graph and a table summarising cumulative Revenue_Benefit by month and fiscal year (Apr-Mar), demonstrating when total budget savings are predicted to be made.
TaskCostDetail_Table
| Project | *Finish_Date | Revenue_Benefit |
| A | 25/03/2022 | £10,000 |
| B | 4/06/2022 | £165,000 |
| C | 16/11/2021 | £96,000 |
| D | 26/9/2020 | £106,000 |
*Relationship
Calendar
| *Date | Month | Fiscal Year |
| 01/04/2020 | Apr | 20/21 |
| 02/04/2020 | Apr | 20/21 |
| 03/04/2020 | Apr | 20/21 |
| 04/04/2020 | Apr | 20/21 |
| 05/04/2020 | Apr | 20/21 |
From browsing the forums I currently have the below which profiles my savings to their project dates and continues that saving figure onwards for future days/months/years which is great.
CumulativeBenefit:=VAR MaxDate=MAX('Calendar'[Date])
RETURN
CALCULATE(
SUM(TaskCostDetail_Table[Revenue_Benefit]),
'Calendar'[Date]<=MaxDate)
But what it doesn't yet do is reduce the annualised saving figure in its first year when the project is scheduled for completion mid-year e.g. currently shows mid-year saving at 100% annual saving rather than 50% annual saving in the first year and 100% for following years.
The kind of output I'm after is the below table which I'll also use the total to graph. For this demonstration I have ignored project finish day of month and just assumed the partial year figure = RevenueBenefit * (# months left in fiscal year after project finish / 12). In reality I was trying to use YEARFRAC to make this more accurate and account for day of the year, not just month of the year.
| | | Project | | | | |
| Fiscal year | Month | A | B | C | D | Total |
| 21/22 | Apr | | | | £106,000 | £106,000 |
| 21/22 | May | | | | £106,000 | £106,000 |
| 21/22 | Jun | | | | £106,000 | £106,000 |
| 21/22 | Jul | | | | £106,000 | £106,000 |
| 21/22 | Aug | | | | £106,000 | £106,000 |
| 21/22 | Sep | | | | £106,000 | £106,000 |
| 21/22 | Oct | | | | £106,000 | £106,000 |
| 21/22 | Nov | | | £40,000 | £106,000 | £146,000 |
| 21/22 | Dec | | | £40,000 | £106,000 | £146,000 |
| 21/22 | Jan | | | £40,000 | £106,000 | £146,000 |
| 21/22 | Feb | | | £40,000 | £106,000 | £146,000 |
| 21/22 | Mar | £833.33 | | £40,000 | £106,000 | £146,833 |
| 22/23 | Apr | £10,000 | | £96,000 | £106,000 | £212,000 |
| 22/23 | May | £10,000 | | £96,000 | £106,000 | £212,000 |
| 22/23 | Jun | £10,000 | £137,500 | £96,000 | £106,000 | £349,500 |
| 22/23 | Jul | £10,000 | £137,500 | £96,000 | £106,000 | £349,500 |
| 22/23 | Aug | £10,000 | £137,500 | £96,000 | £106,000 | £349,500 |
| 22/23 | Sep | £10,000 | £137,500 | £96,000 | £106,000 | £349,500 |
| 22/23 | Oct | £10,000 | £137,500 | £96,000 | £106,000 | £349,500 |
| 22/23 | Nov | £10,000 | £137,500 | £96,000 | £106,000 | £349,500 |
| 22/23 | Dec | £10,000 | £137,500 | £96,000 | £106,000 | £349,500 |
| 22/23 | Jan | £10,000 | £137,500 | £96,000 | £106,000 | £349,500 |
| 22/23 | Feb | £10,000 | £137,500 | £96,000 | £106,000 | £349,500 |
| 22/23 | Mar | £10,000 | £137,500 | £96,000 | £106,000 | £349,500 |
| 23/24 | Apr | £10,000 | £165,000 | £96,000 | £106,000 | £377,000 |
| ... | | | | | | |
I want to show that in the first year a project is complete it only gives a proportion of its annual benefit saving e.g. Project A finishes Mar 22 therefore only 1 month's worth of the annual figure = £833. But in the following year it will give a full year saving of £10,000 from the start. This is all around annual budget setting if that helps. I am trying to determine impact on annual budget in year 1 of a project and then impact on all future years e.g. for project C I can reduce my annual budget by £40,000 in November 21 and then by the full annual figure of £96,000 at the start of the new year.
To achieve this I tried the below (our budget year is Apr-Mar hence 31/3 as end of year).
CashRevenueBenefit:=VAR MaxDate=MAX('Calendar'[Date])
RETURN
CALCULATE(
IF(MaxDate>ENDOFYEAR(TaskCostDetail_Table[Finish_Date],"31/3"),SUM(TaskCostDetail_Table[Revenue_Benefit]),
(1-YEARFRAC(MAX(TaskCostDetail_Table[Finish_Date]),ENDOFYEAR(TaskCostDetail_Table[Finish_Date],"31/3")))
*SUM(TaskCostDetail_Table[Revenue_Benefit])),
'Calendar'[Date]<=MaxDate)
But this doesn't produce the desired results of how much partial annual budget can be saved at a given project completion date.
I would really appreciate any help and pointers in the right direction as I'm stuck after lots of tinkering with different variations.
Many thanks
