**Independent Project 2-4**

Windows Mac

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: **CentralSierra-02.xlsx***(Available from the* Start File *link.)*

Completed Project File Name: **[your name]-CentralSierra-02.xlsx**

**Skills Covered in This Project**

**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.

- Open the start file
**CentralSierra-02.xlsx**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. - Select the
**Tables**sheet, select cells**A6:B10**, and create range names using the**Create from Selection**button [*Formulas*tab,*Defined Names*group]. - Select cells
**B1:F2**and click the**Name**box. Name the selection H_Rates. Note that the first row is arranged in ascending order. - Create an
*HLOOKUP*function to display the bonus rate.- Click the
**Commissions**sheet tab and select cell**F5**. - Start the
*HLOOKUP*function and use cell**E5**as the*lookup_value*. - For the
*table_array*argument, use the**H_Rates**range. - Use the second row for the
*row_index_num*argument (Figure 2-100).**Figure 2-100****HLOOKUP****function to display bonus**

- Click the
- Format the results as
**Percent Style**with two decimal places. - Copy the formula in cell
**F5**to cells**F6:F14**. - Set order of operations to calculate total earnings.
- Select cell
**G5**. - Build a formula to add the commissions amount (E5) to the commissions amount times the rate (F5*E5).
- Copy the formula in cell
**G5**to cells**G6:G14**.

- Select cell
- Create and copy a
*SUMIF*function to calculate total earnings by branch office.- Select cell
**E18**. - Start the
*SUMIF*function with cells**$D$5:$D$14**as the*Range*argument. - Set the
*Criteria*argument as a relative reference to cell**C18**. - Select cells
**G5:G14**for the*Sum_range*argument and make the references absolute. - Copy the formula in cell
**E18**to cells**E19:E20**without formatting to preserve borders. - Format cells
**E18:E21**as**Currency**.

- Select cell
- Total the earnings in cell
**E21**. - Create and format the current date.
- Select cell
**G23**and insert the*NOW*function. - Select cell
**G23**and click the**Number**group launcher [*Home*tab]. On the*Number*tab, select the**Date**category. - Scroll the
*Type*list to find the date that displays the month spelled out, the date, a comma, and a four-digit year (Figure 2-101).**Figure 2-101 Date format selected** - Click
**OK**. Press**Ctrl+Home**.

- Select cell
- Create and copy a
*VLOOKUP*function to display goals for each funding source.- Click the
**Family Day**sheet tab and select cell**F6**. - Start the
*VLOOKUP*function and use cell**E6**as the*lookup_value*. - Click the
**Tables**sheet tab for the*table_array*argument and use cells**$A$6:$B$10**. The data is sorted by the first column in ascending order. - Use the second column as the
*col_index_num*The*range_lookup*argument is empty. - Copy the formula in cell
**F6**to cells**F7:F20**without formatting to preserve the fill color. - Format cells
**F6:F20**as**Currency**with no decimal places.

- Click the
- Create and copy an
*IF*function.- Select cell
**H6**and start an*IF* - Type a
*logical_argument*to determine if cell**G6**is greater than or equal to (**>=**) cell**F6**. - Type Yes as the
*Value_if_true*argument and No as the*Value_if_false*argument. - Copy the formula in cell
**H6**to cells**H7:H20**without formatting to maintain the fill color. - Center align cells
**H6:H20**. - Press
**Ctrl+Home**.

- Select cell
- Insert a new sheet at the end of the tab names and paste the range names starting in cell A1.
*AutoFit*columns**A:B**and name the worksheet as Range Names. - Save and close the workbook (Figure 2-102).
**Figure 2-102 Excel 2-4 completed** - Upload and save your project file.
- Submit project for grading.