Tutorial Assignment TA2: Decision Support in Excel
Print
Overview:
The goals for this assignment are to:
Show you how Excel can be used in the preparation of data for analysis
Introduce you to filtering and consitional formatting of data
Introduce you to pivot tables
prepare you for completing the next tutorial assignment (#3) on pivot tables and decision support as assigned by your instructor.
Instructions:
Open a document file this Is where you will post your answers to the 5 questions set forth below. Save it as TA_your last name.
Download the TA2_Fall_2021_Arkansas.xlsx (TA2_Fall_2021_Arkansas) file and save it as TA2_your last name. For your convenience, this file has been attached to theassignment folder for TA2. This is a list for all Arkansas counties of the cumulative cases and deaths from COVID over a period of about a year and a half. You have also been provided with a Read-only example spreadsheet file for the required opeations for this assignment as carried out on the same data for Ohio, albeit for a different month.
Go to the Province/State column and make sure that only the rows for Arkansas are selected and the blanks rows are not.
Add a new worksheet, relabeling it Sorted
Copy the Arkansas source table from the worksheet labeled Raw data into the worksheet Sorted and then sort the rows by Date.
Add another worksheet and label it Date filter. Copy the sorted table from Sorted into Date Filter. In the Date filter worksheet perform a filter on the table to list only rows of COVID data between (& including) 9/30/20 and 10/31/20 (i.e. click on the label for Date at the top of the column and choose Date Filters – Between, and then enter the dates as upper and lower bounds.)
Add another worksheet and label it Source. You will copy the table from Date filter into it BUT you will do this a little differently. As you copy the filtered data from Date Filter into Source you will not just paste it in; Instead you will do a Paste Special to copy in only the values of the data.
Note that once you have done this the values for the Date in Source are no formatted properly. Select that column and then format these using the Format Cells command to set these up as Dates again in the mm/dd/yy format.
Now it is time to create a pivot table that will summarize the data for this state by date across counties.
Highlight all the data and then use the insert Pivot table to create a pivot table form in a new worksheet. Rename the new worksheet Pivot.
In Pivot, if you do not see a field list, right click on the Pivot table area and click on Show Field list. In the Field list you will:
Right Click on Date and Choose Add to Row labels
Right Click on Confirmed and Choose Add to Values
Right Click on Deaths and Choose Add to Values
Finally right click on the Date column in the pivot table and choose Ungroup – this will reset the date value back to the mm/dd/yy format. In the date column ot the Pivot table, please uncheck the box for “Blank” – this remove any count of null values.
You are now looking at a pivot table that is very close to to the data for Arkansas you were given for the first tutorial assignment.
Scroll down to the Sum of deaths for 10/1/20 and double-click on it. A new worksheet will appear rename this worksheet Start of Month
Q1: Describe what data you are seeing in the Start of Month worksheet.
Copy the Pivot table from the Pivot worksheet and use paste special (values) to copy it into a new worksheet that is to be labeled New source. Once again you will have reformat the date column to the mm/dd/yy format – AND you should change the title of this column to “Date”.
In the New Source worksheet Scroll down to the Sum of deaths for 10/1/20 and double-click on it.
Q2: What happens now? Is this different from before? Why?
Copy the data from New Source into yet another new worksheet rename the new one New Calc.
In the New Calc worksheet please redo the same calculations you did in the first tutorial assignment for October 1 thru October 31: Daily cases, Daily deaths and Ratio.
Highlight the Ratio column from Oct 1 to Oct 31 and use Home – Conditional Formatting to overlay the Ratios (i.e. % death rates) with a color scale with Red being the highest values and Green the lowest.
Next use Conditional formatting on the daily cases column for October to insert a rule that daily cases that above the average should be in red font. (i.e. Home – Conditional Formatting – New Rule – Rule Type: Only Values that are above or below average – [Format values that are above the average – set font color to Red.])
Q3: Do any of the days highlighted with the highest ratios (death rates) also have the highest cases? How many?
Q4: Do any of the days highlighted with the lowest ratios (death rates) also have the highest cases? How many?
Q5: Quantitatively speaking, what do you think this (i.e. your answers to Q3 & Q4) tells us about using a death rate (ratio) as an indicator of the spread of COVID?
Reorder the worksheets into the following order:
Raw Data
Sorted
Date filter
Source
Pivot
Start of Month
New Source
New Calc
Please submit your assignment by uploading your completed spreadsheet file (i.e. to the appropriate dropbox for TA2) along with your document file containing your answers to the 5 quesions listed above.
How this assignment is evaluated
Checklist of assignment completion criteria:
All worksheets are clearly labeled, as per the assignments instructions above
All worksheets were presented in the order specified above
The assigned work for each worksheet was done exactly and correctly
All your answers to the questions were effective
No extraneous worksheets were included (i.e. there are exactly 7 worksheets, as listed above).
The spreadsheet file submitted by the student was not only submitted to the correct dropbox (without having submitted it to the wrong dropbox first), but it was labeled correctly as per the instructions listed above, along wtth the document file containing your answers
Grade Criteria met
100 Not only were all criteria met, but the student submitted work that was so well well-organized that it was easy to review and grade.
90 All criteria listed above were met
80 All but one criteria above were met
70 All but two criteria were met
60 Three or more criteria were not met
50 Same as 100, but late
40 Same as 90, but late
30 Same as 80, but late
20 Same as 70, but late
10 Same as 60, but late
