Question

17. The demand pattern for a product at for consumer centers, A, B, C and D are 5000 units, 7000 units, 4000 units and 2000 units respectively. The supply for these centers is from| three factories X, Y and Z. The capacities for the factories are 3000 units, 6000 units and/ 9000 units respectively. The unit transportation cost in rupees from a factory to consumer center is given below in the matrix. Develop an optimal transportation schedule and find the optimal cost. From Toe e A B C D fe X | 8 | 9 | 12 | 8° | Ye 3 4 34 22 | Ze 5 3 7 4 í Į Į

Answer 1

Below is the screenshot of the LP table -

Page Layout Formulas Data Review View ESS- File Home Insert | Cut EB Copy Format Painter Clipboard Calibri 11 VAA = = = 0 BIU - - Paste Font Aligi A1 X B for C D E A B C D From х 31 Supply Available 3000 6000 9000 4 2 6 Units Demand 5000 7000 4000 2000 8 LP Table From D Supply Available 0 13 Units Demand 0 Cost

Below is the screenshot of the formula applied -

Book2 - Excel ESSE File Home Insert Page Layout Formulas Data Review View Help * ? Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - Used - - Time Reference Trigº Functions Manager Function Library fx A .l le - Tell me what you want to do Define Name Ft Use in Formula Create from Selection Defined Names - Trace Precedents 5 Show Formulas Trace Dependents ! Error Checking Remove Arrows - Evaluate Formula Formula Auditing Wat Wind A1 A From Supply Available N00 vw 3000 6000 9000 Units Demand 5000 7000 4000 2000 LP Table From Supply Available =SUM(B10:E10) =SUM(B11:E11) =SUM(B12:E12) Y Units Demand =SUM(B10:B12) =SUM(C10:C12) =SUM(D10:012) =SUM(E10:E12) Cost =SUMPRODUCT(B3:E5, B10:E12)

Below is the screenshot of the solver -

Book2 - Excel Abhishek F Review View Hel Solver Parameters ES File Home Insert Page Layout Formulas Data Show Queries Connections From Table e Properties Get External New Refresh Data Query - Lo Recent Sources All - Edit Links Get & Transform Connections Set Objective: $B$15 ĀL Sort Filter To: O Max Min O Value Of: Sort & Filter 5 By Changing Variable Cells: $B$10:SE$12 А B D E From T A T C 12 3 Add Subject to the Constraints: $B$10:$E$12 = integer $B$13:$E$13 >= $B$6:SES6 SF$10:SF$12 <= $F$3: SFS5 D 8 Supply Availabl 3000 6000 9000 2 Change 5 5000 Delete 6 Units Demand 7000 4000 2000 Reset All LP Table From 9 D Supply Available Load/Save loo Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options 13 Units Demand Cost Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help | Solve T Close

Below is the screenshot of the result -

Review View He Sao File Home Insert Page Layout Formulas Data Show Queries Connections From Table Properties Get External New Refresh Data Query - Le Recent Sources All Edit Links Get & Transform Connections AL ZA Al Sort Filter Sort & Filte A1 А B olo E E m 2 From WO 000 Supply Available 3000 6000 9000 12 3 7 4000 13 7000 2 4 2000 6 Units Demand 5000 To LP Table From 0 4000 3000 0 2000 5000 0 0 7000 7000 D 0 2000 0 2000 Supply Available 3000 6000 9000 13 Units Demand 4000 15 Cost 71000 16