MGMT 3213 Part 1 Forecasting (30 pts) All analysis and calculations and report must be done in a single (ONE) Excel file. Put your name at the top of the worksheet Make Excel do all of the calculations. (Instructor must be able to see your cell-reference formulas.) Include report/answers below the forecasting calculations. Make sure answers are clear, complete and easy to find. Your report must include: a Presentation of forecasts b. Explanation of why you chose each of the methods Tom Simpson, Director of the Chamber of Commerce for Exeter township is investigating the past ten years of tourist visits to the area. The following data has been gathered on number of tourists who signed into the local information center. Year Number of tourists 1 700 2 248 4 5 458 1410 7 8 1629 1301 1455 1455 1989 9 9 10 Tom is interested in implementing a forecasting system and is investigating the following forecasting methods as possibilities: Simple moving average, weighted moving average, Stapie Exponential Smoothing. Regression Choose two of the above methods and calculate forecasts for Year 11 If you use Exponential Smoothing, begin with the assumption the Forecast for Year 9 was 1500 (choose your own alpha) Present your forecast results Explain why each of your chosen methods is appropriate for the data and time frame given HW 1 Part 2- Please complete all work in excel. Use excel to make the calculations (cells can be clicked on to view any formulas used) and be sure to identify your answer, including units. Upload one excel file for your submission. (70 pts) 1) One product has a holding cost of $225 per year. This product can be shipped in either 3 or 7 day shipping with the faster shipping costing $1.5 more per product. Using the slower method would be how much more expensive per product than the faster method? (5 pts) 2) Home sales over a 4-month period were forecasted as follows: 189, 198, 305, and 197. The actual results over the 4-month period were as follows: 191, 206, 301, and 210. What was the MAD of the 4-month forecast? (5 pts) 3) The errors in a particular forecast are as follows: 2,-1,5, 0,-3. What is the tracking signal for each period of the forecast? (10 pts) 4) Given the following sales data: Quarter Year 1 Year 2 360 440 2 580610 3 600 680 4 370 4 20 Compute the seasonal index for each quarter and forecast quarterly demand for year 3 with a projected annual demand of 2400. (10 pts) 5) The efficiency of a production unit is 80 percent. The unit produces an average of 500 products per day. Determine the effective capacity of the unit. (5 pts) 6) The utilization of a machine is 70 percent. The machine has a design capacity of 240 units per hour and an effective capacity of 200 units per hour. Find the efficiency of the machine. (10 pts) 7) Design Expo is considering new locations for its manufacturing plants. Costs for constructing a new facility in Birmingham are $10,100,000 and the company estimates that for every product from its new line, there would be an additional cost of $2. If the company were to locate in Ames, the new facility would cost $11,800,000 and each product would incur a $1 incremental cost. The company plans to sell the products to retailers for $12. a. What is the break-even point for each location? (10 pts) b. At what volume is the point of indifference? (10 pts) c. At what forecasted volumes should Design Expo select Birmingham as their new manufacturing location and what volumes should the company select Ames and why? (5 pts) MGMT 3213 Part 1 Forecasting (30 pts) All analysis and calculations and report must be done in a single (ONE) Excel file. Put your name at the top of the worksheet Make Excel do all of the calculations. (Instructor must be able to see your cell-reference formulas.) Include report/answers below the forecasting calculations. Make sure answers are clear, complete and easy to find. Your report must include: a Presentation of forecasts b. Explanation of why you chose each of the methods Tom Simpson, Director of the Chamber of Commerce for Exeter township is investigating the past ten years of tourist visits to the area. The following data has been gathered on number of tourists who signed into the local information center. Year Number of tourists 1 700 2 248 4 5 458 1410 7 8 1629 1301 1455 1455 1989 9 9 10 Tom is interested in implementing a forecasting system and is investigating the following forecasting methods as possibilities: Simple moving average, weighted moving average, Stapie Exponential Smoothing. Regression Choose two of the above methods and calculate forecasts for Year 11 If you use Exponential Smoothing, begin with the assumption the Forecast for Year 9 was 1500 (choose your own alpha) Present your forecast results Explain why each of your chosen methods is appropriate for the data and time frame given HW 1 Part 2- Please complete all work in excel. Use excel to make the calculations (cells can be clicked on to view any formulas used) and be sure to identify your answer, including units. Upload one excel file for your submission. (70 pts) 1) One product has a holding cost of $225 per year. This product can be shipped in either 3 or 7 day shipping with the faster shipping costing $1.5 more per product. Using the slower method would be how much more expensive per product than the faster method? (5 pts) 2) Home sales over a 4-month period were forecasted as follows: 189, 198, 305, and 197. The actual results over the 4-month period were as follows: 191, 206, 301, and 210. What was the MAD of the 4-month forecast? (5 pts) 3) The errors in a particular forecast are as follows: 2,-1,5, 0,-3. What is the tracking signal for each period of the forecast? (10 pts) 4) Given the following sales data: Quarter Year 1 Year 2 360 440 2 580610 3 600 680 4 370 4 20 Compute the seasonal index for each quarter and forecast quarterly demand for year 3 with a projected annual demand of 2400. (10 pts) 5) The efficiency of a production unit is 80 percent. The unit produces an average of 500 products per day. Determine the effective capacity of the unit. (5 pts) 6) The utilization of a machine is 70 percent. The machine has a design capacity of 240 units per hour and an effective capacity of 200 units per hour. Find the efficiency of the machine. (10 pts) 7) Design Expo is considering new locations for its manufacturing plants. Costs for constructing a new facility in Birmingham are $10,100,000 and the company estimates that for every product from its new line, there would be an additional cost of $2. If the company were to locate in Ames, the new facility would cost $11,800,000 and each product would incur a $1 incremental cost. The company plans to sell the products to retailers for $12. a. What is the break-even point for each location? (10 pts) b. At what volume is the point of indifference? (10 pts) c. At what forecasted volumes should Design Expo select Birmingham as their new manufacturing location and what volumes should the company select Ames and why? (5 pts)