Ordering fuel. Three liquid fuels are stored in tanks for use in a blending process. Each fuel is characterized by a demand rate, a fixed replenishment cost, and a unit cost. Inventory carrying costs are assessed at the rate of 12 percent per year.
In order to save money on insurance rates, the operating policy is to limit the average value of stock on hand to no more than $7,500.
Fuel Demand Fixed Cost Unit Cost
1 1,000/year $100 $50
2 250/year $75 $100
3 500/year $50 $20
a. What are the optimal order quantities for the three items when they are ordered jointly, assuming that the fixed ordering costs are incurred for each item? What is the average annual cost for the three items combined?
b. Suppose the items are managed separately, but the overall limit on stock value still holds. What are the order quantities for the individual items? What is the average annual cost for the three items combined?
c. Suppose the stock limit in the base case is relaxed. If the limit is raised by $500, what is the impact on the optimal average annual costs?© BrainMass Inc. brainmass.com October 16, 2018, 8:09 pm ad1c9bdddf
Please see the attached Excel file for a detailed response to your ...
An attached Excel file contains formulas and explanations for finding optimal solutions to the given problems.
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?