Financial Modeling Bootcamp: Model all 3 Financial Stmts

Learn the three core principles of financial modeling: dynamic models, breaking out assumptions, and formulas that flow from assumptions to build flexible, scalable models.

Apply the third principle: keep your financial model clean, beautiful, and easy to read by proper formatting, whitespace, and clear labeling. Color input cells blue, other cells black.

Explore building a financial model across income statement, balance sheet, and cash flow, including EBITDA, segment-level sales, and scenario and sensitivity analyses.

Understand how audited financial statements provide reliable data for modeling, featuring the independent auditors report, CFO attestation, and notes detailing balance sheets, income statements, cash flows, and taxes.

Project income by breaking revenue into segments with exact or rough breakouts, forecast each segment by year, then derive gross profit margin and expenses as a percent of sales.

Apply management-provided growth rates to each segment to project income, then sum segment revenues to obtain total revenue and company growth. Use conservative assumptions and consider industry reports if needed.

Project income statement metrics by forecasting revenue growth, calculating gross profit margin, and modeling expenses as a percent of sales using three-year averages and blue-labeled conservative assumptions.

Project dynamic income statement items by tying revenue, gross margin, and other expenses to percent-of-sales, back into cost of goods sold, and forecast net income with tax and operating income.

Forecast the balance sheet by modeling working capital from current assets and liabilities, then project accounts receivable with days receivable and revenue, applying the same method to accounts payable.

Forecast inventory turnover by dividing cost of goods sold by end-of-period inventory, projecting toward a 17x benchmark, and adjust assumptions to reflect cash impact.

Forecasting other current assets, this lecture uses 1% growth rate for prepaid expenses and other assets, applying end-of-period = prior amount times (1 + growth rate) to the balance sheet.

Forecasts accounts payable using AP days and cost of goods sold to estimate cash outflow, extends AP days to 50, and applies growth to accrued expenses while deferring long-term debt.

Project other current and long-term assets and liabilities using growth rates or zero growth, emphasizing accounts payable, inventory, and accounts receivable, and tie totals to liabilities and stockholders’ equity.

This bonus lecture teaches building a full depreciation schedule with a six-year useful life, subtracting non depreciable land, and applying yearly depreciation to existing and new capital expenditures.

Compute straight line depreciation for asset purchases using cost, salvage value, and a six-year life, project five years, and link the depreciation schedule to the balance sheet.

learn to project property, plant and equipment by starting with the prior period balance, adding capital expenditures, and subtracting depreciation to derive ending PP&E and accumulated depreciation.

Model goodwill and other intangibles, allocate assets and liabilities to explain the 50 million value, and forecast annual amortization of 1.7 million.

Forecast the debt schedule by building total payments from quarterly interest and principal amortization, using notes with fixed payments and interest rates from the financial statements.

Build and reconcile a debt schedule by projecting current portion and long-term debt, inputting principal payments, and deriving interest as the difference for an amortization view.

Build a two-loan amortization schedule to compute quarterly payments, interest, principal, and balances, improving accuracy of interest expense and debt payments.

Learn to build an automated debt amortization schedule in Excel using SUMIFS to sum annual interest and principal, align with yearly cash flows, and model debt instruments up to three.

Calculate stockholder's equity for a private company by carrying forward common stock and accumulated earnings, net income minus dividends, then perform model check and link to the cash flow statement.

Build the cash flow statement by starting with net income, then adjust for depreciation and amortization and changes in working capital to reveal true cash and financing activities.

Tie the balance sheet to the cash flow statement and income statement, handle circular references as a normal part of modeling, and estimate interest income on cash and short-term investments.

Learn to enable iterative calculation for circular references in a financial model, manage errors with a circularity breaker, and keep the balance sheet, income statement, and cash flow tied together.

Create a scenario analysis to test upside and downside revenue paths, exploring EBITA, gross profit margin, and operating expenses across years.

Link a dropdown to scenario data with a lookup formula to pull upside, base, or downside revenue growth, margins, and expenses for ROIC and valuation analysis.

Perform a sensitivity analysis of free cash flow using a data table to test varying sales growth and gross margins and assess the fixed charge coverage ratio.

Sensitize the fixed charge coverage ratio through data tables and scenario analysis to test loan covenants and optimize gross profit margins.

Perform sanity checks to catch errors by tracing anomalies in the model back to their source. Examine the unexpected free cash flow jump, margins, ROIC, and quick ratio across statements.

Spot errors in your financial model by analyzing the cash flow statement, focusing on operating activities, accounts receivable and payable movements, depreciation, and linked balance sheet sanity checks.

Celebrate completing the course and master core financial modeling items, including depreciation and amortization schedules. Review the solution workbook with tables and the organization schedule, and explore free resources online.