Spreadsheet Modeling and Decision Analysis: A Practical Introduction to Business Analytics
7th Edition
ISBN: 9781285418681
Author: Cliff Ragsdale
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 4, Problem 21QP
Summary Introduction
To determine: The sensitivity report using solver.
a)
Summary Introduction
To determine: Whether the optimal solution is unique.
b)
Summary Introduction
To determine: The location that receives the fewest cars.
c)
Summary Introduction
To determine: The increase in the cost for the company for the given information.
d)
Summary Introduction
To determine: The impact the given information would have on the optimal objective function value.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solutionStudents have asked these similar questions
Using the grid technique to determine the least-cost location (warehouse) for this problem:
Tons
Rate
X - Coordinates
Y – Coordinates
S1
200
0.5
2
14
S2
300
0.6
6
10
M1
100
1
2
2
M2
100
2
10
14
M3
100
1
14
18
M4
100
2
14
6
The Grid center coordination is ____ and _____
(round the results to 1 decimal place)
Group of answer choices
(9.9; 7.8)
(5.5; 6.9)
(8.7; 10.5)
(10.5; 6.4)
(9.9; 8.9)
Please answer:
b) Using a computer software for solving LP, the objective value at the optimal solution Minimum number of total miles traveled (objective
value)equals=
(round your response to a whole number).
A fertilizer manufacturer has to fulfill supply contracts to its two main customers (650 tons to Customer A and 800 tons to Customer B). It can meet this demand by shipping existing inventory from any of its three warehouses. Warehouse 1
has 400 tons of inventory onhand, Warehouse 2 (W2) has 500 tons, and Warehouse 3 (W3) has 600 tons. The company would like to arrange the shipping for the lowest cost possible, where the per-ton transit costs are as follows:
W 1
W 2
W 3
$7.50
$6.75
$6.25
$7.00
$6.50
$8.00
Customer A
Customer B
Write the objective function and the constraint in equations. Let V;= tons shipped to customer i from warehouse j, and so on. For example, VA1 = tons shipped to customer A from warehouse W1.
This exercise contains only parts b, c, d, e, and f.
b) The objective function for the LP model =
Minimize Z =
$7.50
+ $6.25
+ $6.50
(shipping cost to customer A)
V +
$6.75
+ $7.00
+ $8.00
(shipping cost to customer B)
c) Subject to:
Customer A's demand
Customer B's demand…
Chapter 4 Solutions
Spreadsheet Modeling and Decision Analysis: A Practical Introduction to Business Analytics
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_forwardA retail store in Des Moines, Iowa, receives shipments of a particular product from KansasCity and Minneapolis. Let x 5 number of units of the product received from Kansas City y 5 number of units of the product received from Minneapolisa. Write an expression for the total number of units of the product received by the retail store in Des Moines. b. Shipments from Kansas City cost $0.20 per unit, and shipments from Minneapolis cost$0.25 per unit. Develop an objective function representing the total cost of shipments to Des Moines. c. Assuming the monthly demand at the retail store is 5000 units, develop a constraint that requires 5000 units to be shipped to Des Moines. d. No more than 4000 units can be shipped from Kansas City, and no more than 3000 units can be shipped from Minneapolis in a month. Develop constraints to model this situation. e. Of course, negative amounts cannot be shipped. Combine the objective function and constraints developed to state a mathematical model…arrow_forwardgiven the transportation problem, assume that the demands at the 4 destinations are 5, 5, 7, and 8 units, respectively, and the supplies at the 4 sources are 7, 3, 7 and 8 units, respectively.C₁₁=7ㅤ C₁₂=9ㅤ C₁₃ = 1ㅤ C₁₄ = 10 C₂₁= 22ㅤ C₂₂ = 25ㅤ C₂₃ = 16ㅤ C₂₄ = 26 С₃₁ = 28ㅤ C₃₂ = 32ㅤ C₃₃ = 24ㅤ Сз₄ = 32 C₄₁ = 12ㅤ C₄₂ = 14ㅤ C₄з = 6ㅤ C₄₄ = 16where m=4, n=4what is the optimal solution using vogel's approximation method, least-cost method, and northwest-corner rule?arrow_forward
- Companies A, B, and C supply components to three plants (F, G, and H) via two crossdocking facilities (D and E). It costs $4 to ship from D regardless of final destination and $3 to ship to E regardless of supplier. Shipping to D from A, B, and C costs $3, $4, and $5, respectively, and shipping from E to F, G, and H costs $10, $9, and $8, respectively. Suppliers A, B, and C can provide 200, 300 and 500 units respectively and plants F, G, and H need 350, 450, and 200 units respectively. Crossdock facilities D and E can handle 600 and 700 units, respectively. Logistics Manager, Aretha Franklin, had previously used "Chain of Fools" as her supply chain consulting company, but now turns to you for some solid advice. What is the objective function? Group of answer choices Max Z = $3AD + $3AE + $4BD + $3BE + $5CD + $3CE + $4DF + $4DG + $4DH + $10EF + $9EG + $8EH Min Z = $3AD + $3AE + $4BD + $3BE + $5CD + $3CE + $4DF + $4DG + $4DH + $10EF + $9EG + $8EH Min Z = $3AD + $3BE + $5CD + $3CE…arrow_forwardProblem-solving is a critical technique used in many businesses to overcome challenges and improve services.In the case of CoffeeVille, they faced a limitation in their delivery service which was affecting their business operations. The problem-solving process was used to find a solution to this issue.Possible solutions could have included hiring more delivery personnel, optimizing delivery routes, or implementing a delivery scheduling system. After evaluating these solutions, they might have chosen to hire more delivery personnel and optimize delivery routes as the most feasible and impactful solution. After implementing these changes, they would have reviewed their delivery service to ensure that the problem was effectively solved. This is a simplified example, but it illustrates how problem-solving can be used to overcome challenges and improve services in a business context. Perform a self evaluation of the critical thinking skills. This must include writing and responding to five…arrow_forwardCompanies A, B, and C supply components to three plants (F, G, and H) via two crossdocking facilities (D and E). It costs $4 to ship from D regardless of final destination and $3 to ship to E regardless of supplier. Shipping to D from A, B, and C costs $3, $4, and $5, respectively, and shipping from E to F, G, and H costs $10, $9, and $8, respectively. Suppliers A, B, and C can provide 200, 300 and 500 units respectively and plants F, G, and H need 350, 450, and 200 units respectively. Crossdock facilities D and E can handle 600 and 700 units, respectively. Logistics Manager, Aretha Franklin, had previously used "Chain of Fools" as her supply chain consulting company, but now turns to you for some solid advice. Set up the solution in Excel and solve with Solver. What are total costs?arrow_forward
- A company manufactures a product at its plants in A, B, and C, then ships the product to six customers in G, H, J, K, and L. The monthly capacity of plants A, B, and Care 7300, 7400, and 7500 units, respectively. The monthly demand from customers G, H, J, K, and Lare. 1200, 8900, 7600, 2600, and 1500 units, respectively. Use Excel Solver to find the optimal distribution plant that will give the lowest total monthly shipping cost if the shipping cost per unit (in dollars per unit) are as follows: from A to G, H, J, K, and L are 4, 9, 20, 6, and 13 dollars per unit, respectively; from B to G, H, J, K, and Lare 7, 6, 12, 2, and 9 dollars per unit, respectively; from C to G, H, J, K, and Lare 12, 7, 3, 17, and 6 dollars per unit, respectively. The optimal total shipping costs is dollars per montharrow_forwardBETTER PRODUCTS COMPANY has decided to start manufacturing four new products in three plants that currently have excess production capacity. The products require comparable production effort per unit, so the available production capacity in the plants is measured by the number of units of any product that can be manufactured per day. a)Formulate this problem as a transportation problem by constructing the appropriate parameter table. I already have an optimal solution (it's from the book). (I dont understand)-------------------------------------------------------------------------OPTIMAL SOLUTION:It has basic variables (assignments) X₁₂ = 30, X₁₃ = 30, X₁₅ = 15, X₂₄ = 15, X₂₅ = 60, X₃₁ = 20 and X₃₄ = 25, so that: Plant 1 produces all of products 2 and 3.Plant 2 produces 37.5% of product 4.Plant 3 produces 62.5% of product 4 and all of product 1. The total cost of Z = $3 260 per day.-------------------------------------------------------------------------Instructions:Please explain this…arrow_forwardBETTER PRODUCTS COMPANY has decided to start manufacturing four new products in three plants that currently have excess production capacity. The products require comparable production effort per unit, so the available production capacity in the plants is measured by the number of units of any product that can be manufactured per day. a)Formulate this problem as a transportation problem by constructing the appropriate parameter table. I already have an optimal solution (it's from the book). (I dont understand)-------------------------------------------------------------------------OPTIMAL SOLUTION:It has basic variables (assignments) X₁₂ = 30, X₁₃ = 30, X₁₅ = 15, X₂₄ = 15, X₂₅ = 60, X₃₁ = 20 and X₃₄ = 25, so that: Plant 1 produces all of products 2 and 3.Plant 2 produces 37.5% of product 4.Plant 3 produces 62.5% of product 4 and all of product 1. The total cost of Z = $3 260 per day.-------------------------------------------------------------------------Instructions:Please explain this…arrow_forward
- BETTER PRODUCTS COMPANY has decided to start manufacturing four new products in three plants that currently have excess production capacity. The products require comparable production effort per unit, so the available production capacity in the plants is measured by the number of units of any product that can be manufactured per day. a)Formulate this problem as a transportation problem by constructing the appropriate parameter table. I already have an optimal solution (it's from the book). (I dont understand)-------------------------------------------------------------------------OPTIMAL SOLUTION:It has basic variables (assignments) X₁₂ = 30, X₁₃ = 30, X₁₅ = 15, X₂₄ = 15, X₂₅ = 60, X₃₁ = 20 and X₃₄ = 25, so that: Plant 1 produces all of products 2 and 3.Plant 2 produces 37.5% of product 4.Plant 3 produces 62.5% of product 4 and all of product 1. The total cost of Z = $3 260 per day.-------------------------------------------------------------------------Instructions:Please explain this…arrow_forwardfertilizer manufacturer has to fulfill supply contracts to its two main customers (650 tons to Customer A and 800 tons to Customer B). It can meet this demand by shipping existing inventory from any of its three warehouses. Warehouse 1 (W1) has 400 tons of inventory onhand, Warehouse 2 (W2) has 500 tons, and Warehouse 3 (W3) has 600 tons. The company would like to arrange the shipping for the lowest cost possible, where the per-ton transit costs are as follows: W 1 W 2 W 3 Customer A $7.50 $6.25 $6.50 Customer B $6.75 $7.00 $8.00 Write the objective function and the constraint in equations. Let Vij= tons shipped to customer i from warehouse j, and so on. For example, VA1=tons shipped to customer A from warehouse W1. This exercise contains only parts b, c, d, e, and f. Part 2 b) The objective function for the LP model =arrow_forwardThe question is included in the pictures.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,