PROBLEM. In this problem you will extend Monk's Tutorial D dealing with Excel's Solver feature. Please read the tutorial and follow the instructions below. Download Tutorial-D.xlsx from Moodle and rename it to LastName-THX11.xlsx. Upload the completed spreadsheet to the Moodle drop box. Make sure that the Answer Report sheet produced by Solver is included. EXTENDING THE EXAMPLE Next, you'll modify the sporting goods spreadsheet because management wants to know what net income would be if certain constraints were changed. In other words, management wants to play "what if" with certain constraints in the base case. The resulting second case is called the extension case. Here are the changes to the original base case conditions: Assume maximum production constraints will be removed. Similarly, the basketball-to-football production comparison (B3 >= B4) will be removed. There still will be minimum production constraints. Assume that at least 30,000 basketballs and 30,000 footballs will be produced. A different and possibly more ambitious profit goal is desired. The ratio of net income after taxes to total revenue should be greater than or equal to .33. This new constraint will replace the constraint that calls for profits greater than zero. The machine-hours shared resource imposes the same limits as in the base case. 2. $14.00 $11.00 0.28 0.50 0.30 $10.00 $2.00 $1.25 0.50 0.60 1.10 2 CHANGING CELLS 3 NUMBER OF BASKETBALLS 4 NUMBER OF FOOTBALLS 5 6 CONSTANTS 7 BASKETBALL SELLING PRICE 8 FOOTBALL SELLING PRICE 9 TAX RATE 10 NUMBER OF HOURS TO MAKE A BASKETBALL 11 NUMBER OF HOURS TO MAKE A FOOTBALL 12 COST OF LABOR -- 1 MACHINE HOUR 13 COST OF MATERIALS -- 1 BASKETBALL 14 COST OF MATERIALS -- 1 FOOTBALL 15 16 CALCULATIONS 17 TOTAL BASKETBALL HOURS USED 18 TOTAL FOOTBALL HOURS USED 19 TOTAL MACHINE HOURS USED (BB+ FB) 20 21 INCOME STATEMENT 22 BASKETBALL REVENUE (SALES) 23 FOOTBALL REVENUE (SALES) 24 TOTAL REVENUE 25 BASKETBALL MATERIALS COST 26 FOOTBALL MATERIALS COST 27 COST OF MACHINE LABOR 28 TOTAL COST OF GOODS SOLD 29 INCOME BEFORE TAXES 30 INCOME TAX EXPENSE 31 NET INCOME AFTER TAXES 32 33 34 35 36 37 38 $14.00 $22.00 $36.00 $2.00 $2.50 $11.00 $15.50 $20.50 $5.74 $14.76 39 40 11 PROBLEM. In this problem you will extend Monk's Tutorial D dealing with Excel's Solver feature. Please read the tutorial and follow the instructions below. Download Tutorial-D.xlsx from Moodle and rename it to LastName-THX11.xlsx. Upload the completed spreadsheet to the Moodle drop box. Make sure that the Answer Report sheet produced by Solver is included. EXTENDING THE EXAMPLE Next, you'll modify the sporting goods spreadsheet because management wants to know what net income would be if certain constraints were changed. In other words, management wants to play "what if" with certain constraints in the base case. The resulting second case is called the extension case. Here are the changes to the original base case conditions: Assume maximum production constraints will be removed. Similarly, the basketball-to-football production comparison (B3 >= B4) will be removed. There still will be minimum production constraints. Assume that at least 30,000 basketballs and 30,000 footballs will be produced. A different and possibly more ambitious profit goal is desired. The ratio of net income after taxes to total revenue should be greater than or equal to .33. This new constraint will replace the constraint that calls for profits greater than zero. The machine-hours shared resource imposes the same limits as in the base case. 2. $14.00 $11.00 0.28 0.50 0.30 $10.00 $2.00 $1.25 0.50 0.60 1.10 2 CHANGING CELLS 3 NUMBER OF BASKETBALLS 4 NUMBER OF FOOTBALLS 5 6 CONSTANTS 7 BASKETBALL SELLING PRICE 8 FOOTBALL SELLING PRICE 9 TAX RATE 10 NUMBER OF HOURS TO MAKE A BASKETBALL 11 NUMBER OF HOURS TO MAKE A FOOTBALL 12 COST OF LABOR -- 1 MACHINE HOUR 13 COST OF MATERIALS -- 1 BASKETBALL 14 COST OF MATERIALS -- 1 FOOTBALL 15 16 CALCULATIONS 17 TOTAL BASKETBALL HOURS USED 18 TOTAL FOOTBALL HOURS USED 19 TOTAL MACHINE HOURS USED (BB+ FB) 20 21 INCOME STATEMENT 22 BASKETBALL REVENUE (SALES) 23 FOOTBALL REVENUE (SALES) 24 TOTAL REVENUE 25 BASKETBALL MATERIALS COST 26 FOOTBALL MATERIALS COST 27 COST OF MACHINE LABOR 28 TOTAL COST OF GOODS SOLD 29 INCOME BEFORE TAXES 30 INCOME TAX EXPENSE 31 NET INCOME AFTER TAXES 32 33 34 35 36 37 38 $14.00 $22.00 $36.00 $2.00 $2.50 $11.00 $15.50 $20.50 $5.74 $14.76 39 40 11