ITIS 1P97 FALL 2019 GROUP ASSIGNMENT #2 (100 points and 7.5% of Final Grade) Due Date: Nov 15, 2019 @11:55 PM THROUGH SAKAI INSTRUCTIONS: 1. This is a group assignment. A group must have BETWEEN 4 AND 6 MEMBERS within the same section. Individual assignments are not acceptable. The same group who worked on assignment 1 can work on the second assignment as well. 2. There are four problems in this assignment. Use Microsoft Excel to solve the problems. If the answers to the questions need to be supported with written arguments or explanations, you must do it in the file itself. 3. Answer each problem in a separate worksheet. 4. The file that you will be submitting has to have a coversheet (use the first worksheet in the Excel workbook that you will be submitting as a coversheet) with the following information: a. Group members first and last names, student numbers, and the course and section numbers. b. The contribution of each member towards the assignment out of 100%. Marks to individual members will be allocated based on this contribution rate. If a student has contributed 40% towards the project and the total mark of the project was 80, the student gets 32 points (40% of the 80 total). If members contributed equally (100% each), then all of them will get the total project mark as their individual mark. c. Assignments not having a coversheet will be deducted 25% of the assignment grade. 5. Verify that your assignment file is submitted properly through the course website. 10% deduction per day will apply to late submissions; wrong files will automatically receive a grade of zero. ITIS 1P97 FALL 2019 PROBLEM 1: (36 marks) The Fowler Martial Arts Academy trains young boys and girls in selfdefense. Joan Fowler, the owner of the academy, notes that monthly revenue is higher when school is in session but quite low when school is out (because many children are away on vacation or at summer camp). She has researched revenues for the past four years and obtained the information shown in the Table below. Answer the following questions based on the data provided in the table. Month January February March April May June July August September October November December Year 1 $59,042.00 $62,659.00 $22,879.00 $29,946.00 $26,839.00 $19,134.00 $20,051.00 $19,625.00 $19,925.00 $58,435.00 $87,705.00 $77,430.00 Year 2 $57,495.00 $62,622.00 $24,273.00 $30,492.00 $28,237.00 $17,893.00 $21,126.00 $22,876.00 $22,641.00 $60,796.00 $87,815.00 $78,711.00 Year 3 $56,583.00 $66,438.00 $27,766.00 $31,600.00 $29,589.00 $20,115.00 $19,324.00 $23,486.00 $24,719.00 $60,666.00 $86,693.00 $80,056.00 Year 4 $55,658.00 $67,164.00 $27,795.00 $30,667.00 $31,962.00 $21,096.00 $22,778.00 $23,144.00 $26,601.00 $61,385.00 $88,581.00 $81,048.00 a) Forecast the revenue for January of Year 5 using the moving averages method. Consider three different numbers of periods for the moving average calculation (4, 6, and 8 months). (6 marks) 1. Do the forecasts for January Year 5 change if you change the number of periods to be used for calculating the moving average? Enumerate the three different forecasts for January Year 5. (1 mark) 2. Plot both the actual revenue and the predicted revenue for all the three moving average calculations (You will have four plots in total on one chart). (1 mark) 3. Visually inspect the plots for all three of the moving average predictions (i.e. moving averages based on 4, 6, and 8 months). a. What are the advantages of using a small number of periods in the moving average calculations? What are the disadvantages? (1 marks) b. What are the advantages of using a large number of periods in the moving average calculations? What are the disadvantages? (1 marks) 4. Which model is the best (the one based on a 4, 6, or 8 months moving average)? Why? Show your calculations. (2 marks) b) Forecast the revenue for January of Year 5 using the exponential smoothing method. Consider three different smoothing constants for the calculations: 0.3, 0.5, and 0.8. (6 marks) 1. Do forecasts for January Year 5 change if you change the smoothing constant values in your calculations? Enumerate the three different forecasts for January Year 5. (1 mark) 2. Plot both the actual revenue and the predicted revenues for all the three calculations (You will have four plots in total on one chart). (1 mark) Page 2 of 6 Pages ITIS 1P97 FALL 2019 Visually inspect the plots for all three predictions (i.e. predictions based on smoothing constant values of 0.3, 0.5, and 0.8). (i) What are the advantages of using a small value for the smoothing constant? What are the disadvantages? (1 mark) (ii) What are the advantages of using a large value for the smoothing constant? What are the disadvantages? (1 marks) 4. Which model is the best (the one based on alpha 0.3, 0.5, or 0.8)? Why? Show your calculations. (2 marks) c) Using the decomposition method, forecast revenues for each month of Year 5. (12 marks) d) Using multiple regression analysis, forecast revenues for each month of Year 5 (8 marks) e) Compare the models, the best models from a and b, and the models based on regression analysis and the decomposition method, and choose the best one. Which model is the best? Why? Show your calculations. (4 marks) 3. PROBLEM 2: (21 Marks) Agri-Pro is a company that sells agricultural products to farmers in a number of provinces. One service that it provides to customers is custom feed mixing, for which a farmer can order a specific amount of livestock feed and specify the amount of corn, grain, and minerals that the feed should contain. This is an important service because the proper feed for various farm animals changes regularly depending upon the weather, and pasture conditions, for example. Agri-Pro stocks bulk amounts of four types of feeds that it can mix to meet a given customer's specifications. The following table summarizes the four feeds; their composition of corn, grain, and minerals; and the cost per kilogram for each feed type: Nutrient Corn Grain Minerals Cost per kilogram Feed 1 20% 25% 30% $0.25 Percent of Nutrient Feed 2 Feed 3 15% 20% 30% 25% 20% 20% $0.30 $0.32 Feed 4 10% 15% 30% $0.15 Agri-Pro has just received an order from a local chicken farmer for 8,000 kilograms of feed. The farmer wants the feed to contain at least 20% corn; at least 15% grain; and at least 15% minerals. a) Develop the model in Excel. (10 marks) b) Solve the problem of finding the least-cost feed mix. Give the quantities of the individual feeds included and the total daily cost. (2 marks) c) In what nutritional elements will you have a surplus? In what quantities? (2 marks) d) Create a Sensitivity Report and answer the following questions: (i) Suppose we forced Feed 3 to be in the solution, what would happen to the objective function's value? (2 marks) Page 3 of 6 Pages ITIS 1P97 FALL 2019 (i) What is are the max and min values of the objective function coefficients for Feed 1; Feed 2; and Feed 4 for which the current optimal solution is valid? (3 marks) (ii) What do the shadow prices for the corn and grain constraints mean? What are the maximum and minimum values for the corn and grain constraints for which the current constraint value does not change? (2 marks) Hint: When you define your decision variables use thousands of kilograms as the units. PROBLEM 3: (13 marks) An insurance company has three secretaries, A, B, and C that each is capable of processing four different types of insurance claims. The amount of time required by each secretary to process a particular type of a claim is summarized in the following table. Secretary A B C Processing Time in Hours Claim Type 1 2 3 4 2 3 2 4 4 5 3 1 3 2 1 5 On a typical week, the insurance firm has 5 type 1 claims, 4 type 2 claims, 2 type 3 claims, and 3 type 4 claims. Each secretary works a maximum of 40 hours per week. The office manager wants to know how many of each type of an insurance claim should be processed by each secretary to minimize the total processing time. Page 4 of 6 Pages ITIS 1P97 FALL 2019 Use the Sensitivity Report to answer the following questions: A) What is the total minimal processing time in hours? (1 mark) B) Suppose that secretary A can process a type 2 claim in two hours rather than three hours. How would this impact the current optimal solution? (1 mark) C) Assume that secretary C can process a type 4 claim in 6 hours rather than 5 hours. How would this impact the current optimal solution? (1 mark) D) Currently, secretary C is not processing any type 4 claims. Suppose that we force secretary C to process one type 4 claim. What impact would this have on the optimal solution? (2 mark) E) Which constraints are binding? (2 mark) F) Suppose that secretary A has a total of 45 weekly hours. What impact would this have on the current optimal solution? (2 mark) G) What is the total number of unused weekly hours for secretary B? (2 marks) H) Suppose that the number of type 4 claims increases to 4. What impact would this have on the current optimal solution? (2 marks) PROBLEM 4: (30 Marks) Management of Charlottesville Bank is concerned about a loss of customers at its main office down- town. One solution that has been proposed is to add one or more drive-through teller windows to make it easier for customers in cars to obtain quick ser- vice without parking. Neha Patel, the bank president, thinks the bank should only risk the cost of installing one drive-through window. She is informed by her staff that the cost (amortized over a 20-year period) of building a drive-through window is $36,000 per year. It also costs $48,000 per year in wages and benefits to staff each new drive-through window. The director of management analysis, Robyn Lyon, believes that two factors encourage the immediate construction of two drive-through windows, however. According to a recent article in Banking Research magazine, customers who wait in long lines for drive-through service will cost banks an average of $3 per minute in loss of goodwill. Also, adding a second drive-through window will cost an additional $48,000 in staffing, but amortized construction costs can be cut to a total of $60,000 per year if the two drive-through windows are installed together instead of one at a time. To complete her analysis, Lyon collected arrival and service rates at a competing down- town bank's drive-through windows for one month. These data are shown in the following table: Time Between Arrivals (Minutes) 1 2 3 4 5 Number of Occurrences 200 250 300 150 100 Service Time (Minutes) 1 2 3 4 5 6 Page 5 of 6 Pages Number of Occurrences 100 150 350 150 150 100 ITIS 1P97 FALL 2019 A. Simulate a 1-hour time period, from 1 PM to 2 PM, for a single-teller drive through. Replicate the model 200 times. (12 marks) B. Simulate a 1-hour time period, from 1 PM to 2 PM, for a single line of people waiting for next available teller in a two-teller system. Replicate the model 200 times. (12 marks) C. Conduct a cost analysis of the two options (based on the 200 replications). Assume that the bank is open 7 hours per day and 200 days per year. (6 marks) Page 6 of 6 Pages