Depreciation Schedules

Create an Excel worksheet.

At the TOP, enter all of these amounts given:

Truck Cost $60,000

Salvage Value $10,000

Life in Years: 5

Life in Miles: 100,000

Annual miles driven: 10,000, 30,000, 15,000, 25,000 and 20,000

MACRS percentages for 5-year property: 20.00%, 32.00%, 19.20%, 11.52%, 11.52% and 5.76%

Enter each amount in a separate cell and LABEL EVERYTHING.

Don't do any calculations for the top - just enter the information that is given.

Make a clear distinction between the top and the bottom.

At the BOTTOM, create five separate depreciation schedules - Straight-line, Double-declining-balance, Sum-of-the-years'-digits, Units-of-production and MACRS. Be sure to label each schedule.Each schedule will have 6 columns - for the year, the beginning book value, the rate, the depreciation expense for the year, the accumulated depreciation and the ending book value.

It's really important that you follow these instructions for Excel. At the top, I should see only the given amounts and their labels. No formulas. Then, at the bottom, there should be only formulas using only cell references, referring to the cells at the top. Exceptions: entering the years, entering a 1 in the formula for the straight-line rate, and entering a 2 in the formula for the double-declining balance rate. This way, you can use the worksheet to perform "what-if analysis" - for instance, change the salvage value, and see what happens.

To make sure that you only used formulas at the bottom, select CTRL+~, and make sure you don't see any numbers - only formulas.

Examples of formulas: =A3+G5 =F4-S3 =B2*B3 =C4/L7

To round the annual depreciation to whole dollars, use the ROUND function in Excel: =ROUND(your formula,0) That zero means no decimal places.

For the Double-declining-balance schedule, for the annual depreciation for the 4th year, use a formula that will reduce the book value to the salvage value.

For the Sum-of-the-years'-digits schedule, you can format your rates as fractions, up to two digits. A great way to get those fractions is to sum your year column. Then you can use that amount for the denominator, and each year for the numerator. Excel will automatically reduce your fractions to lowest terms, and that's fine.

For the MACRS schedule, for the annual depreciation for the 6th year, use a formula that will reduce the book value to zero.

I need helping with this lab