|EasyFit Help Home • Learn More About EasyFit|
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:
The sample VBA code can be found in ThisWorkbook.
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.
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:
This sheet also contains two charts and a button which
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.
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.
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
The simulation results are presented in the form of a histogram which shows the distribution of the project completion times:
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 OK (see Data Analysis for details).from the main menu of Excel, and click
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 :
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).
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.
|Copyright © MathWave Technologies