3.Create a one-way data table using the values in cells B32:B43 after referencing total profit (calculated in cell F12) to determine the supplier contract amount that leads to the most profit given that price is $290 and advertising budget is $35,000,000. Enter this contract amount in cell C45. Notice the "Supplier Contract and Profitability" chart updates with the values in the data table to visually demonstrate the relationship between supplier contract amounts and profitability. [10 points]

File Home Insert Page Layout Formulas Data Review View Help Assignment Share Comments Instruction Task Sheet Guide ca Submit View Report Automatic Scoring Show Instructions C45 fac A B D E F 1 M N o R s T U v w X Y p to es: 1 2 3 4 5 6 7 o 8 9 10 Decisions to be Made Price $2901 Supplier Contract $75,000,000 Advertising Budget $35,000,000 Market Information Competitor Pricing $200 Base Demand 1,000,000 Market Size 5,000,000 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $1991 $261 $173 Profitability Total Revenue $761,250,000 (-) Total Variable Cost $453,463,926 (-) Total Fixed Costs $110,000,000 11 12 13 Total Profit $197,786,074 Introducing the new, smaller eTablet to market ru Work II CCTIC produce UCRTUPTIC CUT TOT your company TIC CUVIC computer offering, a smaller version of your wildly popular eTablet line. You have been given the task of determining three important decisions for this new product. First, you have been asked to determine the price for this product. Pricing is a tricky decision. You don't want to price the new tablet too high because few customers will choose the new product over your full-sized tablet offerings and you risk losing sales to your va sale raggressively priced competitors' products. You don't want to price the product too low, because you want to Compania more earn as much revenue as possible from the product. Second, you must determine where to set the marketing budget for the new product. You know that there will be base demand for your product that comes from your loyal customers who will buy just about anything you produce. Beyond that you also know that every dollar you spend on "maceve advertising will increase the demand for your product. Of course, there is a limit to how much money you will want to spend on advertising because eventually more money spent on advertising will have little effect on demand and will reduce the profitability of ans the new product. Finally, you have been asked to help decide how much money to prepay to the suppliers of the raw materials of the new product to reduce the overall costs of these materials. Every dollar you spend on prepaying your suppliers will reduce the costs of these materials and will ensure that your competitors don't have access to these materials. You have completed a spreadsheet model to aid in your analysis. Use the "What If Analysis' options in Excel to help you determine the right price, advertising Demand Calculations Price Demand Factor Total Demand -45% 26250001 15 16 17 18 TO 19 Inputs Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 Price (C3) $200 $250 $329 $250 Supplier Contract (C4) $50,000,000 $75,000,000 $100,000,000 $20,000,000 Advertising (C5) $25,000,000 $25,000,000 $50,000,000 $50,000,000 20 21 A Which Scenario is most profitable? Decision Set 3 10 Ji 22 23 24 25 26 -- 27 28 20 29 30 31 Goal Seek Parameters and Results Set Cell $C$14 To Value 2,625,000 By Changing Cell $C$3 Supplier Contract and Profitability Resulting Price? $290 1 0.9 0.8 0.7 Profit 34 0.6 0.5 0.4 03 36 Supplier Contract and Profitability Supplier Contract $0 $10,000,000 $20,000,000 $30,000,000 www. $40,000,000 $50,000,000 $60,000,000 $70,000,000 $80,000,000 $90,000,000 $100,000,000 0.2 0.1 0 so $20,000,000 38 39 40 41 42 43 44 45 46 47 $50,000,000 $60,000,000 $100,000,000 $10,000,000 $30,000,000 $40,000,000 Supplier Contract Level $70,000,000 $80,000,000 $90,000,000 Optimal Supplier Contract? Advertising Budget Price, Advertising, and Profitability Paycheck Pricing Scenario Summary + a File Home Insert Page Layout Formulas Data Review View Help Assignment Share Comments Instruction Task Sheet Guide 60 Submit View Report Show Instructions Automatic Scoring C45 B $70,000,000 $80,000,000 $60,000,000 $50,000,000 $40,000,000 $30,000,000 $10,000,000 $20,000,000 D K L M N o P o R s T U V w X Y ooo $70,000,000 $80,000,000 $90,000,000 $100,000,000 0 Supplier Contract Level 43 A 45 Optimal Supplier Contract? Advertising Budget $50,000,000 $75,000,000 Price, Advertising, and Profitability $25,000,000 $1 $1 $1 Total Profit $25,000,000 $200 $210 $220 $230 $240 $250 $260 $270 $280 VOU $290 $300 $310 wear $320 wear $330 weer $340 wer $350 we $360 $370 $380 $390 $400 $0 $0 $0 66 67 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 Price 71 Chart Area How much should they spend on advertising? Where should they set their price? 13 74 13 16 80 81 82 85 86 87 Paycheck Pricing Scenario Summary + 80% File Home Insert Page Layout Formulas Data Review View Help Assignment Share Comments Instruction Task Sheet Guide ca Submit View Report Automatic Scoring Show Instructions C45 fac A B D E F 1 M N o R s T U v w X Y p to es: 1 2 3 4 5 6 7 o 8 9 10 Decisions to be Made Price $2901 Supplier Contract $75,000,000 Advertising Budget $35,000,000 Market Information Competitor Pricing $200 Base Demand 1,000,000 Market Size 5,000,000 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $1991 $261 $173 Profitability Total Revenue $761,250,000 (-) Total Variable Cost $453,463,926 (-) Total Fixed Costs $110,000,000 11 12 13 Total Profit $197,786,074 Introducing the new, smaller eTablet to market ru Work II CCTIC produce UCRTUPTIC CUT TOT your company TIC CUVIC computer offering, a smaller version of your wildly popular eTablet line. You have been given the task of determining three important decisions for this new product. First, you have been asked to determine the price for this product. Pricing is a tricky decision. You don't want to price the new tablet too high because few customers will choose the new product over your full-sized tablet offerings and you risk losing sales to your va sale raggressively priced competitors' products. You don't want to price the product too low, because you want to Compania more earn as much revenue as possible from the product. Second, you must determine where to set the marketing budget for the new product. You know that there will be base demand for your product that comes from your loyal customers who will buy just about anything you produce. Beyond that you also know that every dollar you spend on "maceve advertising will increase the demand for your product. Of course, there is a limit to how much money you will want to spend on advertising because eventually more money spent on advertising will have little effect on demand and will reduce the profitability of ans the new product. Finally, you have been asked to help decide how much money to prepay to the suppliers of the raw materials of the new product to reduce the overall costs of these materials. Every dollar you spend on prepaying your suppliers will reduce the costs of these materials and will ensure that your competitors don't have access to these materials. You have completed a spreadsheet model to aid in your analysis. Use the "What If Analysis' options in Excel to help you determine the right price, advertising Demand Calculations Price Demand Factor Total Demand -45% 26250001 15 16 17 18 TO 19 Inputs Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 Price (C3) $200 $250 $329 $250 Supplier Contract (C4) $50,000,000 $75,000,000 $100,000,000 $20,000,000 Advertising (C5) $25,000,000 $25,000,000 $50,000,000 $50,000,000 20 21 A Which Scenario is most profitable? Decision Set 3 10 Ji 22 23 24 25 26 -- 27 28 20 29 30 31 Goal Seek Parameters and Results Set Cell $C$14 To Value 2,625,000 By Changing Cell $C$3 Supplier Contract and Profitability Resulting Price? $290 1 0.9 0.8 0.7 Profit 34 0.6 0.5 0.4 03 36 Supplier Contract and Profitability Supplier Contract $0 $10,000,000 $20,000,000 $30,000,000 www. $40,000,000 $50,000,000 $60,000,000 $70,000,000 $80,000,000 $90,000,000 $100,000,000 0.2 0.1 0 so $20,000,000 38 39 40 41 42 43 44 45 46 47 $50,000,000 $60,000,000 $100,000,000 $10,000,000 $30,000,000 $40,000,000 Supplier Contract Level $70,000,000 $80,000,000 $90,000,000 Optimal Supplier Contract? Advertising Budget Price, Advertising, and Profitability Paycheck Pricing Scenario Summary + a File Home Insert Page Layout Formulas Data Review View Help Assignment Share Comments Instruction Task Sheet Guide 60 Submit View Report Show Instructions Automatic Scoring C45 B $70,000,000 $80,000,000 $60,000,000 $50,000,000 $40,000,000 $30,000,000 $10,000,000 $20,000,000 D K L M N o P o R s T U V w X Y ooo $70,000,000 $80,000,000 $90,000,000 $100,000,000 0 Supplier Contract Level 43 A 45 Optimal Supplier Contract? Advertising Budget $50,000,000 $75,000,000 Price, Advertising, and Profitability $25,000,000 $1 $1 $1 Total Profit $25,000,000 $200 $210 $220 $230 $240 $250 $260 $270 $280 VOU $290 $300 $310 wear $320 wear $330 weer $340 wer $350 we $360 $370 $380 $390 $400 $0 $0 $0 66 67 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 Price 71 Chart Area How much should they spend on advertising? Where should they set their price? 13 74 13 16 80 81 82 85 86 87 Paycheck Pricing Scenario Summary + 80%