Goal:
As a finance leader, I want to see the YoY delta of my previous year actuals to my current year forecast such that I can be proactive in managing my current year spend.
Ingredients:
- General Ledger
- Forecast
- Home-made Metrics
Prepare ahead of time:
Ensure you have your General Ledger and Forecast (either manual or ITPF version) loaded into TBM Studio for Costing. I prefer to create transforms for each data set and then map/append them into Cost Source Master Data. Prepare your Cost and Forecast drivers, as well.
Steps:
- Create an annual Metric for your Cost
- Home ribbon-> New-> Metric
- Name = myAnnualCost
- Model Type =Calculated Metric
- Value Calculation =Annual(Cost) (function reference)
- TableFormat =Currency({myAnnualCost},"#,##0")
- Ps – there is an out of the box version of Annual Cost, but be sure to check if the calculation is producing the result you are hoping for.
- Create an annual Metric for your Forecast
- Home ribbon-> New-> Metric
- Name = myAnnualForecast
- Model Type =Calculated Metric
- Value Calculation =Annual(Forecast)
- TableFormat =Currency({myAnnualForecast},"#,##0")
- Create a Metric to get previous year annual Cost
- Home ribbon-> New-> Metric
- Name = myPreviousAnnual
- Model Type =Calculated Metric
- Value Calculation =TimePeriod(myAnnualCost, -12) (function reference)
- TableFormat =Currency({myPreviousAnnual},"#,##0")
- Create a new report, call it Year to Year Analysis or Year over Year Analysis. Or a name of your choice – such as myFavoriteReportEVER_v2_Final
- Add a table - Report Ribbon at top of page->Table
- Bring in dimensions of your choice from Cost Source object
- Drag your newly created Metrics into the table (you only need myAnnualForecast and myPreviousAnnual)
- Finally, with the report table highlighted, add your YoY formula column
- Go to the Data tab->Insert-> Insert Formula Column
- YoY=myPreviousAnnual-myAnnualForecast
- Be sure to have your finance team taste test this YoY formula. Tastes differ, some prefer forecast-previous year actuals
- Check-in
- Best enjoyed with leadership
Footnotes:
- Validate your annual metrics to ensure all rows are being included throughout time
- Shout out to my teammates Scott and Josh for the assist with this solution