Scenario Analysis - Goal Seek Video Tutorial (Data Mining Add-in For Excel)
Applies to:Microsoft SQL Server Analysis Services |
Author: Michele Hart, Microsoft Corporation Narrator: Michele Hart, Microsoft Corporation |
Length: 00:5:32 Size: 13,018 KB Type: WMV file |
.gif "Watch this video") |
|
|
Video Summary
In this tutorial we will learn how to use the Scenario Analysis - Goal Seek Table Analysis Tool for Microsoft Excel.
Video Transcript
Introduction
Welcome to another SQL Server 2008 Analysis Services (SSAS) screencast from Michele and Mary.
My name is Michele Hart and I’m a Technical Writer for Microsoft SQL Server 2008.
In this video I will show you how to use the Scenario Analysis - Goal Seek Table Analysis Tool for data mining.
This tool is just one of many data mining add-ins for Excel and we’ve created screencasts for each one.
The Scenario Analysis tool uses the Microsoft logistic regression algorithm and allows you to model two types of scenarios and report the effect on either a single row or the entire table of input data. Those two scenarios are Goal Seek and What If. In this video we’ll look at the Goal Seek scenario and in a separate video we’ll take a look at What If.
Goal Seeking helps you answer the question "What needs to change so that I can achieve my goal of such-and-such?" For example, "how can I grow my company from ten million to fifty million dollars?" Or, "how can I increase sales by 20%?" This tool helps determine how you can reach a goal, based on factors that it analyzes in your data.
In this tutorial we’ll use call center data to see how we can decrease lost calls. The Excel data I am using is available on codeplex.com. If you use your own spreadsheet, just remember that in order to find meaningful patterns, you must begin with a minimum amount of good data. At least fifty rows of data.
The Wizard
We are interested in Level2Operators and ServiceGrade, so for the purposes of this tutorial I’m going to hide some columns to make reviewing the results easier.
To begin, select the Table Analysis Tools Sample tab and click anywhere inside the table to activate the Table Analysis Tools.
Under the Table Tools menu, select the Analyze tab to open the Table Analysis Tools ribbon.
Click Scenario Analysis and then Goal Seek to launch the wizard.
Select Service Grade as the target column.
Select In range, and type 0 to .05. What would it take to decrease our dropped calls to 5%?
If the target column contains continuous numeric values, you can also specify a desired increase or decrease in the value. For example, you can choose Service Grade as the target and specify a decrease of 95%. Or, you can specify the goal as an exact value.
In the What to Change box, select the column that will be manipulated to produce the desired result. I am going to select LevelTwoOperators.
If I click Run now, the analysis will be performed on all of the columns. Instead, I open Choose columns to be used for analysis… and deselect FactCallCenterID, Column1, and TotalOperators. By streamlining my analysis I improve performance and accuracy. But be careful; don’t deselect columns that you will use for either the target goal or the change.
There is a bug in the wizard that you need to watch out for. If I make any changes in the Choose columns to be used for analysis window, I often see changes made to my Goal to seek selections. Just double-check before you click Run.
I will make predictions on Entire table and click Run.
My results are added as new columns to the right of the original data table. These columns show the likelihood that the targeted outcome can be achieved, and the recommended changes, if any.
The green circles on a row show that if you change the number of Level 2 Operators, the ServiceGrade goal of 0-5% can be met.
The red circles on a row indicate that the target cannot be met even if the number of Operators is increased or decreased.
Now let’s perform a Goal Seek on a single row of data.
With a single row, the tool reports in the Results pane of the dialog box. If a successful solution is found, the tool displays the required change. For example, the Goal Seek tool might tell you that increasing the number of Level2Operators from 2 to 4 would reduce ServiceGrade to 5% or better.
I’ll quickly go through the same steps as we did for an entire table EXCEPT that I will make my predictions for On this row and then click Run.
My results display below; along with a level of confidence. This tells me that I can improve service grade from 8% to my desired level by increasing operators to 7. But that confidence in this solution is only Fair.
If I select a row with a 14% service grade, Goal Seek does *not* find a solution.
And finally, if I select a row that already has a 5% or better service grade, Goal Seek finds a solution, and that solution is to leave the number of Level 2 Operators unchanged.
Conclusion
This concludes the Scenario Analysis - Goal Seek video tutorial. For additional help with the data mining add-ins for Excel, I recommend viewing the other video tutorials and the Help documentation included with the Data Mining Add-ins. Thank you for viewing this tutorial.