Excel Advanced

(1)

Excel 2010 Courseware

Office 2010 Training

(2)

Microsoft Office Excel 2010 Advanced Courseware Written by Kelvin MacDonald

Published by Velsoft Training Materials Inc. Courseware Release Version 3.0

No part of this publication may be reproduced, transmitted, transcribed, stored in a retrieval system, or translated into any language or computer language, in any form or by any means, electronic, mechanical, magnetic, optical, chemical, manual, or otherwise, without the prior written permission of Velsoft Training Materials, Inc. except under the terms of a courseware site license agreement.

Trademark Notice

PowerPoint, Windows, Word, Microsoft are trademarks of Microsoft, Inc. Throughout this courseware title, trademark names are used. Rather than just put a trademark symbol in each occurrence of a trademarked name, we state we are using the names only in an editorial fashion and to the benefit of the trademark owner with no intention of infringement of the trademark.

Notice of Liability

The information in this courseware title is distributed on an ‘as is’ basis, without warranty. While every precaution has been taken in the preparation of this course, neither the authors nor Velsoft Training Materials, Inc. shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the instructions contained in this book or by the computer software and hardware products described in it.

Disclaimer

We make a sincere effort to ensure the accuracy of the material described herein; however, Velsoft Training Materials makes no warranty, expressed or implied, with respect to the quality, correctness, reliability, accuracy, or freedom from error of this document or the products it describes. Data used in examples and sample data files are intended to be fictional. Any resemblance to real persons or companies is entirely coincidental.

All information in this manual was correct at the time of writing. Velsoft is not affiliated with nor has any control over changes made to the product described in this manual. These include, but are not limited to, changes in the application’s color scheme, icon appearance and locations, addition or removal of program features, online templates, and help content. Velsoft reserves the right to make corrections to the courseware at any time and without notification.

Terms and conditions

Sample versions: If the version of courseware that you are viewing is marked as NOT FOR TRAINING, SAMPLE, or similar, then it is made available for content and style review only and cannot be used in any part of a training course. Sample versions may be shared but cannot be re-sold to a third party. For licensed users: This document may only be used under the terms of the license agreement from Velsoft Training Materials, Inc. Velsoft reserves the right to alter the licensing conditions at any time, without prior notice.

Velsoft Training Materials, Inc. 185 Provost Street

New Glasgow, Nova Scotia B2H 2P8

(3)

(PivotTables and PivotCharts) will be discussed at length. Following that, advanced concepts dealing with Excel functions, AutoFill lists, and consolidating and combining data will also be covered. The new Slicer features and PowerPivot add-on, two important tools for working with PivotTables and PivotCharts, are covered as well.

This manual was created using Microsoft Office 2010 Professional Plus. Our test machine was a 32-bit computer using Windows 7 Ultimate. If you are an instructor, you can use any version of Windows that is accessible to your students. Any feature specific to Windows 7 in this manual will be marked as such.

Occasionally, this manual may reference where certain keys are on the keyboard (such as Insert, Home, or Page Up). The directions are given based on a standard desktop keyboard that contains a separate number pad. Laptop keyboards may be different or have combined keys.

(7)

Prerequisites

This manual presumes that the user has a basic knowledge of Windows, including concepts such as using the mouse and keyboard. With respect to Excel, we also presume that the user has completed the Intermediate level of this course (or has equivalent knowledge) and is familiar with concepts such as:

 Using right-click menus

 Working with dialog boxes

 Worksheet organization (rows, columns, labels)

 Cutting, copying, pasting, selecting, and moving Excel data

 Basic cell data formatting tasks

 Working with cell references and cell ranges (named ranges)

 Manipulating multiple open worksheets and/or workbooks

 Creating and manipulating standard Excel data tables

 Using Excel functions

 Building formulas in Excel

 Working with external data sources

(8)

Section 1: Getting the Most from Your Data

In this section you will learn how to:

 Use automatic outlining

 Display and collapse levels

 Manually group data

 Create subtotals

 Describe scenarios

 Create a scenario

 Save multiple scenarios

 Merge scenarios together

 Create a scenario summary report

 Enable data analysis tools

 Identify available analysis and add-in tools

 Use a one or two input data table

 Use Goal Seek

 Generate reports and scenarios with Solver

 Change Solver values and manage constraints

 Choose a solving method

 Use Solver as a goal seek tool

(9)

Lesson 1.1: Outlining and Grouping Data

Sometimes, a worksheet can become very large and contain so much data that it becomes difficult to understand. Totals and subtotals that summarize the data can be lost in screen after screen of numbers. Thankfully, Excel 2010 has an excellent feature to help with this kind of problem.

Excel’s grouping and outlining features will allow you to collapse a large worksheet to show or print summary data. The grouping and outlining features can also expand the same worksheet to show data, or allow modification of the details. Outlining lets you manage the level of detail at which your worksheet will be presented.

(10)

Using Automatic Outlining

Excel’s automatic outlining feature makes outlining a worksheet fairly straightforward. Automatic outlining works best with numerical data organized into groups and sub groups by formulas or functions. The following worksheet, for example, contains monthly financial data for a business, organized into quarterly and yearly totals using formulas and the SUM function:

It can be difficult to discern quarterly and yearly totals at a glance because these figures are lost in with all of the other data.

To automatically outline this worksheet, click the arrow next to the Group button in the Outline group of the Data tab:

(11)

This action will display a menu with two options: Group and Auto Outline. If you click the Auto Outline button, the spreadsheet will be outlined automatically:

Here are the results of Excel’s automatic outline:

All of the original data is shown, as well as outline group indicators (the thick black lines that look like large brackets) and collapse buttons (marked with a minus sign). You can see that the quarters (sets of three months) are grouped together and summarized by Quarterly totals, and that each year has been grouped to be summarized by Yearly Totals. Notice also that the

columns Supplies, Wages, and Utilities have been grouped under Total Expenses, and that there is an overarching group of all columns under Profit.

(12)

In the image that follows, you can see the summary results of Excel’s automatic outline. The original information is still available in all of its detail, but it is now presented in a summary view, showing only the yearly profit totals. Of course, automatic outlines will differ from

worksheet to worksheet depending on your headings and data and the way they are organized.

We’ll look at how to expand and collapse levels in the next lesson.

To remove the outlining from your worksheet, click Data → Ungroup → Clear Outline:

Displaying and Collapsing Levels

Here is an outlined spreadsheet:

(13)

The view of the data in the preceding image is provided by the buttons numbered 1. Clicking on the number 2 buttons will expand the worksheet to the second level of detail for the rows, columns, or both as required:

This is the same worksheet after both number 2 buttons have been clicked. You can see that the worksheet rows and columns have been expanded to show another, secondary level of detail. You can now see rows with quarterly totals as well as yearly totals. You can also see columns with figures for Income and Total Expenses.

If you click on an expand button, marked with a plus sign (+), a specific section of the outlined worksheet corresponding to the button will be shown. Clicking the collapse (-) button will collapse the corresponding expanded section.

(14)

Remember that with outlines, only the data that is visible on your screen will be printed. This is a great way to print only the pertinent information from a large or complicated worksheet. You can expand and collapse the outlined worksheet with the numbered outline buttons, or with the expansion (+) and collapse (-) buttons, to reveal the level of detail that you want in your printed copy.

Grouping Data Manually

Automatic outlining may not be adequate for worksheets with non-numerical values or with no distinctive totals (from formulas or functions).

If automatic outlining does not, or cannot, organize your data in the way you want, you can manually group your data. Take the following worksheet as an example:

(15)

We would like to group the data according to teams and divisions, but if you use the Auto Outline option under the Group button, Excel displays the following alert:

Because the data contains no formulas or functions providing numeric totals, Excel cannot implement automatic outlining for the worksheet. You can, however, create your own

groupings by selecting the rows or columns that you want to group, and then clicking the Group button.

For example, if you select the members of Team 1, the Sharks (cells B4:B6), and click Data → Group, Excel 2010 will display the following box asking if you want to group by rows or columns:

If you select the Rows radio button and click OK, Excel will provide an outline indicator and a collapse/expand button for the new grouping.

(16)

You should also notice numbered buttons in the top left corner. These outline level buttons work the same as before. Clicking on the number 1 button will show the first level of detail, and clicking on the 2 button will show the second (increased) level of detail.

If you follow the same procedure for cells B8:B10 (select the cells and then click the Group button on the Data tab) and then for cells B2:B10, you will end up with a worksheet like this:

This image shows the fully detailed view. If you click the 2 button, you will see the following view of the data:

You can see that the individual player information for the first two teams has been collapsed from view.

(17)

Clicking the number 1 button will display the highest level view (lowest detail) of the manual groupings:

In the preceding image you can see that the entire first division grouping has been collapsed from view.

To remove manual groupings, select the rows or columns corresponding to the grouping you wish to remove and click the Ungroup button.

(18)

When you see the Ungroup dialog box, select the appropriate radio button (rows or columns) for what you are ungrouping, and click OK.

. The selected rows or columns will be ungrouped.

Creating Subtotals

Another kind of outlining technique in Excel 2010 is the Subtotals feature. If you have numeric data organized with clear column and row headings, you can use Excel to create automatic subtotals and grand totals for the data.

The following worksheet contains sales information for different products across geographical regions.

To use Excel’s subtotal feature, use your mouse to select the range of data you want to apply subtotals to, and click Data → Subtotal:

(19)

Before you click Subtotal, be sure to include the column labels when you make your selection, so Excel will be able to discern what numbers to total. For this example you could select cells A1:E10 and click the Subtotal button:

(20)

The drop list under the “At each change in:” heading gives you options as to the number of rows that will be totaled. (For columns, totals will be applied every time the labels change.)

In this example, the region column is selected, so with every change in the Region labels (from East to West, West to South, and so on) a subtotal will be applied.

The “Use function” drop list lets you choose from a list of functions including SUM, AVERAGE, COUNT, PRODUCT, and STDEV to apply to your data. The function you choose (normally SUM) will be used to calculate the subtotals:

In the “Add subtotal to” section, you can select check boxes for the columns you want totals for. You can apply totals to a single column or to multiple columns as long as they are covered by your selected range.

Finally, there are three check boxes at the bottom of the Subtotal dialog box that will allow you to:

 Replace any pre-existing subtotals

 Put page breaks between totaled groups so they can be printed on separate pages

(21)

If you choose to apply totals to each change in the Region column while applying the Sum function to the Total Sales column, and choose no page breaks, the resulting worksheet will look like this:

Notice that there is a subtotal for the Total Sales figures at every change in the Region value. There is also a grand total for the Total Sales column at the bottom of the data. You can also see that Excel has provided outlined levels, collapse/expand buttons, and numbered outline level buttons associated with the subtotals. These buttons and outlines work exactly as previously explained: the lower the number on the button you choose, the less detail will be shown.

If you want to remove these subtotals, you can select the range of data in question (A1:E14) and click Data → Subtotal to invoke the Subtotal dialog box. When you see the box, click the Remove All button:

(22)

Lesson 1.2: Exploring Scenarios

In business it can often be beneficial to explore multiple scenarios for a given situation. This allows you to see what combination of factors gives the best results; in other words, test

various scenarios. With Excel 2010, you can easily explore multiple scenarios based on the same data.

In this lesson you will learn what a scenario is and how to create one. You will also learn about scenario summary reports and how to save multiple scenarios based on a given situation.

What is a Scenario?

In Excel, a scenario can be described as a set of cell values that is saved and substituted into your worksheet at your convenience. If you have multiple scenarios saved, you can load

different scenarios into your worksheet and compare and contrast them to see which particular set of values gives the best results. Since the worksheet will be fully calculated according to the given set of scenario data being used, you can compare and contrast the results of one set of data (scenario) with another by simply switching between different scenarios. Used in this way, Excel scenarios provide a powerful tool for performing what-if analysis.

You might use scenarios to represent different budget options, or perhaps evaluate different financial forecasts. You can use Scenarios to compare different data projections based on any number of factors.

(23)

This is how it works. The data that makes up your scenario is saved with your workbook, but it remains hidden until you use the Scenario Manager to load the scenario data. All of the scenarios you create are just versions of the same worksheet layout, with each different scenario having different values for certain key cells.

Creating a Scenario

To create a scenario in Excel, begin with the worksheet that you want to add scenarios to. Then, click Data → What-If Analysis → Scenario Manager:

(24)

When you click on this option, the Scenario Manager dialog will appear:

To create a scenario, click the Add button in the upper right. This will display the Add Scenario dialog box:

(25)

To enter a scenario name, make sure that your cursor is in the “Scenario name” text box and type a name that describes the scenario you are creating.

Next, decide which cells you want to change with this scenario, and then put your cursor in the “Changing cells” textbox. Select the cells that will be changing with your mouse. (Remember that you can use the Ctrl key for selecting non adjacent cells.)

If the Add Scenario dialog box gets in the way when you are selecting cells, you can drag it to one side, or collapse it by clicking the small Collapse Dialog button:

In the following image, you can see the name for the scenario is Change Wages, and the Changing Cells are G2 to G9 (the Wages column in the worksheet):

You can also describe the scenario in the “Comment” area of the dialog box if you wish. You will also see two check boxes at the bottom of the dialog box: “Prevent changes,” and “Hide.” If you check the “Prevent changes” box, changes to this scenario will not be permitted if the

corresponding worksheet is protected.

(26)

Once you click OK, you will see the Scenario Values box.

Here you will see the references for the cells that you selected for your scenario. When you use this scenario in the future, the values you enter here will be loaded into the corresponding worksheet. (Remember, you can move between textboxes in a dialog box by pressing the Tab key.)

Notice that they are populated with the current worksheet data by default. If you want to save the current (actual) data as a scenario, you could just click OK at this point. Otherwise, you should enter the appropriate new value that you want for this scenario in each box.

(27)

Click OK to create the scenario. You will then see the Scenario Manager Box with your newly created scenario available in the “Scenarios” area.

In this dialog box, you can see the changing cells in the “Changing cells” area, and any comments in the “Comment” area. If there is an assortment of scenarios in the scenario

manager, just click the one that you want and click Show to see the results of the given scenario in the worksheet.

Saving Multiple Scenarios

To save multiple scenarios for a worksheet, click Data tab → What–if Analysis → Scenario Manager to display the Scenario Manager dialog. Then, use the Add button in the Scenario Manager to create as many scenarios as you require (following the process as described previously). All of the scenarios you create for a given worksheet will be available in the scenarios list in the Scenario Manager dialog.

(28)

In the following image, you can see four scenarios in the scenario list. These scenarios will be saved with this workbook when the workbook itself is saved. When you open the workbook, you can see the available scenarios by displaying the Scenario Manager.

To load a scenario into the worksheet, click to select it from the Scenarios list and click the Show button at the bottom of the dialog. To remove a scenario, click to select it in the list and then click Delete. Finally, to make changes to a scenario, select it, and then click Edit.

(29)

The Edit command will invoke the Edit Scenario dialog:

This dialog allows you to change the name, changing cells, cell values, and comments

associated with the selected scenario. (This is essentially that same dialog that is used in the process of creating a scenario.)

If you wish, you can have a different set of scenarios for each worksheet in your workbook. It is important to note that when you display the Scenario Manager, only scenarios for the currently active worksheet will be shown in the Scenarios list.

Merging Scenarios

There are times when it could be useful to bring together scenarios from different worksheets. For example, you could create a worksheet and then distribute copies to several co-workers. Each person could create their own scenario (their own opinion of how the values will change) and then send the worksheets back to you. If you merged all of the scenarios from the different worksheets into one worksheet, you could compare all of the scenarios with a simple click of your mouse.

To merge scenarios from different worksheets, first open up each Excel workbook that contains a different scenario. It is important to note that merging scenarios works best if each worksheet has the same design and data layout, and if the changing cells in each scenario are in the same

(30)

Here is an example.

The preceding worksheet contains two scenarios: “Lower Wages” and “Actual Values.” For both of these scenarios, the changing cells are the cells with data in the “Wages” column.

The following image shows a similar worksheet from a different workbook. This worksheet has only one scenario, called “Increase wages.” The important thing to note is that the changing cells for the “Increase wages” scenario are the same as the changing cells for the two scenarios in the first worksheet.

To merge the scenarios from the two different worksheets, you should first open both of the respective workbooks at the same time. Next, you should decide which worksheet will be the destination for the merged scenarios and which worksheet will be the source. For this example, we will use the worksheet with the “Increase wages” scenario (preceding image) as the

(31)

Now, make the destination worksheet the active worksheet and then display the Scenario Manager dialog by clicking Data → What-If Analysis → Scenario Manager. When the dialog appears, click the Merge button:

(32)

If you expand the drop list in the Merge Scenarios dialog you will see a list of the currently open workbooks. Select the workbook that contains the other scenarios and then select the

worksheet in that workbook that contains the specific scenario that you want to merge:

Once you choose the appropriate worksheet, click OK.

At this point, the Scenario Manager dialog for the active worksheet will appear. It will now contain the scenario(s) from the source worksheet that you chose in the Merge Scenarios dialog.

Now that the scenarios are merged into one worksheet, you can compare them easily. (You can repeat this process to merge additional scenarios from other open workbooks if you wish.)

(33)

Creating a Scenario Summary Report

A Scenario Summary report can be used to display and compare the scenarios stored with your worksheet. To create a summary report, open the worksheet containing the scenarios and click the Data tab. Next, click What–If Analysis → Scenario Manager:

When the Scenario Manager dialog appears, click the Summary button:

This will display the Scenario Summary box. Here you can choose to create a PivotTable (based on the scenario data) or a scenario summary by selecting the appropriate radio button. (You will learn more about PivotTables later in this manual.)

(34)

In this example, the Scenario Summary radio button is selected. The next step is to select the result cells that you want to be shown in the summary report. You can do this by selecting cells with your mouse on the worksheet, just as before. Finally, click OK to create the summary:

The scenario summary will appear on its own sheet. As you can see, it shows the changing cells and the results cells for the scenarios that are stored with your worksheet. The changing cells that have values different from the current values will be highlighted in grey. Notice the row of scenario names across the top of the columns to identify which scenario a given column of values belongs to. (The scenarios in the report shown above are: Current values, Lower Wages, and Actual Values.)

(35)

Lesson 1.3: Using Data Analysis Tools

Excel 2010 provides a rich assortment of analysis tools that you can use to explore and interpret your data. While there are plenty of built-in tools in Excel, there are also additional software files than can be added to Excel to extend its functionality even more. These additional software tools are known as add-ins.

Add-ins can come in the form of a single task-oriented tool like Solver (which we will look at later in this lesson), or they can consist of several tools combined in a pack, like Excel’s Analysis ToolPak, which provides an assortment of tools for analyzing scientific, statistical, or

engineering related data. Some add-ins come packaged with the Microsoft Office system, while others may be provided by third party vendors.

In this lesson, we’ll have a look at a few of the what-if analysis tools that are built into Excel 2010, like goal seek and one and two input data tables. We’ll also look at add-ins and how to enable them. We’ll also take a close look at the Solver data analysis add-in.

Enabling Data Analysis Tools

As mentioned above, there are some interesting data analysis tools provided as add-ins for Excel, such as the Analysis ToolPak and the Solver. In a general sense, add-ins can be described as small software modules or files that extend the functionality of the larger Office applications. The important thing to know is that these are not enabled in the Excel program when it is installed, as they often provide more obscure or mathematically advanced functionality than is required in a typical business environment. You must enable them manually from within Excel.

(36)

The first step in enabling add-Ins is to navigate to the add-in management section of the Excel Options window. To start, click File → Options:

When the Excel Options window opens, you will see a panel on the left side with a series of categories. Click Add-Ins:

(37)

When you choose this option, the Excel Options window should look something like this:

In the main viewing area, you will see a large scrollable list called Add-ins. This list is broken down into the following sections: Active Application Add-ins, Inactive Application Add-ins, Document Related Add-ins, and Disabled Application Add-ins. At the top of this list are the headings Name, Location, and Type. The corresponding name, location path, and add-in type, for each add-in are grouped in the list according to these headings.

(38)

If you click on any add-in in this list you will see the name of the add-in, the publisher, the location (file directory), and a brief description of the add-in in the area beneath the list. Here’s an example:

If you click the Manage menu near the bottom of the window, you will see the following options:

Here you can see two types of add-ins at the top of the list: COM Add-ins and Excel Add-ins. COM stands for component object model, a Microsoft technology that helps software components (like Microsoft Office programs) to communicate. For example, a form of COM technology is used to allow Word documents to link to data in Excel spreadsheets. Often a single COM add-in will be available for a number of Office applications.

For the purposes of this lesson, we are interested in the add-ins specifically for Excel. To manage the Excel add-ins, select it in the list, and click Go.

(39)

When you click Go, an Add-Ins dialog will appear and show a list of the available Excel add-ins:

If an add-in is enabled, it will have a checkmark next to its name in the list. In the image above, no Excel add-ins are enabled yet. If you want to see a brief description of an add-in, click it in the list. In the example above, the Solver add-in is selected and a description of the Solver appears near the bottom of the dialog.

To enable a particular add-in, click to place a checkmark in the appropriate checkbox and click OK. To enable the Solver add-in, for example, simply place a checkmark next to it the Add-Ins dialog and then click OK.

If you see a dialog stating that the add-in you are enabling is not currently installed on your computer, simply click the Yes button in the dialog to install it.

(40)

To make sure the Solver add-in is enabled, click the Data tab, and then look for Solver.

Similarly, if you check the box corresponding to the Analysis ToolPak, and then click OK, it will be enabled in Excel as well.

In the following image, you can see that the Analysis option is now available on the Data tab.

An Overview of Excel’s Analysis Tools

As mentioned near the beginning of this lesson, the Analysis ToolPak add-in provides an assortment of tools for analyzing scientific, statistical, or engineering related data. Many of these features are for scientific or statistical data analysis, so a comprehensive exploration of the use of these tools is beyond the scope of this manual. That being said, here is a quick and gentle overview of some of the Analysis ToolPak features.

(41)

To enable the Analysis ToolPak, make sure there is a check mark in the appropriate checkbox in the Add-Ins dialog:

If the Analysis ToolPak is enabled, there will now be a corresponding option (Data Analysis button) for it on the Data tab:

If you click the Data Analysis button on the Data tab, a dialog with a scrollable list of tools will appear.

(42)

The tools available in the list are intended for quite specific and advanced data analysis techniques. To start one of the tools, simply click it in the list to select it and then click OK.

The first tool listed is called “Anova: Single factor.” (Anova is short for analysis of variance.) This tool can be used to examine if the means (averages) of samples (groups of experimental data from a population) are different in a significant way. Tests like these are often used to examine the significance of the results in scientific studies.

Generally in a study, an independent variable will be changed by the experimenters to see if it has a result on the dependent variable. This is often done by comparing the results for different groups of data (samples), resulting from tests at different levels of the independent variable. For basic experiments with only two samples, you could use a basic test like a T test (also included in the Analysis ToolPak) to compare the averages of the samples. For more complex experiment (with more than two samples), an Anova test could be used.

 A single factor Anova can be used to compare multiple samples, based on a single independent variable.

 A two factor Anova test can be used to compare multiple samples, based on two independent variables

(43)

It is often useful to know if two variables are related in some way, and a correlation test can help to determine this. Think of the word correlation as “co-relation.” If there is a strong correlation between two sets of data, it generally means that they trend or change in concert with each other. If there is a significant correlation in your data, you may be able to make predictions based on this relationship.

For example, if you sell sunglasses, you may be curious as to the relationship between the weather (amount of sun or temperature) and the sales of your product. Here is an example worksheet.

To invoke the Data Analysis dialog, click Data → Data Analysis. When the dialog appears, select the Correlation option and click OK.

(44)

In the following example, the range of data to be analyzed is B1 to C14, with the data grouped in columns and column labels included. Furthermore, the results will be displayed on a new worksheet.

Once you are ready, just click OK to generate the analysis results. The following image shows the results as displayed in a new worksheet.

The important value to take note of is in cell B3. This is the correlation value. This value will be between -1 and +1. If the value is +1 it means that there is a perfect correlation. In other words, the two sets of data rise in perfect concert. If the correlation value is a -1, it means that just as one set of data rises, the other set of data shrinks. A value of 0 would indicate no relationship whatsoever. As you can see in this example, a value of .915834314 (almost 1) indicates a very strong positive relationship between the hours of sunlight per day and the sales of pairs of sunglasses per day.

(45)

Another interesting tool in the Analysis ToolPak is the Random Number Generator.

You can use random number generation to simulate data or random behavior. For example, if you wanted to simulate a series of coin tosses for a statistical experiment, you would select Random Number Generation from the list in the Analysis Tools dialog and click OK. This will display the Random Number Generation dialog.

To simulate a coin toss, enter the number of variables (1 for one coin) and then enter the number of random numbers (the amount of flips of the coin) in the appropriate fields. You must also choose the correct distribution (which is the Bernoulli distribution for the case of flipping a coin) from the drop list provided. You must also enter a p value (a value between 0 and 1), which represents the probability of getting a heads or a tails. Since we want to simulate a fair coin we will choose .5 (50/50 chance) for the p value. For the purposes of this example, it is not necessary to enter anything in the Random Seed box. Finally, you can choose to put the

(46)

The following image shows the results of the coin toss in cells A1 through to A10. Think of a 1 as a head and a 0 as a tail:

(47)

You can use this feature to create a percentile rank of your chosen data values. For example, here is a batch of random data that could represent scores on a hypothetical test.

To rank these scores we would select “Rank and Percentile” from the list in the Data Analysis dialog and click OK. This action will display the Rank and Percentile dialog:

(48)

example, the New Worksheet radio button is selected for the destination. When you are ready, just click OK to proceed.

In the results, you can see separate columns labeled Point, Column1, Rank, and Percent. The Point column provides values for the position of the given test score in the original data column. Column1 lists the ranked test scores, the Rank column shows the rank of the

corresponding test score, and Percent column shows the percentage of scores that are below that score.

As a final note on the tools in the Analysis ToolPak, let’s discuss how to get a bit of help with a given tool if you need it.

(49)

If you see a tool in the Data Analysis dialog that you would like to know more about, click the Help button on the right side of the dialog:

Then an Excel help window will open with a list of items corresponding to the tools in the Data Analysis dialog. To get help or information on an item in this list, click on the name of the item and it will expand to show a brief description of the given item and what it is used for:

(50)

By default, Excel will try to connect to the Internet to gather available information from Office.com. If you do not have an Internet connection, help will retrieve information that is stored locally on your computer.

An Overview of the Available Add-In Tools

As mentioned earlier, the available Excel 2010 add-ins are:

 Analysis ToolPak

 Analysis ToolPak – VBA

 Euro Currency

 Solver Add-in

Now that we have looked at some of the tools in the Analysis ToolPak, it is time to turn our attention to the remaining Excel add-ins.

The Analysis ToolPak – VBA add-in can be enabled just like any other Excel add-in, but you will not see an option for it in any of the tabs.

The Analysis ToolPak-VBA add-in should not be confused with the Analysis ToolPak that we just looked at. VBA stands for Visual Basic for Applications. It is a programming tool for building functions and automating tasks in Microsoft Office applications. The ToolPak-VBA add-in provides the user with the ability to call (use) the functions in the Analysis ToolPak from within VBA programs (like macros). This is not the same as using the ToolPak functions manually from within your worksheet, which is why the ToolPak-VBA add-in doesn’t appear as on the Data tab. It is primarily for behind-the-scenes work by providing VBA programmers with more

functionality. If you only intend on using the ToolPak features manually as shown in the previous pages, there is no need to install the ToolPak VBA add-in.

(51)

When this add-in is enabled, currency commands will appear on the Formulas tab:

These tools can be used to convert various European currencies to Euros or other European currencies. Take the following worksheet as an example.

(52)

Here we have a column of values that represent Austrian Schillings. To convert this column of Schillings to Euros, simply select the range of values, and then choose Formulas → Euro Conversion:

(53)

At this point, a Euro Conversion dialog box will appear. In the dialog, you must specify the range of source data (A2:A13 in this case) and the destination range for the conversion (B2:B13). Next, you can use the drop lists provided to specify the source currency (Schillings) and the destination currency (Euros).

(54)

You can also format cells so that Excel recognizes the cell values as Euro currency, much the same as if you formatted the cells as standard ($) currency. To do this, start by selecting a range of cell values.

Next, choose Formulas → Euro Formatting.

Here is the result:

Just as with standard currency formatting, if you change the value in the cell, the Euro currency formatting still remains with the cell.

As you can see, this add-in isn’t really for data analysis, but it is worth mentioning these tools in this overview as they can be useful if you work with European currencies.

(55)

The final Excel add-in is Solver:

When the Solver is enabled, it can be accessed by a button on the Data tab:

Solver is a fairly advanced tool for examining complicated what-if scenarios. Essentially, it is used to minimize or maximize systems of equations based on a set of constraints. We won’t get into too much detail about it here because we will explore it in depth at the end of this lesson.

Using a One or Two Input Data Table

You can use Excel data tables to test how your formula results will vary when the data that the formula operates on changes. You do this by specifying a series of hypothetical values that Excel will use to evaluate the formulas with. This allows you to then view the results of the

evaluations and compare the results for the different data inputs. Data tables save you the trouble of entering a series of input values into a worksheet and recording each recalculation of the worksheet for each new input that you enter. When you use a data table, Excel will

substitute a range of values into the worksheet formulas for you, and tabulate the results so they can be viewed easily.

In Excel, you can create a single input data table or a two input data table. A single input table will substitute a range of values as a single variable in as many formulas as you like. With a two input data table, you can specify ranges for two input variables, but these input variables can only be applied to one formula.

(56)

The following example involves a hypothetical consulting firm. Our first goal is to examine the effect of the number of clients on the total profit, expenses, and income.

Currently, the firm has 10 clients. The values for Wages, Total Costs, Total Expenses, and Profit are all calculated by formulas dependent on the number of clients the firm can retain. For example, you might like to see what the profit formula and other formulas in the worksheet would produce for a varying number of clients. To manage this, we can use a single input data table.

(57)

Keep in mind that there are some strict rules you should follow when building your data table to help ensure that it works correctly. First, list the input values that you want to try in a row or column of adjacent cells. For this example, we’ll use a column of input values. The column will be named Hypothetical Clients and will hold different values for our variable (the number of clients).

In the row just above your input data (row 4 in this example), enter the cell references to the formulas that you want to evaluate. Make sure you enter the references starting one cell to the right of the column of input values. In this case, we want references to cells C5, C13, and C15 placed in the row just above our input values (row 4), and starting one cell over to the right from the input column (in column F).

(58)

The input variables are in the cell range E5:E19. Cell F4 contains the reference =C5, cell G4 contains the reference =C13, and cell H4 contains the reference =C15. These references point to the Total Income, Total Expenses, and Profit formulas respectively.

It is a good idea to label your new columns appropriately, so you can clearly understand the data table results. In the preceding example, the new columns are labeled Total Income, Total Expenses, and Total Profit. Now, let’s have a closer look at the structure.

Notice that the formula references (in cells F4:H4) are in a row just above, and one cell to the right of the first input variable (in E5). The data table is now ready. The input variables are listed in the “Hypothetical Clients” column and the formula references are one row above and one cell to the right with respect to the first input value. Also, all of the elements in the data table are clearly identified.

The next step is to select the full range of cells from the data table formed by the input variables and the formula references. In this example, the range is E4:H19:

(59)

After you have the correct cells selected, choose Data → What-If Analysis → Data Table:

This action will display the Data Table dialog box:

At this point in the process, it is important to remember the reasoning behind our choice of input variables: we want to see our formula results over a range of different values for the number of clients. It is now time to tell Excel that we will be using the number of clients as the variable for the data table.

Looking back to the section of the worksheet that contains the formulas and original data, we see that the number of clients is stored in cell C3.

(60)

The next step is to enter this cell reference into the Data Table dialog. Because the values that we are substituting for the number of clients are arranged in a column, the cell reference C3 (the original location of the number of clients) should be entered into the “Column input cell” box in the dialog.

Then, click OK in the dialog to see your values:

You can now see at a glance how the 15 different values in the Hypothetical Clients column influence the results for total income, total expenses, and total profit.

(61)

If you change the values in the Hypothetical Clients column, the data table will automatically recalculate in accordance with the new values. You can also apply formatting styles, borders, shading, and other enhancements to the data table in the same way that you could any other area of your worksheet:

Now, what if you want to consider the effects of two input variables on a single formula? For example, what would be the effect of both the number of clients and the fee per client on profit? To find out, you can use a two input data table.

(62)

To start, choose an empty cell, and then enter a reference to the formula you want to examine. Using the same consulting firm example as before, we will choose cell E2 and enter the

reference =C15 to refer to the cell containing the profit formula:

The next step is to create two variable ranges. One range will be a column starting in the cell immediately beneath the cell that contains the formula reference. The other range will be a row starting on the immediate right of the cell that contains the formula reference. This means that the formula reference cell (E2) will be at the upper left corner of the two input data table. For the column of input variables, we will again use the number of clients. For the row of input variables, we will use the fee per client. (The formula for the Profit values is indirectly

(63)

After entering some hypothetical values for the number of clients in cells E3:E15 and some different client fee values in cells F2:J2, we will end up with a worksheet that looks something like the following:

(In the preceding picture, shading and labels have been added to the data table for organization and clarity.)

(64)

Once the table is set up, the next step is to select the range of cells covered by the new data table and invoke the Data Table dialog (Data → What-If Analysis → Data Table):

(65)

This time, we have two input variables into the Data Table dialog; one corresponding to the row of client fees, and one corresponding to the Number of Clients column:

After the correct cell references are entered, it is simply a matter of clicking OK in the dialog box to complete the table. This time, the formula referenced in E2 will be evaluated once for every combination of Clients and Client Fees that exists in the table:

Now the data table contains speculative profit values based on a varying number of clients and varying fees per client.

(66)

Using Goal Seek

Goal Seek is another useful what-if analysis tool built into Excel. With Goal Seek, Excel can help you find a value for a specified cell that makes a given worksheet formula equal to a value that you define. In other words, you can set a formula to a value (goal) that you would like to attain, and then specify one of the cells that the formula references as a cell that Excel should adjust in order to reach the goal.

Take the following small worksheet as an example.

In the formula bar, you can see that the formula for total profit is the Total Income (B4) minus the total cost (B7). It should be noted that cells B4 and B7 themselves contain formulas. The formula in cell B4 is =B2*B3 (units sold multiplied by price per unit). The formula in cell B7 is =(B5*B2)+B6 (materials per unit multiplied by units sold) plus the labor to make the units. The following image shows the worksheet with some sample data.

Clearly, at current production levels the company is losing money. How would you figure out how many units to manufacture to break even or make a profit? One solution is to use goal

(67)

To begin, select cell B8 (the cell with the profit formula) and then invoke the Goal Seek dialog by choosing Data → What-If Analysis → Goal Seek:

When the dialog box appears, B8 is already entered in the “Set cell” box because that is the cell that you selected just prior to invoking Goal Seek:

In the “To value” box enter 0, since a profit of 0 is the breakeven point). In the “By changing cell” field enter B2 (the number of units required).

(68)

When everything is set up, the Goal Seek dialog looks like the following:

When you click OK, Goal Seek will find a value for cell B2 (units sold) that will make the profit formula (B8) equal to 0 (breakeven).

The Goal Seek Status dialog reports that a solution has been found, and you can see the value 300 in cell B2. This means that if all other variables remain unchanged, you must sell 300 units to break even. At this point, you can click Cancel to restore the original worksheet values, or click OK to enter the goal seek solution values into the worksheet.

You can just as easily use goal seek to find the price per unit, materials per unit, or labor that would make the worksheet break even. You could also set a specific level of profit for your goal by entering a value of your choice in the “To value” field of the Goal Seek dialog.

(69)

The following example image shows how goal seek evaluates the price per unit required to make 1000 dollars in profit with 150 units sold.

(70)

Lesson 1.4: Using Solver

Sometimes, when dealing with more complex problems, tools like data tables or Goal Seek cannot provide the kind of forecast or data analysis you are looking for. In this type of situation, Solver might be able to help.

Solver is a data analysis tool that is designed to optimize systems of equations (formulas) that are subject to specific constraints. Solver can be used to find optimal solutions for linear programming problems involving multiple equations and multiple unknowns. For example, an optimal solution might be one that maximizes profit, or it could be one that minimizes costs. Basically, the optimal solution will depend on the context of the situation (the constraints and formulas) and what you are looking for (the objective).

If you are trying to solve a complex problem, Solver will require certain information to work correctly. You will have to designate one or more formulas that reference the unknowns you want to solve for, and you will have to define constraints that model the given situation. Really, the best way to get an idea of how Solver works is to see it used in an example. Let’s get

started!

Understanding Solver

First, since Solver is an Excel add-in, you should make sure that it is enabled. To do this click the Data tab. If Solver is enabled, you will see it on the Data tab:

(If you need more information on how to enable Solver, refer to the “Enabling Data Analysis Tools” concept at the beginning of this lesson.)

(71)

In order to use Solver, you must set up the worksheet correctly. This requires a solid understanding of the problem you are trying to solve. The following example involves a business that assembles and sells furniture. The business sells two types of tables: one made from maple and another made from mahogany. The maple table is of lower quality than the other table, but the price is reasonable. The mahogany table is of higher quality and is made from more expensive wood, and so it has a higher price.

From the worksheet, you can see that it takes three hours of rough carpentry to assemble the maple table and four hours of rough carpentry to assemble the mahogany table. Once the table is assembled, it takes one hour of finish work for the maple table and two hours of finish work for the mahogany table.

The goal is to find out how many of each type of table we should make to maximize our weekly total revenue and what the maximum total revenue would be. To complicate matters, the business employs one part time finish carpenter who is only available for 30 hours of finish work per week, and two rough carpenters that are available for a combined 80 hours of rough carpentry a week. Also, the lumber yard that supplies the business with mahogany can only supply enough for 10 tables a week.

This means that the total finish work hours used must be less than or equal to 30 hours for the week, and the total rough work must be less than or equal to 80 hours. Furthermore, the number of mahogany tables we can make in a week must be less than or equal to 10. These constitute the constraints of the problem.

(72)

The following worksheet is the same as the previous one, except that the cell formulas are now visible. Examine the cell references in the formulas carefully.

Cells B4 and C4 hold the number of rough carpentry and finish work hours required to build a maple (less expensive) table. Similarly, cells B5 and C5 contain the number of rough work and finish work hours required to assemble a mahogany table. The revenues from the maple tables can be calculated by multiplying the number of tables assembled by the sale price. This is what the formula in F4 does. The revenues from the mahogany tables are calculated by a similar formula in cell F5.

Notice that in both cases, the revenue formulas depend on the number of tables assembled (cells D4 and D5). Because of this, the total revenue formula (F6) is indirectly dependent on D4 and D5.

The formula we want to optimize (also known as the objective formula) represents the total revenue (F6). The cells we will change to maximize the total revenue formula represent the quantities of each type of table assembled (D4 for maple and D5 for mahogany).

The constraints for this problem are shown in the lower right area. It is not absolutely necessary to label the constraints as they are shown here, but clearly identifying them on the worksheet helps when entering the constraints in Solver. We have specified that:

 The rough work hours are to be less than or equal to 80

 The finish work hours are to be less than or equal to 30

 The number of mahogany tables that can be assembled is less than or equal to 10

 The number of each type of table assembled has to be greater than or equal to 0 This last constraint may seem obvious and silly, but it is important to include it so the Solver knows that using negative values in the changing cells is not an option when optimizing the objective formula.

(73)

To summarize, in order to use Solver you must have a formula to optimize (called the objective formula) and you must have cells that can be changed to optimize the objective. The cells to be changed should be precedents to the objective formula; that is, the calculation of the objective formula should depend on results in the cells to be changed. If constraints are involved, the formulas to be subjected to the constraints should also be dependent on the changing cells.

In the preceding worksheet, you can see that cells G4 and H4 contain formulas that will be subject to the constraints. Cell F6 contains the objective formula and cells D4 and D5 are the changing cells (the numbers of each type of table that will be made). You should notice that the formulas in cells G4, H4, and F6, are all dependent on the changing cells (either directly or indirectly).

Note: It is assumed throughout that there is enough demand to ensure that every table that is made will be sold.

(74)

This will invoke the Solver Parameters dialog:

Place your cursor in the “Set Objective” text box and click on the worksheet cell that contains your objective formula (cell F6 from the preceding worksheet).

Since we want to find the maximum total revenue, select the “Max” radio button in the dialog. To enter the changing cells, place your cursor in the text area under the “By Changing Variable Cells” heading, and select the appropriate cells from the worksheet. For the problem shown

(75)

At this stage, the Solver dialog should look like this:

The next step is to add the constraints by clicking the Add button to the right of the large white Constraints area. This will display the Add Constraint box:

To add the first constraint, place the cursor in the Cell Reference box and select the cell with the formula you want to constrain. In this particular example, cell G4 is selected, which contains the formula for calculating the total finish work used. Next, click in the Constraint box and click the cell that contains the appropriate constraint value. Here, we chose cell H10, which contains the value 30. Next, use the drop down list in the center to specify the type of relationship required between the two cells. In this case, the constraint reads G4 <= H10; that is, total finish work hours should be less than or equal to 30.

(76)

Click OK to enter the constraint and close the Add Constraint dialog. The Solver Parameters dialog now looks like this:

Next, we will follow the same process to enter the cell references for the rest of the constraints:

 Total rough work hours less than or equal to 80: (H4 <= H9)

 Number of mahogany tables less than or equal to 10: (D5 <= H11)

 Number of mahogany tables greater or equal to 0: (D5 >= H12)

(77)

Here is the resulting Solver Parameters dialog:

Here is the original worksheet with formulas shown:

Examine the worksheet carefully so that you understand the relationships between the objective, the changing cells, and the constraints as specified in the Solver Parameters dialog. To implement the Solver, click the Solve button at the bottom of the dialog.

(78)

You will now see the Solver Results box:

If the Solver Results dialog reports that a solution has been found, the values that maximize the total profit (the solution) will now be visible in the changing cells in the worksheet (D4 and D5). Moreover, the value of the maximum profit will now be visible in the target cell (F6). The “Keep Solver Solution” radio button will be selected by default. If you click OK, the new (optimum) values will remain in the worksheet.

If you select the “Restore Original Values” radio button and click OK, the solutions that the Solver found will not be entered into the worksheet and the original values will be retained. The following image shows the worksheet after the Solver solution has been implemented. If 10 maple tables and 10 mahogany tables are assembled, the total revenue will be maximized at 17500.

(79)

Generating Reports and Scenarios with Solver

In the Solver Results dialog, you also have the option to save the results as a scenario that you can name and reload into the worksheet at a later date. (Refer to Lesson 1.2 for more

information on scenarios.) To do this, click the Save Scenario button in the Solver Results dialog. You can also select one or more report types from the list at the right of the Solver Results dialog. Just click on a report type to select it and Excel will generate it for you. These formatted reports will be generated on separate worksheets.

Now let’s look at a Limits report based on the Solver solution. (It is generated on a separate worksheet if you select Limits from the report list in the Solver Results dialog before you click OK.) Here is what the report looks like for this case:

Changing Solver Values

(80)

Solver Parameters dialog, it will be populated with the same cell references and constraints as before.

When we run the Solver with the new price for a maple table entered into the worksheet, the Solver finds a new solution to maximize profits.

This time, we should assemble 20 maple tables and 5 mahogany tables, for a total revenue of 21000 dollars.

(81)

Managing Solver Constraints

Let’s have a look at a few more details about using Solver. In the Solver Parameters dialog, you will see a series of buttons next to the Constraints area:

Here is what each command does.

 If you want to add a new constraint, click Add.

 If you want to change a constraint, select it and click Change.

 If you want to delete a constraint, select it and click Delete.

 Reset All will clear all of the constraints, variables, and options in the Solver Parameters dialog

Choosing a Solving Method

You will also notice an option to select a solving method in the Solver Parameters dialog.

To choose a method, simply select it from the drop list. The default choice is “GRG Nonlinear.” To understand these solving method options, you have to know a little something about formulas. Let’s start with one of the formulas in our furniture company example:

C4*D4+C5*D5. This formula is using just basic multiplication and addition so it is a linear formula. In other words, if you graphed this formula it would basically look like a straight line. A formula like this: 3*(POWER(H11,3))+3 is not a linear formula. This is because we have a value

(82)

If you use Solver with non-linear formulas (for example, formulas with exponents), use the “GRG Nonlinear” solving method.

If you use Solver with linear formulas, we recommend that you use the “Simplex LP” solving method.

Using Solver as a Goal Seek Tool

Finally, you can also use Solver as a kind of advanced goal-seek tool. As an example, have a look at the following worksheet with formulas displayed.

Suppose you want to adjust the number of units sold, the price per unit, and the labor costs so that we generate a profit of $1000. How would you do this?

One way to solve this problem is by using the Solver as a goal seek tool. To begin, you just display the Solver Parameters dialog in the usual way (Data → Solver):