Explore BrainMass
Share

Excel Solver: Toy Manufacturing Company

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

A toy manufacturing company currently maintains plants in Atlanta and Tulsa that supply to retail centers in Los Angeles and New York. Because of expanding demand, the company has decided to build a third plant and has narrowed the choice to one of two cities, Seattle or Baltimore. The distribution costs, as well as plant capacity and retailer demand, are shown in the table:

Retail Stores Manufacturing Plant Demand
Atlanta Tulsa Seattle Baltimore
Los Angeles $ 8.00 $ 4.00 $ 5.00 $ 4.00 800
New York $ 5.00 $ 7.00 $ 6.00 $ 6.00 1200
Capacity 600 900 500 500

Solve the given problem scenario for the toy manufacturer by recommending the most suited new location to establish the plant. Formulate and set up the problem using the most appropriate technique in Excel Solver.

The Excel set-up should provide clearly labeled values used for the decision variables, constraints, and objective function.

© BrainMass Inc. brainmass.com October 25, 2018, 10:14 am ad1c9bdddf
https://brainmass.com/business/operations-research/excel-solver-toy-manufacturing-company-603524

Solution Preview

Please find attached response to your posting. There are two tabs 1) Both ...

Solution Summary

This solution shows step-by-step calculations to determine which new potential location should be selected for new plant development. Factors that are considered are retailer demand and minimization of distribution cost. Toy manufacturing company wants to decide which of the new potential locations should be selected for a new plant to satisfy retailer demand and minimize distribution cost. The problem is formulated and solved using integer programming with binary variables. The solve process is demonstrated using Excel solver.

$2.19
See Also This Related BrainMass Solution

Linear Programming - Tots Toys: Analytical Methods of Supply Chain Management

See attached file for proper format.

Linear Programming - Analytical Methods of Supply Chain Management

Problem 2

The Tots Toys Company is trying to schedule production of two very popular toys for the
next three months: a rocking horse and a scooter. Information about both toys is given
below.

Beg. Inv.
June 1
30
10
Scooter
Plastic
Available
3500
5000
4800
Time
Available
2100
4000
2500
Monthly Demand
Horse
220
350
600
Monthly Demand
Scooter
450
700
520
Required
Plastic/Unit
5
4
Required
Time/Unit
2
3
Production
Cost/Unit
10
12
Holding
Cost/Unit/Month
1.50
1.20
Toy
Rocking
Horse
Summer Schedule
June
July
August

Develop a linear programming model that would tell the company how many of each toy
to produce during each month. You are to minimize total cost (production cost + holding
cost). Inventory holding cost will be levied on any items in ending inventory on June 30,
July 31, or August 31 after demand for the month has been satisfied. The company wants
to end the summer with 85 rocking horses and 50 scooters as beginning inventory for
Sept. 1.

Questions: How many of each toy will be produced each month? What will the total
cost equal? Be sure to specify that all variables are integer.

Define the decision variables, the objective function, and the constraints within your
answer to this question. Then, solve the model using Excel Solver. Finally, copy your
spreadsheet into the appendix of your Word report and submit your Excel file to the
dropbox.
2

View Full Posting Details