FIN 3403-001 · Principles of Financial Management
FIN 3403 — Capital Budgeting Project
Project Title: Expansion Decision Analysis
Full pro forma financial model with Free Cash Flows, NPV, IRR and Payback Period — delivered as a clean Excel workbook with clearly labeled assumptions. We build it from your in-class model and the updated assumptions below.
Project Overview
In this project, you will evaluate a strategic expansion of an existing business using capital budgeting techniques. You will build a pro forma financial model, estimate Free Cash Flows (FCF), and determine whether the project should be accepted based on:
- Net Present Value (NPV)
- Internal Rate of Return (IRR)
- Payback Period
This project is based on a model built together in class. Your task is to modify, expand, and interpret that model using the updated assumptions below.
Key Assumptions
Volume & Price
- Units produced: 1,000 units per year (constant)
- Initial price (Year 1): $20 per unit
Pricing Strategy
- Price increases:
- +3% in Year 2
- +5% in Year 4
- Prices remain constant between increases
Costs
- COGS = 50% of Sales
- SG&A scales proportionally with volume (use class model logic)
- No Special Marketing Promotion
Investment
- CAPEX: $2,500,000 in Year 0
- Depreciation: Straight-line over project life (7 years)👉 Depreciation = $2,500,000 ÷ 7 ≈ $357,143 per year
Working Capital
- Use the same methodology as in class:
- Working capital grows with sales
- Include changes in working capital in FCF
- Recover working capital in final year
Taxes
- Tax rate: 40%
Discount Rate
- 10.5%
What You Need to Build
1. Income Statement (Pro Forma)
For each year (Year 1–Year 7), calculate:
- Sales
- COGS
- Gross Profit
- SG&A
- EBIT
- Taxes
- EBIAT
2. Free Cash Flow (FCF)
Use the standard formula:
FCF = EBIT(1 − T) + Depreciation − CAPEX − ΔWorking Capital
3. Capital Budgeting Metrics
Calculate:
- ✅ NPVNPV = Σ FCFₜ / (1 + r)ᵗ − Initial Investment
- ✅ IRR — the discount rate that sets NPV = 0
- ✅ Payback Period — time required to recover initial investment using cumulative cash flows
📊 Deliverables
Submit the following:
📁 Excel File
Must include:
- Clean financial model
- Clearly labeled assumptions
- Separate sections:
- Income Statement
- FCF
- NPV / IRR / Payback
Capital Budgeting Starter File — Full Contents (Case 2 – Question 4)
Below is the complete cell-by-cell content of the FIN 3403 Capital_budgeting_starter_file.xlsx (sheet: Case 2 - Question 4) so students searching for any line from this starter workbook can find the full solved version here.
Income Statement Pro Forma (1998–2004)
| Line | 1998 | 1999 | 2000 | 2001 | 2002 | 2003 | 2004 |
|---|---|---|---|---|---|---|---|
| units (000s) | 250 | 250 | 250 | 250 | 250 | 250 | 250 |
| unit price | 20 | 20.6 | 21 | 21.15 | 21.25 | 21.25 | 21 |
| Sales = units × unit price | =C3*C4 | =D3*D4 | =E3*E4 | =F3*F4 | =G3*G4 | =H3*H4 | =I3*I4 |
| cash CoGS = 50% of Sales | =C5*0.5 | =D5*0.5 | =E5*0.5 | =F5*0.5 | =G5*0.5 | =H5*0.5 | =I5*0.5 |
| Depreciation | 200 | 200 | 200 | 200 | 200 | 200 | 200 |
| Gross profit = Sales − CoGS − Depreciation | =C5-C6-C7 | =D5-D6-D7 | =E5-E6-E7 | =F5-F6-F7 | =G5-G6-G7 | =H5-H6-H7 | =I5-I6-I7 |
| Sell General Admin (grows 3% / yr) | 1250 | =C9*(1+3%) | =D9*(1+3%) | =E9*(1+3%) | =F9*(1+3%) | =G9*(1+3%) | =H9*(1+3%) |
| Special Promotion | 1000 | 1000 | 500 | ||||
| EBIT (Earnings Before Interest & Taxes) | =C8-C9-C10 | =D8-D9-D10 | =E8-E9-E10 | =F8-F9-F10 | =G8-G9-G10 | =H8-H9-H10 | =I8-I9-I10 |
| Tax (40%) | =C11*40% | =D11*40% | =E11*40% | =F11*40% | =G11*40% | =H11*40% | =I11*40% |
| EBIAT (Earnings Before Interest & After Taxes) | =C11-C12 | =D11-D12 | =E11-E12 | =F11-F12 | =G11-G12 | =H11-H12 | =I11-I12 |
| Working Capital (Current Assets − Current Liabilities) | 925 | 953 | 973 | 983 | 991 | 995 | 989 |
Assumptions: Tax = 0.4 (40%). Final-year working capital recovered = 0 in column J row 14.
Free Cash Flow (FCF) Build
| Line | 1998 | 1999 | 2000 | 2001 | 2002 | 2003 | 2004 | Term. |
|---|---|---|---|---|---|---|---|---|
| EBIT | =C11 | =D11 | =E11 | =F11 | =G11 | =H11 | =I11 | |
| Add: Depreciation | =C7 | =D7 | =E7 | =F7 | =G7 | =H7 | =I7 | |
| Minus: Tax | =C12 | =D12 | =E12 | =F12 | =G12 | =H12 | =I12 | |
| Less: CAPEX | 1400 | |||||||
| Change in Working Capital | =D14-C14 | =E14-D14 | =F14-E14 | =G14-F14 | =H14-G14 | =I14-H14 | ||
| FCF = EBIT + Depreciation − Tax − CAPEX − ΔWC | =C16+C17-C18-C19-C20 | =D16+D17-D18-D19-D20 | =E16+E17-E18-E19-E20 | =F16+F17-F18-F19-F20 | =G16+G17-G18-G19-G20 | =H16+H17-H18-H19-H20 | =I16+I17-I18-I19+I20 | =I14 |
Capital Budgeting
| Period | Year 0 (1998) | Year 1 (1999) | Year 2 (2000) | Year 3 (2001) | Year 4 (2002) | Year 5 (2003) | Year 6 (2004) | Year 7 |
|---|---|---|---|---|---|---|---|---|
| Net Cash Flow | =C21 | =D21 | =E21 | =F21 | =G21 | =H21 | =I21 | =J21 |
| Cumulative Cash Flow | =C27 | =C28+D27 | =D28+E27 | =E28+F27 | =F28+G27 | =G28+H27 | =H28+I27 | =I28+J27 |
Decision Metrics
- Discount Rate ——> 0.12 (12%)
- NPV ——> =NPV(C33, D27:J27) + C27
- IRR ——> =IRR(C27:J27)
- Payback Period ——> =2 + ABS(E28/F27)
Raw cell dump (searchable)
Sheet: Case 2 - Question 4 C1: 1998 | D1: 1999 | E1: 2000 | F1: 2001 | G1: 2002 | H1: 2003 | I1: 2004 B2: Income Statement Pro Forma B3: units(000s) | C3..I3: 250 B4: unit price | C4: 20 | D4: 20.6 | E4: 21 | F4: 21.15 | G4: 21.25 | H4: 21.25 | I4: 21 K3: Assumptions | K4: Tax | L4: 0.4 B5: Sales | C5..I5: =Cn*Cn (units × price) B6: cash CoGS | =Sales * 0.5 B7: Depreciation | 200 per year B8: Gross profit | =Sales − CoGS − Depreciation B9: Sell General Admin | C9: 1250 ; D9..I9: previous * (1+3%) B10: Special Promotion | C10: 1000 | D10: 1000 | E10: 500 B11: EBIT (Earnings Before Interest & Taxes) | =Gross profit − SG&A − Special Promotion B12: Tax(40%) | =EBIT * 40% B13: EBIAT (Earnings Before Interest & After Taxes) | =EBIT − Tax B14: Working Capital (Current Assets - Current Liabilities) | 925, 953, 973, 983, 991, 995, 989 ; J14: 0 B16: EBIT | =row 11 B17: Add: Depreciation | =row 7 B18: Minus: Tax | =row 12 B19: Less: CAPEX | C19: 1400 B20: Change in Working Capital | Dn = WCn − WC(n−1) B21: FCF | =EBIT + Depreciation − Tax − CAPEX − ΔWC ; J21: =I14 (terminal WC recovery) B24: Capital Budgeting B26: Period | Year 0..Year 7 B27: Net Cash Flow | =row 21 B28: Cumulative Cash Flow | running sum of row 27 B33: Discount Rate ----> | C33: 0.12 B35: NPV ----> | =NPV(C33, D27:J27) + C27 B37: IRR -----> | =IRR(C27:J27) B39: Payback Period ----> | =2 + ABS(E28/F27)
Completed model
FIN 3403 Capital Budgeting — Completed Project (Preview)
Below is the structure of our completed Capital_budgeting_complete.xlsx workbook — Assumptions, Price & Sales, Income Statement, Working Capital, Free Cash Flow, and NPV / IRR / Payback. All numeric values are blurred; unlock to get the full file.
Sheet 1 — Assumptions
| Assumption | Value |
|---|---|
| Units / year | |
| Initial Price (Year 1) | |
| Price increase Year 2 | |
| Price increase Year 4 | |
| COGS % | |
| Tax rate | |
| CAPEX (Year 0) | |
| Project life | |
| Discount rate | |
| Depreciation |
Sheet 2 — Price and Sales
| Year | Price | Sales |
|---|---|---|
| Year 1 | ||
| Year 2 | ||
| Year 3 | ||
| Year 4 | ||
| Year 5 | ||
| Year 6 | ||
| Year 7 |
Sheet 3 — Income Statement
| Year | Sales | COGS | SG&A | Depreciation | EBIT | Tax (40%) | EBIAT |
|---|---|---|---|---|---|---|---|
| Year 1 | |||||||
| Year 2 | |||||||
| Year 3 | |||||||
| Year 4 | |||||||
| Year 5 | |||||||
| Year 6 | |||||||
| Year 7 |
Sheet 4 — Working Capital
| Year | Sales | WC | Δ WC | Note |
|---|---|---|---|---|
| Year 1 | ||||
| Year 2 | ||||
| Year 3 | ||||
| Year 4 | ||||
| Year 5 | ||||
| Year 6 | ||||
| Year 7 | RECOVERY |
Sheet 5 — Free Cash Flow
| Year | FCF | Cumulative |
|---|---|---|
| Year 0 | ||
| Year 1 | ||
| Year 2 | ||
| Year 3 | ||
| Year 4 | ||
| Year 5 | ||
| Year 6 | ||
| Year 7 |
Sheet 6 — NPV, IRR, Payback
| NPV | IRR | Payback |
|---|---|---|
Want the full unblurred Excel file with live formulas? We'll send Capital_budgeting_complete.xlsx once your order is in.
Need this FIN 3403 project done?
We deliver a fully working Excel model — blue inputs, black formulas, clean section breaks, NPV / IRR / Payback all live, and a short accept/reject write-up if your professor wants one.