Excel Guided Project 6-3

by
Uncategorized

 

The Wear-Ever Shoes company maintains inventory data and customer survey results in your workbook. You use Lookup & Reference, Database, and Logical functions to complete the data. You also use a Financial function to calculate depreciation and a Text function to enter email addresses.

[Student Learning Outcomes 6.1, 6.2, 6.3, 6.5, 6.6, 6.7]

File Needed: WearEverShoes-06.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-WearEverShoes-06.xlsx

Skills Covered in This Project

  • Nest INDEX and MATCH functions.
  • Use SUMIFS from the Math & Trig category.
  • Use DAVERAGE.
  • Create an IFS formula.
  • Use a Text function to concatenate text strings.
  • Calculate depreciation with the DB function.
  1. Open the WearEverShoes-06 start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
  2. Click the Inventory sheet tab.
  3. Select cells A3:I39, click the Name box, type Inventory as the range name, and press Enter.
  4. Select cell L5 and type WE006.
  5. Create a nested function with INDEX and MATCH to display inventory for a product.
    1. Select cell L6.
    2. Click the Lookup & Reference button [Formulas tab, Function Library group] and choose INDEX. Select the first argument list array, row_num, column_num and click OK.
    3. For the Array argument, press F3 (FN+F3) and select Inventory.
    4. Click the Row_num box and click the Name box arrow. Choose MATCH in the list or choose More Functions to find and select MATCH. The INDEX function uses this MATCH statement to find the row.
    5. Click cell L5 for the Lookup_value argument.
    6. Click the Lookup_array box and select cells A3:A39. This MATCH function finds the row that matches cell L5 in column A.
    7. Click the Match_type argument and type 0.
    8. Click INDEX in the Formula bar. (Click OK if the argument list opens.)
    9. Click the Column_num argument, click the Name box arrow, and choose MATCH (Figure 6-92).Both the row_num and col_num arguments are MATCH functions.Figure 6-92 MATCH is nested twice
    10. Type quantity in the Lookup_value box.
    11. Click the Lookup_array box and select cells A3:I3. This MATCH function finds the cell in the “Quantity” column after the row is located by the first MATCH function.
    12. Click the Match_type box and type 0. The formula is =INDEX(Inventory,MATCH(L5,A3:A39,0),MATCH(“quantity”,A3:I3,0)).
    13. Click OK. The result is 2.
    14. Click cell L5, type WE015, and press Enter. The quantity is updated.
  6. Use SUMIFS to calculate total pairs in stock by specific criteria.
    1. Select cell M13.
    2. Click the Math & Trig button [Formulas tab, Function Library group] and choose SUMIFS.
    3. Select cells E4:E39 for the Sum_range argument and press F4 (FN+F4) to make the references absolute.
    4. Click the Criteria_range1 box, select cells C4:C39, the “Color” field, and press F4 (FN+F4).
    5. Click the Criteria1 box and select cell K13. Leave this as a relative reference.
    6. Click the Criteria_range2 box, select cells D4:D39, and make the references absolute.
    7. Click the Criteria2 box and select cell L13. The criteria specifies the number of black pairs, size 8 (Figure 6-93).The sum and criteria ranges must have the same dimension.Figure 6-93 SUMIFS to calculate number by color and size
    8. Click OK. The result is 7.
    9. Copy the formula in cell M13 to cells M14:M21.
  7. Click the Satisfaction Survey worksheet tab and review the data.
  8. Select cells A4:H40 and name the range as Survey. Note that the “Comfort” field is the fifth column and that the other attributes follow in the sixth, seventh, and eighth columns.
  9. Use DAVERAGE to summarize customer survey data.
    1. Click the Criteria sheet tab.
    2. Select cell B2 and type rug*, criteria for the Rugged Hiking Boots.
    3. Click the Average Ratings worksheet tab and select cell C5.
    4. Click the Insert Function button [Formulas tab, Function Library group].
    5. Choose Database in the Or select a category list.
    6. Select DAVERAGE and click OK to calculate an average comfort rating for the boots.
    7. Press F3 (FN+F3), choose Survey for the Database argument, and click OK.
    8. Click the Field box and select cell C4.
    9. Click the Criteria box, select the Criteria sheet tab, select cells B1:B2, and make the references absolute (Figure 6-94).DAVERAGE ignores values that do not match the criteria.Figure 6-94 DAVERAGE for comfort rating
    10. Click OK. The result is 7.75.
    11. Copy the formula in cell C5 to cells D5:F5.
  10. Use DAVERAGE to summarize survey data.
    1. Select the Criteria sheet tab and select cell B5. Type the criteria as shown here for the shoe styles.The table lists the criteria to be entered on the Criteria sheet.CellCriteriaB5com*B8laz*B11ser*B14gli*
    2. Click the Average Ratings sheet tab and select cell C6.
    3. Click the Recently Used button [Formulas tab, Function Library group] and select DAVERAGE.
    4. Press F3 (FN+F3) and choose Survey for the Database argument.
    5. Click the Field argument box and select cell C4.
    6. Click the Criteria box, select cells B4:B5 on the Criteria sheet, and press F4 (FN+F4).
    7. Click OK. The result is 7.5.
    8. Copy the formula in cell C6 to cells D6:F6.
  11. Build DAVERAGE functions for the remaining shoe styles on the Average Ratings sheet.
  12. Select cells G5:G9 on the Average Ratings sheet, click the AutoSum arrow [Home tab, Editing group], and choose Average.
  13. Create an IFS function.Note: If your version of Excel does not include the IFS function, build the following nested IF function =IF(G5>=9,$J$5,IF(G5>=8,$J$6,IF(G5>=5,$J$7,$J$8))) to show the ratings.
    1. Select cell H5, click the Logical button [Formulas tab, Function Library group], and choose IFS.
    2. Click the Logical_test1 argument, select cell G5, and type >=9.
    3. Click the Value_if_true1 box, click cell J5, and press F4 (FN+F4) to make the reference absolute.
    4. Click the Logical_test2 box, click cell G5, and type >=8.
    5. Click the Value_if_true2 box, click cell J6, and press F4 (FN+F4).
    6. Click the Logical_test3 box, click cell G5, and type >=5.
    7. Click the down scroll arrow to reveal the Value_if_true3 box, click cell J7, and press F4 (FN+F4).
    8. Click the down scroll arrow to reveal the Logical_test4 box, click cell G5, and type <5.
    9. Click the down scroll arrow to reveal the Value_if_true4 box, click cell J8, and press F4 (FN+F4) (Figure 6-95). The complete formula is:=IFS(G5>=9,$J$5,G5>=8,$J$6,G5>=5,$J$7,G5<5,$J$8)The Logical_test1 argument is scrolled out of viewFigure 6-95 IFS function with multiple logical tests
    10. Click OK and copy the formula to cells H6:H9.
    11. Format column H to be 13.57 (100 pixels) wide.
  14. Calculate depreciation for an asset using a Financial function.
    1. Click the Depreciation sheet tab and select cell C11. Depreciation is the decrease in the value of an asset as it ages. The DB function calculates the loss in value over a specified period of time at a fixed rate.
    2. Click the Financial button [Formulas tab, Function Library group] and choose DB.
    3. Select cell C6 for the Cost argument, and press F4 (FN+F4) to make the reference absolute. This is the initial cost of the equipment.
    4. Click the Salvage box, select cell C7, and press F4 (FN+F4). This is the expected value of the equipment at the end of its life.
    5. Click the Life box, select cell C8, and press F4 (FN+F4). This is how long the equipment is expected to last.
    6. Click the Period box and select cell B11. The first formula calculates depreciation for the first year (Figure 6-96).DB stands for declining balance depreciation.Figure 6-96 DB function to calculate asset depreciation
    7. Click OK. The first year depreciation is $39,900.00.
    8. Copy the formula in cell C11 to cells C12:C18. Each year’s depreciation is less than the previous year’s.
    9. Select cell C19 and use AutoSum. The total depreciation plus the salvage value is approximately equal to the original cost. It is not exact due to rounding.
  15. Use CONCAT to build an email address. (If your version of Excel does not include CONCAT, use CONCATENATE.)
    1. Right-click any worksheet tab, choose Unhide, select E-Mail, and click OK.
    2. Select cell C5, type =con, and press Tab. The text1 argument is first.
    3. Select cell A5 and type a comma (,) to move to the text2 argument.
    4. Select cell B5 and type a comma (,) to move to the text3 argument.
    5. Type “@weshoes.org” including the quotation marks (Figure 6-97).CONCAT was CONCATENATE in earlier versions of Excel.Figure 6-97 CONCAT references and typed data
    6. Type the closing parenthesis ()) and press Enter.
    7. Copy the formula in cell C5 to cells C6:C8.
  16. Save and close the workbook (Figure 6-98).Completed worksheets for Excel 6-3
Tags: No tags