Question: Modify the Barney-Jones investment model so

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

> 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

> A large CPA firm currently has 100 junior staff members and 20 partners. In the long run—say, 20 years from now—the firm would like to consist of 130 junior staff members and 20 partners. During a given year, 10% of all partners and 30% of all junior sta

> Discuss how the aggregate planning model could be extended to handle a company that produces several products on several types of machines. What information would you need to model this type of problem?

> In this chapter, we give you the cost of producing a product and other inputs that are used in the analysis. Do you think most companies find it easy to determine the cost of producing a product? What difficulties might arise?

> Keefer Paper produces recycled paper from paper purchased from local offices and universities. The company sells three grades of paper: high brightness paper, medium-brightness paper, and low-brightness paper. The high-brightness paper must have a bright

> In the profit model in this section, we used the power curve to relate demand and price because it has the lowest MAPE (as well as the lowest RMSE and MAE). However, the exponential curve was not far behind. Rework the profit model using the exponential

> You have been assigned to develop a model that can be used to schedule the nurses working in a maternity ward. a. What data would you collect as inputs to your model? b. Describe in words several appropriate objective functions for your model. c. Describ

> You have been assigned to develop a model that can be used to schedule employees at a local fast food restaurant. Assume that computer technology has advanced to the point where very large problems can be solved on a PC at the restaurant. a. What data wo

> Three bonds, as listed in the file, are currently for sale. Each bond has a face value of $100. Every six months, starting six months from the current date and ending at the expiration date, each bond pays 0.5*(coupon rate)*(Face value). At the expiratio

> Based on Glassey and Gupta (1978). A paper recycling plant processes box board, tissue paper, newsprint, and book paper into pulp that can be used to produce three grades of recycled paper. The prices per ton and the pulp contents of the four inputs are

> During each six-hour period of the day, the Bloomington Police Department needs at least the number of police officers shown in the file P04_125.xlsx. Police officers can be hired to work either 12 consecutive hours or 18 consecutive hours. Police office

> Based on Robichek et al. (1965). At the beginning of month 1, Finco has $35,000 in cash. At the beginning of months 1, 2, 3, and 4, Finco receives certain revenues, after which it pays bills. (See the file P04_124.xlsx.) Any money left over can be invest

> Based on Dobson and Kalish (1988). Chandler Enterprises produces two competing products, A and B. The company wants to sell these products to two groups of customers. The values each customer places on a unit of A and B are shown in the file. Each custom

> Based on Carino and Lenoir (1988). Brady Corporation produces cabinets. Each week, Brady requires 90,000 cubic feet of processed lumber. The company can obtain lumber in two ways. First, it can purchase lumber from an outside supplier and then dry it at

> Based on Franklin and Koenigsberg (1973). The city of Busville contains three school districts. The numbers of minority and nonminority students in each district are given in the file P04_121.xlsx. The local court has decided that each of the town’s two

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

> Consider the exponential curve y = 1000e-0.014x. Calculate y when x = 5; when x = 10; and when x = 20. For each of these values of x, find the percentage change in y when x increases by one unit. That is, find the percentage change in y when x increases

> Each week, Chemco can purchase unlimited quantities of raw material at $6 per pound. Each pound of purchased raw material can be used to produce either input 1 or input 2. Each pound of raw material can yield 2 ounces of input 1, requiring 2 hours of pro

> Flexco produces six products in the following manner. Each unit of raw material purchased yields 4 units of product 1, 2 units of product 2, and 1 unit of product 3. Up to 1200 units of product 1 can be sold, and up to 300 units of product 2 can be sold.

> Sunco Oil has three different processes that can be used to manufacture various types of gasoline. Each process involves blending oils in the company’s catalytic cracker. Running process 1 for an hour costs $20 and requires two barrels of crude oil 1 and

> Rylon Corporation manufactures Brute cologne and Chanelle perfume. The raw material needed to manufacture each type of fragrance can be purchased for $60 per pound. Processing 1 pound of raw material requires 1 hour of laboratory time. Each pound of proc

> Owens-Wheat uses two production lines to produce three types of fiberglass mat. The demand requirements (in tons) for each of the next four months are shown in the file P04_115.xlsx. If it were dedicated entirely to the production of one product, a line

> Each hour from 10 a.m. to 7 p.m., Bank One receives checks and must process them. Its goal is to process all checks the same day they are received. The bank has 5 check processing machines, each of which can process up to 1200 checks per hour. It takes o