1 Quarter Actual (Metric tons) Prediction 2 1990 Q1 6562 HW 1 - Business analytics. 3 1990 Q2 7063 4 1990 Q3 7374 5 1990 Q4 6563 6 1991 Q1 6202 7 1991 Q2 6694 8 1991 Q3 6748 9 1991 Q4 6160 10 1992 Q1 5777 11 1992 Q2 5853 12 1992 Q3 6416 a. Use the Generalized Analytics Procedure (GAP) to set up your problem as follows: i. Define your model in words 1. Identify the objective function in words 2. Identify the random variables in words 3. Identify the decision variables in words ii. Formulate your model mathematically 1. Define the decision variables 13 1992 Q4 5825 2. Define the random variables 14 1993 Q1 5724 15 1993 Q2 6036 16 1993 Q3 6570 17 1993 Q4 5675 18 1994 Q1 5311 19 1994 Q2 5717 20 1994 Q3 7000 21 1994 Q4 6085 22 1995 Q1 4714 23 1995 Q2 3939 24 1995 Q3 6137 25 1995 Q4 4739 26 1996 Q1 4275 27 1996 Q2 5239 28 1996 Q3 6293 29 1996 Q4 5575 30 1997 Q1 4802 31 1997 Q2 5523 32 1997 Q3 5708 33 1997 Q4 4821 34 1998 Q1 4919 35 1998 Q2 3. Define the objective function in terms of decision variables and random variables b. Solve the problem in Excel c. Answer the questions stated in the problem (in words). If you make any additional assumptions, state them clearly. Tab "training data" of HW1 spreadsheet.xlsx spreadsheet contains 1990-2001 quarterly Australian Tobacco Production (in metric tons). Use the following three forecasting methods (see Lecture 1 slides for details) to forecast the production in the 10 quarters starting with Q1 2002, using the 1990-2001 data as your training data: Simple Average Nave Seasonal Seasonal Average Create a line graph in Excel showing the predictions from the three forecasts. In addition to the forecasts, include the actual data (from 1990 to 2004) in your graph. Which method do you expect to perform best/worst? 5809 36 1998 Q3 5904 37 1998 Q4 4555 38 1999 Q1 5198 39 1999 Q2 5388 40 1999 Q3 5142 41 1999 Q4 5517 -42 2000 Q1 5169 43 2000 Q2 4860 44 2000 Q3 5185 45 2000 Q4 4763 46 2001 Q1 4217 47 2001 Q2 4959 48 2001 Q3 5196 49 2001 Q4 4522 50 2002 Q1 3843 51 2002 Q2 4806 52 2002 Q3 5280 53 2002 Q4 4709 54 2003 Q1 4362 55 2003 Q2 5210 56 2003 Q3 5258 57 2003 Q4 4526 b) You have been tasked to select the best of the three methods to forecast the production in the next 10 quarters (Q1 2002 - Q2 2004). Which method do you prefer? Answer this question comparing the forecasts from each method against the actual (realized) production quantities found in Q1 2002 - Q2 2004 displayed in tab "full data". Follow the procedure on the previous page (in red), and an evaluation method of your choice (MAD or MSE). c) The Australian government is planning to collect a tax on tobacco production. Specifically, the tax will be $100 per metric ton in 2002, and $200 per metric ton in 2003-2004. Based on your best prediction, what will be the total tax revenue? Use the "SUMPRODUCT" formula to obtain your result. Note: Your submission should include a screenshot showing how you used the "SUMPRODUCT" formula (You can display all formulas in an Excel spreadsheet by going to Formulas tab/Show formulas). d) (Bonus not required to receive full points for this assignment) Come up with a method that outperforms the methods used in a)/b). 58 2004 Q1 3974 59 2004 Q2 5027 HW 1 - Business analytics. < Due Week 2 before class (See Blackboard). Late submissions receive 2pt per day deduction. < To solve part b) below, you will need to do the following: < a. Use the Generalized Analytics Procedure (GAP) to set up your problem as follows: < i. Define your model in words < 1. Identify the objective function in words < 2. Identify the random variables in words < 3. Identify the decision variables in words ii. Formulate your model mathematically < 1. Define the decision variables 2. Define the random variables < 3. Define the objective function in terms of decision variables and random variables b. Solve the problem in Excel < c. Answer the questions stated in the problem (in words). < Please submit only one file in PDF format with your write-up. Do not submit your Excel file. Your writeup must include the screenshots from your Excel Spreadsheets. < If you make any additional assumptions, state them clearly