On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
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 and 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 Would Be Interested in This Course:
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.
- Recall how to use the Data Table feature in Excel to compare calculation results based on varying inputs.
- Define how to make one worksheet serve multiple purposes by way of Excel’s Scenario Manager.
Level:
Intermediate
Format:
On-Demand Webcast
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
- Introduction
- Excel Versions 00:01:25
- Excels What-If Analysis Features 00:02:11
- IF Introduction 00:04:54
- PMT Function 00:09:50
- CUMIPMT 00:12:50
- Scenario Manager Feature - Steps 1-8 00:16:36
- Scenario Manager Feature - Steps 9-13 00:23:07
- Scenario Manager Feature - Steps 14-16 00:26:12
- Scenario Manager Summary 00:28:06
- Scenario Summary Pivot Table 00:32:46
- Named Ranges/Scenario Summary - Steps 1-9 00:34:02
- Named Ranges/Scenario Summary - Step 10 00:35:03
- Merging Scenarios 00:38:51
- Goal Seek Feature 00:41:45
- Data Table Overview 00:47:31
- Data Table - One Variable 00:51:17
- Data Table - Two Variables 00:56:42
- Data Table - Three Variables - Steps 1-6 00:59:26
- Data Table - Three Variables - Steps 7-14 01:04:46
- Resizing Data Tables 01:08:31
- Forecast Feature (Excel 2016+) 01:10:17
- Enabling Excel’s Solver Add-In 01:16:23
- Solver Introduction 01:18:38
- Solver: Find Amounts That Add Up to an Input - Steps 1-3 01:23:03
- Solver: Find Amounts That Add Up to an Input - Steps 4-7 01:25:03
- Solver: Find Amounts That Add Up to an Input - Steps 8-14 01:27:50
- Solver: Find Amounts That Add Up to an Input - Steps 15-24 01:29:52
- Solver: Find Amounts That Add Up to an Input - Steps 25-28 01:32:00
- Solver: Find Amounts That Add Up to an Input - Steps 29-31 01:32:35
- Solver: Find Amounts That Add Up to an Input - Steps 31-34 01:33:38
- Solver: Find Amounts That Add Up to an Input - Steps 35-36 01:34:11
- Solver and Duplicate Amounts 01:34:22
- Conditional Formatting - Duplicates 01:38:14
- Thank You For Attending! 01:41:49
- Add-In 01:16:27
- Cell 00:18:01, 00:35:39, 01:05:30, 01:28:02
- Column 00:17:51, 00:48:39, 01:08:54, 01:25:25
- Conditional Formatting 01:34:45, 01:38:29
- Create from Selection Command 00:34:19
- CUMIPMT 00:12:53
- Data Table 00:02:36, 00:47:41, 00:51:59, 00:57:03, 01:05:02
- Dialog Box 00:20:01, 00:28:34, 00:34:23, 00:39:09, 01:29:24
- Filter 01:25:32
- Forecast Feature 00:01:39, 00:03:18, 01:10:17
- Formula 00:02:51, 00:29:11, 00:34:09, 00:42:00, 00:48:23, 01:08:47
- Goal Seek 00:00:46, 00:02:34, 00:41:47, 00:45:58
- IF Function 00:00:17, 00:05:05
- IS Functions 00:05:50
- LEFT Function 01:00:05
- Microsoft 365 00:01:30
- MID Function 01:00:06, 01:03:42
- Pivot Table 00:02:54, 00:28:47, 00:33:05, 01:13:54
- PMT Function 00:09:57, 00:13:12, 00:46:21, 00:48:24, 01:04:50
- Quick Access Toolbar 00:27:25
- Ribbon 00:02:21
- Row 00:48:31, 01:08:40, 01:25:37
- Scenario Manager 00:00:39, 00:02:33, 00:16:36, 00:27:13, 00:28:30, 00:39:01
- Scenario Summary 00:28:39
- Solver 00:01:01, 00:04:04, 00:45:03, 00:47:11, 01:16:26, 01:18:38, 01:27:52
- SUBTOTAL 01:25:26, 01:32:38
- SUM 01:25:21, 01:32:48
- Table Feature 00:02:59, 00:49:19
- What-If Analysis 00:00:25, 00:02:13, 00:10:01, 00:32:56, 00:34:53, 00:38:58, 00:51:56, 01:05:22
- Workbook 00:18:15
- Worksheet 00:18:11, 00:31:56, 00:39:16
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.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
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.
Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Forecast Feature: A feature in Excel 2016 that enables you to extrapolate trends for a data set into the future.
Formula: A formula is an expression which calculates the value of a cell.
Goal Seek : A feature in Excel that simplifies repetitive data entry by enabling the user to automatically solve for a specific amount.
IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
IS Functions: The IS Functions return a TRUE or FALSE value based upon a condition they evaluate. They can detect errors, blank cells, text, numbers...etc. They are critical in the development of dashboard interfaces and are heavily used with the IF function.
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.
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)
Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
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.
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.
Quick Access Toolbar: A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
Ribbon: The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
SUBTOTAL: A worksheet function that allows you to sum, average, count, and other otherwise analyze data on just the visible cells within a given range.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet.
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.
Scenario Summary: A Scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios and then switch between these scenarios to view the different results.
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.
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
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.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
