I. Problem Statement
Acme Products is planning its production and shipping for its widgit product for the next month. Acme has 4 production plants and 5 warehouses. Its goal is to satisfy the demand at its 5 warehouses (located at regional points and which serve the retailers located in the associated region) at minimum cost. The total cost includes the production cost at each plant (which differ due to local conditions for energy, labor, taxes, etc.) and the cost to ship from each plant to each warehouse. Your challenge is to find the optimal mix of production and shipping quantities which ultimately minimizes total costs given a few different scenarios.
II. Familiarization of the Model, Understanding Optimization and the Use of Solver
Like any word problem and/or a pre-defined spreadsheet, you must first "familiarize yourself" with it so as to understand the values, functions, formulas and cell relationships that exist.
For this homework assignment, the model is calculating the production and shipping costs from a set of values which represent the number of units shipped from each plant to each warehouse.
The cells which contain the values of those units shipped represent the independent decisions in this scenario (meaning that the company can arbitrarily specify how many units to ship from a plan to a warehouse - that is, that value is not computed from some other value).
Other cells represent the total units shipped to each warehouse, and the total units shipped from each plant.
The units shipped from each plant to each warehouse are multiplied by the cost to ship on that route to yield the cost for that shipment.
The total of all such shipment costs represents the total shipping cost.
The total shipments from each plant is multiplied by the per-unit production cost at that plant to arrive at the total production cost for that plant.
The sum of these costs over all plants represents the total production cost of the plan.
Make sure that you understand how the excel model does all of these calculations. If you cannot explain exactly what each cell in the model represents, you do not understand it sufficiently to complete the assignment successfully. This has nothing to do with solver - it has to do with understanding the model upon which the solver problem is to be built.
III. Understanding Solver Terminology (Excel 2007 and Excel 2010 are "very similar")
|
#
|
Excel 2007 Solver Terms
|
Excel 2010 Solver Terms
|
Description
|
|
1
|
Set Target Cell
|
Set Objective
|
Your Objective - the cell value to optimize
|
|
2
|
Equal to
|
To
|
Optimize How? Maximize/Minimize/Attain a Target Value
|
|
3
|
By Changing Cells
|
By Changing Variable Cells
|
The Decision cells which represents the independent cell values for which you want Excel Solver to "figure out"
|
|
4
|
Subject to the Constraints
|
Subject to the Constraints
|
The conditions or rules that must be followed
|
III. Your Challenge: Minimize Total Costs
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.
The initial constraints or conditions placed on the model include:
Requirement: total shipments to each warehouse must be GE (>=) the Demand at that warehouse
Limitation: total shipments from each plant must be LE (<=) the Capacity of that plant
ALL of the shipment quantity values must be GE (>=) 0
IV: Time to Get Busy:
Step 0. Setup: Obtain the Excel file from the TRACS Homework Assignment Tab and save it as: CIS3380_HW3_Fall2015_LnameFname.xls or xlsx and OPEN it
Verify that Excel Solver is installed (Data Tab Far Right) and if Solver does NOT Appear:
1.Click the Microsoft Office Button and then click Excel Options
2.Click Add-Ins, then in the Manage box, select Excel Add-ins
3.Click Go
4. In the Add-Ins available box, select the Solver Add-In Check box
*If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in.
*If you get prompted that the Solver Add-in is not currently installed on your computer, click Yes to install it.
5. After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data tab.
Step 1. 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 willneed for Solver
Step 2: Next, we actually invoke Solver, and enter ALL the parameters& Constraints then Solve the Initial Model(Base Case)
Step 3: Record your answer (Initially Solved Total Delivery Costs) in the cell provided (Step 3)
Then restore original values !!!
Step 4: Case #1:You will notice that the Chicago plant has the largest production cost of allthe 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 !!!
Step 5: 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 in the table but ONLY as long as you guarantee ALL of your Phoenix shipments to this new shipper.
*Note: some are increases and some are decreases!
|
From
|
To
|
Current $
|
Proposed $
|
Change
|
|
Phoenix
|
New York
|
$12.00
|
$10.00
|
$2.00 Savings
|
|
Phoenix
|
Salt Lake City
|
$5.00
|
$5.50
|
$0.50 Increase
|
|
Phoenix
|
Chicago
|
$7.00
|
$8.00
|
$1.00 Increase
|
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." Keep final results to print out!
Step 6: Make Your Recommendation
Review the results of Case 1 .vs. Case 2 and determine if you want to take the offer from the new potential Phoenixshipper. Answer the questions in the Step 6 portion of the excelmodel and be specific on your answers/reasons!
Step 7: Finalize
a. Add your Name to the Excel Sheet header (Left Hand side) where there is already a place holder for it
b.Upload your Excel solution file with your answers to the TRACS Drop Box for a backup of Homework #3
c.Deliverable: print out the Excel Sheet with final results in cells and turn in the hard copy.
REFERENCES: There are many great video clips on YouTube that focus on Excel Solver and the differences between Excel 2007 and Excel 2010 have no impact on the Solver Tool!
https://www.youtube.com/watch?v=hbEn_CeYr6U
https://www.youtube.com/watch?v=W7DdbAZDmAM