Sports of All Sorts needs to forecast the demand at each of its four retail loca

Sports of All Sorts needs to forecast the demand at each of its four retail locations for next year, and then plan how to produce and distribute its product from the factories through the DCs to the retailers. In the Excel data, add another factory and set its capacity in a range between minimum and maximum capacities of other factories, use the city where you were born for this new factory’s location. Add a row for shipping costs from this new factory adequately adjusted to the distances between it and the DCs.

The attached files contain five tabs: Plant Capacities, Transportation Costs, DC Capacities, DC Processing Costs, and Historical Demand.

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.
2. Plot regression model (trend lines) on scatter plots for each retailer.
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 are given.
4. Solve the linear programming model using Solver Add-in in Excel. In your report include the following:
   1. All the optimization variables listed in 3.
   2. What is the total cost of shipping and processing?
   3. Which factories are planned to use all of their capacity?
   4. Which distribution centers will use all of their capacity?