In this valuable webcast, Excel expert David Ringstrom, CPA, draws your attention to the What-If Analysis tools available within Excel’s Data menu. David explains how and when to make use of Excel’s Scenario Manager as well as how to use the Data Table feature to compare calculation results based on two or three inputs. He also shows you how Excel’s Goal Seek can be implemented to perform basic what-if analyses, allowing you to solve for a single value.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.

Who should attend:
Practitioners seeking to understand the what-if problem-solving capabilities in Excel.

Topics covered:

  • Avoiding the need to write repetitive formulas using Excel’s Data Table feature.
  • Comparing the results of multiple scenarios within a pivot table.
  • Contrasting Excel’s Data Table feature with traditional approaches used in spreadsheets.
  • Enabling Excel’s Solver add-in for more complex what-if analyses.
  • Enhancing the Summary Report generated by the Scenario Manager with range names.
  • Exploring Excel’s Goal Seek feature, which can be used to solve for a single missing input.
  • Exploring Excel’s Scenario Manager feature that enables you to store various sets of inputs, such as best case, worst case, and most likely, without having to replicate worksheets or workbooks.
  • Exploring the Forecast Sheet feature in Excel 2016 and later, which can extrapolate trends based on existing data in your spreadsheets.
  • Identifying duplicates in a list using Conditional Formatting.
  • Incorporating decisions into calculations with Excel's IF function.
  • Learning the basics of Excel’s Solver feature.
  • Merging scenarios from other workbooks into your present workbook.
Learning Objectives
  • Identify when to use Excel’s Goal Seek feature versus Solver.
  • Recognize and recall how to use the Data Table feature in Excel to compare calculation results based on varying inputs.
  • Discover and define how to make one worksheet serve multiple purposes by way of Excel’s Scenario Manager.
Last updated/reviewed: August 11, 2023

Included In Certifications

This course is included in the following Certification Programs:

17 CoursesExcel Modeler Certification

  1. Excel Efficiency: VLOOKUP
  2. Excel Efficiency: Logic Functions
  3. Excel Efficiency: Filtering and Formatting Data
  4. Excel Efficiency: Intermediate Pivot Tables
  5. Excel Efficiency: Auditing Spreadsheets
  6. Excel Efficiency: Minimizing Worksheet Errors
  7. Excel Efficiency: Workbook Links
  8. Excel Efficiency: What-If Analysis in Microsoft Excel
  9. Excel Efficiency: Quick and Easy Financial Statements in Excel
  10. Excel Efficiency: Budget Spreadsheets
  11. Hands-On Excel: Waterfall Calculations
  12. Excel Efficiency: Table Feature
  13. Excel Efficiency: Intro to Macros Part 1
  14. Excel Efficiency: Intro to Macros Part 2
  15. Excel Efficiency: Excel Chart Speed Tips
  16. Excel Efficiency: Taming Large Spreadsheets
  17. Excel Efficiency: Internal Controls
13 Reviews (64 ratings)

Reviews

5
Anonymous Author
Another great course form Instructor David Ringstrom, It was kind of hard but finally I got. I don not work with financial data, I work with State report for students. so was a bit challenge for me. I learn something new .

5
Anonymous Author
This was a great course to introduce me to features of excel that I have never used to give me a great understanding of how they might be used in practical everyday applications of Excel.

5
Member's Profile
This is possibly one of the very best Excel courses I have ever taken. I am an intermediate Excel user and I learned a ton from this presentation. A job well done.

4
Member's Profile
Good examples and instructions as always. Some of the tools are a little hard to understand and I'm not sure that they are as useful for every day Excel users.

4
Anonymous Author
Informative course that can be used and referenced in the future. As a user of excel who does not use the what if analysis often this is a good reference tool

5
Member's Profile
David does a great job of explaining the class objective then actually demonstrating the concept to really make sure you understand the lesson.

4
Member's Profile
Clear, focused presentation. I would like more keyboard shortcuts, but appreciate the step by step instructions.

5
Anonymous Author
Great content! I really learned a lot about how to apply Excel functions to financial planning & analysis.

4
Member's Profile
I like the scenario manager feature. I had not been exposed to that prior to this course.

5
Member's Profile
I really enjoy this presenter. Excellent and thorough with his excel teaching and examples.

5
Anonymous Author
Great information that I'm looking forward to using in my daily work

5
Member's Profile
Excellent course that cover some lesser known excel tools

5
Member's Profile
I always learn something in David's excel courses.

Prerequisites
Course Complexity: Intermediate

Program Prerequisites: Experience Using Excel to Store Lists of Data Is Recommended

Advance Preparation: None

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Instructor for this course
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to What If Analysis 12:48
  CUMIPMT Function3:43
  Scenario Manager Feature17:24
  Named Ranges/Scenario Summary4:48
  Merging Scenarios2:53
  Goal Seek Feature5:42
  Data Table Overview9:04
  Data Table - Two Variables2:48
  Data Table - Three Variables9:02
  Resizing Data Tables7:50
  Enabling Excel's Solver Add-In2:14
  Solver Introduction4:28
  Solver Find Amounts that Add Up to an Input19:02
CONTINUOUS PLAY
  Excel Efficiency: What-If Analysis 1:41:46
SUPPORTING MATERIALS
  Slides: Excel Efficiency: What-If Analysis in Microsoft ExcelPDF
  Excel Efficiency: What-If Analysis in Microsoft Excel Glossary/IndexPDF
  Workbook: Excel Efficiency: What-If Analysis in Microsoft ExcelXLSX
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam