CVP Analysis and Margin of Safety. Click here to access the Assignment 4 Excel File of Financial Data. Assignment 4 Steps: L. 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 Excet in the list of connection sources. You wil then select the Excel data flle you just downioaded in your file explorer. 2. Whin the sheetl 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. Titie the new variable "Total Variable Cost". In the space for formulas, sum Variable Manufacturing Costs and Variable Selling Costs. 3. Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Fleld 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. 4. Create a calculated field by elther 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 caiculated by subtracting the Variable Costs from Price. So, the formula will look like the following "[Total Fived Costs] I [Price] - [Total Variable Costs]) 5. 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 Foxed Costs]/ ([Price] - [Total Variabie Costs]) / [Price]", 6. 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 tob and select Create Calculated Field from the dropdown menu. Tite 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 Polnt (Units)". 7. 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 monu. Titje the new variable "Margin of Safety (\$) - Poor Performance: Assume the company only selis 300 units. In the 5 pace for formulas, subtract the Breakeven Point (\$) from 300 multiplied by Price. So, the formula will look like the following "(300 Price) - Breakeven Point (\$)". 8. Create a calculated fieid by either right cilcking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Cakulated Field from the dropdown menu. Title the new variable "Margin of Safety (Units) - Good Performance"; Assume the company selis 10,000 units, In the space for formulas, subtract the Breakeven 9. 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 (\$)". 10. 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 titied "Sum(Total Variable Cost)". Drag and drop Total Fixed Cost onto the Label mark in the drop down menu in the Marks section titled "Sum (Total Fixed Cost)" 11. Part 2: In a blank worksheet, drag and drop Company Name from the data pane into the Columns shelf. Drag and drop Breakeven Point (Units) and Breakeven Point (\$) from the Data pane on the Rows sheif. Drag and drop Company Name onto the Color mark. Drag and drop Breakeven Point (Units) onto the Label mark in the drop down menu in the Marks section titled "Sum(Breakeven Point (Units))". Drag and drop Breakeven Point (\$) onto the Label mark in the drop down menu in the Marks section titled "Sum(Breakeven Point (\$))" 12. Part 3: In a blank worksheet, drag and drop Company Name from the data pane into the Columns shelf. Drag and drop Margin of Safety (Units) - Poor Performance and Margin of Safety (\$) - Poor Performance from the Dato pane on the Rows shelf. Drag and drop Company Name onto the Color mark. Drag and drop Margin of Safety (Units) - Poor Performance onto the Label mark in the drop down menu in the Marks section titled "Sum(Margin of Safety (Units) - Poor Performance)". Drag and drop Margin of Safety (\$) - Poor Performance onto the Label mark in the drop down menu in the Marks section titied "Sum(Margin of Safety (\$) - Poor Performance)": 13. Part 4; in a blank worksheet, drag and drop Company Name on the Columns shelf. Place Margin of Safety (Units) - Good Performance and Margin of Safety (\$) - Good Performance on the Rows shelf. Drag and drop Company Name onto the Color mark. Drag and drop Margin of Safety (Units) - Good Performance onto the Label mark in the drop down menu in the Marks section titled "Sum(Margin of Safety (Units) - Good Performance)". Drag and drop Margin of Safety (\$) - Good Performance onto the Label mark in the drop down menu in the Marks section titled "Sum(Margin of Safety (\$) - Good Performance)". Save your file, then answer the questions below using the Tableau dashboards you have created. \begin{tabular}{|l|l|} \hline Questions \\ \hline 1. Which company has the lowest variable cost? \\ \hline 2. Which compary has the lowest foxed cost? \\ \hline 3. Which company has the lowest breakeven point in units? \\ \hline 4. What is the breakeven point in units for the company that has the lowest breakeven point? \\ \hline 5. Which cormpany has the highest breakeven point in dollars? \\ \hline 6. What is the breakeven point in dollars for the company that has the highest breakeven point? \\ \hline 7. What is the Poor Performance margin of safoty in units for Avocado Co? \\ \hline 8. What is the Poor Performance margin of safety in dollars for Avocado Co? \\ \hline 9. What is the Good Performance margin of safety in units for Banana Co.? \\ \hline 10. What is the Good Pertormance margin of safety in dollars for Banana Co? \\ \hline \end{tabular}