Problem set 1-Working with Excel as Visualization tool

pdf

School

Utica College *

*We aren’t endorsed by this school

Course

333

Subject

Computer Science

Date

Feb 20, 2024

Type

pdf

Pages

Uploaded by MagistrateOxide28263

Data Visualzation and Storytelling _ Spring 2024 Problem set -1- Working with Excel as fist data visualization tool Faten Alabbas Overview Today, various tools are available for creating data visualizations. In this problem, we will start designing a visualization to effectively convey the health provider's productivity data story to the audience. In this assignment, you will use the Excel tool to complete exercises and gain a better understanding of its capabilities. The total points for this problem set is 100 points. Your Problem set: Step1: 10 points 1. Download the attached dataset containing information on 12,820 medical visits at various Boston Children's Hospital sites. 2. Explore the dataset to understand its contents. The reference tab contains the Provider category; search for information about each category to understand what each one means. 3. The dataset file initially contains two tabs: 'Medical Visit' and 'Reference' tabs. 4. The dataset contains missing values in the 'Medical Visit' tab, specifically in the 'Site' and 'Provider Category' columns, which are highlighted in yellow. Step2: 10 points. 1. Use the VLOOKUP function to retrieve the corresponding location names from the 'Reference' tab for the 'Site' column and do the same for the 'Provider Category' column. This will allow you to complete the table with no missing values . Step3 10 points. 1. Create a PivotTable using the 'Medical Visit' table. 2. Place 'Loc Name' in the Rows field and 'Encounter' in the Values field, ensuring that you select 'Count' from the Value Field Settings.

Data Visualzation and Storytelling _ Spring 2024 Problem set -1- Working with Excel as fist data visualization tool Faten Alabbas 3. Now, you have a table that presents each location with the total number of visits, rename this tab to 'Visits/Location ’ . 4. Highlight the data in the PivotTable and create a chart in Excel. This chart will provide insights into the performance of each location. Show the data labels and give the chart a suitable name. 5. Below the chart, provide your analysis regarding the variance of medical visits per Location. Step4: 10 points 1. Create a second PivotTable using the 'Medical Visit' tab. 2. Place Provider in the Rows field and 'Encounter' in the Values field, ensuring that you select 'Count' from the Value Field Settings. 3. Now that you have a table that presents each location with the total number of visits, rename this tab to ‘ Visits/Provider’ . 4. Highlight the data in the PivotTable and create a chart in Excel. This time the chart will provide insights into the performance of each Provider. Show the data labels and give the chart a suitable name. 5. Below the chart, provide your analysis regarding the medical visits per provider ’s name. Step5: 10 points 1. Create a third PivotTable using the 'Medical Visit' tab.

Data Visualzation and Storytelling _ Spring 2024 Problem set -1- Working with Excel as fist data visualization tool Faten Alabbas 2. Place Provider Category in the Rows field and 'Encounter' in the Values field, ensuring that you select 'Count' from the Value Field Settings. 6. Now that you have a table that presents each Provider Category with the total number of visits, rename this tab to ‘ Visits/Provider Category’ . 7. Highlight the data in the PivotTable and create a chart in Excel. This time the chart will provide insights into the performance depending on Provider Category. Show the data labels and give the chart a suitable name. 8. Below the chart, provide your analysis regarding the medical visits per provider category. Step6: 10 points 1. Create fourth PivotTable using the 'Medical Visit' tab. Place Site in the Rows field and 'Encounter' in the Values field, ensuring that you select 'Count' from the Value Field Settings. 2. Now that you have a table that presents each Site with the total number of visits, rename this tab to ‘ Visits/Site ’ . 3. Highlight the data in the PivotTable and create a chart in Excel. This time the chart will provide insights into the performance depending on Site. Show the data labels and give the chart a suitable name. 4. Below the chart, provide your analysis regarding the medical visits per Site. Step7: 10 points 1. Create a new empty tab and rename it to ' Summary ' and give the tab red color. 2. Make sure that the 'Summary' tab is the first tab in your Excel file. 3. Copy the four Charts you created and paste them to Summary tab in a proper way. Step8: 20 points 1. Suppose that you have meeting with hospital ’s CEO (Chief Executive Officer), this means that the CEO will be your audience. 2. Explain all keywords to the CEO in a proper way. 3. By your own words, give the analysis to the hospital’s CEO, your words will give them an idea about the hospital’s performance from different aspect (location, providers, sites and providers category). Step9: 10 points 1. Now your file has 7 tabs, as follow:

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

Data Visualzation and Storytelling _ Spring 2024 Problem set -1- Working with Excel as fist data visualization tool Faten Alabbas 2. Save your work file under the name of: ‘ your first name ’_ assignment1. 3. Save the Summary tab only as PDF file under the name of Hospital’s Productively Report. 4. Submit your Excell file and the Summary as PDF file as a result of your work.

Related Documents

Chapter 3.docx

HW3_ Dead Load and Load Tracing.pdf

Chapter 2 Discussion_ Game Research.docx

HW3_draft.docx

HW4_latest.docx

Security+ Exam.pptx

Understanding digital citizenship, ribble 2015.pdf

CS651_KristineCameron_IP2.doc.docx

CS651_KristineCameron_IP3.doc.docx

CS651_KristineCameron_Final.doc.docx

CS651_KristineCameron_IP4.doc.docx

12.21.23 Sec3 Hmwk Lab2 wrk with Tables.docx

Recommended textbooks for you

Information Technology Project Management

Computer Science

ISBN:9781337101356

Author:Kathy Schwalbe

Publisher:Cengage Learning

Management Of Information Security

Computer Science

ISBN:9781337405713

Author:WHITMAN, Michael.

Publisher:Cengage Learning,

Principles of Information Systems (MindTap Course...

Computer Science

ISBN:9781305971776

Author:Ralph Stair, George Reynolds

Publisher:Cengage Learning

Fundamentals of Information Systems

Computer Science

ISBN:9781337097536

Author:Ralph Stair, George Reynolds

Publisher:Cengage Learning

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:9781337508841

Author:Carey

Publisher:Cengage

Database Systems: Design, Implementation, & Manag...

Computer Science

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Cengage Learning

SEE MORE TEXTBOOKS

Recommended textbooks for you

Information Technology Project Management
Computer Science
ISBN:9781337101356
Author:Kathy Schwalbe
Publisher:Cengage Learning
Management Of Information Security
Computer Science
ISBN:9781337405713
Author:WHITMAN, Michael.
Publisher:Cengage Learning,
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning