Powerhouse produces capacitors at three locations: Los Angeles, Chicago, and New York. Capacitors are shipped from these locations to public utilities in five regions of the country: northeast (NE), northwest (NW), midwest (MW), southeast (SE), and southwest (SW). The cost of producing and shipping a capacitor from each plant to each region of the country is given in the file P05_63.xlsx. Each plant has an annual production capacity of 100,000 capacitors. Each year, each region of the country must receive the following number of capacitors: NE, 55,000; NW, 50,000; MW, 60,000; SE, 60,000; SW, 45,000. Powerhouse believes that shipping costs are too high, and it is therefore considering building one or two more production plants. Possible sites are Atlanta and Houston. The costs of producing a capacitor and shipping it to each region of the country are given in the same file. It costs $3 million (in current dollars) to build a new plant, and operating each plant incurs a fixed cost (in addition to variable shipping and production costs) of $50,000 per year. A plant at Atlanta or Houston will have the capacity to produce 100,000 capacitors per year. Assume that future demand patterns and production costs will remain unchanged. If costs are discounted at a rate of 12% per year, how can Powerhouse minimize the net present value (NPV) of all costs associated with meeting current and future demands?
> Suppose in the Dorian production model that no minimum production limits are placed on the individual vehicle types. However, minimum production limits are placed on all cars and on all minivans. Specifically, if Dorian produces any cars, regardless of s
> In the Dorian production model, suppose that the production quantity of compact cars must either be less than or equal to 100 (a small batch) or greater than or equal to 1000 (a large batch). The same statements hold for the other vehicle types as well,
> In the last sheet of the file Fixed Cost Manufacturing Finished.xlsx, we illustrated one way to model the Great Threads problem with IF functions that didn’t work. Try a slightly different approach here. Eliminate the binary variables in row 14 altogethe
> In the Great Threads model, we found an upper bound on production of any clothing type by calculating the amount that could be produced if all of the resources were devoted to this clothing type. a. What if you instead used a very large value such as 1,0
> Solve the problem 1 using the input data in the file P06_02.xlsx. Data from Problem 1: In the capital budgeting model in Figure 6.5, we supplied the NPV for each investment. Suppose instead that you are given only the streams of cash inflows from each i
> If Solver could handle IF functions correctly, how would you use them in the Dorian production example to create an arguably more natural model—without binary variables? Run Solver on your modified model. Do you get the correct solution?
> The optimal solution to the Dorian production model appears to be sensitive to the model inputs. For each of the following inputs, create a one-way Solver Table that captures all decision variable cells and the objective cell as outputs. You can choose t
> As the Dorian production model is currently stated, each vehicle type has a minimum production level; if this type is produced at all, its production quantity must be at least this minimum. Suppose that for large minivans, there is also a maximum product
> Assume the demand for a company’s drug Wozac during the current year is 50,000, and assume demand will grow at 5% a year. If the company builds a plant that can produce x units of Wozac per year, it will cost $16x. Each unit of Wozac is sold for $3. Each
> In the Dorian production model, the optimal solution calls for the minimum number of compact cars and midsize minivans to be produced, but for more than the minimum number of large minivans to be produced. If the large minivans are evidently that profita
> In the optimal solution to the Great Threads model, no pants are produced. Suppose Great Threads has an order for 300 pairs of pants that must be produced. Modify the model appropriately and use Solver to find the new optimal solution. (Is it enough to p
> In the optimal solution to the Great Threads model, the labor hour and cloth constraints are both binding—the company is using all it has. a. Use SolverTable to see what happens to the optimal solution when the amount of available cloth increases from it
> In the Great Threads model, we didn’t constrain the production quantities in row 16 to be integers, arguing that any fractional values could be safely rounded to integers. See whether this is true. Constrain these quantities to be integers and then run S
> Referring to the previous problem, if it is optimal for the company to produce sweatshirts, use SolverTable to see how much larger the fixed cost of sweatshirt machinery would have to be before the company would not produce any sweatshirts. However, if t
> How difficult is it to expand the Great Threads model to accommodate another type of clothing? Answer by assuming that the company can also produce sweatshirts. The rental cost for sweatshirt equipment is $1100; the variable cost per unit and the selling
> Telco, a national telemarketing firm, usually picks a number of sites around the country from which it makes its calls. As a service, AD&D’s telecommunication marketing department wants to help Telco choose the number and location of its sites. How can I
> The models in this chapter are often called combinatorial models because each solution is a combination of the various 0–1 values, and only a finite number of such combinations exist. For the capital budgeting model in Figure 6.5, there are seven investm
> In the capital budgeting model in Figure 6.5, we supplied the NPV for each investment. Suppose instead that you are given only the streams of cash inflows from each investment. This file also shows the cash requirements and the budget. You can assume th
> Modify the original Grand Prix example as follows. Increase the demands at the regions by 200 each, so that total demand is well above total plant capacity. This means that some demands cannot be supplied. Suppose there is a unit penalty cost at each reg
> A bond is currently selling for $1040. It pays the amounts listed in the file at the ends of the next six years. The yield of the bond is the interest rate that would make the NPV of the bond’s payments equal to the bond’s price. Use Excel’s Goal Seek to
> Unlike the small logistics models presented here, real-world logistics problems can be huge. Imagine the global problem a company like FedEx faces each day. Describe as well as you can the types of decisions and constraints it has. How large (number of d
> What is the relationship between transportation models and more general logistics models? Explain how these two types of linear optimization models are similar and how they are different.
> “It is essential to constrain all shipments in a transportation problem to have integer values to ensure that the optimal LP solution consists entirely of integer-valued shipments.” Is this statement true or false? Why?
> You have been assigned to ensure that each high school in the Indianapolis area is racially balanced. Explain how you would use a network model to help attain this goal.
> You want to start a campus business to match compatible male and female students for dating. How would you use a network model to help you run your business?
> You have put four valuable paintings up for sale. Four customers are bidding for the paintings. Customer 1 is willing to buy two paintings, but each other customer is willing to purchase at most one painting. The prices that each customer is willing to p
> You have put four valuable paintings up for sale. Four customers are bidding for the paintings. Customer 1 is willing to buy two paintings, but each other customer is willing to purchase at most one painting. The prices that each customer is willing to p
> Kellwood, a company that produces a single product, has three plants and four customers. The three plants will produce 6000, 4000, and 5000 units, respectively, during the next time period. Kellwood has made a commitment to sell 3000 units to customer 1,
> Modify the original Grand Prix example as follows. Increase the demands at the regions by 200 each, so that total demand is well above total plant capacity. However, now interpret these “demands” as “maximum sales,” the most each region can accommodate,
> Based on Jacobs (1954). The Carter Caterer Company must have the following number of clean napkins available at the beginning of each of the next four days: day 1, 1500; day 2, 1200; day 3, 1800; day 4, 600. After being used, a napkin can be cleaned by o
> The yield of a chemical reaction is defined as the ratio (expressed as a percentage) of usable output to the amount of raw material input. Suppose the yield of a chemical reaction depends on the length of time the process is run and the temperature at wh
> In the original RedBrand problem, suppose that the company could add up to 100 tons of capacity, in increments of 10 tons, to any single plant. Use SolverTable to determine the yearly savings in cost from having extra capacity at the various plants. Assu
> Based on Ravindran (1971). A library must build shelving to shelve 200 4-inch-high books, 600 8-inchhigh books, and 500 12-inch-high books. Each book is 0.5 inch thick. The library has several ways to store the books. For example, an 8-inch-high shelf ca
> Eight students need to be assigned to four dorm rooms at Faber College. Based on incompatibility measurements, the cost incurred for any pair of students rooming together is shown in the file. How should the students be assigned to the four rooms to mini
> At present, 40,000 long-distance calls must be routed from New York to Los Angeles (L.A.), and 30,000 calls must be routed from Philadelphia to L.A. On route to L.A. from Philadelphia or New York, calls are sent through Indianapolis or Cleveland, then th
> Ewing Oil has oil fields in San Diego and Los Angeles. The San Diego field can produce up to 500,000 barrels per day, and the Los Angeles field can produce up to 600,000 barrels per day. Oil is sent from the fields to a refinery, either in Dallas or in H
> Rework the previous problem under the assumption that Galveston has a refinery capacity of 150,000 barrels per day and Mobile has a refinery capacity of 180,000 barrels per day.
> Assume that before being shipped to Los Angeles or New York, all oil produced at the wells must be refined at either Galveston or Mobile. To refine 1000 barrels of oil costs $5780 at Mobile and $6250 at Galveston. Assuming that both Mobile and Galveston
> Bloomington has two hospitals. Hospital 1 has four ambulances, and hospital 2 has two ambulances. Ambulance service is deemed adequate if there is only a 10% chance that no ambulance will be available when an ambulance call is received by a hospital. The
> There are 15 jobs that must be done by 10 employees. Each job must be done by a single employee, and each employee can do at most two jobs. The times (in minutes) for the employees to do the jobs are listed in the file P05_70.xlsx, where blanks indicate
> Continuing the previous problem (with capacity 300 at plant 2), suppose you want to see how much extra capacity and extra demand you can add to plant 1 and region 2 (the same amount to each) before the total shipping cost stops decreasing and starts incr
> A company manufacturers a product in the United States and sells it in England. The unit cost of manufacturing is $50. The current exchange rate (dollars per pound) is 1.221. The demand function, which indicates how many units the company can sell in Eng
> In the Quality Sweaters model, the range E9:E11 does not have a range name. Open your completed Excel file and name this range Costs. Then look at the formula in cell E12. It does not automatically use the new range name. Modify the formula so that it do
> Based on Glover and Klingman (1977). The government has many computer files that must be merged frequently. For example, consider the Survey of Current Income (SCI) and the Consumer Price Service (CPS) files, which keep track of family income and family
> Referring to the previous problem, suppose that Allied Freight can purchase and ship extra units to either warehouse for a total cost of $100 per unit and that all customer demand must be met. Determine how to minimize the sum of purchasing and shipping
> Allied Freight supplies goods to three customers. The company has two warehouses. The warehouse availabilities, the customer requirements, and the unit shipping costs from warehouses to customers are shown in the file. There is a penalty for each unsatis
> Based on Denardo et al. (1988). Three fires have just broken out in New York. Fires 1 and 2 each require two fire engines, and fire 3 requires three fire engines. The “cost” of responding to each fire depends on the time at which the fire engines arrive.
> Three professors must be assigned to teach six sections of finance. Each professor must teach two sections of finance, and each has ranked the six time periods during which finance is taught, as shown in the file P05_65.xlsx. A ranking of 10 means that t
> Based on Hansen and Wendell (1982). During the month of July, Pittsburgh resident Bill Fly must make four round-trip flights between Pittsburgh and Chicago. The dates of the trips are shown in the file P05_64.xlsx. Bill must purchase four round-trip tick
> Delko is considering hiring people for four types of jobs. The company would like to hire the number of people listed in the file P05_62.xlsx for each type of job. Delko can hire eight types of people. Each type is qualified to perform two or more types
> At the beginning of year 1, a new machine must be purchased. The cost of maintaining a machine, depending on its age, is given in the file P05_61.xlsx. The cost of purchasing a machine at the beginning of each year is given in this same file. There is no
> Suppose it costs $30,000 to purchase a new car. The annual operating cost and resale value of a used car are shown in the file. Assume that you presently have a new car. Determine a replacement policy that minimizes your net costs of owning and operating
> Assume that the number of units sold of a product is given by 100 2 0.5P 1 26√A, where P is the price (in dollars) charged for the product and A is the amount spent on advertising (in thousands of dollars). Each unit of the product costs $5 to produce. U
> Here is a problem to challenge your intuition. In the original Grand Prix example, reduce the capacity of plant 2 to 300. Then the total capacity is equal to the total demand. Run Solver on this model. You should find that the optimal solution uses all c
> It costs $300 to buy a lawn mower from a lawn supply store. Assume that you can keep a lawn mower for at most five years and that the estimated maintenance cost each year of operation is as follows: year 1, $90; year 2, $135; year 3, $175; year 4, $200;
> Each year, Data Corporal produces up to 10,000 computers in Boston and up to 6000 computers in Charlotte. There are customers in Los Angeles, New York, and Seattle who must receive 5300, 6700, and 3300 computers, respectively. Producing a computer costs
> Edsel Motors produces cars in Detroit and Dallas. The Detroit plant can produce up to 8500 cars, and the Dallas plant can produce up to 4000 cars. Producing a car costs $2000 in Detroit and $1800 in Dallas. Cars must be shipped to 12 cities. The costs of
> Nash Auto has two plants, two warehouses, and three customers. The plants are in Detroit and Atlanta, the warehouses are in Denver and New York, and the customers are in Los Angeles, Chicago, and Philadelphia. Cars are produced at plants, then shipped to
> Sunco Oil produces oil at two wells. Well 1 can produce up to 150,000 barrels per day, and well 2 can produce up to 200,000 barrels per day. It is possible to ship oil directly from the wells to Sunco’s customers in Los Angeles and New York. Alternativel
> General Ford produces cars in Los Angeles and Detroit and has a warehouse in Atlanta. The company supplies cars to customers in Houston and Tampa. The costs of shipping a car between various points are listed in the file P05_54.xlsx, where a blank means
> A company manufactures widgets at two factories, one in Memphis and one in Denver. The Memphis factory can produce up to 150 widgets per day, and the Denver factory can produce up to 200 widgets per day. The company are shipped by air to customers in Los
> A company is taking bids on four construction jobs. Three contractors have placed bids on the jobs. Their bids (in thousands of dollars) are given in the file P05_52.xlsx. (A blank indicates that the contractor did not bid on the given job.) Contractor 2
> Based on Machol (1970). A swimming coach is putting together a relay team for the 400-meter relay. Each swimmer must swim 100 meters of breaststroke, backstroke, butterfly, or freestyle, and each swimmer can swim only one race. The coach believes that ea
> The file gives the annual sales for Microsoft (in millions of dollars) for the years 1984–1993, where 1984 = year 1. a. Fit an exponential curve to these data. b. Assuming you are back in 1993, by what percentage do you estimate that Microsoft has grown
> Five employees are available to perform four jobs. The time it takes each person to perform each job is given in the file P05_50.xlsx. Determine the assignment of employees to jobs that minimizes the total time required to perform the four jobs. (A blank
> In the Grand Prix example with varying tax rates, the optimal solution uses all available plant capacity and more than satisfies customer demands. Will this always be the case? Experiment with the unit selling prices and/or tax rates to see whether the c
> Touche Young has eight auditors. Each can work up to 160 hours during the next month, during which time six projects must be completed. The hours required for each project and the amounts each auditor can be billed for each project are given in the file
> The Amorco Oil Company controls two oil fields. Field 1 can produce up to 22 million barrels of oil per day, and field 2 can produce up to 19 million barrels of oil per day. At field 1, it costs $42.50 to extract and refine a barrel of oil; at field 2 th
> The 7th National Bank has two check-processing sites. Site 1 can process 10,000 checks per day, and site 2 can process 6000 checks per day. The bank processes three types of checks: vendor, salary, and personal. The processing cost per check depends on t
> The government is auctioning off oil leases at two sites. At each site, 150,000 acres of land are to be auctioned. Cliff Ewing, Blake Barnes, and Alexis Pickens are bidding for the oil. Government rules statethat no bidder can receive more than 45% of th
> A school system has 16 bus drivers that must cover 12 bus routes. Each driver can cover at most one route. The driver’s bids for the various routes are listed in the file P05_45.xlsx. Each bid indicates the amount the driver will charge the school system
> You are trying to help the MCSCC (Monroe County School Corporation) determine the appropriate high school district for each housing development in Bloomington. For each development, you are given the number of students, the mean family income, the percen
> A truck must travel from New York to Los Angeles. As shown, several routes are available. The number associated with each arc is the number of gallons of fuel required by the truck to traverse the arc. Determine the route from New York to Los Angeles tha
> The town of Busville has three school districts. The numbers of black students and white students in each district are shown in the file P05_42.xlsx. The Supreme Court requires the schools in Busville to be racially balanced. Thus, each school must have
> Dataware is trying to determine whether to give a $10 rebate, cut the price $6, or have no price change on a software product. Currently, 40,000 units of the product are sold each week for $45 apiece. The variable cost of the product is $5. The most like
> One rather unrealistic assumption in the flight scheduling model is that a given plane can fly two consecutive flights with no downtime. For example, it could fly flight 5903 that gets into Washington, D.C. at time 14 and then fly flight 7555 that leaves
> In the flight-scheduling model, use SolverTable to examine the effect of increasing both the fixed cost per plane and the overnight cost by the same percentage, assuming that the company owns eight planes. Let this percentage vary from 0% to 50% in incre
> In the Grand Prix example with varying tax rates, the optimal solution more than satisfies customer demands. Modify the model so that regions have not only lower limits on the amounts they require, but upper limits on the amounts they can sell. Assume th
> In the flight-scheduling model, use SolverTable to examine the effect of decreasing all net revenues by the same percentage, assuming that the company owns six planes. Let this percentage vary from 0% to 50% in increments of 10%. Discuss the changes that
> We illustrated how a machine replacement problem can be modeled as a shortest path problem. This is probably not the approach most people would think of when they first see a machine replacement problem. In fact, most people would probably never think in
> In the VanBuren machine replacement problem, suppose the company starts with a machine that is eight quarters old at the beginning of the first quarter. Modify the model appropriately, keeping in mind that this initial machine must be sold no more than f
> In the VanBuren machine replacement problem, the company’s current policy is to keep a machine at least four quarters but no more than 12 quarters. Suppose instead that the company imposes no upper limit on how long it will keep a machine; its only polic
> In the VanBuren machine replacement problem, the company’s current policy is to keep a machine at least 4 quarters but no more than 12 quarters. Suppose this policy is instead to keep a machine at least 5 quarters but no more than 10 quarters. Modify the
> How difficult is it to add nodes and arcs to an existing shortest path model? Answer by adding a new node, node 11, to Maude’s network. Assume that node 11 is at the top of the network, geographically, with double-arrowed arcs joining it to nodes 2, 5, a
> In the VanBuren machine replacement problem, we assumed that the maintenance cost and salvage values are linear functions of age. Suppose instead that the maintenance cost increases by 50% each quarter and that the salvage value decreases by 10% each qua
> The file lists sales (in millions of dollars) of Dell Computer during the period 1987–1997 (where year 1 corresponds to 1987). a. Fit a power and an exponential trend curve to these data. Which fits the data better? b. Use your part a answer to predict 1
> Continuing the previous problem, suppose again that all arcs go in both directions, but suppose Maude’s objective is to find the shortest path from node 1 to node 7 (not node 10). Modify the spreadsheet model appropriately and solve.
> In Maude’s shortest path problem, suppose all arcs in the current network from higher-numbered nodes to lower numbered nodes, such as from node 6 to node 5, are disallowed. Modify the spreadsheet model and find the shortest path from node 1 to node 10. I
> In Maude’s shortest path problem, suppose all arcs in the network are double-arrowed, that is, Maude can travel along each arc (with the same distance) in either direction. Modify the spreadsheet model appropriately. Is her optimal solution still the sam
> Suppose in the original Grand Prix example that the routes from plant 2 to region 1 and from plant 3 to region 3 are not allowed. (Perhaps there are no railroad lines for these routes.) How would you modify the original model to rule out these routes? Ho
> Continuing the previous problem, develop and optimize a sample model with your own choices of N, M, and L that barely stay within Solver’s limit. You can make up any input data. The important point here is the layout and formulas of the spreadsheet model
> Consider a modification of the original RedBrand problem where there are N plants, M warehouses, and L customers. Assume that the only allowable arcs are from plants to warehouses and from warehouses to customers. If all such arcs are allowable—all plant
> In the RedBrand problem with shrinkage, change the assumptions. Now instead of assuming that there is some shrinkage at the warehouses, assume that there is shrinkage in delivery along each route. Specifically, assume that a certain percentage of the uni
> How difficult is it to expand the original RedBrand model? Answer this by adding a new plant, two new warehouses, and three new customers, and modify the spreadsheet model appropriately. You can make up the required input data.
> In the RedBrand two-product problem, we assumed that the unit shipping costs are the same for both products. Modify the spreadsheet model so that each product has its own unit shipping costs. You can assume that the original unit shipping costs apply to
> Expand the RedBrand two-product spreadsheet model so that there are now three products competing for the arc capacity. You can make up the required input data.
> Repeat parts a–d of the problem 24 for a six-month European put option with exercise price $40. Again, assume a current stock price of $35, a risk-free rate of 5%, and an annual volatility of 40%. Data from Problem 24: A European call option on a stock
> Continuing the previous problem, make the problem even more general by allowing upper bounds (arc capacities) and lower bounds for the flows on the allowable arcs. Some of the upper bounds can be very large numbers, effectively indicating that there is n
> In the original RedBrand problem, we assume a constant arc capacity, the same for all allowable arcs. Modify the model so that each arc has its own arc capacity. You can make up the required arc capacities.
> In the original RedBrand problem, the costs for shipping from plants or warehouses to customer 2 were purposely made high so that it would be optimal to ship to customer 1 and then let customer 1 ship to customer 2. Use SolverTable appropriately to do th
