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 months # of Patient Visit and has also tried using the average of all historical data to predict the next periods # 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 months # of Patient Visit and has also tried using the average of all historical data to predict the next periods # 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?