Chapter 7: Asset and Debt Schedules

The projected balance sheets and profit and loss accounts are now complete, except for the effects of Capital Expenditure (CapEx)—purchase, disposal, and depreciation of long-term assets and long-term debt; fresh issues; repayments; and interest charges.

The fixed asset, depreciation, interest, and debt schedules are very important to our model as they tend to be very significant amounts in financial statements. They are long-term balances and are not covered by growth drivers.

You will learn how long-term assets and long-term debts are treated in financial statements, along with how we arrive at the different amounts to be included in the balance sheet and profit and loss account and how the values change from year to year.

This chapter covers the following topics:

• Understanding the BASE and corkscrew concepts
• Asset schedule
• Approaches to modeling assets
• Asset and depreciation schedule
• Debt schedule
• Creating...

Understanding the BASE and corkscrew concepts

These are common standards to follow in modeling our balance sheet items. BASE stands for Beginning add Additions less Subtractions equals End. The corkscrew concept refers to the way in which the BASE setup is connected from one period to the next. In the following screenshot, we will see that the closing balance from one year is carried forward as the opening balance of the next year:

Figure 7.1 – BASE formation

We notice that the movement starts from the opening balance, goes down the rows of the first year to the closing balance, then goes back up to the opening balance of the second year, then down the rows of the second year, and so on:

Figure 7.2 – Corkscrew formation

This creates a corkscrew effect, as seen in the previous screenshot.

Asset schedule

A quick recap of our agenda is as follows:

• Record the historical profit and loss accounts and balance sheet
• Calculate the historical growth drivers
• Project the growth drivers for the profit and loss accounts and balance sheet
• Build up the projected profit and loss accounts and the balance sheet
• Prepare the asset and depreciation schedule
• Prepare the debt schedule
• Prepare the cash flow statement
• Ratio analysis
• DCF valuation
• Other valuations
• Scenario analysis
• Fixed assets including plant and machinery, land and buildings, equipment, motor vehicles, furniture, and fixtures

Any asset from which the company will derive economic value over a period of more than one year falls into the category of fixed assets. The period of producing economic value is called the useful life of the asset. Since the asset will be in use for over a year, it would be unfair to charge the entire cost of such an asset to the period in which...

Approaches to modeling assets

There are two approaches to modeling fixed assets, which are as follows:

• The detailed approach
• The simple approach

The detailed approach

The detailed approach is a more precise method than the simple method that looks at the components of fixed assets—the costs of the assets, additions, disposals, depreciation, and accumulated depreciation. Your discussions with management will give you an idea of their CapEx plans over the next five years. Where there is a disposal or sale, a fixed asset has to be removed from the books. The net book value (cost less accumulated depreciation) of that asset will be transferred to a disposal account as a debit and the proceeds of the sale will be transferred to the same account as a credit. The difference between the two will either be a profit—where the sale's proceeds exceed the net book value—or a loss—where the net book value is greater than the sale's proceeds...

Asset and depreciation schedule

The following figure is an illustration of an asset schedule and its various components:

Figure 7.8 – Full depreciation schedule

This is the full asset and depreciation schedule that should be prepared for each asset class. A description of the various parts of the asset schedule follows.

The first section contains the following information:

Figure 7.9 – First section of depreciation schedule

Here, we have several keywords, such as Depreciation Method and Asset Life. We will look at what they are for this particular schedule:

• Depreciation Method: In our case, it is the SLM.
• Asset Life: This is used to represent the useful life of the asset, which, in our case, is 10 years.
• Disposal of Assets: This section is for the proceeds of the sale of fixed assets, if any are present.
• Capex: This row shows how much was—and is projected to be—spent on fixed assets...

Debt schedule

A company's capital is made up of debt and equity, and most businesses try to maintain a steady ratio between debt and equity (a leverage ratio). The debt schedule is part of our forecast of capital structure.

The following list shows our current agenda:

• Record the historical profit and loss accounts and balance sheet
• Calculate the historical growth drivers
• Project the growth drivers for the profit and loss accounts and balance sheet
• Build up the projected profit and loss accounts and balance sheet
• Prepare the asset and depreciation schedule
• Prepare the debt schedule
• Prepare the cash flow statement
• Ratio analysis
• DCF valuation
• Other valuations
• Scenario analysis

As with fixed assets, forecasting debt can be done in one of two ways: a detailed, complex, more accurate method, or a quick and simple less accurate method.

In addition, we need to consider the treatment of interest. The question is, do we take...

Creating a simple loan amortization schedule

As mentioned in Chapter 1, An Introduction to Financial Modeling and Excel, a loan amortization schedule is a type of financial model. The overall financial decision to be made is whether or not to accept the bank's terms and take the loan.

You will build a set of assumptions made up of interconnected variables. The model will be set up to perform calculations on those variables to eventually arrive at the periodic (usually monthly) repayment. This is the amount to be paid monthly until the loan is fully repaid. It is now left to the customer to decide whether they can afford the periodic repayment now and throughout the term of the loan.

The following is a more detailed step-by-step guide to creating an amortization schedule:

1. Assumptions: The first step is to prepare a list of assumptions.

Figure 7.26 – Assumptions

2. The list, as shown in Figure 7.26, is as follows:
   • Cost of the Asset...

Summary

In this chapter, we have seen the importance of fixed asset and debt schedules. We have illustrated how they affect balance sheets, profit and loss accounts, and cash flow statements. We have learned about the base and corkscrew methods, as well as complex and simple approaches to preparing fixed assets, depreciation, and debt schedules.

It is important to note also that even outside of modeling, it is good practice for asset and debt schedules to be maintained by all companies as part of their accounting procedures. This helps to keep track of non-current assets and liabilities.

We have learned how to prepare a simple loan amortization table and also introduced one of the new functions of Excel 365, the SEQUENCE function, and we have seen how we can combine this with our amortization table to produce a dynamic numbered list of the number of repayment periods.

In the next chapter, we will perform our final calculations and prepare the cash flows in order to arrive...