Image is clear, just zoom in a little
Please help with formulas
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 5000 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 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. Task # Points Task Description 1 15 Use the scenario manager to input each of the four decision sets depicted in the picture near cell E16. Create the summary of the scenarios. Enter the name of the most profitable scenario in cell D16 using the drop-down box in the cell. 2 10 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 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. 3 10 Create a one-way data table using the values in cells B26:B37 and referencing total profit (calculated in cell F12) to determine the price that leads to the most profit given that rebate is $10 and advertising budget is $10,000. Enter this price in cell C39. Notice the "Price and Profitability" chart updates with the values in the data table to visually demonstrate the relationship between supplier contract amounts and profitability. 4 8 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. 5 2 Complete the "Advertising, Rebate, and Profitability" chart to include series for advertising budgets $10,000 and $20,000 on your data table (notice that the series for $0 advertising budget is already on the chart). 6 5 Enter the advertising budget amount from your analysis that will produce the most profit in cell E65. Enter the rebate that will product the most profit in cell E66. Total: 50 FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Assignment Instruction Task Sheet Guide Show Instructions 68 Submit View Report Automatic Scoring . E16 Xfc B D E G K M N o P R S T U V w 1 2 Price, Advertising, and Rebates for QuickStove Combination Kits 3 Decisions to be Made Price Rebate Advertising Budget Market Information Competitor Pricing Base Demand Market Size $30.00 $10.00 $5,000 4 $30.00 2,575 5,050 5 6 7 8 9 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 $119,340 $43,639 $5,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 5000 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 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. 10 11 12 Total Profit $70,701 13 Demand Calculations Demand Factor Total Demand 57% 3978 14 15 16 17 Which Scenario is most profitable? 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 18 19 20 21 Goal Seek Parameters and Results Set Cell To Value By Changing Cell 22 Price and Profitability Resulting Budget? 23 24 $1.20 25 $1.00 26 27 28 29 30 $0.80 Price and profitability Price $1 $5 $10 $15 $20 $25 $30 Profit $0.60 31 $0.40 32 33 Receipt Pricing READY FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Assignment Sign in Instruction Task Sheet Guide Show Instructions Submit View Report Automatic Scoring E16 C A B D E F G - J K L M N O R S T V w 34 35 $0.20 $35 $40 $45 $50 36 $0.00 37 38 $1 $5 $10 $15 $20 $25 $30 $35 $40 $45 $50 Price Optimal Supplier Contract? 39 40 41 42 Advertising Budget $10,000.00 $0.00 $20,000.00 43 Advertising, Rebate, and Profitability 44 $1.20 45 $1.00 46 47 $0.80 48 $0.60 49 $0.40 50 $0.20 $0.00 51 52 53 54 55 56 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 $2.00 $3.00 $4.00 $5.00 $6.00 $7.00 $8.00 006$ $12.00 8 $10.00 $14.00 16.00 $18.00 $20.00 $22.00 $24.00 $26.00 $28.00 $30.00 $0.00 57 58 59 60 61 62 63 64 65 66 How much should they spend on advertising? How large of a rebate should they offer? Receipt Pricing + 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 5000 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 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. Task # Points Task Description 1 15 Use the scenario manager to input each of the four decision sets depicted in the picture near cell E16. Create the summary of the scenarios. Enter the name of the most profitable scenario in cell D16 using the drop-down box in the cell. 2 10 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 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. 3 10 Create a one-way data table using the values in cells B26:B37 and referencing total profit (calculated in cell F12) to determine the price that leads to the most profit given that rebate is $10 and advertising budget is $10,000. Enter this price in cell C39. Notice the "Price and Profitability" chart updates with the values in the data table to visually demonstrate the relationship between supplier contract amounts and profitability. 4 8 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. 5 2 Complete the "Advertising, Rebate, and Profitability" chart to include series for advertising budgets $10,000 and $20,000 on your data table (notice that the series for $0 advertising budget is already on the chart). 6 5 Enter the advertising budget amount from your analysis that will produce the most profit in cell E65. Enter the rebate that will product the most profit in cell E66. Total: 50 FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Assignment Instruction Task Sheet Guide Show Instructions 68 Submit View Report Automatic Scoring . E16 Xfc B D E G K M N o P R S T U V w 1 2 Price, Advertising, and Rebates for QuickStove Combination Kits 3 Decisions to be Made Price Rebate Advertising Budget Market Information Competitor Pricing Base Demand Market Size $30.00 $10.00 $5,000 4 $30.00 2,575 5,050 5 6 7 8 9 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 $119,340 $43,639 $5,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 5000 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 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. 10 11 12 Total Profit $70,701 13 Demand Calculations Demand Factor Total Demand 57% 3978 14 15 16 17 Which Scenario is most profitable? 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 18 19 20 21 Goal Seek Parameters and Results Set Cell To Value By Changing Cell 22 Price and Profitability Resulting Budget? 23 24 $1.20 25 $1.00 26 27 28 29 30 $0.80 Price and profitability Price $1 $5 $10 $15 $20 $25 $30 Profit $0.60 31 $0.40 32 33 Receipt Pricing READY FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Assignment Sign in Instruction Task Sheet Guide Show Instructions Submit View Report Automatic Scoring E16 C A B D E F G - J K L M N O R S T V w 34 35 $0.20 $35 $40 $45 $50 36 $0.00 37 38 $1 $5 $10 $15 $20 $25 $30 $35 $40 $45 $50 Price Optimal Supplier Contract? 39 40 41 42 Advertising Budget $10,000.00 $0.00 $20,000.00 43 Advertising, Rebate, and Profitability 44 $1.20 45 $1.00 46 47 $0.80 48 $0.60 49 $0.40 50 $0.20 $0.00 51 52 53 54 55 56 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 $2.00 $3.00 $4.00 $5.00 $6.00 $7.00 $8.00 006$ $12.00 8 $10.00 $14.00 16.00 $18.00 $20.00 $22.00 $24.00 $26.00 $28.00 $30.00 $0.00 57 58 59 60 61 62 63 64 65 66 How much should they spend on advertising? How large of a rebate should they offer? Receipt Pricing +
