Question

Demand Forecasting for the Inner-city Health Center

Inner City Health Center is a federally funded health clinic that serves the needs of the inner-city poor. Currently the center is at the end of third-year operation and is preparing its staffing plan for the upcoming year. The federal government requires that the center prepare a budget request each year. The request is based largely on the forecast of the # of Patient Visit for specific services during the next year.

The health center administrator has in the past tried using the last month’s # of Patient Visit and has also tried using the average of all historical data to predict the next period’s # of Patient Visit for the center. Neither of these two techniques has proven satisfactory due to complicated month to month data pattern. They are currently seeking outside helpers to forecast the # of patient visit for the upcoming January year 2016.

The # of patient visit each month in the preceding three years (including the current year) is available In the following Table.

Table. Emergency Service Demand for the Inner-city Health Center

Month	# of patient Visit
	Year 2013	Year 2014	Year 2015
Jan.	267	358	486
Feb.	269	383	496
Mar.	301	480	550
Apr.	372	464	578
May	420	496	709
June	485	633	748
July	441	574	655
Aug.	423	533	673
Sept.	360	464	559
Oct.	275	393	567
Nov.	320	354	494
Dec.	233	333	490

Assignment: use Ms. Excel Spreadsheet to do the following.
You may copy and paste the data to your Excel Spreadsheet. I would also suggestion that you re-arrange data into a 2-dimension table (Months, #of patient visit). To do so will make your job easier.

1. Forecast
   1. Use a 4- Month Simple Moving Average Method to forecast the # of the emergency visit from May 2013 to January 2016.
   2. Use a Linear Projection Forecast Method to forecast the # of the emergency visit from January 2013 to January 2016.
   3. Use an Exponential Smoothing Forecast Method, with a = 0.25, to forecast the # of the emergency visit from June 2013 to January 2016. Assume that initial forecast for May 2013 is 450.
2. Plot One (nice) Chart for Data Series over time (Jan 2013 to Jan 2016):
   1. the historical data series,
   2. the data series of forecasts obtained in 1a), 1b) and 1c).
3. Use one of forecast Error Measurements, either MAD, or MSE, or MAPE (you choose) to determine which of the forecasts from 1a), 1b) or 1c) provides the best (smallest) forecasting error summary from the given historical data set.
   1. It is important to point out that error comparison of different forecast methods should be done on a Consistent Base. That is, the forecast error comparison for different forecast methods is meaningful only when we compare the forecast error of the Same Range of forecasts.
4. For the Exponential Smoothing forecast obtained in 1c), use Tracking Signal to monitor the forecast results and draw a conclusion on whether or not the forecasts are Biased, assume C = 3, and -C = -3 to be the control limits of the tracking signal method.
5. Use the same Forecast Error Measurement you used in question 3), find the best smoothing parameter a (i.e. the a that leads to the smallest forecast error) of Exponential Smoothing Forecast Method.
6. For the given historical data set,
   1. Based on the data pattern of the three-year data set, one can easily argue that the forecast method used in 1a)-c) are not very good forecast methods. Explain why?.
   2. Propose your own Forecast Method that might be better than the forecast methods 1a) - 1c). Use the Forecast Method proposed to do forecasts from May 2013 to January 2016.
   3. use the Same Forecast Error measurement as you used in part 3) to calculate the forecasting error and, then, compare it with the results you obtained in 1a)- c). Is your method better?

      Demand Forecasting for the Inner-city Health Center
      

      Inner City Health Center is a federally funded health clinic that serves the needs of the inner-city poor. Currently the center is at the end of third-year operation and is preparing its staffing plan for the upcoming year. The federal government requires that the center prepare a budget request each year. The request is based largely on the forecast of the # of Patient Visit for specific services during the next year.

      The health center administrator has in the past tried using the last month’s # of Patient Visit and has also tried using the average of all historical data to predict the next period’s # of Patient Visit for the center. Neither of these two techniques has proven satisfactory due to complicated month to month data pattern. They are currently seeking outside helpers to forecast the # of patient visit for the upcoming January year 2016.

      The # of patient visit each month in the preceding three years (including the current year) is available In the following Table.

      Table. Emergency Service Demand for the Inner-city Health Center

      Month	# of patient Visit
      	Year 2013	Year 2014	Year 2015
      Jan.	267	358	486
      Feb.	269	383	496
      Mar.	301	480	550
      Apr.	372	464	578
      May	420	496	709
      June	485	633	748
      July	441	574	655
      Aug.	423	533	673
      Sept.	360	464	559
      Oct.	275	393	567
      Nov.	320	354	494
      Dec.	233	333	490

      Assignment: use Ms. Excel Spreadsheet to do the following.
      You may copy and paste the data to your Excel Spreadsheet. I would also suggestion that you re-arrange data into a 2-dimension table (Months, #of patient visit). To do so will make your job easier.

   4. Forecast
      1. Use a 4- Month Simple Moving Average Method to forecast the # of the emergency visit from May 2013 to January 2016.
      2. Use a Linear Projection Forecast Method to forecast the # of the emergency visit from January 2013 to January 2016.
      3. Use an Exponential Smoothing Forecast Method, with a = 0.25, to forecast the # of the emergency visit from June 2013 to January 2016. Assume that initial forecast for May 2013 is 450.
   5. Plot One (nice) Chart for Data Series over time (Jan 2013 to Jan 2016):
      1. the historical data series,
      2. the data series of forecasts obtained in 1a), 1b) and 1c).
   6. Use one of forecast Error Measurements, either MAD, or MSE, or MAPE (you choose) to determine which of the forecasts from 1a), 1b) or 1c) provides the best (smallest) forecasting error summary from the given historical data set.
      1. It is important to point out that error comparison of different forecast methods should be done on a Consistent Base. That is, the forecast error comparison for different forecast methods is meaningful only when we compare the forecast error of the Same Range of forecasts.
   7. For the Exponential Smoothing forecast obtained in 1c), use Tracking Signal to monitor the forecast results and draw a conclusion on whether or not the forecasts are Biased, assume C = 3, and -C = -3 to be the control limits of the tracking signal method.
   8. Use the same Forecast Error Measurement you used in question 3), find the best smoothing parameter a (i.e. the a that leads to the smallest forecast error) of Exponential Smoothing Forecast Method.
   9. For the given historical data set,
      1. Based on the data pattern of the three-year data set, one can easily argue that the forecast method used in 1a)-c) are not very good forecast methods. Explain why?.
      2. Propose your own Forecast Method that might be better than the forecast methods 1a) - 1c). Use the Forecast Method proposed to do forecasts from May 2013 to January 2016.
      3. use the Same Forecast Error measurement as you used in part 3) to calculate the forecasting error and, then, compare it with the results you obtained in 1a)- c). Is your method better?

Answer 1

(a) 4- MONTH SIMPLE MOVING AVERAGE:

Year Month #of Patient Visit 4-Month Simple Moving Average 2013 May 420 #N/A June 485 #N/A July 441 #N/A August 423 442.25 September 360 427.25 October 275 374.75 November 320 344.5 December 233 297 2014 January 358 296.5 February 383 323.5 March 480 363.5 April 464 421.25 May 496 455.75 June 633 518.25 July 574 541.75 August 533 559 September 464 551 October 393 491) November 354 436 December 333 386 2015 January 486 391.5 February 496 417.25 March 550 466.25 April 578 527.5 May 709 583.25 June 748 646.25 655 672.5 August 673 696.25 September 559 658.75 October 567 613.5 November 494 573.25 December 490 527.5 2016 January 517 July

Step 1: Arrange the Data in the Format Year, Month, # of Patient Vist, 4- Month Simple Moving Average
Step 2: Go to Data Analysis in Data Tab.
Step 3: Select Moving Average.
Step 4: In the Input tab, Select the Range in which # of Patient Visit data is there.
Step 5: Input Interval as 4 (because of the 4-month moving average)
Step 6: In the output, select the First Cell (Cell just after the column heading)of 4-Month moving Average Column.

(b) LINEAR PROJECTION FORECAST METHOD

Month # of Patient Visit Linear Projection Forecast Jan-13 267 Feb-13 269 Mar-13 301 Apr-13 372 May-13 420 Jun-13 485 Jul-13 441 Aug-13 423 Sep-13 360 Oct-13 275 Nov-13 320 Dec-13 233 Jan-14 358 342 Feb-14 383 329 Mar-14 480 321 Apr-14 464 346 May-14 496 372 Jun-14 633 415 Jul-14 574 515 Aug-14 533 579 Sep-14 464 620 Oct-14 393 618 Nov-14 354 571 Dec-14 333 513 Jan-15 486 430 Feb-15 496 419 Mar-15 550 413 Apr-15 578 438 May-15 709 466 Jun-15 748 540 Jul-15 655 646 Aug-15 673 704 Sep-15 559 756 Oct-15 567 747 Nov-15 494 723 Dec-15 490 657 Jan-16 580

Step 1: Use Function FORECAST. LINEAR
Step 2: Arrange the data as shown above.
Step 3: In column Linear Projection Forecast, in January 2014, Type Formula FORECAST.LINEAR(A14,B2:B13,A2:A13)

where,

A:14 = Cell of January 2014

B2: B13= # of Patient Vist from January 2013 to December 2013

A2: A13= Cell Range from January 2013 to December 2013

Drag this formula up to January 2016.

(c) EXPONENTIAL SMOOTHING FORECAST METHOD