Quarterly revenues (in $1,000,000's) for a national restaurant chain for a five-year period were as follows: Quarter Year 1 Year 2 Year 3 Year 4 Year 5 1 33 42 54 70 85 2 36 40 53 67 82 3 35 42 54 70 87 4 38 47 62 77 99 Based on these data, develop a multiple regression model that can capture both a trend and seasonal components. Carefully explain how you built the model and interpret each slope. Based on this model, what revenues would you predict for the third quarter of Year 6 (the following year)?

Explanation:

Let's first Define 4 independent variables - t, x₁, x₂, and x₃

t represents the period i.e. t =1 for year 1 Q1,

t =2 for year 1 Q2,..., t = 20 for year 5 Q4.

SO;

x₁ = 1 when it is Q1 and equal to zero otherwise

x₂ = 1 when it is Q2 and equal to zero otherwise

x₃ = 1 when it is Q3 and equal to zero otherwise

The situation is illustrated in the first table attached below.

However; Using Excel data analysis to develop a multiple regression with 'Revenue' being the dependent variable and t, x₁, x₂, and x₃ the independent variables; we obtain the following results as shown in the second image below.

Thus; the model will now  be:

Forecast = 24.475 + 3.344 t + 2.231 x₁ - 2.313 x₂ - 3.656 x₃

The Prediction for the third quarter of Year 6 is:

t = 23

x₁ = 0

x₂ = 0

x₃ = 1

Finally; Forecast = 24.475 + 3.344×23 + 2.231×0 - 2.313×0 - 3.656×1 = 97.73