Independent Project-
Central Sierra Insurance (CSI) is a multi-office insurance company that handles all lines of commercial and personal insurance policies. CEO Eliana Lingle is planning to pay a bonus to employees calculated on each employee's base monthly salary. For this project, you create a spreadsheet for Ms. Lingle to summarize the bonus payments. This project has been modified for use in SIMnet.
Skills Covered in This Project-
Create and copy formulas.
Apply mathematical order of operations.
Use relative, absolute, and mixed cell references.
Use AutoSum.
Apply the VLOOKUP function.
Apply the SUMIF function.
1. Open the CentralSierra-02.xlsx 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. NOTE: If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify it.
2. Create a VLOOKUP function to calculate the bonus amount for each employee.
a. Click the Employees sheet tab, and select I4.
b. Type a formula to reference the Base Monthly Salary amount as the lookup_value.
c. Click the Bonus sheet tab for the table_array argument.
d. Use the second column of the table for the col_index_num.
e. Verify that the formula syntax is =VLOOKUP(H4,Bonus!A4:B8,2).
3. Select the formula in I4 and apply absolute values to the cell references. (Hint: Not all references or ranges may require the absolute values.)
4. Copy the formula in I4 to I5:I13.
5. Type a formula in J4 to calculate the total monthly salary.
a. Use parentheses in the formula to calculate the amount of the bonus and then add the bonus to the Base Monthly Salary.
b. Copy the formula in J4 to J5:J13.
6. Select J15 and use AutoSum to calculate a total for the Total Monthly Salary column.
a. Edit the range to not include the empty cell.
b. Apply the Total cell style to J15.
7. Create a SUMIF function to calculate the total monthly salary for each office.
a. Select B26.
b. Use the cells that contain the Branch locations in column D for the range argument.
c. Select A26 (Cameron Park) for the criteria argument.
d. Use the cells that contain the Total Monthly Salary amounts in column J for the sum_range argument.
8. Edit the formula in B26 to include absolute references for the cells in columns D and J.
9. Copy the formula in B26 to B27:B28.
a. Verify that the formula in cell B27 has a reference to A27 (Folsom).
b. Verify that the formula in cell B28 has a reference to A28 (Granite Bay).
10. Select cell B29 and create a formula to add the salary amounts for each branch.
11. Format the data in column I using the Percent Style button.
12. Format the salary amounts in columns H and J and the "Branch Totals" section to the Accounting style with no symbol.
13. Select the data from A3:J30 and change the font to Calibri and the font size to 11 pt.
14. Select E26 and type Highest Salary.
15. Type Lowest Salary in E27 and Average Salary in E28.
16. Select F26 and type =ma. When you see MAX, double-click to insert the function. Drag to select J4:J13. Press Enter to complete the formula.
17. Select F27 and use the MIN function to calculate the lowest salary.
18. Select F28 and use the AVERAGE function to calculate the average salary.
19. Select E25 and type Salary Summary.
20. Merge and center "Salary Summary" in cells E25 and F25.
21. Format the "Salary Summary" section to match the "Branch Totals" section.
a. Apply a fill color and Bold the title. Apply Accounting Format without the symbol to the salary results.
b. Apply All Borders from the Borders button drop-down list. (Hint: Use the Format Painter button to copy all formats at once.)
22. Format the two total amounts (B29 and J15) to include a dollar sign. (Hint: Click the button in the Number group.)
23. Adjust column widths if necessary.
Attachment:- CentralSierra-02.xlsx.rar