Question

This question needs to be answered using spreadsheets. Please include the following:

- objectives and objective function(s)

- constraints

- screenshot of solver

6.6 Stocking Warehouses: Nicklaus Razor Blade (NRB) Company plans to test market a new blade next month. The blades will be stocked in their three ware- houses in the following quantities Warehouse A B C Stock (cartons 50 50 50 The carton quantities required by the distributors in the four test markets are as follows. Distributor D E F G Requirement 45 15 25 20 The unit costs (in dollars per carton) of shipping the blades from warehouses to distributors are given in the table below 10 15 (a) If the NRB Company wishes to meet the distributor's requirements at the minimum total transportation cost, what is the optimal distribution plan and its cost? (b) Suppose that the policy at the NRB Company is that each distributor must be serviced by a single warehouse. What is the optimal distribution plan under this policy? (c) By how much (in percentage terms) does the single-warehouse policy inflate distribution cost?

Answer 1

(a) OBJECTIVE FUNCTION : MINIMIZE COST = $\sum$$\sum$ X_ijC_ij : i,j=1 to m,n

SUBJECT TO (CONSTRAINTS) : $\sum$ X_{ij =}S_ij for all i=1 to m

$\sum$ X_{ij =}D_ij for all i=1 to n

X_ij > 0

X_ij represent number of items shipped from warehouse i to distribution j

C_ij represent cost

(b) From the solution, we can already see that distributor E,F and H are serviced by only one distributor each.

For F , we have to find which warehouse results in least cost. So, we will minimize cost of F alone subject to supply and demand constraints as written above.

Minimum requirement for F is 25 out of which it takes 15 from A and 5 from B and C each. Now that it can only take from one, trying out combinations of each warehouse supplying to F in excel, we see the following results:

Cost(Total) QUESTION 10 COST/UNIT capacity 13 14 15 10 15 12 15 demand 25 20 17 SOLUTION 19 20 21 capacity SS constraint 35 COST/UNIT 15 50 50 50 20 25 2 3 3 70 23 24 25 demand dd constraints 15 25 20 45 15 25 20

In first and third image i.e supply done by A and C respectively, we see the supply constraint is not satisfied in each case

( NOTICE CELL H22 IS 70 WHICH EXCEEDS CONSTRAINT OF 50 in picture 3 of (b) AND CELL H20 IS 60 WHICH ALSO EXCEEDS SUPPLY CONSTRAINT OF 50 in picture 3 of (b))

This leaving us with only B supplying to F. Total cost is 635 which is (635-590=45) . Cost exceeds by 45 due to this rule.

(c) Distribution cost is inflated by( in %) = (NEW-OLD)/OLD *100 = ((635-590)/590)*100 =7.62 %