The Book Wermz expanded its operations in March 2005 by purchasing an existing chain of bookstores. The total cost of the purchase was $5 million. Of this amount, $2.2 million was allocated to the cost of buildings, $1.0 million to the cost of store equipment, and $275,000 to the cost of transportation equipment. The buildings have an estimated useful life of 30 years (360 months) and a salvage value of $100,000. The store equipment has an estimated useful life of five years (60 months) and a salvage value of $50,000. The transportation equipment has an estimated useful life of three years (36 months) or 90,000 miles and a salvage value of $75,000. The Book Wermz uses straight-line depreciation for financial reporting purposes. For income tax purposes, it uses double-declining-balance depreciation for buildings and store equipment and units-of-production depreciation for transportation equipment.

Required Use a spreadsheet to prepare a depreciation schedule for The Book Wermz for the assets described. The depreciation schedule will contain data for April through December 2005. Rows 1 through 3 should contain “The Book Wermz”, “Depreciation Schedule”, and “April-December 2005”. Beginning in cell A5, list the following captions in column A: Asset, Cost, Salvage, Life (months), and Method. In column B, provide the data for Buildings that correspond to the captions in column A. Repeat the process for Store Equipment in column D and for Transportation Equipment in column F.

In cell A10 enter “Month”, followed by the numbers 1 through 9 (corresponding to April through December) in cells A11 through A19. In cell B9, enter “Straight-Line”. In cell C9, enter “Declining-Balance”.

In cell B11, enter a function to calculate the straight-line depreciation for April for Buildings. Click on the Function button, select the Financial category, and select SLN from the Function name list. In the dialog box, enter the appropriate cell addresses for each of the values requested to complete the function. For example, enter $B$6 for cost. Make sure you include the $ sign in front of the cell references for cost, salvage, and life because the references are to fixed locations (cells B6 to B8). Once the calculation is completed for April, copy cell B11 to cells B12 to B19 to provide calculations for May through December. The amount of depreciation in each cell should be the same. In cell B20, calculate the total depreciation for 2002 using the Summation button.

In cell C11, enter a function to calculate the double-declining-balance depreciation for April for Buildings. Repeat the process used for straight-line depreciation, except use the DDB function in the Financial category. The references in the dialog box will be the same as those for straight-line depreciation. You will need to include a reference for Period. The reference is A11 for April. Do not enter $ signs for this reference because the reference will change for months May through December. You may leave the Factor reference blank. The factor is 2 for double-declining-balance, and 2 is the default for the DDB method. Once the calculation is made for April, copy cell C11 to cells C12 to C19. The amount of depreciation should be less each month than the preceding month (declining balance). Total the column of depreciation amounts in cell C20.

In columns D and E, repeat the processes described above for Store Equipment. In column F, enter data for Transportation Equipment and calculate straight-line depreciation.

In column G enter data for the units-of-production method. In cell G10, enter the caption “Miles”. In cells G11 through G19 enter the miles the equipment was used each month, as follows: 1500, 1800, 2000, 2800, 3000, 2500, 2700, 2600, and 2200. In cell H10, enter the caption “Expense”. In cell H11, calculate the amount of depreciation for April using the equation _$F$6*(G11/90000). Copy cell H11 to cells H12 through H19. Calculate totals for each column in row 20.

In columns I and J, calculate the total straight-line and accelerated depreciation amounts for each month. Total each column in row 20.

Calculate the tax savings to The Book Wermz of using accelerated depreciation for tax purposes. In column A, enter captions for “Total Accelerated”, “Total Straight-line”, “Difference”, “Tax rate”, and “Tax savings”, beginning in cell A22. In column B, enter the amounts (cell references) that correspond with the total amounts. Use an equation to calculate the difference and assume a tax rate of 35%.

Format cells to provide a business appearance to the spreadsheet. Suppose the life of the buildings was 380 months and the life of the store equipment was 72 months. How much depreciation would the company report and how much tax would it save?