Question 1. What is the correct answer for the C45? (highlighted in green)
Question 2. How do you solve the table B28:E72 correctly? (Please see Question 2 for instructions
Help me correct the mistake and please tell me how you come up with the answer (or help me understand the problem)
Instructions:
1. 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.
[-5] The supplier contract level identified in C45 is incorrect
2. 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.
Below are the answers that I am missing when I solved the problem. So, how do you solve the question 2 correctly based on the information(screenshots) provided?
[-2] The $50 million information from the data table was not included on the chart
[-2] The $75 million information from the data table was not included on the chart
[-5] The advertising budget amount identified in cell E71 is not correct
[-5] The price identified in E72 is not correct
B D E F 2 3 4 5 Decisions to be Made Price $250 Supplier Contract $20,000,000 Advertising Budget $50,000,000 Market Information Competitor Pricing Base Demand 1,000, Market Size 5,000, 6 7 8 9 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $199 $9 $190 Profitability Total Revenue $1,187,500 (-) Total Variable Costs $902,353 (-) Total Fixed Costs $70,000 10 11 12 13 Total Profit $215,146 Demand Calculations Price Demand Factor Total Demand -25% 4,750,000 14 Inputs Price (C3) Supplier Contract (C4) Advertising (C5) Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 $200 $250 $329 $250 $50,000,000 $75,000,000 $100,000,000 $20,000,000 $25,000,000 $25,000,000 $50,000,000 $50,000,000 .......................... 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Which Scenario is most profitable? Decision Set 3 Goal Seek Parameters and Results Set Cell $C$14 To Value $2,625,000 By Changing Cell $C$3 Supplier Contract 300000000 Resulting Price? $290 A B D H Goal Seek Parameters and Results Set Cell $C$14 To Value $2,625,000 By Changing Cell $C$3 Supplier Contract and Profitability 300000000 23 24 25 26 27 28 29 30 31 32 33 34 35 Resulting Price? $290 250000000 20000000 150000000 100000000 Supplier Contract and Profitability Supplier Contract $215,146,774 $0 192250000 $10,000,000 182250000 $20,000,000 215146774 4 $30,000,000 230239778.8 $40,000,000 238043548.B $50,000,000 241853225.60 $60,000,000 243136553.2 $70,000,000 242576470.7 $80,000,000 240940323.1 $90,000,000 238229557.6 $100,000,000 234750000 37 38 39 40 50000000 0 $0 $10,000,000 $60,000,000 000 bod oss 000'00065 000OOTS $70,000,000 $20,000,000 $30,000,000 $49,000,000 1.000.000 Supplier Contract Level 42 43 44 45 46 Optimal Supplier Contract? $90,000,000 - A B E F H Price, Advertising, and Profita $160,000,000 $140,000,000 $120,000,000 $100,000,000 $80,000,000 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 $215,146,774 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 $60,000,000 D Advertising Budget $50,000,000 -$9,814,601 $45,177,674 $95,169,949 $140,162,224 $180,154,499 $215,146,774 $245,139,049 $270,131,324 $290,123,599 $305,115,875 $315,108,150 $320,100,425 $320,092,700 $315,084,975 $305,077,250 $290,069,525 $270,061,800 $245,054,075 $215,046,350 $180,038,625 $140,030,900 $25,000,000 -$14,907,300 $12,588,837 $37,584,975 $60,081,112 $80,077,250 $97,573,387 $112,569,525 $125,065,662 $135,061,800 $142,557,937 $147,554,075 $150,050, 212 $150,046,350 $147,542,487 $142,538,625 $135,034,762 $125,030,900 $112,527,037 $97,523,175 $80,019,312 $60,015,450 $75,000,000 -$34,814,601 $20,177,674 $70,169,949 $115,162,224 $155,154,499 $190,146,774 $220,139,049 $245,131,324 $265,123,599 $280,115,875 $290,108,150 $295,100,425 $295,092,700 $290,084,975 $280,077,250 $265,069,525 $245,061,800 $220,054,075 $190,046,350 $155,038,625 $115,030,900 Total Profit $40,000,000 $20,000,000 $0 $200 $210$220$230$240 $250 $250 $270 $280$290 $300 $310 $320 $330$340 -$20,000,000 $40,000,000 Price How much should they spend on advertising? Where should they set their price? $75,000,000 $270 72 73 74 75 76 B D E F 2 3 4 5 Decisions to be Made Price $250 Supplier Contract $20,000,000 Advertising Budget $50,000,000 Market Information Competitor Pricing Base Demand 1,000, Market Size 5,000, 6 7 8 9 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $199 $9 $190 Profitability Total Revenue $1,187,500 (-) Total Variable Costs $902,353 (-) Total Fixed Costs $70,000 10 11 12 13 Total Profit $215,146 Demand Calculations Price Demand Factor Total Demand -25% 4,750,000 14 Inputs Price (C3) Supplier Contract (C4) Advertising (C5) Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 $200 $250 $329 $250 $50,000,000 $75,000,000 $100,000,000 $20,000,000 $25,000,000 $25,000,000 $50,000,000 $50,000,000 .......................... 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Which Scenario is most profitable? Decision Set 3 Goal Seek Parameters and Results Set Cell $C$14 To Value $2,625,000 By Changing Cell $C$3 Supplier Contract 300000000 Resulting Price? $290 A B D H Goal Seek Parameters and Results Set Cell $C$14 To Value $2,625,000 By Changing Cell $C$3 Supplier Contract and Profitability 300000000 23 24 25 26 27 28 29 30 31 32 33 34 35 Resulting Price? $290 250000000 20000000 150000000 100000000 Supplier Contract and Profitability Supplier Contract $215,146,774 $0 192250000 $10,000,000 182250000 $20,000,000 215146774 4 $30,000,000 230239778.8 $40,000,000 238043548.B $50,000,000 241853225.60 $60,000,000 243136553.2 $70,000,000 242576470.7 $80,000,000 240940323.1 $90,000,000 238229557.6 $100,000,000 234750000 37 38 39 40 50000000 0 $0 $10,000,000 $60,000,000 000 bod oss 000'00065 000OOTS $70,000,000 $20,000,000 $30,000,000 $49,000,000 1.000.000 Supplier Contract Level 42 43 44 45 46 Optimal Supplier Contract? $90,000,000 - A B E F H Price, Advertising, and Profita $160,000,000 $140,000,000 $120,000,000 $100,000,000 $80,000,000 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 $215,146,774 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 $60,000,000 D Advertising Budget $50,000,000 -$9,814,601 $45,177,674 $95,169,949 $140,162,224 $180,154,499 $215,146,774 $245,139,049 $270,131,324 $290,123,599 $305,115,875 $315,108,150 $320,100,425 $320,092,700 $315,084,975 $305,077,250 $290,069,525 $270,061,800 $245,054,075 $215,046,350 $180,038,625 $140,030,900 $25,000,000 -$14,907,300 $12,588,837 $37,584,975 $60,081,112 $80,077,250 $97,573,387 $112,569,525 $125,065,662 $135,061,800 $142,557,937 $147,554,075 $150,050, 212 $150,046,350 $147,542,487 $142,538,625 $135,034,762 $125,030,900 $112,527,037 $97,523,175 $80,019,312 $60,015,450 $75,000,000 -$34,814,601 $20,177,674 $70,169,949 $115,162,224 $155,154,499 $190,146,774 $220,139,049 $245,131,324 $265,123,599 $280,115,875 $290,108,150 $295,100,425 $295,092,700 $290,084,975 $280,077,250 $265,069,525 $245,061,800 $220,054,075 $190,046,350 $155,038,625 $115,030,900 Total Profit $40,000,000 $20,000,000 $0 $200 $210$220$230$240 $250 $250 $270 $280$290 $300 $310 $320 $330$340 -$20,000,000 $40,000,000 Price How much should they spend on advertising? Where should they set their price? $75,000,000 $270 72 73 74 75 76
