The ultimate goal of a research analyst is to effectively communicate the best information for decision-makers. Data analytics and data visualization are widely used in business and policy to provide decision-makers and stakeholders with relevant, easily-understood input for their decisions.
In this course, we have looked at several tools in Microsoft Excel to help us find insights from data sets and visualize these insights in a clear and effective manner.
For this assignment, you will choose a dataset from the list below, come up with some research questions or hypotheses about it, and conduct an analysis in Excel to address them.
-
Note: Pay attention to the codebooks, including missing values that should not be included in analyses.
-
Research Questions / Topics / Hypotheses
Come up with 2 research questions about the dataset. You should be looking for relationships/comparisons between variables and/or descriptive statistics for variables of interest.
You can pose them as:
- Questions, e.g., “Does enjoyment of country music differ by gender?”
- Topics, e.g., “Country Music Enjoyment by Gender”
- Hypotheses, e.g., “Men enjoy country music more than women”
These instructions will refer to them as questions, but they can be in the above forms.
The 2 questions should involve different variables, and be distinct enough so that they require different analysis methods in Excel, as well as different chart types. Questions can have multiple parts (e.g. 1a, 1b, 1c…) in order to incorporate more Excel tools.
Analysis of the Dataset
Use the tools you’ve learned in Excel to address your questions. Each question should have at least one dedicated worksheet.
You should demonstrate as many different Excel tools as you can, if applicable to the data & questions, such as:
- Formatting, Sorting & filtering
- Arithmetic formulas and functions (e.g. SUM, AVERAGE)
- Logical functions such as IF/COUNTIF/AVERAGEIF/SUMIF
- Other functions such as VLOOKUP, text functions, data validation, etc.
- Pivot tables
It is understood that the dataset and/or your questions may not support using all of these functions, so don’t shoehorn them if they aren’t useful. If you feel the need to do so, better to revise your question so that the function can be used to answer it or part of it.
You will be marked on the sophistication of the analysis, variety of the methods used, and the appropriateness of the tools being used relative to the data.
Note: Make sure all of your formulas are present in the worksheets, do not copy and paste values resulting from formulas at any point.
Data Visualization
You must also create at least one high quality chart or pivot chart for each research question to communicate the results of your analysis to place in your report. You can include more charts, and not all have to go into the Report.
Charts are expected to reflect the principles of good data visualization covered in week 6, and be formatted to look complete and professional.
Report
The first sheet of your workbook should be dedicated to a summary of the results of your analyses.
You should include text boxes that describe the headline result (i.e., the answer to your question), and any other noteworthy details, but these should be brief and in point form.
While not required, including some dashboard-style customization in your report would be a positive (e.g., drop-down menus, macro buttons). Even if you do not include this, the charts should still be linked to the data so that if it were changed, the charts would update accordingly.
Make sure to include the most relevant/appropriate/interesting data visualizations here (at least one for each question).
The sheet should be formatted to look clean and presentable, e.g. hide gridlines, keep visuals aligned with each other, proper labels, etc.
For each research question, your report should include:
- A brief statement of the research question
- Explain any methodology used that would not be easily understood without doing so, e.g., if you used variables that were transformed in any way (e.g., collapsed categories, sums/averages of multiple variables, variables with certain values excluded, etc.)
- At least one chart that bests tells the story
- A brief text description of the result (i.e., the answer to your question)
The report should roughly fit on one 16:9 screen. Different resolutions on different computers will make it larger or smaller, but as long as I can zoom in/out to a point where it nicely fits in that rectangular area, that’s fine.
When complete, submit your workbook below.
Feel free to reach out to me for further guidance if necessary.
Example Demo Video: https://youtu.be/54oiruXY2Ck
(This is not an ideal example, it’s very quick and simple)