supply chain management on excel

GUIDE TO MICROSOFT® EXCEL’S DATA ANALYSIS TOOLS

1. What Are the Data Analysis Tools? Excel includes a collection of 19 data analysis tools. These provide analysis capabilities beyond Excel’s inherent capabilities. One of them provides Fourier analysis for engineering applications. The remaining 18 provide statistical analyses. A discussion of the differences between these 18 statistical analysis tools and Excel’s 80 built-in statistical functions begins on the first page of guide to Excel’s Statistical Functions. 2. Where Can I Find the Data Analysis Tools? The data analysis tools are available in the component called the Analysis ToolPak that is an add-in program. Add-in programs are developed by organizations other than Microsoft and can be added to Excel to increase its capabilities. Microsoft includes a number of add-ins, such as the Analysis ToolPak, with the Excel software it distributes. These must be added in to Excel before they can be used within it. 3. Are the Data Analysis Tools Available on the Computer I am Using? The level of availability of the data analysis tools depends on the specific computer you are using. There are three possibilities. a. The first possibility is that the Analysis ToolPak (1) has been installed and (2) has

been added to the Tools pull-down menu. This means the data analysis tools are ready to use. To determine if this is your situation, click on Tools from the menu bar. If there is an entry usually near the bottom of the subsequent pull-down menu labeled Data Analysis…, the tools are ready to use. If it is not present, continue to the next possibility.

b. The second possibility is that the Analysis ToolPak (1) has been installed but (2) has

not been added to the Tools menu. You must then add it to the menu. To determine if this is your situation and to add it to the menu, click on Tools from the menu bar. Then click on Add-ins… from the Tools pull-down menu. If the menu includes the selection Analysis ToolPak in the subsequent dialog box, you should click on the check box to its left to add a check mark to the box. (Note you are to add Analysis ToolPak, not Analysis ToolPak- VBA.) Next click on OK and the Analysis ToolPak will be added to the Tools menu and ready to use. If Analysis ToolPak is not an option on the add-ins dialog box, continue to the third possibility. Usually this will not be true if you are using Excel 2000 but could be if you are using Excel 97.

c. The third possibility is that the Analysis ToolPak (1) has not been installed and (2) has not been added. You must then first install it and second add it to the Tools menu. If this is your situation, you will need to exit from Excel and all other application programs and return to your computer’s opening screen. Get your original diskettes or CD-ROM’s for Microsoft Office (or just Excel) and run the Setup program for it. Put the first CD or first diskette for your Microsoft Office software in the appropriate drive. (1) Click on the Start command button and click on Run from the subsequent menu.

Excel’s Data Analysis Tools 2

(2) The Run dialog box will appear on the screen. Assuming your CD or diskette is in drive D enter D:\SETUP in the text box and click on OK. (You may next get a dialog box indicating the Shortcut Bar is running. If so, click on OK.) (3) The next dialog box will be labeled the Microsoft Office—Set Up. Click on the Add/Remove command button. (4) The next dialog box is labeled Microsoft Office—Maintenance. Click to put a check mark in the Excel checkbox and to highlight Excel. Click on the Change Option button. (5) The next dialog box is labeled Microsoft Office—Microsoft Excel. Click on the Add Ins button. Click on Change Option. (6) The next dialog box is labeled Microsoft Office—Add-Ins. Click to put a check mark in the Analysis ToolPak (not Analysis ToolPak—VBA) check box and to highlight Analysis ToolPak. Click on OK. The installation program will now install the Analysis ToolPak. You can then start Excel. You will next need to add it to the Tools menu. Select Tools, then Add-Ins and put a check mark in the Analysis ToolPak check box and select OK. The Data Analysis Tools will now be available from the Tools pull-down menu.

4. How Do I Use the Data Analysis Tools? Once you have the Analysis ToolPak installed and it has been added to the Tools pull-down menu, you are ready to use these analysis tools. First click on Tools from the menu bar and then click on Data Analysis on the subsequent pull- down menu. The result will be the Data Analysis dialog box with a scrolling list of the 19 data analysis tools. You then click on the name of the tool you wish to use and click on OK. A dialog box will then appear which allows you to enter the input range, output options and any other options required for the tool you have selected. If you are uncertain of an entry for a tool’s dialog box, first click on Help (this is the Help in the tool’s dialog box, not the Help in the prior Data Analysis dialog box with the scrolling list of the 19 tools). Once the tool’s dialog box is complete, click on OK and the output will appear. 5. For What Analyses Are the Data Analysis Tools Used? A presentation of the uses of the data analysis tools is perhaps most helpful if it is given by category of analysis. Accordingly, the following list of the 18 statistical tools is by the type of statistical analysis each supports. The six categories used are Descriptive Statistics, Sampling, Hypothesis Testing, Analysis of Variance, Regression and Correlation, and Time Series Forecasting. The order of presentation of the six categories is the order of a typical business statistics textbook. Within each category the tools are listed in the order they appear in this manual. The entry for each of the 18 statistical tools provides its name and its purpose. Further description of these is available through the Excel help facility. To access it, select Tools from the menu bar, Data Analysis

Excel’s Data Analysis Tools 3

from the subsequent pull-down menu, select the tool you wish to use from the resulting scrolling list, click on OK and click on Help in the next dialog box. DESCRIPTIVE STATISTICS

Histogram—determines and graphs individual and cumulative frequencies for a one variable data set. Descriptive Statistics—generates a report of the values for 16 descriptive statistics such as the mean, median, mode, standard deviation, range, skewness and kurtosis for a one variable data set. Rank and Percentile—produces a table of the ordinal and percentage rank of each value in a one variable data set.

HYPOTHESIS TESTING

t-Test: Two Sample Assuming Unequal Variances—performs a t-test on two independent samples to determine if the difference in the two population means is equal to a specified value (such as zero). This test is conducted under the condition that the two population variances are not known to be equal. t-Test: Two Sample Assuming Equal Variances—performs a t-test on two independent samples to determine if the difference in the two population means is equal to a specified value (such as zero). This test is conducted under the condition that the two unknown population variances are known to be equal. z-Test: Two Sample for Means—performs a z-test on two independent samples to determine if the difference in the two population means is equal to a specified value (such as zero). This test is conducted under the condition that the values of the two population variances are known, or the sample sizes are large so the normal distribution can be used to approximate the t distribution. t-Test: Paired Two Sample for Means—performs a t-test on two paired (matched) samples to determine if the mean of the differences between the two population is equal to a specified values (such as zero). F-Test: Two-Sample for Variances—performs an F-test on two independent samples to determine if two population variances equal.

REGRESSION AND CORRELATION

Excel’s Data Analysis Tools 4

Covariance—creates a table of covariance values for all possible pairs of 2 or more independent variables. Correlation—creates a table of correlation coefficients for all possible pairs of 2 or more independent variables. Regression—performs a simple linear regression analysis or a multiple regression analysis with up to 16 independent variables.

TIME SERIES FORECASTING

Moving Average—projects a time series based on the moving averages smoothing method. Exponential Smoothing—projects a time series based on the simple exponential smoothing method.