Answer Happy

Chrome File Edit View View History Bookmarks Profiles Tab Window Help ... Mail - Carrera, Jakob D X HW A3 X C answers Search X + canvas.txstate.edu/courses/1868078/assignments/25231342 * ☐ J ► YouTube Canvas Texas State Unive... Texas State Email Print Netflix GOLF ↑ JOBS Indeed CIS3380-Zhang-HW3-Solver-Spring2022-Instructions.docx Download Info X Close Page 1 > of 3 O ZOOM + ✓ 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 51 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. → с Ready How Do I Complete Ste x M GMAIL 74% Group Ungroup 3 Subtotal J 175 use (column): 3.00 $ 12.00 $ 1.00 $ 10.00 $ 26.00 $ 125 7.00 12.00 11.00 15.00 45.00 Mon 11:06 AM Share Analysis Tools Data Analysis ? Solver M N 2 OU KL E Q Comments 91%
Chrome File Edit View View History Bookmarks Profiles Tab Window Help ... Mail - Carrera, Jakob D X HW A3 X C answers Search X canvas.txstate.edu/courses/1868078/assignments/25231342 ► YouTube Canvas Texas State Unive... Texas State Email Print CIS3380-Zhang-HW3-Solver-Spring2022-Instructions.docx → с Ready + * ☐ J Netflix GOLF Info X Close Page < 1 > of 3 O ZOOM + ✓ 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 2016 are "very similar") # Excel 2007 Solver Terms Description Set Target Cell Excel 2016 Solver Terms Set Objective To Your Objective - the cell value to optimize Equal to By Changing Cells By Changing Variable Cells Subject to the Constraints Optimize How? Maximize/Minimize/Attain a Target Value The Decision cells which represents the independent cell values for which you want Excel Solver to "figure out" The conditions or rules that must be followed Subject to the Constraints 1 2 3 4 How Do I Complete Ste x ↑ JOBS Indeed Page 1 of 3 Download M GMAIL 74% Group Ungroup 3 Subtotal J 175 use (column): 3.00 $ 12.00 $ 1.00 $ 10.00 $ 26.00 $ 125 7.00 12.00 11.00 15.00 45.00 2 OU Mon 11:07 AM Share Analysis Tools Data Analysis ? Solver M N Q KL E Q Comments 91%
Chrome File Edit View View History Bookmarks Profiles Tab Window Help ... Mail - Carrera, Jakob D X HW A3 X C answers Search X canvas.txstate.edu/courses/1868078/assignments/25231342 ☐ J ► YouTube Canvas Texas State Unive... Texas State Email Print GOLF ↑ JOBS Indeed CIS3380-Zhang-HW3-Solver-Spring2022-Instructions.docx Download Info X Close Page < 2 > of 3 O ZOOM + ✓ 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: 1. Requirement: total shipments to each warehouse must be GE (>) the Demand at that warehouse 2. Limitation: total shipments from each plant must be LE (<=) the Capacity of that plant 3. ALL of the shipment quantity values must be GE (>=) 0 Time to Get Busy: Setup: Obtain the Excel file from the Canvas Homework Assignment Tab and save it as: CIS3380_HW3_Spring2022_LnameFname.xls or xlsx and OPEN it Verify that Excel Solver is installed (Data Tab Far Right) and if Solver does NOT Appear: Click the File button and then click Options 1. 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 Date tak → с Ready IV: Step 0. Netflix How Do I Complete Ste x + * M GMAIL 74% Group Ungroup 3 Subtotal J 175 use (column): 3.00 $ 12.00 $ 1.00 $ 10.00 $ 26.00 $ 125 7.00 12.00 11.00 15.00 45.00 20 U Mon 11:07 AM Share Analysis Tools Data Analysis ? Solver M N Q KL E Q Comments 91%
Chrome File Edit View View History Bookmarks Profiles Tab Window Help ... Mail - Carrera, Jakob D X HW A3 X C answers Search X + canvas.txstate.edu/courses/1868078/assignments/25231342 * ☐ J ► YouTube Canvas Texas State Unive... Texas State Email Print Netflix GOLF CIS3380-Zhang-HW3-Solver-Spring2022-Instructions.docx Info X Close Page 2 > of 3 O ZOOM + ✓ 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 I zone: Case 1). Page 2 of 3 → с Ready How Do I Complete Ste x ↑ JOBS Indeed Download M GMAIL 74% Group Ungroup 3 Subtotal J 175 use (column): 3.00 $ 12.00 $ 1.00 $ 10.00 $ 26.00 $ 125 7.00 12.00 11.00 15.00 45.00 © U Mon 11:07 AM Share Analysis Tools Data Analysis ? Solver M N Q KL E Q Comments 91%
Chrome File Edit View View History Bookmarks Profiles Tab Window Help ... Mail - Carrera, Jakob D X HW A3 X C answers Search X canvas.txstate.edu/courses/1868078/assignments/25231342 ☐ J ► YouTube Canvas Texas State Unive... Texas State Email Print Netflix GOLF ↑ JOBS Indeed CIS3380-Zhang-HW3-Solver-Spring2022-Instructions.docx Download Info X Close Page < 3 > of 3 O ZOOM + ✓ 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! Current $ Proposed $ $10.00 Change $2.00 Savings $12.00 From To Phoenix New York Phoenix Salt Lake City Phoenix Chicago $5.00 $5.50 $0.50 Increase $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! 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! Finalize → с Ready Step 6: Step 7: How Do I Complete Ste x + * M GMAIL 74% Group Ungroup 3 Subtotal J 175 use (column): 3.00 $ 12.00 $ 1.00 $ 10.00 $ 26.00 $ 125 7.00 12.00 11.00 15.00 45.00 回凹 Mon 11:07 AM Share Analysis Tools Data Analysis ? Solver M N Q KL E Q Comments www.cam.com -
Excel File Edit View Insert Format Tools Data Window Help ●●● AutoSave OFF ABA Duo *** Home Insert Draw Page Layout Formulas Data Review Data View O FA Ba ▬▬▬ J LE Stocks Data from Picture Refresh All A Description 8 9 Objective? 10 11 12 Function? 13 14 15 Changing Decision Cells? 16 17 Initial Constraints 18 19 20 21 22 NEW Case 1 Constraint? 23 24 25 26 29 Step 2: 30 31 34 36 37 39 40 41 42 43 44 45 16 47 48 49 50 51 Get Data G23 7 Ready H. 15 2 8 ProductionShipping Queries & Connections Properties Edit Links Field Com Decoration Minimize cont Shipping Plants |Chicago Phoenix Calas Los Angeles Total Shipments Received by Warehouse: Plant Chicago Phoenb Calas Los Angeles Total Shipping Cost Total Production Cost Total Cost To Deliver Goods To Warehouses + (0₂ Currencies C Constraint Values/Cell Re ma Production Costunit 6.06 4.50 3.80 5.50 |HW3 SC2 - Saved to my Mac Tell me A ZA Sort Filter Clear Reapply Advanced D E F Total Shipments from Plant 5 5 5 5 Demanda by Warehouse Plant Capacity (units) 525 500 550 137.00 99.25 236.25 San Diego Text to Columns AX Salt Lake What-If Analysis H Receiving Warehouses Chicago 1 ☆ ☎ 71% O Group Ungroup Subtotal J New York 1 1 1 Dallas 4 4 370 140 200 175 Shipping costs (per unit) from plant (row) to warehouse (column): 4.00 $ $ 3.00 $ $ 3.00 3 $ 6.00 $ 5.00 $ 7.00 S $ 12.00 $ 1.00 $ 7.00 $ 5.00 $ 12.00 S 7.00 12.00 11.00 15.00 $ 1.00 $ 9.00 $ 3 3 2.00 S 5.00 5 10.00 $ $ 18.00 3 $ 23.00 $ 25.00 $ 26.00 $ 45.00 20 U 126 Q Mon 11:11 AM Share +3 Analysis Tools Data Analysis ? Solver 3 KL M N = Q Comments 91%
Excel File Edit View Insert Format Tools Data Window Help ●●● AutoSave OFF ABA Duo *** Home Insert Draw Page Layout Formulas Data Review Data View O FA Ba LE Refresh Stocks Data from Picture All G23 35 37 40 41 42 43 44 45 46 47 48 Queries & Connections Properties Edit Links fx B Total Shipments Received by Warehouse Plant Chicago Phoenix Calas Los Angeles Total Shipping Cost 50 Total Production Cont 51 52 Total Cost To Deliver Goods To Warehouses $ 53 54 55 Step 3: Initially Solved Base Case: "Total Cost to Deliver Goods to Warehouses" $ 7,906.25 57 Step 4: Case 1: Minimum cost to deliver goods without the Chicago plant => 58 59 Step 5: Case 2 61 Adj Minimum cost to deliver goods without the Chicago "AND" the proposed Phoenix prices from the new shipper candidate/proposal: 62 63 Case 2 Total Costs 64 65 Does the offer from the new Phoenix potential shipper make sense and reduces your total cost of delivering goods to your warehouses? 66 Justify your answer with numbers from the solution. 67 68 Step 6: Take the Offer? Yes or No???? 69 70 WHY? State why you should or should not take the offer 71 What is the $ impact (difference) between case #1 & case #Z? 72 73 74 ProductionShipping + Get Data 49 Ready * 8 A |HW3 SC2 - Saved to my Mac Tell me 21 32 Clear Reapply Advanced F (0₂ Currencies C D Sort Filter E Demands by Warehouse 137.00 99.25 236.25 4 | $ Text to Columns AX H ☆ ☎ 71% O Group Ungroup Subtotal J What-If Analysis 4 A 370 140 200 175 125 3.00 $ 7.00 12.00 $ 12.00 $ Shipping costs (per unit) from plant (row) to warehouse (column): 4.00 5 $ 3.00 $ 9.00 $ 3 2003 $ |--|| 6.00 $ 5.00 $ 7.00 $ 5.00 3 5 1.00 $ 7.00 | S 5.00 $ 12.00 $ 3 1.00 $ 11.00 $ 10.00 $ 15.00 $ 18.00 $ 23.00 $ 25.00 $ 26.00 $ 45.00 © U I Q = Q Share Comments Analysis Tools Data Analysis ? Solver M N Mon 11:11 AM +3 3 K L www