AFM Mock Exam – 5-Year Integrated Financial Model Analysis (2021–2025)
Base / Best / Worst Scenarios • Built in Excel • Interactive Visualizations
<div class="tabs">
<button class="tab active" onclick="showTab(0)">Overview & Key Metrics</button>
<button class="tab" onclick="showTab(1)">Revenue & Growth</button>
<button class="tab" onclick="showTab(2)">Profitability & Margins</button>
<button class="tab" onclick="showTab(3)">Scenario Comparison</button>
<button class="tab" onclick="showTab(4)">DuPont & ROE</button>
</div>
<!-- Tab 0: Overview -->
<div id="tab-0" class="tab-content">
<div class="card">
<h2>Base Case Summary (2021–2025)</h2>
<div class="chart-container" id="overview-chart"></div>
</div>
<div class="insight">
<strong>Key Takeaway:</strong> Revenue grows steadily until capacity constraint in 2024 causes a temporary dip. EBITDA margin expands to 17.5% peak thanks to operating leverage. Net Income remains positive and robust despite rising preferred dividends. Debt amortization significantly reduces interest expense over time.
</div>
</div>
<!-- Tab 1: Revenue & Growth -->
<div id="tab-1" class="tab-content" style="display:none;">
<div class="card">
<h2>Revenue Trajectory by Scenario</h2>
<div class="chart-container" id="revenue-chart"></div>
</div>
<div class="card">
<h3>Annual Revenue Growth (%)</h3>
<table>
<thead><tr><th>Year</th><th>Base</th><th>Best</th><th>Worst</th></tr></thead>
<tbody>
<tr><td>2021</td><td class="highlight">+5.1%</td><td>+6.5%</td><td>+3.7%</td></tr>
<tr><td>2022</td><td>+5.1%</td><td>+6.5%</td><td>+3.7%</td></tr>
<tr><td>2023</td><td>+5.1%</td><td>+6.5%</td><td>+2.0%</td></tr>
<tr><td>2024</td><td class="highlight">-1.8%</td><td>+6.6%</td><td>-5.3%</td></tr>
<tr><td>2025</td><td>+2.5%</td><td>+3.0%</td><td>+2.0%</td></tr>
</tbody>
</table>
</div>
</div>
<!-- Tab 2: Profitability -->
<div id="tab-2" class="tab-content" style="display:none;">
<div class="card">
<h2>EBITDA & Net Income Trends</h2>
<div class="chart-container" id="profit-chart"></div>
</div>
<div class="insight">
<strong>Capacity Impact:</strong> In Base case, production limit (260k → 240k units) triggers the 2024 revenue and EBITDA dip. Best case assumes higher capacity and avoids this entirely.
</div>
</div>
<!-- Tab 3: Scenario Comparison -->
<div id="tab-3" class="tab-content" style="display:none;">
<div class="card">
<h2>2025 Performance Across Scenarios</h2>
<div class="chart-container" id="scenario-2025-chart"></div>
</div>
<table style="margin-top: 2rem;">
<thead><tr><th>Metric (2025)</th><th>Base</th><th>Best</th><th>Worst</th></tr></thead>
<tbody>
<tr><td>Revenue ($M)</td><td>4,480</td><td>5,081</td><td>4,060</td></tr>
<tr><td>EBITDA ($M)</td><td>704</td><td>1,296</td><td>173</td></tr>
<tr><td>Net Income ($M)</td><td>338</td><td>750</td><td>-47</td></tr>
<tr><td>EBITDA Margin</td><td>15.7%</td><td>25.5%</td><td>4.3%</td></tr>
</tbody>
</table>
</div>
<!-- Tab 4: DuPont -->
<div id="tab-4" class="tab-content" style="display:none;">
<div class="card">
<h2>DuPont Analysis – ROE Decomposition (Base Case)</h2>
<div class="chart-container" id="dupont-chart"></div>
</div>
<div class="insight">
ROE improves dramatically from negative historical levels as leverage decreases (debt paydown) and net profit margin expands. Asset turnover remains stable.
</div>
</div>
<div style="text-align:center; margin-top: 4rem; color: #64748b; font-size: 0.9rem;">
Interactive dashboard generated from the CTD Industries AFM Mock model • Data extracted from Excel files • Powered by Plotly.js
</div>
</div>
<script>
// Data from model
const years = ['2021','2022','2023','2024','2025'];
const revenueBase = [4025.01,4231.30,4448.63,4370.77,4480.04];
const revenueBest = [4078.17,4344.16,4628.35,4932.02,5080.92];
const revenueWorst = [3972.18,4119.67,4202.07,3980.75,4060.37];
const ebitdaBase = [544.10,658.36,780.68,669.18,704.42];
const ebitdaBest = [604.23,788.23,987.54,1203.31,1296.09];
const ebitdaWorst = [470.35,497.93,471.67,206.46,172.84];
const niBase = [254.73,332.82,410.33,322.81,337.94];
const niBest = [296.01,421.86,552.69,694.16,750.18];
const niWorst = [203.73,221.81,193.96,-5.93,-46.50];
const roeBase = [2.69,0.79,0.52,0.30,0.24];
// Tab switching
function showTab(n) {
document.querySelectorAll('.tab-content').forEach(el => el.style.display = 'none');
document.getElementById('tab-' + n).style.display = 'block';
document.querySelectorAll('.tab').forEach((el,i) => {
el.classList.toggle('active', i === n);
});
}
// Overview stacked bar / line
function createOverviewChart() {
const trace1 = { x: years, y: revenueBase, name: 'Revenue ($M)', type: 'bar', marker: {color: '#60a5fa'} };
const trace2 = { x: years, y: ebitdaBase, name: 'EBITDA ($M)', type: 'line', line: {width: 4, color: '#34d399'}, yaxis: 'y2' };
Plotly.newPlot('overview-chart', [trace1, trace2], {
title: 'Base Case: Revenue vs EBITDA',
yaxis: {title: 'Revenue ($ millions)'},
yaxis2: {title: 'EBITDA ($ millions)', overlaying: 'y', side: 'right'},
legend: {orientation: 'h', y: 1.1},
plot_bgcolor: '#1e2937',
paper_bgcolor: '#1e2937',
font: {color: '#e2e8f0'}
});
}
// Revenue comparison
function createRevenueChart() {
Plotly.newPlot('revenue-chart', [
{x: years, y: revenueBase, name: 'Base', type: 'scatter', mode: 'lines+markers', line: {width: 3}},
{x: years, y: revenueBest, name: 'Best', type: 'scatter', mode: 'lines+markers', line: {width: 3}},
{x: years, y: revenueWorst, name: 'Worst', type: 'scatter', mode: 'lines+markers', line: {width: 3}}
], {
title: 'Revenue by Scenario',
yaxis: {title: 'Revenue ($ millions)'},
plot_bgcolor: '#1e2937',
paper_bgcolor: '#1e2937',
font: {color: '#e2e8f0'}
});
}
// Profitability
function createProfitChart() {
Plotly.newPlot('profit-chart', [
{x: years, y: ebitdaBase, name: 'EBITDA Base', type: 'bar', marker: {color: '#34d399'}},
{x: years, y: niBase, name: 'Net Income Base', type: 'bar', marker: {color: '#60a5fa'}}
], {
title: 'Base Case Profitability',
barmode: 'group',
plot_bgcolor: '#1e2937',
paper_bgcolor: '#1e2937',
font: {color: '#e2e8f0'}
});
}
// 2025 Scenario comparison
function create2025Chart() {
const metrics = ['Revenue', 'EBITDA', 'Net Income'];
const base2025 = [4480, 704, 338];
const best2025 = [5081, 1296, 750];
const worst2025 = [4060, 173, -47];
Plotly.newPlot('scenario-2025-chart', [
{x: metrics, y: base2025, name: 'Base', type: 'bar'},
{x: metrics, y: best2025, name: 'Best', type: 'bar'},
{x: metrics, y: worst2025, name: 'Worst', type: 'bar'}
], {
title: '2025 Outcomes – Base vs Best vs Worst',
barmode: 'group',
plot_bgcolor: '#1e2937',
paper_bgcolor: '#1e2937',
font: {color: '#e2e8f0'}
});
}
// DuPont (simplified stacked for ROE components)
function createDuPontChart() {
Plotly.newPlot('dupont-chart', [{
x: years,
y: roeBase,
type: 'scatter',
mode: 'lines+markers',
name: 'ROE (%)',
line: {color: '#f59e0b', width: 5}
}], {
title: 'Base Case ROE Trend (DuPont Driven)',
yaxis: {title: 'ROE (%)'},
annotations: [{text: 'Improving from negative historical to positive', x: 2, y: 1.5, showarrow: false}],
plot_bgcolor: '#1e2937',
paper_bgcolor: '#1e2937',
font: {color: '#e2e8f0'}
});
}
// Initialize all charts
window.onload = function() {
createOverviewChart();
createRevenueChart();
createProfitChart();
create2025Chart();
createDuPontChart();
// Show first tab
showTab(0);
};
</script>
</body>
</html>