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:

$\backslash$table$[[,,,,$Max$],[$Income Statement,Product A$,$Product B$,$Product C$,\backslash$table$[[$Capacity$]]],[$Units$,40,000,6,000,2,000,48,000],[$Price per unit,$$12\mathrm{.}00,$$$8\mathrm{.}00,$$$50\mathrm{.}00,],[$Variable expense per unit,$$3\mathrm{.}00,$$$2\mathrm{.}00,$$$15\mathrm{.}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 $48,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 $37,000$ units and a maximum of $42,000$ units. Product B can vary between $4,000$ units and a maximum of $9,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.

$\backslash$table$[[$SCENARIO $1,$Units,$\backslash$table$[[$Operating$],[$Income $($Loss$)]]],[$Product A$,,],[$Product B$,,],[$Product C$,,],[,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.

Prev

$2$ of $8$

NextOp