Director Very Busy needs to allocate time this week for office appointments, so
ID: 388774 • Letter: D
Question
Director Very Busy needs to allocate time this week for office appointments, so he needs to forecast the number of employees who will seek appointments. The director has gathered the following time series data recently
Period Employee Appointments
4 weeks ago 95
3 weeks ago 80
2 week ago 65
last week 50
a. What would naive forecasting suggest as the number of employee appointments that can be expected this week? ¨ b.
b. What is this week’s forecast for employee appointments using a 3-week moving average? What would the same forecast be using a 2-week moving average? ¨
c. What would be this week’s forecast for employee appointments using exponential smoothing with alpha of 0.2, if the forecast for 2 weeks ago was 90? ¨
d. If the director used these 4 weeks of data to create a linear regression, what does that linear regression formula suggest for this week’s forecast of employee appointments? What does the regression analysis suggest in general about employee appointments for Director Very Busy?
Explanation / Answer
Ft = At-1
Where ,
Ft = Forecast for period t
At-1 = Actual employee appointment
Therefore ,
Expected employee this week
= Actual employee last week
= 50
Expected employee this week = 50
Ft = ( At-1 + At-2 + At- 3 ) / 3
Where,
Ft = Forecast for period t
At-1, At-2, At-3 = Actual appointments for period t-1, t-2 and t-3 respectively
Therefore,
Forecast for this week
= ( Actual appointment last week + actual appointment 2 weeks ago + actual appointment 3 weeks ago ) / 3
= ( 50 + 65 +80 ) / 3
= 65
Forecast using 2 weeks moving average :
Ft = ( At-1 + At-2 ) /2
Therefore ,
Forecast for this week
= ( actual appointment last week + actual appointment 2 weeks ago ) /2
= ( 50 + 65) /2
= 57.50
Forecast using 3 week’s moving average = 65
Forecast using 2 week’s moving average = 57.50
Ft = alpha x At-1 +( 1 – alpha ) x Ft-1
= 0.2 x At-1 + 0.8 x Ft-1
Ft, Ft-1 = Forecasts for period t and t-1 respectively
At-1 = Actual employee appointment during period t-1
Alpha = Exponential smoothing constant = 0.2
Therefore ,
Forecast for last week
= 0.2 x Actual employment 2 weeks ago + 0.8 x Forecast of employment 2 weeks ago
= 0.2 x 65 + 0.8 x 90
= 13 + 72
= 85
Forecast for last week
= 0.2 x actual appointment last week + 0.8 x Forecast for last week
= 0.2 x 50 + 0.8 x 85
= 10 + 68
= 78
This week’s forecast for employee appointment = 78
Y = a + b.t
Y ( dependent variable ) = Forecast on employee appointments
T = Srial number of period
e.g ,
4 weeks ago = 1
3 weeks ago = 2
2 weeks ago = 3
Last week = 4
This week = 5
We place values of t ( as mentioned above ) as well as values of employee appointments ( as given ) in two adjacent columns in excel and apply the formula LINEST ( ) and obtain following values of a and b :
A= 110
B = - 15
Therefore ,
Y = 110 – 15.t
Therefore , Forecast for this week ( apply t – 5 )
= 110 – 15 x 5
= 110 – 75
= 35
This week’s forecast for employee appointment = 35
Linear regression analysis suggests that as time passes , director’s workload regarding employee appointment reduces
Expected employee this week = 50
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.