Whether you’re a finance professional at an NGO, a small-business CFO, or an aspiring analyst, mastering a robust Excel-based forecasting framework is essential. In this post, we’ll walk through how to build a dynamic, driver-based three-statement model—capturing the Income Statement, Balance Sheet, and Cash Flow Statement—using a real-world demo case, AGF Fresh Juice Ltd. Though our demo is denominated in Nigerian Naira (₦), all techniques apply globally.
Why a Three-Statement Model Matters
A properly linked set of financial statements lets you:
-
Project future profitability by adjusting revenue and cost drivers
-
Test scenarios (e.g., price changes, volume growth, CapEx spikes)
-
Ensure accuracy through balance-sheet integrity and cash-flow reconciliation
-
Communicate insights clearly to stakeholders and decision-makers
Case Study Overview: AGF Fresh Juice Ltd
-
Business: Production and sale of fresh juice, plus training services
-
Forecast period: 2025–2029
-
Drivers:
-
Unit sales growth (3% annual)
-
Price inflation (2% annual)
-
Cost of goods sold (raw materials 20% of revenue; packaging 5%)
-
Operating expenses (personnel 10%, marketing 3%, SG&A 2%)
-
CapEx (refrigerator, juicer machine) and straight-line depreciation
-
Debt financing (existing repayments, new loan in year 3, 7% interest)
-
Step-by-Step Model Structure
1. Build a Clean Assumptions Sheet
Create a single tab (“Assumptions”) to house all inputs—growth rates, unit prices, cost percentages, CapEx schedules, tax rate, debt terms. Link every formula elsewhere back to these cells so that updating your inputs instantly refreshes the entire model.
2. Forecast Revenue & Cost of Sales
-
Volume × Price drives juice sales.
-
Training revenue is a simple growth curve.
-
Compute raw material and packaging costs as percentages of total revenue.
3. Calculate Operating Expenses & Depreciation
-
Personnel, marketing, SG&A lines link to revenue.
-
Set up a depreciation schedule for existing assets plus new refrigerator and juicer purchases, using straight-line method.
4. Construct the Income Statement
-
Link revenue, COGS, operating expenses, and depreciation.
-
Apply a 20% corporate tax rate to pre-tax profit.
-
Output – Net Income each year.
5. Build the Balance Sheet
-
Assets: Cash, receivables, inventory, fixed assets (net of accumulated depreciation).
-
Liabilities: Payables, existing debt, and new loan drawdown/repayments.
-
Equity: Opening balance + retained earnings (cumulated net income less dividends, if any).
-
Ensure Assets = Liabilities + Equity each year.
6. Assemble the Cash Flow Statement
-
Operating Activities: Start with Net Income, add back depreciation, adjust for working-capital changes.
-
Investing Activities: Subtract CapEx spend.
-
Financing Activities: Include additional loans and repayments.
-
Confirm that the year-over-year change in cash on the Balance Sheet matches ending cash from the CF statement.
7. Run Sanity Checks
-
Does the cash-flow statement’s net change in cash equal the cash-line movement on the balance sheet?
-
Do all subtotals reconcile?
-
Format key ratios or a simple dashboard for quick insights.
Watch the Full Tutorial
For a guided, hands-on walkthrough—including Excel screen demos and on-camera explanations—watch the complete video:
▶️ Build a 3-Statement Financial Model in Excel | Beginner Tutorial
Who Should Learn This?
-
Finance & Accounting Teams (NGOs, SMEs, Corporates): Improve budgeting and forecasting rigor.
-
Students & Graduates: Learn real-world modeling techniques that hiring managers value.
-
Analysts & Consultants: Speed up scenario analysis with a repeatable framework.
Take Your Modelling to the Next Level
-
Download the companion Excel workbook HERE and the case study file HERE
-
Subscribe for advanced tutorials.
-
Join our community on LinkedIn to share tips, ask questions, and network with fellow finance professionals.