Year-End Budget Monitoring & Projections
Getting Started
You are 7 months into the fiscal year. Your job is to project year-end expenditures and revenues using three different forecasting formulas, select the best estimate for each line item, and compare projections to the adopted budget.
Three Forecasting Approaches
- Average Spending: Assumes uniform spending each month
- Seasonal Spending: Uses last year's spending pattern as a template
- Current Month: Projects based on the most recent month's spending
Data Structure
Both the Exp and Rev tabs have columns: MTD (month-to-date), YTD (year-to-date, 7 months), Last Year YTD, Last Year Total, and Adopted Budget.
Average Spending Formula
This assumes spending is approximately the same from month to month — common for routine functions like base salaries.
=(D5/7*5)+D5Worked Example — Salaries
What to Do
=(D5/7*5)+D5loading...Seasonal & Current Month Formulas
The Seasonal Formula assumes spending follows the same seasonal pattern as last year. The Current Month Formula extrapolates from the most recent month.
=IF(OR(F5=0,E5=0),0,D5/(E5/F5))=(C5*5)+D5Worked Examples — Salaries
loading...Salaries Current Month =
loading...Select Best Estimate
Assign formula 1 to the first line item, formula 2 to the second, formula 3 to the third, repeating the pattern (1, 2, 3) for all line items. Then compute the End of Year Estimate using the selected formula.
=(IF(L5=1,H5,IF(L5=2,I5,J5)))+K5Salaries EOY (formula 1):
loading...Total Exp EOY:
loading...Variance & Budget Comparison
Compute the variance (Adopted Budget − EOY Estimate) for each line item. Negative values mean the line item is projected to exceed the adopted budget. Then compare total revenues to total expenditures.
=G5-M5Three Projection Methods Compared
Total Exp Variance:
loading...Surplus/Deficit:
loading...Discussion Questions
Key Points
- Best for routine, predictable expenses (base salaries, rent, utilities)
- Inappropriate for seasonal programs, one-time purchases, or new programs started mid-year
Key Points
- Division by zero is undefined, so the IF statement returns 0 as a safety measure
- This occurs for new line items, reorganized departments, or capital projects that don't recur annually
- In these cases, the analyst must use judgment rather than a formula
Key Points
- Compare your projected total revenue with projected total expenditures
- If a deficit is projected, consider: hiring freezes, deferring purchases, reallocating funds, or requesting a supplemental appropriation
- If a surplus is projected, consider: accelerating maintenance, addressing deferred needs, or returning funds to the general fund
Answer Key
Complete all tasks to reveal, or: