Business Analytics and Big Data (ACC73002)
Assignment 3 – Report (50%)
The Vintage Restaurant, on Captiva Island near Fort Myers, Florida, is owned and operated by Charles Bronson. The restaurant just completed its third year of operation. During those three years, Bronson sought to establish a reputation for the restaurant as a high-quality dining establishment that specializes in fresh seafood. Through the efforts of Bronson and his staff, his restaurant has become one of the best and fastest-growing restaurants on the island.
To better plan for future growth of the restaurant, Bronson needs to develop a system that will enable him to forecast food and beverage sales by month for up to one year in advance. The Excel file (RestaurantSales) contains data on sales over 36 months.
Perform an analysis of the sales data for the Vintage Restaurant using Excel. Prepare a 1500 word report for Bronson, which is structured as follows:
• Purpose (5 marks).
• Data and empirical strategy (15 marks)
• Results & Discussion 20 marks)
• Recommendations (10 marks).
Include in your report the following:
• A time series plot (comment on the underlying pattern in the time series).
• Run a multiple linear regression without a trend. Present and explain the results. Calculate the mean squared error (MSE) for this model.
• Run a multiple linear regression model with a linear trend. Present, and explain the results. Calculate the mean squared error (MSE) for this model.
• Which model should be used to forecast sales? Explain. (Hint: Compare the MSE for both models).
• Forecast sales for January through December of the fourth year using the preferred regression model.
• Assume that January sales for the fourth year turn out to be $295,000. What was your forecast error? If this error is large, Bronson may be puzzled about the difference between your forecast and the actual sales value. What recommendations could you make to resolve Bronson’s uncertainty about the forecasting procedure?