signment H M Introducing the new, smaller e Tablet to market You work on the new product development team for your company's new tablet computer offering a smaller version of your wildly popular eTablet 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. Pricingisatricky 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 Profitability ame Insert Draw Page Layout struction Task Sheet Guide Submit View Report 6 x fx 8 A D C Decisions to be Made Price Supplier Contract Advertising Budget $350 $75,000,000 $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 Adjusted Variable costs $199 $26 $173 Profitability Total Revenue (-) Total Variable Costs -) Total Fixed Costs $551,250,000 $272,078,355 $110,000,000 Total Profit $169,171,645 Demand Calculations Price Demand Factor Total Demand -75% 1575000 Inputs Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 Price (C3) $200 $250 $329 $250 Supplier Contract (C4) $50,000,000 $75,000,000 $100,000,000 $20,000,000 Advertising (C5) $25,000,000 $25,000,000 $50,000,000 $50,000,000 m 1 2 Which Scenario is most profitable? 5 16 Seek Parameters and Results Set Cell To Value By Changing Cell Supplier Contract and P 27 28 29 30 Resulting Price? 1.2 1 18 32 33 34 35 36 37 Profit 0.6 0.4 Supplier Contract and Profitability Supplier Contract $0 $10,000,000 $20,000,000 $30,000,000 540,000,000 $50,000,000 $60,000,000 $70.000.000 $80,000,000 590.000.000 5100,000,000 Paycheck Pricing 0.2 Supplier Contract Law $10.000.000 $20.000.000 530.000.000 SAD 000000 350,000,000 560.000.000 struction Task Sheet Gulde Submit View Report 116 x for D 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? in Goal Seek Parameters and Results Set Call To Value By Changing Cell 5 Supplier Contra 12 Resulting Price? 0 1 1 2 0.8 Profit 6 0.4 Supplier Contract and Profitability Supplier Contract $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 14 35 36 37 38 39 40 41 42 43 0.2 O os 520.000.000 $20.000.000 530,000,000 540,000,000 $50 Supplie Optimal Supplier Contract? 45 46 47 48 $25,000,000 Advertising Budget $50,000,000 $75,000,000 50 51 52 53 54 55 56 $200 $210 $220 $230 $240 $250 $260 $270 5280 $290 Paycheck Pricing + B 0.2 0 $30,000,000 $40,000,000 $50,000,000 $60,000,000 $70,000,000 $80,000,000 $90,000,000 $100,000,000 SO $20,000,000 $10,000,000 $30,000,000 $40 Optimal Supplier Contract? Advertising Budget $50,000,000 $25,000,000 $75,000,000 Price Price $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 How much should they spend on advertising? Where should they set their price? Paycheck Pricing signment H M Introducing the new, smaller e Tablet to market You work on the new product development team for your company's new tablet computer offering a smaller version of your wildly popular eTablet 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. Pricingisatricky 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 Profitability ame Insert Draw Page Layout struction Task Sheet Guide Submit View Report 6 x fx 8 A D C Decisions to be Made Price Supplier Contract Advertising Budget $350 $75,000,000 $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 Adjusted Variable costs $199 $26 $173 Profitability Total Revenue (-) Total Variable Costs -) Total Fixed Costs $551,250,000 $272,078,355 $110,000,000 Total Profit $169,171,645 Demand Calculations Price Demand Factor Total Demand -75% 1575000 Inputs Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 Price (C3) $200 $250 $329 $250 Supplier Contract (C4) $50,000,000 $75,000,000 $100,000,000 $20,000,000 Advertising (C5) $25,000,000 $25,000,000 $50,000,000 $50,000,000 m 1 2 Which Scenario is most profitable? 5 16 Seek Parameters and Results Set Cell To Value By Changing Cell Supplier Contract and P 27 28 29 30 Resulting Price? 1.2 1 18 32 33 34 35 36 37 Profit 0.6 0.4 Supplier Contract and Profitability Supplier Contract $0 $10,000,000 $20,000,000 $30,000,000 540,000,000 $50,000,000 $60,000,000 $70.000.000 $80,000,000 590.000.000 5100,000,000 Paycheck Pricing 0.2 Supplier Contract Law $10.000.000 $20.000.000 530.000.000 SAD 000000 350,000,000 560.000.000 struction Task Sheet Gulde Submit View Report 116 x for D 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? in Goal Seek Parameters and Results Set Call To Value By Changing Cell 5 Supplier Contra 12 Resulting Price? 0 1 1 2 0.8 Profit 6 0.4 Supplier Contract and Profitability Supplier Contract $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 14 35 36 37 38 39 40 41 42 43 0.2 O os 520.000.000 $20.000.000 530,000,000 540,000,000 $50 Supplie Optimal Supplier Contract? 45 46 47 48 $25,000,000 Advertising Budget $50,000,000 $75,000,000 50 51 52 53 54 55 56 $200 $210 $220 $230 $240 $250 $260 $270 5280 $290 Paycheck Pricing + B 0.2 0 $30,000,000 $40,000,000 $50,000,000 $60,000,000 $70,000,000 $80,000,000 $90,000,000 $100,000,000 SO $20,000,000 $10,000,000 $30,000,000 $40 Optimal Supplier Contract? Advertising Budget $50,000,000 $25,000,000 $75,000,000 Price Price $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 How much should they spend on advertising? Where should they set their price? Paycheck Pricing