MIS614 Park University

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:All the optimization variables listed in 3.What is the total cost of shipping and processing?Which factories are planned to use all of their capacity?Which distribution centers will use all of their capacity?

Plant
Annual Capacity
Kansas City
55000
Houston
45000
Los Angeles
38000
Shipping Costs ($ per skateboard)
Houston
Los Angeles
Kansas City
Maryland
Iowa
$20.00
$30.00
$30.00
$40.00
$35.00
$45.00
Idaho Arkansas
$25.00
$42.00
$30.00
$44.00
$35.00
$37.00
Just Sports
Sports’N Stuff
The Sports Dude
Maryland
Iowa
$20.00
$30.00
$27.50
$32.50
$30.00
$40.00
Idaho Arkansas
$35.00
$27.50
$40.00
$25.00
$32.50
$42.50
DC
Iowa
Maryland
Idaho
Arkansas
Annual Capacity
80000
45000
38000
50000
DC
Iowa
Maryland
Idaho
Arkansas
Processing Cost ($/skateboard)
$1.54
$1.82
$1.70
$1.89
Retailer
Just Sports
Sports’N Stuff
The Sports Dude
1
45000
38000
31600
2
20100
49500
42000
Year:
3
4
20000
20400
50900
49000
42500
50400
5
19800
54300
52400
6
19500
49000
57900
7
20500
49400
65400