Applicable areas are in pictures 2 and 3 using the Data>What-If Analysis>Data table feature
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.
Tell me what you want to do Home Page Layout Data View BLUEBEAM Assignment Show Qu Co Consolidate a Fr Flash Fi From Table Properties Reapply Ungroup From Web From Othe Existing New Refresh Sort Filter Text to What Forecast Subtotal From Text Edit Links Advanced Recent Sources Data Validation Manage Data Model Sources Connections Query Colum Analysi Get External Data Get & Transform Connection Sort & Filte Data Tool Foreca D29 Decisions to be Made Market Information Introducing the new, smaller eTablet to market $200 Price Competitor Pricing You work on the new product developmen eam for your company's new tablet computer $75,000,000 Supplier Contract Base Demand 1,000,000 offering, a smaller version ofyour w dly popular eTablet line. You have been given the task of $35,000,000 Advertising Budget Market Size 5,000,000 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 wan o price the new Variable Cost Calculations Profitability tablet too high because few customers will choose the new product over your full-sized tablet Variable Production Costs $761,250,000 Total Revenue offerings and you risk losing sales to your aggressively priced competitors' products. You don't $26 Total Variable Costs $453,463,926 Prepaid Discount Amount o price the product too lo w, because you wan o earn as much revenue as possible om t Wan 10 Adjusted Variable Costs $173 Total Fixed Costs $110,000,00 product. Second, you must determine where to set the m arketing budget for the new product. Y know that there will be a base demand for your product that comes from you oyal customers w $197,786,074 Demand Calculations Total Profit will buy just about anything you produce. Beyond that you also know that every dolla you spen Price Demand Fa -45% ctor on advertising wi ncrease the demand for your product. Of course, there is a limit to how muc otal Demand 2,625,000 want to spend on advertising because eventually more money spent on advert money you will have ct on demand and w reduce the profitability of the new product. Finally, y tle effet have been asked to help decide how much money to prepay to the suppliers of the raw material Inputs Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 of the new product to reduce the overall costs of these materials. Every dollar you spend on $200 $250 $329 Price (C30 $250 educe the costs of these mi aterials and wi ensure that you prepaying your suppliers vi Supplier Contract (CA) S50,000,000 $75,000,000 S100,000,000 S20,000,000 competitors don't have access to these materials. You have completed a spreadsheet model to a $25,000,000 $25,000,000: $50,000,000: $50,000,000 Advertising (C5 20 n your analysis. Use the 'What o help you determine the right price Analysis options in Xcel advertisin endin and prepaid su roduct contract fo 22 Which Scenario is most profitable? Decision set 3 Paycheck Pricing Scenario Summary Tell me what you want to do Home Page Layout Data View BLUEBEAM Assignment Show Qu Co Consolidate a Fr Flash Fi From Table Properties Reapply Ungroup From Web From Othe Existing New Refresh Sort Filter Text to What Forecast Subtotal From Text Edit Links Advanced Recent Sources Data Validation Manage Data Model Sources Connections Query Colum Analysi Get External Data Get & Transform Connection Sort & Filte Data Tool Foreca D29 Decisions to be Made Market Information Introducing the new, smaller eTablet to market $200 Price Competitor Pricing You work on the new product developmen eam for your company's new tablet computer $75,000,000 Supplier Contract Base Demand 1,000,000 offering, a smaller version ofyour w dly popular eTablet line. You have been given the task of $35,000,000 Advertising Budget Market Size 5,000,000 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 wan o price the new Variable Cost Calculations Profitability tablet too high because few customers will choose the new product over your full-sized tablet Variable Production Costs $761,250,000 Total Revenue offerings and you risk losing sales to your aggressively priced competitors' products. You don't $26 Total Variable Costs $453,463,926 Prepaid Discount Amount o price the product too lo w, because you wan o earn as much revenue as possible om t Wan 10 Adjusted Variable Costs $173 Total Fixed Costs $110,000,00 product. Second, you must determine where to set the m arketing budget for the new product. Y know that there will be a base demand for your product that comes from you oyal customers w $197,786,074 Demand Calculations Total Profit will buy just about anything you produce. Beyond that you also know that every dolla you spen Price Demand Fa -45% ctor on advertising wi ncrease the demand for your product. Of course, there is a limit to how muc otal Demand 2,625,000 want to spend on advertising because eventually more money spent on advert money you will have ct on demand and w reduce the profitability of the new product. Finally, y tle effet have been asked to help decide how much money to prepay to the suppliers of the raw material Inputs Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 of the new product to reduce the overall costs of these materials. Every dollar you spend on $200 $250 $329 Price (C30 $250 educe the costs of these mi aterials and wi ensure that you prepaying your suppliers vi Supplier Contract (CA) S50,000,000 $75,000,000 S100,000,000 S20,000,000 competitors don't have access to these materials. You have completed a spreadsheet model to a $25,000,000 $25,000,000: $50,000,000: $50,000,000 Advertising (C5 20 n your analysis. Use the 'What o help you determine the right price Analysis options in Xcel advertisin endin and prepaid su roduct contract fo 22 Which Scenario is most profitable? Decision set 3 Paycheck Pricing Scenario Summary
