Shelly Cashman Excel 2019 | Module 10: End of Module Project 1 Wyoming Commerce Department ANALYZING DATA WITH POWER TOOLS   GETTING STARTED Open the file SC_EX19_EOM10-1_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

Shelly Cashman Excel 2019 | Module 10: End of Module Project 1

Wyoming Commerce Department

ANALYZING DATA WITH POWER TOOLS

 

• GETTING STARTED

• Open the file SC_EX19_EOM10-1_FirstLastName_1.xlsm, available for download from the SAM website.
• Save the file as SC_EX19_EOM10-1_FirstLastName_2.xlsm by changing the “1” to a “2”.
  • If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_EX19_EOM10-1_Education.xlsx
  • Support_EX19_EOM10-1_Population.xlsx
• With the file SC_EX19_EOM10-1_FirstLastName_2.xlsm 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.
• To complete this project, you need to display the Power Pivot and Developer tabs. If these tabs do not display, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the following check boxes:
  • Developer
  • Layout
  • Power Pivot
    
    Click the OK button to close the Excel Options dialog box and add the Developer tab, the Power Pivot tab, and the buttons for the Power tools to the ribbon.

• PROJECT STEPS

1. As a business liaison for the Wyoming Commerce Department, Pedro Suarez works to attract businesses to the state of Wyoming. He is developing an Excel workbook that compares population, income, and education data in Wyoming counties to identify places he can promote to businesses wanting to expand their operations. He asks for your help in analyzing the data.
   Go to the Education and Age worksheet, where Pedro wants to include educational information about Wyoming counties. He has another workbook that already contains this data. Use Power Query to connect to and import data from the Support_EX19_EOM10-1_Education.xlsx workbook. Transform the data in the Education table to remove the top two rows and the column containing state data. Use the first row as headers. Close and load the transformed data to a table in cell A2 of the existing worksheet.
2. Go to the Population and Income worksheet. Pedro also wants to analyze data about the 10 most populated counties in Wyoming and their median income. This data is stored in another workbook.
   Use Power Query to connect to and import data from the Support_EX19_EOM10-1_Population.xlsx workbook. Transform the data in the Population table to sort it in descending order by population. Keep only the top 10 rows, and then close and load the transformed data to a table in cell A2 of the existing worksheet.
3. Pedro wants to create a PivotTable that compares the population and education data for the top 10 most populated Wyoming counties.
   Add the Population query to the Data Model, and then add the Education query to the Data Model. Use Power Pivot to create a PivotTable on a new worksheet, using Population Pivot as the name of the worksheet. Display the County field values from the Population table as row headings. Sum the Population field values, and then sum the Area (sq mi) field values. Display the % Completed High School field values from the Education table as a filter.
4. Pedro also wants to compare the population and age data for the top 10 most populated Wyoming counties.
   Use Power Pivot to create a PivotTable on a new worksheet, using Age Pivot as the name of the worksheet. Display the County field values from the Population table as row headings. Sum the Population field values. Display the Median Income field values as a filter. Display the Median Age field values from the Education table as column headings.
5. To properly combine data from the tables, create a relationship from the Population table to the Education table using the Geo ID column to relate the tables.
6. Pedro asks you to make the PivotTable on the Age Pivot worksheet easier to interpret and to display data only for residents with a high median income.
   Format the Population values using the Number format, 0 decimal places, and a 1000 separator. Display the text Median Age in cell C3. Display the text County in cell B4. Filter the Median Income values to show population data for those with a median income of $60,000 or more.
7. Return to the Population Pivot worksheet. Pedro wants to format the data in this PivotTable and calculate the number of people per square mile.
   Format the Population and Area data using the Number format, 0 decimal places, and a 1000 separator. Create a measure named Density and use Population per square mile as the description. Insert a formula that divides the Sum of Population field by the Sum of Area (sq mi) field for the measure. Format the measure using the Number format and 1 decimal place.
8. Pedro wants to focus on the counties with the most high school graduates.
   Filter the PivotTable on the Population Pivot worksheet to display data for counties where 93, 95, or 96 percent of the population completed high school.
9. Return to the Population and Income worksheet. Pedro wants to include his contact information in case businesses need additional information from him. He also wants to include the same contact information on the Overview worksheet. He asks you to record a macro instead of entering the same information twice.
   Enable all macros in the workbook, display the Developer tab on the ribbon, and then click cell A14. Record a macro stored in this workbook using ContactInfo as the name of the macro. With the macro recording, type Contact: Pedro Suarez in cell A14 and then press ALT+ENTER. With the macro still recording, type Wyoming Commerce Department and then press ENTER. Stop recording the macro.
   Go to the Overview worksheet, click cell J10, and then run the ContactInfo macro to insert Pedro's contact information in the cell.
10. Pedro asks you to complete the list of links on the Overview worksheet.
    In cell J6, insert a link to cell A1 of the Age Pivot worksheet. In cell J7, insert a link to cell A1 of the Population Pivot worksheet. In cell J8, insert a link to the Support_EX19_EOM10-1_Population.xlsx file. Add Census Source Data as the ScreenTip for the link in cell J8. In cell J10, insert a link to the info@wycom.example.gov email address.
11. Pedro wants to indicate that the link in cell J8 opens another file. In cell J8, after the "Raw population data" text, insert an external link symbol, an arrow in a circle (Wingdings 3, symbol 82).

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.