Question
answer all parts Income Statement Product A Product B Product C Max Capacity Units 42,000 7,000 2,000 51,000 Price per unit $ 12.00 $ 8.00
answer all parts
| Income Statement | Product A | Product B | Product C | Max Capacity | |||||||||||
| Units | 42,000 | 7,000 | 2,000 | 51,000 | |||||||||||
| Price per unit | $ | 12.00 | $ | 8.00 | $ | 50.00 | |||||||||
| Variable expense per unit | $ | 4.00 | $ | 2.00 | $ | 15.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 51,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 2,000 units.
Item2
Part 2 of 2
50points
ItemSkipped
eBookReferences
Check my workCheck My Work button is now enabled1
Item 2
Required information
Required information
Applying Excel - Data Tables
Skip to question
[The following information applies to the questions displayed below.]
This Applying Excel worksheet includes an explanation of the Data Table What-If Analysis Tool.
rev: 05_02_2019_QC_CS-167686
Applying Excel - Data Tables: Exercise (Part 2 of 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:
| Income Statement | Product A | Product B | Product C | Max Capacity | |||||||||||
| Units | 42,000 | 7,000 | 2,000 | 51,000 | |||||||||||
| Price per unit | $ | 12.00 | $ | 8.00 | $ | 50.00 | |||||||||
| Variable expense per unit | $ | 4.00 | $ | 2.00 | $ | 15.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 51,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 2,000 units.
Item2
Part 2 of 2
50points
ItemSkipped
eBookReferences
Check my workCheck My Work button is now enabled1
Item 2
Required information
Required information
Applying Excel - Data Tables
Skip to question
[The following information applies to the questions displayed below.]
This Applying Excel worksheet includes an explanation of the Data Table What-If Analysis Tool.
rev: 05_02_2019_QC_CS-167686
Applying Excel - Data Tables: Exercise (Part 2 of 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:
| Income Statement | Product A | Product B | Product C | Max Capacity | |||||||||||
| Units | 42,000 | 7,000 | 2,000 | 51,000 | |||||||||||
| Price per unit | $ | 12.00 | $ | 8.00 | $ | 50.00 | |||||||||
| Variable expense per unit | $ | 4.00 | $ | 2.00 | $ | 15.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 51,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 2,000 units.
Proudct A- Units= OPerating income/lost
Product B- Units= OI/Loss
Product C- Units- OI/Loss
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.
Proudct A- Units= OPerating income/lost
Product B- Units= OI/Loss
Product C- Units- OI/Loss
Based on your calculations above, which scenario creates the highest overall Operating Income for the company?
multiple choice
-
Scenario 1
-
Scenario 2
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Public Finance And Public Policy
Authors: Jonathan Gruber
6th Edition
1319105254, 9781319105259
