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
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…
Cargo Loading. You are in charge of loading cargo ships for International Cargo Company (ICC) at a major East Coast port. You have been asked to prepare a loading plan for an ICC freighter bound for Africa. An agricultural commodities dealer would like to transport the following products aboard this ship:Commodity Tons Available Volume per Ton (cu.ft.) Profit per Ton ($)1 4,000 40 702 3,000 25 503 2,000 60 604 1,000 50 80You can elect to load any or all of the available commodities. However, the ship has three cargo holds with the following capacity restrictions:
Cargo Hold Weight Capacity (tons) Volume Capacity (cu.ft.)Forward 3,000 100,000Center 5,000 150,000Rear 2,000 120,000More than one type of commodity can be placed in the same cargo hold. However, because of balance considerations, the weight in the forward cargo hold must be within 10 percent of the weight in the rear cargo hold, and the center cargo hold…
Cargo Loading. You are in charge of loading cargo ships for International Cargo Company (ICC) at a major East Coast port. You have been asked to prepare a loading plan for an ICC freighter bound for Africa. An agricultural commodities dealer would like to transport the following products aboard this ship:Commodity Tons Available Volume per Ton (cu.ft.) Profit per Ton ($)1 4,000 40 702 3,000 25 503 2,000 60 604 1,000 50 80You can elect to load any or all of the available commodities. However, the ship has three cargo holds with the following capacity restrictions:
Cargo Hold Weight Capacity (tons) Volume Capacity (cu.ft.)Forward 3,000 100,000Center 5,000 150,000Rear 2,000 120,000More than one type of commodity can be placed in the same cargo hold. However, because of balance considerations, the weight in the forward cargo hold must be within 10 percent of the weight in the rear cargo hold, and the center cargo hold…
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_forwardJoburg 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…arrow_forwardThe Canadian government has built a large grain-shipping port at Churchill, Manitoba, on the Hudson Bay. Grain grown in southern Manitoba is carried by rail to Churchill during the open-water shipping season. Unfortunately the port is open only 50 days per year during July and August. This leads to some critical crew staffing decisions by management. The port has the capacity to load up to 7 ships simultaneously, provided that each loading bay has an assigned crew. The remote location and short shipping season results in a very high labor cost for each crew assigned, and management would like to minimize the number of crews. Ships arrive in a random pattern that can be modeled using the Poisson probability model. If a ship arrives and all available loading bays are filled, the ship will be delayed, resulting in a large cost that must be paid to the owner of the ship. This penalty was negotiated to encourage ship owners to send their ships to Churchill.Results of an initial analysis…arrow_forward
- Use 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_forwardA firm has established a distribution network for the supply of a raw material critical to itsmanufacturing. Currently there are two origins for this material, which must be shipped to threemanufacturing plants. The current network has the following characteristics: COSTS PLANT 1 PLANT 2 PLANT 3 SUPPLY Raw Material source 1 $6 $8 $9 400 Raw Material source 2 $4 $7 $3 600 Demand 500 500 500 Supply < Demand The firm has identified two potential sites for a third raw material source; these are identified asCandidate A and Candidate B. From A, the costs to ship would be $9 to Plant 1, $10 to Plant 2,and $12 to Plant 3. From B, these costs would be $11, $14, and $8. The new source, wherever itis located, will have a capacity of 500 units. Solve with the transportation method. Which siteshould be selected?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_forwardMacon Controls produces three different types of control units used to protect industrial equipment from overheating. Each of these units must be processed by a machine that Macon considers to be their process bottleneck. The plant operates on two 8-hour shifts, 5 days per week, 52 weeks per year. The table below provides the time standards at the bottleneck, lot sizes, and demand forecasts for the three units. Because of demand uncertainties, the operations manager obtained three demand forecasts (pessimistic, expected, and optimistic). The manager believes that a 25 percent capacity cushion is best. D Demand Forecast Component A B C Time Standard Processing Setup (hr/unit) (hr/lot) 0.04 1.0 0.20 4.4 0.05 8.5 Lot Size (units/lot) 50 75 100 Demand Forecast Pessimistic Expected Optimistic Pessimistic 16,000 10,000 18,000 a. How many machines are required to meet minimum (Pessimistic) demand, expected demand, and maximum (Optimistic) demand? (Enter your responses rounded up to the next…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,