PLEASE SHOW IN EXCEL Kelson Sporting Equipment, Inc., makes two different types of baseball gloves: a regular model and a catcher’s model. The firm has 900 hours of production time available in its cutting and sewing department, 300 hours available in its finishing department, and 100 hours available in its packaging and shipping department. The production time requirements and the profit contribution per glove are given in the following table: Production Time (hours) Model Cuttuing and Sewing Finishing and Shipping Profit/Glove Regular model 1 1/2 1/8 $5 Catcher's model 3/2 1/3 1/4 $8 Assume that the company is interested in maximizing the total profit contribution. Let R = number of units of regular model. C = number of units of catcher’s model. Max 5 R + 8 C s.t. 1 R + C ≤ 900 Cutting and sewing R + C ≤ 300 Finishing R + C ≤ 100 Packaging and Shipping R, C ≥ 0 1. Spreadsheet Model with Solver setup 2. User Interface (be sure to set Object Properties as appropriate) 3. VBA Code to link Model and Interface 4. VBA Code for Formatting 5. VBA Code for Error Trapping
PLEASE SHOW IN EXCEL
Kelson Sporting Equipment, Inc., makes two different types of baseball gloves: a regular model and a catcher’s model. The firm has 900 hours of production time available in its cutting and sewing department, 300 hours available in its finishing department, and 100 hours available in its packaging and shipping department. The production time requirements and the profit contribution per glove are given in the following table:
|
Production Time (hours) |
||||
|
Model |
Cuttuing and Sewing |
Finishing |
and Shipping |
Profit/Glove |
|
Regular model |
1 |
1/2 |
1/8 |
$5 |
|
Catcher's model |
3/2 |
1/3 |
1/4 |
$8 |
Assume that the company is interested in maximizing the total profit contribution.
Let R = number of units of regular model.
C = number of units of catcher’s model.
|
Max |
5 R |
+ |
8 C |
|||
|
s.t. |
||||||
|
1 R |
+ |
C |
≤ |
900 |
Cutting and sewing |
|
|
R |
+ |
C |
≤ |
300 |
Finishing |
|
|
R |
+ |
C |
≤ |
100 |
Packaging and Shipping |
|
|
R, C |
≥ |
0 |
1. Spreadsheet Model with Solver setup
2. User Interface (be sure to set Object Properties as appropriate)
3. VBA Code to link Model and Interface
4. VBA Code for Formatting
5. VBA Code for Error Trapping
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 4 images
