For the shirt manufacturing problem, write down the formula and constraints of the excel file

	A	B	C
19	Calculations:
20	Ratio of net income to total revenue
21	Cotton used: T-Shirts
22	Cotton used: Button Downs
23	Cotton used: total
24	Buttons used: T-Shirts
25	Buttons used: Button Downs
26	Buttons used: total
27	Ratio of Button Downs to T-Shirts
28
29	Income Statement:
30	T-Shirt Revenue
31	Button Down Revenue
32	Total Revenue
33	Variable costs: T-Shirts
34	Variable costs: Button Downs
35	Total Costs
36	Pre-Tax Income
37	Income Tax Expense
38	Net Income

Constraints:

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: BIRD DERETTER SHIRT MANUFACTURING PROBLEM -2. Changing Cells 39 Number of T-Shirts A Number of Button Down Shirts Fig. D-23 Constants The Constants should look like this: MSVARSBERDINE 6 Constante Tax Rate 0.28 a Selling prica: T-Shirt 8 9 Seling price: Button Down 36 A Variable cost to make: T Shirt 25 31. Variable cost to make: Button Down 141 12 Cotton usage (bs): T-Shirt 1.5 13 Cotton usage (bs): Button Down 25! 14 Total cotton available (lbs) 13000000 15 Buttons per T-Shirt 3 216 Buttons per Button Down 121 17 Total buttons available 11000000 Fig. D-24 . 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 polo T-shirt has 3 buttons. Each button-down shirt has a button 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 SER F 19 Calculations: 20. Ratio of net income to total revenue 21 Cotton used. T-Shirts 2. Cotton used Button Downs 23. Cotton used total 24 Buttons used: T-Shirts 25 Buitons 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 ! PRAWY DUBE F 29 Income Statement: 31 T-Shirt Revenue 31 Button-Down Revenue 32 Total Revenue 33 Variable costs: T-Shirts 134 Variable costs: Button Downs El Total Costs 35 Pre-Tax Revenue 37 Income Tax Expense 38 Net Income 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 Ts 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. foc SHIRT MANUFACTURING PROBLEM 1 2 Changing Cells: 3 Number of T-Shirts 4 Number of Button Down Shirts 6 Constants: 7 Tax Rate 8 Selling price: T-Shirt 9 Selling price: Button Down 10 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 14 Total cotton available (lbs) 15 Buttons per T-Shirt 16 Buttons per Button Down 17 Total buttons available 19 Calculations: 20 Ratio of net income to total revenue 21 Cotton used: T-Shirts 22 Cotton used: Button Downs 23 Cotton used: total 24 Buttons used: T-Shirts 25 Buttons used: Button Downs 26 Buttons used: total 27 Ratio of Button Downs to T-Shirts 0.28 8 36 2.5 14 1.5 2.5 13000000 3 12 110000000 29 Income Statement: 30 T-Shirt Revenue 31 Button Down Revenue 32 Total Revenue 33 Variable costs: T-Shirts 34 Variable costs: Button Downs 35 Total Costs 36 Pre-Tax Income 37 Income Tax Expense 38 Net Income 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: BIRD DERETTER SHIRT MANUFACTURING PROBLEM -2. Changing Cells 39 Number of T-Shirts A Number of Button Down Shirts Fig. D-23 Constants The Constants should look like this: MSVARSBERDINE 6 Constante Tax Rate 0.28 a Selling prica: T-Shirt 8 9 Seling price: Button Down 36 A Variable cost to make: T Shirt 25 31. Variable cost to make: Button Down 141 12 Cotton usage (bs): T-Shirt 1.5 13 Cotton usage (bs): Button Down 25! 14 Total cotton available (lbs) 13000000 15 Buttons per T-Shirt 3 216 Buttons per Button Down 121 17 Total buttons available 11000000 Fig. D-24 . 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 polo T-shirt has 3 buttons. Each button-down shirt has a button 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 SER F 19 Calculations: 20. Ratio of net income to total revenue 21 Cotton used. T-Shirts 2. Cotton used Button Downs 23. Cotton used total 24 Buttons used: T-Shirts 25 Buitons 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 ! PRAWY DUBE F 29 Income Statement: 31 T-Shirt Revenue 31 Button-Down Revenue 32 Total Revenue 33 Variable costs: T-Shirts 134 Variable costs: Button Downs El Total Costs 35 Pre-Tax Revenue 37 Income Tax Expense 38 Net Income 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 Ts 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. foc SHIRT MANUFACTURING PROBLEM 1 2 Changing Cells: 3 Number of T-Shirts 4 Number of Button Down Shirts 6 Constants: 7 Tax Rate 8 Selling price: T-Shirt 9 Selling price: Button Down 10 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 14 Total cotton available (lbs) 15 Buttons per T-Shirt 16 Buttons per Button Down 17 Total buttons available 19 Calculations: 20 Ratio of net income to total revenue 21 Cotton used: T-Shirts 22 Cotton used: Button Downs 23 Cotton used: total 24 Buttons used: T-Shirts 25 Buttons used: Button Downs 26 Buttons used: total 27 Ratio of Button Downs to T-Shirts 0.28 8 36 2.5 14 1.5 2.5 13000000 3 12 110000000 29 Income Statement: 30 T-Shirt Revenue 31 Button Down Revenue 32 Total Revenue 33 Variable costs: T-Shirts 34 Variable costs: Button Downs 35 Total Costs 36 Pre-Tax Income 37 Income Tax Expense 38 Net Income