Multiply this against projected sales to find a forecast for capital expenditure. For a complete depreciation waterfall schedule to be put together, more data from the company would be required to track the PP&E currently in use and the remaining useful life of each. Additionally, management plans for future capex spending and the approximate useful life assumptions for each new purchase are necessary.
But a financial statement model is supposed to represent what we think will actually happen. And what will most likely actually happen is that Apple will continue to borrow and offset future maturities with additional borrowings. Typically, the main balance sheet section of a model will either have its own dedicated worksheet or it will be part of a larger worksheet containing other financial statements and schedules. Before we dive into individual line items, here are some balance sheet best practices. Investors and analysts should thoroughly understand how a company approaches depreciation because the assumptions made on expected useful life and salvage value can be a road to the manipulation of financial statements.
This is because sales revenue is a common driver for both capital expenditures and depreciation expense. The schedule will list the different classes of assets, the type of depreciation method they use, and the cumulative depreciation they’ve incurred at various points in time. The depreciation schedule may also include historic and forecasted capital expenditures (CapEx). Based on analyst research and management guidance, we have completed the company’s income statement projections, including revenues, operating expenses, interest expense and taxes – all the way down to the company’s net income. Suppose, however, that the company had been using an accelerated depreciation method, such as double-declining balance depreciation.
If it seems that the depreciation expense has remained constant, the company may be using a linear depreciation policy, such as the straight-line depreciation method. In such a case, it is handy to use depreciation expense as a percentage of net PP&E, or to simply roll forward the recurring depreciation amount. There are various depreciation methodologies, but the two most common types are straight-line depreciation and accelerated depreciation.
Straight Line Depreciation Method
Suppose that the company changes salvage value from $10,000 to $17,000 after three years, but keeps the original 10-year lifetime. With a book value of $73,000, there is now only $56,000 left to depreciate over seven years, or $8,000 per year. That boosts income by $1,000 while making the balance sheet stronger by the same amount each year.
Forecasting Cash and Short Term Debt (Revolving Credit Line)
In effect, this accounting treatment “smooths out” the company’s income statement so that rather than showing the $100k expense entirely this year, that outflow is effectively being spread out over 5 years as depreciation. The depreciation expense is scheduled over the number of years corresponding to the useful life of the respective fixed asset (PP&E). The straight-line depreciation method gradually reduces the carrying balance of the fixed asset over its useful life. Our Balance Sheet Forecasting Guide depreciation waterfall provides step-by-step instructions on how to forecast the key line items and how to balance a 3-statement model.
- This is just one example of how a change in depreciation can affect both the bottom line and the balance sheet.
- The final total should be the ending balance of PP&E, already net of accumulated depreciation.
- For the depreciation schedule, we will use the “OFFSET” function in Excel to grab the Capex figures for each year.
- The depreciation expense, despite being a non-cash item, will be recognized and embedded within either the cost of goods sold (COGS) or the operating expenses line on the income statement.
- Additionally, management plans for future capex spending and the approximate useful life assumptions for each new purchase are necessary.
PP&E Roll-Forward Schedule Build
The Salvage Value is not included in the Book Value calculation for the declining balance method, so that necessitates the use of the MIN and MAX functions in the above formula. Capex can be forecasted as a percentage of revenue using historical data as a reference point. In addition to following historical trends, management guidance and industry averages should also be referenced as a guide for forecasting Capex. The depreciation expense can be projected by building a PP&E roll-forward schedule based on the company’s existing PP&E and incremental PP&E purchases. But in practice, most companies prefer straight-line depreciation for GAAP reporting purposes because lower depreciation will be recorded in the earlier years of the asset’s useful life than under accelerated depreciation.
Using this new, longer time frame, depreciation will now be $5,250 per year, instead of the original $9,000. It also keeps the asset portion of the balance sheet from declining as rapidly, because the book value remains higher. Both of these can make the company appear “better” with larger earnings and a stronger balance sheet.
But in the absence of such data, the number of assumptions required based on approximations rather than internal company information makes the method ultimately less credible. The recognition of depreciation on the income statement thereby reduces taxable income (EBT), which leads to lower net income (i.e. the “bottom line”). If a manufacturing company were to purchase $100k of PP&E with a useful life estimation of 5 years, then the depreciation expense would be $20k each year under straight-line depreciation. One exception to this is when modeling private companies that amortize goodwill. Conceptually, working capital is a measure of a company’s short-term financial health. It does not matter if the trailer could be sold for $80,000 or $65,000 at this point; on the balance sheet, it is worth $73,000.
