Spreadsheet Add-Ins

Go back to Tutorial

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

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.

What is Spreadsheet Modeling

A model is a simplification of the real world. It’s also a tool for problem solving

Why Model?

• 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

Using Spreadsheets

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.

Why Spreadsheets

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

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.

Particulars	Apr	May	Jun	Total	Average
Sales
Beverage	78000	234242	34543	346785	115595
Food	54353	23424	34535	112312	37437
Internet	45645	2300	67677	115622	38541
Total Sales	177998	259966	136755	574719	191573
Expenses
Cost of goods sold	4543	5600	675	10818	3606
Payroll	67800	4535	787	73122	24374
Computers	65765	30000	8766	104531	34844
Miscellaneous	786	78686	89888	35435	675
Total Expenses	138894	118821	100116	223906	63499
Income
Net Income	39104	141145	36639	350813	128074
Profit margin	22.0%	54.3%	26.8%	61.0%

 

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.

Goal Seek

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.

Scenarios

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.

Creating Scenarios

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

Certified Inventory and Warehouse Analytics Professional

Go back to Tutorial