Financial Modeling and its Business Applications

Develop core skills in accounting, finance concepts, macroeconomic assumptions, and Excel to forecast the income statement, balance sheet, and cash flows, aligning figures with business strategy.

Explore how data drives financial modeling, contrasting past historical data with future forecasts, and overview essential components and tools like Excel, Power BI, and Tableau for input calculations and outputs.

Explore two approaches to financial modeling: a mechanical, story-driven method using past data, and an artistic, Excel-driven method focused on structure and growth strategy.

Explore how the business and accounting cycles interlink, with cash as the driving asset, and operating assets fueling revenue through balance sheets, P&L, and cash flow statements.

Identify the three components of a model—inputs on a single worksheet with a style guide, and calculations that connect inputs to outputs, using 100,000 and 10% as examples.

Define a model as inputs, calculation, and output that updates with changing inputs, using the three financial statements—income statement, balance sheet, and cash flow statement—for forecasting and valuation.

Learn the financial modeling toolkit with basic formulas for growth and arithmetic operations, and apply boolean logic like greater-than, less-than, and equal-to to keep models simple and forward-looking.

Explore how to build the BCE schedule using share capital, beginning and ending balances, and changes, including buybacks and equity contributions, and link to the balance sheet.

Examine the structure of the income statement, identify revenue as the umbrella and cost of sales as the neck, yielding the top line and gross profit.

Understand how revenue flows from the top line to ebitda and down to profit after tax, detailing operating expenses, depreciation, other income, and taxes shaping the bottom line.

Learn to forecast income statements by identifying price and quantity drivers, applying growth factors, and modeling costs, depreciation, EBITDA, operating profit, other income, finance costs, and taxes.

Explore the balance sheet structure, separating assets and funding (equity and liabilities), and identify drivers such as property, plant and equipment, inventories, trade receivables, and cost of sales.

Duplicate worksheet by right-clicking to move or copy, then create a copy using Ctrl key; name copies like cover, hour, and menu, and add model information and table of contents.

Create a cell style guide sample to standardize input cells with a blue font, blue border, blue fill, and one-decimal number format, then apply it across the model.

Explore building a financial model with a table of contents, covering inputs, calculations, outputs and analysis, plus assumptions and scenarios across monthly income statements, balance sheets, and cash flow statements.

Finalize the financial model's menu by defining business name and location, setting currency to US dollar, formatting input cells, adding shapes and separators, and naming ranges like company and operations.

Create a model template cover page for a financial model, link company name, location, and operations, adjust layout and fonts, and add a table of contents with an iPad link.

Learn to build a yearly worksheet layout for financial modeling, using the Edith formula to calculate end dates, extract years, and navigate with freeze panes and a menu link.

Duplicate yearly worksheets to a 60-month monthly sample, use a period counter with drag fill, and build income statement, balance sheet, cash flow, and analysis sheets.

Create hyperlinks to the table of contents in a financial model, linking calculations, monthly financials, income statements, balance sheet, cash flow statements, and analysis with screen tips for navigation.

Assess data quality to avoid garbage in, garbage out in financial models, and build an information request list from a client brief like Vumbaca, a Colorado bread company.

Create an information request list that captures macroeconomic assumptions, financing and use-of-funds, working capital, and operating expenses to forecast profit and loss, balance sheet, and cash flow in financial modeling.

Identify direct costs per unit, outline cost of sales and operating expenses, plan staffing and working capital, then forecast revenue from a base year.

Identify the required information for building a financial model, including Capex, non-current assets, licensing, pre-operational expenses, and marketing, and prepare an information request list for clients.

Define monthly sales targets (5500 Whitbread, 5000 multigrain units), estimate staffing and expenses, research data gaps with the client, and consolidate all inputs into a single financial model.

Set up year-specific inflation inputs in the cost incremental factor using five-year forecasts rather than a single rate. Source data (Statista) and link data sources in a worksheet for transparency.

Calculate and incorporate macroeconomic assumptions, including consumer price index (cpi) and price index forecasts, corporate tax rate of 21%, depreciation rates, and maintainable factors, to build a robust financial model.

Extract funding assumptions by detailing funding sources—owners contribution, debt, and equity—and calculating total funding in a flexible model currency, including pre-operational expenses like research and development and business development.

Extract the cost of funding by selecting realistic interest rates (bank loan, SBA 11.75%) and a four-year loan. Define cost of equity (risk-free 3.82%, 15%) to defend assumptions.

Structure a financial model by mapping use of funds to items like registration, professional fees, assets, inventory, and marketing, and automate the funding balance.

Analyze pricing and unit cost assumptions by setting up revenue and cost structures, linking per-unit costs, and enabling period-based price updates to reflect time value of money.

Extract and quantify personnel assumptions for the financial model by defining headcount, salary per head, and fictitious inputs for client review and outsourcing scenarios.

Identify and set monthly operating cost assumptions, including rents, utilities, maintenance, internet subscriptions, marketing, insurance, logistics, and miscellaneous expenses, using 2023 data with inflation considerations.

Extract operating working capital assumptions from the client brief and set them in a single input cell, then show how inventories, receivables, prepayments, payables, and other liabilities drive the model.

Organize your financial model with a single assumptions worksheet and use outline to group and collapse sections, providing easy navigation for macroeconomic, general, and operations inputs.

Link inputs to calculations to keep the model navigable while applying macroeconomic assumptions. Use two inflation approaches—inflation index and growth—and enable client updates by converting inflation to an index.

Build and link the equity schedule in a financial model, calculating beginning balances from owner contributions, and retained earnings from net profit minus dividends using a 30% dividend payout ratio.

Link assumptions to build a debt schedule tracking beginning balance, new loans, repayments, ending balance, and PMT-based interest expense with a restriction to finish on schedule.

Classify the use of funds by linking business registration, assets, inventory, awareness, and working cash to total funds raised, and organize pre-operational and operating expenses, capex, and cost of sale.

Build an asset schedule to track beginning balance, capex, depreciation, and ending balance over five years, linking new capex to the PPE schedule and illustrating accompanying expenses for revenue growth.

Explore maintainable capex and depreciation in an asset schedule, linking depreciation to reinvestment and capacity planning. See how input adjustments reflect growth, inflation, and keeping assets productive to sustain output.

Build the revenue calculation by linking pricing, pricing index, and quantity to forecast annual revenue for two products, Whitbread and Multigrain.

Calculate cost of sales using a cost inflation index and linked inputs, then derive cost per unit, revenue per unit, and the five-year average price and cost per unit.

Build a salary schedule by linking inputs, applying the inflation index, and calculating total salaries across staff over 12 months for auditability.

Link revenue, cost of sales, and SG&A to calculate operating working capital, and model current assets, current liabilities, inventory, trade receivables, and prepayments for an end-to-end WC schedule.

Forecast balance sheets by building assets, including non-current and current assets, property, plant and equipment, inventories, and cash, and link ending balances to the total assets and liabilities.

Link current liabilities and tax payable to the balance sheet, define equity as capital contributions and retained earnings tied to net profit and dividends, for the cash flow statement.

Build the cash flow statement from profit before tax by adding depreciation and changes in operating working capital, then outline investing and financing activities including debt, equity, and dividends.

Build a cash flow schedule by calculating beginning cash balance, cash generated, and linking cash flow to the balance sheet while deciding whether to capitalize or expense startup costs.