In this assignment you will create a spreadsheet
for calculating depreciation amounts and salvage values. Your spreadsheet should look EXACTLY like the following:
The user of this worksheet should be able to
enter the initial value of the asset (Value), and the MACRS Class and get a
table indicating by year the percentage and amounts of depreciation.
The MACRS Class value should use the Data Validation List feature to
ensure that the user is only able to enter a blank (no value) or one
of the available years from the MACRS Table.
When the user provides the Tax Rate,
MACRS Year Salvaged, and the Market Value the calculator should
provide the Book Value, Gain or -Loss, Taxes Owed or -Tax Credit,
and the Net Cash Flow from Salvage.
NOTICE: the MACRS Year Salvaged value should use the Data Validation
List feature with the OFFSET function to ensure the user is only
able to enter a blank (no value) or one of the available years for
the selected MACRS class.
Book Value should be calculated using the SUM and OFFSET functions
to find the sum of depreciated values through the Year Salvaged so
they can be subtracted from the asset's Value.
Print out a values view.
Print a formulas view.
Print gridlines on both views.
Print row and column headings on both views.
(Make this a habit for the duration of the course.)
You will need to use the INDEX and MATCH functions to retrieve the
NOTE: Sometimes when a
cell is formatted as a number some cells may display the pound or number
sign (i.e., "##########"). Don't panic! This
simply means that EXCEL is unable to display these numbers in the format you
asked for because the column is not wide enough. If this happens, simply
increase the width of the column.