Add-ins are a feature in Microsoft Excel that provide additional features and commands. Two of the more popular add-ins are the Analysis ToolPak and Solver, both of which provide extended data analysis capability for “what-if” planning. To use these add-ins, you need to install and activate them.
If you need to develop complex statistical or engineering analyses, you can save steps and time by using the Analysis ToolPak. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables.
The data analysis functions can be used on only one worksheet at a time. When you perform data analysis on grouped worksheets, results will appear on the first worksheet and empty formatted tables will appear on the remaining worksheets. To perform data analysis on the remainder of the worksheets, recalculate the analysis tool for each worksheet.
To access tools of the Analysis ToolPak, click Data Analysis in the Analysis group on the Data tab. If the Data Analysis command is not available, you need to load the Analysis ToolPak add-in program.
Install and activate the Add-ins
- Click the File tab.
- Click Options, and then click the Add-Ins category.
- Near the bottom of the Excel Options dialog box, make sure that Excel Add-ins is selected in the Manage box, and then click Go.
- In the Add-Ins dialog box, select the check boxes for Analysis ToolPak and Solver Add-in, and then click OK.
- If Excel displays a message that states it can’t run this add-in and prompts you to install it, click Yes to install the add-ins.
Get started using the Analysis ToolPak
- On a worksheet, click a cell.
- On the Data tab, in the Analysis group, click Data Analysis.
- In the Data Analysis dialog box, click the tool that you want to use and click OK
- In the dialog box that appears for the tool you chose, enter the parameters and select any options as needed.
Solver is part of a suite of commands sometimes called what-if analysis tools. With Solver, you can find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet.
A model is a simplification of the real world. It’s also a tool for problem solving
- Help us communicate
- Allow us to clarify and test understanding
- Create credibility and accountability
- Help you organize your thoughts
- Simplify and solve problems
- Help you understand your data
You have probably used a spreadsheet to see what will happen when you change a value.
The sorts of things that you might have wanted to try out could be:
‘If the cost of food rises by 10%, how much will that decrease the profit we make for the school fete?”
“If I reduce the number of staff in my business by two, what effect will that have on the profit at the end of this month?”
Analysis works well in spreadsheets because all of the calculations have been set up using formulas. So, when you change one value e.g. food cost, all of the values related to it, e.g. food cost, total costs, profit will change automatically.
Spreadsheets are excellent for analysis because you can keep testing out different scenarios until you find one you are happy with.
You can keep adjusting the model until you get the result that suits your business.
At this stage, it is only numbers in a spreadsheet, you are not risking your business or upsetting your staff. But you will get an idea of what might happen if you go ahead and make the changes for real.
What if analysis is a technique used to evaluate the effects of changing selected factors in a worksheet. This technique is a common accounting function that has made much easier with the introduction of spreadsheet programs. By substituting different values in cells that are referenced by formulas, we can quickly see the effect of the changes when the formulas are recalculated.
|Cost of goods sold||4543||5600||675||10818||3606|
To increase the profit margin, we will need to adjust the values in the sheet. After some considerations, we decide we can most easily reduce monthly payroll expenses by carefully scheduling employee work during these three months. Reducing the monthly expense will increase the profit margin for the quarter. We want to find out the maximum payroll value we can spend during that period is for each month to accomplish this goal.
To do this, we will enter different payroll expense values for each month and see what are effect is on that month’s profit margin.
It took us several tries to find the payroll value that would achieve the profit margin objective. The Goal seek tool is to find the value needed in one cell to attain a result we want in another cell. Goal Seek varies the value in the cell we specify until a formula that is dependent on that cell returns the desired result. The value of only one cell can be changed.
Click in the by changing Cell text box and then click on the cell in the worksheet to enter the cell reference to the required cell in the text box.
Excel’s Scenario Manager feature enables you to analyze your data to see how changing one or more values in the worksheet affects the other cells in the worksheet. This feature comes in handy for figuring out what would happen if certain factors in your business changed.
In many cases, you use worksheets to perform what-if analysis. After you set up a series of calculations, you can change the values of certain cells to view different scenarios.
The Tools, Scenarios option enables you to substitute one or more values with a range of values and observe how the new values affect the rest of the data in the worksheet.
Displaying a Scenario
Once you create a scenario, you’ll want to display it on your worksheet to be able to perform a true what-if analysis.
- Choose Tools: Scenarios… from the Menu bar
- Click the name of the scenario that you want to display
- Click Show
Create a Scenario Summary
There are two types of scenario summary:
- Tabular view: Shows each scenario variable and the result produced.
- PivotTable view: Shows just the scenario names and the result produced