Assumptions and requirements for the scenario analysis of pricing mixes. (2 points) 1. Open the Worksheet Scenario-Question. In Part I, create a scenario using the data and formula given in the gray area in Scenario Question worksheet. 2. Keep the Scenario setting after you have created it successfully and you have to use the scenario manager to create the required scenario summary (i.e., a separate worksheet) for you. 3. You will create 4 different scenarios by changing the product pricing mix in order to determine their impacts to Total Gross Profit and Total Revenue. The first scenario is the original Pricing Mix with no price change. The Second Scenario is to raise the price of Product A by $5.00 The Third Scenario is to raise the price of Product B by $5.00 The Fourth Scenario is to raise the price of Product C by $5.00 The four pricing mixes are listed in the following: Product Name Pricing Mix 1 $50.00 Pricing Mix 2 $55.00 Pricing Mix 3 $50.00 Pricing Mix 4 $50.00 Product A $45.00 $45.00 $50.00 $45.00 Product B $32.00 $32.00 $32.00 $37.00 Product C Working with the data given in the gray area in Scenario Question worksheet. Create a Scenario summary report as a separate worksheet such as the one below (The actual result cells values were altered to hide their true values, your answers should have different values). (2 points) Make sure the Results Cells include TotalGrossProfit and Totalrevenue Use meaningful labels for the Changing Cells and Results Cells (for example using the label "Product_A_Price" instead of $F$10). The Best Way to do this is to name all the Changing Cells and Results Cells. Circle the product mix scenario that results in the highest TotalGrossProfit and also insert an Oval Callout shape with text inside stating that the circled scenario is The Most Profitable Scenario". [Use Insert > Shape as the following screen snapshot] NingLiu Cin 1 Home Insert Page Layout F Pivot Table Table Picture Clip Shapes Art Recently Used Shapes OOOOOOAZZ >> Lines ZZ 222216 Rectangles ** Open the Worksheet Scenario-Question and scroll down your will find Part II. Please answer the following two questions in the cells provided (1 point) a. How can you determine the most profitable product mix scenario manually (without using Excel or a calculator) just by observing the pattern in the pricing mixes (i.e., price increase has a pattern $5 each for a different product in each scenario) and other relevant numbers for these products? b. Discuss one major flaw in the underlying hidden assumptions used in this scenario analysis. Please use the laws of demand and supply in economics to point out the flaw. A B D E F G H J K L L M N N o Q R S T U 4 5 Oo Max Actual With Scenario Manager==> 2 6 Manual Analysis ====> 1 7 8 2009 2009 2009 9 Revenue Target Unit Sold Unit Cost Unit Price Revenue Costs of Good Sold 10 Product A 3000 $40.00 $50.00 $ 150,000.00 $120,000.00 11 Product B 4500 $30.00 $45.00 $202,500.00 $135,000.00 12 Product C 2300 $25.00 $32.00 $73,600.00 $57,500.00 13 14 Total Revenue $426,100.00 15 Total COGS $312,500.00 16 Total Gross Profit $113,600.00 17 Part II. 18 a. How can you determine the most profitable product mix scenario manually (without Excel or a caculator)? 19 b. Discuss one of the major flaws in the underlying hidden assumption used in this scenario analysis 20 Scroll down 21 Write your answers below (I provide hints in the problem set for these two questions) 22 23 A. I can figure out that ". 24 scenario" is the most profitable scenario because Please complete the sentence ... Please complete the sentence 25 B. The major flaw in the assumption used in the scenario analysis is that 26 27 28 29 30. Assumptions and requirements for the scenario analysis of pricing mixes. (2 points) 1. Open the Worksheet Scenario-Question. In Part I, create a scenario using the data and formula given in the gray area in Scenario Question worksheet. 2. Keep the Scenario setting after you have created it successfully and you have to use the scenario manager to create the required scenario summary (i.e., a separate worksheet) for you. 3. You will create 4 different scenarios by changing the product pricing mix in order to determine their impacts to Total Gross Profit and Total Revenue. The first scenario is the original Pricing Mix with no price change. The Second Scenario is to raise the price of Product A by $5.00 The Third Scenario is to raise the price of Product B by $5.00 The Fourth Scenario is to raise the price of Product C by $5.00 The four pricing mixes are listed in the following: Product Name Pricing Mix 1 $50.00 Pricing Mix 2 $55.00 Pricing Mix 3 $50.00 Pricing Mix 4 $50.00 Product A $45.00 $45.00 $50.00 $45.00 Product B $32.00 $32.00 $32.00 $37.00 Product C Working with the data given in the gray area in Scenario Question worksheet. Create a Scenario summary report as a separate worksheet such as the one below (The actual result cells values were altered to hide their true values, your answers should have different values). (2 points) Make sure the Results Cells include TotalGrossProfit and Totalrevenue Use meaningful labels for the Changing Cells and Results Cells (for example using the label "Product_A_Price" instead of $F$10). The Best Way to do this is to name all the Changing Cells and Results Cells. Circle the product mix scenario that results in the highest TotalGrossProfit and also insert an Oval Callout shape with text inside stating that the circled scenario is The Most Profitable Scenario". [Use Insert > Shape as the following screen snapshot] NingLiu Cin 1 Home Insert Page Layout F Pivot Table Table Picture Clip Shapes Art Recently Used Shapes OOOOOOAZZ >> Lines ZZ 222216 Rectangles ** Open the Worksheet Scenario-Question and scroll down your will find Part II. Please answer the following two questions in the cells provided (1 point) a. How can you determine the most profitable product mix scenario manually (without using Excel or a calculator) just by observing the pattern in the pricing mixes (i.e., price increase has a pattern $5 each for a different product in each scenario) and other relevant numbers for these products? b. Discuss one major flaw in the underlying hidden assumptions used in this scenario analysis. Please use the laws of demand and supply in economics to point out the flaw. A B D E F G H J K L L M N N o Q R S T U 4 5 Oo Max Actual With Scenario Manager==> 2 6 Manual Analysis ====> 1 7 8 2009 2009 2009 9 Revenue Target Unit Sold Unit Cost Unit Price Revenue Costs of Good Sold 10 Product A 3000 $40.00 $50.00 $ 150,000.00 $120,000.00 11 Product B 4500 $30.00 $45.00 $202,500.00 $135,000.00 12 Product C 2300 $25.00 $32.00 $73,600.00 $57,500.00 13 14 Total Revenue $426,100.00 15 Total COGS $312,500.00 16 Total Gross Profit $113,600.00 17 Part II. 18 a. How can you determine the most profitable product mix scenario manually (without Excel or a caculator)? 19 b. Discuss one of the major flaws in the underlying hidden assumption used in this scenario analysis 20 Scroll down 21 Write your answers below (I provide hints in the problem set for these two questions) 22 23 A. I can figure out that ". 24 scenario" is the most profitable scenario because Please complete the sentence ... Please complete the sentence 25 B. The major flaw in the assumption used in the scenario analysis is that 26 27 28 29 30