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 6QP
a)
Summary Introduction
To determine: The sensitivity report using solver.
b)
Summary Introduction
To solve: The problem in spreadsheet.
c)
Summary Introduction
To determine: The optimal solution.
d)
Summary Introduction
To determine: Whether the optimal solution degenerate.
e)
Summary Introduction
To determine: Whether the optimal solution is unique.
f)
Summary Introduction
To determine: The change in solution when the plant in City C shuts for one day.
g)
Summary Introduction
To determine: The optimal objective function value for the given condition.
h)
Summary Introduction
To interpret: The reduced cost for shipping from City E to City M.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Suppliers A, B, and C supply components to three plants -- F, G, and H via two transshipment facilities -- T1 and T2. It costs $4 to ship from T1 regardless of final destination and $3 to ship to T2 regardless of supplier. Shipping to T1 from A, B, and C costs $3, $4, and $5, respectively, and shipping from T2 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. The Logistics Manager has come to you for help.
Answer the following question:
Which of these is not an element of the objective function?
Group of answer choices
4D-T1
600T1
9T2-G
3C-T2
Plz do fast asap
ChocoTown has a 4,000 square foot outlet store that serves five major customer regions, denoted A, B, C, D and E. The locations of the points representing the customer regions and their weekly demands are shown in the table below. The ChocoTown outlet store is at location (10,20).
Customer
Region
Location
Demand
($/week)
A
(0,10)
4,000
B
(0,20)
2,000
C
(20,0)
4,000
D
(30,20)
3,000
E
(40,10)
2,000
CandyTime is a new competitor of ChocoTown and they are considering opening a 4,000 square foot store at location (30,10). Assume the travel time from location i to j in minutes equals the straight line distance from i to j.
a) Use the spatial interaction model to estimate the demand that would be captured by the new CandyTime store.
Demand captured by CandyTime = ___________
b) Suppose the CandyTime store is located at (30,10), but it is not 4,000 square feet. Use the spatial interaction model to find the size in square feet for…
Joburg Logistics (JL), a distributor of various products in
Polokwane receives their products from suppliers in
Durban, Cape Town, and Johannesburg. The company's
management is concerned with the supply cost of a
certain product that is supplied by a manufacturer in
Durban. Currently JL has a contract with Transnet Freight
Rail (TFR) for the transport of the orders from Durban. The
following are the rail rate and transit time:
Transit time (rail)
Rail rate
5 days
R2.75/unit
The impact on the transport and inventory carrying cost
because of this arrangement is a concern of the logistics
director, Mrs Micky. As a result, Mrs Micky has requested
the logistics manager to evaluate the option to use a 3PL
service provider for the shipment from Durban to
Johannesburg. The logistics manager has identified a
certain 3PL company that has the capacity to perform this
contract. However, in order to provide a transport rate, the
3PL company required the size and frequency of orders.
JL currently…
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
- The Tinkan Company produces one-pound cans for the Canadian salmon industry. Each year the salmon spawn during a 24-hour period and must be canned immediately. Tinkan has the following agreement with the salmon industry. The company can deliver as many cans as it chooses. Then the salmon are caught. For each can by which Tinkan falls short of the salmon industrys needs, the company pays the industry a 2 penalty. Cans cost Tinkan 1 to produce and are sold by Tinkan for 2 per can. If any cans are left over, they are returned to Tinkan and the company reimburses the industry 2 for each extra can. These extra cans are put in storage for next year. Each year a can is held in storage, a carrying cost equal to 20% of the cans production cost is incurred. It is well known that the number of salmon harvested during a year is strongly related to the number of salmon harvested the previous year. In fact, using past data, Tinkan estimates that the harvest size in year t, Ht (measured in the number of cans required), is related to the harvest size in the previous year, Ht1, by the equation Ht = Ht1et where et is normally distributed with mean 1.02 and standard deviation 0.10. Tinkan plans to use the following production strategy. For some value of x, it produces enough cans at the beginning of year t to bring its inventory up to x+Ht, where Ht is the predicted harvest size in year t. Then it delivers these cans to the salmon industry. For example, if it uses x = 100,000, the predicted harvest size is 500,000 cans, and 80,000 cans are already in inventory, then Tinkan produces and delivers 520,000 cans. Given that the harvest size for the previous year was 550,000 cans, use simulation to help Tinkan develop a production strategy that maximizes its expected profit over the next 20 years. Assume that the company begins year 1 with an initial inventory of 300,000 cans.arrow_forwardUse the table below to answer the following question(s). The Riviera Transport Company (RTC) produces car accessories at two plants: Dallas and Atlanta. They ship them to major distribution centers in Houston, San Jose, Jacksonville, and Memphis. The accounting, production, and marketing departments have provided the information in the table below, which shows the unit cost of shipping between any plant and distribution center, plant capacities over the next planning period, and distribution center demands. RTC's supply chain manager faces the problem of determining how much to ship between each plant and distribution center to minimize the total transportation cost, not exceed available capacity, and meet customer demand. Assume Xij = amount shipped from plant i to distribution center j, where i = 1 represents Dallas, i = 2 represents Atlanta, j = 1 represents Houston, and so on. Transportatio In Model Data Distribution Center Plant Houston Memphis 18.48 Dallas 13.00 Atlanta 10.75…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_forwardAdelie Enterprises is exploring a new service to provide weekly delivery of grocery items to homes in the greater Greenwood area. The company’s customers place Web-based orders and Adelie’s team assembles and delivers the orders in specially designed cardboard boxes. Management, interested in locating a supplier that can provide boxes cheaply and efficiently, has discovered that each potential supplier’s ability to satisfy the company’s requirements is influenced by the level of demand. The following table provides Adelie’s vendor selection criteria, criterion weights, and rankings (1–10 with 10 being the highest) under the assumption that low, moderate,or high demand is generated for their service.a. Which supplier should be selected if there is low demand for Adelie’s new service? Which supplier should be selected under moderate demand assumptions? Under high demand assumptions?b. Which supplier is selected if Adelie evaluates each alternative using a Maximin decision criterion (see…arrow_forwardJohn Clooney, a bush pilot in Alaska, makes regular charter flights in his floatplane to various towns and cities in western Alaska. His passengers include hunters, fishermen, backpackers and campers, and tradespeople hired for jobs in the different localities. He also carries some cargo for delivery. The following network shows the possible air routes between various towns and cities John might take (with the times, in hours). For safety reasons, he flies point-to-point, flying over at least one town along a route, even though he might not land there. In the upcoming week John has scheduled charter flights for Kotzebue, Nome, and Stebbins. Determine the shortest route between John’s home base in Anchorage and each of these destinations.arrow_forward
- DM Office Products (DMOP) is a wholesale supplier of office products with one facility in Pennsylvania. They have decided to build a new distribution warehouse in the state of New York to help serve the growing demand in that market. They have four major customers located in Buffalo, Syracuse, Albany, and New York City. Though New York City is the largest market, it also has the greatest competition and DMOP is not a major player there. When DMOP ships an order to a customer they use their own small fleet of two trucks to deliver, so the cost of delivery is essentially the same for a full or partially full truck. The expected number of annual shipments to each city and their coordinates on an x, y grid is shown in the following table. CITY Buffalo Syracuse Albany New York x-COORDINATES 370 1,465 2,345 2,320 y-COORDINATES 895 945 675 70 NUMBER OF SHIPMENTS 96 100 140 80 Use the centroid method to recommend a location for the new warehouse for DMOP (Round your answers to 1 decimal…arrow_forwardIn the past, Peter Kelle's tire dealership in Baton Rouge sold an average of 1,000 radials each year. In the past 2 years, 240 and 240, respectively were sold in fall, 350 and 320 in winter,150 and165 in spring, and 340 and 195 in summer. With a major expansion planned, Kelle projects sales next year to increase to 1,200 radials. Based on next year's projected sales, the demand for each season is going to be (enter your responses as whole numbers): Fall: Winter: Spring: Summer:arrow_forwardMark Ewing has decided to enter contract with uber service provider in his area. The driver offers a car variety of mileage or distance to be travelled to him. All contracts were to be signed for three years. The first option has a monthly rent of P3,000, with a total mileage allowance of 36,000 kilometers (an average of 12,000 kilometers per year) and a cost of P35 per kilometer for any kilometers over 36,000. The following table summarizes each of the Uber Service Contract offered to him: 3-Year Contract Monthly Cost Mileage Allowance Cost Per Excess Kilometer Option A P3,000 30,000 P 35 Option B P3,500 45,000 P 25 Option C P4,000 54,000 P 15 Mark has estimated that, during the 3 years of the agreement, there is a 40% chance he will drive an average of 12,000 kilometers per year, a 30% chance he will drive an average of 15,000 miles per year, and a 30% chance that he will drive 18,000 miles per year. In evaluating the options, Mark would…arrow_forward
- Trips Logistics, a third-party logistics firm that provides warehousing and other logistics services, is facing a decision regarding the amount of space to lease for the upcoming three-year period. The general manager has forecast that Trips Logistics will need to handle a demand of 100,000 units for each of the next three years. Historically, Trips Logistics has required 1,000 square feet of warehouse space for every 1,000 units of demand. For the purposes of this discussion, the only cost Trips Logistics faces is the cost for the warehouse. Trips Logistics receives revenue of $1.22 for each unit of demand. The general manager must decide whether to sign a three-year lease or obtain warehousing space on the spot market each year. The three-year lease will cost $1 per square foot per year, and the spot market rate is expected to be $1.20 per square foot per year for each of the three years. Trips Logistics has a discount rate of k = 0.1.arrow_forwardThe Winston Company has four distribution centers (A, B, C, and D) that require 40,000, 60,000, 30,000, and 50,000 gallons of de-ionized water, respectively, per month for cleaning their long-haul trucks. Three de-ionized water wholesalers (1, 2, and 3) indicated their willingness to supply as many as 50,000, 70,000, and 60,000 gallons, respectively. The total cost (shipping plus price) of delivering 1,000 gallons of de-ionized water from each wholesaler to each distribution center is shown in the following table: Distribution Center Wholesaler A B D $1.90 $1.90 $1.70 $1.60 $1.70 2 $1.40 $1.60 $1.70 3 $1.60 $1.50 $1.80 $1.40 a. Which of the following statements regarding setting up the tableau for the optimal solution for this problem is true? O A. The sum of supply capacities is greater than the sum of demands, so a dummy distribution center is needed. O B. The sum of supply capacities is less than the sum of demands, so a dummy wholesaler is needed. O C. The sum of supply capacities…arrow_forwardThe Winston Company has four distribution centers (A, B, C, and D) that require 40,000, 60,000, 30,000, and 50,000 gallons of de-ionized water, respectively, per month for cleaning their long-haul trucks. Three de-ionized water wholesalers (1, 2, and 3) indicated their willingness to supply as many as 50,000, 70,000, and 60,000 gallons, respectively. The total cost (shipping plus price) of delivering 1,000 gallons of de-ionized water from each wholesaler to each distribution center is shown in the following table: Distribution Center Wholesaler A B 1 $1.70 $1.90 $1.60 $1.70 2 $1.40 $1.90 $1.60 $1.70 3 $1.60 $1.50 $1.80 $1.40 a. Which of the following statements regarding setting up the tableau for the optimal solution for this problem is true? O A. The sum of supply capacities is greater than the sum of demands, so a dummy distribution center needed, O B. The sum of supply capacities is less than the sum of demands, so a dummy wholesaler is needed. C. The sum of supply capacities…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,