G Advertising (5) D $25,000,000 $25,000,000 E $50,000,000 $50,000,000 19 20 21 22 23 24 25 Supplier Contract and Profitability Supplier Contract and Profitability Supplier Contract $197,786,074 $0 $10,000,000 $20,000,000 $30,000,000 $40,000,000 $50,000,000 $60,000,000 $70,000,000 $80,000,000 $90,000,000 $100,000,000 5: 55 $3 51 50 Profit 27 50 2 29 30 31 32 33 5. So.. . torra $10.00 $20.000 S. Supper Contract Level Son $350.000 Advertising Budget $50.000.000 $75,000,000 Price, Advertising, and Profitability 51 53 S1 TotalProfe 51 30 5.000.000 40 41 42 43 44 45 46 47 Ice 4 49 10 $25,000,000 $200 $210 $220 $230 $240 $250 $260 $270 $200 5290 5300 $310 $320 $330 5340 $350 5360 Pricing Paycheck Circular References 50 500 SOMOS Pre % 53 Ready Add-ins loade 1 LOAN Decisions to be Made Price $290 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 6 7 8 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $199 $26 $173 Profitability Total Revenue $761,250,000 (-) Total Variable Costs $453,463.926 - Total Fixed Costs $110,000,000 Total Profit $197 786,074 Demand Calculations Price Demand Factor Total Demand 45% 2625000 Inputs Price (3) Supplier Contract (CA) Advertising (5) $250 Decision Set 1 Decision Set 2 $200 $50,000,000 $75,000,000 $25,000,000 $25,000,000 Decision Set 3 Decision Set 4 5329 $2501 $100,000,000 $20,000,000 $50,000,000 $50,000,000 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Supplier Contract and Profitability Supplier Contract and Profitability Supplier Contract 5197, 786,074 $0 $10,000,000 520,000,000 $30,000,000 $40,000,000 550,000,000 560,000,000 570,000,000 $80,000,000 $90,000,000 $100,000,000 Profit 31 $1 51 53 50 50 50 to SO $3.000 Doors 0.000.000 ..D Supplier Contracte ... 5.000.000 + Pricing Paycheck Circular References Ready 8.1 Create a one-way data table for profit at different levels of supplier contact in range B22:033). a. Ensure that the price in cell C3) is $290 and the advertising budget in cell C5) is $35,000,000 (you may need to manually adjust these values). b. Use the values in range B23:B33 as the different levels of the supplier contract. C. Reference the profit calculation in cell F12). d. 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 the profitability. 8.2 Create a two-way data table for profit at different levels of price and advertising budget in range B36:E57). . a. Use the values in range B37:B57) as the values for Price. b. Use the values in range C36:E36 as the values for Advertising Budget. c. Reference the profit calculation in cell F12)