3 Statement Model: Income & Balance Sheet | A Simple Model (2024)

In this video you will learn to build a fully functional and dynamic three-statement financial model in Excel. A three-statement model links the income statement, the balance sheet and the cash flow statement of a company, providing a dynamic framework to help evaluate different scenarios. It is the foundation upon which all thorough financial analysis is built.

This video will follow the procedure outlined in the previous video titled Overview of the Process, but the model built will be far more thorough.

For this exercise two years of historical financial data are provided to build the model. The historical data is visible in the image that follows. To complete this step you will need to link the information contained on these two worksheets to the template available on a separate worksheet.

Once the historical data has been included in the template, the next step is to project the income statement. For most items on the financial statements, the historical information provides sufficient data to project the future. Some items, however, must first be calculated on a different financial statement or on a supporting schedule. All such items will be shaded purple to indicate that this data will be linked later in the process.

On the income statement, two items will be shaded purple:

1. Interest Expense: This is calculated on the Debt Schedule.
2. Depreciation: Depreciation is calculated on the PP&E Schedule.

With the income statement projected (purple-shaded line items excluded), the next step is to project the balance sheet. Five items will need to be shaded purple on the balance sheet for the same reason outlined above.

1. Cash: Cash must first be calculated on the cash flow statement.
2. PP&E, net of Accum. Depreciation. This is calculated on the PP&E schedule.
3. Line of Credit: This is calculated on the Debt Schedule.
4. Current Maturities of Long Term Debt: This is calculated on the Debt Schedule.
5. Long Term Debt, Net of Current Maturities: This is calculated on the Debt Schedule.

With the balance sheet projected, the next step is to project the cash flow statement. Four items will be shaded purple on the cash flow statement.

1. Depreciation: Depreciation is calculated on the PP&E Schedule.
2. Capital Expenditures: This is calculated on the PP&E schedule.
3. Revolving Credit Facility (Line of Credit): This is calculated on the Debt Schedule.
4. Long Term Debt: This is calculated on the Debt Schedule.

With the three primary financial statements projected, the next step is to build the supporting schedules. As these schedules are built the items shaded in purple can be appropriately linked to complete the model.