Budget vs. Actuals Tracker

Prompt

Build a budget vs. actuals tracking model in Excel for [DEPARTMENT OR COMPANY] for [FISCAL YEAR].

Budget context:
- Total budget: [AMOUNT]
- Budget categories: [LIST MAIN LINE ITEMS]
- Reporting cadence: [MONTHLY / QUARTERLY]
- Audience: [CFO / DEPARTMENT HEAD / TEAM]
- Current tracking method: [SPREADSHEET / SYSTEM / MANUAL]

**MODEL STRUCTURE**

1. **Input Tab** — Where to paste monthly actuals. Column structure with data validation.

2. **Budget Tab** — Annual budget broken into monthly allocations with a seasonality curve (flat, front-loaded, or custom).

3. **Variance Analysis Tab** — For each line item:
   - Budget
   - Actual
   - Variance ($)
   - Variance (%)
   - YTD budget
   - YTD actual
   - Full-year forecast (actuals + remaining budget)
   - Traffic light: Green (<5% variance), Amber (5-15%), Red (>15%)

4. **Summary Dashboard Tab** — Visual summary with:
   - Total spend vs. budget gauge
   - Top 5 over-budget items
   - Monthly burn rate chart
   - Full-year forecast vs. budget

**FORMULAS**
Provide exact formulas for variance calculations, conditional formatting rules, and the full-year forecast logic.

**MONTHLY CLOSE PROCESS**
Step-by-step instructions for updating the tracker each month-end (15 minutes or less).