only for itsudentguide do not send me a handshakeeeeee
zykidbbay
itec355_homework_1_instructions_updated.pdf
ITEC355 Spring 2014 Homework 1
1
Capacity Planning Problem Statement A company has 2 Factories and 4 Warehouses that are used to meet the demands of 3 products purchased by 5 Customers. The product flows can be either 1) Factory Warehouse Customer or 2) Factory Customer and there are different costs associated with each. Using Solver, come up with optimum solution that: minimizes the costs of shipping goods from factories to warehouses and customers, and warehouses to customers, while not exceeding the supply available from each factory or the capacity of each warehouse, and meeting the demand from each customer.
The following table lists the Cost of shipping ($ per product) for each Factory Warehouse, Factory Customer and Warehouse Customer combination:
Destinations
Warehouse 1 Warehouse 2 Warehouse 3 Warehouse 4
Factory 1 Product 1 $0.50 $0.50 $1.00 $0.20
Product 2 $1.00 $0.75 $1.25 $1.25
Product 3 $0.75 $1.25 $1.00 $0.80
Factory 2 Product 1 $1.50 $0.30 $0.50 $0.20
Product 2 $1.25 $0.80 $1.00 $0.75
Product 3 $1.40 $0.90 $0.95 $1.10
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5
Factory 1 Product 1 $2.75 $3.50 $2.50 $3.00 $2.50
Product 2 $2.50 $3.00 $2.00 $2.75 $2.60
Product 3 $2.90 $3.00 $2.25 $2.80 $2.35
Factory 2 Product 1 $3.00 $3.50 $3.50 $2.50 $2.00
Product 2 $2.25 $2.95 $2.20 $2.50 $2.10
Product 3 $2.45 $2.75 $2.35 $2.85 $2.45
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5
Warehouse 1 Product 1 $1.50 $0.80 $0.50 $1.50 $3.00
Product 2 $1.00 $0.90 $1.20 $1.30 $2.10
Product 3 $1.25 $0.70 $1.10 $0.80 $1.60
Warehouse 2 Product 1 $1.00 $0.50 $0.50 $1.00 $0.50
Product 2 $1.25 $1.00 $1.00 $0.90 $1.50
Product 3 $1.10 $1.10 $0.90 $1.40 $1.75
Warehouse 3 Product 1 $1.00 $1.50 $2.00 $2.00 $0.50
Product 2 $0.90 $1.35 $1.45 $1.80 $1.00
Product 3 $1.25 $1.20 $1.75 $1.70 $0.85
Warehouse 4 Product 1 $2.50 $1.50 $0.60 $1.50 $0.50
Product 2 $1.75 $1.30 $0.70 $1.25 $1.10
Product 3 $1.50 $1.10 $1.50 $1.10 $0.90
ITEC355 Spring 2014 Homework 1
2
The following table lists the Capacity of products that can be shipped from each warehouse:
Warehouse 1 Warehouse 2 Warehouse 3 Warehouse 4
Product 1 35,000 20,000 30,000 15,000
Product 2 30,000 25,000 15,000 24,000
Product 3 20,000 20,000 25,000 20,000
The following table lists the Demand by product by customer:
Customer 1 Customer 2 Customer 3 Customer 4 Customer 5
Product 1 30,000 23,000 15,000 32,000 16,000
Product 2 20,000 15,000 22,000 12,000 18,000
Product 3 25,000 22,000 16,000 20,000 25,000
The following table lists the Capacity by product for each Factory:
Factory 1 Factory 2
Product 1 90,000 75,000
Product 2 100,000 65,000
Product 3 80,000 90,000
Detailed Instructions
This is a Team assignment so the Lead should submit only one file on behalf of the Team.
Label your file ITEC355-XXX_Team_X_Homework1 where XXX = Section Number and X = Team Number (1, 2, etc.)
Label the first tab as 'Solver' and include all calculations therein. Note: ensure that under Solver Options, the 'Assume Non-Negative' option is checked (i.e., you cannot have negative units in this case).
Label the second tab as 'Assessment' and include a Text Box (for ease-of-reading) that lists the following:
Decision Variables, Constraints, and Objective Function
Also include a summary of the Team's findings (i.e., a few sentences on what Solver calculated) as well as any recommendations to optimize the overall process. This could include additional questions you would ask the company about their operations.
Hints: Since this is complex, multi-step problem, you may want to do the following: 1. Start with the basics of the Assessment tab. 2. Set up the problem in Excel and print out the worksheet or draw the different sections on a
whiteboard to ensure you are setting up the correct relationships. 3. Shade the cells that will be changing and/or have calculations in them to ensure you don't make any
mistakes. 4. Have the team split up and work independently on a solution; if you come up with different
answers, you know for certain one of them is wrong.
