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: Income Statement Product A Product B Product C Max Capacity Units 42,000 7,000 3,000 52,000 Price per unit $ 11.00 $ 8.00 $ 60.00 Variable expense per unit $ 4.00 $ 2.00 $ 20.00 Total Fixed Costs $ 20,000 $ 40,000 $ 10,000 Required: 2. Using the Data Table What-If 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.
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:
| Income Statement | Product A | Product B | Product C | Max Capacity | |||||||||||
| Units | 42,000 | 7,000 | 3,000 | 52,000 | |||||||||||
| Price per unit | $ | 11.00 | $ | 8.00 | $ | 60.00 | |||||||||
| Variable expense per unit | $ | 4.00 | $ | 2.00 | $ | 20.00 | |||||||||
| Total Fixed Costs | $ | 20,000 | $ | 40,000 | $ | 10,000 | |||||||||
Required:
2. Using the Data Table What-If 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.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
