Linear Programming help. Must be able to use excel solver

CIS\t653-\t01 EXAM\t1\tQUESTIONS 1_ Mr.\tAdam\tis\ta\tsupervisor\tin\tthe production\tdepartment\tof\ta\tfurniture\tcompany.\tThe\tfirm\tproduces desks\tas\twell\tas\tchairs.\tThe\tproduction\tdepartment\tset\tthe\tprofit\tof a\tdesk at\t$80,\talso\tthey\tcalculated the\tamount\tof\tassembly\ttime\tof\teach\tdesk\tat\t3\thours, and\tthe\tfinishing\ttime\tof\ta\tdesk\tis\t4\thours. The unit\tprofit\tof\ta\tchair\tis\t$50,\tthe\tassembly\ttime\tof\teach\tchair\tis\tset\tat\t3\thours\tand\tthe\tthe\tfinishing\ttime\tof each\tchair\tis\tevaluated\tas\t2\thours\tby\tthe\tproduction\tdepartment. The\tsupervisor\tdecided\tthat\tthe maximum\tassembly\thours\tshould\tbe\t360\thours\tand\tthe\tavailable\tfinishing\ttime\tis\t240\thours.\tThe\ttotal production\tamount\tshould\tbe\tno\tmore\tthan\t1000\tpieces. The\tmanagement\twants\tat\tleast\t110\tchairs\tto be\tproduced. a)\tDefine\tthe\tdecision\tvariables, b)\tFormulte\tthe\tLP\tmodel\tof\tthe\tgiven\tproblem\t(objective\tfunction\tand\tthe\tconstraints), and c)\tSolve\tit\tby\tthe\tMS\tExcel\tSolver 2_ The\tETRADE\tBrokerage\tfirms\thas\tjust\tbeen\tinstructed\tby\tone\tof\tits\tclients\tto\tinvest $250,000\tfor\ther\tmoney\tobtained\trecently\tthrough\tthe\tsale\tof\tland\tholdings\tin\tToronto.\tThe\tclient\thas\ta good\tdeal\tof\ttrust\tin\tthe\tinvestment\thouse,\tbut\tshe\talso\thas\ther\town\tideas\tabout\tthe\tdistribution\tof\tthe funds\tbeing\tinvested.\tIn\tparticular,\tshe\trequests\tthat\tthe\tfirm\tselect\twhatever\tstocks\tand\tbonds\tthey believe\tare\twell\trated,\tbut\twithin\tthe\tfollowing\tguidelines: Municipal\tbonds\tshould\tconstitute\tat\tleast\t20%\tof\tthe\tinvestment; at\tleast\t40%\tof\tthe\tfunds\tshould\tbe\tplaced\tin\ta\tcombination\tof\telectronic\tfirms,\taerospace\tfirms,\tand drug\tmanufacturers.\tNo\tmore\tthan\t50%\tof\tthe\tamount\tinvested\tin\tmunicipal\tbonds\tshould\tbe\tplaced\tin a\tnursing\thome\tstock Subject\tto\tthese\tconstraints,\tthe\tclient\tgoal\tis\tto\tmaximize\tprojected\treturn\ton\tinvestments.\tThe\tanalysts at\tETRADE\tBrokerage\taware\tof\tthese\tguidelines,\tprepare\ta\tlist\tof\thigh-quality\tstocks\tand\tbonds\tand\ttheir corresponding\trates\tof\treturn Investment Projected Rate of Return % Municipal bonds 5.3 Electronics 6.8 Aerospace 4.9 Drugs 8.4 Nursing Homes 11.8 a)\tDefine\tthe\tdecision\tvariables, b)\tFormulte\tthe\tLP\tmodel\tof\tthe\tgiven\tproblem\t(objective\tfunction\tand\tthe\tconstraints), and c)\tSolve\tit\tby\tthe\tMS\tExcel\tSolver 1 3_ The load master for a freighter wants to determine the mix of cargo to be carried on the next trip. The ship's volume limit for cargo is 150,000 cubic meters, and its weight capacity is 6,000 tons. The master has five different types of cargo from which to select and wishes to maximize the value of the selected shipment. However, to make sure that none of his customers are ignored, the load master would like to make sure that at least 10% of cargo A and 10% of cargo B's available weight is selected. In addition, the total amount of cargo B, C, and D should be no more than 15% of the total amount of the shipment. The specifications for the five cargoes are shown in the following table. a)\tDefine\tthe\tdecision\tvariables, b)\tFormulte\tthe\tLP\tmodel\tof\tthe\tgiven\tproblem\t(objective\tfunction\tand\tthe\tconstraints), and c)\tSolve\tit\tby\tthe\tMS\tExcel\tSolver Cargo\tType Tons\tAvailable Total\tValue Volume\tper\tTon\t(cu.\tm.) A 1,000 $13,000 20 B 1,200 $16,000 45 C 2,900 $10,000 20 D 3,400 $85,000 35 E 4,600 $150,000 37 4_ LULU\tmanufacturing\tcompany\tproduces\toffice\tdesks\tin\tfactories\tA,\tB\tand\tC.\tThen\tit\tships\tthe\tproduced office\tdesks\tto\tthe\tstores\tin\tD\tand\tE.\tThe\tamount\tof\tsupply\tand\tdemand\tand\tthe\tunit\tshipping\tcost\tis given\tbelow. Determine\tthe\tnumber\tof\tdesks\tto\tbe\tshipped\tfrom\tfactories\tto\tthe\tstores\tin\torder\tto minimize\tthe\ttotal\tshipping\tcost a)\tDefine\tthe\tdecision\tvariables, b)\tFormulte\tthe\tLP\tmodel\tof\tthe\tgiven\tproblem\t(objective\tfunction\tand\tthe\tconstraints), and c)\tSolve\tit\tby\tthe\tMS\tExcel\tSolver Factories Stores Unit\tShipping\tCost\t($) A\t(300\tDesks) D\t(350\tDesks) B\t(250\tDesks) E\t(450\tDesks) C\t(250\tDesks) 2 From/To A B C D $11 $15 $8 E $9 $5 $14 5_ The\tfirm\tXYZ\tproduces\tcertain\tagricultural\tproducts\tsuch\tas\tsugar\tbeet,\twheat,\tand\tcotton.\tThere\tare three\tdifferent\tarable\tlands\tin\tthe\tregion.\tThe\ttable\tbelow\trepresents\tthe\tmaximum\tarable\tland\tin\tacre and\tavailable\twater\tresource\tin\tm3. Land Maximum\tarable\tland\t(acres) Maximum\tavailable\twater\t(m3) Land\t1 500 900 Land\t2 700 1000 Land\t3 800 1200 The\tfollowing\ttable\tshows\tthe\tmaximum\tquota\tin\tacres,\tunit\twater\tconsumption\tand\tunit\tprofit\tper acres\tof\teach\tproduct. Product Maximum\tquota Water\tconsumption Profit (acres) (m3/Acre) ($/Acre) Sugar\tbeet 750 5 1450 Cotton 850 3 1350 Wheat 950 2 1250 The\tmanagement\tof\tthe\tXYZ\tfirm\twould\tlike\tto\tdetermine\thow\tmany\tacres\tof\teach\tproduct\tshould\tbe planted\tin\teach\tland\tto\tmaximize\tthe\ttotal\tprofit. a)\tDefine\tthe\tdecision\tvariables, b)\tFormulte\tthe\tLP\tmodel\tof\tthe\tgiven\tproblem\t(objective\tfunction\tand\tthe\tconstraints), and c)\tSolve\tit\tby\tthe\tMS\tExcel\tSolver Hint: You\tmay\tdefine\tthe\tvariables\tas: As Land1 Land\t2 Land\t3 Sugar\tBeet X1\t(number\tof\tacres\tto plant\tsugar\tbeet\tin\tland1) X4... X7... Cotton X2... Wheat X3.... X5... X8... X6... X9... 6_ Woofer\tPet\tFoods\tproduces\ta\tlow-calorie\tdog\tfood\tfor\toverweight\tdogs.\tThis\tproduct\tis\tmade\tfrom\tbeef products\tand\tgrain.\tEach\tpound\tof\tbeef\tcost\t$1.20,\tand\teach\tpound\tof\tgrain\tcost\t$1.60.\tA\tpound\tof\tthe dog\tfood\tmust\tcontain\tat\tleast\t9\tunits\tof\tvitamin\t1\tand\t10\tunits\tof\tvitamin\t2.\tA\tpound\tof\tbeef\tcontains 10\tunits\tof\tvitamin\t1\tand\t12\tunits\tof\tvitamin\t2.\tA\tpound\tof\tgrain\tcontains\t6\tunits\tof\tvitamin1\tand\t9\tunits of\tvitamin\t2.\tDetermine\tthe\tamount\tof\tbeef\tand\tgrain\tin\torder\tto\tminimize\tthe\ttotal\tcost. a)\tDefine\tthe\tdecision\tvariables, b)\tFormulte\tthe\tLP\tmodel\tof\tthe\tgiven\tproblem\t(objective\tfunction\tand\tthe\tconstraints), and c)\tSolve\tit\tby\tthe\tMS\tExcel\tSolver 3