Charts & Visual Data Modeling
Sprint Burndown Chart Formula: Ideal Remaining Line, Daily Velocity & Completion Forecast
Build an agile Sprint Burndown table and line chart: compute Ideal Remaining Story Points P_0 * (1 - d / D), Schedule Variance, Realized Daily Velocity, and Projected Days to Zero.
Pair a linear Ideal Burndown line with Realized Daily Velocity to spot scope creep early!
An agile Sprint Burndown Chart plots two series across working days d = 0, 1, ..., D: the diagonal Ideal Remaining Guideline P_ideal(d) = P_0 * (1 - d / D) from initial sprint commitment P_0 down to 0 on day D, and Actual Remaining Story Points P_rem(d) updated after each daily standup (using NA() on future days so the actual line stops cleanly at today!).
Adding two live KPI formulas turns the chart table into an early-warning forecast: Schedule Variance P_rem(d) - P_ideal(d) (> 0 means behind ideal pace; < 0 means ahead of pace) and Projected Total Days to Zero P_0 / ((P_0 - P_rem(d)) / d), which flags immediately whether current daily burn velocity will clear the backlog by Day D!
| Sprint Metric (P_0 = 80 pts, D = 10 days, Day d = 4, P_rem = 52 pts) | Worksheet Formula | Evaluated Result |
|---|---|---|
| Ideal Remaining Story Points at Day d | =B2 * (1 - B4 / B3) | 48.00 pts |
| Burndown Variance (Actual - Ideal) | =B5 - B2 * (1 - B4 / B3) | +4.00 pts (Behind Ideal) |
| Realized Burn Velocity (pts / day) | =(B2 - B5) / B4 | 7.00 pts/day |
| Projected Total Working Days to Zero | =ROUND(B2 / ((B2 - B5) / B4), 2) | 11.43 days (> 10-day Sprint!) |
Ideal Remaining Story Points at Elapsed Working Day d
Computes the straight-line ideal burndown benchmark P_0 * (1 - d / D) for initial commitment B2, total sprint working days B3, and elapsed working day B4.
=IF(OR(B2 <= 0, B3 <= 0, B4 < 0, B4 > B3), "Invalid Sprint Parameters", ROUND(B2 * (1 - B4 / B3), 2))Projected Sprint Working Days Required to Burn Remaining Backlog to Zero
Divides total committed story points B2 by realized daily burn velocity (B2 - B5) / B4, guarding against zero elapsed days or stalled/negative burn.
=IF(OR(B2 <= 0, B3 <= 0, B4 <= 0, B4 > B3), "Invalid Sprint Parameters", IF(B5 >= B2, "Stalled or Scope Added: No Net Burn", ROUND(B2 / ((B2 - B5) / B4), 2)))Try the example
Use the original values or change them to test your understanding.