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