Using Excel What-If Analysis: When, Why, and How (Currently Unavailable)

Author: David H. Ringstrom

CPE Credit:  2 hours for CPAs

Excel can be a powerful analytical tool if you know how to make the most of its features. This class reviews Scenario Manager, Data Tables, and Goal Seek, as well as the 2016 version's Forecast and Solver features to help you perform simple what-if analyses.

Publication Date: July 2018

Designed For
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.
  • Contrasting Excel's Data Table feature with traditional approaches used in spreadsheets.
  • Utilizing a work-around that will let you go beyond the 32-input limit built into Scenario Manager.
  • Utilizing the CUMIPMT worksheet function to calculate interest amounts for any period of a loan without utilizing an amortization table.
  • Utilizing the PMT worksheet function to calculate loan payment amounts.
  • Exploring the Forecast Sheet feature in Excel 2016, which can extrapolate trends based on existing data in your spreadsheets.
  • Understanding the nuances of resizing data tables within Excel spreadsheets.
  • Implementing a simple Excel macro that will empower you to apply multiple scenarios at once when you have more than 32 inputs.
  • Utilizing a work-around that will let you go beyond the 32-input limit built into Scenario Manager.
  • Exploring Excel's Goal Seek feature, which can be used to solve for a single missing input.
  • Utilizing a work-around that will let you go beyond the 32-input limit built into Scenario Manager.
  • Using the Summary Report aspect of Scenario Manager to compare different scenarios side by side.

Learning Objectives

  • Recognize the ribbon tab where the Macros command appears in Excel.
  • State the mouse action that causes the Fill Handle to copy formulas or values down a column or to create a series.
  • Identify the menu command where Excel's what-if commands reside.

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

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

Advance Preparation
None

">
 Chat — Books Support