ONLY WANT THE CONSTRAINTS'S ANSWER, THANK YOU

USING THE SOLVER ON A NEW PROBLEM Here is a short problem that will let you test what you have learned about the Excel Solver. Assume you run a shirt-manufacturing company. You have two products: (1) polo-style T-shirts, and (2) dress shirts with button-down collars. You must decide how many T-shirts and how many button-down shirts to make. Assume you'll sell every shirt you make. AT THE KEYBOARD Set up a Solver spreadsheet to handle this problem. Changing Cells The Changing cells should look like this: SHIRT MANUFACTURING PROBLEM 52 Changing Cells: 13 Number of T-Shirts 14 Number of Button Down Shirts PL Fig. D-23 Constants The Constants should look like this: BARCHA DESF UNAWA 6 Constants: 7 Tax Rate 8. Selling price: T-Shirt 9. Selling price: Button Down 510 Variable cost to make: T-Shirt 11 Variable cost to make: Button Down 12. Cotton usage (lbs): T-Shirt 13 Cotton usage (lbs): Button Down 214 Total cotton available (lbs) 15 Buttons per T-Shirt 16. Buttons per Button Down 172 Total buttons available Fig. D-24 0.28 81 36 2.5 14 1.51 2.51 13000000 121 110000000 The constants cells and some of your company's operations) are discussed next. The tax rate is 28 on pre-tax income, but no taxes are paid on losses. You sell a polo-style T-shirt for $8, and a button-down shirt for $36. It costs $2.50 to make a T-shirt, and it costs $14 to make a button-down shirt. These variable costs are for machine-operator labor, cloth, buttons, and so forth. Each polo T-shirt uses 1.5 pounds of cotton fabric. Each button-down shirt uses 2.5 pounds of cotton fabric. You have only 13 million pounds of cotton on hand to be used to make all the T-shirts and button-down shirts. Each T-shirt has 3 buttons. Each button-down shirt has a on each collar tip, 8 buttons down the front, and 1 on each cuff, for a total of 12 buttons. You have 110 million buttons on hand to be used to make all your shirts. Calculations Your spreadsheet needs these calculations: 2 Det E19 Calculations: 20. Ratio of net income to total revenue 21 Cotton used: T-Shirts 22. Cotton used: Button Downs 23] Cotton used total 229 Buttons used: T-Shirts 25 Buttons used: Button Downs 26 Buttons used: total 27. Ratio of Button Downs to T-Shirts Fig. D-25 Calculations and related business constraints) are discussed next. Your minimum return on sales (net income divided by total revenue) is.20. You have a limited amount of cotton and buttons. Use of each resource must be calcu- lated, then used in constraints. You 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 9 million button- down shirts and 2 million T-shirts were produced, the ratio would be too high.) Income Statement Your spreadsheet should have this income statement skeleton: Tutorial D B.CZ ESF ASSAN 29. Income Statement: 30. T-Shirt Revenue 31 Button-Down Revenue '32 Total Revenue 133 Variable costs: T-Shirts 13 Variable costs: Button Downs 5. Total Costs 36. Pre-Tax Revenue 37 Income Tax Expense 38. Net Income ............. LOGO Fig. D-26 The Solver's target is net income, which must be maximized. You can use this table to write out your constraints before entering them into the Solver. Solver Constraints Expression in English Excel Expression Net income to revenue Ratio of BDs to T's Minimum T-shirts Minimum Button Downs Usage of buttons Usage of cotton Fig. D-27 Enter your calculations and income statement formulas. Enter the constraints. Run the Solver to get an answer to the production problem. Please hand in your workings and answers to your lecturer at the end of the class. Thank you. For the shirt manufacturing problem, write down the formula and constraints of the excel file "tut D-shirt- skeleton". A B 19 Calculations: Ratio of net income to 20total revenue =C38/C32 21 Cotton used: T-Shirts =C3*C12 Cotton used: Button 22 Downs =C4*C13 23 Cotton used: total =C21+C22 24 Buttons used: T-Shirts =C3C15 Buttons used: Button 25 Downs =C4*C16 26 Buttons used: total =C24+C25 Ratio of Button Downs to 27 T-Shirts =C4/C3 28 29 Income Statement: 30 T-Shirt Revenue =C3*C8 31 Button Down Revenue =C4C9 32 Total Revenue =C30+C31 33 Variable costs: T-Shirts =C3*C10 Variable costs: Button 34 Downs =C4*C11 35 Total Costs =C33+C34 36 Pre-Tax Income =C32-C35 37 Income Tax Expense =IF(C30 = =