Community Answer

Honor CodeSolved 1 Answer

See More Answers for FREE

Enhance your learning with StudyX

Receive support from our dedicated community users and experts

See up to 20 answers per week for free

Experience reliable customer service

Get Started

Data: Truck Type Cost per truck Capacity per truck Drivers needed per truck Small 20000 2000 1 Medium 30000 4500 2 Large 50000 7000 3 Total available/required 96000 42   Facility is available for at most 25 trucks a.  Decision variables: Let S, M, and L be the number of Small, Medium and Large trucks employed per day respectively. Objective: To minimize the total cost Minimize   20000S+30000M+50000L Constraints: 2000S+4500M+7000L >= 96000 (minimum capacity required per day) S+M+L <= 25 (facility is available for maximum of 25 trucks) 1S+2M+3L <= 42 (maximum number of drivers available) S, M, L >= 0 (non negativity) Solving the LP in solver: The solver is an excel plug in which can be installed from excel options. After installation, it is available in the data segment of the excel sheet. Once installed and launched, the parameters can be added Spreadsheet Model along with formula: Adding Parameters to Solver: Get & Transform DataQueries & ConnectionsSort & FilterData ToolsForecastSolver ParametersASet Objective:To:MaxMinBy Changing Variable Cells:SES2:SES4Subject to the Constraints:SBS10:SBS11<= SDS10:SDS11S B S 9>=\operatorname{SDS} 9SBS6Value Of:00\times 1st: Enter Green highlighted cell (objective function) in the set objective field 2nd: Select Min 3rd: Enter the yellow cells (decision variables) in the by changing variable cells field 4th: In constraints, click on add, enter the blue cells in the dialogue box which appears. Add ConstraintCell Reference:Constraint:I<=\underline{\mathrm{OK}}AddCancel On the left area (cell reference), enter the left side values, select relationships in the middle, and in the right enter the right side values of the inequality signs. Similarly, repeat for the next constraints by clicking on add button. Then click ok to go back to the parameters part. Note: the non-negativity constraints are taken care of by the below option Make Unconstrained Variables Non-NegativeSelect a SolvingSimplex LPMethod:Solving Method 5th; Select Simplex Lp in solving method 6th: Click solve Solution: I11Solver ResultsSolver found a solution. All Constraints and optimality conditions are satisfied.Keep Solver SolutionRestore Original ValuesReturn to Solver Parameters DialogReportsCreates the type of report that you specify, and places each report on a separate sheet in the workbook The solution is in yellow cells and the optimal value is in green cell. In solver results, select sensitivity and click ok to save the result as well as generate sensitivity report. b. Optimal solution: Small = 0 Medium = 12 Large = 6  Minimal Total cost = 660000 for the rest of the two answers we need the sensitivity report: Sensitivity report: Variable CellsConstraints c. If the cost of the medium truck is increased by 307 then the optimal solution will not change. (because the allowable increase for the medium number of trucks is 2142 which means the optimal solution will not change if the increased cost is less than this number) The optimal cost will be =sum product of  final value*new objective coefficient = 0*20000+12*(30000+307)+6*50000 = 663684 d. If the minimum daily trucking capacity is decreased by 307, the optimal cost will be decreased by 307*shadow price = 307*20 = 6140 The minimum daily trucking capacity available is 96000. the shadow price for this constraint is 20. this means for each unit reduction in the available capacity the total cost will decrease by 20 units provided the reduced number is less than the allowable decrease number (1500). So the total cost will be reduced by 307*20 = 6140 ...