Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Business Analytics (MindTap Course List)
8th Edition
ISBN: 9781305947412
Author: Cliff Ragsdale
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 3, Problem 27QP
A manufacturer of prefabricated homes has decided to subcontract four components of the homes. Several companies are interested in receiving this business, but none can handle more than one subcontract. The bids made by the companies for the various subcontracts are summarized in the following table.
Assuming all the companies can perform each subcontract equally well, to which company should each subcontract be assigned if the home manufacturer wants to minimize payments to the subcontractors?
- a. Formulate an LP model for this problem.
- b. Create a spreadsheet model for this problem and solve it using Solver.
- c. What is the optimal solution?
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
The U.S. government is auctioning off oil leases at twosites: 1 and 2. At each site, 100,000 acres of land are to beauctioned. Cliff Ewing, Blake Barnes, and Alexis Pickens arebidding for the oil. Government rules state that no bidder canreceive more than 40% of the land being auctioned. Cliff hasbid $1,000/acre for site 1 land and $2,000/acre for site 2 land.Blake has bid $900/acre for site 1 land and $2,200/acre for site2 land. Alexis has bid $1,100/acre for site 1 land and$1,900/acre for site 2 land. Formulate a balanced transportationmodel to maximize the government’s revenue.
Boris Milkem’s financial firm owns six assets. Theexpected sales price (in millions of dollars) for each asset isgiven in Table 32. If asset 1 is sold in year 2, the firmreceives $20 million. To maintain a regular cash flow,Milkem must sell at least $20 million of assets during year1, at least $30 million worth during year 2, and at least $35million worth during year 3. Set up an IP that Milkem canuse to determine how to maximize total revenue from assetssold during the next three years. In implementing this model,how could the idea of a rolling planning horizon be used?
Doug Turner Food Processors wishes to introduce a new brand of dog biscuits composed of chicken and liver flavored biscuits that meet certain nutritional requirements. The liver flavored biscuits contain 1 unit of nutrient A and 2 units of nutrient B; the chicken flavored biscuits contain 1 unit of nutrient A and 4 units of nutrient B. According to federal requirements, there must be at least 40 units of nutrient A and 60 units of nutrient B in a package of the new mix. In addition, the company has decided that there can be no more than 12 liver flavored biscuits in a package. It costs 1¢ to make 1 liver flavored biscuit and 2¢ to make 1 chicken flavored. Doug wants to determine the optimal product mix for a package of the biscuits to minimize the firm's cost.
The aim of the objective function should be to (Maximize or Minimize) the objective value.
Chapter 3 Solutions
Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Business Analytics (MindTap Course List)
Ch. 3 - Prob. 1QPCh. 3 - Prob. 2QPCh. 3 - Prob. 3QPCh. 3 - Prob. 4QPCh. 3 - Prob. 5QPCh. 3 - Prob. 6QPCh. 3 - Refer to question 19 at the end of Chapter 2....Ch. 3 - Prob. 8QPCh. 3 - Prob. 9QPCh. 3 - Prob. 10QP
Ch. 3 - Prob. 11QPCh. 3 - Prob. 12QPCh. 3 - Prob. 13QPCh. 3 - Prob. 14QPCh. 3 - Prob. 15QPCh. 3 - Prob. 16QPCh. 3 - Prob. 17QPCh. 3 - Tuckered Outfitters plans to market a custom brand...Ch. 3 - Prob. 19QPCh. 3 - Prob. 20QPCh. 3 - Prob. 21QPCh. 3 - Prob. 22QPCh. 3 - Prob. 23QPCh. 3 - Prob. 24QPCh. 3 - Prob. 25QPCh. 3 - Prob. 26QPCh. 3 - A manufacturer of prefabricated homes has decided...Ch. 3 - Prob. 28QPCh. 3 - Prob. 29QPCh. 3 - Prob. 30QPCh. 3 - Prob. 31QPCh. 3 - Prob. 32QPCh. 3 - Prob. 33QPCh. 3 - Prob. 34QPCh. 3 - Prob. 35QPCh. 3 - Prob. 36QPCh. 3 - Prob. 37QPCh. 3 - Prob. 38QPCh. 3 - Prob. 39QPCh. 3 - Prob. 40QPCh. 3 - Prob. 41QPCh. 3 - Prob. 42QPCh. 3 - Prob. 43QPCh. 3 - Prob. 44QPCh. 3 - A natural gas trading company wants to develop an...Ch. 3 - Prob. 46QPCh. 3 - The CFO for Eagle Beach Wear and Gift Shop is in...Ch. 3 - Prob. 48QPCh. 3 - Prob. 1.1CCh. 3 - Prob. 1.2CCh. 3 - Prob. 1.3CCh. 3 - Prob. 1.4CCh. 3 - Prob. 2.1CCh. 3 - Prob. 2.2CCh. 3 - Prob. 2.3CCh. 3 - Prob. 2.4CCh. 3 - Prob. 2.5CCh. 3 - Kelly Jones is a financial analyst for Wolverine...
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, management and related others by exploring similar questions and additional content below.Similar questions
- Seas Beginning sells clothing by mail order. An important question is when to strike a customer from the companys mailing list. At present, the company strikes a customer from its mailing list if a customer fails to order from six consecutive catalogs. The company wants to know whether striking a customer from its list after a customer fails to order from four consecutive catalogs results in a higher profit per customer. The following data are available: If a customer placed an order the last time she received a catalog, then there is a 20% chance she will order from the next catalog. If a customer last placed an order one catalog ago, there is a 16% chance she will order from the next catalog she receives. If a customer last placed an order two catalogs ago, there is a 12% chance she will order from the next catalog she receives. If a customer last placed an order three catalogs ago, there is an 8% chance she will order from the next catalog she receives. If a customer last placed an order four catalogs ago, there is a 4% chance she will order from the next catalog she receives. If a customer last placed an order five catalogs ago, there is a 2% chance she will order from the next catalog she receives. It costs 2 to send a catalog, and the average profit per order is 30. Assume a customer has just placed an order. To maximize expected profit per customer, would Seas Beginning make more money canceling such a customer after six nonorders or four nonorders?arrow_forwardDoug Turner Food Processors wishes to introduce a new brand of dog biscuits composed of chicken and liver flavored biscuits that meet certain nutritional requirements. The liver flavored biscuits contain 1 unit of nutrient A and 2 units of nutrient B; the chicken flavored biscuits contain 1 unit of nutrient A and 4 units of nutrient B. According to federal requirements, there must be at least 40 units of nutrient A and 60 units of nutrient B in a package of the new mix. In addition, the company has decided that there can be no more than 16 liver flavored biscuits in a package. It costs 1¢ to make 1 liver flavored biscuit and 2¢ to make 1 chicken flavored. Doug wants to determine the optimal product mix for a package of the biscuits to minimize the firm's cost. The aim of the objective function should be to Minimize the objective value. The optimum solution is: Number of liver flavored biscuits in a package = nothing (round your response to two decimal places).arrow_forwardMichael, Inc. is an investment advisory firm that uses an asset allocation model that recommends the portion of each client's portfolio to be invested in a growth fund (G), an income fund (I), and a money market fund (M). To maintain diversity in each client's portfolio, the firm places limits on the percentage of each portfolio that may be invested in each of the three funds. General guidelines indicate that the amount invested in the growth fund must be between 20% and 40% of the total portfolio value. Similar percentages for the other two funds stipulate that between 20% and 50% of the total portfolio value must be in the income fund and at least 30% of the total portfolio value must be in the money market fund. In addition, the company attempts to assess the risk tolerance of each client and adjust the portfolio to meet the needs of the individual…arrow_forward
- 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 state that no bidder can receive more than 45% of the land being auctioned. Cliff has bid $2000 per acre for site 1 land and $1000 per acre for site 2 land. Blake has bid $1800 per acre for site 1 land and $1500 per acre for site 2 land.Alexis has bid $1900 per acre for site 1 land and $1300 per acre for site 2 land.a. Determine how to maximize the government’s revenue with a transportation model.b. Use SolverTable to see how changes in the government’s rule on 45% of all land being auctioned affect the optimal revenue. Why can theoptimal revenue not decrease if this percentage required increases? Why can the optimal revenue not increase if this percentage required decreases?arrow_forwardSolve each of these problems by computer and obtain the optimal values of the decision variables and the objective function. Maximize Z = 4x1 + 2x2 + 5x3 Subject to 1x1 + 2x2 + 1x3 ≤ 25 1x1 + 4x2 + 2x3 ≤ 40 3x1 + 3x2 + 1x3 ≤ 30 x1, x2, x3 ≥ 0 Maximize Z = 10x1 + 6x2 + 3x3 Subject to 1x1 + 1x2 + 2x3 ≤ 25 2x1 + 1x2 + 4x3 ≤ 40 1x1 + 2x2 + 3x3 ≤ 40 x1, x2, x3 ≥ 0arrow_forwardThe following table shows the amount spent by four U.S. airlines to fly one available seat 1 mile in the second quarter of 2014. Set up a system and then solve using technology. Airline United Continental American JetBlue Southwest Cost 14.9 14.6 11.9 12.4 Suppose that, on a 3,000 - mile New York to Los Angeles flight, United Continental, American, and Southwest flew a total of 240 empty seats, costing them a total of $100,920. If United Continental had three times as many empty seats as American, how many empty seats did each of these airlines carry on its flight? United Continental empty seats American empty seats Southwest empty seatsarrow_forward
- An investment advisor at RMC Financial Services wants to develop a model that can be used to allocate investment funds among four alternatives: stocks, bonds, mutual funds, and cash. For the coming investment period, the company developed estimates of the annual rate of return and the associated risk for each alternative. Risk is measured between 0 and 1, with higher risk value denoting more volatility and thus more uncertainty. The estimated rate of return for stocks is 10%, for bonds 3%, for mutual funds 4%, and cash 1%. The risk index for the stocks, bonds, and mutual funds investment is estimated as 0.8, 0.2, and 0.3, respectively. Because cash is held in money market funds, the annual return is lower, but it carries essentially no risk (i.e., risk index is zero for cash investment). The objective is to determine the portion of funds allocated to each investment alternatives in order to maximize the total annual return for the portfolio subject to the risk level the client is…arrow_forwardDoug Turner Food Processors wishes to introduce a new brand of dog biscuits composed of chicken- and liver-flavored biscuits that meet certain nutritional requirements. The liver-flavored biscuits contain 3 units of nutrient A and 1 unit of nutrient B; the chicken-flavored biscuits contain 3 units of nutrient A and 2 units of nutrient B. According to federal requirements, there must be at least 55 units of nutrient A and 50 units of nutrient B in a package of the new mix. In addition, the company has decided that there can be no more than 20 liver-flavored biscuits in a package. If it costs 3¢ to make 1 liver-flavored biscuit and 2¢ to make 1 chicken-flavored, what is the optimal product mix for a package of the biscuits to minimize the firm’s cost? Formulate this as a linear programming problem (write out the objective function and the constraints including non-negativity constraint). Solve this problem using the corner-point method or the iso-profit/iso-cost line method, giving the…arrow_forwardThe 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 bid- ding for the oil. Government rules state that no bidder can receive more than 45% of the land being auctioned. Cliff has bid $2000 per acre for site 1 land and $1000 per acre for site 2 land. Blake has bid $1800 per acre for site 1 land and $1500 per acre for site 2 land. Alexis has bid $1900 per acre for site 1 land and $1300 per acre for site 2 land. Determine how to maximize the government’s revenue with a transportation model.Use SolverTable to see how changes in the government’s rule on 45% of all land being auctioned affect the optimal revenue. Why can the optimal revenue not decrease if this percentage required increases? Why can the optimal revenue not increase if this percentage required decreases?arrow_forward
- An automobile manufacturer wants to award contracts for the supply of four different fuel injection system components. Four contracts have submitted bids on the components; the Table below summarizes the prices bid per unit. Where no entry is made, the contractor submitted no bid. Component Contractor 1 2 3 4 1 $25 - $30 $40 2 $28 $80 $28 - 3 - $75 $33 - 4 $30 $82 - $42 Demand (units) 15,000 30,000 10,000 20,000 The demand for a component does not have to be supplied completely by one contractor. In fact, certain contractors have indicated maximum quantities that can be supplied at the bid price. Contractor 1 can supply no more than 18,000 of item 4, contractor 2 no more than 3,000 of item 1, contractor 4 no more than 15,000 units of item 2 and no more than 5,000 of item 4. There is no provision that awards must go to the low bidder. The automobile manufacturer wants to determine how many units of each…arrow_forwardProblem There are three factories on the Momiss River. Each emits two types of pollutants, labeled P1 and P2, into the river. If the waste from each factory is processed, the pollution in the river can be reduced. It costs $1500 to process a ton of factory 1 waste, and each ton processed reduces the amount of P1 by 0.10 ton and the amount of P2 by 0.45 ton. It costs $1000 to process a ton of factory 2 waste, and each ton processed reduces the amount of P1 by 0.20 ton and the amount of P2 by 0.25 ton. It costs $2000 to process a ton of factory 3 waste, and each ton processed reduces the amount of P1 by 0.40 ton and the amount of P2 by 0.30 ton. The state wants to reduce the amount of P 1in the river by at least 30 tons and the amount of P2 by at least 40 tons. Use Solver to determine how to minimize the cost of reducing pollution by the desired amounts. Use SolverTable to investigate the effects of increases in the minimal reductions required by the state. Specifically, see what…arrow_forwardXYZ Mutual Fund is seeking to invest $80 million in real estate projects with high potential returns. The fund has identified several investment opportunities, each with a specific investment cost and expected economic benefit (see the table below). The goal is to maximize the total economic benefit by selecting a combination of projects to invest in while staying within the budget mentioned above. However, there are several constraints to consider, including the requirement to invest in at least four projects in total, with at least two of them being environmentally friendly (projects that are considered environmentally friendly are: 3, 5, 9, and 10). Additionally, if project 3 is selected, then project 4 must also be chosen, but project 4 can be chosen independently of project 3. The fund cannot make partial or fragmented investments, and projects cannot be purchased multiple times as each project is unique. The challenge is to determine the optimal combination of projects that will…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Practical Management ScienceOperations ManagementISBN:9781337406659Author:WINSTON, Wayne L.Publisher:Cengage,
Practical Management Science
Operations Management
ISBN:9781337406659
Author:WINSTON, Wayne L.
Publisher:Cengage,
Inventory Management | Concepts, Examples and Solved Problems; Author: Dr. Bharatendra Rai;https://www.youtube.com/watch?v=2n9NLZTIlz8;License: Standard YouTube License, CC-BY