b. Create formulas to calculate the following:

Accumulated depreciation (all assets use the straight-line method; all assets

acquired any time during the year get a full year's initial depreciation)

Current year's depreciation (straight-line method, full amount for initial year in

which asset acquired)

Ending accumulated depreciation

Net book value at end of period

Current year in the cell to the right of the phrase "Depreciation schedule for year"

Column totals for acquisition cost, beginning depreciation, current depreciation,

ending accumulated depreciation, net book value

In the cell to the right of the arrow following the text "Cross-footing test," create a

formula that checks whether the sum of the net book value column equals the sum

of acquisition costs minus the sum of ending accumulated depreciation. If the two

values match, the formula should display the text "Okay"; otherwise, it should display the text "Error."

c. Create a table at the bottom of your worksheet that consists of two columns: (1) asset

name (values should be chair, desk, laptop, monitor, software, and workstation); and

(2) net book value (create a formula to calculate this number), assuming that the current date is 06/30/2011. Then:

Create a formula that sums the total net book values for all classes of assets.

In the cell to the right of the total net book values for all asset classes, create a

formula that compares the total net book values for all classes of assets to the

sum of all net book values in the top portion of the spreadsheet. The formula

should return "Okay" if the two totals match or "Error: Sum of net book values

by asset class does not equal sum of all net book values" if the two totals do not

equal one another.

P14-7 My Name Depreciation Schedule for year 2011 Date Place Estimated Account Number Description 11001 Desk 11100 Laptop 11200 Workstation 11050 Chair 11500 Software 11500 Sofware 11500 Software 11300 Monitor 11300 Monitor 11300 Monitor 11001 Desk 11001 Desk 11050 Chair in Service Net Book Value $920 $1,200 $580 $850 $250 $475 $4,275 $100 $400 $250 $50 $0 $0 $0 $100 $100 $100 $150 $250 $125 240 800 990 700 250 1400 900 350 1100 375 340 1250 225 80 400 330 0 250 700 0 175 0 125 170 0 225 320 1200 1320 700 500 2100 900 525 1100 500 510 1250 450 $180 $1,200 $580 $50 $250 $0 $0 $275 $100 $100 $490 $250 $800 Totals $15,650 $1,625 Cross-Footing Test ---> Asset Name Desk Laptop Workstation Chair Software Monitor Total 4/5/08 5/2/09 3/25/08 2/1/05 7/1/10 6/30/09 1/31/07 2/20/09 9/30/05 10/15/08 4/4/09 8/8/05 6/30/10 Acquistion Salvage Cost Value Useful Life 5 5 5 5 3 3 3 4 4 4 5 5 5 $8,920 $2,455 $11,375 $4,275 Okay Okay $500 $2,400 $1,900 $750 $750 $2,100 $900 $800 $1,200 $600 $1,000 $1,500 $1,250 Current Beginning Net Book Period Accumulated Accumulated Depreciation Value Depreciation