There is a real problem hiding in this spreadsheet, and it is not a math problem. The user has built a projection that repeats the same monthly enrollment numbers for four years, but the third line, the one that tracks active students, depends on a rolling five-month graduation cycle. That means the calculation is not a simple sum. It is a lagged formula, and the user already knows this because they described it perfectly: January's 6 enrollments become May's 6 graduates, and the active count each month is prior active plus new enrollments minus graduates from five months prior.
The practical fix is straightforward, and it is worth stating plainly: the third line should be a running total that references the enrollment row with a five-month offset. In Excel, that means using a formula like `=C2 + B3 - B2` if the rows are set up with enrollments in row 2, graduations in row 3, and active counts in row 4. The key is that the graduation row is not a separate input; it is the enrollment value from five months earlier. So the user does not need to track graduations manually at all. They only need to point the formula back to the enrollment cell that is five columns over. That is the automation they are looking for.
What makes this interesting is that the user has already done the hard part. They identified the relationship between enrollments, graduations, and active counts. They even articulated the logic clearly enough to ask the right question. The spreadsheet is not failing because the data is complex. It is failing because the user is treating the third row as if it needed its own manual entries, when in fact it is a derived value. That is a common trap in forecasting work. People duplicate numbers because it feels safer than writing formulas, but duplication is exactly what creates the problem. If they change an enrollment figure next year, the active count will not update unless the formula is in place.
The concrete takeaway is this: stop duplicating the enrollment row and start treating the active count as a function of time. Use a formula that subtracts the enrollment from five months prior, and let the spreadsheet do the rest. The user is close. They just need to trust the math they already described. Once they do, they will have a model that responds to changes instantly, and that is the real value of moving from manual tracking to automated projection.