Its goal is to satisfy the demand at its 5 warehouses

Assignment Help Business Management

Reference no: EM13874017

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

Reference no: EM13874017

Questions Cloud

What contributions did muslim scholars and thinkers : What contributions did Muslim scholars and thinkers make to the development of European civilization?

Write an argumentative essay on milirrupm vs nabalco : Write down an argumentative essay on the following points:- • Milirrupm Vs Nabalco • Incorporating Indigenous law • High court decisions and NTAA • Hidden Whiteness.

Which of these expenses would not be considered : In computing the program services ratio, which of these expenses would not be considered part of the program service expenses? Which of the following is considered an asset in computing the quick ratio?

Write a research report on customer relationship management : Represent a Research Report on Customer Relationship Management and explain the major points Present this service organisation as a blueprint.

Its goal is to satisfy the demand at its 5 warehouses : 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 re..

Analyzing new type of insulation for interior walls : Steele Insulators is analyzing a new type of insulation for interior walls. Management has compiled the following information to determine whether or not this new insulation should be manufactured.

Compute the after tax cost of preferred stock : The treasurer of Riley Coal Co. is asked to compute the cost of fixed income securities for her corporation. Even before making the calculations, she assumes the aftertax cost of debt is at least 3 percent less than that for preferred stock. Compute ..

Journal entries assuming ace uses a perpetual inventory : The sequence of events was as follows: June 3 Purchased goods for $ 4,100 from Diamond Inc. with terms 2/10, n/30. 5 Returned goods costing $ 1,100 to Diamond Inc. for full credit. 6 Purchased goods from Club Corp. for $ 1,000 with terms 2/10, n/30. ..

Describe the contents of the text box : A form contains two text boxes and one large label between them with no preset caption. When the first text box receives the focus, the label reads "Enter your full name." When the second text box receives the focus, the label reads "Enter your ph..

User Account

All Pages