Lesson 74 - Intermediate Accounting Course 

74. Building a Simple Financial Model in Excel

Financial modeling in Excel helps businesses, investors, and analysts forecast financial performance, evaluate investments, and make data-driven decisions. A simple financial model typically includes revenue, expenses, profitability, and cash flow projections.

1️⃣ What is a Financial Model?

A financial model is a spreadsheet that simulates a company’s financial situation based on assumptions about revenue, costs, and other variables.

📌 Common Uses of Financial Models
✔️ Startup financial planning 📊
✔️ Investment analysis 💰
✔️ Budgeting and forecasting 📈
✔️ Business valuation 💼

2️⃣ Steps to Build a Simple Financial Model in Excel

💡 Example: We’ll create a basic financial model for a small online business selling digital products.

Step 1: Set Up the Spreadsheet

🔹 Open Excel and create separate sections for:
✔️ Assumptions
✔️ Revenue Forecast
✔️ Expense Forecast
✔️ Profit Calculation

Step 2: Define Key Assumptions

In Sheet1, list key assumptions in column A and their values in column B.

📌 Tip: Use named ranges (Formulas > Name Manager) for easy reference. 

Step 3: Revenue Forecast

Formula:

Revenue= Units Sold × Price per Product

💡 Example in Excel (C2):

=B2 * B3

To forecast growth, use: 

=C2 * (1 + B4)

Copy this formula across columns to project for multiple months. 

Step 4: Expense Forecast

🔹 Fixed Costs: Rent, salaries, software, etc. → Remains constant
🔹 Variable Costs: Based on units sold

Formula for Total Cost:

Total Cost= Fixed Costs+ (Variable Cost per Unit × Units Sold) 

💡 Example in Excel (D2): 

=B5 + (B6 * B3)

Step 5: Calculate Profit

Profit = Revenue−Total Costs

💡 Example in Excel (E2):

=C2 - D2

To calculate Net Profit After Tax, use: 

=E2 * (1 - B7)

3️⃣ Formatting the Financial Model

✔️ Use cell references instead of hardcoding values.
✔️ Apply conditional formatting to highlight negative values.
✔️ Insert charts (Bar or Line) to visualize revenue and profit trends.

4️⃣ Final Thoughts

✅ A simple financial model provides a clear snapshot of a company’s future financial health.
✅ Automate calculations with formulas to adjust projections quickly.
✅ Expand the model by adding cash flow analysis, debt payments, or investment returns.