80.2K

Verified Solution

Link Copied!

Assignment $4($similar to Exercise $9-31)$

CVP Analysis and Margin of Safety.

Click here to access the Assignment $4$ Excel File of Financial Data.

Assignment $4$ Steps:

Open a new Tableau Workbook from the File menu. Select the $$Connect to Data$$ option in the Data tab on your blank worksheet page, or you can select Data Source in the bottom left hand corner. Next, select Microsoft Excel in the list of connection sources. You will then select the Excel data file you just downloaded in your file explorer.

Within the Sheet$1$ tab, create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable $$Total Variable Cost$.$ In the space for formulas, sum Variable Manufacturing Costs and Variable Selling Costs.

Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable $$Total Fixed Cost$.$ In the space for formulas, sum Fixed Manufacturing Costs and the Fixed Administrative Costs.

Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable $$Breakeven Point $($Units$).$ In the space for formulas, divide the Fixed Costs by the contribution margin per unit. The contribution margin per unit can be calculated by subtracting the Variable Costs from Price. So$,$ the formula will look like the following $[$Total Fixed Costs$]/([$Price$]-[$Total Variable Costs$]).$

Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable $$Breakeven Point $($$$).$ In the space for formulas, divide the Fixed Costs by the contribution margin ratio. The contribution margin ratio can be calculated by subtracting the Variable Costs from Price then dividing by Price. So$,$ the formula will look like the following $[$Total Fixed Costs$]/(([$Price$]-[$Total Variable Costs$])/[$Price$]).$

Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable $$Margin of Safety $($Units$)$ Poor Performance$.$ Assume the company only sells $300$ units. In the space for formulas, subtract the Breakeven Point $($Units$)$ from $300.$ So$,$ the formula will look like the following $300$ Breakeven Point $($Units$).$

Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable $$Margin of Safety $($$$)$ Poor Performance$.$ Assume the company only sells $300$ units. In the space for formulas, subtract the Breakeven Point $($$$)$ from $300$ multiplied by Price. So$,$ the formula will look like the following $(300$ x Price$)$ Breakeven Point $($$$).$

Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable $$Margin of Safety $($Units$)$ Good Performance$.$ Assume the company sells $10,000$ units. In the space for formulas, subtract the Breakeven Point $($Units$)$ from $10,000.$ So$,$ the formula will look like the following $10000$ Breakeven Point $($Units$).($NOTE: Commas cannot be used in Tableau formulas$)$

Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable $$Margin of Safety $($$$)$ Good Performance$.$ Assume the company sells $10,000$ units. In the space for formulas, subtract the Breakeven Point $($$$)$ from $10,000$ multiplied by Price. So$,$ the formula will look like the following $(10000$ x Price$)$ Breakeven Point $($$$).$

Part $1$: Drag and drop Company Name from the data pane into the Columns shelf. Drag and drop Total Variable Costs and Total Fixed Costs into the Rows shelf. Add an average line by selecting the Analytics pane. Under the Summarize options, drag the Average Line option onto the graphic and drop it on the Table option. Drag and drop Company Name onto the Color mark. Drag and drop Total Variable Cost onto the Label mark in the drop down menu in the Marks section titled $$Sum$($Total Variable Cost$).$ Drag and drop Total Fixed Cost onto the Label mark in the drop down menu in$\backslash$table$[[$Company Name,P

Answer & Explanation Solved by verified expert

See Answer