Quick-Screen is a clothing manufacturing company that specializes in producing commemorative shirts immediately following major sporting events such as the World Series, Super Bowl, and Final Four. The company has been contracted to produce a standard set of shirts for the winning team, either State University or Tech, following a college football bowl game on New Year's Day. The items produced include two sweatshirts, one with silk-screen printing on the front and one with print on both sides, and two T-shirts of the same configuration. The company has to complete all production within 72 hours after the game, at which time a trailer truck will pick up the shirts. The company will work around the clock. The truck has enough capacity to accommodate 1,200 standard-size boxes. A standard-size box holds 12 T-shirts, and a box of 12 sweatshirts is three times the size of a standard box. The company has budgeted $25,000 for the production run. It has 500 dozen blank sweatshirts and T-shirts each in stock, ready for production. This scenario is illustrated in Figure 4.1.
Figure 4.1. Quick-Screen Shirts
(This item is displayed on page 112 in the print version)
The resource requirements, unit costs, and profit per dozen for each type of shirt are shown in the following table:
The company wants to know how many dozen (boxes) of each type of shirt to produce in order to maximize profit.
Following is a review of the model formulation steps for this problem:
This problem contains four decision variables, representing the number of dozens (boxes) of each type of shirt to produce:
x 1 = sweatshirts, front printing
x 2 = sweatshirts, back and front printing
x 3 = T-shirts, front printing
x 4 = T-shirts, back and front printing
The Objective Function
The company's objective is to maximize profit. The total profit is the sum of the individual profits gained from each type of shirt. The objective function is expressed as
maximize Z = $90 x 1 + 125 x 2 + 45 x 3 + 65 x 4
The first constraint is for processing time. The total available processing time is the 72- hour period between the end of the game and the truck pickup:
0.10 x 1 + 0.25 x 2 + 0.08 x 3 + 0.21 x 4 72 hr
The second constraint is for the available shipping capacity, which is 1,200 standard-size boxes. A box of sweatshirts is three times the size of a standard-size box. Thus, each box of sweatshirts is equivalent in size to three boxes of T-shirts. This relative size differential is expressed in the following constraint:
3 x 1 + 3 x 2 + x 3 + x 4 1,200 boxes
The third constraint is for the cost budget. The total budget available for production is $25,000:
$36 x 1 + 48 x 2 + 25 x 3 + 35 x 4 $25,000
The last two constraints reflect the available blank sweatshirts and T-shirts the company has in storage:
x 1 + x 2 500 dozen sweatshirts
x 3 + x 4 500 dozen T-shirts
The linear programming model for Quick-Screen is summarized as follows :
This model can be input as shown for computer solution .
Computer Solution with Excel
The Excel spreadsheet solution for this product mix example is shown in Exhibit 4.1. The decision variables are located in cells B14:B17 . The profit is computed in cell B18, and the formula for profit, = B14*D5+B15*E5+B16*F5+B17*G5 , is shown on the formula bar at the top of the spreadsheet. The constraint formulas are embedded in cells H7 through H11, under the column titled "Usage." For example, the constraint formula for processing time in cell H7 is = D7*B14+E7*B15+F7*B16+G7*B17 . Cells H8 through H11 have similar formulas.
Cells K7 through K11 contain the formulas for the leftover resources, or slack . For example, cell K7 contains the formula = J7H7 . These formulas for leftover resources enable us to demonstrate a spreadsheet operation that can save you time in developing the spreadsheet model. First, enter the formula for leftover resources, = J7H7 , in cell K7, as we have already shown. Next, using the right mouse button, click on "Copy." Then cover cells K8:K11 with the cursor (by holding the left mouse button down). Click the right mouse button again and then click on "Paste." This will automatically insert the correct formulas for leftover resources in cells K8 through K11 so that you do not have to type them all in individually. This copying operation can be used when the variables in the formula are all in the same row or column. The copying operation simply increases the row number for each cell that the formulas are copied into (i.e., J8 and H8, J9 and H9, J10 and H10, and J11 and H11).
The Solver window for this model is shown in Exhibit 4.2. Notice that we were able to insert all five constraint formulas with one line in the "Subject to the Constraints:" window. We used the constraint H7:H11 <= J7:J11 , which means that all the constraint usage values computed in cells H7 through H11 are less than the corresponding available resource values computed in cells J7 through J11.
(This item is displayed on page 114 in the print version)
Computer Solution With QM for Windows
The QM for Windows solution for this problem is shown in Exhibits 4.3 and 4.4.
The model solution is
x 1 = 175.56 boxes of front-only sweatshirts
x 2 = 57.78 boxes of front and back sweatshirts
x 3 = 500 boxes of front-only T-shirts
Z = $45,522.22 profit
The manager of Quick-Screen might have to round off the solution to send whole boxesfor example, 175 boxes of front-only sweatshirts, 57 of front and back sweatshirts, and 500 of front-only T-shirts. This would result in a profit of $45,375.00, which is only $147.22 less than the optimal profit value of $45,522.22.
After formulating and solving this model, Quick-Screen might decide that it needs to produce and ship at least some of each type of shirt. Management could evaluate this possibility by adding four constraints that establish minimum levels of production for each type of shirt, including front and back T-shirts, x 4 , none of which are produced in the current solution. The manager might also like to experiment with the constraints to see the effect on the solution of adding resources. For example, the dual value for processing time shows profit would increase by $233.33 per hour (up to 98.33 hours, the upper limit of the sensitivity range for this constraint quality value). Although the 72-hour limit seems pretty strict, it might be possible to reduce individual processing times and achieve the same result.