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 5, Problem 3.2C
Summary Introduction
Case summary:
Company USE is a package delivery company operating in City A. The expense of Jet fuel is one of the largest operating costs incurred by the company. The company is looking to manage this cost. The goal is to identify the most economic fuel purchasing plan for the previous schedule.
To implement: A spreadsheet model for the given problem and solve it.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
The Easy Time Grocery chain operates in major metropolitan areas on the East Coast. The stores have a “no-frills” approach, with low overhead and high volume. They generally buy their stock in volume at low prices. However, in some cases they actually buy stock at stores in other areas and ship it in. They can do this because of high prices in the cities they operate in compared with costs in other locations. One example is baby food. Easy Time purchases baby food at stores in Albany, Binghamton, Claremont, Dover, and Edison and then trucks it to six stores in and around New York City. The stores in the outlying areas know what Easy Time is up to, so they limit the number of cases of baby food Easy Time can purchase. The following table shows the profit Easy Time makes per case of baby food, based on where the chain purchases it and at which store it is sold, plus the available baby food per week at purchase locations and the shelf space available at each Easy Time store per week:…
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 (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 =
Sinclair Plastics operates two chemical plants which produce polyethylene; the Ohio Valley plant which can produce up to 10,000 tons per month and the Lakeview plant which can produce up to 7,000 tons per month. Sinclair sells its polyethylene to three different auto manufacturing plants, Grand Rapids (demand = 3000 tons per month), Blue Ridge (demand = 5000 tons per month), and Sunset (demand = 4000 tons per month). The costs of shipping between the respective plants is shown in the table below:
Grand Rapids
Blue Ridge
Sunset
Ohio Valley
50
40
100
Lakeview
60
50
75
Implement the LP model in Solver and obtain the optimal shipping plan. What is the optimized cost?
Chapter 5 Solutions
Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Business Analytics (MindTap Course List)
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_forwardDavid Joseph is a supermarket chain manager based in Milwaukee, Wisconsin. He wants to establish business relations with China because China is part of the fastest-growing economies in the world. Through one of his Chinese employees, Wang Xin, David reached an agreement to import green tea from a farm animal products import and export company in Zhejiang Province, China. The goods should be in time to reach the sales peak of Thanksgiving. David himself is also a tea lover, and he is very struck by the quality of this tea, and the packaging is better than he expected. He is looking forward to tea selling in his supermarket and even tirelessly lets Wang Xin write some bilingual advertisements for tea in local mainstream newspapers or broadcast on the radio. Because of the small volume of transactions, the freight for each transaction is relatively high. To make a profit, David's accounting department suggested that the price of Chinese green tea could be set at a slightly higher price…arrow_forwardSunco Oil produces oil at two wells. Well 1 canproduce up to 150,000 barrels per day, and well 2 canproduce up to 200,000 barrels per day. It is possible toship oil directly from the wells to Sunco’s customersin Los Angeles and New York. Alternatively,Sunco could transport oil to the ports of Mobile andGalveston and then ship it by tanker to New Yorkor Los Angeles. Los Angeles requires 160,000 barrelsper day, and New York requires 140,000 barrels perday. The costs of shipping 1000 barrels betweenvarious locations are shown in the file P05_55.xlsx,where a blank indicates shipments that are not allowed.Determine how to minimize the transport costs inmeeting the oil demands of Los Angeles and New York.arrow_forward
- The figure below shows the possible routes from city A to city M as well as the cost (in dollars) of a trip between each pair of cities (note that if no arc joins two cities it is not possible to travel non-stop between those two cities). A traveler wishes to find the lowest cost option to travel from city A to city M. Which type of network optimization problem is used to solve this problem? Multiple Choice: Minimum Flow Problem Average-Cost Flow problem Maximum Flow Problem Shortest Path Problem Maximum-Cost Flow problemarrow_forwardPLEASE SHOW ALL FORMULA PharmaCo wants to determine how to deploy sales representatives across its Western U.S. region to support a new drug for obesity. Sales representatives will be located in a "home city", which they serve, in addition to cities with feasible commuting distance, with the objective that all cities must be served by at least one sales representative. The feasible connections between each city in the region are listed below (1 indicates a feasible connection, potential home cities are shown in columns, and cities served in rows): Potential Rep Home City Served? Albuquerque El Paso Denver Phoenix San Diego Los Angeles San Francisco Portland Seattle Las Vegas Salt Lake City Albuquerque 1 1 1 1 0 0 0 0 0 0 0 El Paso 1 1 0 1 0 0 0 0 0 0 0 Denver 1 0 1 1 0 0 0 0 0 0 1 Phoenix 1 1 1 1 1 1 0 0 0 1 1 San Diego 0 0 0 1 1 1…arrow_forwardSRS Limited is a manufacturing company in Asia (China and India) specializing in bicycles that they sell to the USA, Canada, and Africa. Recently they acquired JAP Bicycle in Jamaica and supplying them with bikes and parts. They want to get in the automatic wheelchair for the physical challenge. SRS Limited contracted you as a transportation consultant to see their shipment from Asia to Canada, the East and West coast of the USA, Jamaica, and Africa. a. Differentiate the difference between intra-modal and inter-modal and why you will have recommended one over the other.arrow_forward
- Luminous lamps have three factories - F1, F2, and F3 with production capacity 30, 50, and 20 units per week respectively. These units are to be shipped to four warehouses W1, W2, W3, and W4 with requirements of 20, 40, 30, and 10 units per week respectively. The transportation costs (in Rs.) per unit between factories and warehouses are given below. Find an initial basic feasible solution.arrow_forwardA transportation problem involves the following costs, supply, and demand: Solve this problem by using the computer.arrow_forwardDevelop a LP model for the transportation problem (cost problem). Solve the LP model to find the optimal solution and the minimum total cost.arrow_forward
- The Hamilton County Local Government has eight sectors which need fire protection. Adequate Fire protection can be provided in each sector either by building a fire station in that sector, or by building a fire station in another sector which is no more than a 12-minute drive away. The time to drive between the centers of each pair of sectors is given in the following table. (Because of one-way streets and left turns the times are not symmetric.) The cost to build a fire station is the same in each sector. Formulate an integer programming model to choose which sectors should have their own fire station. Solve the model by using Excel Solver.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 C D 1 $1.30 $1.40 $1.80 $1.60 2 $1.30 $1.50 $1.80 $1.60 3 $1.60 $1.40 $1.70 $1.50 a. Determine the optimal solution. Show that all capacities have been exhausted and that all demands can be met with this solution. b. What is the total cost of the solution?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 its 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 C D 1 $1.30 $1.40 $1.80 $1.60 2 $1.30 $1.50 $1.80 $1.60 3 $1.60 $1.40 $1.70 $1.50 Determine the optimal solution. Show that all capacities have been exhausted and that all demands can be met with this solution. (use below table format, numbers are generic, erase them) Distribution Center Supply Wholesaler A B C D 1 xx xx x x 400-200-150-50=0 2…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,