this course is information systems and business analytics . i have attached the excel file . please solve every question

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 lowa, 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. - Skateboards (Excel File) Shipping Costs (S per skateboard) \begin{tabular}{lr} \multicolumn{1}{r}{ DC Annual Capacity } \\ Iowa & 80000 \\ Maryland & 45000 \\ Idaho & 38000 \\ Arkansas & 50000 \end{tabular} \begin{tabular}{lr} \multicolumn{1}{c}{ DC } & Processing Cost (\$/skateboard) \\ lowa & $1.54 \\ Maryland & $1.82 \\ Idaho & $1.70 \\ Arkansas & $1.89 \end{tabular} \begin{tabular}{lccccccc} & \multicolumn{7}{c}{ Year: } \\ Retailer & 1 & 2 & 3 & 4 & 5 & 6 & 7 \\ Just Sports & 45000 & 20100 & 20000 & 20400 & 19800 & 19500 & 20500 \\ Sports'N Stuff & 38000 & 49500 & 50900 & 49000 & 54300 & 49000 & 49400 \\ The Sports Dude & 31600 & 42000 & 42500 & 50400 & 52400 & 57900 & 65400 \end{tabular} 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. 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: 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