Week 1 Independent Project Excel 2019 In Practice Ch 2 Independent Project 2,4

Student Name: Week 1: Independent Project: Excel 2019 In Practice - Ch 2 Independent Project 2-4 COURSE NAME BIS155 MAY21 Master | Data Analysis with Spreadsheets with Lab - 10037 Start Date: Due Date: End Date: Independent Project 2-4 Central Sierra Insurance (CSI) sets bonus percentages based on commissions earned by each agent and calculates totals by branch office. This workbook also tracks fundraising efforts of employees for a community event. [Student Learning Outcomes 2.1, 2.2, 2.3, 2.5, 2.6, 2.7] File Needed: CentralS (Available from the Start File link.) Completed Project File Name: [your name]-CentralS Skills Covered in This Project Create, copy, and edit formulas. Name cell ranges. Set mathematical order of operations. Set cell references to be absolute. Use the NOW function. Use HLOOKUP and VLOOKUP Use the SUMIF function. Build an IF function. 1. Open the start file CentralS workbook. 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. Select the Tables sheet, select cells A6:B10, and create range names using the Create from Selection button [Formulas tab, Defined Names group]. 3. Select cells B1:F2 and click the Name box. Name the selection H_Rates. Note that the first row is arranged in ascending order. 4. Create an HLOOKUP function to display the bonus rate. a. Click the Commissions sheet tab and select cell F5. b. Start the HLOOKUP function and use cell E5 as the lookup_value. c. For the table_array argument, use the H_Rates range. d. Use the second row for the row_index_num argument (Figure 2-100). 5. Format the results as Percent Style with two decimal places. 6. Copy the formula in cell F5 to cells F6:F14. 7. Set order of operations to calculate total earnings. a. Select cell G5. b. Build a formula to add the commissions amount (E5) to the commissions amount times the rate (F5*E5). c. Copy the formula in cell G5 to cells G6:G14. 8. Create and copy a SUMIF function to calculate total earnings by branch office. a. Select cell E18. b. Start the SUMIF function with cells $D$5:$D$14 as the Range argument. c. Set the Criteria argument as a relative reference to cell C18. ......................................................CONTINUED.................................................................