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: HOME ME DAE SHIRT MANUFACTURING PROBLEM 52 Changing Calls Number of T-Shirts BAN Number of Button Down Shirts Fig. D-23 Constants The Constants should look like this: 18 0.28 8 361 2.51 14 1.51 2.5 13000000 3 12 110000000 BACHAD 6 Constants: 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 lisage (lbs): Button Down 2014 Total cotton available (lbs) 15 Buttons per T-Shirt 16. Buttons per Button Down 917 Total buttons available 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 F 19 Calculations: 23 Ratio of net income to total revenue 21 Cotton ured T-Shirts 22. Cotton used: Button Downs Cotton used total 22 Buttons Used: T-Shirts 125 Buttons used. Button Downs 26 Buttons used: total 2 Ratio of Button Downs to T-Shirts el 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 STARE F 129Income Statement: 30. T-Shirt Revenue 31 Button-Down Revenus 32 Total Revenge 33 Variable costs: T-Shirts 34 Variable costs: Button Downs E6 Total Costs 36. Pre-Tax Revenue Income Tax Expense 39 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. 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: HOME ME DAE SHIRT MANUFACTURING PROBLEM 52 Changing Calls Number of T-Shirts BAN Number of Button Down Shirts Fig. D-23 Constants The Constants should look like this: 18 0.28 8 361 2.51 14 1.51 2.5 13000000 3 12 110000000 BACHAD 6 Constants: 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 lisage (lbs): Button Down 2014 Total cotton available (lbs) 15 Buttons per T-Shirt 16. Buttons per Button Down 917 Total buttons available 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 F 19 Calculations: 23 Ratio of net income to total revenue 21 Cotton ured T-Shirts 22. Cotton used: Button Downs Cotton used total 22 Buttons Used: T-Shirts 125 Buttons used. Button Downs 26 Buttons used: total 2 Ratio of Button Downs to T-Shirts el 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 STARE F 129Income Statement: 30. T-Shirt Revenue 31 Button-Down Revenus 32 Total Revenge 33 Variable costs: T-Shirts 34 Variable costs: Button Downs E6 Total Costs 36. Pre-Tax Revenue Income Tax Expense 39 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