Project: Building a Financial Model
A Financial Model is a tool to forecast a company's future performance. We will build a simple "Lemonade Stand" model.
Step 1: Assumptions (The Inputs)
Create a separate sheet called "Assumptions". Never hard-code numbers in formulas. Always link to Assumptions.
| Item | Value |
|---|---|
| Cups Sold (Year 1) | 10,000 |
| Growth Rate | 10% |
| Price per Cup | ₹20 |
| Cost per Cup | ₹8 |
| Tax Rate | 25% |
Step 2: Revenue Schedule
Formula: Revenue = Cups Sold * Price per Cup
- Year 1: 10,000 * 20 = ₹2,00,000
- Year 2: (10,000 * 1.10) * 20 = ₹2,20,000
Step 3: Cost Schedule (COGS)
Formula: COGS = Cups Sold * Cost per Cup
- Year 1: 10,000 * 8 = ₹80,000
Step 4: Income Statement (P&L)
Link the rows:
| Item | Year 1 | Year 2 |
|---|---|---|
| Revenue | 2,00,000 | 2,20,000 |
| (-) COGS | (80,000) | (88,000) |
| Gross Profit | 1,20,000 | 1,32,000 |
| (-) Fixed Costs (Rent) | (20,000) | (20,000) |
| EBITDA | 1,00,000 | 1,12,000 |
| (-) Depreciation | (10,000) | (10,000) |
| EBIT | 90,000 | 1,02,000 |
| (-) Interest | (0) | (0) |
| PBT | 90,000 | 1,02,000 |
| (-) Tax (25%) | (22,500) | (25,500) |
| Net Profit | 67,500 | 76,500 |
Step 5: Sensitivity Analysis (What-If)
What if Price is ₹18 instead of ₹20? What if Growth is 5% instead of 10%?
Use Excel Data Tables (Alt + A + W + T) to create a matrix of outcomes.
7-Day Action Plan
Day 1: Open a blank Excel sheet. Create the "Assumptions" tab.
Day 2: Build the "Revenue Model". Link volume growth to assumptions.
Day 3: Build the "Cost Model". Separate Variable Costs (Lemons) vs Fixed Costs (Stall Rent).
Day 4: Construct the "Income Statement" linking to Revenue and Cost sheets.
Day 5: Calculate "Margins" (Gross Margin %, Net Profit %).
Day 6: Create a "Scenario Switch" (Best Case, Base Case, Worst Case) using CHOOSE function.
Day 7: Finalize the model. Format it professionally (Blue font for inputs, Black for formulas).
Quiz
Test Your Knowledge
Question 1 of 5
1. In Financial Modeling, hard-coded numbers should be:
💡 Final Wisdom: A model is only as good as its assumptions. "Garbage In, Garbage Out."