Question: In the dual-objective advertising model, we

> 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

> Continuing the previous problem, use Goal Seek for each value of number mailed (once for 80,000, once for 90,000, and so on). For each, find the response rate that allows the company to break even. Then chart these values, where the number mailed is on t

> 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 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

> In the current blending model, a barrel of any input results in a barrel of output. However, in a real blending problem, there can be losses. Suppose a barrel of input results in only a fraction of a barrel of output. Specifically, each barrel of either

> In Chandler’s blending model, suppose a chemical ingredient called CI is needed by both gasoline and heating oil. At least 3% of every barrel of gasoline must be CI, and at least 5% of every barrel of heating oil must be CI. Suppose that 4% of all crude

> How sensitive is the optimal solution (barrels of each input and output sold and total revenue) to the required quality levels? Answer this by running a two-way SolverTable with these five outputs. You can choose the values of the two quality levels to v

> Modify Acron’s model so that sales increase, then stay steady, and finally decrease. Specifically, assume that the gross margin is $1.2 million in year 1, then increases by 10% annually through year 6, then stays constant through year 10, and finally dec

> Use SolverTable in Chandler’s blending model to find the shadow price of crude oil 1—that is, the amount Chandler would be willing to spend to acquire more crude oil 1. Does this shadow price change as Chandler keeps getting more of crude oil 1? Answer t

> Use SolverTable in Chandler’s blending model to see whether, by increasing the selling price of gasoline, you can get an optimal solution that produces only gasoline, no heating oil. Then use SolverTable again to see whether, by increasing the selling pr

> Suppose, as a matter of corporate policy, that General Flakes decides not to advertise on the “Rachael Ray” show. Modify the original advertising model appropriately and find the new optimal solution. How much has it cost the company to make this policy

> We saw that the natural way to model SureStep’s backlogging model, with IF functions, leads to a nonsmooth model that Solver has difficulty handling. Another version of the problem is also difficult for Solver. Suppose SureStep wants to meet all demand o

> In the SureStep no-backlogging problem, change the demands so that they become 6000, 8000, 5000, 3000. Also, change the problem slightly so that newly hired workers take six hours to produce a pair of shoes during their first month of employment. After t

> In the current no-backlogging problem, SureStep doesn’t hire any workers and uses almost no overtime. This is evidently because of low demand. Change the demands to 6000, 8000, 5000, and 3000, and reoptimize. Is there now hiring and overtime? With this n

> Suppose SureStep could begin a machinery upgrade and training program to increase its worker productivity. This program would result in the following values of labor hours per pair of shoes over the next four months: 4, 3.9, 3.8, and 3.8. How much would

> The current solution to SureStep’s no-backlogging aggregate planning model requires a lot of firing. Run a one-way SolverTable with the firing cost as the input variable and the numbers fired as the outputs. Let the firing cost increase from its current

> SureStep is currently getting 160 regular-time hours from each worker per month. This is actually calculated from 8 hours per day times 20 days per month. For this, they are paid $9.375 per hour (51500y160). Suppose workers can change their contract so t

> Extend SureStep’s original no-backlogging aggregate planning model from four to six months. Try several different values for demands in months 5 and 6, and run Solver for each. Is your optimal solution for the first four months the same as the one in the

> Modify Acron’s model so that development lasts for an extra year. Specifically, assume that development costs of $7.2 million and $2.1 million are incurred at the beginnings of years 1 and 2, and then the sales in the current model occur one year later,

> In the employee scheduling example, suppose that the company can require the employees to work one day of overtime each week on the day immediately following this five-day shift. For example, an employee whose regular shift is Monday to Friday can also b

> In the employee scheduling example, suppose the company has 25 full-time employees and is not allowed to hire or fire any of them. Determine a schedule that maximizes the number of weekend days off received by these employees.

> In the employee scheduling example, suppose the employees want more flexibility in their schedules. They want to be allowed to work five consecutive days followed by two days off or to work three consecutive days followed by a day off, followed by two co

> In addition to the constraints already in the (original) advertising model, suppose General Flakes also wants to obtain at least 180 million exposures to men and at least 160 million exposures to women. Does the current optimal solution satisfy these con

> Explain why it is problematic to include a constraint such as the following in an LP model for a blending problem: Total octane in gasoline 1 blend / Barrels of gasoline 1 blended daily ≥ 0

> The employee scheduling model in this chapter was purposely made small (only seven decision variable cells). What would make a similar problem for a company like McDonald’s much more difficult? What types of constraints would be required? How many decisi