Questions:
Question 1: Formulate the integrated sourcing, production and distribution problem as a linear program.
In particular, specify your decision variables, objective function, and constraints. Briefly discuss any key assumptions used to finalize your LP formulation.
Next, encode your LP model in Excel. To help you with your analysis, I will post an Excel template, but you can also initially choose to build your own model from scratch. You will use Excel Solver to compute the optimal solution, and then conduct further analysis using the Sensitivity Report and by modifying the base model or building a new model.
Question 2: Based on the optimal solution of this model, what is the annual sourcing and production cost? What is the total annual distribution cost?
Hint: If Excel reports "no solution," change the demand constraints to inequalities. That is, instead of specifying that the amount supplied to each market must exactly equal demand, you now specify that the amount supplied to each market must be at least as much as the demand.
Question 3: Instead of the integrated approach, where you minimize the total costs for production, sourcing and distribution, how would your results change if you adopted a hierarchical approach, in which you first optimize the production and sourcing costs (ignoring the distribution costs) and then, optimize the distribution costs (based on the optimal sourcing and production decisions you came up with)? Which approach is more advantageous? Briefly discuss the differences without actually solving for the hierarchical approach.
Attachment:- Kangaroo Oil Company.rar