The Batch Fit tool analysis example uses a Crystal Ball example model, Magazine Sales.xls. This model (Figure 50, Magazine Sales Workbook) shows the estimated gross profit resulting from newsstand sales of four of the company’s most popular magazines.
In this model, cells C5 through F5 are formulas that refer to the first row of data on the Sales Data worksheet. However, the model would be more accurate if these formulas were replaced with assumptions based on the entire range of historical data. The tool can be used to generate an assumption for each data column of the Sales Data worksheet. Then, Crystal Ball commands can be used to copy and paste those assumptions from the output data to the first data row of the Magazine Sales model.
Figure 51, Results Below the Model shows assumptions and correlations generated by the tool using data on the Sales Data tab of Magazine Sales.xls. When the tool runs, it fits each column of data to each selected distribution. For each fit of a distribution to a set of data, the tool calculates the indicated goodness-of-fit test statistic. The distribution with the best fit is placed in the spreadsheet to create an assumption cell that you can copy to the appropriate location in the model.
The tool was set to use all continuous data for curve fitting, to rank fits using the Anderson-Darling method, to define correlations between all assumptions, to show a correlation matrix between all data series, and to place output on the Model tab below existing data.
In this example, the generated assumptions in row 14 are copied into row 5, and then cleared from row 13. The forecast in cell C10 indirectly references all of these Sales Volume assumptions. Then, a Monte Carlo simulation is run, using the same sequence of random numbers with a seed value of 999.
Running the simulation produces a forecast chart of the total gross profit from the Magazine Sales workbook. In the Total Gross Profit forecast chart, if you replace –Infinity with $5,500, you find that the certainty or probability of making this amount of profit is about 75% (Figure 52, Magazine Sales Profit from Newsstand Sales).
