O'Reilly logo
live online training icon Live Online training

Mastering Problem Analysis with Microsoft Excel

How to use Microsoft Excel’s What-if Analysis to solve problems and explore scenarios

Topic: Business
Dawn Griffiths

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.

Prerequisites

  • Basic familiarity with Excel, including how to use Excel formulas

Recommended preparation:

Recommended follow-up:

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.

Schedule

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
  • 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