The Norgas Distributing Company

Kingstown, Ontario

To: The Executive Management Committee

From: CHAPS

Please find attached a summary of our findings in regard to the optimal forecasting of gas demand for the following operating cycle.

It is our intention in this report to elaborate on these recommendations so you can analyze our findings and make the appropriate decisions.

 We recommend the purchase of ten years of data for our analysis base on the following rationale:

The larger the sample, the better the possibilities for accurate measurement. We could decide after looking at ten years whether a smaller sample from this one would be more appropriate for our forecasting calculations.

Since the benefits of proper forecasting far outweigh the cost of the data, we felt that the out of pocket expense of $15,000 was not material.

2)

We recommend a subscription to the Orion weather forecast service based on our analysis of the relationship between the forecast services’ past forecasts and actual past TEMP and WIND values.

To do this we used regression analysis of actual temp vs. Orion’s FTO and regression analysis of actual WIND vs. Orion’s FWO. We repeated this process using Polaris’ values. Furthermore, we looked at the correlation coefficients between the forecast values and the actual values. We ran calculations using the last ten years, the last 5 years, the last 4 years and the last three years.

What we found is that in the case of TEMP, using ten years gave us better values wherein the R2’s were better.

Similarly, using correlation coefficients, we got better results using 10 years data.

Orion’s forecasts were consistently more accurate than those of Polaris.

In the case of WIND speed we found that the correlation coefficients were quite low. In addition to looking at different samples of past years (10, 5, 4 and 3) we ran regressions for the past 10 years of a particular month. In this way we decided to use the equation that resulted from the regression of Actual Wind and FWO for the month of Februarys for the last ten years.

These calculations and analysis provided us with the means to decide how TEMP and WIND should be forecast in order to later on forecast the year’s minimum gas order and the maximum to order per day.

The equation resulting from regressing FTO and TEMP for the last ten years is

NORGAS FTEMP = (1.057)(FTO)-.2906 R2=.9626

The equation resulting from our regression of February FWO vs. WIND is

NORGAS FWIND = (.6056)(FWO)+3.7319 R2= .3444

 3)

To forecast Daily demand for gas we performed more regression analysis. We also decided to use DDAY as our measure of TEMP in the equation. Using 15 degrees as a base value we plugged in our equation of FTEMP into the DDAY equation resulting in the following:

DDAY=15-FTEMP, if FTEMP <15

DDAY=0, if FTEMP > or = to 15

Where FTEMP = (1.057)(FTO)-.2906

Having decided on DDAY, we proceeded to run regression adding several dummy variables to account for day of the week effects on gas demand. We tried using SAT, SUN and DOWEEK and dropping DOWEEK as well as using SAT, SUN, and DOWEEK where DOWEEK = Mon., Tue, Thur, and Fri, thereby dropping the Wednesday dummy variable. This regression resulted in the following equation:

NORGAS FDEM = 9121.555+1492.659(DDAY)+173.9952(WIND)-2644.07(SAT)-1982.71(SUN)-557.524(DOWEEK)

Where on Wed constants are 0

Where DOWEEK = Moon, Tue, Thur, Fri

We ran regressions using the last 10, 5, 4 and 3 years of data. We found that using the last 3 years, yielded a better R2, namely a better fit for our model, implying more accuracy. This may be because demand for the last ten years exhibits an upward trend.

Next we needed to determine the daily order of gas (Q, in KM3). From our constraints, it follows that Q can not be higher than D so

Q= Demand if Demand < or = to D

Q= D if Demand > D

To arrive at the actual number for Q, however, we needed to determine D and A.

To determine A we plotted demand for the last ten years and projected demand for the eleventh year. This gave us a figure of 8

We also looked at the mean demand for the last ten years. This gave us a mean of 7

To determine D we used linear programming. Since we realize that this forecasting problem does not behave well, we used different figures and how these figures affected our profits forecast. We used Pivot tables to look at maximum and minimum daily demands for the last ten years to get an idea of where we might be close to an optimum number. In our example.xls, we plugged in all our formulas and proceeded to try A and D figures.

Furthermore, we added data for an entire year to our monthly example.xls. For our Actual Figures we used last year’s numbers (19Y4).

We discovered that at a certain point we were able to forecast a profit of around two million dollars. This seemed unacceptable, given a target of fifty five million. Thus, we surmised that we were loosing money because of our A figure. So we tried out different scenarios and arrived by trial and error to our final recommendation, namely

A = 8,100,000

D = 39,000

These figures lead us to predict a Net profit of $32,902,000.