***Excel Solver - Please help with the constraints that need to be entered into there for figure D-24 at the bottom.

Problem

This section includes a short problem that lets you practice what you have learned about Excel Solver.

Setting Up the Spreadsheet

Assume that you run a shirt-manufacturing company. You have two products: polo-style T-shirts and dress shirts with button-down collars. You must decide how many T-shirts and button-down shirts to make. Assume that youll sell every shirt you make.

AT THE KEYBOAAT THE KEYBOARD

Start a new file called Shirts.xlsx and set up a Solver spreadsheet to handle the problem. You can also use the skeleton file to save time. Select Tutorial D in your data files, and then select Shirts.xlsx.

Changing Cells Section

Your changing cells should look like those in Figure D-20.

FIGURE D-20 Shirt manufacturing changing cells

Constants Section

Your spreadsheet should contain the constants shown in Figure D-21. A discussion of the constant cells and some of your companys operations follows the figure.

FIGURE D-21 Shirt manufacturing constants

TAX RATEThe rate is .28 on income before taxes. Note that no taxes are paid on losses.

SELLING PRICEYou sell polo-style T-shirts for $8 and button-down shirts for $36.

VARIABLE COST TO MAKEIt costs $2.50 to make a T-shirt and $14 to make a button-down shirt. These variable costs are for machine operator labor, cloth, buttons, and so on.

COTTON USAGEEach polo T-shirt uses 1.5 pounds of cotton fabric. Each button-down shirt uses 2.5 pounds of cotton fabric.

TOTAL COTTON AVAILABLEYou have 13 million pounds of cotton on hand to make all of the T-shirts and button-down shirts.

BUTTONSEach polo T-shirt has three buttons. By contrast, each button-down shirt has one button on each collar tip, eight buttons down the front, and one button on each cuff, for a total of 12 buttons.

TOTAL BUTTONS AVAILABLEYou have 110 million buttons on hand to be used in making the shirts.

Calculations Section

Calculations and related business constraints are discussed next. Your spreadsheet should contain the calculations shown in Figure D-22.

FIGURE D-22 Shirt manufacturing calculations

RATIO OF NET INCOME TO TOTAL REVENUEThe minimum return on sales is 0.20. This ratio is net income after taxes divided by total revenue.

COTTON USED/BUTTONS USEDYou have a limited amount of cotton and buttons. The usage of each resource must be calculated and then used in constraints.

RATIO OF BUTTON-DOWNS TO T-SHIRTSYou think you must make at least 2 million T-shirts and at least 2 million button-down shirts. You want to be known as a balanced shirt maker, so you think the ratio of button-downs to T-shirts should be no greater than 4:1. Thus, if you produced 9 million button-down shirts and 2 million T-shirts, the ratio would be too high.

Income Statement Section

Your spreadsheet should have the Income Statement section shown in Figure D-23.

FIGURE D-23 Line items in the shirt manufacturing income statement

The meaning of the line items should be clear from the sports example shown earlier in this tutorial. Solvers objective is to maximize net income after taxes.

You could use the table shown in Figure D-24 to hand-write the constraints before entering them into Solver.

FIGURE D-24 Logic of shirt manufacturing constraints

Before running Solver, set the method to GRG Nonlinear in the Solver Parameters window.

\begin{tabular}{|ll|r|} \hline & \multicolumn{1}{|c|}{ A } & B \\ \hline 1 & SHIRT MANUFACTURING EXAMPLE & \\ \hline 2 & CHANGING CELLS & \\ \hline 3 & NUMBER OF T-SHIRTS & 1 \\ 4 & NUMBER OF BUTTON-DOWN SHIRTS & 1 \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline & \multicolumn{1}{|c|}{ A } & B \\ \hline 29 & INCOME STATEMENT & \\ \cline { 2 - 3 } & T-SHIRT REVENUE & \\ \hline 31 & BUTTON-DOWN SHIRT REVENUE \\ \hline 32 & TOTAL REVENUE & \\ \hline 33 & VARIABLE COSTS: T-SHIRTS \\ \hline 34 & VARIABLE COSTS: BUTTON-DOWNS \\ \hline 35 & TOTAL COSTS \\ \hline 36 & INCOME BEFORE TAXES & \\ \hline 37 & INCOME TAX EXPENSE & \\ \hline 38 & NET INCOME AFTER TAXES \\ \hline \end{tabular} Expression Net income to revenue Ratio of button-downs to T-shirts Minimum T-shirts Minimum button-downs Usage of buttons Usage of cotton \begin{tabular}{|r|l|} \hline 19 & \multicolumn{1}{|c|}{ A } \\ \hline 19 & CALCULATIONS \\ \hline 21 & RATIO OF NET INCOME TO TOTAL REVENUE \\ \hline 21 & COTTON USED: T-SHIRTS \\ \hline 22 & COTTON USED: BUTTON-DOWN SHIRTS \\ \hline 23 & COTTON USED: TOTAL \\ \hline 24 & BUTTONS USED: T-SHIRTS \\ \hline 25 & BUTTONS USED: BUTTON-DOWN SHIRTS \\ 26 & BUTTONS USED: TOTAL \\ 27 & RATIO OF BUTTON-DOWNS TO T-SHIRTS \\ \hline \end{tabular} \begin{tabular}{|c|l|c|} \hline & \multicolumn{1}{|c|}{ A } & B \\ 6 & CONSTANTS & \\ \hline 7 & TAX RATE & 0.28 \\ 8 & SELLING PRICE: T-SHIRT & $8.00 \\ 9 & SELLING PRICE: BUTTON-DOWN SHIRT & $36.00 \\ 10 & VARIABLE COST TO MAKE: T-SHIRT & $2.50 \\ 11 & VARIABLE COST TO MAKE: BUTTON-DOWN SHIRT & $14.00 \\ 12 & COTTON USAGE (LBS): T-SHIRT & 1.50 \\ 13 & COTTON USAGE (LBS): BUTTON-DOWN SHIRT & 2.50 \\ 14 & TOTAL COTTON AVAILABLE (LBS) & 13,000,000 \\ 15 & BUTTONS PER T-SHIRT & 3.00 \\ 16 & BUTTONS PER BUTTON-DOWN SHIRT & 12.00 \\ 17 & TOTAL BUTTONS AVAILABLE & 110,000,000 \\ \hline \end{tabular}