excel 2013 homework

Excel 2013 Chapter 2 Working with Functions and Formulas Last Updated: 2/4/15 Page 1

USING MICROSOFT EXCEL 2013 Independent Project 2-4

Independent Project 2-4 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 information in column D (Branch) for the range argument.

c. Select D4 (Cameron Park) for the criteria argument.

d. Use the information in column J (Total Monthly Salary) for the sum_range argument.

8. Edit the formula in B26 to include absolute references in all cell references and ranges.

9. Copy and edit a formula.

a. Select the formula in B26 and copy the formula through B28.

b. Select B27 and change the reference from Cameron Park ($D$4) to Folsom ($D$8).

c. Select B28 and change the reference from Cameron Park ($D$4) to Granite Bay ($D$7).

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.

Step 1

Download start file

Excel 2013 Chapter 2 Working with Functions and Formulas Last Updated: 2/4/15 Page 2

USING MICROSOFT EXCEL 2013 Independent Project 2-4

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.

24. Save and close the workbook (Figure 2-73).

25. Upload and save your project file.

26. Submit project for grading.

2-73 Excel 2-4 completed

Step 2

Upload & Save

Step 3

Grade my Project