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]

Prke, Advertising, and Rebates for QuickStove Combination Kits Decisions to be Made | Price Robata Advertising Budget $30.00 $10.00 $10,000 Market Information Competitor Pricing Base Demand Market Size $30.00 2,600 5,100 Variable Cast Calculations Variable Production Costs Expected Rebate Redemption Variable Casts w/Rebate $7.27 33% $10.60 Profitability Total Revenue (- Total Variable Costs (-) Total Fixed Costs $139,260 $49,218 $10,000 Howard would like to systematically analyze the marketing of the most popular combination kit that QuickStove sells for $30. He has conducted sorne market research and believes that the current demand for the kit is 5000 units per year. QuickStove has a competitor that offers a similar product that is ako priced at $30. QuickStave and their competitor have roughly the same share of the market. Howard knows that price will mpact the relative market share of both firms. He also beleves the spending money on advertising will not only increase the market shart of QuickStove, but will also have the effect of increasing the overall demand for the kit for both firms. Howard is considerine 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 wil 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. Demand Calculations Total Profe $80,042 825 Demand Factor Total Demand 4642 which Scenario is most profitable Decision Set 3 Inputs Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 Price $30 $25 $35 $20 Rebate $5 $10 $5 $10 Advertising Budget $10,000 $5,000 $20,000 $10,000 Goal Seek Parameters and Results Set Cell $F$5 To Value 5100 By Chareine Cell $C$s Price and Profitability Resulting Budget? $10,000.00 $200 Donna $100,000.00 $0.00 $1 $S $10 $15 $20 $25 $30 $35 $45 $50 $100,000.00 $200,000.00 Price and profitability Price $80,042.06 $1 $550,600.00 $5 $122,200.00 $10 $45,700.00 $15 -$3,264.87 $20 $32,608.63 $25 $60,551.47 $30 $80,042.06 $35 $91,259.78 $40 $94,452.42 $45 $89,821.68 $50 $77,462.13 -$300,00.00 -S400,000.00 $500,000.00 -$600.000.00 Price Optimal Supplier Contract? $40.00 $50 $77,462.13 -5600,000.00 Price Optimal Supplier Contract? $40.00 $0.00 Advertising Budget $10,000.00 $20,000.00 Advertising, Rebate, and Profitability $1.20 S1 $0.00 $2.50 $0.00 $0.20 $0.00 $1.00 $2.00 $3.00 $4.00 55.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 $100 39.00 Sao 57.00 $16.00 450.00 SW.00 $5.00 Assignment Tasks Pricing Create a two-way data table using the values in cells B43:363 (Rebate) and C42:E42 (Advertising Budget) to analyze the relationships among advertising budget, rebate, and profitability. (8 points) 4 How much should they spend on advertising? How large of a rebate should they offer? Prke, Advertising, and Rebates for QuickStove Combination Kits Decisions to be Made | Price Robata Advertising Budget $30.00 $10.00 $10,000 Market Information Competitor Pricing Base Demand Market Size $30.00 2,600 5,100 Variable Cast Calculations Variable Production Costs Expected Rebate Redemption Variable Casts w/Rebate $7.27 33% $10.60 Profitability Total Revenue (- Total Variable Costs (-) Total Fixed Costs $139,260 $49,218 $10,000 Howard would like to systematically analyze the marketing of the most popular combination kit that QuickStove sells for $30. He has conducted sorne market research and believes that the current demand for the kit is 5000 units per year. QuickStove has a competitor that offers a similar product that is ako priced at $30. QuickStave and their competitor have roughly the same share of the market. Howard knows that price will mpact the relative market share of both firms. He also beleves the spending money on advertising will not only increase the market shart of QuickStove, but will also have the effect of increasing the overall demand for the kit for both firms. Howard is considerine 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 wil 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. Demand Calculations Total Profe $80,042 825 Demand Factor Total Demand 4642 which Scenario is most profitable Decision Set 3 Inputs Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 Price $30 $25 $35 $20 Rebate $5 $10 $5 $10 Advertising Budget $10,000 $5,000 $20,000 $10,000 Goal Seek Parameters and Results Set Cell $F$5 To Value 5100 By Chareine Cell $C$s Price and Profitability Resulting Budget? $10,000.00 $200 Donna $100,000.00 $0.00 $1 $S $10 $15 $20 $25 $30 $35 $45 $50 $100,000.00 $200,000.00 Price and profitability Price $80,042.06 $1 $550,600.00 $5 $122,200.00 $10 $45,700.00 $15 -$3,264.87 $20 $32,608.63 $25 $60,551.47 $30 $80,042.06 $35 $91,259.78 $40 $94,452.42 $45 $89,821.68 $50 $77,462.13 -$300,00.00 -S400,000.00 $500,000.00 -$600.000.00 Price Optimal Supplier Contract? $40.00 $50 $77,462.13 -5600,000.00 Price Optimal Supplier Contract? $40.00 $0.00 Advertising Budget $10,000.00 $20,000.00 Advertising, Rebate, and Profitability $1.20 S1 $0.00 $2.50 $0.00 $0.20 $0.00 $1.00 $2.00 $3.00 $4.00 55.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 $100 39.00 Sao 57.00 $16.00 450.00 SW.00 $5.00 Assignment Tasks Pricing Create a two-way data table using the values in cells B43:363 (Rebate) and C42:E42 (Advertising Budget) to analyze the relationships among advertising budget, rebate, and profitability. (8 points) 4 How much should they spend on advertising? How large of a rebate should they offer