A 10-sheet expert Excel financial model built for Apple Inc. (AAPL) combining advanced financial modeling, data automation, and interactive visualization.
- Pulls real financial data via Power Query pipeline
- Builds three professional financial statements
- Calculates DCF fair value using WACC and Terminal Value
- Runs 1,000 Monte Carlo simulations for valuation range
- Calculates Beta, Sharpe Ratio, Volatility from scratch
- Scenario analysis — Base, Bull, Bear cases
- Auto-generates PDF reports with one click
- Self-documenting audit trail logging every change
- Fair Value Per Share: $142.69 (Base Case)
- Bull Case: $179 | Bear Case: $101
- Current Price: $210 (Overvalued by 47.2%)
- Beta: 0.44 | Sharpe Ratio: 0.84
- 10-Year Cumulative Return: 808%
- Monte Carlo Mean: $200.87 | Range: $110 — $360
| Sheet | Description |
|---|---|
| Dashboard | Main KPI overview with live charts |
| Control Panel | Master settings and scenario switcher |
| Data Engine | Power Query automated data pipeline |
| Financial Statements | Income, Balance Sheet, Cash Flow |
| Valuation Engine | DCF model + Monte Carlo simulation |
| Scenario Lab | One-way, two-way sensitivity tables |
| Portfolio Tracker | Beta, Sharpe, Volatility calculations |
| Audit Trail | VBA auto-logging system |
| Galaxy Command Center | Interactive bubble chart universe |
| Automation Engine | One-click PDF report generator |
- Power Query + M Language
- VBA Event Triggers and Automation
- Monte Carlo Simulation (1,000 runs)
- DCF Valuation from scratch
- XLOOKUP, INDEX/MATCH, Dynamic Arrays
- Data Tables for Sensitivity Analysis
- Conditional Formatting
- Named Range Systems
- Sparklines and Bubble Charts
- Apple Financial Statements (Investing.com)
- AAPL Stock Prices (Yahoo Finance / Macrotrends)
- S&P 500 Index (Yahoo Finance)
- Microsoft Stock Prices (Yahoo Finance)
- Download Nexus_Apple_Valuation.xlsm
- Place data files in same folder
- Open Excel — Enable Macros
- Go to Data Engine sheet — Refresh All
- Navigate using sheet tabs
- Change scenario in Control Panel (Base/Bull/Bear)
- Click Generate PDF in Automation Engine
Built as a portfolio showpiece demonstrating expert Excel skills in financial modeling, data automation, and visualization.