Please only accept the question after understanding the assignment. The instructions will be provided below, I added the required factory to the data as specified in the directions. I also did the forecasting of demand for year 8 (find in Regression Analysis sheet). The results of the regression are as follows: Just Sports = 13,000. SportsN Stuff 54,000. The sports Dude = 69,000.
I’m stuck in Q3 & Q4 of the assignment. How do I use the forecasted demand to do what-if analysis? I can’t figure out the prep before being able to run the what-if analysis. Like what data to use, how to set up a table, any formulas? Please help. Same for Q4, what do I do? Please explain briefly the steps/formulas as you solve the two questions, so I can replicate and write a report.
CASE STUDY
Sports of All Sorts produces, distributes, and sells high-quality handcrafted skateboards. Its supply chain consists of three factories that produce the skateboards (located in Kansas City, Houston, and Los Angeles).
The skate-boards must be shipped from the factories to one of four distribution centers, or DCs, located in Iowa, Maryland, Idaho, and Arkansas.
Because of differences in labor rates, the processing and handling charge at each DC is different. Each distribution center can process (repackage, mark for sale, and ship) at most 55,000 skateboards per year. Skateboards are then shipped from the distribution centers to retailers. Sports of All Sorts supplies three major U.S. retailers: Just Sports, Sports ’N Stuff, and The Sports Dude. The following tables display the per unit costs for shipping skateboards between the factories and DCs and for shipping between the DCs and the retailers.
Directions
Respond in detail to each question. Review the rubric prior to responding. Prepare a managerial report as described below.
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.
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.
Plot regression model (trend lines) on scatter plots for each retailer.
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.
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?
Requirements: