PLEASE SHOW HOW TO DO THIS WITH EXCEL. Construct and solve a linear optimization model that defies the supply chain strategy that meets the demand of each retail outlet at minimum total shipping cost. ### Shipping Costs and Supply Chain Analysis#### Shipping Costs ($ per bracelet) - Plant to Warehouse| Plant | Davenport | Cincinnati | Supply (number of bracelets) ||--------------|-----------|------------|------------------------------|| Denver | $2.00 | $3.00 | 700 || Jacksonville | $3.00 | $1.00 | 400 |In this table, we see the costs associated with shipping bracelets from two plants (Denver and Jacksonville) to two warehouses (Davenport and Cincinnati). The shipping cost per bracelet from Denver to Davenport is $2.00, while to Cincinnati, it’s $3.00. Jacksonville's costs are $3.00 to Davenport and $1.00 to Cincinnati. Denver can supply 700 bracelets, and Jacksonville can supply 400 bracelets.#### Shipping Costs ($ per bracelet) - Warehouse to Retail Outlet| Warehouse | Chicago | Orlando | Houston | Little Rock ||-------------|---------|---------|---------|-------------|| Davenport | $3.00 | $7.00 | $4.00 | $7.00 || Cincinnati | $5.00 | $5.00 | $7.00 | $6.00 |This table displays the costs of shipping bracelets from two warehouses (Davenport and Cincinnati) to four retail outlets: Chicago, Orlando, Houston, and Little Rock. From Davenport, the shipping costs are $3.00 to Chicago, $7.00 to Orlando, $4.00 to Houston, and $7.00 to Little Rock. From Cincinnati, the costs are $5.00 to both Chicago and Orlando, $7.00 to Houston, and $6.00 to Little Rock.#### Demand (number of bracelets)- Chicago: 200- Orlando: 150- Houston: 350- Little Rock: 300This section provides the demand in number of bracelets needed at each retail outlet. Chicago requires 200 bracelets, Orlando needs 150, Houston demands 350, and Little Rock requires 300 bracelets.This analysis helps in understanding the distribution costs and logistics, allowing for strategic planning in supply chain management. **Fitness Bracelet Distribution**Orion Fitness produces bracelets with an embedded chip that tracks its wearer’s activities. Orion has plants in Denver and Jacksonville. Bracelets produced at either plant may be shipped to either of the firm’s two regional warehouses, which are located in Davenport and Cincinnati. These regional warehouses subsequently supply retail outlets in Chicago, Orlando, Houston, and Little Rock. The file Orion contains data on each plant’s supply (number of bracelets), each retail outlet’s demand (number of bracelets), and the shipping costs (per bracelet) for the shipping channels.

Linear programming is a mathematical technique that is also used in operations management…

PLEASE SHOW HOW TO DO THIS WITH EXCEL. Construct and solve a linear optimization model that defies the supply chain strategy that meets the demand of each retail outlet at minimum total shipping cost.

PLEASE SHOW HOW TO DO THIS WITH EXCEL. Construct and solve a linear optimization model that defies the supply chain strategy that meets the demand of each retail outlet at minimum total shipping cost.

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...

See similar textbooks

Similar questions

Hudson Corporation is considering three options for managing its data processing operation: continuing with its own staff, hiring an outside vendor to do the managing (referred to as outsourcing), or using a combination of its own staff and an outside vendor. The cost of the operation depends on future demand. The annual cost of each option (in thousands of dollars) depends on demand as follows: Demand Staffing Options High Medium Low Own staff 600 550 350 Outside vendor 900 650 450 Combination 700 600 400 a. If the demand probabilities are 0.4, 0.25, and 0.35, which decision alternative will minimize the expected cost of the data processing operation? Own Staff What is the expected annual cost associated with that recommendation? If required, round your answer to the nearest thousand of dollars. Expected annual cost = $ ___________
It is summertime and your winter coat factory is trying to decide its production schedule and staffing level for the next season. You are forecasting a demand of 5000 coats in October, 8000 coats in November, 8000 coats in December, 12000 coats in January, 12000 coats in February, and 9000 coats in March. You want to manufacture these coats at the lowest cost possible/ The first decision you need to make is how many people to hire. Each employee will cost $2500 a month. You don’t want to hire or fire any employees mid-season in order to minimize disruptions in your factory floor. You can have employees idle if there isn’t sufficient work for them to do. Employees still need to be paid even if they are idle. An employee works 20 business days a month and takes half a day to produce one coat. The employees can also be asked to work overtime, in which case you need to pay them $70 per coat they produce. You don’t need to assume there is any capacity constraint on the number of overtime…
Describe what is meant by the shadow price in linear programming?
The 23,000-seat Air East Arena houses the local professional ice hockey, basketball, indoor soccer, and arena football teams as well as various trade shows, wrestling and boxing matches, tractor pulls, and circuses. Arena vending annually sells large quantities of soft drinks and beer in plastic cups with the name of the arena and the various team logos on them. The local container cup manufacturer that supplies the cups in boxes of 100 has offered arena management the following discount price schedule for cups: Order Quantity (boxes) Price per Box 2000-6999 $47 7000-11,999 43 41 12,000-19,999 20,000+ 38 The annual demand for cups is 2.3 million, the annual carrying cost per box of cups is $1.90, and ordering cost is $320. Determine the optimal order quantity and total annual inventory cost. a. Find the economic order quantity Q* for each group b. Find the total annual cost TC for each group for the values of Q* found in (a). c. From (a) and (b) state the economic order quantity that…
Typed plzzz And Asap Thanks
Please solve using Excel @Risk add-in the question is Textbook Problem from Practical Management Science 6th Edition Chapter 11.4 Problem 30P. In the solution please provided step-by-step excel formulas used. The full Question is attached in the image. Seas Beginning sells clothing by mail order. An important question is when to strike a customer from the company’s 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. Thefollowing 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…
Read the following passage and answer the question that follows Quincy Snodgrass Enterprises—Forecasting Quincy Snodgrass is an entrepreneur and a lover of the outdoors. He has worked for various companies since he graduated college with his business administration degree in management. Over the years, he has saved every extra penny and now has the starting capital he needs; consequently, he plans to open his own business. Quincy plans to open a landscaping business. The primary services he’ll offer are grass cutting, edging, and bush trimming. Obviously, this will only provide income in the spring, summer, and early fall. Therefore, he plans to offer snow removal in the winter. His goal is to continue to provide those baseline services and expand into actual landscaping work. Quincy’s initial challenge is to develop a forecast of how many customers he’ll have each month. This is essential to determine if he needs to hire any additional labor throughout the season. Unfortunately, none…
A retail has 8 stores supplied from 4 suppliers, where each supplier supplies different goods. Delivery from the Supplier is in trucks with a capacity of 40,000 units at a cost of $1100 per load plus $100 per delivery to the store. Inventory costs are $0.2 per unit per year. The Supply Chain Manager is considering whether to use direct shipping or Milk Run delivery for 4 stores for each truck in one shipment a) if annual sales in each store are 1000,000 units, which shipping method provides the lower cost b) if the annual sales in each store are 200,000 units, which shipping method provides the lower cost
A chef must decide how many chocolate lava cakes to prepare for the upcoming Mother's Day Dinner special. The chef can either prepare 50, 100, or 150 lava cakes. Assume that demand for the lava cakes can be 50, 100, or 150. Each dish costs $5 to make and is priced at for $7 on the menu. Unsold cakes are donated to a nearby charity center. Assume that there is no opportunity cost for lost sales. Which alternative should be chosen based on the maximax criterion?
Solve asap
Dexter Company has been approached by a new customer with an offer to purchase 1,400 units of Dexter's product at a price of P3 each. The new customer is graphically separated from Dexter's other customers, and there would be no effect on existing sales. Dexter normally produces 10,000 units but only plans to produce and sell 8,000 in the coming year. The normal sales price is P5 per unit. Unit cost information is as follows: Direct materials PO.75 Direct labor 0.80 Variable overhead 0.40 Fixed Overhead 2.00 Total 3.95 If Dexter accepts the order, no fixed manufacturing activities will be affected because there is sufficient excess capacity. However, the distribution center at the warehouse is operating at full capacity and would need to add capacity costing P1,000 for every 5,000 units to be packed and shipped. BY HOW MUCH WILL PROFIT INCREASE/ DECREASE IF THE ORDER IS ACCEPTED?
Taylor Smith owns a small clothing company, Cuteness for You, that offers an online subscription and personal shopping service targeted at busy families with children aged newborn to five years old. Currently, Taylor has one level of subscription service, the standard service. For $100 a month, the standard service provides its customers a monthly delivery of 10 clothing items carefully chosen to match the child's size, gender, and emerging style. The online clothing subscription market is fairly new but is growing rapidly and thus Taylor is considering extending the product line to increase its market share and profits. Taylor is debating whether to add a premium subscription service featuring profitable high-markup items for $125 per month, a basic subscription service that contains lower-markup popular items priced at $75 per month, or possibly both. Taylor knows that the new product lines provide an opportunity to attract more customers and possibly increase revenues and profit,…