Download the Applying Excel form below. Follow the tutorial on the first tab and then complete the questions using Excel tools and formulas on the second tab. Use the Data Table What-lf Analysis Tool in Excel to calculate the answers for Scenario 1 and Scenario 2. Hint: Don't forget that the warehouse can only hold up to 50,000 units. For Scenario 1, create a two-variable data table to find the number of Units and Operating Income (Loss) for each product in Scenario 1. For Scenario 2, create a one-variable data table to find the number of Units and Operating Income (Loss) for each product in Scenario 2. Check your worksheet setup and data table results. For Scenario 1, you should have calculated 45,000 units for Product A. For Scenario 2, you should have calculated a final Total Operating Income (Loss) of $329,000 for all three products. If you did not get these answers, redo the Data Table What-lf Analysis for both scenarios by referencing the given information table formulas. For Scenario 1, reference the Total Operating Income (Loss) and for Scenario 2, reference the Product B Operating Income (Loss). Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse". Next click "Save". You will use this worksheet to answer questions in Part 2. On the Data Tables - Student tab in your Excel spreadsheet, update the given information section of the Income Statement for Hello Company with the data below: Max Capacity 52,000 Income Statement Units Price per unit Variable expense per unit Total Fixed Costs Product A 42,000 $ 11.00 $ 4.00 $20,000 Product B 7,000 $ 8.00 $ 2.00 $40,000 Product C 3,000 $ 60.00 $ 20.00 $10,000 Required: 2. Using the Data Table What-lf Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product based on the following scenarios. (Hint: Don't forget that the warehouse can only hold up to 52,000 units.) Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating Income, perform this analysis using a two variable data table. Product A can vary between 39,000 units and a maximum of 44,000 units. Product B can vary between 5,000 units and a maximum of 10,000 units. Both Products A and B are manufactured in 1,000-unit increments. The production level of Product C is the same each month at 3,000 units. Units Operating Income (Loss) SCENARIO 1 Product A Product B Product C 0 S 0 Scenario 2: Pete wants each product line in the mix to be profitable. Use a One Variable Data Table and then determine the number of units for each product that should be produced to the nearest thousand) to make each product line profitable. SCENARIO 2 Units Operating Income (Loss) Product A Product B Product C 0 S 0 3. Based on your calculations above, which scenario creates the highest overall Operating Income for the company? O Scenario 1 O Scenario 2 Download the Applying Excel form below. Follow the tutorial on the first tab and then complete the questions using Excel tools and formulas on the second tab. Use the Data Table What-lf Analysis Tool in Excel to calculate the answers for Scenario 1 and Scenario 2. Hint: Don't forget that the warehouse can only hold up to 50,000 units. For Scenario 1, create a two-variable data table to find the number of Units and Operating Income (Loss) for each product in Scenario 1. For Scenario 2, create a one-variable data table to find the number of Units and Operating Income (Loss) for each product in Scenario 2. Check your worksheet setup and data table results. For Scenario 1, you should have calculated 45,000 units for Product A. For Scenario 2, you should have calculated a final Total Operating Income (Loss) of $329,000 for all three products. If you did not get these answers, redo the Data Table What-lf Analysis for both scenarios by referencing the given information table formulas. For Scenario 1, reference the Total Operating Income (Loss) and for Scenario 2, reference the Product B Operating Income (Loss). Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse". Next click "Save". You will use this worksheet to answer questions in Part 2. On the Data Tables - Student tab in your Excel spreadsheet, update the given information section of the Income Statement for Hello Company with the data below: Max Capacity 52,000 Income Statement Units Price per unit Variable expense per unit Total Fixed Costs Product A 42,000 $ 11.00 $ 4.00 $20,000 Product B 7,000 $ 8.00 $ 2.00 $40,000 Product C 3,000 $ 60.00 $ 20.00 $10,000 Required: 2. Using the Data Table What-lf Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product based on the following scenarios. (Hint: Don't forget that the warehouse can only hold up to 52,000 units.) Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating Income, perform this analysis using a two variable data table. Product A can vary between 39,000 units and a maximum of 44,000 units. Product B can vary between 5,000 units and a maximum of 10,000 units. Both Products A and B are manufactured in 1,000-unit increments. The production level of Product C is the same each month at 3,000 units. Units Operating Income (Loss) SCENARIO 1 Product A Product B Product C 0 S 0 Scenario 2: Pete wants each product line in the mix to be profitable. Use a One Variable Data Table and then determine the number of units for each product that should be produced to the nearest thousand) to make each product line profitable. SCENARIO 2 Units Operating Income (Loss) Product A Product B Product C 0 S 0 3. Based on your calculations above, which scenario creates the highest overall Operating Income for the company? O Scenario 1 O Scenario 2
