MIS614 Unit 6 Case Study Doc

Unit 6: Knowledge and Skills Case Study Stephen Hill Park University MIS614 Unit 6 02/19/2023

1. Use simple linear regression to forecast the demand for the next year. Round your forecast to the nearest one thousand units (e.g., if your forecast is 12,303, round to 12,000 for use for the next question. Just Sports Slope= X 7 − X 1 = 7 − 1 =− 0.0244 Y 7 − Y 1 20500 − 45000 Where the equation of a simple line is given by y = mx + c (Hope, 2020). 20500 =(− 0.0244 ∗ 7 )+ c c = 20502 y =(− 0.0244 ∗ 8 )+ 20502 y 8 = 20410 Sports'N Stuff Slope= X 7 − X 1 = 7 − 1 = 0.0005263 Y 7 − Y 1 49400 − 38000 Where the equation of a simple line is given by y = mx + c 49400 =( 0.0005263 ∗ 7 )+ c c = 49399.996 y =( 0.0005263 ∗ 8 )+ 49399.996 y 8 = 49400 The Sports Dude Slope= X 7 − X 1 = 7 − 1 = 0.0001775 Y 7 − Y 1 65400 − 31600 Where the equation of a simple line is given by y = mx + c

The plot illustrates an increasing trend for The Sports Dude 3. Using the demand prediction construct a What-if spreadsheet in Excel to describe the shipping and processing costs if the number of skateboards to be produced in each factory, the number of skateboards then to be shipped to each DC and the number of shipped skateboards to each retailer is given. Shipping Plan From/To IO ML ID AR Total Capacity Detroit 150 200 0 0 350 350 Los Angeles 200 0 150 0 350 350 Austin 150 0 0 500 650 700 TOTAL IN 500 200 150 500 TO/ FROM IO ML ID AR Total Capacity JS 0 200 0 0 200 200 SN 0 0 0 500 500 500 SD 500 0 150 0 650 650 TOTAL OUT 500 200 150 500 When you change the value of cell $C$12 to 80 and do the optimization function, the total cost become $79,250. The value of cost per week is computed as $ 40000 = $ 800 50

The value of cost saving per week is computed as $ 79,625 − $ 79,250 = $ 375 In this case, the savings are less than the investment. Therefore, the plan is rendered not viable. 4. Solve the linear programming model using Solver Add-in in Excel. In your report include the following: A. All the optimization variables listed in 3. B. What is the total cost of shipping and processing? C. Which factories are planned to use all of their capacity? D. Which distribution centers will use all of their capacity?