Excel Time Intelligence: MTD, YTD, YoY, Rolling 12 Months & Fiscal Years
Build the time comparisons every management report needs with plain Excel formulas: month keys with EOMONTH, MTD and YTD windows with SUMIFS, prior-year YTD with EDATE, YoY growth, rolling 12 months, April fiscal years and quarters, like-for-like store growth and a KPI page with a current vs prior year chart, in 8 hands-on labs checked cell by cell.
The first 2 lessons are free — no account needed.
About this course
An Excel time intelligence course that builds the monthly management report of one fictional coffee chain, Larkspur Coffee Co., with plain formulas, for analysts, controllers and anyone who reports month, year-to-date and prior-year figures. You turn order dates into month keys with EOMONTH and total each month with SUMIFS, build a month-to-date block up to a report date with a run-rate, calculate YTD up to the last closed month and the comparable prior-year YTD with EDATE, compare the years with YoY variance and growth and guard against a new category, compute rolling 12-month totals and averages with a trend chart, map months to a fiscal year starting in April and its quarters, measure like-for-like store growth with COUNTIFS flags and finish with KPI tiles and a current vs prior year chart. The first two labs are free.
Lessons
- Month Keys with EOMONTH: Group Any Date into Its MonthFree
- Month-to-Date (MTD): SUMIFS Between the Month Start and the Report DateFree
- Year-to-Date (YTD) and Prior-Year YTD with SUMIFS and EDATE Pro
- Year-over-Year (YoY): Variance, Growth % and the "New" Case Pro
- Rolling 12 Months: Moving Totals, Averages and a Trend Chart Pro
- Fiscal Years Starting in April: FY, Fiscal Quarter and Fiscal YTD Pro
- Like-for-Like (Same-Store) Growth with COUNTIFS Flags Pro
- Capstone: KPI Tiles (Month, YTD, YoY, R12) and a Current vs Prior Year Chart Pro