Data Analysis in Excel

8 hours of instruction

Using their own licensed version of Excel, students will build on foundational Excel skills to handle more complex analytical situations. Students will learn how to build a variety of models and test scenario analyses to make better data-driven decisions. By the end of this program, students will be able to produce multiple scenarios in Excel, optimize data models, and build predictive linear models to test cause and effect relationships.

OBJECTIVES

1. Build data models in Excel
2. Build multiple scenarios in Excel
3. Evaluate and optimize data models

PREREQUISITES

Students must be comfortable using Excel to clean, merge, wrangle, and visualize data. They should be familiar with writing advanced functions and creating PivotTables.

SYLLABUS & TOPICS COVERED

1. Introducing analytics in Excel
   • Introducing What-If Analysis
   • Trying out values with Goal Seek
   • Analyzing a scenario with a 1-variable data table
2. Solving optimization problems
   • Analyzing a scenario with a 2-variable data table
   • Using Scenario Manager to compare multiple possibilities
   • Introducing Excel Solver
   • Setting up and running Excel Solver
3. Advanced optimization and prediction
   • Using Excel Solver for Scenario Analysis
   • Creating a Monte Carlo simulation
   • Introducing linear regression
   • Visualizing data with a scatterplot
4. Interpreting linear regression results
   • Evaluating a linear regression
   • Preparing data for multiple linear regression
   • Running a multiple linear regression
   • Encoding categorical data for regression

SOFTWARE REQUIREMENTS

• Learners will need to provide their own licensed version of Excel (at least 2016) and have the ability to install the Analysis ToolPak and the Solver Add-in.