attachment_1

docx

School

University of Michigan *

*We aren’t endorsed by this school

Course

311

Subject

Statistics

Date

Nov 24, 2024

Type

docx

Pages

Uploaded by Dr.Coons

1. Format the data ranges located on the IXIC, COVID_STATE , and COVID_US worksheets as tables. Give the tables the same names as their worksheets. 2. On the COVID_STATE worksheet, format the REPORT_DATE column to Short Date. 3. Insert two columns to the right of STATE: REGION . Name the first column REGION_HOLD and the second column REGION. Use Text to Columns to split the data from the STATE: REGION column into two columns using the colon as the delimiter. 4. The resulting values in the REGION_HOLD column will contain a leading space. Populate the REGION column by using the TRIM function to eliminate the leading spaces from the values in the REGION_HOLD column. 5. Rename the STATE: REGION column to STATE . Create range names of the same name for for the REPORT_DATE and STATE columns. Also create a range name for the PEOPLE_DEATH_COUNT column named YTD_DEATHS. To be counted as correct, the ranges should refer only to the data contained in the table. 6. On the COVID_US worksheet, define a range names for the REPORT_DATE and PEOPLE_DEATH_NEW columns. Shorten the range name of REPORT_DATE to DATE . Shorten the range name of PEOPLE_DEATH_NEW to DEATHS. 7. On the IXIC worksheet, insert a column to the right of GAIN/LOSS . Name the column CASES. Use the VLOOKUP function to look up the DATE value in the COVID_US table and return the corresponding value in the PEOPLE_POS_NEW_CASES column. For the formula to be counted correct, the COVID_US table must be specified as the table array and the range lookup must require an exact match. 8. Insert a column to the right of the CASES column named DEATHS. Use a combination of INDEX/MATCH to

retrieve the appropriate DEATHS value from the COVID_US table. For the formula to be counted correct, the formula must refer to the range names created in a previous step and require an exact match. In this exercise, we will be building a data table that will allow us to analyze year-to-date COVID-19 deaths for each state in the US. We will build this table manually using array formulas and functions. The basic structure of the table we will be building can be found on the DeathsbyState worksheet. Your final product should look like this (partial screen shot due to table size): 1. Move the worksheet so that it is the first worksheet in the workbook. 2. On the Page Layout tab in the ribbon, click on the Themes drop-down. Select Parcel to apply the theme to the entire workbook. This will change the default font and colors for the workbook. 3. Format the data range as a table. Apply the Olive Green, Table Style Medium 5 to the table.

4. On the PICKLIST worksheet, create a range name of the same name as the worksheet for the dates found in Column A. 5. On the DeathsbyState worksheet, use Data Validation to limit the entries in cell B1 to the values found in the PICKLIST range. Format the value to Short Date so it will display as a date. 6. Assign the range name SELECT_DATE to the value in B1. 7. Populate the Cumulative Deaths column by using a combination of INDEX/MATCH and an IF array formula to find the number of year-to-date deaths that had been reported by the date selected in the picklist. Build the formula from the inside out using the following steps: 1. Use an IF array formula to create an array that contains the state names associated with the date selected from the picklist. Use the REPORT_DATE , SELECT_DATE, and STATE named ranges to build the formula. 2. Wrap the formula from the previous step in a MATCH function to find the location in the array where the value in the a given row of the STATE column is located. 3. Wrap the MATCH function in an INDEX function that uses the YTD_DEATHS named range to find the cumulative deaths reported by the selected Date. 8. Format the Cumulative Deaths column to Number, zero decimal places, using comma separators. 9. Copy and paste the formula from the Cumulative Deaths column into the Cumulative Trends column. Use conditional formatting to add solid blue data bars to the Cumulative Trends column. Apply a custom format so that that values are not displayed. 10. Insert a PivotTable based on the COVID_STATE table in a new worksheet. Name the worksheet and the PivotTable Sparklines. 11. Build a PivotTable that shows the number of deaths reported for each state and day in the dataset (using PEOPLE_DEATH_NEW). Remove grand totals from the

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

PivotTable. Your final result should look like this (partial screenshot due to table size): 12. Use the Sparklines PivotTable to populate the Daily Trends column on the DeathsbyState worksheet. Now that we’ve got the data table built, we are going to add a filled map to the worksheet to complement the table.

1. On the DeathsbyState worksheet, select the data in the State and Cumulative Deaths columns. 2. Insert a filled map that will display deaths by state. Place the map on the worksheet immediately to the right of the data table. 3. Edit the chart's properties so that it will not move or size with cells. 4. Change the color of the map to Monochromatic Palette 4. 5. Edit the chart's title to match the screenshot. We are going to build a dashboard that will allow us to analyze the impact of COVID-19 on the Nasdaq Composite’s performance. Before we can build the dashboard, we must first build the PivotTable/Charts that will feed data to the dashboard. IXICStartingPrice 1. Insert a PivotTable based on the IXIC table onto a new worksheet. Name the worksheet and the PivotTable IXICStartingPrice. 2. The PivotTable should display the earliest date in the dataset. 3. In the cell to the right of the pivot table header, type “Closing Price” 4. In the cell to the right the date, use a VLOOKUP function to lookup the PivotTable value in the IXIC table and return the corresponding value from the CLOSE column.

5. Insert a formula to the right of the PivotTable that will concatenate the various elements on the worksheet. Use the CHAR function to insert a hard return as demonstrated. Use the “mmm-d” format for the date value and the “$#,##0” number format to display the closing price as currency. 6. Make a copy of the IXICStartingPrice worksheet. Rename the worksheet and Pivot table IXICEndingPrice. 7. Edit the PivotTable so that it displays the latest date in the dataset. 8. On the IXICEndingPrice worksheet, enter the text “Percent Change” in Column B just beneath the PivotTable. In the next row, create a formula that calculates the percent change for the Nasdaq's highest and lowest closing stock prices using the values in the two PivotTables: (Ending Closing Price – Starting Closing Price)/Starting Closing Price 9. Insert a formula to the right of the Percent Change label that will concatenate the values found in the header and the body of table. Construct the formula in the same manner used for Lowest Closing Price and Highest Closing Price. Use the "0.0%" number format to display the percent change as percentage. 10. Insert a PivotTable based on the IXIC table into a new worksheet. Name the worksheet and the PivotTable IXICCases. The PivotTable should display the Nasdaq's daily gains/losses and reported COVID cases for each day in the dataset. 11. Use the DATE , GAIN/LOSS , and CASES fields to accomplish this. Once created, insert a PivotChart that will display a combo chart of the daily values for the two measures. Make the following adjustments to chart: 1. Put Cases on the Secondary Axis 2. Hide all field buttons on the chart 3. Edit the chart’s size to 2” height x 10” width. Edit the size properties so that the chart does not move or resize with cells 4. Add a chart title and edit to display as demonstrated

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

5. Delete the x-axis labels 6. Move the legend to the bottom of the chart 7. Format the Primary Axis to currency, zero decimal places 8. Format the Secondary Axis to Number, Thousands unit by using the 0,"K" format code (NOTE: It is fine if the numbers displaying on your axes aren't exactly like the screen shot. This is a scaling issue and can vary based upon your display). 9. Remove the chart’s outline 12. Make a copy of the IXICCases worksheet. Name the copy IXICDeaths. 13. Edit the PivotTable/Chart so that it displays the deaths rather than cases. 14. Change the chart type back to Combo and put Deaths on the Secondary Axis. 15. Format the Secondary Axis to Number, Thousands unit by using the 0.0,"K" format code (NOTE: It is fine if the numbers displaying on your axes aren't exactly like the screen shot. This is a scaling issue and can vary based upon your display). 16. Edit the title as demonstrated. 17. Delete the legend.

With our new dashboard objects created, we now need to construct a dashboard within Excel. Using the screenshot below as your guide, create the Nasdaq Composite Performance dashboard. 1. Insert a new worksheet named Dashboard . If needed, move the worksheet so that it is the first worksheet in the workbook (the sheet furthest to the left). 2. Move the pivot charts created in previous steps to this worksheet. Align the charts and group them as demonstrated so they can be moved as a single object. 3. Select an area of the worksheet that you approximate to be the size of the dashboard. Set the fill color for this area to Blue-Gray Accent 2 and the font size to white. 4. Create the dashboard's title to read as demonstrated. 5. Either take a screen shot of the existing legend or manually create the legend using a series of text boxes. Edit the shape’s properties so it will not move or size with cells. 6. Insert a text box and set the value of the text box equal to the cell containing the formula you built on the IXICStartingPrice worksheet. 7. Remove the box’s outline, set the font size to 14 pt. and font color to Blue-Gray Accent 2. Center the text. Set the box's size to .7" height and 3.33" width. Edit the box’s size properties so that the box’s size will not move or resize with cells. 8. Copy the text box and paste two more instances of it onto the worksheet. Edit the formulas so that they point to the appropriate values on the IXICEndingPrice worksheet. 9. Arrange the boxes as demonstrated and align them, then group them with the charts so they can be moved as a single object.

1. Select one of the PivotCharts and insert a slicer for the Month field. 2. Connect the slicer to all pivot tables associated with the dashboard 3. Create a custom slicer with the following specifications: 1. Duplicate Ice Blue, Slicer Style Dark 2 and name the new style as “Custom”. Modify the whole slicer’s style by changing the font size for the whole slicer to 9pt. 2. Apply the custom style to the slicer on the dashboard. 3. Decrease the slicer's button height to .2". 4. Edit the slicer’s caption to match the example. While in the slicer settings, also check the box to hide items with no data. 5. Edit the slicer’s properties. Disable resizing and moving and edit the properties so that it doesn’t move or resize with cells. 6. Place and size the slicer as demonstrated.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

1. Make any additional changes needed for your dashboard to match the appearance of the screen shot. 2. Once you have completed work on your dashboard, hide the formula bar, gridlines, and headings.