"Create a two-way data table using the values in cells B43:B63 (Rebate) and C42:E42 (Advertising Budget) to analyze the relationship among advertising budget, rebate, and probability."

. M 1 2 Price, Advertising and Rebates for Quid Stove Combination Kits 3 Decisions to be Made Price Rebate Advertising Budget Market Information Competitor Priding Base Demand Market Size $30.00 $ $10.00 $10.000 4 5 $30.00 2,600 5.100 6 7 8 9 10 Variable Cost Calculations Variable Production Costs Expected Rebate Redemption Variable costs 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 some market research and believes that the current demand for the kit is 500 units per year. QuickStove has a competitor that offers a similar product that is also priced at $30. QuickStove 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 QuickStove, 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 Priding worksheet that he believes approdates 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 11 Demand Calculations Total Profit $80,0421 Demand Factor Total Demand Which Scenario is most profitable? Decision Set 3 Inputs Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 Price $30 $25 535 $20 Rebate $5 $10 SS $10 Advertising Budget $10,000 $5,000 $20.000 $10,000 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Goal Seek Parameters and Results Set Cell $$s To Value 5100 By Changing Cell $C$5 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 Price and profitability Resulting Budget? $10,000.00 $200,000.00 $100,000.00 SODO $1 1 $S $10 $15 $20 $25 $30 $35 $40 $45 $50 28 29 $100poo.com Price and profitability Prics $80,042.06 $1 -$550,600.00 $s $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 $200,000.00 Vertical (Value) Axis 30 31 32 33 34 35 36 37 $300 po.co $ $400.000.C 400 $500 po.co $600.000.CO Price Optimal Supplier Contract? $40.00 38 39 40 41 42 43 Advertising, Rebate, and Profitability 44 45 46 47 48 49 SO $0.00 $1.00 $2.00 $3.00 $4.00 $5.00 $6.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Advertising Budget $10,000.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 to $20,000.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.90 $0.80 $0.20 $ $0.80 $0.90 $0.40 $ 90.20 51 $7.00 $8.00 cam . M 1 2 Price, Advertising and Rebates for Quid Stove Combination Kits 3 Decisions to be Made Price Rebate Advertising Budget Market Information Competitor Priding Base Demand Market Size $30.00 $ $10.00 $10.000 4 5 $30.00 2,600 5.100 6 7 8 9 10 Variable Cost Calculations Variable Production Costs Expected Rebate Redemption Variable costs 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 some market research and believes that the current demand for the kit is 500 units per year. QuickStove has a competitor that offers a similar product that is also priced at $30. QuickStove 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 QuickStove, 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 Priding worksheet that he believes approdates 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 11 Demand Calculations Total Profit $80,0421 Demand Factor Total Demand Which Scenario is most profitable? Decision Set 3 Inputs Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 Price $30 $25 535 $20 Rebate $5 $10 SS $10 Advertising Budget $10,000 $5,000 $20.000 $10,000 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Goal Seek Parameters and Results Set Cell $$s To Value 5100 By Changing Cell $C$5 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 Price and profitability Resulting Budget? $10,000.00 $200,000.00 $100,000.00 SODO $1 1 $S $10 $15 $20 $25 $30 $35 $40 $45 $50 28 29 $100poo.com Price and profitability Prics $80,042.06 $1 -$550,600.00 $s $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 $200,000.00 Vertical (Value) Axis 30 31 32 33 34 35 36 37 $300 po.co $ $400.000.C 400 $500 po.co $600.000.CO Price Optimal Supplier Contract? $40.00 38 39 40 41 42 43 Advertising, Rebate, and Profitability 44 45 46 47 48 49 SO $0.00 $1.00 $2.00 $3.00 $4.00 $5.00 $6.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Advertising Budget $10,000.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 to $20,000.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.90 $0.80 $0.20 $ $0.80 $0.90 $0.40 $ 90.20 51 $7.00 $8.00 cam