Question

Delivery cost/ton(S)P $24.00 30.00 $ 40.00 $30.00 40.00 $ 45.00 $ 18.00 $ 20.00 $ 24.00 $ 12.00 12.00 $ 28.00 Warehouse 1 Warehouse 2 Warehouse 3 Warehouse 4

VOOM has four warehouses 1, 2,3, and 4. VOOM needs to supply 100 Tons to retailer P, 300 Tons to retailer Q, and 300 tons to retailer R. The cost of delivery from each warehouse to retailers is provided in the following table. While each of the warehouses has an inventory of 400 tons they can supply, R requires that every warehouse supplies at least 25 tons to R.

    There is a special arrangement between Warehouse 4 and R which requires that Warehouse 4 has to supply exactly 100 tons to R. On the other hand, P cannot take more than 50 tons from warehouse 1 and 2.  

Use linear programming to provide VOOM a plan that will keep their transportation cost to the minimum.

Answer 1

We will use Excel- Solver to solution for this problem. Below screenshots shows detailed approach

LPnew Apr20xisx - Excel Share Insert Page Layout Formulas Data Review View Tell me what you want to do. Punoet Sabharwal Cut 團 | ..「Normal-Bad 9-5, Wrap Text = 11AA Calibn Good Copy Insert Delele Fumal Surl & Find & Check Cell Furnal Painter Formattin Table Ton dting N17 Warehouse 1 Warehouse 2 Warchousc 3 Warehouse 4 $10 45 530 S20 S12 $12 Demand from retailers in tons) Inventory of maximum 400 tons in cach warchousc R requires that every warehouse supplies at least 25 tons to R Warehouse 4 has tu supply exactly 100 toris tu R P cannot take more than 50 tons from warehouse 1 and 2. 100 10) 13 We now setup take for linear programming and used Excel-Solver to solution this problem 16 Units supplied from Warehouse to retailer Warchousc 1 Warehouse 2 Warehouse 3 Warehouse 4 17 0 SUMIC18:E18 400 a SUM C19:F19) 400 0surie 0 soame 25 25 23 SUM(C20:亡23) 26 Objective is to minimize total transportation cost SUMPROUUCTIC19:t22,C3:E6) MIN Sheet1 :14 AM 4/21/2019 OType here to search

LPnew Apr20xlsx- Excel Puneet Sabharwal Info Infe LPnew Apr20 C: a Usersa puneet44619 Open Downloads a Chegq shrut Protect Workbook Control what types of changes pecple can make to this workbook Sizc Savo As 12 3KD Add a title Add a tag Add a category Protect Titie Print Share Inspect Workbook Pefore publishing this file, he aware that it contains Related Dates Last Modified oday, 109 AM Created Last Printed Dooument properties, printer path, authors name and absolute path Content that pcople with disabilities find difficult to read Issues Today, 1241 AM Close Manage Workbook Check in, check cut, and recover unsaved changes Related People ManageToday Tnday, 1:09 AM (autnsavr) Today, 12-57 AM (autasave Puneet Sabharwal Add an author Last Modified by Browser View Options Pick what users can sce when this workhook is viwed on the We. Puneet Sabharwal Drowser View Related Documents Open File Lucation Show All Properties 1:11 AM 4/21/2019 OType here to search XB

LPnew Apr20xlsx- Excel Insert Page Layout Formulas Data Review View Tell me what you want to do. Home Punoet Sabharwal Σ AutoSum A Cut 2 Calibn AAExcel Options Insert Delele Fumal Surl & Find & Filter Select · ●Format Painter B , u·T·O·스 Clear Microsolt Office Add-irns. ting Ton N19 D Save ocation Active Application Add-ins Solver Add-in Delivery costtonS Warchousc 1 Warehouse 2 Warehouse 3 Warehouse 4 C..Office16 Library'SOLVERSOALVERXLAM Excel Adi-in S30 | Advanced 40 Custonizc Ribbon $24 Inactive Application Add-Ins Analysis ToolPak Analysis TaalPak-VBA CA Officbrary Analysis ANALYS32.XIL Excel Add in CA ffice16Library Analysist ATPVBAENXLAM Excel Adi-in $18 512 Quick Access Toolba CAS Microsoft Sharedart Tag MOFLDLL Action Euro Currency Tools Financial Symbal XMI) C.OtficeOttice16Librar EUROTOOLXLAM cel Add-in Demand from retailers in tons Iriventury ofraxinur 400 toris in each war R requires that every warehouse supplies at Warehouse 4 has to supply exactly 100 tons ρ cannot take more than 50 tons from Trust Center sMicrosoft Shared, Smart Tag MOFI.DII Action 300 T C.osoft Office Orfice 15% DCR NativeShimdll COM Add-in .FilesMcAfee DLP Agentia5cplott.dllCOM Add-in McAfee DLP Ortice Addin XML Expansion Pack Micrasaft Power Map far Exce Microsoft Power Pivet for Cxcel Microsoft Power View tor Excel ap Excel Adld-in EXCELPLLUGINSHFLLDLL COM Add-in C el Add-ini Power PivotExcelClientAddin.dll COM Add-in el Add-invAdHockeportingExcelClientdll COM Add-in We now setup take for linear p Document Related Add-ins 15 No Docuet Reloted Units supplicd from Warehouse to retailer Warehouse 1 Warehouse 2 Warehouse 3 Warchousc 4 Compatibility No compatibility information available Location: Program Files b80)Mirosott Ottice Ottice 16 Library SOLVERSOLVERXLAM Description: Teal for optimication and equation solving Go Manage Excel Add-ins SUM C20:C23) Objective is to ninimize total transportatian cost 19:E22,3:F6) Sheet1 ョ1 - 1:11 AM 4/21/2019 OType here to search

LPnew Apr20.xlsx- Excel Insert Page Layout Formulas Data Review View Tell me what you want to do. Punoet Sabharwal Cut 2 AutoSum A 團 | ..「Normal-Bad Calibn Good ECopy Insert Delele Fumal Surl & Find & Check Cell .Furmal Painter Formattin Table Fon dting N19 Add-ins Delivery costtonS Warchousc 1 Warehouse 2 Warehouse 3 Warehouse 4 ads-ns avalable: $24 natyi Toci VEA 518 512 528 Demand from retailers in tons Iriventury fraxinur 400 toris in each warehouse R requires that every warehouse supplies at least 25 tons to R. Warehouse 4 has to supply exactly 100 tons to K P cannot take morc than 50 tons from warchousc 1 and 2 10 14 15 16 We now setup take for linear programming and used Excel ->Solver to solution this problem Tool tor aptmazbon and equation sang Units supplicd from Warehouse to retailer Warehouse 1 Warehouse 2 Warehouse 3 Warchousc 4 18 19 20 21 0 SUMiC18:518) 400 0 SUM C19:E19) 400 0 100 same SUM C20 C23) 25 26 27 Objective is to ninimize total transportatian cost Sl/MPRODUCT(C19:E22,C3F5) Sheet1 ョ1ロー 1:11 AM Type here to search 4/21/2019

LPnew Apr20xlsx- Excel Rewiew View Tel me what you want to do.. Home Insert Page Layout Formulas Puneet Sabharwal Show Queries From Tabble Recent SourcesAldi Links Reapply rom From From Frum Olher Existing New Access Web Text Sources Connections Query Sont Filter Text to Flash Remove Dala Consolidale Relationships What-If Furecast Group Ungroup Sublotal Columns Fil Duplicates Validation Analysis Sheet Get Esternal Dala Get &Trs om Connestias Sort & rite. Dals Toals What-if analysis tool that finds the optimal value of a target cell by changing values in cells used to crallculate he target cell Warehouse 1 Warehouse 2 Warchousc 3 Warehouse 4 524 5olver Parameters $10 $45 S20 S12 SOLVER.XLAM Tell me mcre $12 Demand from retailers in tons) Inventory of maximum 400 tons in cach warchousc R requires that every warehouse supplies at least 25 tons to R Warehouse 4 has tu supply exactly 100 toris tu R P cannot take more than 50 tons from warehouse 1 and 2. 100 y Chargng Vrizble Cel 10) utject to the Conatraints 13 We now setup take for linear programming and used Excel-Solver to solution this problem 16 Units supplied from Warehouse to retailer Warchousc 1 Warehouse 2 Warehouse 3 Warehouse 4 Eeset All 17 0 SUMIC18:E18 400 a SUM C19:F19) 400 Suie 0 soame 25 25 25 GRG Nonlines Solving Methed 23 elect the CRS Naninear engne ter Sabver Problems that are smoath nanlinsar. Seect te LP Simpisx engine for inear Saver Prahlems and select the Evaiutionary encine far Saver preblems thar ane SUM(C20:亡23) 26 Objective is to minimize total transportation cost SUMPROUUCTIC19:t22,C3:E6) MIN 28 Sheet1 :14 AM 4/21/2019 OType here to search

LPnew Apr20xlsx- Excel Rewiew View Tel me what you want to do.. Home Insert Page Layout Formulas Puneet Sabharwal Show Queries From Tabble Recent SourcesAldi Links Reapply rom From From Frum Olher Existing New Access Web Text Sources Connections Query Sont Filter Text to Flash Remove Dala Consolidale Relationships What-If Furecast Group Ungroup Sublotal Columns Fil Duplicates Validation Analysis Sheet Get Esternal Dala Get &Trs om Connestias Sort & rite. Dals Toals G27 Salver Parameters Warehouse 1 Warehouse 2 Warchousc 3 Warehouse 4 524 $10 $45 S20 S12 Se otjective $12 Demand from retailers in tons) Inventory of maximum 400 tons in cach warchousc R requires that every warehouse supplies at least 25 tons to R Warehouse 4 has tu supply exactly 100 toris tu R P cannot take more than 50 tons from warehouse 1 and 2. 100 By Changing VaristleCells 10) Subjed to the Constraints: C322:5E523 C525 SES2 13 We now setup take for linear programming and used Excel-Solver to solution this problem 16 Units supplied from Warehouse to retailer Warchousc 1 Warehouse 2 Warehouse 3 Warehouse 4 Reset 시 17 0 SUMIC18:E18 400 a SUM C19:F19) 400 Suie 0 soame 25 25 slect a Sahing GRG Solvng Mathod Select the GRG Nonlinear enqine for Solver Problems that are smooth noninear. Select the LP Simplex engine for inear Sarer Froblems, and selest the Evoluticnary engine for Solver problems that ars 23 26 Objective is to minimize total transportation cost SUMPROUUCTIC19:t22,C3:E6) MIN 28 Sheet1 ョ圓ロ 1:16 AM 4/21/2019 OType here to search

LPnew Apr20xlsx- Excel Rewiew View Tel me what you want to do.. File Home Insert Page Layout Formulas Puneet Sabharwal 2Connections 2, Solver Show Queries From Tabble OueryRecent Sourcesl Sont Filer Reapply Sort & rite. rom From From Frum Olher Existing New Access Web Text Sources Connections Query Texl to Flash RenDala Consolidale Relationships What-I Furecast Gruup Urgroup Sublalal Edit Links Advanced AllEdi Links Columns Fill Duplicates Validation Dals Toals Analysis Sheet Get Esternal Dala Get &Trs om Connestias N17 Warehouse 1 Warehouse 2 Warchousc 3 Warehouse 4 524 $10 $45 Solver Results S20 S12 Solver found a solution. All Constraints and opt mality conditions are satisfied. $12 Demand from retailers in tons) Inventory of maximum 400 tons in cach warchousc R requires that every warehouse supplies at least 25 tons to R Warehouse 4 has tu supply exactly 100 toris tu R P cannot take more than 50 tons from warehouse 1 and 2. s100 Kosp 5o ver Salurtion O Restore Orginal Values 10) Rsturn to Sover Parameters Calag Outline Reports 13 We now setup take for linear programming and used Excel->So Save Scenarlo... 16 Units supplied from Warehouse to retailer Warchousc 1 Warehouse 2 Warehouse 3 Warehouse 4 Solver found solution. All Constraints and optimality conditions·re satisfied. 17 25 when the GRG engine is used, Solver has found at least a local optimal salution. When Simplex LP is used, this 25 25 mesns Solver has found aglobsl optimal solutio 100 23 100 300 26 Objective is to minimize total transportation cost 13925.000U6 SUMPROUCTC19:t22,C3:E6) MIN 28 Sheet1 ョ1 - 1:16 AM 4/21/2019 OType here to search

LPnew Apr20xisx - Excel Share Insert Page Layout Formulas Data Review View Tell me what you want to do. Punoet Sabharwal Cut lot Accounting ·團 9-5, Wrap Text = 11AA Calibn Accentó Copy omma 0)Currenc Insert Delele Fumal Surl & Find & Filter Select .Furmal Painter Clear Formattin Table Ton dting G27 Warehouse 1 Warehouse 2 Warchousc 3 Warehouse 4 $10 45 530 S20 S12 $12 Demand from retailers in tons) Inventory of maximum 400 tons in cach warchousc R requires that every warehouse supplies at least 25 tons to R Warehouse 4 has tu supply exactly 100 toris tu R P cannot take more than 50 tons from warehouse 1 and 2. 100 10) 13 We now setup take for linear programming and used Excel-Solver to solution this problem 16 Units supplied from Warehouse to retailer Warchousc 1 Warehouse 2 Warehouse 3 Warehouse 4 17 25 25 25 SUMIC18:E18) 400 25 SUMc19:F19) 400 250 me 400 some 25 100 25 23 100 26 Objective is to minimize total transportation cost Sheet1 ョ1 - 1:17 AM 4/21/2019 OType here to search

Optimal solution to minimize transport cost

Units supplied from Warehouse to retailer	P	Q	R
Warehouse 1	0	0	25
Warehouse 2	0	0	25
Warehouse 3	100	0	150
Warehouse 4	0	300	100

Minimum total transportation cost is

$   13,925.00

If you liked my answer, please give thumbs up/ likes. Thank you!