Practice:: Excel 2021 In Practice – Ch 6 Independent Project 6-5

Steps to complete This Project

Mark the steps as checked when you complete them.

1. Open the ClassicGardens-06 start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. 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. Create a nested INDEX and XMATCH function to display the number of transactions by city.
   1. Click the Transactions sheet tab and select and name cells A4:D29 as Stats.
   2. Click the Transactions Stats sheet tab.
   3. Click cell B16 and type Springfield.
   4. Click cell C16, start an INDEX function, and select the first argument list option.
   5. Choose or type the Stats range name for the Array argument.
   6. Click the Row_num box and nest an XMATCH function.
   7. Select cell B16 for the Lookup_value and cells A4:A29 on the Transactions sheet for the Lookup_array.
   8. Click INDEX in the Formula bar to return to its Function Arguments dialog box and click the Column_num box.
   9. Nest a second XMATCH function to look up cell C15 in the lookup_array A4:D4 on the Transactions sheet.
   10. Click INDEX in the Formula bar. The preview result in the Function Arguments dialog box is #N/A (Figure 6-113).Figure 6-113 Nested XMATCH and INDEX functions
   11. Click OK.
3. Evaluate the formula.
   1. Select cell C16 and click the Evaluate Formula button [Formulas tab, Formula Auditing group].
   2. Click Evaluate to work through each step of the formula. The error occurs at the fifth click, the second XMATCH function.
   3. Close the dialog box.
   4. Click the Transactions sheet tab and select cell C4. The label is # Transactions.
   5. Click the Transactions Stats sheet tab and select cell C15.
   6. Edit the label to delete the word “of” and verify that there is one space between the # symbol and the word “Transactions.”
   7. Select cell C16 and format it as Comma Style with no decimal places.
   8. Type Smyrna in cell B16.
4. Use DSUM to summarize transaction and visit data.
   1. Click the Criteria sheet tab.
   2. Select cell B2 and type lan* to select data for the Landscape Design department.
   3. Type law* in cell B5 for the Lawn & Maintenance department.
   4. Select cell B8 and enter criteria for the Patio & Furniture department.
   5. Select the Transactions sheet and note that transactions are in the third column and visits are in the fourth column.
   6. Click the Transactions Stats sheet tab and select cell B7.
   7. Use DSUM from the Database category with the range name Stats as the Database argument.
   8. Type 3 for the Field argument (“# Transactions” column), and enter an absolute reference to cells B1:B2 on the Criteria sheet as the Criteria argument. The result is 11555.
   9. Copy the formula in cell B7 to cell C7.
   10. Edit the Field argument in cell C7 to use the fourth column.
   11. Use DSUM in cells B8:C9 to calculate results for the two remaining departments.
5. Calculate totals and ratios.
   1. Use SUM in cells B10:C10.
   2. Select and format all values as Comma Style with no decimal places.
   3. Select cell D7 and enter a formula to divide cell C7 by cell B7.
   4. Format the results as Percent Style with two decimal places.
   5. Copy the formula to complete the column and select cell A5 (Figure 6-114).Figure 6-114 Completed Transactions Stats sheet
6. Use SUMIFS to total insurance claims and dependents by city and department.
   1. Click the Employee Insurance sheet tab and select cell E25.
   2. Use SUMIFS with an absolute reference to cells E4:E23 as the Sum_range argument.
   3. The Criteria_range1 argument is an absolute reference to cells D4:D23. The Criteria1 argument is bre* for the city of Brentwood.
   4. The Criteria_range2 argument is an absolute reference to cells C4:C23, the department column, with criteria of lan* for the Landscape Design department.
   5. Click OK. The result for cell E25 is 10.
   6. Build SUMIFS formulas for cells E26:E28 based on the criteria displayed in rows 26:28.
   7. Format borders to remove inconsistencies, if any.
7. Use REPT with LEFT and CONCAT to display names.
   1. Unhide column I and select cell B4.
   2. Start a CONCAT function. The Text1 argument is a nested LEFT function.
   3. Select cell I4 as the Text argument for the LEFT function.
   4. Click the Num_chars box and type 3 to display the first three characters of the name.
   5. Click CONCAT in the Formula bar to return to its Function Arguments dialog box.
   6. Click the Text1 argument box to expose the Text2 argument box.
   7. Nest the REPT function from the Text category.
   8. Type * as the Text argument and repeat it 20 times.
   9. Click CONCAT in the Formula bar (Figure 6-115).Figure 6-115 Nested Text functions
   10. Click OK.
   11. Copy the formula and preserve the borders.
   12. Hide column I and select cell A1.
8. Calculate depreciation for an asset using a Financial function. Depreciation is the decrease in the value of an asset as it ages.
   1. Click the Depreciation sheet tab and select cell C11.
   2. Click the Financial button [Formulas tab, Function Library group] and choose DB. The DB function calculates the loss in value over a specified period of time at a fixed rate.
   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 dozer.
   4. Click the Salvage box, select cell C7, and press F4 (FN+F4). This is the expected value of the dozer at the end of its life.
   5. Click the Life box, select cell C8, and press F4 (FN+F4). This is how long the dozer is expected to last.
   6. Click the Period box and select cell B11. The first formula calculates depreciation for the first year (Figure 6-116).Figure 6-116 DB function to calculate depreciation
   7. Click OK. The first-year depreciation is $31,800.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.
9. Save and close the workbook (Figure 6-117).Figure 6-117 Excel 6-5 completed
10. Upload and save your project file.
11. Submit file for grading.