Excel Agility - What-If Analysis
Please see below for additional instructions and information regarding this program.
This course is designed to teach you how to implement Excel’s powerful What-If Analysis tools.
Excel expert David Ringstrom, CPA, demonstrates how and when to make use of Excel’s Scenario Manager, how to use the Data Table feature to compare calculation results based on two or three inputs, how Excel’s Goal Seek feature empowers you to perform basic what-if analyses, and much more.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He’ll draw to your attention any differences in Excel 2013, 2010, or 2007 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.
Who Would Be Interested in This Course:
Practitioners seeking to understand the what-if problem-solving capabilities in Excel.
- Enhancing the Summary Report generated by the Scenario Manager with range names.
- Using the Summary Report aspect of Scenario Manager to compare different scenarios side by side.
- Implementing a simple Excel macro that will empower you to apply multiple scenarios at once when you have more than 32 inputs.
- Exploring the Forecast Sheet feature, which can extrapolate trends based on existing data in your spreadsheets.
- Enabling Excel’s Solver Add-in for more complex what-if analyses.
- Pausing Goal Seek when necessary to check the status of calculations that involve large numbers.
- Contrasting Excel’s Data Table feature with traditional approaches used in spreadsheets.
- Avoiding the need to write repetitive formulas with Excel’s Data Table feature.
- Utilizing the PMT worksheet function to calculate loan payment amounts.
- State the mouse action that enables you to edit a shape, such as to assign a macro.
- Recall how to use the Data Table feature in Excel to compare calculation results based on varying inputs.
- Recognize the ribbon tab where the Macros command appears in Excel.
NASBA Field of Study:
Computer Software & Applications (2 hours)
Experience Using Excel to Store Lists of Data Is Recommended
Excel Versions 00:00:48
Excels What-If Analysis Features 00:01:52
PMT Function 00:05:24
Scenario Manager Feature 00:11:42
Scenario Manager Feature (cont.) 00:15:07
Scenario Manager Feature (cont.) 00:17:38
Scenario Manager Summary 00:19:58
Named Ranges/Scenario Summary 00:24:00
Named Ranges/Scenario Summary (cont.) 00:27:27
Scenario Summary Pivot Table 00:28:22
Merging Scenarios 00:31:44
Scenarios with >32 Fields 00:34:56
Scenarios with >32 Fields (cont.) 00:37:55
Scenarios with >32 Fields (cont.) 00:40:25
Automating Scenarios >32 Fields 00:42:51
Automating Scenarios >32 Fields (cont.) 00:48:14
Automating Scenarios >32 Fields (cont.) 00:53:37
Goal Seek Feature 00:57:04
Pausing Goal Seek 01:01:37
Data Table Overview 01:06:26
Data Table - One Variable 01:07:55
Data Table - Two Variables 01:12:11
Data Table - Three Variables 01:18:21
Data Table - Three Variables (cont.) 01:22:23
Resizing Data Tables 01:25:19
Forecast Feature (Excel 2016+) 01:28:41
Forecast Feature (Excel 2016+) (cont.) 01:33:07
Forecast Feature Compatibility 01:33:25
Enabling Excel’s Solver Add-In 01:34:37
Introduction to Solver 01:37:11
Saving Solver Results to Scenarios 01:39:58
Resolving Solver32.dll Error 01:41:59
Presentation Closing 01:43:59
- Add-In 01:34:48
- CUMIPMT 00:08:38
- Data Table 01:06:29
- FIND Function 01:19:38
- Forecast Feature 00:04:11, 01:28:44
- Goal Seek 00:02:41, 00:57:07
- LEFT Function 01:19:14
- Macros 00:42:56
- MID Function 01:20:15
- Pivot Table 00:28:27
- PMT Function 00:05:38, 01:22:29
- Range Names 00:24:07
- Scenario Manager 00:02:15. 00:11:45, 00:13:10, 01:40:07
- Solver 00:03:14, 01:34:40
- Visual Basic Editor 00:43:51
- What-If Analysis 00:01:54, 00:13:05
Add-In: An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. The file contains code (VBA in the case of an .xla Add-In) that adds additional functionality to Excel, usually in the form of new functions.
CUMIPMT : The CUMIPMT worksheet function enables you to calculate the interest expense (or earnings) on a loan for as little as one period, such as a month or year, or as long as the entire length of the loan.
Data Table: Data tables are defined as a range of cells that are used for testing and analyzing outcomes on a large scale. It is a way to see how altering the values in a formula affect the results. Data tables can store the results of multiple scenarios in your spreadsheet, and saves you time in calculating multiple formulas.
FIND Function: The Microsoft Excel FIND function returns the location of a substring in a string. The search is case-sensitive. The FIND function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.
Forecast Feature: A feature in Excel 2016 that enables you to extrapolate trends for a data set into the future.
Goal Seek : A feature in Excel that simplifies repetitive data entry by enabling the user to automatically solve for a specific amount.
LEFT Function: The Microsoft Excel LEFT function is a function which allows you to extract a substring from a string and starts from the leftmost character. This is a built-in function in excel which has been categorized as a String/Text Function.
Macro: One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.
MID Function: The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl". Extract text from inside a string. The characters extracted. =MID (text, start_num, num_chars)
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
PMT Function: The PMT function enables you to calculate a loan payment based on providing an interest rate, period of the loan, and amount to be borrowed or lent. The interest rate must be on the same footing as the term of the loan, so if the loan period is expressed in months, be sure to divide the interest rate by 12.
Range Names: A user-defined identifier for a cell or block of cells. Range names simplify formula writing and auditing, as the words SalesTax are readily identifiable within a formula as opposed to $B$1. Range names cannot contain spaces; must begin with a letter; be no longer than 255 characters in length; and can be comprised of only letters, numbers, underscores, periods, and slashes.
Scenario Manager: The Scenario Manager feature allows you to create scenarios that store up to 32 inputs. You can then swap out sets of inputs on a worksheet by applying a scenario or creating reports that compare the output of scenarios. If you have more than 32 inputs that you wish to save, you can create and then apply two or more scenarios sequentially.
Solver: Solver is an add-in for Microsoft Excel that allows you to perform what-if analysis operations. Excel's Goal Seek feature allows you to solve for a single input, while Solver allows you to solve for a single input while optionally placing constraints additional cells during the solving process.
Visual Basic Editor : Excel’s development interface that can be used to edit and create macros, user forms, class modules, custom worksheet functions, and other enhancements to Excel’s functionality. The programming interface for Microsoft Excel that can be accessed by way of the Visual Basic command on Excel’s Developer tab or by pressing Alt-F11.
What-If Analysis: What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Three kinds of What-If Analysis tools come with Excel: Scenarios, Goal Seek, and Data Tables. Scenarios and Data tables take sets of input values and determine possible results.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively. To learn more about David, you can view his LinkedIn profile and follow him on ... View Full Profile
Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or firstname.lastname@example.org.
You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.