Question: Continuing the previous problem, use Goal Seek

> When we (the authors) originally developed the bus route assignment model, we included an arc capacity constraint: Flow # 1. After giving this further thought, we deleted this constraint as being redundant. Why could we do this? Specifically, why can’t o

> In the optimal solution to the bus route assignment problem, company 2 is assigned to bus routes 6 and 7. Suppose these two routes are far enough apart that it is infeasible for one company to service both of them. Change the model to accommodate this re

> In the optimal solution to the machine-to-job assignment problem, jobs 3 and 4 are assigned to different machines. Suppose there is an extra restriction that these jobs must be assigned to the same machine. Change the model to accommodate this restrictio

> In the optimal solution to the machine-to-job assignment problem, jobs 1 and 2 are both assigned to machine 4. Suppose there is an extra restriction that jobs 1 and 2 must be assigned to different machines. Change the model to accommodate this restrictio

> A European call option on a stock earns the owner an amount equal to the price at expiration minus the exercise price, if the price of the stock on which the call is written exceeds the exercise price. Otherwise, the call pays nothing. A European put opt

> We modeled the bus route assignment problem with the alternative form of the transportation model (as in Figure 5.8). Model it instead with the standard form. Discuss the pros and cons of these two approaches for this particular example.

> Modify the bus route assignment model, assuming that company 1 decides to place bids on routes 7 and 8 (in addition to its current bids on other routes). The bids on these two routes are $5200 and $3300. Does the optimal solution change?

> In the machine-to-job assignment problem, the current capacities of the machines are 1, 2, 1, 2, and 1. If you could increase one of these capacities by 1, which would you increase? Why?

> Modify the machine-to-job assignment model under the assumption that only three of the four jobs must be completed. In other words, one of the four jobs does not have to be assigned to any machine. What is the new optimal solution?

> One possible solution method for the machine-to job assignment problem is the following heuristic procedure. Assign the machine to job 1 that completes job 1 quickest. Then assign the machine to job 2 that, among all machines that still have some capacit

> In the original Grand Prix example, the total capacity of the three plants is 1550, well above the total customer demand. Would it help to have 100 more units of capacity at plant 1? What is the most Grand Prix would be willing to pay for this extra capa

> Budget Auto produces inexpensive cars. Each car is sold for $12,500. The raw material in a car costs $8000. Labor time and robot time are needed to produce cars. A worker can do the needed labor on, at most, 100 cars per month; a robot can complete the n

> Broker Sonya Wong is currently trying to maximize her profit in the bond market. Four bonds are available for purchase and sale at the bid and ask prices shown in the file P04_97.xlsx. Sonya can buy up to 1000 units of each bond at the ask price or sell

> An automobile manufacturing company has a $500,000 advertising budget. To increase its automobile sales, the company is considering advertising in newspapers and on television. The more the company uses a particular medium, the less effective each additi

> Mondo Motorcycles is determining its production schedule for the next four quarters. Demands for motorcycles are forecasted to be 400 in quarter 1; 700 in quarter 2; 500 in quarter 3; and 200 in quarter 4. Mondo incurs four types of costs, as described h

> Georgia McBeal is trying to save for her retirement. She believes she can earn 10% on average each year on her retirement fund. Assume that at the beginning of each of the next 40 years, Georgia will allocate x dollars to her retirement fund. If at the b

> Bexter Labs produces three products: A, B, and C. Bexter can sell up to 2000 units of product A, up to 3000 units of product B, and up to 5000 units of product C. Each unit of product C uses two units of A and three units of B and incurs $7 in processing

> Molecular Products produces three chemicals: B, C, and D. The company begins by purchasing chemical A for a cost of $650 per 100 liters. For an additional cost of $320 and the use of three hours of skilled labor, 100 liters of A can be transformed into 4

> Capsule Drugs manufactures two drugs. The drugs are produced by blending two chemicals. By weight, drug 1 must contain at least 75% chemical 1, and drug 2 must contain at least 45% chemical 1. Drug 1 sells for $10 per ounce, and drug 2 sells for $7 per o

> Based on Magoulas and Marinos-Kouris (1988). An oil company produces two products: regular and premium gasoline. Each product contains 0.15 gram of lead per liter. The two products are produced from these six inputs: reformate, fluid catalytic cracker ga

> You can calculate the risk index of an investment by taking the absolute values of percentage changes in the value of the investment for each year and averaging them. Suppose you are trying to determine the percentages of your money to invest in several

> In the employee scheduling example, suppose that each full-time employee works eight hours per day. Thus, Monday’s requirement of 17 employees can be viewed as a requirement of 8(17) 5 136 hours. The company can meet its daily labor requirements by using

> You own a wheat warehouse with a capacity of 20,000 bushels. At the beginning of month 1, you have 6000 bushels of wheat. Each month, wheat can be bought and sold at the prices per bushel listed in the file. The sequence of events during each month is as

> The Internal Revenue Service (IRS) has determined that during each of the next 12 months it will need the numbers of supercomputers given in the file. To meet these requirements, the IRS rents supercomputers for a period of one, two, or three months. It

> During the next four quarters, Dorian Auto must meet (on time) the following demands for cars: 4000 in quarter 1; 2000 in quarter 2; 5000 in quarter 3; 1000 in quarter 4. At the beginning of quarter 1, there are 300 autos in stock. The company has the ca

> Based on Charnes and Cooper (1955). A small company is trying to determine employee salary based on the following attributes: effectiveness, responsibility, initiative, experience, education, self-expression, planning ability, intelligence, and the abili

> You are thinking of opening a small copy shop. It costs $5000 to rent a copier for a year, and it costs $0.03 per copy to operate the copier. Other fixed costs of running the store will amount to $400 per month. You plan to charge an average of $0.10 per

> AdminaStar processes Medicare claims. At the beginning of month 1 they have a backlog of 40,000 difficult claims and 60,000 easy claims. The predicted claim volume for months 1 through 8 is listed in the file P04_85.xlsx. At the beginning of month 1, Adm

> Based on Brams and Taylor (2000). Suppose that Eli Lilly and Pfizer are going to merge. Merger negotiations must settle the following issues: ■ What will the name of the merged corporation be?\ ■ Will corporate headquarters be in Indianapolis (Lilly want

> Suppose you are planning for retirement. At the beginning of this year and each of the next 39 years, you plan to contribute some money to your retirement fund. Each year, you plan to increase your retirement contribution by $500. When you retire in 40 y

> A graduated payment mortgage (GPM) enables the borrower to have lower payments earlier in the mortgage and increased payments later on. The assumption is the borrower’s income will increase over time so that it will be easier for the borrower to meet all

> You want to take out a $450,000 loan on a 20-year mortgage with end-of-month payments. The annual rate of interest is 3%. Twenty years from now, you will need to make a $50,000 ending balloon payment. Because you expect your income to increase, you want

> During the next two months an automobile manufacturer must meet (on time) the following demands for trucks and cars: month 1, 400 trucks and 800 cars; month 2, 300 trucks and 300 cars. During each month at most 1000 vehicles can be produced. Each truck u

> How much influence can the employee requirements for one, two, or three days have on the weekly schedule in the employee scheduling example? Explore this in the following questions: a. Let Monday’s requirements change from 17 to 25 in increments of 1. Us

> The HiTech company produces Blu-Ray disc players. Estimated demands for the next four quarters are 5000, 10,000, 8000, and 2000. At the beginning of quarter 1, HiTech has 60 workers. It costs $2000 to hire a worker and $4000 to fire a worker. Workers are

> An oil company produces gasoline from five inputs. The cost, density, viscosity, and sulfur content, and the number of barrels available of each input are listed in the file. Gasoline sells for $75 per barrel. Gasoline can have a density of at most 0.95

> An automobile manufacturer needs to plan its production for the next year. Demands for the next 12 months are forecasted to be 940, 790, 360, 720, 270, 130, 160, 300, 990, 290, 280, and 790. Other relevant information is as follows: ■ Workers are paid $5

> You are thinking of opening a Broadway play, I Love You, You’re Mediocre, Now Get Better! It will cost $5 million to develop the show. There are 8 shows per week, and you project the show will run for 100 weeks. It costs $1000 to open the theater each ni

> A rock company uses five types of rocks to fill six orders. The phosphate content, availability of each type of rock, and the production cost per pound for each rock are listed in the file, as well as the size of each order and the minimum and maximum ph

> Based on Gaballa and Pearce (1979). Northwest Airlines has determined that it needs the number of ticket agents during each hour of the day listed in the file. Workers work nine-hour shifts, one hour of which is for lunch. The lunch hour can be either th

> A bank needs exactly two employees working each hour from 9 a.m. to 5 p.m. Workers can work the shifts and are paid the wages listed in the file. For example, a worker working 9 a.m. to 2 p.m. is paid $42.00. Find an assignment of workers that provides e

> A company produces two products: A and B. Product A sells for $11 per unit and product B sells for $23 per unit. Producing a unit of product A requires two hours on assembly line 1 and one unit of raw material. Producing a unit of product B requires two

> Based on Thomas (1971). A toy company produces toys at two plants and sells them in three regions. The current demands at these regions are given in the file. Each plant can produce up to 4500 units. Each toy sells for $30, and the cost of producing and

> Carrington Oil produces gas 1 and gas 2 from two types of crude oil: crude 1 and crude 2. Gas 1 is allowed to contain up to 4% impurities, and gas 2 is allowed to contain up to 3% impurities. Gas 1 sells for $72 per barrel, whereas gas 2 sells for $84 pe

> A feed company produces two types of cattle feed, both consisting totally of wheat and alfalfa. Feed 1 must contain at least 75% wheat, and feed 2 must contain at least 65% alfalfa. Feed 1 sells for $2.50 per pound, and feed 2 sells for $2.10 per pound.

> Modify the employee scheduling model so that employees are paid $10 per hour on weekdays and $15 per hour on weekends. Change the objective so that you now minimize the weekly payroll. (You can assume that each employee works eight hours per day.) Is the

> Federated Oil has refineries in Los Angeles and Chicago. The Los Angeles refinery can refine up to two million barrels of oil per year, and the Chicago refinery up to three million. After the oil is refined, it is shipped to two distribution points, Hous

> Candy Kane Cosmetics (CKC) produces Leslie Perfume, which requires chemicals and labor. Two production processes are available. Process 1 transforms 0.3 unit of labor and 0.4 unit of chemicals into an ounce of perfume. Process two transforms 0.2 unit of

> Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand is $50.00. Her best guess is that she can sell 300 cups per week at $0.50 per cup. The variable cost of producing a cup of lemonade is $0.20. a. Given her

> Based on Heady and Egbert (1964). Walnut Orchard has two farms that grow wheat and corn. Because of differing soil conditions, there are differences in the yields and costs of growing crops on the two farms. The yields and costs are listed in the file. E

> United Steel manufactures two types of steel at three different steel mills. During a given month, each steel mill has 240 hours of blast furnace time available. Because of differences in the furnaces at each mill, the time and cost to produce a ton of s

> All steel manufactured by Allied Steel must meet the following requirements: between 3.2% and 3.5% carbon; between 1.8% and 2.5% silicon; between 0.9% and 1.2% nickel; tensile strength of at least 45,000 pounds per square inch (psi). The company manufact

> An oil company processes oil into aviation fuel and heating oil. It costs $55,000 to purchase each 1000 barrels of oil, which is then distilled and yields 450 barrels of aviation fuel and 550 barrels of heating oil. Output from the distillation can be so

> You now have $10,000, and the following investment plans are available to you during the next three years: ■ Investment A: Every dollar invested now yields $0.10 a year from now and $1.30 three years from now. ■ Investment B: Every dollar invested now yi

> At the beginning of year 1, you have $100,000. Investments A and B are available; their cash flows are shown in the file. Assume that any money not invested in A or B earns interest at an annual rate of 2.5%. a. Determine how to maximize your cash on han

> An investor has $100,000 to invest right now (the beginning of year 1). The cash flows associated with five available investments are listed in the file. For example, every dollar invested in A in year 1 yields $1.40 in year 4. In addition to these inves

> Abotte Products produces three products, A, B, and C. The company can sell up to 500 pounds of each product at the following prices (per pound): product A, $10; product B, $15; and product C, $25. Abotte purchases raw material at $7 per pound. Each pound

> Suppose there are three objectives, not just two: the total advertising cost, the total number of excess exposures to men, and the total number of excess exposures to women. Continuing the approach suggested in the previous problem, how might you proceed

> A chemical company produces three products, A, B, and C, and can sell these products in unlimited quantities at the following unit prices: A, $10; B, $55; and C, $100. Producing a unit of A requires one hour of labor; a unit of B, two hours of labor plus

> The XNPV function can calculate NPV for any (possibly irregular) series of cash flows. Look this function up in Excel’s online help. Then use it to develop a spreadsheet model that finds the NPV of the following series: a payment of $25,000 today (assume

> A furniture company manufactures tables and chairs. A table requires 45 board-feet of wood, and a chair requires 20 board-feet of wood. Wood can be purchased at a cost of $1.90 per board-foot, and 25,000 board-feet of wood are available for purchase. It

> A coal company produces coal at three mines and ships it to four customers. The cost per ton of producing coal, the ash and sulfur content (per ton) of the coal, and the production capacity (in tons) for each mine are given in the file. The number of ton

> Many Wall Street firms use LP models to select a desirable bond portfolio. The following is a simplified version of such a model. Solodrex is considering investing in four bonds; $1 million is available for investment. The expected annual return, the wo

> Hiland’s Appliances stocks laptops, TVs, refrigerators, microwave ovens, and ranges. These products compete for floor space, tie up different amounts of capital, and have different profit margins. The company has also specified minimum percentages of the

> A chemical manufacturer uses chemicals 1 and 2 to produce two drugs. Drug 1 must be at least 80% chemical 1, and drug 2 must be at least 50% chemical 2. Up to 70,000 ounces of drug 1 can be sold at $40 per ounce; up to 50,000 ounces of drug 2 can be sold

> A fertilizer company blends silicon and nitrogen to produce two types of fertilizers. Fertilizer 1 must be at least 40% nitrogen and sells for $70 per pound. Fertilizer 2 must be at least 70% silicon and sells for $40 per pound. The company can purchase

> Young MBA Erica Cudahy can invest up to $20,000 in stocks and loans. Each dollar invested in stocks yields $0.08 profit, and each dollar invested in a loan yields $0.13 profit. At least 40% of all money invested must be in stocks, and at least $7000 must

> A bank is attempting to determine where its assets should be invested during the current year. At present, $500,000 is available for investment in bonds, home loans, auto loans, and personal loans. The annual rates of return on each type of investment ar

> Sunblessed Juice Company sells bags of oranges and cartons of orange juice. Sunblessed grades oranges on a scale of 1 (poor) to 10 (excellent). At present, Sunblessed has 220,000 pounds of grade 6 oranges and 150,000 pounds of grade 9 oranges on hand. Th

> In the dual-objective advertising model, we put a budget constraint on the total advertising cost and then maximized the total number of excess exposures. Do it the opposite way, reversing the roles of the two objectives. That is, model it so that you pu

> In a situation such as Acron’s, where a one-time cost is followed by a sequence of cash flows, the internal rate of return (IRR) is the discount rate that makes the NPV equal to 0. The idea is that if the discount rate is greater than the IRR, the compan

> You have decided to enter the candy business. You are considering producing two types of candies: Slugger candy and Easy Out candy, both of which consist solely of sugar, nuts, and chocolate. At present, you have in stock 10,000 ounces of sugar, 2000 oun

> NewAge Pharmaceuticals produces the drug NasaMist from four chemicals. Today, the company must produce 5000 pounds of the drug. The three active ingredients in NasaMist are A, B, and C. By weight, at least 7% of NasaMist must consist of A, at least 5% mu

> Shoemakers of America forecasts the following demand for the next six months: 5000 pairs in month 1; 6000 pairs in month 2; 7000 pairs in month 3; 9000 pairs in month 4; 6000 pairs in month 5; 5000 pairs in month 6. It takes a shoemaker 20 minutes to pro

> During each four-hour period, the Smalltown police force requires the following number of on-duty police officers: four from midnight to 4 a.m.; four from 4 a.m. to 8 a.m.; seven from 8 a.m. to noon; seven from noon to 4 p.m.; eight from 4 p.m. to 8 p.m.

> A bus company believes that it will need the following numbers of bus drivers during each of the next five years: 60 drivers in year 1; 70 drivers in year 2; 50 drivers in year 3; 65 drivers in year 4; and 75 drivers in year 5. At the beginning of each y

> You have been commissioned by Indiana University to evaluate the relative efficiency of four degree-granting units: Business, Education, Arts and Sciences, and Health, Physical Education, and Recreation (HPER). You are given the information in the file.

> You have been asked to evaluate the efficiency of the Port Charles Police Department. Three precincts are to be evaluated. The inputs and outputs for each precinct are as follows: ■ input 1 5 number of policemen ■ input 2 5 number of vehicles used ■ outp

> The Salem Board of Education wants to evaluate the efficiency of the town’s four elementary schools. The three outputs of the schools are ■ output 1 5 average reading score ■ output 2 5 average mathematics score ■ output 3 5 average self-esteem score The

> Pine Valley Bank has three branches. You have been asked to evaluate the efficiency of each. The following inputs and outputs are to be used for the study: ■ input 1 5 labor hours used (hundreds per month) ■ input 2 5 space used (in hundreds of square fe

> Continuing the previous problem in a slightly different direction, continue to use the Money_allocated cell as a decision variable cell, and add a constraint that it must be less than or equal to any value, such as $195,000, that is less than its current

> The NPV function automatically discounts each of the cash flows and sums the discounted values. Verify that it does this correctly for Acron’s model by calculating the NPV the long way. That is, discount each cash flow and then sum these discounted value

> Suppose that General Flakes decides that it shouldn’t place any more than 10 ads on any given show. Modify the (original) advertising model appropriately to incorporate this constraint, and then reoptimize (with integer constraints on the numbers of ads)

> In the pension fund model, if the amount of money initially is less than the amount found by Solver, then James will not be able to meet all of the pension fund payments. Use the current model to demonstrate that this is true. To do so, enter a value les

> Suppose the investments in the Barney-Jones model sometimes require cash outlays in more than one year. For example, a $1 investment in investment B might require $0.25 to be spent in year 1 and $0.75 to be spent in year 2. Does the current model easily

> The pension fund model is streamlined, perhaps too much. It does all of the calculations concerning cash flows in row 20. James decides he would like to break these out into several rows of calculations: Beginning cash (for year 1, this is the amount all

> In the pension fund model, suppose James has been asked to see how the optimal solution changes if the required payments in years 6 to 15 all increase by the same percentage, where this percentage could be anywhere from 5% to 25%. Use an appropriate one-

> In the pension fund model, suppose there is a fourth bond, bond 4. Its unit cost in year 1 is $1020, it returns coupons of $70 in years 2 to 5 and a payment of $1070 in year 6. Modify the model to incorporate this extra bond and then run Solver. Does the

> In the pension fund model, suppose there is an upper limit of 60 on the number of bonds of any particular type that can be purchased. Modify the model to incorporate this extra constraint and then reoptimize. How much more money does James need to alloca

> In the Barney-Jones investment model, we ran investments across columns and years down rows. Many financial analysts seem to prefer the opposite. Modify the spreadsheet model so that years go across columns and investments go down rows. Run Solver to ens

> We claimed that our model for Barney-Jones is generalizable. Try generalizing it to the case where there are two more potential investments, F and G. Investment F requires a cash outlay in year 2 and returns $0.50 in each of the next four years for every

> Modify the Barney-Jones investment model so that a minimum amount must be put into any investment, although this minimum can vary by investment. For example, the minimum amount for investment A might be $0, whereas the minimum amount for investment D mig

> Create a one-way data table in the Acron model to see how the NPV varies with discount rate, which is allowed to vary from 8% to 18% in increments of 0.5%. Explain intuitively why the results go in the direction they go—that is, the NPV decreases as the

> In the Barney-Jones investment model, increase the maximum amount allowed in any investment to $150,000. Then run a one-way sensitivity analysis to the money market rate on cash. Capture one output variable: the maximum amount of cash ever put in the mon

> Suppose, in addition to the shows already listed, General Flakes wants to open the possibility of purchasing ads on the “Good Morning America” show on ABC. Make up any reasonable input data you need to include this possibility in the (original) model, an

> In a production process model such as Repco’s, certain inputs make no sense in the usage table (the range B7:D9 of the model). For example, suppose that, in addition to current usages, each unit of drug A requires one unit of drug C. Why does this result

> We claimed that the Repco model could either constrain the units produced to be greater than or equal to the units used by production or constrain the units sold to be nonnegative. Modify the model to implement the latter (deleting the former), and verif

> Suppose there is a fourth drug, drug D, that Repco can produce and sell. Each unit of drug D requires four labor hours, one unit of drug A, and one unit of drug C to produce, and it sells for $150 per unit. Modify the current model to incorporate drug D

> Run a one-way sensitivity analysis on the optimal solution to the unit selling price of drug A in the Repco model. If this price is high enough, will Repco start selling drug A in addition to producing it? Then run a similar one-way sensitivity analysis

> We warned you about clearing denominators in the quality constraints. This problem illustrates what can happen if you don’t do so. a. Implement the quality constraints as indicated in Inequality (4.3) of the text. Then run Solver with the Simplex LP meth