Overview
This assignment uses Excel and Powerpoint, and is based on data collected from U.S. Census. This exercise is an excellent way for you to know how to get data from U.S. Census Bureau and analyze using Excel. You will cut and paste the data from Excel to Powerpoint in order to create a Dashboard. A dashboard is simply a self-explanatory visualization of complex data in the form of a map, chart, or table with captions to explain the figure. You will create a Dashboard using Powerpoint. What to do: Visit the web page called ″Analyzing Census Data with Excel″ [Link: https://www.census.gov/data/academy/courses/excel.html]. There are six modules in this video tutorial (besides introduction and conclusion). Go through the videos indicated below and follow through the instructions by actually duplicating the instructions on your end. That is, you will need to visit the Census data page mentioned in the tutorial [Link: https://data.census.gov], download the specified data, and manipulate the data using Excel. Modules required for Exam purposes [Module 1, Module 2, Module 3, and Module 5 are required] -The Module 1 [Basic Data Access, Pivot Tables and Table Formatting] shows how to get data from the U.S. Census. Download the data and save on your computer for the next module. [You can skip the video between the timeline 1.30 minutes to 7.15 minutes which covers Pivot Chart. You do not need to do the Pivot Chart in newer computers as the downloaded data is already in a column format. You can just keep the data columns for 2017, 2018, and 2019; delete the columns with the data for the years 2010 to 2016.] -The Module 2 [Data Analysis] shows how to manipulate the Census data. You will learn how to create population trends using Sparklines. Sparkline is a visualization technique to show trendlines when there are a large number of entities (like states/ counties/ or even countries). Save the file you create at the end of Module 2. -The Module 3 [Introduction to Mapping] helps in creating a Map. You need only the first two minutes of this module. Save the 2d map that is created at timeline 1.56 minutes; do not delete this. You can skip the rest of the module which covers 3d Map. We do not need 3d Map for this exercise. -Skip the Module 4 [Advanced 3d Mapping]. We do not need 3d Map for this exercise. -Follow through Module 5 [Hierarchical Charts] and save the Treemap created until the timeline 6.30 minutes. You can skip the rest of this module. Save the file with the Treemap. -Skip the Module 6 [Analysis with ToolPak] Creating the Dashboard [25 points] You will need the Excel files saved in Module 2, Module 3, and Module 5 for the Dashboard. You will create the Dashboard using Powerpoint. -Slide 1: Provide a cover slide with your name etc. -Slide 2 [9 points]: Paste the data from Module 2 (along with Sparklines). Provide a caption of the chart, explaining what you observe as your interpretation of the sparklines. (Hint: Since the number of states is large, you can paste the chart in two or three groups of states so that the charts are readable) -Slide 3 [6 points]: Paste the Map from Module 3. Provide a caption for the Map, explaining what you observe as your interpretation of the Map. -Slide 4 [6 points]: Paste the Treemap from Module 5. Provide a caption for the Treemap, explaining what you observe as your interpretation of the data. -Overall visualization [4 points]: How you format and present the dashboard is very important. Make it pleasing visually to the best of your ability. [Often students do not pay enough attention to the visual, but this is very important in presentations. Powerpoint presentations should be readable. The minimum font size to be used in this powerpoint is 12 points; use at least 24 points for Title and 18 points for Captions. Captions need to be pithy, i.e. short and sweet, not more than 2-3 sentences.] Don′t use background colors (in dashboard language, the data to ink ratio should be minimal; i.e. you should convey the maximum information with minimal graphic and writing). Your main task with the visualization is to effectively display the information so that it is easily readable by any layperson. The motto is: Simple is elegant. -CAPTIONS are very important in this exercise. Please give meaningful caption that explains the visual. For example, take a look at the following map: https://www.census.gov/library/visualizations/2021/dec/2020-percent-change-map.html. What caption will you give? It CANNOT be a bland statement like ″This figure shows percent change in population for 50 states between 2010 and 2020″. This information is already captured in the title of the map. The caption has to INTERPRET the map and convey what your interpretation is. This way, you can guide the reader to see the point you want to make with the map. In other words, the caption is the ARGUMENT you are making, and the map is the EVIDENCE for your argument. These interpretations could vary between people, based on what you want to highlight. For me, for example, I notice the following: ″Population of three states (IL, MS, WV) have reduced. Northwestern and sun belt states have experienced significant population increase (˃10%). Rustbelt states have experienced modest population (˂5%) increase as well.″ [The last point on rustbelt states is interesting especially if you consider the earlier decades when the region lost population; of course, this is not in the map, so you cannot say it.]