Illustrated Excel 365 | Modules 5-8: SAM Capstone Project A DGO Benefits
MANAGE FORMULAS, DATA, AND TABLES
· Excel Projects Help
GETTING STARTED
1. Save the file IL_EX365_CS5-8A_FirstLastName_1.xlsx as IL_EX365_CS5-8A_FirstLastName_2.xlsx
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
2. To complete this Project, you will also need the following files:
Support_EX365_CS5-8A_Deductible.csv
3. With the file IL_EX365_CS5-8A_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
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:
a. Unprotect the worksheet so that you can edit the data.
b. Unfreeze the first column, since the worksheet is not wide enough to scroll horizontally.
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.
a. Unhide the Clients worksheet.
b. Group the Health Insurance, Premiums, and Clients worksheets so that you can edit and format them at the same time.
c. In cell A1 of the Health Insurance worksheet, type DGO Benefits to display the name of the company on each worksheet.
d. Fill cell A1 with Olive Green, Accent 4, Lighter 40% to coordinate with the company logo, and then ungroup the worksheets.
3. Nolan says he is likely to sort and filter the health insurance data. Convert the range of data to a table as follows:
a. On the Health Insurance worksheet, format the health insurance data in the range A2:E11 as a table with headers using Olive Green, Table Style Medium 5.
b. Apply the fill color Blue-Gray, Accent 2, Darker 50% to the range A2:E2.
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:
a. For all cells in the Type column, create an in-cell dropdown data validation rule that accepts only entries from a list.
b. Use Standard, Add-on as the Source values.
c. Provide an input message that uses Coverage Type as the title and the following sentence as the input message: Enter the type of insurance coverage.
d. Set a Stop style error alert that uses Entry Error as the title and the following sentence as the error message: Enter Standard or Add-on.
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:
a. Get data from the Text/CSV file Support_EX365_CS5-8a_Deductible.csv.
b. Edit the text file before loading it, making sure the first row appears as headers.
c. In the Power Query Editor window, choose to close and load to a location in the worksheet.
d. View the imported data as a table and insert the data in cell A13 of the existing worksheet. [Mac Hint: Close & load the data to the default location, then cut and paste the table from the new worksheet to cell A13 of the Health Insurance worksheet. Delete the new worksheet.]
e. Format the dollar amounts in the range A14:B19 using the Currency number format with 2 decimal places and the $ symbol.
f. Fill the header row of the new table with Blue-Gray, Accent 2, Darker 50%.
7. Add hyperlinks to the workbook as follows to make it easier to navigate:
a. In the Health Insurance worksheet, make the text in cell F13 a hyperlink to cell A1 of the Premiums worksheet.
b. Include Display annual premiums by age and state as the ScreenTip text.
c. Copy the hyperlink from cell F13 in the Health Insurance worksheet to cell I2 of the Clients worksheet.
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:
a. Create an advanced filter that copies the results to another location.
b. Use the Premiums table (range A2:E34) as the List range.
c. Use the data in the range G2:G3 as the Criteria range.
d. Copy the results to the range starting in cell G5. (Hint: Delete the logo if it is also copied to cell G5.)
e. Set the new range (range G5:K21) as the print area.
10. Nolan wants you to create a summary showing the average minimum premium for each state. Provide this summary for Nolan as follows:
a. Insert the Sum of Employee Contribution by State recommended PivotTable based on the data in the Premiums table. [Mac Hint: Use the Sum of Employee Contribution recommended PivotTable.]
b. Use Premiums Pivot as the name of the new worksheet.
c. Apply Ice Blue, Pivot Style Light 17 to the PivotTable.
d. Change the calculation for the Employee Contribution field to Average.
e. Change the number format of the Average of Employee Contribution field to Currency with 2 decimal places and the $ symbol.
f. Move the Premiums Pivot worksheet after the Premiums worksheet so that they appear in logical order.
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:
a. Add the Sum of Marketplace field to the Values area of the PivotTable.
b. Change the calculation for the Marketplace field to Average, and change the number format to match the Average of Employee Contribution field.
c. Insert a Stacked Column PivotChart that displays the data from the PivotTable on the same worksheet.
d. Move and resize the PivotChart so that its upper-left corner is within cell A10 and its lower-right corner is within cell C25.
e. Apply Layout 5 to the PivotChart to display the values at the bottom of each column.
f. Change the PivotChart colors to Monochromatic Palette 1.
g. Add the title Average Premiums by State to the PivotChart.
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:
a. In cell H3, create a formula using the IF function that tests whether the value in the Bundled column ([@[Bundled?]]) is "Yes".
b. If the value in the Bundled column is Yes, subtract 25 from the amount in the Per Month column ([@[Per Month]]).
c. Otherwise, the payment is the same as the value in the Per Month column.
d. If necessary, fill the formula to the range H4:H46.
e. Add a Total Row to the Clients table, which automatically totals the amounts in the Payment column.
f. In cell B47, use the total row to display the count of the clients.
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:
a. In place of the 165 after the structured reference to the Clients table, insert the MATCH function.
b. Match the value in cell K2, and look it up using a structured reference to the Client ID column of the Clients table.
c. Return an exact match.
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:
a. In cell J5, below the "Policy Type" text, begin to enter a formula using the VLOOKUP function to determine the policy type.
b. The formula should look up the Client ID and return the value in the Policy Type column of the Clients table, using a structured reference to the table.
c. Look up an exact match to the Client ID in the Clients table.
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:
a. In cell K6, begin to enter a formula using the DSUM function.
b. Use structured references to the [#Headers] and [#Data] in the Clients table to specify the formula database.
c. Use a structured reference to the Payment field header to specify the field to summarize.
d. Use the values in the range J4:J5 as the criteria.
17. Nolan also wants you to identify the number of a policies he has sold of a specified type. Calculate this information as follows:
a. In cell K7, begin to enter a formula using the DCOUNTA function.
b. Based on the headers and data in the Clients table, and using structured references, count the number of values in the Policy Type column that match the criteria in the range J4:J5.
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:
a. Insert a PivotTable on a new worksheet based on the Clients table.
b. Use Monthly Payments as the name of the worksheet.
c. Use Payments as the name of the PivotTable.
d. Display the start dates in rows.
e. Display the policy types in columns.
f. Display the sum of the payment amounts as values.
19. Format the new PivotTable as follows to make it easier to interpret:
a. Format the payment amounts as Currency using 2 decimal places and the $ symbol.
b. Group the dates by month.
c. Hide the Field Headers to remove some clutter from the PivotTable.
20. Add a slicer to the PivotTable as follows to make it easy for Nolan to filter the data:
a. Add a slicer to the PivotTable based on the Start Date field.
b. Position the slicer so that its upper-left corner is in cell G3 and its lower-right corner is in cell I17.
21. Return to the Clients worksheet. In the range K9:K12, Nolan wants to display the total payments received for each type of policy. Display this information for Nolan as follows:
a. In cell K9, enter a formula that uses the GETPIVOTDATA function.
b. Using "Payment" as the data field, extract data from the PivotTable starting in cell A3 on the Monthly Payments worksheet. Use an absolute reference to the cell.
c. Select the grand total quantity amount for insurance policies by using "Policy Type" as the field1 argument and the name of the policy type (cell J9 of the Clients worksheet) as the item1 argument.
d. Fill the range K10:K12 with the formula in cell K9, filling the range without formatting.
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.
a. Create a copy of the Clients worksheet and place it at the end of the workbook.
b. Use Subtotals as the name of the copied worksheet.
c. On the Subtotals worksheet, remove the Total Row from the table.
d. Clear the contents and formatting from the range J2:K12.
e. Sort the table in ascending order by the Bundled? column.
f. Convert the table to a range.
23. Include subtotals on the Subtotals worksheet as follows:
a. Add subtotals to the range A2:H46 so that for each change in the Bundled? value, the formula uses the Sum function to add subtotals to the Payment field.
b. Collapse the outline to display only the subtotals and the grand total.
24. Nolan wants you to illustrate bundled insurance policies on the Subtotals worksheet. Add SmartArt to the worksheet as follows:
a. Insert a Basic Radial SmartArt from the Relationships section of the SmartArt gallery.
b. Type the following text in the SmartArt shapes: Middle shape: Bundles Left shape: HMO Right shape: PPO Bottom shape: HDHP Top shape: EPO
25. Format and position the SmartArt as follows:
a. Change the colors of the SmartArt to Colorful Range – Accent Colors 2 to 3.
b. Move the SmartArt so that its upper-left corner is in cell B54 and its lower-right corner is in cell E66.
26. Add Policy Analysis in the Categories document property so that Nolan can find the workbook easily when he is searching for files.
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 website to submit your completed project.
Final Figure 1: Health Insurance Worksheet
Final Figure 2: Premiums Worksheet
Final Figure 3: Premiums Pivot Worksheet
Final Figure 4: Monthly Payments Worksheet
Final Figure 5: Clients Worksheet
Final Figure 6: Subtotals Worksheet