EXCEL FORMULAS / FUNCTIONS Assignment

Depreciation Schedule

Learning Objectives:

1. Creating Range Names

2. Creating variable Comments

3. Using Absolute and Relative References

4. Creating Formulas using following functions:
a. =IF()
b. =DDB()
c. =SLN()
d. =MATCH()
e. =INDEX()
f. =SUM()
g. =SUMIF
h. =AND() / OR()

Requirements:

Open 2016 F DEPRECIATION Excel File & create following range names for cell addresses indicated:
Depreciation Worksheet - E2 (YEAR)

Tax Table Worksheet:

C3..F3 (TYPE)
B4..B14 (MACRS_YEAR)
C4..F14 (TAX_TABLE)
H5: Display "xxxx Depreciation Expense" note: xxxx is the year entered in E2
H6..H20 (IF, DDB, SLN, INDEX, MATCH, SUM):

Calculate depreciation expense for each asset using depreciation method specified in column G.

Use only ONE formula, except referencing cells, i.e., create a formula for H6 and then copy & paste to H7..H20.

Display Requirements (Conditional Formatting):

If "Year Placed in Service" is greater than the year entered in E2 (named, YEAR):

Depreciation expense (column H) shows "N/A" and note (column I) displays "Not in Service."

If the asset is fully depreciated:

Depreciation expense (column H) shows "0" (zero), and note (column I) displays "Fully Depreciated."

K25..K30 (SUM, SUMIF):

Sum depreciation expense, H6..H18, by asset (description). Note: Use Only ONE formula, i.e., create a formula for K25 and then copy & paste to K26..K50.

Save your file as (Save As) DEPRECIATION & submit it in the Assignments (Excel 1).

FUNCTIONS REVIEW

=IF(condition,x,y)

Evaluates condition and returns either x if condition is true or y if condition is false.
=AND(CONDITION1, CONDITION2, .)
Returns TURE if ALL arguments are true.
=OR(CONDITION1, CONDITION2, .)
Returns TURE if ANY arguments is true.
=MATCH(LOOKUP_VALUE,LOOKUP_ARRAY,MATCH_TYPE)
Returns the relative position of an item in an array that matches a specified value in a specified order.
Lookup_Value: The value you want to match in lookup_array
Lookup_Array: The range being searched
Match_Type: -1 - Smallest value greater than or equal to lookup_value (Lookup_Array
must be in descending order)

0 - First value exactly equal to lookup_value, or

1 - Largest value less than or equal to lookup_value (Lookup_Array must be in ascending order)] that specifies how the match is determined.

=INDEX(ARRAY,ROW_NUM,COLUMN_NUM)

Returns a value or the reference to a value from within a table or range.

Array: A range.

Row_Num: A row number within Array.
Col_Num: A column number within Array.
=SLN(cost,salvage,life)

Calculates the straight-line depreciation allowance of an asset with an initial cost, an expected useful life, and a final value of salvage, for one period.

=SLN($C,$S,$L) è $1,100.00 [C = $6,000 / S = $500 / L = 5]
=DDB(cost,salvage,life,period)

Calculates the depreciation allowance of an asset using the double-declining balance method.
=DDB($C,$S,$L,$P) è $320.00 [C = $3,000 / S = $600 / L = 5 / P = 4]

Attachment:- depreciation.xlsx