Simple Financial Statement Modeling

Overview This exercise introduces building, forecasting, and linking the three core financial statements—the Income Statement, Balance Sheet, and Cash Flow Statement. Using three years of historical data to project the next three years, a common approach for simplified 3-statement models, you will practice setting assumptions, modeling key drivers, and connecting the statements. You will analyze historical actuals to identify key performance drivers, calculate average growth rates and margins, and project the P&L for the next three years. The goal is to understand how assumptions flow through the model and ensure the Balance Sheet remains balanced. Learning Goals Identify key historical drivers from past financial statements. Translate historical trends into forward-looking assumptions. Build a fully linked 3-statement financial model (P&L, Balance Sheet, Cash Flow). Understand how operating decisions impact cash generation and financing needs. Ensure accounting consistency by reconciling cash and balancing the Balance Sheet. Key Concepts Revenue: Projecting sales based on historical trends and growth assumptions. Cost of Goods Sold (COGS): Estimating direct costs to produce goods or services. Operating Expenses: Forecasting overhead and administrative expenses as a percentage of revenue. Taxes: Applying a consistent tax rate to pre-tax earnings. Gross Profit: Revenue minus COGS, showing how efficiently the company produces its goods or services. EBITDA: Earnings before interest, taxes, depreciation, and amortization; a measure of operational profitability. Operating Income (EBIT): Earnings before interest and taxes, reflecting profitability after accounting for operating expenses and D&A. EBT: Earnings before taxes, showing profit after interest expenses but before taxes. Net Income: Final profit after all expenses and taxes, representing the company’s bottom line. Gross Margins: Using historical growth rates and gross margins to forecast future performance. Depreciation & Amortization: Linking non-cash expenses to PP&E and capital expenditures. Working Capital: Modeling receivables, inventory, and payables using turnover days. Cash Flow Mechanics: Deriving operating, investing, and financing cash flows from projected statements. Statement Linkage: Ensuring ending cash from the Cash Flow Statement matches cash on the Balance Sheet. DSO (Days Sales Outstanding): Predict Accounts Receivable based on the average time it takes to collect payment. DPO (Days Payables Outstanding): Predict Accounts Payable based on the average time a company takes to pay its suppliers. DIO (Days Inventory Outstanding): Predict Inventory based on the average time inventory is held before being sold. Other Current Liabilities (% of Revenue): Forecast miscellaneous liabilities as a percentage of revenue, using historical averages. Operating Activities: Cash generated or used in the company’s core business operations. Investing Activities: Cash flows related to the purchase or sale of long-term assets such as PP&E. Financing Activities: Cash flows arising from debt, equity, and dividend transactions. Non-Cash Adjustments: Items such as depreciation and amortization that affect earnings but not cash. Changes in Working Capital: Movements in receivables, inventory, payables, and other short-term accounts that impact cash.

Practice Simple Financial Statement Modeling with interactive Excel modeling exercises in our Financial Statement Modeling module.

This hands-on modeling exercise helps you master Simple Financial Statement Modeling through real-world Excel practice and financial modeling techniques.