Mr$.$ Dan Fisher is the Chief Operations Officer for Betsy Johnson Regional Medical Center, a private, non$-$profit $[501($c$)(3)]$ organization located in Dunn, North Carolina. Mr$.$ Fisher has requested to speak with you about a project he$$d like you to tackle. He would like for you to forecast the demand for inpatient beds during the upcoming $2019$ fiscal year using the prior $22$ years$$ bed utilization data. While the two of you were discussing the project, she suggested that you consider performing a $3$ year and $5$ year moving average and chart the results on a single graph along with the historical inpatient bed utilization data. Mr$.$ Fisher knows that whenever you employ a static, time series forecasting model, you also need to assess forecasting error. To this end, he would like for you to compute the Mean Absolute Deviation $($MAD$),$ Mean Squared Error $($MSE$),$ Root Mean Squared Error $($RMSE$),$ and the Mean Absolute Percent Error $($MAPE$)$ for your $3$ and $5$ year moving averages comparing these results to the historical inpatient bed utilization data. Finally, he would like you to perform a descriptive analysis on the $22$ years of historical inpatient bed utilization data. Following your meeting, you promptly got with the business office, pulled the data, and imported it into an Excel spreadsheet where you$$ll be able to perform the necessary analyses.

For this assignment, you're a Healthcare Administrative Resident for Betsy Johnson Regional Medical Center. After reading the case scenario: Betsy Johnson Regional Medical Center, you$$ll be expected to conduct a $3$ year and $5$ year moving average to forecast bed demand for $2019.$ To conduct your forecast, you will use the Medical Centers$$ actual bed utilization data for the past $22$ years $(1997$ through $2018).$ In addition, Mr$.$ Fisher would like to see the historical patient bed utilization data along with the $3$ year and $5$ year moving averages charted on the same graph. She believes this side$-$by$-$side comparison of this time series data may be telling. Mr$.$ Fisher would also like for you to determine forecasting error by computing the MAD, MSE, RMSE, and MAPE for both the $3$ and $5$ year moving average. Finally, he has requested that you perform a descriptive analysis of the historical inpatient bed utilization data. You have already pulled the data and imported it into an Excel spreadsheet $($view Excel spreadsheet sample$).$ You$$re expected to draft a business memorandum narratively summarizing AND interpreting your findings. You will also be expected to thoughtfully embed the moving averages graph into the body of your memo and reference it in the write$-$up$.$ The memo should be directed to the attention of Ms$.$ Foster.

To complete this task, you$$ll be expected to use the Data Analysis ToolPak to perform the $3$ year and $5$ year moving averages, chart the moving averages along with the historical bed utilization data, and then complete your descriptive analysis. Next, you$$ll summarizing AND interpreting the findings from your forecasting and descriptive analysis into a professional business memorandum to Mr$.$ Fisher.

You may want to consider your responses to the below questions; review the current literature; and watch the recommended YouTube videos located under the Help section in preparation for composing your business memorandum to Mr$.$ Fisher.

What is the true purpose for conducting a descriptive analysis on a given dataset?

How do I accurately interpret the output from the descriptive analysis?

What is forecasting?

Why is forecasting necessary in business?

What are the strengths and weaknesses associated with forecasting?

What are the strengths and weaknesses associated with conducting a moving average?

What do the results from a moving average tell us$?$

What are the results from the descriptive analysis and forecasting telling me about the demand for inpatient beds in $2019?$

How do I determine forecasting error and why is this so important when employing forecasting models in business?

What is a Mean Absolute Deviation $($MAD$),$ Mean Squared Error $($MSE$),$ Root Mean Squared Error $($RMSE$),$ and Mean Absolute Percent Error $($MAPE$)?$

Which measure of forecasting error is best given the data being forecasted? Why?

Instructor tips: In your memorandum, be careful not to draw any conclusions that exceed you analyses. In other words, you CANNOT read any more into the results then what is there. Make certain that you read up on forecasting, moving averages, and determining methods of forecasting errors, and descriptive analysis; understand their purpose; and keep your report back to Mr$.$ Fisher aligned with these purposes.