Finban

Excel Financial Planning: Step-by-Step Guide

Marcus Smolarek

Marcus Smolarek

Gründer von finban

Zuletzt aktualisiert

From your first spreadsheet to a complete 3-statement model — here are guides for every level. Each guide contains concrete steps you can implement directly in Excel.

Want to automate your financial planning? Finban connects to your bank accounts and creates your cash flow forecast automatically. Start for free →

For Beginners

Creating a Liquidity Plan in Excel (Basics)

A liquidity plan answers every entrepreneur's most important question: Do I have enough cash in the bank?

What you need: Excel or Google Sheets, bank statements from the last 3–6 months.

Step 1: Set up the table structure

  • Months as columns (Jan–Dec)
  • Categories as rows (Revenue, Expenses, Balances)
  • First row: Opening balance

Step 2: Enter the opening balance

  • Current bank balance as the starting point in cell B2
  • Formula for subsequent months: =PREVIOUS_CLOSING_BALANCE

Step 3: Enter revenue

  • Revenue by product line/service
  • Grants and capital inflows
  • Important: Always plan by expected payment receipt, not invoice date!

Step 4: Enter expenses

  • Fixed costs: Rent, salaries, insurance (same every month)
  • Variable costs: Marketing, contractors, materials
  • Taxes: VAT prepayment (quarterly), trade tax

Step 5: Calculate closing balance

=Opening Balance + SUM(Revenue) - SUM(Expenses)

Step 6: Conditional formatting

  • Color closing balance cells red when < 0
  • Yellow when < €10,000 (or your individual threshold)

Step 7: Create a chart

  • Line chart with closing balance across all 12 months
  • Additionally: Bar chart with revenue vs. expenses per month

Time required: ~2 hours to build, then 30–60 min/week for maintenance.

Building a Cash Flow Statement (Direct Method)

The direct method is the most intuitive way to calculate your cash flow: you list all actual inflows and outflows.

Operating inflows:

  • Customer payments (from invoices)
  • Interest income
  • Tax refunds

Operating outflows:

  • Suppliers and contractors
  • Salaries and social contributions
  • Rent and utilities
  • Tax payments (VAT, trade tax)

Investment cash flow:

  • Asset purchases (computers, equipment, office furnishing)
  • Asset sales

Financing cash flow:

  • Loan drawdown / loan repayment
  • Equity contribution (e.g., shareholder loan)

Result: Net cash flow = change in bank balance for the period.

For Intermediate Users

Scenario Planning with Data Tables

Scenarios help you quantify risks and opportunities before they materialize.

Step 1: Define input cell — e.g., revenue growth in cell B2 (current value: 5%)

Step 2: Define result cell — e.g., closing balance December (cell N14)

Step 3: Create variants

  • -10% (Worst Case)
  • 0% (Stagnation)
  • 5% (Base Case)
  • 15% (Best Case)
  • 25% (Aggressive Growth)

Step 4: Create data table

  1. Select variants + result formula
  2. Data → What-If Analysis → Data Table
  3. Input cell: B2

Step 5: Interpret results You can immediately see how each growth rate affects your closing balance.

Pro tip: For two variables simultaneously (e.g., growth × payment terms), use the 2D data table.

Building a Rolling Forecast

A rolling forecast extends the plan by one month each month — instead of planning once a year.

Why rolling forecast?

  • Annual planning becomes historically irrelevant by Q3
  • A rolling forecast is always current
  • Better responsiveness to market changes

Setup:

  1. Planning horizon: Always 12 months from the current month
  2. Actual data: Overwrite completed months with real numbers
  3. Plan data: Plan remaining months with updated assumptions
  4. Actual vs. plan: Analyze variances and adjust assumptions
  5. Update cadence: Monthly, ideally in the first week

For CFOs & Finance Teams

3-Statement Model in Excel

The ultimate financial planning model — P&L, balance sheet, and cash flow statement, fully linked.

Step 1: Build the P&L

Revenue
  − Cost of Goods Sold
= Gross Profit
  − Operating Expenses (Personnel, Rent, Marketing)
= EBITDA
  − Depreciation & Amortization
= EBIT
  − Interest
= EBT
  − Taxes
= Net Income

Step 2: Build the Balance Sheet

  • Assets: Fixed assets + Current assets (Receivables, Inventory, Cash)
  • Liabilities: Equity + Debt (Payables, Provisions)
  • Check formula: =SUM(Assets) - SUM(Liabilities) must always equal 0

Step 3: Cash Flow Statement (Indirect Method)

Net Income (from P&L)
  + Depreciation (non-cash)
  ± Working Capital Changes
= Operating Cash Flow
  − Capital Expenditure
= Free Cash Flow
  ± Financing Cash Flow
= Net Cash Flow

Step 4: Create linkages (the critical part)

  • P&L → Balance Sheet: Net income increases equity
  • Balance Sheet → Cash Flow: Changes in receivables, payables, inventory
  • Cash Flow → Balance Sheet: Ending cash = liquid assets on balance sheet

Time required: 1–2 days to build. Then it's the most powerful planning tool — but also the most error-prone.

Building Your Excel Financial Plan: The 3-Phase Approach

Phase 1: Setup (Day 1–2)

Goal: Basic structure is in place, first numbers are entered.

  1. Create file: One workbook with sheets for: Assumptions, Revenue, Expenses, Liquidity, Dashboard
  2. Assumptions sheet: Maintain all variables centrally (tax rate, payment terms, growth rate, inflation)
  3. Define categories: Revenue (Product, Service, Other) and Expenses (Salaries, Rent, Marketing, Software, Taxes, Investments)
  4. Import bank data: Last 3–6 months of bank statements as baseline
  5. Enter actual data: Fill completed months with real numbers

Phase 2: Build (Day 3–5)

Goal: Model calculates automatically, scenarios are possible.

  1. Build formulas: SUM, IF, VLOOKUP for automatic calculations
  2. Link liquidity plan: Opening balance + Revenue − Expenses = Closing balance
  3. Conditional formatting: Define warning thresholds (yellow < €10,000, red < €0)
  4. Create scenarios: Copy base case, vary assumptions (±20% revenue)
  5. Create dashboard: Line chart (cash flow), bar chart (revenue vs. expenses), KPIs (burn rate, runway)
  6. VAT calculation: Automatically calculate quarterly prepayments

Phase 3: Review & Maintain (Ongoing)

Goal: Model stays current and decision-relevant.

  1. Weekly: Update bank data, enter new transactions
  2. Monthly: Actual vs. plan comparison, adjust forecast, go through monthly checklist
  3. Quarterly: Quarterly review, validate assumptions, update scenarios
  4. Annually: Model audit, review structure, evaluate upgrade to specialized software

Weekly time investment: 30–60 minutes (when the model is well built).

You can save those 30–60 minutes per week. Finban syncs your bank accounts automatically, categorizes transactions, calculates your forecast, and shows you scenarios at the click of a button. Start for free →