Question

Using Excel: 1.1 Team sales personnel think that merchandise sales at their upcoming sporting event could be related to ticket sales. Below is the data from past home games for a regression analysis: Merchandise Sales ($10,000s) 15,17,10,9,16,14,12,19,18,17 Tickets Sold (1000s) 54,67,53,49,59,58,56,63,65,61 a. What is the regression equation? (5 pts) b. Use the model to forecast merchandise sales if there are 58,000 tickets sold. (5 pts) c. Is the model statistically significant at the .05 level? (5 pts) d. How much of the variability in merchandise sales is determined by tickets sold? (5 pts) 2.1 Sales personnel want to further develop the model to include other variables. Merchandise Sales ($10,000s) 15,17,10,9,16,14,12,19,18,17 Tickets Sold (1000s) 54,67,53,49,59,58,56,63,65,61 Win or Loss (W or L) W, L, L, W, W, L, W, W, L, L, Event includes fan giveaway (Y or N) N, Y, Y, Y, N,Y, N, Y, Y, Y a. Should you keep either or both of the variables in the model and why? (15 pts) b. What is the regression equation (only include variables that should be in the model)? (5 pts) c. What is the merchandise sales forecast for 58,000 tickets sold at a winning event that does not include a fan giveaway? (10 pts)

Answer 1

Answer 1.1

a) the regression equation is: linear fit with R-square value of 0.7744 i.e. 77.44% which is a good fit.

Y(Merchandise Sales ($10,000s)) = -16.2229+0.5286X(Merchandise Sales ($10,000s))

the plot is:

Tickets Sold (1000s) Line Fit Plot 25 ך y 0.5286x 16.223 e 20 R2 0.7744 Merchandise Sales ($10,000s) Predicted Merchandise Sales ($10,000s) 15 a 10 Linear (Merchandise Sales ($10,000s)) 0 0 20 40 60 80 Tickets Sold (1000s)

b) The forecast value when there are 58,000 tickets sold based on the above model is 14.43567(144357)

c) the model statistically significant at the .05 level because p-value is less than 0.05

d)the variability in merchandise sales is determined by tickets sold is: 2.93613518197574

2.1) if u convert the data set in win and loss (i.e. W and L) then it can not fit the regression model because the data will not be continuous it becomes binary which cant predicted by the regression model.

if u have any query your comments are welcome