Question

Three suppliers located in Baltimore, St. Louis, and Kansas City provide four customers with a special component every year. These customers are located in Cleveland, Charlotte, Atlanta, and Houston. The yearly requirements of these customers are given below. Annual Requirements (in thousand units) Cleveland Charlotte Atlanta Houston 16 20 25 34 Suppliers’ annual production capacity will be decided by your student number. The third, fourth, and fifth of your student number will be the capacity of the supplier in Baltimore, the sixth and seventh of your student number represent the capacity of the supplier in St. Louis, and the eighth and ninth of your student number decide the capacity of the supplier in Kansas City. Below is an example of suppliers’ capacity if your student number is A01234567 (Please change each supplier’s capacity based on your student number.) Capacity (in thousand units) Baltimore St. Louis Kansas City 123 45 67 The unit cost for supplying each customer from each supplier is given below (in $/unit). Baltimore St. Louis Kansas City Cleveland 97 100 93 Charlotte 132 85 90 Atlanta 125 91 90 Houston 104 112 115 Slippery Rock University Spring 2020 Page 2 of 2 a) If all customers' demand should be satisfied, use Linear Programming technique to formulate a mathematical model to minimize total cost. Solve the model in excel with your objective function colored in green, decision variables colored in yellow, and resource utilization colored in blue (15 pts). Please also prepare a short statement to explain your model results to your manager (5 pts). b) In a separate worksheet, suppose all customers' demand will be tripled next year and remain at this new level for a relatively long period of time. Solve the problem again in excel. Is the solution feasible and why? (10 pts) c) In another worksheet, with new demand requirements (all tripled), consider the situation in which 1) Requirements coming from Houston must be satisfied 2) All suppliers need to operate at full capacity to satisfy demand as much as possible 3) Total cost should be minimized Solve this problem in excel and answer the following questions. i. What’s your new minimum total cost? (15 pts) ii. Please create a designated row or column, named unmet demand, in excel to indicate whether all customers’ demand would be satisfied. (5 pts) iii. Please create a designated row or column, named excess capacity, in excel to illustrate whether all suppliers are operating at full capacity. (5 pts) iv. Prepare a short statement to explain your model results to your manager. (5 pts) d) If the solution for problem c) is not feasible, consider the scenario in which you have the option to increase supplier’s capacity by 50,000 units with a cost of $25,000 for St. Louis and $23,000 for Kansas City, while increasing capacity by 100,000 units will cost $45,000 in St. Louis and $49,000 in Kansas City. Baltimore only has the option to increase capacity by 30,000 units at a cost of $20,000. Moreover, each supplier can have at most one option to increase its capacity and all customers' demand should be satisfied. Please develop a mathematical model to help your manager to decide which option or combination is the best in order to minimize total cost. Write down your algebraic formulation in a worksheet (Objective function, decision variables, and constraints). (10 pts) e) Solve the model you developed for d) in a separate worksheet and answer the following questions. i. What’s your new minimum total cost? (15 pts) ii. Please create a designated row or column, named unmet demand, in excel to indicate whether all customers’ demand would be satisfied. (5 pts) iii. Please create a designated row or column, named excess capacity, in excel to illustrate whether all suppliers are operating at full capacity. (5 pts) iv. Prepare a short statement to explain your model results to your manager. (5 pts)

Answer 1

Now Use Solver
i am adding my Excel's Screenshot




These are the parameters of my Solver
if you don't know how to use Solver
Go to data option in excel and click There is option of Solver on the top right most corner

Don't forget to change Solving Method to Simplex LP
I am high lighting the same in my next Image

I am adding the solution of the solver in my next image





It is clear that b4 =34 ; l2=20; k1=16, k3=25 ;
The Final cost is 8974