Create a one-way data table using the values in cells B32:B43 and 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.

Create a two-way data table using the values in cells B49:B69 (Price) and C48:E48 (Advertising Budget) to analyze the relationships among advertising budget, price, and profitability. 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.

  
 

Transcribed Image Text:

Decisions to be Made Price Supplier Contract Advertising Budget Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $350 $75,000,000 $35,000,000 Demand Calculations Price Demand Factor Total Demand $199 $26 $173 -75% 1,575,000 Which Scenario is most profitable? Market Information Competitor Pricing Base Demand Market Size Profitability Total Revenue (-) Total Variable Costs (-) Total Fixed Costs Total Profit Inputs Price (C3) Supplier Contract (C4) $50,000,000 $75,000,000 $100,000,000 $20,000,000 Advertising (C5) $50,000,000 $50,000,000 $25,000,000 $25,000,000 Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 $200 $250 $329 $250 Decision Set Selection Choose the Scenario that is most profitable $200 1,000,000 5,000,000 $551,250,000 $272,078,355 $110,000,000 $169,171,645 Decisions to be Made Price Supplier Contract Advertising Budget Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $350 $75,000,000 $35,000,000 Demand Calculations Price Demand Factor Total Demand $199 $26 $173 -75% 1,575,000 Which Scenario is most profitable? Market Information Competitor Pricing Base Demand Market Size Profitability Total Revenue (-) Total Variable Costs (-) Total Fixed Costs Total Profit Inputs Price (C3) Supplier Contract (C4) $50,000,000 $75,000,000 $100,000,000 $20,000,000 Advertising (C5) $50,000,000 $50,000,000 $25,000,000 $25,000,000 Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 $200 $250 $329 $250 Decision Set Selection Choose the Scenario that is most profitable $200 1,000,000 5,000,000 $551,250,000 $272,078,355 $110,000,000 $169,171,645