Assignment Question
Instructions: As always, open up the data and explore what is contained within the two worksheets (“encounter” and “organization”).
Task 1: Successfully merge “organization name”, “organization address”, “organization city”, “organization state”, “organization zip”, and “revenue” data (found in the “organizations” worksheet) with the main dataset (the “encounters” worksheet). Merge the two datasets using the “Organization ID” variable. Hint: In Excel Workshop 4, you learned how to merge data from two different worksheets within a workbook using Excel’s Power Query Tool.
Task 2: Create a new column entitled “Full Org Address”. Combine “organization address, organization city, organization state, and organization zip” into one column using a special text function. Hint: In Excel Workshop 3, you learned how to combine text across multiple columns.
Task 3: Create a new column entitled “Outpatient services”. Count the number of “encounter types” that are “outpatient”. Place the answer in row 2 underneath the “Outpatient services” column header. Hint: In Excel Workshop 2, you learned how to count data occurrences by certain criteria.
Task 4: Create a new column entitled “Outpatient costs”. Sum the “Base_enounter_cost” of all “outpatient” encounter types. Format this value to “Currency”. Hint: In Excel Workshop 2, you learned how to sum data by certain criteria using a special math function.
Task 5: Create a new column entitled “Avg outpatient cost coverage”. Average the “payer coverage” of all outpatient services. Format this value to “Currency”. Hint: In Excel Workshop 2, you learned how to average data by certain criteria using a special math function.
Task 6: Using conditional functioning, flag all “Payer_Coverage” values that are less than the “Average payer coverage of outpatient services” using red colors. Flag all “Payer_Coverage” values that are above the “Average payer coverage for outpatient services” using green colors. Hint: In Excel Workshop 4, you learned how to apply conditional formatting rules to signal trends in your data.
Task 7: Make your data (in particular, the column headers) visually appealing. Hint: In Excel Workshop 1, you learned how to format data and cells to be more visually appealing.
Task 8: Insert a pivot table of Encounter Type by Sum of Payer Coverage. Hint: In Excel Workshop 4, you learned how to insert a basic pivot table into a spreadsheet.
Task 9: Select your pivot table and create a chart that represents this data. Make sure you title your chart and make it visually appealing. Hint: In Excel Workshop 5, you learned how to insert a chart that represents your data and is visually appealing
Task 10: Add a slicer for “Description” and select “Cardiac Arrest”. In a blank cell on the worksheet, tell me the amount of money spent on “Cardiac Arrest”. Next, select “Emergency hospital admission for asthma” and tell me the amount of money spent on these encounters in another blank cell. Finally highlight which event (either “Cardiac Arrest” or “Emergency hospital admission for asthma”) has the highest amount of payer coverage. Remove the slicer box after you have found the answers so that the pivot table and chart return to their original state (Task 8 & 9 original pivot table and chart). Hint: In Excel Workshop 5, you learned how to insert a pivot table slicer. Submit the assignment as “final proj YOURLASTNAME.xlsx”.
