H20 Data Anayltics Cost-Volume-Profit: Break-Even Analysis Using Excel Completing this activity will help you learn to: 1. understand how costs and sales price interact to produce profit. 2. create a reusable spreadsheet. 3. create absolute Excel references that will not change when a formula is copied and pasted. 4. create a graph to visually see a break-even point. Case scenario: You are working for a jacket manufacturer. You are interested in how many jackets need to be produced and sold to make a profit. Two types of costs are relevant for production: 1. The fixed costs that your business will incur no matter the number of units produced, such as the manufacturing plant's rent expense and building security costs. 2. Variable costs that are incurred for each jacket produced, e.g., wool material. Assume that all produced jackets are sold. Sales price, variable cost, and fixed cost data are available within the Case Data Excel Spreadsheet. Create Excel calculations to determine the "break-even point." Then graph the units, profits, and cost to "see" the break-even point. Required: x Download spreadsheet BreakEvenPointCaseData-11852d.xIsx You are to build a spreadsheet that can calculate break-even point. Break-even point is the number of units that a business has to sell to negate costs and begin producing a profit. The calculations will update when you change the costs (fixed or variable) or sales price. Hint: The break-even point cannot be calculated with one Excel function. Through this case you will learn how to accomplish a complex task by breaking the problem into smaller steps. This case will guide you through the steps. HINT: STEP-BY-STEP WALKTHROUGH To help you get started, the file already has jacket sales, cost data, and column titles for you to fill out. Complete the following steps: 1. Excel Formula Section: Perform the following calculations using the given data. a. In cell C7, calculate the contribution margin per jacket unit. $ b. In cell C8, calculate the break-even point in jacket units. 2. Data Table Section: Perform the following calculations using the given data. Hint: You \begin{tabular}{|l|r|l|} \hline Data (given) & & \\ \hline Sales Price & $106 & per unit \\ \hline Variable Costs & $46 & per unit \\ \hline Fixed Cost & $660 & \\ \hline Excel Formulas & & \\ \hline Contribution Margin & & per unit \\ \hline Break-Even Point & & units \\ \hline & & \\ \hline Data Table & \\ \hline Use the data and calculations above to create a data table below that will be \\ \hline \end{tabular} Use the data and calculations above to create a data table below that will be used to graph of costs, profits and volume. Units \begin{tabular}{|l|l|l|} \hline Costs & Gross Revenue & Profit \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular}