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 $320 per year. This product can be shipped in either 3 or 7 day shipping, with the faster shipping costing $1.75 more per product. Using the slower method would be how much more expensive per product than the faster method? (5 pts) 2) Auto sales over a 4-month period were forecasted as follows: 88, 99, 105, and 97. The actual results over the 4-month period were as follows: 93,97,100, and 100 . What was the MAD of the 4-month forecast? (5 pts) 3) The errors in a particular forecast are as follows:3, -3, 5, 0, -2. What is the tracking signal for each period of the forecast? ( 10pts ) 4) Given the following sales data: Compute the seasonal index for each quarter and forecast quarterly demand for year 3 with a projected annual demand of 1100 . (10 pts) 5) The efficiency of a production unit is 80 percent. The unit produces an average of 1000 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 180 units per hour and an effective capacity of 150 units per hour. Find the efficiency of the machine. (10 pts) 7) FloorsRUs is considering new locations for its manufacturing plants. Costs for constructing a new facility in Hays are $1,250,000 and the company estimates that for every product from its new line, there would be an additional cost of $4. If the company were to locate in Wichita, the new facility would cost $1,800,000 and each product would incur a $3 incremental cost. The company plans to sell the products to retailers for $7. a. What is the break-even point for each location? (10 pts) b. At what volume is the point of indifference? (10pts) c. At what forecasted volumes should FloorsRUs select Hays as their new manufacturing location and what volumes should the company select Wichita and why? (5 pts)