Solve 8.1, 8.2 8.3

AutoSave OF APvc ... Book4 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments G D E Queries & Connections Properties Analysis Tools 20 ml 7 cioar > Reapply FC Flash Fill Data Validation Text to Romave Duplicates go Consolidata Columns Gi Group 2 Ungrouo Subtotal Get Data Currencies Data from Picture Refresh All Stock Filter 7 Sort Edit Links Advanced What if Analysis Data Analysis C22 xfx A B B E F H 1 L M N MyEducator Tasks 1 2 3 3 4 5 6 7 Decisions to be Made Price $290 Supplier Contract $75,100,000 Actvertising 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 Ad usted Variable Costs $199 $26 $173 Profitability Tatal Revenue - Total Variable costs (-) Total Fixed Casts $761,250,000 $453,463,926 $110,000,000 Tatal Profit $197,786,074 Demand Calculations Price Demard Factor Total Dernanc -45% 262500C WANASE 15 16 Inputs Price (3) Supplier Contract (C4) Advertising (5 Decision Set 1 Decision Set 2 S200 $250 $50,000,000 $75,000,000 $25,000,000 $25.000.000 Decision Set 3 $329 $100,000,000 $50,000,000 Decision Set 4 $250 $20,000 000 $50,000,000 8.1 Create a one-way data table for profit at different levels of supplier contact in range B22:C33 a. Ensure that the price in cell 03) is shule $290 and the advertising budget in cell C5) is $35,000,000 (you may need to manually adjust these nec 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 Not Profitability chart updates with the values in the data table to visually demonstrate the relationship between supplier contract amounts and the profitability. Supplier Contract and Profitability $1 51 $1 $1 Supplier Contract and Profitability Supplier Contract $0 $10,000,000 $20,000,000 $20,000,000 $40,000,000 $50.000.000 $60,000,000 $70,000,000 $80.000.000 $91,000,000 $100.000.000 30 SO 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 SO 0 0, Ste SH0.08.09 ....! 00S Supplier Contact Level Advertising Budget $50,000,000 $75,000,000 Price, Advertising, and Profitability $25,000,000 $1 51 $200 $210 $220 $230 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:357) as the values for Price. b. Use the values in range C36:E36) as the values for Advertising 51 Scenario Summary Pricing Paycheck + Edit 100% AutoSave OF APvc ... Book4 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments G D E Queries & Connections Properties 20 ml 7 cioar FC Analysis Tools > Reapply Gi Group Ungroup Subtotal Get Data Currencies Data from Picture Refresh All Filter Stock 7 Sort Flash Fill Data Validation Text to Ramave Duplicates So Consolidata Columns Edit Links Advanced What if Analysis Data Analysis C22 xfx A B B E F H 1 L M N * 1 2 3 3 4 5 6 7 Decisions to be Made Price $290 Supplier Contract $75,100,000 Actvertising Budget $35,000,000 Market Information Competitor Pricing $200 Base Demand 1,000,000 Market Size 5,000,000 MyEducator Tasks values in the data table to visually demonstrate the relationship between supplier contract amounts and the profitability. 8 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Ad usted Variable Costs $199 $26 $173 Profitability Tatal Revenue - Total Variable costs (-) Total Fixed Casts $761,250,000 $453,463,926 $110,000,000 Tatal Profit $197,786,074 Demand Calculations Price Demard Factor Total Dernand -45% 262500C WANASE 15 16 Inputs Decision Set 1 Decision Set 2 S200 $250 $50,000,000 $75,000,000 $25,000,000 $25.000.000 Price (3) Supplier Contract (C4) Advertising (5 Decision Set 3 $329 $100,000,000 $50,000,000 8.2 Create a two-way data table for profit at different levels of price and advertising budget in range B36:157 a. Use the values in range B37:B57) as the values for Price. b. Use the values in range C36:136) as the values for Advertising Budget c. Reference the profit calculation in cell F12 Decision Set 4 $250 $20,000 000 $50,000,000 Supplier Contract and Profitability $1 51 $1 $1 Supplier Contract and Profitability Supplier Contract $0 $ $10,000,000 $20.000.000 $20,000,000 $40,000,000 $50.000.000 $60,000,000 $70,000,000 $80.000.000 $91,000,000 $100.000.000 30 SO 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 SO 0 0, Ste SH0.08.09 ....! 00S E 7 8.3 Complete the price, Advertising, and Profitability chart. a. Notice that the series for $25,000,000 is already on the chart. b. Add the series for advertising budgets $50,000,000 and $75,000,000 from your data table to the chart. Supplier Contact Level Advertising Budget $50,000,000 $75,000,000 Price, Advertising, and Profitability $25,000,000 $1 51 $200 $210 $220 $230 51 IE Scenario Summary 4 Pricing Paycheck + Edit + 100% Excel File Edit View Insert Format Tools Data Window Help a 2 Tue Apr 26 4:42 PM AutoSave OFF WA 9 m 2 C... Book4 Home Insert Draw Page Layout Formulas Data Review View Tell me Au Share Commer Hon Auto TTTT 5 Queries & Connections Properties Edit Links Analysis Tools > Bay FRE Get Data 21 A 7 Clear Sort Home dl Group Ungroup # Subtotal Refresh All Stocks Currencies FO What-11 Analysis Data from Picture Geography Automatic Filter Reapply Advanced Text to Columns - Data Analysis G Da C22 x fx C22 Get Data A B C D E F G H H 1 L M M N $1 $1 $0 MyEducator Tasks ds me values or Price. b. Use the values in range C36:E36 as the values for Advertising Budget. C. Reference the profit calculation in cell F12 $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 $0 $0 50 $10.000.000 $20,000,000 $50,000,000 $30,000,000 $40,000,000 $60,000,000 $70,000,000 $100,000,000 Supplier Contract Level $80.000.000 $ Advertising Budget $25,000,000 $50,000,000 $75,000,000 Price, Advertising, and Profitability $1 $1 $1 $1 C22 1 25 2 26 3 1 4 27 2 5 5 28 3 6 4 29 7 5 30 8 6 9 31 7 10 32 8 11 9 33 12 10 34 13 11 35 14 12 15 36 13 16 14 37 17 15 38 18 16 39 19 17 40 20 18 21 41 19 22 20 42 23 21 43 24 22 25 44 23 26 45 24 27 25 46 28 26 47 29 27 30 48 28 31 29 49 32 30 50 33 31 51 34 32 52 35 33 36 53 34 37 35 54 38 36 55 39 37 56 40 38 57 39 40 58 EC 59 7 8.3 Complete the Price, Advertising, and Profitability chart. a. Notice that the series for $25,000,000 is already on the chart. b. Add the series for advertising budgets $50,000,000 and $75,000,000 from your data table to the chart. $ 25,000,000 $0 SO $0 $200 $210 $220 $230 $240 More $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 $200 $220 $240 $260 $280 $300 $320 $340 $360 $380 $400 Price Price 4 9) Consider the one-way - data table. What is the optimal supplier contract? Edit Scenario Summary Pricing Paycheck + 1009 24 APR 2 26 J. o tv si ZA 12:10 POST YATTEN AutoSave OF APvc ... Book4 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments G D E Queries & Connections Properties Analysis Tools 20 ml 7 cioar > Reapply FC Flash Fill Data Validation Text to Romave Duplicates go Consolidata Columns Gi Group 2 Ungrouo Subtotal Get Data Currencies Data from Picture Refresh All Stock Filter 7 Sort Edit Links Advanced What if Analysis Data Analysis C22 xfx A B B E F H 1 L M N MyEducator Tasks 1 2 3 3 4 5 6 7 Decisions to be Made Price $290 Supplier Contract $75,100,000 Actvertising 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 Ad usted Variable Costs $199 $26 $173 Profitability Tatal Revenue - Total Variable costs (-) Total Fixed Casts $761,250,000 $453,463,926 $110,000,000 Tatal Profit $197,786,074 Demand Calculations Price Demard Factor Total Dernanc -45% 262500C WANASE 15 16 Inputs Price (3) Supplier Contract (C4) Advertising (5 Decision Set 1 Decision Set 2 S200 $250 $50,000,000 $75,000,000 $25,000,000 $25.000.000 Decision Set 3 $329 $100,000,000 $50,000,000 Decision Set 4 $250 $20,000 000 $50,000,000 8.1 Create a one-way data table for profit at different levels of supplier contact in range B22:C33 a. Ensure that the price in cell 03) is shule $290 and the advertising budget in cell C5) is $35,000,000 (you may need to manually adjust these nec 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 Not Profitability chart updates with the values in the data table to visually demonstrate the relationship between supplier contract amounts and the profitability. Supplier Contract and Profitability $1 51 $1 $1 Supplier Contract and Profitability Supplier Contract $0 $10,000,000 $20,000,000 $20,000,000 $40,000,000 $50.000.000 $60,000,000 $70,000,000 $80.000.000 $91,000,000 $100.000.000 30 SO 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 SO 0 0, Ste SH0.08.09 ....! 00S Supplier Contact Level Advertising Budget $50,000,000 $75,000,000 Price, Advertising, and Profitability $25,000,000 $1 51 $200 $210 $220 $230 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:357) as the values for Price. b. Use the values in range C36:E36) as the values for Advertising 51 Scenario Summary Pricing Paycheck + Edit 100% AutoSave OF APvc ... Book4 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments G D E Queries & Connections Properties 20 ml 7 cioar FC Analysis Tools > Reapply Gi Group Ungroup Subtotal Get Data Currencies Data from Picture Refresh All Filter Stock 7 Sort Flash Fill Data Validation Text to Ramave Duplicates So Consolidata Columns Edit Links Advanced What if Analysis Data Analysis C22 xfx A B B E F H 1 L M N * 1 2 3 3 4 5 6 7 Decisions to be Made Price $290 Supplier Contract $75,100,000 Actvertising Budget $35,000,000 Market Information Competitor Pricing $200 Base Demand 1,000,000 Market Size 5,000,000 MyEducator Tasks values in the data table to visually demonstrate the relationship between supplier contract amounts and the profitability. 8 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Ad usted Variable Costs $199 $26 $173 Profitability Tatal Revenue - Total Variable costs (-) Total Fixed Casts $761,250,000 $453,463,926 $110,000,000 Tatal Profit $197,786,074 Demand Calculations Price Demard Factor Total Dernand -45% 262500C WANASE 15 16 Inputs Decision Set 1 Decision Set 2 S200 $250 $50,000,000 $75,000,000 $25,000,000 $25.000.000 Price (3) Supplier Contract (C4) Advertising (5 Decision Set 3 $329 $100,000,000 $50,000,000 8.2 Create a two-way data table for profit at different levels of price and advertising budget in range B36:157 a. Use the values in range B37:B57) as the values for Price. b. Use the values in range C36:136) as the values for Advertising Budget c. Reference the profit calculation in cell F12 Decision Set 4 $250 $20,000 000 $50,000,000 Supplier Contract and Profitability $1 51 $1 $1 Supplier Contract and Profitability Supplier Contract $0 $ $10,000,000 $20.000.000 $20,000,000 $40,000,000 $50.000.000 $60,000,000 $70,000,000 $80.000.000 $91,000,000 $100.000.000 30 SO 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 SO 0 0, Ste SH0.08.09 ....! 00S E 7 8.3 Complete the price, Advertising, and Profitability chart. a. Notice that the series for $25,000,000 is already on the chart. b. Add the series for advertising budgets $50,000,000 and $75,000,000 from your data table to the chart. Supplier Contact Level Advertising Budget $50,000,000 $75,000,000 Price, Advertising, and Profitability $25,000,000 $1 51 $200 $210 $220 $230 51 IE Scenario Summary 4 Pricing Paycheck + Edit + 100% Excel File Edit View Insert Format Tools Data Window Help a 2 Tue Apr 26 4:42 PM AutoSave OFF WA 9 m 2 C... Book4 Home Insert Draw Page Layout Formulas Data Review View Tell me Au Share Commer Hon Auto TTTT 5 Queries & Connections Properties Edit Links Analysis Tools > Bay FRE Get Data 21 A 7 Clear Sort Home dl Group Ungroup # Subtotal Refresh All Stocks Currencies FO What-11 Analysis Data from Picture Geography Automatic Filter Reapply Advanced Text to Columns - Data Analysis G Da C22 x fx C22 Get Data A B C D E F G H H 1 L M M N $1 $1 $0 MyEducator Tasks ds me values or Price. b. Use the values in range C36:E36 as the values for Advertising Budget. C. Reference the profit calculation in cell F12 $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 $0 $0 50 $10.000.000 $20,000,000 $50,000,000 $30,000,000 $40,000,000 $60,000,000 $70,000,000 $100,000,000 Supplier Contract Level $80.000.000 $ Advertising Budget $25,000,000 $50,000,000 $75,000,000 Price, Advertising, and Profitability $1 $1 $1 $1 C22 1 25 2 26 3 1 4 27 2 5 5 28 3 6 4 29 7 5 30 8 6 9 31 7 10 32 8 11 9 33 12 10 34 13 11 35 14 12 15 36 13 16 14 37 17 15 38 18 16 39 19 17 40 20 18 21 41 19 22 20 42 23 21 43 24 22 25 44 23 26 45 24 27 25 46 28 26 47 29 27 30 48 28 31 29 49 32 30 50 33 31 51 34 32 52 35 33 36 53 34 37 35 54 38 36 55 39 37 56 40 38 57 39 40 58 EC 59 7 8.3 Complete the Price, Advertising, and Profitability chart. a. Notice that the series for $25,000,000 is already on the chart. b. Add the series for advertising budgets $50,000,000 and $75,000,000 from your data table to the chart. $ 25,000,000 $0 SO $0 $200 $210 $220 $230 $240 More $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 $200 $220 $240 $260 $280 $300 $320 $340 $360 $380 $400 Price Price 4 9) Consider the one-way - data table. What is the optimal supplier contract? Edit Scenario Summary Pricing Paycheck + 1009 24 APR 2 26 J. o tv si ZA 12:10 POST YATTEN