Financial modeling: Build a forward looking integrated model

Learn to forecast forward-looking financial statements using historical ratio analysis, trend extrapolation, and linking the income statement, balance sheet, and cash flows in a dynamic, integrated model.

Master the three core financial statements—income statement, balance sheet, and cash flows statement—and their interlinkages, including cash flows from operations, financing, and investing, for forward-looking forecasts.

Analyze a publicly listed U.S. retailer to build a five-year forward-looking financial statements model, using historical trend analysis or public-domain data to form assumptions.

Master the six-step forecast: capture historical income statements and balance sheets, compute ratios, extrapolate, and project income, balance sheet, and five-year cash flow for a public retailer.

Capture the historical income statement and balance sheet in Excel to seed forecasts, using 2012–2016 data and the annual report for revenues, costs, ebit, taxes, assets, and liabilities.

Analyze income statement and balance sheet ratios from 2013 to 2016 and compute revenue growth, cost of goods sold and SG&A as percentages of revenues, depreciation, interest, and tax rate.

Calculate historical balance sheet ratios for a retailer, including inventory, accounts receivable, plant and machinery, and accounts payable as percentages of revenues or cost of goods sold, using Excel formulas.

Forecast future financial statements by extrapolating key ratios from historical trends, projecting revenue growth, cost of goods sold, SG&A, depreciation, interest, and the tax rate over five years.

Extrapolate future balance sheet ratios for a large US retailer by averaging historic inventory, receivables, plant and machinery, and payables as a percentage of revenue.

Review how we capture income statements and balance sheets in a standardized format and compute key ratios. Explain how we extrapolate these ratios to forecast income statements and balance sheets.

Forecasts a 5-year income statement from 2017 to 2021 using growth rates and key ratios to derive revenues, cost of goods sold, sg&a, depreciation, ebit, taxes, and profit.

Forecast the assets side of the balance sheet using revenue-based ratios for inventory and accounts receivable, and forecast plant and machinery with revenue, then sum total assets.

Forecast the balance sheet liabilities and common equity from 2017 to 2021, assuming no new debt or equity, with debt unchanged and accounts payable at 10.9% of cogs.

Learn to prepare a cash flows statement by outlining its three buckets: operating, investing, and financing. Examine how these flows reveal year-to-year cash generation for stable and growth businesses.

Forecast cash flow from operations by starting with net profit after tax, adding back depreciation and interest, and adjusting for accounts payable, accounts receivable, and inventory.

Learn to calculate cash flow from investments by deriving capital expenditure from fixed assets, depreciation, and end-of-year versus beginning-of-year balances, plus changes in long-term assets and liabilities.

Forecast cash flow from financing by calculating new equity raised and new debt, subtracting interest expense, and applying the shareholder equity equation to end-of-year and beginning-of-year equity.

Aggregate yearly cash flows from operations, investments, and financing to compute net cash flow for 2017–2021, then add opening balances to obtain closing cash balance for each year.

Link the cash flows statement to the balance sheet to produce an integrated set of financial statements. Validate that assets equal liabilities each year, 2017 to 2021.

Follow a six-step approach to forecast a US retailer's income statement, balance sheet, and cash flow statement by using historical ratios, extrapolating ratios, and linking cash balance to forecast.

Provide your valuable feedback to help improve this financial modeling course and guide future students worldwide, shaping content based on your insight.