Assignment Information:

The following information will be used for this assignment:

You are currently trying to decide between two cost structures for each of three independent product lines within your business. Each pair of alternatives includes one that has a greater proportion of short$-$term fixed costs and another that is more heavily weighted to variable costs. Estimated revenue and cost data for each alternative are as follows:

Alpha Cost Structure Beta Cost Structure Gamma Cost Structure

Option #$1$ Option #$2$ Option #$1$ Option #$2$ Option #$1$ Option #$2$

Selling price per unit $ $80$ $ $80$ $ $30$ $ $30$ $ $160$ $ $160$

Variable cost per unit $ $60$ $ $55$ $ $22$ $ $20$ $ $115$ $ $110$

Short$-$term fixed costs per year $ $32,000$ $ $36,000$ $ $12,000$ $ $14,000$ $ $92,000$ $ $98,000$

You have been asked what sales volume, in units, is needed for the total costs in each product line$$s cost$-$structure alternative to be the same. You have also been asked, assuming that your profit goal $($operating profit $-$: sales$)$ for the coming year is $4\%$ of sales, what sales level, in both units and dollars, is needed under each alternative to achieve this goal.

Assignment Steps:

Part $1$

Begin the assignment by downloading the necessary financial data: Cost$\_$Structure$\_$Problem.xlsx

To save the file choose File $>$ Save As $>$ Browse, and select the Documents folder. If necessary, create a new folder entitled $$Tableau Assignments$$ and save the file.

Within Tableau select the Microsoft Excel option within the Connect pane, navigate to the Excel data file that you just downloaded, and choose Open.

Choose Save As from the File menu, type an appropriate name for the file, and click Save.

Click the Sheet $1$ tab.

Choose the Analysis tab and select Create Calculated Field. Entitle the new field $$Sales Volume Required for Equal Cost Structure$,$ and within the blank calculation space below enter the formula $($Annual Short$-$Term Fixed Costs: Option $2$ Annual Short$-$Term Fixed Costs: Option $1)-$: $($Variable Cost per Unit: Option $1$ Variable Cost per Unit: Option $2)$ and click OK$.$

Drag and drop Product Line onto the Columns shelf.

Drag and drop Sales Volume Required for Equal Cost Structure onto the Rows shelf.

Click the Swap Rows and Columns button.

Drag and drop Sales Volume Required for Equal Cost Structure onto the Color mark.

Hold cursor over the x$-$axis title and click the Sorted descending by sum of Sales Volume Required for Equal Cost Structure within Product Line button that appears at the far right.

Click the drop$-$down arrow at the top$-$right of the Legend card for SUM$($Sales Volume Required for Equal Cost Structure$)$ and choose Hide Card.

Double$-$click the Sheet $1$ tab, and type $$Equal Cost Structure$.$

This completes Part $1.$ You may now answer question #$1$ within the assignment.

Part $2$

Click the New Worksheet option.

Choose the Analysis tab and select Create Calculated Field. Entitle the new field $$Required Sales Volume in Units: Option $1,$ and within the blank calculation space below enter the formula Ceiling $($Annual Short$-$Term Fixed Costs: Option $1-$: $(($Selling Price per Unit $$ Variable Cost per Unit: Option $1)\mathrm{.}04*$ Selling Price per Unit$))$ and click OK$.$ Be sure to enter $$Ceiling$$ at the beginning of the formula, as this ensures that the required number of units will be rounded up to the nearest whole number.

Choose the Analysis tab and select Create Calculated Field. Entitle the new field $$Required Sales Volume in Units: Option $2,$ and within the blank calculation space below enter the formula Ceiling $($Annual Short$-$Term Fixed Costs: Option $2-$: $(($Selling Price per Unit $$ Variable Cost per Unit: Option $2)\mathrm{.}04*$ Selling Price per Unit$))$ and click OK$.$

Drag and drop Product Line onto the Columns shelf.

Drag and drop Required Sales Volume in Units: Option $1$ onto the Rows shelf. Drag and drop Required Sales Volume in Units: Option $2$ onto the Rows shelf to the right of Required Sales Volume in Units: Option $1$

Click the Show Me option, choose side$-$by$-$side bars, and click the Show Me option again to minimize the menu.

Click the drop$-$down arrow at the top$-$right of the Measure Names legend and choose Hide Card

Click the drop$-$down arrow beside Standard and choose Entire View.

Click the drop$-$down arrow within the Measure Values pill on the Rows shelf and choose Format. Within the Axis tab of the Format Measure Values pane click the drop$-$down arrow beside Numbers in the Scale section and choose Number $($Standard$).$ Within the Pane tab click the drop$-$down arrow beside Numbers in the Default section, choose Number $($Standard$),$ and close the Format Measure Values pane.

Double$-$click the Sheet $2$ tab, and type $$Required Sales Volume in Units$.$

This completes Part $2.$ You may now answer questions #$2$ & #$3$ within the assignment.

Part $3$

Choose the Analysis tab and select Create Calculated Field. Entitle the new field $$Required Sales Volume in Dollars: Option $1,$ and within the blank calculation space below enter the formula Required Sales Volume in Units: Opt