To make some extra money, youve started preparing templates of business forms and schedules for others to

Question:

To make some extra money, you€™ve started preparing templates of business forms and schedules for others to download from the Internet (for a small fee). After relevant information is entered into each template, it automatically performs calculations using formulas you have entered into the template. For the depreciation template, you decide to produce two worksheets€” one that calculates depreciation and book value under the straight- line method and another that calculates these amounts using the double- declining- balance method. The templates perform straightforward calculations of depreciation and book value when given the cost of an asset, its estimated useful life, and its estimated residual value. These particular templates won€™t handle disposals or changes in estimates€” you plan to create a deluxe version for those functions. To illustrate that your tem-plates actually work, you enter the information used to produce the depreciation schedules shown in Exhibit 9.6, with Cedar Fair and Six Flags as examples. Although you€™re confident you can use appropriate formulas in the spreadsheet to create a template for the straight- line method, you€™re a little uncertain about how to make the double-declining- balance method work. As usual, you e- mail your friend Owen for advice. Here€™s what he said:
From: Owentheaccountant@ yahoo. com To: Helpme@ hotmail. com Cc: Subject: Excel Help I wish I€™d thought of charging money for showing how to do ordinary accounting activities. You€™d have made me rich by now. Here€™s how to set up your worksheets. Begin by creating an €œinput values€section. This section will allow someone to enter the asset cost, residual value, and estimated life in an area removed from the actual depreciation schedule. You don€™t want someone accidentally entering amounts over formulas that you€™ve entered into the schedule. The cells from the input values section will be referenced by other cells in the depreciation schedule. You will want to enter formulas in the cells for the first year row, and then copy and paste them to rows for the other years. When doing this, you will need to use what is called an €œabsolute reference,€which means that the cell reference does not change when one row is copied and pasted to a different row. Unlike an ordinary cell reference that has a format of A1, an absolute reference has the format of $ A$ 1, which prevents the spreadsheet from changing either the column (A) or row (1) when copying the cell to other cells. You may find this useful when preparing both the straight- line and double- declining- balance schedules. To create the depreciation schedules, use five columns labeled (1) year, (2) beginning- of- year accumulated depreciation, (3) depreciation, (4) end- of- year accumulated depreciation, and (5) end- of- year book value. The double- declining- balance template will be the trickiest to create because you need to be concerned that the book value is not depreciated below the residual value in the last year of the asset€™s life. To force the template to automatically watch for this, you will need to use the IF function. I have included a screenshot of a template I created, using the IF function to properly calculate depreciation for all years of the asset€™s life. Notice the formula shown in the formula bar at the top.

Required:
Create the spreadsheet templates to calculate depreciation and book value using the straight- line and double- declining- balance methods. Demonstrate that the template works by reproducing the schedules in Exhibit 9.6.