Question

Good Life is a public health clinic is in its fourth year of operation and is preparing its staffing plan for the upcoming quarter. The· federal government requires the clinic to prepare a budget request each quarter for the coming quarter. The request is based largely on the forecast of demand for specific services during the next quarter.

Demand data for emergency services at the clinic are available for each of the four quarters of the preceding three years and for the first two quarters of the current year. The data were entered into an Excel spreadsheet as shown below.

In the past, the administrator of the clinic has tried using the last period's demand and has also tried using the average of all past demand to predict the next period's demand for the center. Neither of these two techniques has proved satisfactory. Using the last period's demand as a predictor of the next period's demand produced erratic forecasts. For example, using this method the administrator predicted a demand of 3500 visits for the second quarter of year 1, but there were actually 8000 visits. (Overtime and rush orders reached a peak during this quarter.) The administrator then predicted 8000 visits for the third quarter, but only 5500 visits materialized. Clearly, this method could not sort out the fluctuations in the demand data, and it was therefore deemed unsatisfactory.

The administrator then turned to the average of all demand data to predict the next period's demand. For the fourth quarter of year 1 the administrator predicted 5667 visits [i.e., (3500 +

1

2

8000 + 5500)/3], but 10,000 actually occurred. For the tenth period, he forecast 5666 visits (i.e., the sum of the first nine periods' demand divided by 9) and 9500 occurred. The administrator recognized that this averaging method produced forecasts that smoothed out the fluctuations but did not adequately respond to any growth or reduction in the demand trend. As a matter of fact, the averaging method performed progressively worse as the amount of data increased. This was because each new piece of demand data had to be averaged with all the old data from period 1 to the present, and therefore each new element of data had less overall impact on the average. In fact, if the administrator were to use the averaging method to forecast the third-quarter demand for year 4, the forecast would be 8143, clearly a poor forecast when compared with demand in the past few periods.

Submit a report describing the development of a better forecasting method for Good Life Clinic and then use the developed method to forecast the demands for the third and fourth quarters of this year.

3 - N 2 11 Year Since Period Number of 12 Opening Quarter Number Patient Visits 3,500 8,000 5,500 10,000 4,500 6,000 3,000 5,500 5,000 9,500 7,500 15,000 13,500 17,500 ON. 12

Answer 1

Year Since Opening	Quarter	Period Number (X)	Number of Patient Visits (Y)	X - Mx	Y - My	(X - Mx)2	(X - Mx)(Y - My)
1	1	1	3500	6.5	4642.86	42.25	30178.5714
	2	2	8000	5.5	142.86	30.25	785.7143
	3	3	5500	4.5	2642.86	20.25	11892.8571
	4	4	10000	3.5	-1857.14	12.25	-6500
2	1	5	4500	2.5	3642.86	6.25	9107.1429
	2	6	6000	1.5	2142.86	2.25	3214.2857
	3	7	3000	0.5	5142.86	0.25	2571.4286
	4	8	5500	-0.5	2642.86	0.25	-1321.4286
3	1	9	5000	-1.5	3142.86	2.25	-4714.2857
	2	10	9500	-2.5	-1357.14	6.25	3392.8571
	3	11	7500	-3.5	642.86	12.25	-2250
	4	12	15000	-4.5	-6857.14	20.25	30857.1429
4	1	13	13500	-5.5	-5357.14	30.25	29464.2857
	2	14	17500	-6.5	-9357.14	42.25	60821.4286

Mean ( Average) Sum of (X - Mx)^2 = 227.5 Sum of (X - Mx)(Y - My) = 167500

X=7.5

Y= 8142.5

For given data, the regression equation for Y is:

ŷ = 736.26374X + 2620.87912

Calculation Summary

Sum of X = 105
Sum of Y = 114000
Mean X = 7.5
Mean Y = 8142.8571
Sum of squares (SS_X) = 227.5
Sum of products (SP) = 167500

Regression Equation = ŷ = bX + a

b = SP/SS_X = 167500/227.5 = 736.26374

a = M_Y - bM_X = 8142.86 - (736.26*7.5) = 2620.87912

ŷ = 736.26374X + 2620.87912

Here, I used a simple linear regression method for forecasting the demands for the third and fourth quarters.

Forecast demand for 3rd quarter by using the above equation

736.26374X + 2620.87912

Substitute X with 15 then we get 13,665.

Forecast demand for 4th quarter by using the above equation

736.26374X + 2620.87912

Substitute X with16 then we get 14,401.

Linear Regression Forecasting Method :

The linear regression tool derives a linear equation from a set of variables. If you want to forecast sales figures, the data is in the form of a pair of values: month 1 and sales amount 1, month 2 and sales amount 2, etc. The derived equation represents a line drawn through the data points that best fits the average trend. You can use the equation to forecast future data by putting in the number of a future month and calculating the forecast sales.

So here, I took the number of patients instead of sales.