Create a two-way data table using the values in cells B43:B63 (Rebate) and C42:E42 (Advertising Budget) to analyze the relationships among advertising budget, rebate, and profitability. [8 points]

B26 X V for Price B C D E Decisions to be Made Price $30.00 Rebate $10.00 Advertising Budget $10,000 Market Information Competitor Pricing $30.00 Base Demand 2.600 Market Size 5.100 8 Variable Cost Calculations Variable Production Costs $7.27 Expected Rebate Redempti Variable Costs wiRebate $10.60 Profitability Total Revenue (-) Total Variable Cos (-) Total Fixed Costs Price, Advertising, and Rebates for Quick Stove Combination Kits Howard would like to systematically analyze the marketing of the most popular combination kit that Quick Stove sells for $30. He has conducted some market research and believes that the current demand for the kit is 5000 units per year. Quick Stove has a competitor that offers a similar product that is also priced at $30. Quick Stove and their competitor have roughly the same share of the market. Howard knows that price will impact the relative market share of both firms. He also believes the spending money on advertising will not only increase the market shart of Quick Stove, but will also have the effect of increasing the overall demand for the kit for both firms. Howard is considering offering a rebate on the kit to stimulate sales. All of these factors (price, advertising, and rebates) will impact the demand for the kits and the variable cost of the kits (increasing production will decrease the variable costs of the kits). This means that changes in these variables will each impact profitability. He has created a spreadsheet model on the Pricing worksheet that he believes approximates how price, advertising, and rebates will influence profitability. Complete the tasks to help Howard to determine an appropriate price, advertising budget, and rebate amount for the kits. $139,2601 $49,218 $10,000 9 Total Profit $80,0421 Demand Calculations Demand Factor Total Demand 4642 Which Scenario is most profitable? Decision Set 3 Inputs Price Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 $30 $25 $35 $20 $5 S10 $5 $10 $10,000 $5,000 $20,000 $10,000 Rebate Advertising Budget Goal Seek Parameters and Results Set Cell $F$5 To Value 5100 By Changing Cell $C$5 21 22 Price and profitability Resulting Budget? $10,000.00 200000 100000 $1 $$ $20 $15 $20 $25 $30 $35 $40 $45 $50 $5 $10 -100000 888888888888 Price and Profitability Price $80,042 -550600 -122200 -45700 $15 -3264.8676 32608.6288 $25 60551.4706 80042.0573 $35 91259.7828 94452.4219 898216814 200000 $20 300000 400000 $40 $45 500000 Receipt Scenario Summary Pricing Average: 5010 810232 Co $35 400000 $40 $45 $50 91259.7828 94452.4219 89821.6814 77462.1324 -500000 600000 Create market change value", respect Optimal Supplier Conti $40.00 Advertising, Rebate, and Profitability $090 $0.70 S060 $050 $040 SOBO $020 Rebate $0.00 $1.00 $2.00 $3.00 $4.00 $5.00 $6.00 $7.00 $8.00 $9.00 $10.00 $12.00 $14.00 $16.00 $18.00 $20.00 $22.00 $24.00 $26.00 $28.00 $30,00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0 $0.00 Advertising Budget $10,000.00 $20,000.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 ODES 0055 $6.00 $7.00 $8.00 $34.00 65 How much should they spend on advertising? Hoy large of a rebate should they offer? 66 67 68 Receipt Scenario Summary Pricing B26 X V for Price B C D E Decisions to be Made Price $30.00 Rebate $10.00 Advertising Budget $10,000 Market Information Competitor Pricing $30.00 Base Demand 2.600 Market Size 5.100 8 Variable Cost Calculations Variable Production Costs $7.27 Expected Rebate Redempti Variable Costs wiRebate $10.60 Profitability Total Revenue (-) Total Variable Cos (-) Total Fixed Costs Price, Advertising, and Rebates for Quick Stove Combination Kits Howard would like to systematically analyze the marketing of the most popular combination kit that Quick Stove sells for $30. He has conducted some market research and believes that the current demand for the kit is 5000 units per year. Quick Stove has a competitor that offers a similar product that is also priced at $30. Quick Stove and their competitor have roughly the same share of the market. Howard knows that price will impact the relative market share of both firms. He also believes the spending money on advertising will not only increase the market shart of Quick Stove, but will also have the effect of increasing the overall demand for the kit for both firms. Howard is considering offering a rebate on the kit to stimulate sales. All of these factors (price, advertising, and rebates) will impact the demand for the kits and the variable cost of the kits (increasing production will decrease the variable costs of the kits). This means that changes in these variables will each impact profitability. He has created a spreadsheet model on the Pricing worksheet that he believes approximates how price, advertising, and rebates will influence profitability. Complete the tasks to help Howard to determine an appropriate price, advertising budget, and rebate amount for the kits. $139,2601 $49,218 $10,000 9 Total Profit $80,0421 Demand Calculations Demand Factor Total Demand 4642 Which Scenario is most profitable? Decision Set 3 Inputs Price Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 $30 $25 $35 $20 $5 S10 $5 $10 $10,000 $5,000 $20,000 $10,000 Rebate Advertising Budget Goal Seek Parameters and Results Set Cell $F$5 To Value 5100 By Changing Cell $C$5 21 22 Price and profitability Resulting Budget? $10,000.00 200000 100000 $1 $$ $20 $15 $20 $25 $30 $35 $40 $45 $50 $5 $10 -100000 888888888888 Price and Profitability Price $80,042 -550600 -122200 -45700 $15 -3264.8676 32608.6288 $25 60551.4706 80042.0573 $35 91259.7828 94452.4219 898216814 200000 $20 300000 400000 $40 $45 500000 Receipt Scenario Summary Pricing Average: 5010 810232 Co $35 400000 $40 $45 $50 91259.7828 94452.4219 89821.6814 77462.1324 -500000 600000 Create market change value", respect Optimal Supplier Conti $40.00 Advertising, Rebate, and Profitability $090 $0.70 S060 $050 $040 SOBO $020 Rebate $0.00 $1.00 $2.00 $3.00 $4.00 $5.00 $6.00 $7.00 $8.00 $9.00 $10.00 $12.00 $14.00 $16.00 $18.00 $20.00 $22.00 $24.00 $26.00 $28.00 $30,00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0 $0.00 Advertising Budget $10,000.00 $20,000.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 ODES 0055 $6.00 $7.00 $8.00 $34.00 65 How much should they spend on advertising? Hoy large of a rebate should they offer? 66 67 68 Receipt Scenario Summary Pricing