Tutorial 2—Toledo Gas

Suppose you work for Toledo Gas Company in the Residential Division. The Public Utilities Commission requires that you predict gas usage for the coming year to make sure that the company can meet the demand.

  To start the tutorial:

  1. Start Crystal Ball, which automatically starts Microsoft Excel.

  2. Select Help, then Crystal Ball, then Examples Guide, and then Toledo Gas in the Model Name list. (In Microsoft Excel 2007 or later, select Resources in the Help group and then select Examples Guide).

    The Toledo Gas spreadsheet opens (Figure 11, Toledo Gas Spreadsheet).

    Figure 11. Toledo Gas Spreadsheet

    Model sheet of Toledo Gas.xls example workbook showing the date, usage in cubic feet, occupancy permits, average temperature, and cost of natural gas per cubic foot by month from January 2006 through December 2010.

  3. Select cell C5.

  4. Select Run, and then Predictor.

    The Input Data panel opens. Predictor selected all the data from cell B4 to cell F64.

  5. Click Next to display Data Attributes.

  6. Confirm that the default settings are selected: months, AutoDetect, and Fill-in missing values. Then, click Next to open Methods.

    The Methods panel offers four methods, including Multiple Linear Regression. Through research, you know that residential gas usage is primarily affected by three variables: new home starts, the temperature, and the price of natural gas. However, you are not certain how much effect each has on gas usage. Because you have independent variables affecting a dependent variable (the variable that you are interested in), regression is recommended for this forecast.

    In the Toledo Gas spreadsheet, the dependent variable is the historical residential gas usage. Independent variables:

    • Number of occupancy permits issued (new housing completions)

    • Average temperature per month

    • Unit cost of natural gas

  7. In Methods, confirm that all four methods are selected, and then click Multiple Linear Regression to display the Multiple Linear Regression Details pane.

    Note:

    Ensure that the Multiple Linear Regression check box stays selected.

  8. Click Select Variables to open the Regression Variables dialog.

  9. If necessary, in Regression Variables, select Usage (ft3) and use The right-arrow button. to move it into Dependent variables (Y's). Be sure the check box is selected, and confirm that the other three variables are listed under Independent variables (X's).

  10. Click OK to close Regression Variables.

    Methods is displayed again.

  11. In the Multiple Linear Regression Details pane, confirm that Method is set to Standard and Include constant in regression equation is selected.

  12. Click Next.

    The Options panel opens with these defaults: RMSE — Root Mean Squared Error and Standard forecasting.

  13. Click Run to run the forecast and display the Predictor Results window (Figure 12, Predictor Results Window, Toledo Gas Example).