Widgetco produces widgets at plants 1 and 2. It costs 20x^(1/2) dollars to produce x units at plant 1 and 40x^(1/3) dollars to produce x units at plant 2. Each plant can produce up to 70 units. Each unit produced can be sold for $10. There is demand for at most 120 widgets.
A) Formulate a mathematical model to help Widgetco determine the amount to produce at each plant so that net profit is maximized.
B) Implement the model in Excel and use Solver to find the optimal solution.
Nonlinear Programming & Evolutionary Optimization
This is a problem #10, chapter 8 - Nonlinear Programming & Evolutionary Optimization, from the textbook by Cliff Ragsdale, "Spreadsheet Modeling and Decision Analysis".
This problem requires the use of Excel and the add-in, called Solver. The course is Excel-based and Solver is the optimization application used for all problems.
A traveler was stranded in her car recently in a snowy blizzard in Wyoming. Unable to drive any farther, the stranded motorist used her cell phone to dial 911 to call for help. Because the caller was unsure of her exact location, it was impossible for the emergency operator to dispatch a rescue squad. Rescue personnel brought in telecommunications experts who determined that the stranded motorist's cell phone call could be picked up by three different communications towers in the area. Based on the strength of the signal being received at each tower, they were able to estimate the distance from each tower to the caller's location. The following table summarized the location (X-Y coordinates) of each tower and the tower's estimated straight line (or Euclidean) distance to the caller.
See attached file for data.
The caller's cell phone battery is discharging quickly and it is unlikely the motorist will survive much longer in the subfreezing temperatures. However, the emergency operator has a copy of Excel on her computer and believe it might be possible, with your help, to use Solver to determine the approximate location of the stranded motorist.
(a) Formulate an NLP for this problem
(b) Implement your model in a spreadsheet and solve it.
(c) To approximately what location should the rescue personnel be dispatched to look for the motorist?