Share Comment Clear C Group HA Sort Filter File Home Insert Draw Page Layout Formulas Data Review View Help Assignment signment Tasks 21 AM Pricing Create a goal seek analysis to determine what the advertising budget should be to generate market size of 5100 kits given that the price is $30 and the rebate is $10 (you may need to 2 change these values). Complete the analysis using Goal Seek. Enter the "Set cell", "To value", and "By changing cell" elements of your analysis in cells c19, 220, C21, respectively. Enter the resulting price in cell C23. [10 points] VaReapply Advanred Text to Ungroup Subtotal Columns ES What If Forecast Analysis Sheet Forecast Sart & Filter Data Tools Outline H K M 1 2 Price, Advertising, and Rebates for QuickStove Combination Kits Decisions to be Made Price Rebate Advertising Budget $30.00 $10.00 $5,000 Market Information Competitor Pricing Base Demand Market Size $30.000 2,600 5,100 Variable Cost Calculations Variable Production Costs Expected Rebate Redemption Variable Costs w/Rebate $7.64 33% $10.97 Profitability Total Revenue - Total Variable costs (-) Total Fixed Costs $120,5101 $44,068 $5,000 Howard would like to systematically analyze the marketing of the most popular combination kit that QuickSto conducted some market research and believes that the current demand for the kit is 5000 units per year. Quid that offers a similar product that is also priced at $30. QuickStove and their competitor have roughly the same Howard knows that price will impact the relative market share of both fims. He also believes the spending m only increase the market share of QuickStove, but will also have the effect of increasing the overall demand fo Howard is considering offering a rebate on the kit to stimulate sales. All of these factors (price, advertising, an demand for the kits and the variable cost of the kits (increasing production will decrease the variable costs of changes in these variables will each impact profitability. He has created a spreadsheet model on the Pricing w approximates how price, advertising, and rebates will influence profitability. Complete the tasks to help Howa appropriate price, advertising budget, and rebate amount for the kits Demand Calculations Total Profit $71,442 Demand Factor Total Demand 57% 4017 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Which Scenario is most profitable? Decision Set 3 Inputs Price Rebate Advertising Budget Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 $30 $25 $35 $20 $5 $10 $5 $10 $10,000 $5,000 $20,000 $10,000 Goal Seek Parameters and Results Set Cell To Value By Changing Cell Price and Profitability Resulting Budget? $1.20 $ $1.00 $0.80 Price and profitability Price $1 55 $10 $15 $20 $25 $0.60 30 31 32 $0.40 Share Comment Clear C Group HA Sort Filter File Home Insert Draw Page Layout Formulas Data Review View Help Assignment signment Tasks 21 AM Pricing Create a goal seek analysis to determine what the advertising budget should be to generate market size of 5100 kits given that the price is $30 and the rebate is $10 (you may need to 2 change these values). Complete the analysis using Goal Seek. Enter the "Set cell", "To value", and "By changing cell" elements of your analysis in cells c19, 220, C21, respectively. Enter the resulting price in cell C23. [10 points] VaReapply Advanred Text to Ungroup Subtotal Columns ES What If Forecast Analysis Sheet Forecast Sart & Filter Data Tools Outline H K M 1 2 Price, Advertising, and Rebates for QuickStove Combination Kits Decisions to be Made Price Rebate Advertising Budget $30.00 $10.00 $5,000 Market Information Competitor Pricing Base Demand Market Size $30.000 2,600 5,100 Variable Cost Calculations Variable Production Costs Expected Rebate Redemption Variable Costs w/Rebate $7.64 33% $10.97 Profitability Total Revenue - Total Variable costs (-) Total Fixed Costs $120,5101 $44,068 $5,000 Howard would like to systematically analyze the marketing of the most popular combination kit that QuickSto conducted some market research and believes that the current demand for the kit is 5000 units per year. Quid that offers a similar product that is also priced at $30. QuickStove and their competitor have roughly the same Howard knows that price will impact the relative market share of both fims. He also believes the spending m only increase the market share of QuickStove, but will also have the effect of increasing the overall demand fo Howard is considering offering a rebate on the kit to stimulate sales. All of these factors (price, advertising, an demand for the kits and the variable cost of the kits (increasing production will decrease the variable costs of changes in these variables will each impact profitability. He has created a spreadsheet model on the Pricing w approximates how price, advertising, and rebates will influence profitability. Complete the tasks to help Howa appropriate price, advertising budget, and rebate amount for the kits Demand Calculations Total Profit $71,442 Demand Factor Total Demand 57% 4017 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Which Scenario is most profitable? Decision Set 3 Inputs Price Rebate Advertising Budget Decision Set 1 Decision Set 2 Decision Set 3 Decision Set 4 $30 $25 $35 $20 $5 $10 $5 $10 $10,000 $5,000 $20,000 $10,000 Goal Seek Parameters and Results Set Cell To Value By Changing Cell Price and Profitability Resulting Budget? $1.20 $ $1.00 $0.80 Price and profitability Price $1 55 $10 $15 $20 $25 $0.60 30 31 32 $0.40