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
Question
Chapter 3, Problem 3QP
Summary Introduction
To develop: A spreadsheet model for the problem and solve it using solver.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Please use excel for this problem
A furniture manufacturer produces two types of tables – country and contemporary – using three types of machines. The time required to produce the tables on each machine is given in the following table:
Â
Machine
Country
Contemporary
Total Machine Time
Available Per Week
Router
3.5
4.0
1,000
Sander
4.5
6.5
2,000
Polisher
3.0
2.0
1,500
Â
Â
Country tables sell for $395 and contemporary tables sell for $515.  Management has determined that at least 25% of the tables made should be country and at least 38% should be contemporary. How many of each type of table should the company manufacture if it wants to maximize its revenue?
Formulate an LP model for this problem
Create the spreadsheet model and use Solver to solve the problem.
Consider the following linear programming model with 4 regular constraints:Maximize 3X + 5Y (a) Draw your graph in the space below:subject to: 4X + 4Y ≤ 48 (constraint #1) 4X + 3Y ≤ 50 (constraint #2) 2X + 1Y ≤ 20 (constraint #3) X ≥ 2 (constraint #4) X, Y ≥ 0 (non-negativity constraints)(a) Which of the constraints is redundant? Constraint #______.Justify by drawing a graph similar to Figure 7.14 on p.263.(b) Is point (9,3) a feasible solution? _____. Explain your answer (by analyzing each of the constraints).Constraint #1: _______________________________________________________________Constraint #2: _______________________________________________________________Constraint #3: _______________________________________________________________Constraint #4: ______________________________________________________________
1. Given the following linear programming model:
  Minimize Z = 480x1 + 160x2  subject to    x1 + x2 >= 40    x1 + 4x2 >= 60   3x1 + x2 >= 60    x1 >= 0,  x2 >= 0
a. Solve the LP model graphically and explain the solution result.
b. Develop a spreadsheet model and solve using Excel Solver. What is the optimal solution?
Â
2. Provident Capital Corp. specializes in investment portfolios designed to meet the specific risk tolerances of its clients. A client contacted Provident with P2,000,000 available to invest.
Provident’s investment advisor recommends a portfolio consisting of two investment funds: the Dynamic fund and the Diversified fund. The Dynamic fund has a projected annual return of 10%, and the Diversified fund has a projected annual return of 8%.
The investment advisor requires that at most P1,400,000 of the client’s funds should be invested in the Dynamic fund. Provident’s services include a risk rating for each investment alternative. The Dynamic…
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
- Solve Problem 1 with the extra assumption that the investments can be grouped naturally as follows: 14, 58, 912, 1316, and 1720. a. Find the optimal investments when at most one investment from each group can be selected. b. Find the optimal investments when at least one investment from each group must be selected. (If the budget isnt large enough to permit this, increase the budget to a larger value.)arrow_forwardThis problem is based on Motorolas online method for choosing suppliers. Suppose Motorola solicits bids from five suppliers for eight products. The list price for each product and the quantity of each product that Motorola needs to purchase during the next year are listed in the file P06_93.xlsx. Each supplier has submitted the percentage discount it will offer on each product. These percentages are also listed in the file. For example, supplier 1 offers a 7% discount on product 1 and a 30% discount on product 2. The following considerations also apply: There is an administrative cost of 5000 associated with setting up a suppliers account. For example, if Motorola uses three suppliers, it incurs an administrative cost of 15,000. To ensure reliability, no supplier can supply more than 80% of Motorolas demand for any product. A supplier must supply an integer amount of each product it supplies. Develop a linear integer model to help Motorola minimize the sum of its purchase and administrative costs.arrow_forwardTwo poultry farms supply companies with chicken feeds. The unit costs of shipping from the farms to the companies are given on the table below. The farm's goal is to minimize the cost of meeting customer's demands. Â For questions a and b;Â (a) Generate a mathematical model for finding the least cost way of shipping chicken feeds from the farms to the companies. (b) if the demand of company number 2 increased by 3 units. By how much would the costs increase? Show solution. (c). Solve the total cost using the solver add-in in excel.arrow_forward
- 1. True/FalseA company wants to hire 4 vendors for the sale of 4 products, they could only sell one type of product. The following table (image) indicates what each seller charges for selling each of the products. The company wants to assign each seller a product, find all possible optimal assignments by pinpointing their optimal cost. answer the following:a) It is an allocation model T() F()b) It is not a transportation model T() F()c) The model is not balanced T() F()d) The problem has exactly 24 feasible points T() F()arrow_forwardA refinery manufactures two grades of jet fuel, Fl and F2, by blending four types of gasoline, A. B, C, and D. Fuel Fl uses gasolines A. B. C, and D in the ratio 1:1:2:4, and fucl F2 uses the ratio 2:2:1:3. The supply limits for A, B.C, and D are 1000, 1200, 900, and 1500 bbl/day, respectively. The costs per bbl for gasolines A, B, C, and D are $120, $90, $100, and $150, respectively. Fucls Fl and F2 sell for $200 and $250 per bbl, respectively. The minimum demand for F1 and F2 is 200 and 400 bbl/day, respectively. Develop an LP model to determine the optimal production mix for F1 and F2, and find the solution using Solverarrow_forwardA refinery manufactures two grades of jet fuel, Fl and F2, by blending four types of gasoline, A. B, C, and D. Fuel Fl uses gasolines A. B. C, and D in the ratio 1:1:2:4, and fucl F2 uses the ratio 2:2:1:3. The supply limits for A, B.C, and D are 1000, 1200, 900, and 1500 bbl/day, respectively. The costs per bbl for gasolines A, B, C, and D are $120, $90, $100, and $150, respectively. Fucls Fl and F2 sell for $200 and $250 per bbl, respectively. The minimum demand for F1 and F2 is 200 and 400 bbl/day, respectively. Develop an LP model to determine the optimal production mix for F1 and F2,arrow_forward
- Using Excel Solve the following LP Maximize $4x + $5y Subject to       2x + 3y ≤ 20 (labor, in hours)                                6x + 6y ≤ 36 (materials, in pounds)                  4x + 4y ≤ 40 (storage, in square feet)                                x, y ≥ 0 a) Write the original optimal solution and objective function value. b) What is the optimal solution and objective function value if you acquire 2 additional pounds of material? c) What is the optimal solution and objective function value if you acquire 1.5 additional hours of labor?arrow_forwardJefferson Distributing is analyzing distribution networks with either 4, 5, 6 or 7 warehouses to serve 200 major customers in Europe. Relevant costs include transportation cost from the warehouses to customers, fixed facility costs, and inventory costs in the warehouses. The table below shows the annual transportation cost produced by a facility location software tool for locating 4, 5, 6 or 7 warehouses for this company. Suppose that annual inventory cost for the network can be modeled as $800,000 times the square root of the number of warehouses. Thus, if the network has 4 warehouses, then the annual inventory cost would be $800,000 x √4= $1,600,000.  Number of Warehouses Transportation Cost 4 8,000,000 5 6,500,000 6 5,000,000 7 4,400,000   a)  If the annual fixed cost per warehouse is $1,000,000, how many warehouses should there be to minimize the total (transportation + warehouse + inventory) cost?                     b)  Now suppose the…arrow_forwardI will need the excel "solver" solution to be able to solve. #4) Solve the following LP Maximize $5x + $6y Subject to           2x + 3y ≤ 10 (labor, in hours)                                6x + 6y ≤ 36 (materials, in pounds)                                7x + 5y ≤ 40 (storage, in square feet)                                x, y ≥ 0 a) Write the original optimal solution and objective function value. b) What is the optimal solution and objective function value if you acquire 2 additional pounds of material? c) What is the optimal solution and objective function value if you acquire 1.5 additional hours of labor? d) What is the optimal solution and objective function value if you give up 1 hour of labor and get 1.5 pounds of material? e) What is the optimal solution and objective function value if you introduce a new product that has a profit contribution of $2? Each unit of this product will use 1 hour of labor, 1 pound of material, and 2 square feet of storage space.arrow_forward
- Fred Block Company has orders for 90 tons of concrete blocks at three suburban locations as follows: Northwood -- 25 tons, Westwood -- 45 tons, and Eastwood -- 20 tons. Fred has two plants, each of which can produce 50 tons per week. Delivery cost per ton from each plant to each suburban location is shown as follows                    Delivery Cost Per Ton           Northwood         Westwood        Eastwood  Plant1         20               30              40          Plant 2        30               40              45 a. Develop a network representation of this distribution problem. b. Develop a linear program model that can be used to determine the plan that will minimize total distribution costs. c. Solve the problem to describe the distribution plan and show total distribution cost.(Submit Excel files).arrow_forwardHart Manufacturing makes three products. Each product requires manufacturing operations in three departments: A, B, and C. The labor-hour requirements, by department, are as follows: During the next production period, the labor-hours available are 450 in department A, 350 in department B, and 50 in department C. The profit contributions per unit are $25 for product 1, $28 for product 2, and $30 for product 3. a. Formulate a linear programming model for maximizing total profit contribution. b. Solve the linear program formulated in part (a). How much of each product should be produced, and what is the projected total profit contribution? c. After evaluating the solution obtained in part (b), one of the production supervisors noted that production setup costs had not been taken into account. She noted that setup costs are $400 for product 1, $550 for product 2, and $600 for product 3. If the solution developed in part (b) is to be used, what is the total profit contribution after taking…arrow_forwardTwo plants supply three customers with medical supplies. The unit costs of shipping from the plants to the customers, along with the supplies and demands, are given in Table below. The company’s goal is to minimize the cost of meeting customers’ demands.           From                                             To   Customer 1 Customer 2 Customer 3 Supply Plant 1 55 65 80 35 Plant 2 10 15 25 50 Demand 10 10 10    Formulate a linear programming (LP) model for this problem.  Use solver to find the optimal transportation rule                   As a management science student, the MD of the company seeks your expert advice on ways in which to determine the optimal                   transportation rule. Advise the MD, providing detailed explanation       using your answer obtain in (b). Write your answer in a form of a report to the MD.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,