View the step-by-step solution to:

I Did NOT put my Name Here! CIS3380 Fall, 2011 (Jetton) Acme Products Shipping Plan -- Fall '12 Step 1: Type in the solver specifications (using

Please see attached documents. (Word document for instructions & Excel file for work space)

Find optimal mix of production and shipping quantities which ultimately minimizes total costs.

I Did NOT put my Name Here! CIS3380 Fall, 2011 (Jetton) Acme Products Shipping Plan -- Fall '12 Step 1: Type in the solver specifications (using excel cell addresses) for the optimization problem into the table below FIRST! Description Field Content Description Constraint Values/Ref Target Cell? Function? n/a Changing/Decision Cells? Initial Constraints 1. 2. 3. NEW Case 1 Constraint? Step 2: Receiving Warehouses Shipping Plants San Diego Salt Lake Chicago Dallas New York Chicago 6.25 550 5 1 1 1 1 1 Phoenix 4.50 400 5 1 1 1 1 1 Dallas 3.80 500 5 1 1 1 1 1 Los Angeles 5.50 550 5 1 1 1 1 1 --- --- --- --- --- Total Shipments Received by Warehouse: 4 4 4 4 4 370 145 250 175 150 Plant Shipping costs (per unit) from plant (row) to warehouse (column): Chicago $4.00 $6.00 $1.00 $3.00 $7.00 Phoenix $3.00 $5.00 $7.00 $12.00 $12.00 Dallas $9.00 $7.00 $5.00 $1.00 $11.00 Los Angeles $2.00 $5.00 $12.00 $10.00 $15.00 --- --- --- --- --- $18.00 $23.00 $25.00 $26.00 $45.00 Total Shipping Cost $137.00 Total Production Cost $100.25 Total Cost To Deliver Goods To Warehouses $237.25 Step 3: Initially Solved Base Case: "Total Cost to Deliver Goods to Warehouses" Step 4: Case 1: Minimum cost to deliver goods without the Chicago plant ==> Step 5: Case 2 Adj Minimum cost to deliver goods without the Chicago *AND* the proposed Phoenix prices from the new shipper candidate/proposal: Case 2 Total Costs ==> Does the offer from the new Phoenix potential shipper make sense and reduces your total cost of delivering goods to your warehouses? Justify your answer with numbers from the solution. Step 6: Take the Offer? Yes or No???? WHY? State why you should or should not take the offer What is the $ impact (difference) between case #1 & case #2? Production Cost/uinit Plant Capacity (units) Total Shipments from Plant Demands by Warehouse
Background image of page 1
I. Your Challenge: Minimize Total Costs a. The initial Excel model contains the calculations for total costs as well as the demand and production capacities for each plant. Using Solver, you are charged to find the optimal mix of product quantities that are to be shipped from your 4 plants to the 5 different receiving warehouses. b. The initial constraints or conditions placed on the model include: b.i. Requirement: total shipments to each warehouse must be GE (>=) the Demand at that warehouse b.ii. Limitation: total shipments from each plant must be LE (<=) the Capacity of that plant b.iii. ALL of the shipment quantity values must be GE (>=) 0 II. Time to Get Busy: 1) Setup: Obtain the Excel file attached and open it, Verify that Excel Solver is installed. 2) Create an excel solver model that attains our objectives. Start by reviewing the cell values, functions, formulas and labels of the Excel file then complete the Step 1 "light blue" part of the Excel model file by manually entering in the Words and Cell references that you will need for Solver 3) Next, we actually invoke Solver, and enter ALL the parameters & Constraints then Solve the Initial Model (Base Case) 4) Record your answer (Initially Solved Total Delivery Costs) in the cell provided (Step 3). Then restore original values! 5) Case #1: You will notice that the Chicago plant has the largest production cost of all the plants in the company. This is because it is old and outdated. Management has decided to shut the plant down for 6 months to be overhauled and its processes re-engineered. Modify your model to force the production at the Chicago to be 0 (zero) . Rerun the model. Enter/Type the new minimum costs into the xlsx file in the area labeled "Step 4: (Case #1) PLUS enter the adjusted constraints up top (Step 1 zone: Case 1). Then restore original values! 6) Case #2. A shipper that you don’t currently use has offered you a package deal that they will ship ALL your goods out of Phoenix for the proposed prices below
Background image of page 1
in the table but ONLY as long as you guarantee ALL of your Phoenix shipments to this new shipper. 6.i. * Note: some are increases and some are decreases! From To Current $ Proposed $ Change Phoeni x New York $12.00 $10.00 $2.00 Savings Phoeni x Salt Lake City $5.00 $5.50 $0.50 Increase Phoeni x Chicago $7.00 $8.00 $1.00 Increase 6.ii. Enter these shipping costs per unit adjustments into your model (DO NOT Un-Do previous steps and keep working in a forward manner! Re-solve the model and enter your new total shipping costs into the box noted “Case 2 Total Costs” 7) Your Recommendation: a.i. Review the results of Case 1 .vs. Case 2 and determine if you want to take the offer from the new potential Phoenix shipper. Answer the questions in the Step 6 portion of the excel model and be specific on your answers/reasons!
Background image of page 2

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question