Create a vlookup function to calculate the bonus amount

Assignment Help Other Subject
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

Reference no: EM13969756

Questions Cloud

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

Reviews

Write a Review

 

Other Subject Questions & Answers

  Program based on disease management

Prepare a managed care program based on disease management. Detail the following:

  How will you use social psychology information learned in

how will you use this social psychology information learned in social psycogoly class either professionally or

  Description sociodemographic and geographical characteristic

Post 3-4 pages- a description of a community that you chose to assess in this course for your Scholar-Practitioner Project. Include in your description sociodemographic and geographical characteristics (e.g., urban, rural, race, ethnicity, gender,..

  The nature of anthropology and culture

Tell me something, which happened to the Tazmanian aborigines in the first video, The Nature of Anthropology. In the second video, The Nature of Culture, culture allows us to flourish, is the blueprint for ideas and is responsible for many patterns..

  Legislative-judicial and executive branches

What were the reasons America's forefathers divided the government into the legislative, judicial, and executive branches? How does this benefit the three branches?

  Write the best three pieces of fiction you have ever

list the best 3 pieces of fiction youve ever read.nbspfictional pieces includenbspstories essays novels or poems

  What statistical analysis should she use for research design

) What statistical analysis should she use for the research design described? Defend your choice of analysis and discuss the advantages and disadvantages of the analysis you recommend

  Explain is it right to inform when no profit in crime

Explain if you think it is right to inform on friend when no profit is involved in crime. Write down reasons why it is, or is not, right to inform on Jonathon.

  This section describes the potential risk of discomfort and

consider a possible social science research project you might like to conduct. describe the project and develop a plan

  Major personality theories

Summarize the key elements of the major personality theories (e.g. type and trait theories, psychodynamic theories, humanistic theories, etc.) discussed in the text.

  Manufacturer of ink-jet printer checks samples

A manufacturer of ink-jet printer checks samples of 50 printers for functionality at the conclusion of the assembly process.

  How do you define domestic violence

Ask the following questions: How do you define domestic violence? How do you define emotional abuse? Please give some examples of emotional abuse. Do you think emotional abuse is as damaging as physical abuse? Why or why not

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd