Reference no: EM13969756
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
|
What can we learn about the human condition in general
: "The Kidney Shaped Stone that Moves Everyday"] Delia Jones in "Sweat," the narrator from "The Yellow Wallpaper,". What can we learn about the human condition in general?
|
|
Determining the revenue collected by the government
: What is the relationship between tax base and tax rate in determining the revenue collected by the government? Give an example of how a particular tax would fit into the various components of the equation.
|
|
Analyze a text, paying special attention to the features
: Your task is to analyze a text, paying special attention to the features that influence a piece of writing the most: the audience, the writer's orientation, the rhetorical situation, and the genre (COLFV features). Use the sample rhetorical analys..
|
|
Determining the compounding of interest
: What will $164,000 grow to be in 6 years if it is invested today in an account with a quoted annual interest rate of 13% with weekly compounding of interest? (Assume 52 weeks per year.)
|
|
Create a vlookup function to calculate the bonus amount
: Create a VLOOKUP function to calculate the bonus amount for each employee. Click the Employees sheet tab, and select I4. Type a formula to reference the Base Monthly Salary amount as the lookup_value
|
|
How can you conncet the given film to social change
: What is the documentary about (the who, what, where, when, why)? What did you learn? How can you conncet this film to social change?
|
|
What is the apr on loan
: You want to borrow $10,000 from your brother. He says if he charged you 7.2% that would be interest of $720 for the year. So in total you would pay him $10,720. So he tells you that you can pay $10,720/12 = $893.33 per month for 12 months that wou..
|
|
Find all equilibrium points
: Find all equilibrium points - if both populations are greater than their values at the equilibrium point, what happens to the populations?
|
|
Identify one of the underwriters in the ipo
: In 1970, they raised $4.5 million through an IPO. Arkansas-based Stephens Inc. and Boston-based White Weild handled the IPO. As the company grew, the Waltons set up Walton Enterprises, which owns shares of the company. 1. Identify one of the under..
|