Create spreadsheets and use Solver to determine the correct
volumes to be produced to minimize cost for the following problem.
Your company has two trucks that it wishes to use on a specific
contract. One is a new truck the company is making payments on, and
one is an old truck that is fully paid for. The new truck’s costs
per mile are as follows: 54? (fuel/additives), 24? (truck
payments), 36? (driver), 12? (repairs), and 1? (misc.). The old
truck’s costs are 60? (fuel/additives), 0? (truck payments), 32?
(rookie driver), 24? (repairs), and 1? (misc.). The company knows
that truck breakdowns lose customers, so it has capped estimated
repair costs at $14,000. The total distance involved is 90,000
miles (to be divided between the two trucks).
In the solver parameters, the third constraint $B$7:$C$7>.=
0. Where are you getting the 0 from. When I try to use this formula
I get an error message. Also in the Contrains field of the excel
shett you A14 with a value of 3800. Where are you getting this
value from. I'm having great difficulty with this number and the 0
in the solver parameter. Thank you.
1 234 5 69 7 WN 3 8 9 10 =D1*C7+D2*B7 11 12 =B7+C7 13 =B7*24/100 14 =C7*12/100 15 1 2 4 5 6 7 8 9 10 11 12 13 14 3 456 A 15 16 B ? Cost per mile for new truck($) Cost per mile for old truck($) Decision Variables Number of miles each truck has to travel Old truck New truck Minimize the total cost Constraints = <= <= No.of miles Objective function B ? D Cost per mile for new truck($) 1.27 Cost per mile for old truck ($) 1.17 Decision Variables Number of miles each truck has to travel Old truck New truck No.of miles Objective function Minimize the total cost 0 Constraints 0 = 0 0 <= 90000 14000 14000 90000 14000 14000 E D |=(54+24+36+12+1)/100 =(60+32+24+1)/100
Solver Parameters Set Objective: To: Max By Changing Variable Cells: SBS7:SCS7 Subject to the Constraints: SAS12 = SCS12 SAS13:SAS14<= $C$13:SCS14 SBS7:SCS7 >= 0 Make Unconstrained Variables Non-Negative Select a Solving Simplex LP Options Method: 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 Close Min SAS10 O Value Of: 0 Add Change Delete Reset All Load/Save H>> 41 X
1 234 5 6 7 8 9 10 11 12 13 14 15 34 A B ? D 1.27 Cost per mile for new truck($) Cost per mile for old truck($) 1.17 Decision Variables Number of miles each truck has to travel Old truck New truck No.of miles 58333.33 31666.67 Objective function Minimize the total cost Constraints 108466.6667 90000 14000 3800 ^ <= 90000 14000 14000 E