Financial Modeling and Valuation: Complete Excel Masterclass

Analyze Allied Business Ltd case study to build a robust financial model using 2024 data, projecting capacity expansion to 600,000 units, price forecasts, capex, and debt financing.

Create a professional cover page in Excel by formatting a new sheet, collapsing columns, applying borders and colors, and bolding a white-filled company name for the financial model.

Create a professional table of contents for a financial model to help users navigate inputs, assumptions, calculations, outputs, and key report with hyperlinks.

Create a dedicated guidelines and style page to ensure consistency across the financial model, defining base styles for input, calculated, total, and subtotal cells, with consistent borders and formatting.

Create a reusable financial model template to standardize layout, reduce errors, and ensure a professional, uniform structure, with printing setup and repeat header rows.

Build a scenario page in Excel to compare different business outcomes by changing assumptions, without rebuilding the model. Use a scenario selector and a combo box for economic scenarios.

Create base, best, and worst case scenarios for selling price, volume, and cost price, link them to an input sheet, and use a choose function to drive the model.

Develop a cost scenario for operating and variable costs, base it on forecast inflation, and outline best and worst cases; build the scenario page and proceed with inputs.

Create a dynamic scenario selection by linking a dropdown to the index function and scenario numbers, so best and worst cases automatically update across templates.

Document revenue assumptions in your financial model to enable a credible forecast and scenario analysis, using a macroeconomic sheet with inflation, days in the period, and units in millions.

Develop revenue assumptions by forecasting selling price, volume, inflation, and volume growth, while considering production capacity and shipping costs to estimate net revenue.

Document cost assumptions for a financial model, include cost inflation and linking costs to the scenario, and detail raw materials, utilities, rent in 2025, and SG&A costs.

Document capex assumptions for fixed assets and depreciation to support capital planning, cash flow forecasting, and balance sheet and income statement effects, including new assets, useful life, and straight-line method.

Document working capital assumptions by listing days in a period, gross revenue, cost of sales, and current assets (receivables, inventory, prepaid, order) plus current liabilities, linking historical numbers to projections.

Document income tax assumptions in a financial model, linking the input to client data at 25 percent and calculating tax effects in Excel.

Document equity and debt financing assumptions, including new debt, repayment, and interest rates, linking client data with imputed inputs and absolute references for a complete valuation.

Create revenue schedules in Excel by duplicating templates, linking assumptions, and forecasting inflation, selling price, and sales volume; evaluate capacity utilization and shipping costs for 2024–2029.

Forecast operating costs by separating variable costs, fixed costs, and sga with inflation. Link sales volume, forecast raw materials and utilities, and compute total cost of sales.

Create a fixed assets schedule in Excel by linking PPE data and applying straight-line depreciation, with a first-year half-rate when purchased and an if statement to skip depreciation after purchase.

Build fixed asset schedules for PPE and net PPE, allocating new assets and depreciation over 20 years, calculating beginning balances, additions, depreciation, and closing balances for the balance sheet.

Build a working capital schedule by linking revenue, cost of sales, accounts receivables days, and inventory days to forecast current assets and liabilities.

Calculate inventory, current assets, and liabilities from cost of sales, build a working capital summary, and analyze changes to understand cash flow impacts.

Develops an equity schedule within a financing schedule by linking preference and common equity to assumptions, modeling dividend, buyback, new issues, and closing balance.

Duplicate and structure a financial statement template, link revenue and costs to historical figures, and calculate ebitda, depreciation, interest, taxes, and net income to build an equity schedule.

Build a common equity schedule from the beginning balance, account for share issues and buybacks, determine the closing balance, and apply a 20 percent fixed dividend to net profit.

Create a depth schedule in Excel, linking inputs to the forecast, and calculating beginning balances, new issues, repayments, and interest expense under fixed and variable rates.

Construct an income tax schedule linked to earnings before tax (EBIT) using a 25% rate, and establish a default tax and balance sheet flow for foundational tax modeling.

Update the income statement by refining interest expense and current tax entries, applying the income tax schedule, and copying forward formatting to finalize net profit.

Update the balance sheet by refreshing asset schedules (ppe, current assets, accounts receivable) and liabilities (debt, overdraft, payables), balance with equity, and prepare the cash flow for the next lesson.

Update the cash flow statement by linking net income, depreciation, and working capital changes; reflect capex in investing activities and debt repayments in financing activities, then verify balance sheet checks.

Build a valuation sheet to calculate unlevered free cash flow to the firm by compiling gross profit, cost of sales, shipping, SGA, EBITDA, depreciation, and taxes into net operating profits.

Compute NOPAT, FCFF, and terminal value in a valuation workflow, linking depreciation, working capital changes, capex, and perpetual growth to discounting with a UK bond baseline.

Compute the present value of free cash flow using a midpoint factor and a discounting factor from the weighted average cost of capital to derive enterprise value and equity value.

Calculate equity and debt from financial statements, apply dividend and interest rates with tax effects, determine capital ratios, and derive the after-tax cost to master WACC for valuation.

Create a data table using what-if analysis to show how enterprise value and equity value vary with terminal growth and the weighted average cost of capital in a valuation model.

Develop an income statement summary page with historical net revenue, cost of sales, gross profit, sg&a, ebitda, net profit, and margins, plus operating, investing, and financing cash flows.

Forecast revenue, build an income statement, and visualize cash flow with line charts while mastering chart formatting and display settings to clearly show capital intensity.

Learn to build and format a chart by linking data to the summary, differentiating actual versus forecast, and visualizing gross margin, net profit margin, and capital intensity.

Analyze equity and debt to value a company through forecast-based valuation, compare equity value with enterprise value, and explore scenario analysis and Excel-based financial modeling.

Set up Excel printing for the financial model by adjusting page breaks, choosing landscape, fitting sheets on one page, and printing only the relevant pages from summary to valuation.