Homework answers / question archive / Illustrated Excel 2019 | Module 1: End of Module Project 2 Trusthaven Insurance Complete a sales worksheet GETTING STARTED Open the file IL_EX19_EOM1-2_FirstLastName_1

Illustrated Excel 2019 | Module 1: End of Module Project 2 Trusthaven Insurance Complete a sales worksheet GETTING STARTED Open the file IL_EX19_EOM1-2_FirstLastName_1

MS Excel

Share With

Illustrated Excel 2019 | Module 1: End of Module Project 2

Trusthaven Insurance

Complete a sales worksheet

GETTING STARTED

• Open the file IL_EX19_EOM1-2_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_EOM1-2_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file IL_EX19_EOM1-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. Tasha Sallah is an insurance agent for Trusthaven Insurance in Hartford, Connecticut. She is developing a worksheet to track her sales of life insurance and related commissions. She has asked you for help in completing the worksheet.
   Go to the Life Insurance Sales worksheet. Use AutoFill to fill the range C6:D6 based on the value in cell B6.
2. In cell E7, enter a formula that uses the SUM function to total the range B7:D7 to calculate the total sales for Term life insurance. Use the Fill Handle to fill the range E8:E12 with the formula in cell E7 to find the total sales for each type of life insurance.
3. Tasha receives 40 percent of sales as a commission. In cell F7, enter a formula that multiplies the value in cell E7 by the value in cell F4 to calculate the 40 percent commission for Term life insurance. Use an absolute reference to cell F4 in the formula. Fill the range F8:F12 with the formula in cell F7 to find the total commission for each type of life insurance.
4. Tasha wants to calculate statistics on her life insurance sales in Quarter 1. In cell B16, enter a formula that uses the AVERAGE function to average the values in the range B7:D12 to determine the average monthly sale for all types of policies.
5. In cell B17, enter a formula that uses the MAX function to find the maximum value in the range B7:D12 to determine the highest accumulated sales in Quarter 1.
6. In cell B18, enter a formula that uses the MIN function to find the minimum value in the range B7:D12 to determine the lowest accumulated sales in Quarter 1.
7. Tasha wants to quickly identify the policy type that generated the most sales in Quarter 1, which was the term policy type. Copy cell A7 and then paste only the value in cell C17.
8. Cut the formula in cell F14 and paste it in cell B19 to move the formula and the cell formatting.
9. Change the orientation of the worksheet to Portrait, and insert a page break above row 20 so that the information fits on a single 8.5-by-11-inch page.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Life Insurance Sales Worksheet