Sample Workbook

The sample Excel workbook (Sample Book.xls) contains several sheets and VBA routines which can help you get started. The workbook can be found in the Samples subfolder of the folder EasyFit has been installed into (typically C:\Program Files\MathWave\EasyFit\Samples).

The sample workbook contains four sheets:

• Basic Functions
• Advanced Functions
• VBA Demo
• Simulation Demo

The sample VBA code can be found in ThisWorkbook.

Basic Functions

This sheet illustrates the use of worksheet functions provided by EasyFitXL to calculate various properties of the Weibull distribution. The cells marked with blue contain the worksheet functions which depend on the yellow-colored cells. For instance, when you change the distribution parameters alpha, beta, and gamma, the dependent cells (distribution mean, variance etc.) update accordingly.

Advanced Functions

The Advanced Functions sheet shows how to use the worksheet functions to fit probability distributions to data, and perform calculations based on the resulting distribution model. The sheet contains:

• random data generated from the Weibull distribution (see "Sample Data");
• routines illustrating how to fit the two-parameter and three-parameter Weibull models (see "Distribution Fitting");
• various functions based on the fitted three-parameter Weibull model, including the basic statistics (mean, variance etc.), general distribution functions (PDF, CDF etc.), and random number generation functions.

VBA Demo

This sheet also contains two charts and a button which has the UpdateWeibullGraph macro assigned to it. The first chart displays the theoretical Probability Density Function of the Weibull distribution, while the second displays the frequency histogram based on a data sample of 1000 random numbers from the same distribution.

Simulation Demo

The Simulation Demo sheet and the associated VBA code illustrate the use of EasyFitXL and Excel for project risk assessment, and can be easily modified or improved to address your specific needs.

Input Data

In this example, the completion time of the hypothesized project is estimated. The project consists of three consecutive phases: Design, Implementation, and Deployment. The total time required to complete the project is calculated as the sum of the times needed to complete each phase.

In the first model (Model A), the completion times of each of the phases and the entire project are assumed to be constant. The second model (Model B) takes account of the uncertainty involved in the completion of the project (good/bad weather, timely supply of needed materials and equipment, and other random factors). The time required to finish each of the project phases is most likely to be the same as in the first model, but can take on any value from min to max days.

Simulation

The resulting times are assumed to have the Beta-PERT (Pert) distribution which is quite commonly used for project risk analysis. This distribution has three parameters: m, a, and b, which correspond to the most likely, minimum and maximum completion times.

The blue-colored sheet cells use the PertRand function to model the completion times of the project phases. When the Run Simulation button is clicked, the SimulationDemo routine (see ThisWorkbook in VBE) forces Excel to recalculate the sheet 2500 times. On each iteration, the PertRand function generates new random numbers, and the total project completion time (see the yellow-colored cell) is recalculated and stored in the VBA array.

Results

The simulation results are presented in the form of a histogram which shows the distribution of the project completion times:

Application

The next step is to find a probability distribution which best fits to the simulated data set. Click the header of the K column to select the entire column, choose EasyFitXL|Fit Distributions from the main menu of Excel, and click OK (see Data Analysis for details).

Once you select the best fitting distribution in the Fitting Results window, you can apply this model to obtain some additional information about your project. For instance, the histogram indicates that even though the most likely completion time is 55 days, it can take as many as 65-66 days to finish the entire project in the worst case, and you need a more realistic time estimate.

In the Fitting Results window, highlight the Beta distribution, and select Tools|StatAssist:

This will open the integrated StatAssist tool which displays the graphs and properties of the distribution you selected. Open the Delimiters pane at the right, click the None button, and select One Delimiter:

Enter 55 into the X1 field, and click Apply. The Probabilities page indicates that the probability of the successful completion of the project in time is only 42%:

To obtain a more realistic estimate, you need to set a confidence level which indicates the desired probability of the timely project completion. A confidence level of 95% is commonly used for most projects; in some cases, a higher (sometimes lower) confidence level might be more appropriate.

Open the Calculations page and enter 0.95 into the P box:

StatAssist will update the x(P) value indicating a new time estimate (61 day).

Conclusion

The Simulation Demo sheet illustrates the various aspects of using EasyFitXL and Excel to obtain a realistic estimate of the project completion time under uncertainty.