Challenge level: 3 out of 5
Estimated reading time: 7 min
Excel functions covered: SUM(), OFFSET() and MATCH()
Excel attachment reference sheets: template_Excel
We can easily obtain YTD figures at this point by slightly tweaking the L12M formula.
A slight tweak to the formula gives us FYTD figures in case your company follows a financial year different from the calendar year.
To get YOY figures for YTD and FYTD, the best way to go about this is to calculate the previous year’s YTD and FYTD figures as well so that we can employ the simple mathematical equation of dividing the difference between current and previous years’ figures by the previous year figure.
To get previous years’ YTD and FYTD figures, we need to only tweak the YTD and FYTD formulas so that they are referencing cells that contain Last Year date figures (i.e. column H instead of column G).
YTD_last year example:
We have finally covered all the formulas you need to know to build a template that gives you L12M, L12_Average, YTD, FYTD and YOY figures. More importantly, these formulas have been structured to automatically update these figures when there are time period changes.
If you were secretly wishing for an easier way to to do this, you’re in luck! There is a more efficient way. We'll be introducing you to it tomorrow, stay tune!
This is the third article in a 5-part introduction tutorial that juxtaposes Excel formula with Power Query and Power Pivot. Here are the other parts if you missed them: