Question: This Excel solver is saying Objective cell values do not converge. How do you do it properly? Thank you so much Make Unconstrained Variables Non-Negative


This Excel solver is saying Objective cell values do not converge. How do you do it properly? Thank you so much
Make Unconstrained Variables Non-Negative Select a Solving 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. To ensure they maintain their reputation, Honda is considering the possibility of acquiring 3 computer-controlled machines that will hopefully increase contribution margins. The new computer-controlled machines will have in total an annual capacity of 13,525 hours. Each Super Luxury unit will require 150 hours of computer-controlled machine hours, while each unit of the Luxury models will require 125 hours and each unit of the Affordable Luxury models 100 hours. With no change in the selling price, the new average contribution margin for Hondas 3 luxury models will be, respectively, $17,000,$14,000, and $9,500 for the Super Luxury, Luxury, and Affordable Luxury. The product mix is constrained by the demand for each type of car. The maximum demand for each type is estimated at 30 units for the Super Luxury, 45 units for the Luxury, and 65 units of the Affordable Luxury. The maximum number of vehicles that can be produced equals 110. To solve this problem, the plant manager has set up the following linear program: Maximize: C(S,L,A)=? Subject to: ? Where:LrepresentsthenumberofLuxuryunitssoldArepresentsthenumberofAffordableLuxuryunitssoldSrepresentsthenumberofSuperLuxuryunitssold REQUIRED: a. Determine the maximization formula. Write this formula out in your Excel solution. b. Determine the constraints. Write these constraints out in your Excel solution. Use solver to answer the following question: c. Calculate the total contribution margin. Make Unconstrained Variables Non-Negative Select a Solving 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. To ensure they maintain their reputation, Honda is considering the possibility of acquiring 3 computer-controlled machines that will hopefully increase contribution margins. The new computer-controlled machines will have in total an annual capacity of 13,525 hours. Each Super Luxury unit will require 150 hours of computer-controlled machine hours, while each unit of the Luxury models will require 125 hours and each unit of the Affordable Luxury models 100 hours. With no change in the selling price, the new average contribution margin for Hondas 3 luxury models will be, respectively, $17,000,$14,000, and $9,500 for the Super Luxury, Luxury, and Affordable Luxury. The product mix is constrained by the demand for each type of car. The maximum demand for each type is estimated at 30 units for the Super Luxury, 45 units for the Luxury, and 65 units of the Affordable Luxury. The maximum number of vehicles that can be produced equals 110. To solve this problem, the plant manager has set up the following linear program: Maximize: C(S,L,A)=? Subject to: ? Where:LrepresentsthenumberofLuxuryunitssoldArepresentsthenumberofAffordableLuxuryunitssoldSrepresentsthenumberofSuperLuxuryunitssold REQUIRED: a. Determine the maximization formula. Write this formula out in your Excel solution. b. Determine the constraints. Write these constraints out in your Excel solution. Use solver to answer the following question: c. Calculate the total contribution margin
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
