Mastering Problem Analysis with Microsoft Excel
How to use Microsoft Excel’s What-if Analysis to solve problems and explore scenarios
Excel’s What-If Analysis tools―data tables, Scenarios, Goal Seek and Solver―are a mystery to many. But once mastered, they can help you make important business decisions and save you and your business time and money.
Expert Dawn Griffiths shows you how Excel’s built-in analysis tools can make problem-solving a breeze. You’ll learn how to use data tables and Scenarios to explore the potential impact of a range of situations; how to use Goal Seek to help you meet your targets; and how to use the powerful Solver tool to quickly solve complex problems such as how to maximize your profits and minimize your overhead.
What you'll learn-and how you can apply it
By the end of this live online course, you’ll understand:
- The purpose of What-If Analysis
- How to use Excel’s What-If Analysis tools: data tables, Scenarios, Goal Seek, and Solver
- What each tool does and the types of problems each one can help you solve
And you’ll be able to:
- Use one-input and two-input data tables to explore the impact of possible changes in your data
- Use Scenarios to manage different versions of your data and quickly see the impact of best- and worst-case scenarios
- Use Goal Seek to find out what value is required to get a specific result
- Use Solver to solve more complex problems, such as maximizing profits or minimizing overhead within defined constraints
This training course is for you because...
- You’re an Excel power user who wants to learn how Excel’s more advanced features can help you work more efficiently.
- You’re a business manager who needs to solve problems such as how to maximize profits and minimize overheads.
- You’re responsible for budgets, and you want to be able to explore different options and scenarios without changing your underlying data.
- You’re a data analyst who wants to find out more about Excel’s What-If Analysis tools.
- Basic familiarity with Excel, including how to use Excel formulas
- In order to take part in the course exercises (optional) you will need to have either Excel 2019 or Excel for Office 365 installed. Make sure that What-If Analysis is available on the Data tab, and that the Solver add-in has been enabled. Instructions for enabling Solver can be found here. All course demonstrations will use Excel for Office 365.
- Read Head First Excel Chapter 1
- Read Excel 2019 Bible, Chapter 9 (Introducing formulas and functions)
- Attend live training Foundations of Excel: Functions, Tables and Pivot Tables
- Read Excel 2019 Bible (book)
About your instructor
Dawn Griffiths has over 20 years’ experience using Excel. She’s written several books in the Head First series, including Head First Statistics, Head First 2D Geometry, Head First Android Development, and Head First Kotlin. She also developed the animated video course The Agile Sketchpad with her husband, David, as a way of teaching key concepts and techniques in a way that keeps your brain active and engaged. She has a first-class honours degree in mathematics.
The timeframes are only estimates and may vary according to how the class is progressing
Data tables (50 minutes)
- Presentation: Introduction to What-If Analysis
- Walk-throughs: How to use a one-input data table to explore the impact of a single cell value changing; how to add multiple formulas to a one-input data table; how to use a two-input data table to explore the impact of two cell values changing
- Hands-on exercises: Create a one-input data table; add multiple formulas to a one-input data table; create a two-input data table
Break (5 minutes)
Scenarios (35 minutes)
- Presentation: Introduction to Scenarios
- Walk-throughs: How to use scenarios to store and substitute different sets of values; how to merge scenarios from other worksheets or workbooks
- Hands-on exercises: Create and use scenarios; use merging to add to your scenarios
Goal Seek (40 minutes)
- Presentation: Introduction to Goal Seek
- Walk-throughs: How to use Goal Seek to find out what cell value will produce a required result; Goal Seek techniques for finding solutions to problems
- Hands-on exercises: Solve a problem using Goal Seek; solve more problems using Goal Seek
Break (5 minutes)
Solver (45 minutes)
- Presentation: Introduction to Solver
- Walk-throughs: How to use Solver to solve simple problems; how to use constraints to solve more complex problems
- Hands-on exercises: Solve a simple problem using Solver; solve more complex problems by adding constraints