This is module 3 of 10 in the course on Business Analytics.
Readings
The Basic Tools of Business Analytics
+
Self-Directed Learning
Search the internet for articles, other resources, and examples of how to use more advanced features of Excel for business analytics. See what you can learn from what others are saying. Resources related to Excel might be webpages about particular functions, introductory videos, etc. that you found helpful. Maybe you can even find tutorials about specific business applications of Excel. LinkedIn Learning has a number of Excel tutorials.
+
Discussion of Module Topic
Write down your thoughts and experiences while you’re learning. For this module, this should include writing about your experience of learning Excel functions.Here are some prompts to get you started for writing about the module topic:
- Provide links to any useful and relevant resources about Excel that you can find on the internet.
- Do you have any prior experience in using more advanced features of Excel like VLOOKUP or PivotTable?
- What do you find interesting or useful about Excel?
- Describe any useful functions or tricks in Excel that you have learned about in this module.
- Do you have any thoughts on potential limitations of Excel?
- What are some other software tools that are available for business analytics?
+
Discussion of Module Submission
For this module, the discussion of the submission should include writing about your experience of practicing with statistics and data visualization. As you’re working on the module exercises, here are some prompts to get you started for writing about it:
- Describe your data and analysis. What interesting conclusions did your reach?
- What was your experience in downloading the data?
- Were you able to reference data in other worksheets?
- What are other examples of how you could use an IF statement?
- VLOOKUP is challenging at first, but can be very useful. What did you learn?
- Pivot Tables are extremely powerful for summarizing data. How did it go? How can you use pivot tables to analyze data in unique ways?
+
Module Exercises
For these exercises, we will be working together in Excel using Microsoft Teams. The goal is to start working with some larger, realistic data and to use some more advanced functions in Excel.
Each student can contribute in different ways to the shared Excel files on the M3 Teams channel. Not every student needs to contribute data. However, every student should be attempting Excel functions like the IF command, VLOOKUP, and the PivotTable. This can be done using data that other students have contributed! The main goal is for each student to learn more about how Excel can be used for business analytics. Students should also be continuing to practice their statistics and data visualization skills.
A. Try to find some interesting data on the internet. Look for whatever kind of data interests you. Kaggle is a free resource for downloadable data. Feel free to reduce the size of a large downloaded dataset to a manageable number of fields (columns) and observations (rows). Try to work with data that has at least 3 fields and 50 observations. The fields should be clearly labeled in the first row. You can add a data worksheet to “Excel Exercises” or create a new Excel workbook. Make sure to document your data in a ReadMe and tell other students about it in Posts.
B. Organize and build on the data in a different worksheet.
- Practice sorting the data on a different field or filtering the data to show fewer observations. You could also hide columns if you like.
- Practice using a calculation to construct a new field (variable) based on the raw data (e.g. a sum of two fields).
- Practice using a version of an IF command to construct a new 0/1 field based on the raw data (i.e. a “yes”/”no” variable that creates two groupings based on some condition)
C. Calculate some of basic statistics for the data based on Module 2 (e.g., mean, correlation, etc.)
D. Use VLOOKUP to “look up” a value in the dataset. For VLOOKUP, you need a field that is the “identifier” and a field that is the value of interest.
E. Use PivotTable to show a table that summarizes the data. Working with data that has “groups” is the easiest way to learn PivotTable. It will return basic statistics like sum and average for each group. You could post a screenshot of your PivotTable in Posts and say something about why you think it is interesting.
F. Create a chart that visualizes the data and tells a story. This chart should have a title and labeled axes. Under the chart, you should write one sentence that is the conclusion of your analysis. This sentence should be a clear interpretation of your chart. You could post a screenshot of your chart in Posts and say something about why you think it is interesting.