Goal 1: The company should use the available plastic completely
Product
Cost
Units Produced
Profit Per toy
Total Profits
Plastic
Labor
Machine
Truck Toy
2,560
2
500
1,000
12
20
20
Car Toy
4,125
8
500
3,750
60
60
30
4,750
72
80
50
Goal 2: The company should not underutilized labor and machine hours available
Product
Cost
Units Produced
Profit Per toy
Total Profits
Plastic
Labor
Machine
Truck Toy
4,440
4
500
2,000
24
40
40
Car Toy
3,000
5
500
2,500
40
40
20
4,500
64
80
60
Goal 3: The company should minimize production cost
Product
Cost
Units Produced
Profit Per toy
Total Profits
Plastic
Labor
Machine
Truck Toy
4,440
4
500
2,000
24
40
40
Car Toy
3,000
5
500
2,500
40
40
20
7,440
4,500
64
80
60
Decision
The company should focus on its first goal because it is maximizing the company’s overall profit to 4750 dollars.
P5
The economic order quantity has been found by using the excel solver and by using the EOQ formula for each appliance.
The optimal inventory level for each appliance by using the EOQ formula and excel solver is presented below.
Products
EOQ
EOQ by Formula
Microwave
57
81
Range
30
42
Washer
35
49
Dryer
35
49
Dishwasher
48
68
The Objective function is
Products
Total Cost
Total Cost by formula
Microwave
1,715
1,819
Range
5,916
6,275
Washer
6,928
7,348
Dryer
6,928
7,348
Dishwasher
3,847
4,080
Total
25,334
26,871
After the incorporation of the discount factor in the model, the profit has been maximized by using the excel solver which is presented below.
Products
Demand
SP
HC
OC
Storage space
EOQ
Total capacity
Total Cost
Cost of purchases
Total Profit
Microwave
700
300
15
70
3
57
171
1,715
150
103,285
Range
500
2,000
100
175
18
30
532
5,916
1,100
444,084
Washer
600
2,000
100
200
20
35
693
6,928
1,100
533,072
Dryer
600
2,000
100
200
22
35
762
6,928
1,100
533,072
Dishwasher
500
800
40
185
18
48
866
3,847
400
196,153
3,024
25,334
1,809,666
5,000
By analyzing the answer report which can be seen in the attached excel workbook, it has been identified that there is enough capacity to store the raw material along with the enough capacity to purchase at a time too much raw material for the production.
P7
The problem has been addressed as the simple linear problem where the goal is to minimize the total cost and all of the constraints has been addressed according to the five goal of the company. The simple linear problem has located in the excel workbook.
The deviational variables are the three-room types which are constrained with the at least expansion of 5, 10 and 15 rooms for small, medium and large rooms construction respectively.
The problem has been solved with solver and found that company should expand 28 small rooms, 10 medium rooms and 15 large rooms.
Room Type
Area per room
Cost per room
Needed to expand
Total Area
Total Cost
Small
1,400
20,000
28
39,200
560,000
Medium
1,800
30,000
10
18,000
300,000
Large
2,200
40,000
15
33,000
600,000
Total
90,200
1,460,000
Constrained
90,000
2,000,000
P7
By using the pivot table, the average processing time, cost of raw materials and maximum monthly demand for each cosmetic product has been calculated.
Excel template has been prepared which have calculated the average net profit for each cosmetic product, average time usage per each cosmetic, the total profit for a given product, and the actual usage of labor hours for a given production mix by assuming the initial production level of one unit for each product.
The solver has been set up with the objective function and constraints in the excel workbook.
The results are shown by using the answer report, it has been identified that the results are highly sensitive because if the company wants to produce more units, they have not all resources available because the 0 value in slack variable have been found......................
This is just a sample partial case solution. Please place the order on the website to order your own originally done case solution.
Helping Employees Realize Their Dreams
Boston Beer Co.: Light Beer Decision
