Task: Create a depreciation schedule to monitor your company’s investment in fixed assets.

a. Your spreadsheet should have the following column headings:

• Account number (5 digits, valid range from 11000 to 19999)

• Description (text, valid entries = software, laptop, monitor, workstation, desk, chair)

• Date placed in service (mm/dd/yyyy format)

• Estimated useful life

• Acquisition cost

• Beginning accumulated depreciation (prior to this year)

• Current period depreciation (straight-line method, assume no salvage value)

• Ending accumulated depreciation (beginning accumulated depreciation plus current period depreciation)

• Net book value (acquisition cost-ending accumulated depreciation)

b. In the top rows of the spreadsheet, enter the following data:

• Your name (in row 1)

• Depreciation schedule for year (in row 2)

• Two cells to the right of the text “Depreciation schedule for year,” a formula that returns the current year (i.e., if the date is 10/31/2009 your formula should return the value 2009)

c. Enter the following data:

d. Create formulas to calculate the beginning balance of accumulated deprecia¬ tion, current depreciation, ending balance, and net book value.
• Use the built-in Year(xxx) function to return the year portion of a date in mm/dd/yyyy format.
• The today0 takes no arguments and returns the current date in mm/dd/yyyy format.
• Assume that assets placed in service anytime during a year can be depreci¬ ated for the entire year.

e. Include the following validation checks, with appropriate and informative error messages should any of these be violated:
• Each entry in the columns account number, description, and date contains allowable values.
• If an asset has already been fully depreciated, current depreciation should be zero.
• Ending balance of accumulated depreciation should equal beginning bal¬ ance plus current depreciation.
• Net book value should equal acquisition cost minus ending balance of accu¬ mulated depreciation.
• The sum of all net book values should equal the sum of all acquisition costs minus the sum of all ending balances of accumulated depreciation.

f. Create a table at the bottom of your worksheet that consists of two columns:
• Asset name (values should be chair, desk, laptop, monitor, software, and workstation)
• New book value (use as a built-in Excel function to calculate this number)

Transcribed Image Text:

Account Number Date Placed Description in Service Estimated Acquisition Useful Life Cost 11001 Desk 04/05/2006 5 500 11100 Laptop 05/02/2007 5 2400 11200 Workstation 03/25/2006 5 1900 11050 Chair 02/01/2003 5 750 11500 Software 07/01/2008 3 750 11500 Software 06/30/2007 3 2100 11500 Software 01/31/2005 3 900 11300 Monitor 02/20/2007 4 800 11300 Monitor 09/30/2003 4 1200 11300 Monitor 10/15/2006 4 600 11001 Desk 04/04/2007 5 1000 11001 Desk 08/08/2003 5 1500 11050 Chair 06/30/2008 5 1250