Illustrated Excel 365 | Modules 5-8: SAM Capstone Project A

Please contact us if you need this project or any other project in any subject to be completed.
Whatsapp: +1-343-321-3276
Email: essayserviceusa@gmail.com
WhatsApp Link: https://wa.me/13433213276
#illustratedexcel365 #DGOBenefits

Illustrated Excel 365 | Modules 5-8: SAM Capstone Project A
DGO Benefits
MANAGE FORMULAS, DATA, AND TABLES
PROJECT STEPS
1. Nolan Levent recently became an insurance agent for DGO Benefits, a major insurance company. Although Nolan has the support of DGO Benefits, he is working independently to track and analyze data about his insurance products and clients. He asks for your help in managing and analyzing data about his health insurance policies.
Go to the Health Insurance worksheet, which shows the coverage options clients can add to their insurance policies. Prepare for updating this worksheet as follows:
2. Nolan wants you to include the name of the insurance company on each worksheet, including the Clients worksheet, which he has hidden to keep the data private.
3. Nolan says he is likely to sort and filter the health insurance data. Convert the range of data to a table as follows:
4. The coverage options are offered in two types. Nolan wants to make sure that anyone entering insurance data enters only "Standard" or "Add-on" in the Type column.
Create a data validation rule for the Type column as follows:
5. The table is currently sorted by Coverage ID, but Nolan prefers to have it sorted by Type so that he can list the standard coverages apart from the add-on coverages.
Sort the table in ascending order by the values in the Type column.
6. Nolan has a text file containing data that compares deductible amounts and their typical monthly payments.
Import the text file as follows:
7. Add hyperlinks to the workbook as follows to make it easier to navigate:
8. Nolan wants you to examine the typical premiums charged by age for the four states he covers: Kentucky, Tennessee, Indianna, and Ohio.
Go to the Premiums worksheet, which contains a table named Premiums, and then remove the filter from the Premiums table to display data for all four states.
9. Nolan wants you to print a list of premium information for people in Kentucky, Tennessee, Indiana, and Ohio who are younger than 45.
Use an advanced filter to provide this information for Nolan as follows:
10. Nolan wants you to create a summary showing the average minimum premium for each state. Provide this summary for Nolan as follows:
11. Nolan wants you to compare the average Employee Contribution premiums with the average marketplace premiums for a full-coverage policy.
On the Premiums Pivot worksheet, modify the PivotTable and add a PivotChart as follows to display the comparison information for Nolan:
12. Filter the PivotChart to display data for Indiana and Ohio only.
13. Go to the Clients worksheet, which includes a table named Clients. DGO Benefits provides a discount of $25.00 per month for clients who bundle their insurance by buying more than one type of policy, such as Mental Health and Prescription coverage. Nolan wants to display the monthly payment amount, including the discount as appropriate, in the Payment column.
Provide this information for Nolan as follows using an IF function and structured references:
14. Nolan has created an area in the range J2:K7 for looking up data in the Clients table. First, he wants you to find the name of the client by looking up the client ID. He has already entered the client ID in cell K2.
Look up the client name by using a MATCH formula to modify the INDEX formula as follows:
15. Nolan also needs you to find the policy type for the Client ID entered in cell K2. Look up the policy type as follows:
16. The third calculation Nolan wants you to make is to determine the total payments for the policy type of the client he is looking up, which now appears in cell J6.
Calculate the total payments for a policy type as follows:
17. Nolan also wants you to identify the number of a policies he has sold of a specified type. Calculate this information as follows:
18. Nolan wants to you create a separate table displaying the policies sold by date.
To provide this information, create and format a PivotTable as follows:
19. Format the new PivotTable as follows to make it easier to interpret:
20. Add a slicer to the PivotTable as follows to make it easy for Nolan to filter the data:
21. Return to the Clients worksheet. In the range K9:K12, Nolan wants to display the total payments received for each type of policy.
22. Nolan wants you to calculate subtotals for payments received for policies that are bundled and those that are not. Because Nolan also wants to be able to access other client data along with the subtotals, he asks you to perform this task on a copy of the Clients worksheet.
25. Format and position the SmartArt as follows:
26. Add Policy Analysis in the Categories document property so that Nolan can find the workbook easily when he is searching for files.