How to Build a Financial Model in Excel

Most Financial Models Never Get Used

Founders build (or commission) financial models for fundraising, then never open them again. The model is too complex to update, too disconnected from actual accounting data, and built around assumptions that bear no resemblance to the business after month three.

The financial model that actually helps you run a business looks different — simpler structure, fewer assumptions, direct connection to actual data.

Here's how to build one that you'll actually use monthly.

Table of Contents

1. The Right Structure: 5 Sheets, No More
2. Sheet 1: Assumptions
3. Sheet 2: P&L Model
4. Sheet 3: Cash Flow Forecast (13-Week Rolling)
5. Sheet 4: Balance Sheet (Quarterly)
6. Sheet 5: Dashboard (The Only Sheet Most People Will See)
7. Connecting to Actual Data
8. FAQ
9. Conclusion

The Right Structure: 5 Sheets, No More

1. Assumptions — all input variables in one place
2. P&L Model — monthly P&L with actuals vs. forecast
3. Cash Flow Forecast — 13-week rolling forecast
4. Balance Sheet — simplified, updated quarterly
5. Dashboard — 5–8 KPIs, one-page view

Five sheets. Every formula pulls from Assumptions. Change one number, everything updates.

Sheet 1: Assumptions

This is where your model lives or dies. Bad assumptions → bad model. Structure:

Revenue drivers:

• New customer acquisition (number per month, based on current pipeline)
• Average deal size (by segment if you have multiple)
• Churn rate (monthly %)
• Revenue per customer per month (or one-time, or mixed)

Cost structure:

• Headcount plan (by role, by month, with salary + benefits)
• Variable costs as % of revenue (e.g., COGS at 35%, payment processing at 2%)
• Fixed costs (rent, software, insurance — actual numbers)
• One-time costs (capex, setup costs)

Working capital:

• Average debtor days (DSO)
• Average creditor days (DPO)
• Inventory days (if applicable)

Keep assumptions range-bounded (enter one number, use sensitivity analysis for scenario planning).

Sheet 2: P&L Model

Layout: Months across the top (Jan–Dec), line items down the side.

Two sets of columns per month: Forecast and Actual. A third column: Variance (with conditional formatting — red if >10% negative variance, green if favorable).

Key P&L sections:

• Gross Revenue
• Discounts and Returns
• Net Revenue
• COGS → Gross Profit + Gross Margin %
• Operating Expenses (by category)
• EBITDA
• Depreciation & Amortization
• Operating Profit (EBIT)
• Interest & Taxes
• Net Profit

Update actuals from Zoho Books export on the 5th of each month. Review variances. Understand what drove them. Adjust future assumptions if they're systematically off.

Sheet 3: Cash Flow Forecast (13-Week Rolling)

This is the most practically useful sheet for day-to-day management.

Structure: 13 columns (weeks), rows for:

• Opening cash balance
• Expected receipts (from AR aging + expected new sales)
• Expected payments (payroll dates, rent, vendor payments, taxes)
• Net cash flow
• Closing balance
• Minimum cash threshold (highlight red if closing < 4 weeks of burn)

Update every Monday. Takes 20–30 minutes once the template is built. This single view prevents 90% of cash surprises.

Sheet 4: Balance Sheet (Quarterly)

For most small businesses, a full monthly balance sheet is overkill. Update quarterly:

Assets:

• Cash and bank balances
• Accounts receivable
• Inventory
• Fixed assets (net of depreciation)
• Prepaid expenses

Liabilities:

• Accounts payable
• Accrued liabilities
• Loans and borrowings
• Tax payable

Equity:

• Share capital
• Retained earnings (opening + current year profit)

Sheet 5: Dashboard (The Only Sheet Most People Will See)

One page, 5–8 KPIs, with trend lines. For most startups:

1. Monthly Revenue (bar chart, 12 months)
2. Gross Margin % (line, 12 months)
3. Net Burn Rate ($/month)
4. Cash Runway (months at current burn)
5. AR Collection Rate (%)
6. Days Sales Outstanding (DSO)
7. Customer Count (if B2B)
8. Month-over-Month Revenue Growth (%)

Every KPI should have: current value, target, trend direction, and a sparkline.

Connecting to Actual Data

The model is only useful if actual numbers flow in cleanly. Setup:

1. Monthly export from Zoho Books (P&L + Balance Sheet)
2. Power Query connection in Excel to auto-refresh from the export file
3. Actuals populate the Actual columns automatically
4. Variance columns calculate automatically
5. Dashboard updates automatically

Once built: 30 minutes to refresh the entire model after month-end close.

Conclusion

A financial model that gets used beats a beautiful model that doesn't. Start with the minimum viable structure above, connect it to your actual accounting data, and review it monthly with your team.

If you'd like help building this for your business — or if your current model needs a rebuild — our team builds Excel financial models as part of our financial reporting service.