Asset and Debt Schedules

At this stage, the projected balance sheet and profit and loss accounts are complete except for the effects of capital expenditure (CapEx)—purchase, disposal and depreciation, long-term debt, fresh issues, repayments, and interest charges. The fixed asset, depreciation, and debt schedules are very important to our model as they tend to appear as very significant amounts in financial statements. These are long-term balances and are not covered by growth drivers. You will rely on the client to give you information about their plans for CapEx and debt for over the next five years. If you have no information on this, you will generally assume that the existing balances will continue to be serviced for the duration of the projected years, or until they are fully written down or off—whichever comes first.

This chapter covers the following topics...

Understanding the BASE and corkscrew concepts

These are common standards to follow in modeling our balance sheet items. BASE is an acronym that 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:

We notice that the movement is from the opening balance, which goes down the rows of the first year to the closing balance, then back up to the opening balance of the second year, then down the rows of the second year, and so on. This creates a corkscrew effect, as seen in the following 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

Our subject matter is referred to as long-term assets, fixed assets, and property plant and equipment. An asset is a long-term asset that the company will derive economic value from by using it for over a period of more than one year. This period is called the useful life of the asset. It would be unfair to charge the entire cost of such an asset in the period in which it was acquired; instead, the cost should...

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 preferred and is a more precise 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 (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...

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 method or a quick and simple method.

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

Creating a loan amortization schedule

Let's assume that you work at a bank and a customer has requested a home loan. However, the customer doesn't want any of the pre-made packages on offer and wants a customized loan for a specific tenure and amount. In such cases, the calculations to compute the detailed schedule might take a lot of time, which is valuable when it comes to dealing with customers. It would be really useful to have a unique model tailored to your needs, which can easily compute values for all kinds of loans. We will now learn how to create one such schedule here, implementing most of the things we have learned in this chapter.

Creating the template

Our first step is to create a common template that...

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.

In the next chapter, we will perform our final calculations and prepare the cash flows in order to arrive at the accurate statement, which should cause our balance sheets to balance and conclude the 3-statement model.