Mastering Problem Analysis with Microsoft Excel
Published by O'Reilly Media, Inc.
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 live event 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.
Prerequisites
- Basic familiarity with Excel, including how to use Excel formulas
Recommended preparation:
- 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
Recommended follow-up:
- Read Excel 2019 Bible (book)
Schedule
The time frames 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
- Q&A
- 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
- Q&A
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
- Q&A
- 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
- Q&A
Your Instructor
Dawn Griffiths
Dawn Griffiths is an author and trainer with over 20 years of experience using Excel. Her most recent book is Excel Cookbook, and she’s also written several books in the Head First series, including Head First Statistics, _Head First Android Development, and Head First Kotlin. Dawn also developed the animated video course The Agile Sketchpad with her husband, David, to teach key concepts and techniques in a way that keeps your brain active and engaged.