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 bulding security costs. 2. Variable costs that are incurred for each jacket produced, e.g., wool material. Assume that all preduced 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 breakeven point. Required: \begin{tabular}{|c|c|c|c|} \hline Units & Costs & Gross Revenue & Profit \\ \hline 1 & & & \\ \hline 2 & & & \\ \hline 3 & & & \\ \hline 4 & & & \\ \hline 5 & & & \\ \hline 6 & & & \\ \hline 7 & & & \\ \hline 8 & & & \\ \hline 9 & & & \\ \hline 10 & & & \\ \hline 11 & & & \\ \hline \end{tabular} a are to build a spreadsheet that can calculate break-even point. Break-even point is the number of units thet a business has to seil to egate costs and begin producing a profit. The calculations will update when you change the costs (fixed or variable) or sales price. int: The break-even point cannot be calculated with one Excel function. Through this case you will learn how to accomplish a complex lask by breaking the problem into smaller steps. This case will guide you through the steps. HINT: STEP-BY-STEP WALKTHROUGH To holp you get started, the file aiready has jocket soles, cost data, and column titles for you to fill out. Complete the following steps: 1. Excel formuth section: Ferform the following calcutstsons usting the given data. a. In cell C7, calculate the contribution mergin per jacket unit. b. In cell Ca, colculate the break-even point in jocket units. x 2. Date Tobie Sectiont Ferform the following calculations using the given data. Hint: You need to use absolute references, Note: You must create this data table to be able to graph the break-even point. e. In cell C12, create a formula using the number of units in column 8 and costs (foxed and variasle) to calculate the total cost for producing that many units. For example, in C13, what is the total costa for producing one jacket? tmportents Athough cost is often thought of as a negative number in nccounting, keep your calculations in poaitive numbers. x Copy and paste your formula into the remaindec of column C. b. In ceil D13, creste a formula using the number of units in column b and the soles price to calculate the gross fevenue for seling that many jockets. For example, in D13, what is the gross revenue for seling one jocket? x Copy and paste your formula for the remoinder of column D. The cerrect oraph is x. 4. Uie vour spreadthet to explore how costs and volume interact. What happens to the break-even point if you raise the fixed costs? The breake wen point will . What happens to the bretertwes point if you increase your sales price? The tresk-even point will The correct graph is x. 4. Use your spreadsheet to explore how costs and volume interact. What happens to the break-even point if you raise the fixed costs? The break-even point will What happens to the break-even point if you increase your sales price? The break-even point will What happens to the break-even point if you raise the variable costs? The break-even point will 5. Calculate profits in column E. For example, in E13, what is the profit for producing one jacket? Use a minus sign to enter loss, if any. x 6. Using the graph, explain how a break-even point changes in relation to cost and sell price. The input in the box below will not be graded, but may be reviewed and considered by your instructor. If the fix cost rises the total coset line staif parallelly upward and will intersect with If the fix cost rises the totai coset line skirt parallelly upward and wil the revenue at a later point then the break ewen polnt will increase
