Create a two-way data table after referencing total profit (calculated in cell F12) and using the values in cells B49:B69 (Price) and C48:E48 (Advertising Budget) to analyze the relationships among advertising budget, price, and profitability. Make sure you highlight cells B48:E69 before going to the Data Table tool.

Complete the "Price, Advertising, and Profitability" chart to include series for advertising budgets $50,000,000 and $75,000,000 on your data table (notice that the series for $25,000,000 is already on the chart). Enter the advertising budget amount from your analysis that will produce the most profit E71. Enter the price that will product the most profit in cell E72. [15 points]

Test Your Skills-Modeling Basics Excel HOME INSERI PA LAYCUI FORMULAS DATA REVIEW VIEW Assignment Paige Huidlestcn Instructicn Task Subnit View Sheet Guide XVfSupplier Contract ssignmen Tasks Decisions to be Made Market Informatiorn Pricing, Advertising, and Prepaid Contracts Price Supplier Contract Advertising Budpet 335 520,000,000 Coripetitor Pricing Base Demand Create a one-way data table using the values in cells B32:B13 after referencing total profit (calculated in cell 12) to determine the supplier contract armount that leads to the most 3 profit given that price is $290 and advertising budgt is S35,000,000. Enter this oontract 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 profitabili . [10 ints 1,00D,000 Variable Cost Calculations Variable Producticn Custs Prepaid Discount Aount Adjusted Variable Costs 879,375,0 () Total Variable Costs $198,668,8 0,000,O Tutal Revenue 10 (-) Total ixed Costs priced competitors'products. You don't want to price the product to low, ecausc ynu want to carn as rnuch revenue as possible tram the product. Second, ynu must determine where to set th marketing budget fo' the new pluduct. Yuu knuw lhal lhere will be base deirland fur yout pr loyal customers who will buy just about anything you produce. Beyond that you also know that every dollar you spend on advertising will increase the demand for your product. Of course, there is a limit to how much mancy you will want to spend on advertising herause eventually mare moncy spent on advertising will hawo little effect on deand arnd will reduce the protitability of the rnew praduct. Finally, you have heen asked to help decide how much money to prepay to the suppliers of the raw naterials of the rnew 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 hawe completed spreadsheet m del to sid in your nalysis. Use the 'What if Analysis' options in Excel to help Total Profit S310,706,112 product that cies frorn your 13 Price Denand Factar Totel Deiand 16 Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 S250 329 19 20 Supplier Contract (C4$50000000 S75 000,000000 $20000 25,000,a00 $25,000,000 $50,000,00D $50,000 C5 determine the r ce, atvertising s aid supplier contrart tor Which Scenario is most able? Decision Set 3 24 Goal Seek Parameters and Results Sct Cell To Valur By Chariirg Cell SC$14 2,625,000 Supplier Contract and Profitability 27 28 29 Resulting Price? 340 150CO0OD 3100000OD r Contract and Profitab Supplier Contract Paycheck Scenario Surrirnary Pricing AVERAGE: 1827921006 CONT: 24 SUM: 4204287451 . + 100% Test Your Skills-Modeling Basics Excel HOME INSERI PA LAYCUI FORMULAS DATA REVIEW VIEW Assignment Paige Huidlestcn Instructicn Task Subnit View Sheet Guide XVfSupplier Contract ssignmen Tasks Decisions to be Made Market Informatiorn Pricing, Advertising, and Prepaid Contracts Price Supplier Contract Advertising Budpet 335 520,000,000 Coripetitor Pricing Base Demand Create a one-way data table using the values in cells B32:B13 after referencing total profit (calculated in cell 12) to determine the supplier contract armount that leads to the most 3 profit given that price is $290 and advertising budgt is S35,000,000. Enter this oontract 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 profitabili . [10 ints 1,00D,000 Variable Cost Calculations Variable Producticn Custs Prepaid Discount Aount Adjusted Variable Costs 879,375,0 () Total Variable Costs $198,668,8 0,000,O Tutal Revenue 10 (-) Total ixed Costs priced competitors'products. You don't want to price the product to low, ecausc ynu want to carn as rnuch revenue as possible tram the product. Second, ynu must determine where to set th marketing budget fo' the new pluduct. Yuu knuw lhal lhere will be base deirland fur yout pr loyal customers who will buy just about anything you produce. Beyond that you also know that every dollar you spend on advertising will increase the demand for your product. Of course, there is a limit to how much mancy you will want to spend on advertising herause eventually mare moncy spent on advertising will hawo little effect on deand arnd will reduce the protitability of the rnew praduct. Finally, you have heen asked to help decide how much money to prepay to the suppliers of the raw naterials of the rnew 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 hawe completed spreadsheet m del to sid in your nalysis. Use the 'What if Analysis' options in Excel to help Total Profit S310,706,112 product that cies frorn your 13 Price Denand Factar Totel Deiand 16 Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 S250 329 19 20 Supplier Contract (C4$50000000 S75 000,000000 $20000 25,000,a00 $25,000,000 $50,000,00D $50,000 C5 determine the r ce, atvertising s aid supplier contrart tor Which Scenario is most able? Decision Set 3 24 Goal Seek Parameters and Results Sct Cell To Valur By Chariirg Cell SC$14 2,625,000 Supplier Contract and Profitability 27 28 29 Resulting Price? 340 150CO0OD 3100000OD r Contract and Profitab Supplier Contract Paycheck Scenario Surrirnary Pricing AVERAGE: 1827921006 CONT: 24 SUM: 4204287451 . + 100%