File Home Insert Page Layout View Formulas Data Review View Automate Search (Alt+Q) Help Darjan PROTECTED VIEW This file has been verified by Microsoft Defender Advanced Threat Protection and it hasn't detected any threats. If you need to edit this file, click enable editing. Enable Editing 7 EX fx A B C Data (given) Sales Price $92 per unit Variable Costs Fixed Cost $54 $570 per unit Excel Formulas Contribution Margin | per unit Break-Even Point units dy 62F D E F G H K M N Data Table Use the data and calculations above to create a data table below that will be used to graph of costs, profits and volume. Units Costs Gross Revenue Profit 1 2 3 4 5 6 7 8 Sheet1 9 10 11 12 13 Masty cloudy Q Search L ha File Home Insert Page Layout Protected View P Search (Alt+ Formulas Data Review View Automate Help PROTECTED VIEW This file has been verified by Microsoft Defender Advanced Threat Protection and it hasn't detected any threats if you need to edit this file c C7 fx A B C D E F G H 18 6 19 7 20 8 21 9 22 10 23 11 24 12 25 13 26 14 27 15 28 16 29 17 30 18 31 19 32 20 33 21 34 22 35 23 36 24 37 25 38 26 39 27 40 28 41 29 42 30 Sheet1 Ready 62F O Savch K agenow.com/ilm/takeAssignment/takeAssignmentMain.do?invoker &takeAssignmentSessionLocator &inprogres... 20 e 3 < 1 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: < Download spreadsheet BreakEvenPointCase Data-83ddbf.dix Check My Work 3 more Check My Work sites remaining Q Search LO Previous MPM 240022 "v2.cengagenow.com/ilm/takeAssignment/takeAssignmentMain.do?invokers&takeAssignmentSessionLocator=&inprogres... 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 need to use absolute references. Note: You must create this data table to be able to graph the break-even point. a. In cell C13, create a formula using the number of units in column B and costs (fixed and variable) to calculate the total cost for producing that many units. For example, in C13, what is the total costs for producing one jacket? Important: Although cost is often thought of as a negative number in accounting, keep your calculations in positive numbers. Check My Work 3 more Check My Work uses remaining Q Search Previous https://v2.cengagenow.com/mm/takeAssignment/takeAssignmentMain.do?invoker-&takeAssignmentSessionLocator &inprogres... A ject manci... Cos... Sis A Copy and paste your formula into the remainder of column C. 20 3 b. In cell D13, create a formula using the number of units in column B and the sales price to calculate the gross revenue for selling that many Jackets. For example, in D13, what is the gross revenue for selling one jacket? Break-Even Point Copy and paste your formula for the remainder of column D. 3. Graph: Select the data table you have created in cells C12-D42. Create a line graph for this data. Where the cost and gross revenue lines cross is the break-even point. Choose the correct graph. A-$3.500- 53,000 $2.500 $2,000 $1,500- $1,000 5500- Break-Even Point Check My Work 3 more Check My Work uses remaining Q Search 8. $3,500 $3,000- $2,500- $2,000- $1,500 $1.000 $300 Previous 1440 5:34 12/4/20 geNOWv2 | Online teach X https://v2.cengagenow.com/ilm/takeAssignment/takeAssignmentMain.do?invoker-&takeAssignmentSessionLocator &inprogres. A alysis Project on-Size Financi.. der Sheets Cos... C-$3,500- $3,000 Even Analysis A mis $2,500- $2,000 $1,500- $1,000 $500 4 Costs Gross Revenue Break Even Point Break-Even Point D$3,300- S 10 12 14 16 18 20 22 24 26 28 30 Costs Gross Revene $3,000 $2,500 $2,000- $1,500 $1,000 $500- Grus Reve 5 10 12 14 16 18 20 22 24 Costs Orp Reve The correct graph is 4. Use your spreadsheet to explore how costs and volume interact. What happens to the break-even point if you lower the food costs? The break-even point will, Check My Work more Check My Work uses remaining Q Search L D IC P 140 The correct graph is 4. Use your spreadsheet to explore how costs and volume interact. What happens to the break-even point if you lower the fixed costs? The break-even point will What happens to the break-even point if you decrease your sales price? The break-even point will, What happens to the break-even point if you lower 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. 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. Check My Work 3 more Check My Work uses remaining Previous