Financial Modeling in Excel & DCF Valuation - from scratch

Define the Stock smiles scenario: China sourcing via Alibaba, sales on Amazon USA in 2023, with initial assumptions, capex, and straight-line depreciation.

Explore qualitative guidelines for building a robust financial model, including color-coded blue input values, formula-based calculations, linking inputs, avoiding hard coding, and repeating linked data for clarity.

Set up a five-year financial model with separate profit and loss statement, balance sheet, and cash flow statement; format for printability with headers, indentation of subaccounts, borders, and clean styling.

Set up a schedules sheet to house calculations separate from financial statements, link years from the assumptions, and apply consistent formulas across profit and loss, balance sheet, and cash flow.

Compute cost of sales across years using the year-one cost as base and 6% inflation, then multiply by volume to reveal gross profit on the profit and loss.

Model administration cost as 10% of revenue and warehousing and shipping as 15%, using schedule links and absolute references to feed the profit and loss statement.

Calculate marketing costs by applying 25% of revenue to advertisement costs and 25% on affiliate sales (affiliate sales = 30% of revenue), then integrate into profit and loss and ebitda.

Explore fixed assets and depreciation in Excel by building simple and advanced schedules, calculating depreciation, net book value, and accumulated depreciation for accurate balance sheet reporting.

Build amortization schedules for intangible assets, calculate annual amortization at 20%, track opening and closing values, and derive net book value, operating profit, and balance sheet linkage.

Link annual sales to receivables, compute daily sales from 365 days, and multiply by 14 to estimate funds tied up, with payables covered in the next lecture.

Link inventory, receivables, and payables to the balance sheet by transferring values from schedules, updating the balance sheet accordingly.

Compute beginning-of-year equity by linking to end-of-year figures, add debt and incremental investment, derive total and incremental equity, and plan to retain profits as equity.

Complete the profit and loss statement to calculate net profit by incorporating interest income, interest expense, and tax expenses, with tax rate sourced from the assumptions page.

Calculate retained earnings and dividends from yearly profits, and determine incremental equity for the balance sheet. Use if-then logic to ensure dividends adjust when profits change.

Apply subtotals and totals to complete a balance sheet, compute total assets, liabilities, and equity, and add conditional formatting to flag imbalances before moving to the cash flow statement.

Learn to build a cash flow statement from the balance sheet by linking profit before tax, depreciation, and working capital changes to operating, investing, and financing activities.

Ensure no hardcoded values across the three statements by using constants, verify formulas across columns, paste values, format numbers, and confirm print layout for cash flow, balance sheet, and P&L.

Master scheduling formatting in Excel by adding headers, removing hardcoded constants, formatting percentages and numbers, ensuring smooth sheet transitions, and highlighting totals for a polished financial model.

Trace values backwards in a financial model using control left bracket shortcuts to expose how balance sheet debt and working capital derive from assumptions, investment, inventory, and cost of sales.