Course outline / Advanced Excel
Lesson 72 of 75 · Advanced · 5 exercises
Finance analyst basics
Variance to budget, year-to-date totals and a simple forecast.
What you'll practise
- Variance to budget
- Variance as a percentage
- Year to date
- Forecast month 5
- The monthly trend with SLOPE
Variance to budget
Variance is actual minus budget. Positive means better than planned for revenue.
In cell E2, work out month 1's variance: C2 minus B2.
Variance as a percentage
In cell E2, show month 2's variance as a percentage of its budget: (C3-B3)/B3.
Year to date
In cell E2, add up the actuals for months 1 to 3 (C2:C4): the year to date at the end of March.
Forecast month 5
FORECAST fits a straight line through past values and extends it.
=FORECAST(x, known_ys, known_xs)
In cell E2, forecast the actual for month 5 from months A2:A5 and actuals C2:C5.
The monthly trend with SLOPE
SLOPE gives how much the line rises each month.
In cell E2, find the slope of actuals C2:C5 against months A2:A5.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.