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_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. 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_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. Set up the solution in Excel and solve with Solver. What are total costs?arrow_forwardA 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_forwardThe question is included in the pictures.arrow_forward
- Long-Life Insurance has developed a linear model that it uses to determine the amount of term life insurance a family of four should have, based on the current age of the head of the household. The equation is:y = 850 − .1xwherey = Insurance needed ($000)x = Current age of head of household a. Plot the relationship on a graph. b. Use the equation to determine the amount of term life insurance to recommend for a family of four if the head of the household is 30 years old.arrow_forwardB & Carrow_forwardWhich of the following is NOT a method to develop an initial solution to a transportation model? - Group of answer choices - None...all are methods to develop an initial solution - northwest corner method - linear programming - intuitive least cost methodarrow_forward
- A person starting in Columbus must visit Great Falls, Odessa, and Brownsville, and then return home to Columbus in one car trip. The road mileage between the cities is shown. Columbus Great Falls Odessa Brownsville Columbus --- 102 79 56 Great Falls 102 --- 47 69 Odessa 79 47 --- 72 Brownsville 56 69 72 --- a)Draw a weighted graph that represents this problem in the space below. Use the first letter of the city when labeling each b) Find the weight (distance) of the Hamiltonian circuit formed using the nearest neighbor algorithm. Give the vertices in the circuit in the order they are visited in the circuit as well as the total weight (distance) of the circuit.arrow_forwardHMO has 40 doctors to be apportioned among four clinics. The HMO decides to apportion the doctors based on the average weekly patient load for each clinic, given in the following table. Clinic A B C D Average Weekly 275 392 611 724 Patient Load Find the apportion of A and C using using Adam's Method. Use modified divisor 52. Doctors for clinic A Doctors for clinic B = %3D Doctors for clinic C= Doctors for clinic D =arrow_forwardPlease answer and provide what is needed please.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,