Grading (20 pts): Your solutions should be clear and concise using appropriate mathematical notation and equation formatting. Any equations should be included on separate lines and NOT be embedded within sentences. Written responses should also be clear and concise and properly copy-edited for typos and grammatical errors prior to submission. Napa Valley Winery has sought your expertise to help them compare forecasted sales using a regression trendline approach and exponential smoothing. They face a very pronounced seasonal effect in their sales from year to year, which complicates the forecast. In the figure below we can see sales across years 2008 through 2015. This is data pulled from their archival sales database for testing purposes. The data can be access in the corresponding file (Sales.xlsx). Please address the questions on the proceeding page for them in your written response. Bottles Sold 25,000 20,000 15,000 10,000 5,000 Sales for Napa Valley Winery by year 0 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2008 2009 2010 2011 -2012 2013 2014 2015 List of Questions to Answer 1. Develop a linear regression trendline for the data provided from (2008-2015) to provide a regression equation. 2. Use the regression equation from question 1 to obtain forecasted sales (FT) for Jan 2015-Dec 2015 (i.e. months 85-96). Insert your forecasted sales in the "Regress Sale Forecast (FT)" column in the Table 1 below. Next, calculate the forecast errors for Jan 2015-Dec 2015 (i.e. months 85-96). Insert your forecast error values in the "Forecast Error (FET)" column in Table below. For your submitted you can simply copy and paste the completed Table 1. Table 1: 2015 Sales Data and Regression Forecast Actual Regression Forecast Sales Sales Error (FET) Month (AT) Forecast Year Month # (T) (FT) 2015 Jan 85 12,287 Insert Insert 2015 Feb 86 11,519 Insert Insert 2015 Mar 87 12,767 Insert Insert 2015 Apr 88 13,235 Insert Insert 2015 May 89 13,643 Insert Insert 2015 Jun 90 13,552 Insert Insert 2015 Jul 91 13,349 Insert Insert 2015 Aug 92 10,240 Insert Insert 2015 Sep 93 14,781 Insert Insert 2015 Oct 94 17,123 Insert Insert 2015 Nov 95 20,396 Insert Insert 2015 Dec 96 23,609 Insert Insert 3. Use the data obtained in Table 1 to calculate the Mean Absolute Percentage Error (MAPE) for 2015 when using the Regression method to forecast sales. 4. Use exponential smoothing with an alpha value of a = 0.50 to obtain forecasted sales (FT) for Jan 2015-Dec 2015 (i.e. months 85-96). Note that you will need the Actual Sales (AT) from Dec 2014 (i.e month 84) and a forecast from month 84 (use the Nave Method) to get the first exponential smoothing forecast. These values can be obtained from the data file provided. Insert your forecasted sales in the "Exponential Smoothing Sales Forecast (FT)" column in the Table 2 below. Next, calculate the forecast errors for Jan 2015-Dec 2015 (i.e. months 85-96). Insert your forecast error values in the "Forecast Error (FET)" column in Table below. For your submitted can simply copy and paste the completed Table 2. Table 2: 2015 Sales Data and Exponential Smoothing Forecast Exponential Forecast you Actual Sales Smoothing Error (FET) (AT) Sales Month Forecast Year Month # (T) (FT) 2015 Jan 85 12,287 Insert Insert 2015 Feb 86 11,519 Insert Insert 2015 Mar 87 12,767 Insert Insert 2015 Apr 88 13,235 Insert Insert 2015 May 89 13,643 Insert Insert 2015 Jun 90 13,552 Insert Insert 2015 Jul 91 13,349 Insert Insert 2015 Aug 92 10,240 Insert Insert 2015 Sep 93 14,781 Insert Insert 2015 Oct 94 17,123 Insert Insert 2015 Nov 95 20,396 Insert Insert 2015 Dec 96 23,609 Insert Insert 5. Use the data obtained in Table 2 to calculate the Mean Absolute Percentage Error (MAPE) for 2015 when using the Exponential Smoothing method to forecast sales. 6. Which method would you recommend for Napa Valley Winery to use to forecast their sales moving forward? Justify your answer with a detailed explanation.