Setup: Obtain the Excel file from the TRACS Homework Assignment Tab and save it as: CIS3380_HW3_Fall2020_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 File button and then click 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 will need 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 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 !!!

Step 5: Case #2. A shipper that you dont 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 Phoenix shipper. Answer the questions in the Step 6 portion of the excel model and be specific on your answers/reasons!