Excel for Finance: Building a Three-Statement Operating Model

Course details

A three-statement operating model incorporates your company’s income statement, balance sheet, and cash flow statement to provide an integrated and complete view of your organization’s financial health. Follow along with Chris Reilly, financial modeling expert and experienced Excel trainer, as he shows you how to build a three-statement model in Excel. Chris explains how to clean up source data, build the cash flow statement, build EBITDA, and forecast the income statement, balance sheet, debt schedule, and more.

Concepts

Introduction

FP&A in Excel
What you should know
What you're going to build

Cleaning Up Your Source Data

Call with the CFO
Initial groundwork and freeze frames
Identifying hardcoded data
Applying the currency format
The value of a placeholder row
Initial error checking
Expanding on error checking
Formatting guide
Indenting and cleanup

Building the Cash Flow Statement

Build the cash from operations
Build the cash from investing
Build the cash from financing
Build the change in cash and ending cash

Building EBITDA

An overview of EBITDA
Building the EBITDA schedule

Forecasting the Income Statement

Foundation of a good forecast
Build the annual income statement
Build the annual balance sheet
Calculating year-over-year growth
Forecasting the revenue
Forecasting the cost of goods sold
Forecasting the variable expenses
Forecasting the fixed expenses
Forecasting the headcount
Headcount in outer years
Forecast the income tax expense

Forecasting the Balance Sheet - Current Assets and Liabilities

An overview of the balance sheet
How to lay the net working capital
Forecasting the accounts receivable
Forecasting the inventory
Forecasting the accounts payable
Modeling prepaid expenses and accruals
Building the bonus accrual
Building the tax accrual

Forecasting Balance Sheet Assets - Long-Term Assets and Liabilities

An overview of CapEx and depreciation
How to lay the fixed assets schedule
Building the CapEx forecast
Building the depreciation waterfall
Modeling existing depreciation
Connecting the fixed assets to the model
Quick cleanup
How to lay the debt schedule
Groundwork of the cash flow sweep
Finishing the cash flow sweep
Modeling the revolver
Getting started with term debt
Setting up term debt amortization
Modeling the term debt amortization
Connecting the debt schedule to the model

Building the Covenants

Model the leverage ratio
Model the fixed charge coverage ratio

Building the Summary

Pre-summary cleanup
Link up the one-pager - Part 1
Link up the one-pager - Part 2
Stress testing the model

Conclusion

Email to the CFO
Epilogue - Real-life application of the model