Скачать или смотреть Illustrated Excel 365 | Modules 5-8: SAM Capstone Project A | DGO Benefits

Illustrated Excel 365 | Modules 5-8: SAM Capstone Project A | DGO Benefits #illustratedexcel

If you directly want to get the project from us then contact us on our Whatsapp. Link is given here,

Whatsapp Contact Link:
https://api.whatsapp.com/message/4B6NMKKBK...

Whatsapp Number:
+919116641093
+918005564456

Gmail Id:
[email protected]

We are providing help in all Online Courses, Computer Science, Business and Management, Business Math, Business and Finance, Business and Accounting, Human Resource Management, History, English.


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.