Pricing, Advertising, and Prepaid Contracts

You work on the new product development team for your company's new tablet computer offering, which is 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. 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. 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 product price, advertising spending, and prepaid supplier contract for your new product.

Use the Scenario Manager to input each of the four decision sets depicted in range B16:F19.

The scenario names you must use for each set of data are given in cellsC16:F16.

You must type the data into Scenario Manager, because Excel does not allow you to use a cell reference to enter data into a scenario.

Create the summary of the scenarios on a new worksheet.

Use the default name for the Scenario Summary worksheet because the grader will examine this sheet during the grading process.

Which scenario is most profitable?

Use the Goal Seek tool to determine the price that will generate demand of 2,625,000 tablets.

Make sure that the supplier contract (cellC4) is $75,000,000 and the advertising budget (cellC5) is $35,000,000 (you may need to manually change these values).

Goal Seek does not store your entries. For grading purposes, you will identify the "Set cell", "To value", and "By changing cell" elements of your analysis by answering multiple-choice questions.

What was the "Set cell" value for your Goal Seek analysis?

What was the "To value" value for your Goal Seek analysis?

What was the "By changing cell" value for your Goal Seek analysis?

Create a one-way data table for profit at different levels of supplier contact in rangeB22:C33.

Ensure that the price in cellC3 is $290 and the advertising budget in cellC5 is $35,000,000 (you may need to manually adjust these values).

Use the values in rangeB23:B33 as the different levels of the supplier contract.

Reference the profit calculation in cellF12.

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 the profitability.

Create a two-way data table for profit at different levels of price and advertising budget in rangeB36:E57.

Use the values in rangeB37:B57 as the values for Price.

Use the values in rangeC36:E36 as the values for Advertising Budget.

Reference the profit calculation in cellF12.

Make sure you highlight cellsB36:E57 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).

Consider the one-way data table. What is the optimal supplier contract?

Consider the two-way data table. How much should the company spend on advertising?

Consider the two-way data table. Where should the company set the price?

B D E F G H I J L M N o Q R 1 2 3 4 5 Decisions to be Made Price Supplier Contract Advertising Budget $200 $75,000,000 $35,000,000 Market Information Competitor Pricing Base Demand Market Size $200 1,000,000 5,000,000 6 7 8 9 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $199 $26 $173 Profitability Total Revenue (-) Total Variable Costs (-) Total Fixed Costs $840,000,000 $725,542,281 $110,000,000 10 11 12 13 14 Total Profit $4,457,719 Demand Calculations Price Demand Factor Total Demand 0% 4200000 Inputs Price (C3) Supplier Contract (C4) Advertising (C5) Decision Set 1 $200 $50,000,000 $25,000,000 Decision Set 2 $250 $75,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 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Supplier Contract and Profitability $1 $1 Supplier Contract and Profitability Supplier Contract $0 $10,000,000 $20,000,000 $30,000,000 $40,000,000 $50,000,000 $60 non non Profit $1 $1 $0 $0 $0 B D E F G H 1 J K L M N Supplier Contract and Profitability $1 $1 Profit $1 $1 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 $0 $0 $0 $o $20,000,000 $50,000,000 000'00009$ $70,000,000 000'000'08$ 000'000'06$ $10,000,000 $100,000,000 $30,000,000 $40,000,000 Supplier Contract Level 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 Advertising Budget $50,000,000 Price, Advertising, and Profitability $25,000,000 $75,000,000 $1 $1 $1 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 Total Profit -$25,000,000 $0 $0 Price $200 $220$240$260 $280$300 $320$340 $360 $380 $400 Price A B D F G K L M N $90,000,000 $100,000,000 Supplier Contract Level 32 33 34 35 36 37 38 39 Advertising Budget $50,000,000 Price, Advertising, and Profitability $25,000,000 $75,000,000 $1 $1 40 41 Total Profit 42 -$25,000,000 43 44 $0 45 46 $0 $200 $220$240 $260$280 $300 $320$340 $360 $380 $400 Price 47 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 Price 48 49 50 51 52 53 54 55 56 57 58 59 60 B D E F G H I J L M N o Q R 1 2 3 4 5 Decisions to be Made Price Supplier Contract Advertising Budget $200 $75,000,000 $35,000,000 Market Information Competitor Pricing Base Demand Market Size $200 1,000,000 5,000,000 6 7 8 9 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $199 $26 $173 Profitability Total Revenue (-) Total Variable Costs (-) Total Fixed Costs $840,000,000 $725,542,281 $110,000,000 10 11 12 13 14 Total Profit $4,457,719 Demand Calculations Price Demand Factor Total Demand 0% 4200000 Inputs Price (C3) Supplier Contract (C4) Advertising (C5) Decision Set 1 $200 $50,000,000 $25,000,000 Decision Set 2 $250 $75,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 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Supplier Contract and Profitability $1 $1 Supplier Contract and Profitability Supplier Contract $0 $10,000,000 $20,000,000 $30,000,000 $40,000,000 $50,000,000 $60 non non Profit $1 $1 $0 $0 $0 B D E F G H 1 J K L M N Supplier Contract and Profitability $1 $1 Profit $1 $1 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 $0 $0 $0 $o $20,000,000 $50,000,000 000'00009$ $70,000,000 000'000'08$ 000'000'06$ $10,000,000 $100,000,000 $30,000,000 $40,000,000 Supplier Contract Level 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 Advertising Budget $50,000,000 Price, Advertising, and Profitability $25,000,000 $75,000,000 $1 $1 $1 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 Total Profit -$25,000,000 $0 $0 Price $200 $220$240$260 $280$300 $320$340 $360 $380 $400 Price A B D F G K L M N $90,000,000 $100,000,000 Supplier Contract Level 32 33 34 35 36 37 38 39 Advertising Budget $50,000,000 Price, Advertising, and Profitability $25,000,000 $75,000,000 $1 $1 40 41 Total Profit 42 -$25,000,000 43 44 $0 45 46 $0 $200 $220$240 $260$280 $300 $320$340 $360 $380 $400 Price 47 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 Price 48 49 50 51 52 53 54 55 56 57 58 59 60

Question

Pricing, Advertising, and Prepaid Contracts

You work on the new product development team for your company's new tablet computer offering, which is 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. 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. 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 product price, advertising spending, and prepaid supplier contract for your new product.

Use the Scenario Manager to input each of the four decision sets depicted in range B16:F19.

The scenario names you must use for each set of data are given in cellsC16:F16.
You must type the data into Scenario Manager, because Excel does not allow you to use a cell reference to enter data into a scenario.
Create the summary of the scenarios on a new worksheet.
Use the default name for the Scenario Summary worksheet because the grader will examine this sheet during the grading process.

Which scenario is most profitable?

Use the Goal Seek tool to determine the price that will generate demand of 2,625,000 tablets.

Make sure that the supplier contract (cellC4) is $75,000,000 and the advertising budget (cellC5) is $35,000,000 (you may need to manually change these values).
Goal Seek does not store your entries. For grading purposes, you will identify the "Set cell", "To value", and "By changing cell" elements of your analysis by answering multiple-choice questions.

What was the "Set cell" value for your Goal Seek analysis?

What was the "To value" value for your Goal Seek analysis?

What was the "By changing cell" value for your Goal Seek analysis?

Create a one-way data table for profit at different levels of supplier contact in rangeB22:C33.

Ensure that the price in cellC3 is $290 and the advertising budget in cellC5 is $35,000,000 (you may need to manually adjust these values).
Use the values in rangeB23:B33 as the different levels of the supplier contract.
Reference the profit calculation in cellF12.
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 the profitability.

Create a two-way data table for profit at different levels of price and advertising budget in rangeB36:E57.

Use the values in rangeB37:B57 as the values for Price.
Use the values in rangeC36:E36 as the values for Advertising Budget.
Reference the profit calculation in cellF12.
Make sure you highlight cellsB36:E57 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).

Consider the one-way data table. What is the optimal supplier contract?

Consider the two-way data table. How much should the company spend on advertising?

Consider the two-way data table. Where should the company set the price?

image text in transcribed

B D E F G H I J L M N o Q R 1 2 3 4 5 Decisions to be Made Price Supplier Contract Advertising Budget $200 $75,000,000 $35,000,000 Market Information Competitor Pricing Base Demand Market Size $200 1,000,000 5,000,000 6 7 8 9 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $199 $26 $173 Profitability Total Revenue (-) Total Variable Costs (-) Total Fixed Costs $840,000,000 $725,542,281 $110,000,000 10 11 12 13 14 Total Profit $4,457,719 Demand Calculations Price Demand Factor Total Demand 0% 4200000 Inputs Price (C3) Supplier Contract (C4) Advertising (C5) Decision Set 1 $200 $50,000,000 $25,000,000 Decision Set 2 $250 $75,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 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Supplier Contract and Profitability $1 $1 Supplier Contract and Profitability Supplier Contract $0 $10,000,000 $20,000,000 $30,000,000 $40,000,000 $50,000,000 $60 non non Profit $1 $1 $0 $0 $0 B D E F G H 1 J K L M N Supplier Contract and Profitability $1 $1 Profit $1 $1 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 $0 $0 $0 $o $20,000,000 $50,000,000 000'00009$ $70,000,000 000'000'08$ 000'000'06$ $10,000,000 $100,000,000 $30,000,000 $40,000,000 Supplier Contract Level 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 Advertising Budget $50,000,000 Price, Advertising, and Profitability $25,000,000 $75,000,000 $1 $1 $1 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 Total Profit -$25,000,000 $0 $0 Price $200 $220$240$260 $280$300 $320$340 $360 $380 $400 Price A B D F G K L M N $90,000,000 $100,000,000 Supplier Contract Level 32 33 34 35 36 37 38 39 Advertising Budget $50,000,000 Price, Advertising, and Profitability $25,000,000 $75,000,000 $1 $1 40 41 Total Profit 42 -$25,000,000 43 44 $0 45 46 $0 $200 $220$240 $260$280 $300 $320$340 $360 $380 $400 Price 47 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 Price 48 49 50 51 52 53 54 55 56 57 58 59 60 B D E F G H I J L M N o Q R 1 2 3 4 5 Decisions to be Made Price Supplier Contract Advertising Budget $200 $75,000,000 $35,000,000 Market Information Competitor Pricing Base Demand Market Size $200 1,000,000 5,000,000 6 7 8 9 Variable Cost Calculations Variable Production Costs Prepaid Discount Amount Adjusted Variable Costs $199 $26 $173 Profitability Total Revenue (-) Total Variable Costs (-) Total Fixed Costs $840,000,000 $725,542,281 $110,000,000 10 11 12 13 14 Total Profit $4,457,719 Demand Calculations Price Demand Factor Total Demand 0% 4200000 Inputs Price (C3) Supplier Contract (C4) Advertising (C5) Decision Set 1 $200 $50,000,000 $25,000,000 Decision Set 2 $250 $75,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 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Supplier Contract and Profitability $1 $1 Supplier Contract and Profitability Supplier Contract $0 $10,000,000 $20,000,000 $30,000,000 $40,000,000 $50,000,000 $60 non non Profit $1 $1 $0 $0 $0 B D E F G H 1 J K L M N Supplier Contract and Profitability $1 $1 Profit $1 $1 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 $0 $0 $0 $o $20,000,000 $50,000,000 000'00009$ $70,000,000 000'000'08$ 000'000'06$ $10,000,000 $100,000,000 $30,000,000 $40,000,000 Supplier Contract Level 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 Advertising Budget $50,000,000 Price, Advertising, and Profitability $25,000,000 $75,000,000 $1 $1 $1 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 Total Profit -$25,000,000 $0 $0 Price $200 $220$240$260 $280$300 $320$340 $360 $380 $400 Price A B D F G K L M N $90,000,000 $100,000,000 Supplier Contract Level 32 33 34 35 36 37 38 39 Advertising Budget $50,000,000 Price, Advertising, and Profitability $25,000,000 $75,000,000 $1 $1 40 41 Total Profit 42 -$25,000,000 43 44 $0 45 46 $0 $200 $220$240 $260$280 $300 $320$340 $360 $380 $400 Price 47 $200 $210 $220 $230 $240 $250 $260 $270 $280 $290 $300 $310 $320 $330 $340 $350 $360 $370 $380 $390 $400 Price 48 49 50 51 52 53 54 55 56 57 58 59 60