Here I am looking at one of the most common types of transactions included in a 3 statement model template. It is capital expenditures or Capex. In simplest terms, it just means money spent on items that have a useful life greater than 1 year. These are generally considered long-term fixed assets. Examples involve buying buildings, equipment, and property. I'll try to explain how to connect everything below.
If you want real spreadsheet examples, there are 3 statement models for various businesses here.
How it effects each of the 3 financial statements:
- Balance Sheet: Capital expenditures increase the non-current assets on the balance sheet, indicating an investment in long-term assets. Over time, as depreciation or amortization is recorded, the asset's book value decreases through a contra-asset line item called accumulated depreciation. This balances by your cash account going down (purchase) and fixed assets going up. Accumulated depreciation will also go up, and that is offset with the retained earnings account going down (as depreciation expenses are recognized on the income statement and net income flows to retained earnings).
- Income Statement: Unlike operational expenses, which are fully expensed in the period they are incurred, CapEx impacts the income statement gradually through depreciation or amortization expenses. This leads to a higher income in the short term compared to expensing the amount immediately. There are situations where you can have quicker depreciation schedules or bonus depreciation. Recovery periods can vary, so you always want a dynamic input for the useful life of the item(s) you have purchased.
- Cash Flow Statement: In the cash flow statement, capital expenditures are listed under cash flows from investing activities. This reflects the use of cash to invest in the company's long-term assets. High levels of CapEx can indicate that a company is investing in future growth.
Disposition of Assets:
One of the more complicated parts of integrating all the above logic into a spreadsheet is what to do if you are including a terminal value or exit value in the financial statements. For that, you are going to have to figure out how much of the sales proceeds should be applied to the assets, if any at all. Whatever amount is allocated to them will be compared against their book value to figure out the net gain/loss.
If you buy a building for $2,000,000 and then sell it for $1,900,000 in 7 years along with the business, and you have depreciated $400,000, then the book value is $1,600,000 and you have a $300,000 gain. That gain goes to the income statement as income, the cash flow goes up by $1,900,000, the $2,000,000 fixed asset value goes to $0 and the accumulated depreciation amount of $400,000 for that item goes to $0. The result of the fixed asset and accumulated depreciation zero out leaves $1,600,000 of reduced assets that need to balance out on the balance sheet. We know that assets equal liabilities + owners equity, so on assets we are net down $1,600,000. This is balanced out with a $1,900,000 increase in the cash account and $300,000 gain coming from net income that flows to owners equity. That means $1,900,000 - $1,600,000 leaves a net increase in assets by $300,000 and $300,000 from the gain in owners equity. That leaves a balanced equation where $300,000 of assets = $300,000 of liabilities + owners equity. In this case, there is no effect for liabilities.
Once you understand the above, you can start to build out the logic in 3 statement models so you get a complete accounting of what has happened and all accounts balance as various assumptions are changed. The formulas are not very difficult to build, but the underlying accounting knowledge is what can be difficult to understand. This is the case with most parts of integrating 3 statement model templates into financial projection models.
I've built 100s of different financial model templates with logic like the above included and much more. You can buy access to everything I've ever built and ever will build with The Super Smart Bundle license.
Article found in Accounting and Finance.
