Year-over-year comparison
AdvancedYear-over-year (YoY) is a common Financial Planning KPI because it compares performance to the same month last year (so seasonality doesn’t distort the story).
In this sheet, you’ll use SUMIFS (and a few other functions) to total Sales for a specific month, then compute the % change.
Your task:
- Review the transaction log in A1:D12 (Date, Region, Product line, Sales).
- In F2, the report month is already set to the first day of the month (you can change it if you want).
- In F3, calculate total Sales for the month in F2.
- Use a date window: Date >= start of the month and Date < first day of next month.
- In F4, calculate total Sales for the same month last year.
- In F5, calculate the YoY % change using (this month / last year) - 1.
Tip: A helpful SUMIFS pattern for date windows is:
=SUMIFS(sum_range, date_range, ">="&start_date, date_range, "<"&end_date)
Need some help?
Hint 1
Your sum_range is the Sales column, and your criteria date_range is the Date column. Both should cover rows 2-12.
Hint 2
For the end of the month boundary, use the first day of the next month: `DATE(YEAR(F2),MONTH(F2)+1,1)`.
Hint 3
For last year, build the same start/end dates but with `YEAR(F2)-1`.
Answer
Exercise
Year-over-year comparison
AdvancedYear-over-year (YoY) is a common Financial Planning KPI because it compares performance to the same month last year (so seasonality doesn’t distort the story).
In this sheet, you’ll use SUMIFS (and a few other functions) to total Sales for a specific month, then compute the % change.
Your task:
- Review the transaction log in A1:D12 (Date, Region, Product line, Sales).
- In F2, the report month is already set to the first day of the month (you can change it if you want).
- In F3, calculate total Sales for the month in F2.
- Use a date window: Date >= start of the month and Date < first day of next month.
- In F4, calculate total Sales for the same month last year.
- In F5, calculate the YoY % change using (this month / last year) - 1.
Tip: A helpful SUMIFS pattern for date windows is:
=SUMIFS(sum_range, date_range, ">="&start_date, date_range, "<"&end_date)
Need some help?
Hint 1
Your sum_range is the Sales column, and your criteria date_range is the Date column. Both should cover rows 2-12.
Hint 2
For the end of the month boundary, use the first day of the next month: `DATE(YEAR(F2),MONTH(F2)+1,1)`.
Hint 3
For last year, build the same start/end dates but with `YEAR(F2)-1`.