Breakeven Analysis

Completing this activity will help you learn to:

$$ Determine the breakeven point and sales necessary to achieve a target profit.

$$ Extract information from a data set using Excel formulas, functions, and$/$or pivot tables.

Scenario:

Company A is planning to open a manufacturing facility that will produce one product.

The company is analyzing $40$ products in terms of costs and breakeven points to determine which one to select.

The Data worksheet contains a list of products with cost information.

Each row provides information about one product, including its color, unit selling price, unit variable cost, and total fixed cost.

Required:

Open spreadsheet

Complete two columns on the Data worksheet: Breakeven Point in Units and Breakeven Point in Sales

R$1.$ Cell G$2$: Enter a formula that results in the breakeven point in units.

fill in the blank $1$ of $1$

R$2.$ Cell H$2$: Enter a formula that results in the breakeven point in total sales dollars.

fill in the blank $1$ of $1$$

R$3.$ Copy down the entries in cells G$2$ and H$2$ to all the other rows with data.

Steps to create a pivot table:

S$1.$ Select a cell in the data on the Data worksheet.

S$2.$ Select Insert $/$ PivotTable. The data range will automatically be inserted. A "Create PivotTable" window should appear.

S$3.$ In the window, under "Choose where you want the PivotTable report to be placed", select "New Worksheet". A new worksheet should appear with the PivotTable.

S$4.$ A PivotTable Field List should pop up$.$ Drag fields into rows, columns, filters, and values areas as needed. $($If this gray work box does not appear, right click in the pivot table and select "Show Field List".$)$

Questions:

Use the information on the Data worksheet and pivot tables, formulas, and$/$or functions to answer the following questions.

Q$1.$ What is the highest breakeven point $($in sales dollars$)$ among all the green products?

fill in the blank $1$ of $1$

Q$2.$ What is the lowest unit contribution margin among the following six products: Product $101,$ Product $14,$ Product $16,$ Product $183,$ Product $191,$ and Product $74?$

fill in the blank $1$ of $1$$

Q$3.$ What is the margin of safety $($in dollars$)$ for Product $125$ if actual total sales are $$1,560,100?$

fill in the blank $1$ of $1$$

Q$4.$ How many additional units of Product $199$ must be sold beyond its breakeven point to achieve a target profit of $$43,200?$

fill in the blank $1$ of $1$

Q$5.$ Company A performed its analysis and narrowed the choices down to four products.

Based on profitability only, which product should the company manufacture?

Product $50$ Expected sales: $13,330$ units

Product $91$ Expected sales: $15,560$ units

Product $131$ Expected sales: $14,520$ units

Product $158$ Expected sales: $17,670$ units