4. Assumptions and requirements for the scenario analysis of pricing mixes. ( 4 points) 1. Open the Worksheet "4. 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: Working with the data given in the gray area in Scenario Question worksheet. Create a Scenario summary report as a separate worksheet. (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 Aspricen" 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 > Shap as the following screen snapshot] ** Open the Worksheet "4. Scenario-Question" and scroll down your will find Part II. Please answer the following two questions in the cells provided ( 2 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. Part I. Analyzing the Product Pricing Mix with Scenario Manager and Part II. without any tools Part I. => Set up Scenario Manager in this worksheet and use the data provided in this worksheet via Data > What-If Analysis > Scenario Manager Please perform the scenario analysis based on the data below (in the gray area) and based on the pricing mixes assumption given in assignment 3 instruction! a. How can you determine the most profitable product mix scenario manually (without Excel or a caculator)? b. Discuss one of the major flaws in the underlying hidden assumption used in this scenario analysis Scroll down Write your answers below (I provide hints in the problem set for these two questions) 4. Assumptions and requirements for the scenario analysis of pricing mixes. ( 4 points) 1. Open the Worksheet "4. 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: Working with the data given in the gray area in Scenario Question worksheet. Create a Scenario summary report as a separate worksheet. (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 Aspricen" 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 > Shap as the following screen snapshot] ** Open the Worksheet "4. Scenario-Question" and scroll down your will find Part II. Please answer the following two questions in the cells provided ( 2 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. Part I. Analyzing the Product Pricing Mix with Scenario Manager and Part II. without any tools Part I. => Set up Scenario Manager in this worksheet and use the data provided in this worksheet via Data > What-If Analysis > Scenario Manager Please perform the scenario analysis based on the data below (in the gray area) and based on the pricing mixes assumption given in assignment 3 instruction! a. How can you determine the most profitable product mix scenario manually (without Excel or a caculator)? b. Discuss one of the major flaws in the underlying hidden assumption used in this scenario analysis Scroll down Write your answers below (I provide hints in the problem set for these two questions)
