Can you get this Assignment done by 12/04/2020? The questions and Answer sheet are attached. you must enter the answer to the excel sheet attached, not in word. This is very important
Case Set-Up • The owner of the Vintage Restaurant, Karen Payne, would like to have the next year’s food and beverage sales forecasted by month. • You are given an Excel file with the survey results and a template to provide your responses to the case questions. • In this project, we practice how to use time series analysis to make forecast. You should be able to o use visual inspection to identify the pattern and make the initial forecasting model o check some signals to see how to improve the forecasting model. Some of the signals include R square (it indicates how much variability in the dependent variable is captured by the model) Measures of Forecast Accuracy, such as MSE o interpret the forecasts in the right way • You can refer to the slides in “Forecasting Model Procedure” for a general guideline. Data Set: • It is a time series data set, with both year and month time stamps. Each data point in a time series needs a unique time stamp. In this data set, year number and month number together make the unique time stamp for each data point. However, such combined time stamps are not easy to handle in the model. We can insert a new column “Period”, using 1, 2… to consecutively label each data point. Excel Data Tools Needed: You will need to use DataAnalysis Toolpak for some questions. DataAnalysis Toolpak is an Excel add-in. You can find it in Data’s Analyze section (at the very end of the ribbon). However, if using it for the first time, you may need to call it out first. Here is how to do it: • Go to “File”, select “Options” at the bottom of the left panel • Click “Add-ins” on the left panel • Find “Manage Excel Add-ins” at the bottom, click “Go…” button • Check “Analysis ToolPak” and click “OK” button Question 1: • Objectives: Make visual inspection to identify the patterns. You should be able to o create the time series plot o Identify the patterns of time series data • Use Line chart to create the time series plot o Slide: Important Concepts o Excel example: 4. LinearTrend o Video: Use Regression tools to forecast (Part 1) • Identify the patterns of time series data. o Slides: Time Series Patterns o Seasonality pattern means that you can identify the repeated cycles in the data, and the length of each cycle is shorter than a year. A cycle is not necessarily in quarters. Inside a cycle, the data is not necessarily in quarters either. They could be in months, in days, in hours, etc. Check Homework Q27 for the example of using hours to characterize the seasonality. • Comment on: What kinds of patterns can be observed in the time series plot? What pattern is more obvious? Is there any trend pattern? If yes, what kind of trend pattern? How would you propose the forecasting model basing on your observation? Question 2: • Objectives: Propose two forecasting models and compare them to decide which one is better o Propose forecasting models basing on the observation from visual inspection o Compare models using important signals such as R square or MSE • Propose the forecasting models o For first model, include only the most obvious pattern o For second model, include the less obvious pattern too o Use separate tab to create different models. o You need to prepare data first to each model • Compare the models o Compare R square o Calculate MSE and compare o Refer to slide “Forecasting Model Procedure” • You can also check the Excel examples “5. NoTrend” and “6. WithTrend” • You can watch the video “B. Use Regression tools to forecast”, especially Parts 2 & 3. • Comment on: Which model is better? Should the better model have a higher R square or a lower R square? Should the better model have a higher MSE or a lower MSE? Question 3 & Question 4: • Objectives: Use the forecasting model you pick to do the forecasts for the next year’s monthly sales. o Prepare the data from the future years to plug in the model for the monthly forecasts o Be able to explain why forecasted sales is different to the actual sales • Calculate the forecast error and the percentage of forecast error. Do you think those errors are big ones? • Comment on: Are the forecast error and the percentage of forecast error the big ones? How would you comment on the forecasting model you pick? Case Set-Up Data Set: Excel Data Tools Needed: Question 1: Question 2: Question 3 & Question 4: Data YearMonthSales 1January242 1February235 1March232 1Apirl178 1May184 1June140 1July145 1August152 1September110 1October130 1November152 1December206 2January263 2February238 2March247 2Apirl193 2May193 2June149 2July157 2August161 2September122 2October130 2November167 2December230 3January282 3February255 3March265 3Apirl205 3May210 3June160 3July166 3August174 3September126 3October148 3November173 3December235