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]