Adjusted Forecast using Actuals based on a KEY

This post is related to Forecast to Actuals formula

I have my Forecast and Actual Spend for Capital Expenses. These are reconciled at project level using a KEY (ie. C-IN-CAMEL).

For closed Q (Q1 and Q2), Spend would be the Actual for the project thus “overwriting” the Forecast. This is occurring for Q1 (Adj. Fct = Spend), but not for Q2 as shown below:

Moreover, the Q1 92K delta should’ve decreased the Forecast of Q2 by that amount (it should’ve been 126K instead of 218K). Then again, Q2 Adj. Fct must be the Spend of 75K, hence increasing Q3 by 51K (126 - 75), so it should be 1,842K (1,791 + 51) and so on. That’s exaclty what I’m seeking for.

Any support here would be greatly appreciated.

Best
CAPITAL EXPENSES.pbix (755.2 KB)

Hi @SamSPAIN

I created second matrix. Hope this meets your requirement.

CAPITAL EXPENSES.pbix (725.1 KB)

Thanks a lot @Rajesh! This works perfectly well! Cheers