Statistics for Manager Using Microsoft Excel - DOC by oht14582


Statistics for Manager Using Microsoft Excel document sample

More Info
									                         Installing and Using the Bootstrap Excel Add-in

                                            Software for

                                   Introductory Econometrics
                            Humberto Barreto and Frank M. Howland
                               (765) 361–6315 and (765) 361–6317

    This software was written and designed for teaching purposes. It has been tested
    on several examples but not on a wide variety of data sets. For mission-critical
    projects, always check the results with full-fledged statistical programs.

The Bootstrap add-in is Bootstrap.xla (on the CD-ROM, it is in BasicTools\ExcelAdd-Ins\Bootstrap).


This document describes how to install and use the Excel add-in Bootstrap.xla to run a bootstrap
simulation of one or two cells in an Excel workbook.


Microsoft offers the following description of an Excel add-in:

       Add-ins are programs that add optional commands and features to Microsoft
       Excel. Before you can use an add-in, you must install it on your computer and
       then load it in Microsoft Excel. Add-ins (*.xla files) are installed by default in the
       Library folder in the Microsoft Excel folder. Loading an add-in makes the feature
       available in Microsoft Excel and adds any associated commands to the
       appropriate menus. [Microsoft Excel Help, add-ins, overview]

Thus, to install an add-in is to have an add-in file (*.xla) in the Library folder of your hard drive.
To load it, you must complete an additional step using the Add-In Manager. Fortunately, you
need to do this only once.

Step 1: Installing the Bootstrap.xla file

If you are accessing the Bootstrap.xla add-in from a CD-ROM, place the CD in your computer.
If accessing from a network server, make sure you can read from the appropriate network drive.

If you do not have CD-ROM or network access to the Bootstrap.xla add-in, download it by
visiting <>. Download the Bootstrap.xla file directly to the
appropriate add-ins folder on your hard drive (usually, C:/Program
Files/MicrosoftOffice/Office/Library), or move it there after downloading to your hard drive.
7a7213d0-a031-4a71-92f4-542d8cd2ff9a.doc               Page 1 of 8
Step 2: Loading The Bootstrap.xla add-in

Once the Bootstrap.xla file is accessible, launch Excel and use the Add-
In Manager to load the Bootstrap add-in. First, open the Add-In
Manager by clicking on the Tools menu item and selecting Add-Ins.

If the Bootstrap add-in is not listed in the Add-Ins scroll box (as in the example above), click the
Browse (or Select) button, navigate to the Bootstrap.xla file on the CD-ROM or network drive,
select it, and click OK.                                                        Mac Note: Some versions of
                                                                                 OfficeX report, ―Unable to Copy
                                                                                 add-in to the Add-ins folder.‖
                                                                                 This is a bug. The add-in really
                                                                                 is there. Simply quit Excel, then
                                                                                 restart it, return to the Add-In
                                                                                 Manager, and continue following
                                                                                 the instructions.
Click OK if you are asked to write the Bootstrap.xla file to the Addins (or Library) folder. The
Add-In Manager dialog box will now list the Bootstrap add-in.

The Add-In Manager lists all of the installed add-ins, and those with checkmarks are also loaded.
Microsoft offers the following advice, ―To conserve memory, unload add-ins you do not use
often. Unloading an add-in removes its features and commands from Microsoft Excel, but the
add-in program remains on your computer so you can easily load it again.‖ [Microsoft Excel
Help, add-ins, overview]

Make sure to select the check box next to the Bootstrap add-in and click OK.
Excel will load the Bootstrap.xla file and notify you of successful installation with the following

7a7213d0-a031-4a71-92f4-542d8cd2ff9a.doc              Page 2 of 8

Using the Bootstrap add-in is easy, but it requires a little planning. Suppose you have an Excel
workbook with data, which we will call the original sample, and you have computed a sample
statistic (e.g., the average or a regression coefficient). To use the bootstrap add-in, you have to
create a place in the workbook where the samples taken from the original sample can be placed
and the statistic of interest computed. There are two ways to do this.

First, if the original sample is small, simply copy the cells containing the original sample and
statistic of interest; then paste these cells elsewhere on the same spreadsheet. This approach was
used in the first two sections of the bootstrap chapter.

However, if the original sample is too large (e.g., more than 123 columns), then duplicating the
entire sample and statistic on the same sheet may not be possible. In addition, if many different
calculations are performed (especially if you have user-defined functions), copying and pasting
cells on the same sheet will be much slower. In these cases, you can simply copy the entire sheet
(execute Edit: Move or Copy sheet and check the Create a copy option). The original sheet
contains the original sample, and the copied sheet will serve as the place where bootstrapped
samples are repeatedly written. You do not want to run the bootstrap add-in on your original
sample sheet because it will destroy your original sample. The add-in does have an error check
that prompts you to make sure you really mean to overwrite cells with data.

Let us work on an example to see how the bootstrap add-in actually works. Open the Excel
workbook BootstrapDemo.xls to practice using the Bootstrap add-in.

The Data sheet has the Original Sample data, and cells C1 and C2 contain two statistics, the
average and median, which we want to bootstrap. Execute Edit: Move or Copy sheet and check
the Create a copy option. Rename the Data (2) sheet, ―Bootstrap Samples.‖ From this new
sheet, you are ready to test drive the bootstrap add-in.

7a7213d0-a031-4a71-92f4-542d8cd2ff9a.doc              Page 3 of 8
Execute Tools: Bootstrap . . . in order to get the Bootstrap dialog box:

                                                                           Click here
                                                                           to make the
                                                                           dialog box
                                                                           collapse so
                                                                           you can see
                                                                           the sheet.

 Check this box to
 record all cells that
 have been selected
 before executing
 Tools: Bootstrap.

Click in the Select Cell Range of Original Sample box, then click on cell A1, hold down the Shift
key, and click on cell A21. Repeat this procedure for the Select Cell Range of Output for One
Bootstrap Sample input. For the Select Single Cell to be Tracked input, click on cell C1; and
click on cell C2 for the Select a Second Cell to be Tracked option.

You are now ready to go. Click the Proceed button. The add-in warns that you are going to
overwrite existing data:

Because we have the original sample stored in the Data sheet, we can safely proceed in
overwriting the data on the Bootstrap Samples sheet. If you are unsure of the answer to this
question, it is always best to cancel and make sure your original sample will not be destroyed by
the bootstrapping process.

7a7213d0-a031-4a71-92f4-542d8cd2ff9a.doc              Page 4 of 8
Use the Progress Bar to gauge how long it will take to finish the simulation. You may use other
programs while the simulation is running, but that may slow Excel down. You can always hit
the Escape (ESC) key (on the top left corner of most keyboards) to kill the simulation. Click
End when prompted. The add-in runs faster if no other Excel workbooks are open.

The bootstrap results spreadsheet in your workbook is alive—you can change the scale, title, and
legends on the graphs, change labels and colors on the cells in the spreadsheets, and add
descriptive information as needed. The data underlying the graph are available by scrolling

You can run as many bootstrap simulations as you want by simply returning to your original
worksheet, copying the sheet, and executing Tools: Bootstrap. Delete unwanted results by
simply deleting the unwanted sheet.

If you check the Record All Selected Cells option, the add-in will track all cells that were
selected before you executed Tools: Bootstrap and brought up the Bootstrap dialog box. The
add-in inserts a new worksheet in your workbook and shows all of the values generated by the
bootstrap simulation. You can use this information to sort the results in order to find percentiles
(e.g., for confidence interval estimation) and to track more than just one or two cells. To use this
option, remember first to select the cells you want to record (using the CTRL key, as usual, to
select noncontiguous cells); then execute Tools: Bootstrap and check the option.

The results generated by the Record All Selected Cells option are in ―raw‖ form. You will need
to compute averages, SDs, and draw histograms on your own. (Our Histogram add-in is a
convenient tool for drawing one-variable and two-variable (superimposed) histograms.) You can
see the full set of simulation results for any cell (including one that was chosen as a tracked cell)
by simply selecting that cell before executing Tools: Bootstrap and then checking the Record All
Selected Cells option. From the BootstrapDemo.xls workbook, select cells E1 and F2 and
execute Tools: Bootstrap. The BootRaw sheet shows the output.


The logic of the add-in is really quite simple. Excel stores the original data in an array and takes
a random sample from that array of exactly the same size as the original sample. It then places
the data on the worksheet and recalculates the worksheet, getting values for any statistics or
formulas that are based on the pasted data. It stores the value of the tracking cell (or both
tracking cells) after each calculation. The results are then presented in a new worksheet in your

Because Excel takes the original sample and stores it internally in an array, you cannot have any
live cells (with formulas) in the original sample range of cells. The original sample is read once,
and it does not change throughout the entire bootstrap procedure. Thus, when planning your
bootstrap simulation, make sure that you do not use live cells in your original sample.

When sampling with replacement from a finite population, it is possible to obtain a sample in
which the statistic may evaluate to an error value (e.g., in a regression context, perfect
multicollinearity means the regression coefficients cannot be computed). In this case, the add-in
7a7213d0-a031-4a71-92f4-542d8cd2ff9a.doc              Page 5 of 8
will simply ignore that ―bad sample‖ and draw another sample. At the end of the analysis, if any
bad samples have been generated, the number is reported by a message box:


In addition to the ―data overwrite‖ warning, the Bootstrap add-in has a few simple error checks.

It will complain if you attempt to do a bootstrap with a tracking cell that is not a formula:

Clicking on the cell to be analyzed is better than typing it in because you might make a mistake
entering the address. If you enter, for example, ―=$A$,‖ for the Original Sample range, you will

The add-in checks to make sure the cell you selected for analysis changes when the sheet is
recalculated. For example, suppose you selected range A1:A21 for the Original Sample and
chose to put the Bootstrapped Samples in cells B1:B21, tracking cell C1. Because cell C1 does
not change (because the random samples are put in cells B1:B21 and cell C1’s formula is
―=AVERAGE(A1:A21)‖), the add-in reports a problem:

7a7213d0-a031-4a71-92f4-542d8cd2ff9a.doc              Page 6 of 8
Finally, if you check the Record All Selected Cells option and have selected more than 256 cells
to record or ask for more than 65,535 repetitions, there will not be enough room on the sheet.
The maximum size of an Excel spreadsheet is 2^16, or 65,536, rows by 2^8, or 256, columns.
Thus, the add-in will display a warning like this one:

Also, if you are having trouble using this feature, remember to select the cells you want to record
first (using the CTRL key to choose nonadjacent cells); then execute Tools: Bootstrap. The add-
in automatically records any cell that is highlighted (i.e., selected) on the sheet.

7a7213d0-a031-4a71-92f4-542d8cd2ff9a.doc             Page 7 of 8
The latest Bootstrap.xla version is 11 August 2005.

To check the date of your installed add-in, execute Tools: Add-ins and then highlight the add-in.
The Add-Ins dialog box displays the date at the bottom.

To install this for the first time, please follow the instructions on the first page of this document.

To install over a previous version that is already installed, please see

To install over a previous version, quit Excel and simply drag this new version into the Excel
Add-ins folder. If you do not know where this folder is, search for ―bootstrap.xla‖ and then drag
the new version over the old one. Of course, you will want to accept rewriting over the previous


If something goes wrong in the installation or loading process, an unexpected error keeps
recurring, or you have other problems, please contact us. We are interested in your comments,
suggestions, or criticisms of the Bootstrap.xla software.

Humberto Barreto                                    Frank Howland
Wabash College                                      Wabash College                       
(765) 361–6315                                      (765) 361–6317

7a7213d0-a031-4a71-92f4-542d8cd2ff9a.doc               Page 8 of 8

To top