Shelly Cashman Excel 365 | Module 10: End of Module Project 1 Indiana Sports Marketing

GETTING STARTED

1. Save the file SC_EX365_EOM10-1_FirstLastName_1.xlsm as SC_EX365_EOM10-1_FirstLastName_2.xlsm

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsm file extension, do not type it. The file extension will be added for you automatically.

2. Files downloaded from the website are safe and do not contain viruses, but due to a recent Microsoft policy update, macros in downloaded files are disabled by default. To complete this project, you will need to enable macros in the file. To enable macros on this file: Open Windows File Explorer and go to the folder where you saved the file. Right-click the file and choose Properties from the context menu. At the bottom of the General tab, select the Unblock checkbox and select Apply, and then click OK.

3. To complete this Project, you will also need the following files:

Support_EX365_EOM10-1_Demographics.xlsx

Support_EX365_EOM10-1_Income.xlsx

4. With the file SC_EX365_EOM10-1_FirstLastName_2.xlsm 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.

5. 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: a. Developer b. 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 marketing analyst for Indiana Sports Marketing, Curtis Jenkins advises Indiana athletic teams and venues on promoting their sporting events. He is developing an Excel workbook that compares demographic and income data in Indiana counties to identify places to increase promotional efforts. He asks for your help in collecting and analyzing the data. Go to the Demographics worksheet, where Curtis wants you to include education, age, and employment data for residents of Indiana counties. He has another workbook that already contains this data. Use Power Query to connect to and import data from the Support_EX365_EOM10-1_Demographics.xlsx workbook. Transform the data in the Education table to remove the State column. Close and load the transformed data to a table in cell B2 of the existing worksheet.

2. Go to the Income worksheet. Curtis wants you to analyze income data about Indiana counties. This data is stored in another workbook. Use Power Query to connect to and import data from the Support_EX365_EOM10-1_Income.xlsx workbook. Transform the data in the Income table to remove the top two rows, which are unnecessary. Use the first row as headers. Close and load the transformed data to a table in cell B2 of the existing worksheet.

3. Curtis asks you to compare the population and housing data for Indiana counties. Add the Income 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 Housing Pivot as the name of the worksheet. Display the County field values from the Income table as row headings. Sum the Population and % Occupied Housing field values. Display the Median Income field values as a filter. Sort the PivotTable in descending order by the Population values.

4. Curtis also asks you to make the PivotTable on the Housing Pivot worksheet easier to interpret and 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. Format the Housing values using the Percentage format with 1 decimal place. Display the text County in cell B3, Population in cell C3, and % Occupied Housing in cell D3. Filter the Median Income values to show population data for those with a median income of $80,000 or more.

5. Curtis wants you to create a PivotTable that compares income and education data for Indiana counties. Use Power Pivot to create a PivotTable on a new worksheet, using Income Pivot as the name of the worksheet. Display the County field values from the Income table as row headings. Add the Population and Median Income fields from the Income table and the Employment field from the Education table to the Values area. Sum the Population and Employment field values, and then average the Median Income field values. Display the % Completed High School field values from the Education table as a filter.

6. To properly combine data from the tables, create a relationship from the Education table to the Income table using the County ID column to relate the tables. Close the relationship message, if necessary.

7. Curtis wants you to format the data in the new PivotTable and calculate the percentage of residents with full-time employment. Format the Population and Employment data using the Number format, 0 decimal places, and a 1000 separator. Format the Median Income data using the Currency format, 0 decimal places, and the $ symbol.

8. Create a measure named % Employed and use Percentage of employed as the description. Insert a formula that divides the Sum of Employment field by the Sum of Population field for the measure. Format the measure using the Percentage format and 1 decimal place.

9. Go to the Demographics worksheet. Curtis wants you to include his contact information in case clients 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 H1. Record a macro stored in this workbook using ContactInfo as the name of the macro. With the macro recording, type Contact: Curtis Jenkins in cell H1 and then press ALT+ENTER. With the macro still recording, type Indiana Sports Marketing and then press ENTER. Stop recording the macro. Go to cell I12 in the Overview worksheet, and then run the ContactInfo macro to insert Curtis's contact information in the cell.

10. Curtis asks you to complete the list of links on the Overview worksheet. In cell I6, insert a link to cell A1 of the Income Pivot worksheet. In cell I7, insert a link to cell A1 of the Housing Pivot worksheet. In cell I8, insert a link to the Support_EX365_EOM10-1_Demographics.xlsx file. Add Census source data as the ScreenTip for the link in cell I8. In cell I10, insert a link to the info@in.example.gov email address using info@in.example.gov as the text to display.

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: Overview Worksheet

Final Figure 2: Income Pivot Worksheet

Final Figure 3: Housing Pivot Worksheet

Final Figure 4: Demographics Worksheet

Final Figure 5: Income Worksheet