I need help with questions 3 and 4. Please give step by step instructions. I am not getting the graph correct and would like some guidance.
Assignment Tasks Pricing, Advertising, and Prepaid Contracts Create a one-way data table using the values in cells B32:343 after referencing total profit (calculated in cell F12) to determine the supplier contract amount that leads to the most 3 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. [10 points) Assignment Tasks Pricing, Advertising, and Prepaid Contracts Create a two-way data table after referencing total profit (calculated in cell F12) and using the values in cells B49:869 (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) Introducing the new, smaller e Tablet to market Decisions to be Made Price Supplier Contract Advertising Budget $290 $50,000,000 $25,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 Adjusted Variable Costs $199 $21 $178 Profitability Total Revenue (-) Total Variable Costs (-) Total Fixed Costs $761,250,000 $467,331,112 $75,000,000 Total Profit $218,918,888 Demand Calculations Price Demand Factor Total Demand -45% 2625000 You work on the new product development team for your company's new tablet computer offering, a smaller version of your wildly popular e Tablet line. You have been given the task of determining three important decisions for this new product. First, you have been asked to determine the price for this product. Pricing is a tricky decision. You don't want to price the new tablet too high because few customers will choose the new product over your full-sized tablet offerings and you risk losing sales to your aggressively priced competitors' products. You don't want to price the product too low, because you want to earn as much revenue as possible from the product. Second, you must determine where to set the marketing budget for the new product. You know that there will be a base demand for your product that comes from your 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 money you will want to spend on advertising because eventually more money spent on advertising will have little effect on demand and will reduce the profitability of the new product. Finally, you have been asked to help decide how much money to prepay to the suppliers of the raw materials of the new 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 have completed a spreadsheet model to aid in your analysis. Use the What If Analysis' options in Excel to help you determine the right price, advertising spending, and prepaid supplier contract for your new product. 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 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 and Profitability 225000000 Resulting Price? $290 220000000 215000000 210000000 205000000 200000000 Supplier Contract and Profitability Supplier Contract $218,918,888 $0 213875000 $10,000,000 203875000 $20,000,000 217581112.2 $30,000,000 221448298.8 $40,000,000 221287224.3 $50,000,000 218918887.8 $60,000,000 215154411 $70,000,000 210426470.7 $80,000,000 204993336.5 $90,000,000 199021597.6 $100,000,000 192625000 195000000 190000000 185000000 180000000 175000000 $0 $40,000,000 $50,000,000 $60,000,000 $70,000,000 $80,000,000 $90,000,000 000'000'OOTS Supplier Contract Level Optimal Supplier Contract? Advertising Budget $50,000,000 Price, Advertising, and Profitability $25,000,000 $75,000,000 $1 $1 $1 Total Profit $1 -$25,000,000 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 SO $0 $0 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 Price How much should they spend on advertising? Where should they set their price? Assignment Tasks Pricing, Advertising, and Prepaid Contracts Create a one-way data table using the values in cells B32:343 after referencing total profit (calculated in cell F12) to determine the supplier contract amount that leads to the most 3 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. [10 points) Assignment Tasks Pricing, Advertising, and Prepaid Contracts Create a two-way data table after referencing total profit (calculated in cell F12) and using the values in cells B49:869 (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) Introducing the new, smaller e Tablet to market Decisions to be Made Price Supplier Contract Advertising Budget $290 $50,000,000 $25,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 Adjusted Variable Costs $199 $21 $178 Profitability Total Revenue (-) Total Variable Costs (-) Total Fixed Costs $761,250,000 $467,331,112 $75,000,000 Total Profit $218,918,888 Demand Calculations Price Demand Factor Total Demand -45% 2625000 You work on the new product development team for your company's new tablet computer offering, a smaller version of your wildly popular e Tablet line. You have been given the task of determining three important decisions for this new product. First, you have been asked to determine the price for this product. Pricing is a tricky decision. You don't want to price the new tablet too high because few customers will choose the new product over your full-sized tablet offerings and you risk losing sales to your aggressively priced competitors' products. You don't want to price the product too low, because you want to earn as much revenue as possible from the product. Second, you must determine where to set the marketing budget for the new product. You know that there will be a base demand for your product that comes from your 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 money you will want to spend on advertising because eventually more money spent on advertising will have little effect on demand and will reduce the profitability of the new product. Finally, you have been asked to help decide how much money to prepay to the suppliers of the raw materials of the new 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 have completed a spreadsheet model to aid in your analysis. Use the What If Analysis' options in Excel to help you determine the right price, advertising spending, and prepaid supplier contract for your new product. 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 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 and Profitability 225000000 Resulting Price? $290 220000000 215000000 210000000 205000000 200000000 Supplier Contract and Profitability Supplier Contract $218,918,888 $0 213875000 $10,000,000 203875000 $20,000,000 217581112.2 $30,000,000 221448298.8 $40,000,000 221287224.3 $50,000,000 218918887.8 $60,000,000 215154411 $70,000,000 210426470.7 $80,000,000 204993336.5 $90,000,000 199021597.6 $100,000,000 192625000 195000000 190000000 185000000 180000000 175000000 $0 $40,000,000 $50,000,000 $60,000,000 $70,000,000 $80,000,000 $90,000,000 000'000'OOTS Supplier Contract Level Optimal Supplier Contract? Advertising Budget $50,000,000 Price, Advertising, and Profitability $25,000,000 $75,000,000 $1 $1 $1 Total Profit $1 -$25,000,000 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 SO $0 $0 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 Price How much should they spend on advertising? Where should they set their price
