Breakeven Analysis

Business Planning Breakeven Analysis Indicates how many items of your product are required to be sold before you make a profit. In this section you are required to:  Calculate breakeven  Graph breakeven  Demonstrate how breakeven is influenced by different pricing methods Calculating Breakeven Breakeven is calculated using the following formula Fixed Costs Contribution Margin The Contribution Margin is calculated as the sales price of the item less the variable costs of the item. (Sort of the gross profit of each item of stock sold. Consider the example: Xtreme RelaX have developed a shirt (pyjama top style) with a wheatbag sewn into the shoulder and neck area that can be microwaved to created gentle healing in the right places. The Production Director has provided the following costs to be considered. T-shirts cost $7 each, material for the wheat bags $0.20 per bag with 3 bags per shirt, sewing costs of $3 per shirt and packaging $1.25 each. The shirt is also to have a embroidery that costs$50 to set up then $2 per shirt. The embroidery will say Xtreme Relax on the breast pocket. Other expected costs include the team registration fee $112.50, advertising $53 and team stationery $35. Xtreme RelaX is aiming to be sold for $19.95 Your task (Complete) Fixed Costs (those paid whether you make zero or 5,000 shirts) Total Variable Cost per Xtreme RelaX Shirt $250.50 $ 13.85 Business Planning Contribution Margin = $19.95-$13.85 = $6.10 Breakeven = Fixed Cost/ Contribution Margin = $250.50/$6.10 = 41 shirts that need to be sold in order to make a profit. Using a spreadsheet 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 A B Breakeven Spreadsheet Xtreme RelaX shirts Sales Price 19.95 Variable Cost 13.85 Fixed Cost 250.50 Output 0 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 Revenue 0 99.75 199.5 299.25 399 498.75 598.5 698.25 798 897.75 997.5 1097.25 1197 1296.75 1396.5 1496.25 1596 1695.75 1795.5 1895.25 1995 C D Set up the spreadsheet as follows until the end of line 7. Type in the output column. In B8 type “=” click on A8 type “*” then click on B3, press F4 (this locks the cell B3 in rather than changing with the fill. Total Cost 250.5 319.75 389 458.25 527.5 596.75 666 735.25 804.5 873.75 943 1012.25 1081.5 1150.75 1220 1289.25 1358.5 1427.75 1497 1566.25 1635.5 In C8 type “=” click on B5 then F4, type “+(” click on B8 “*” click on B4 then F4, then type “)” Highlight cells B8 and C8 together, place the mouse over the bottom right hand corner of C8 so the mouse turns into a smaller + sign. Hold the left mouse button down and drag to the bottom, cellC28. Graphing Highlight the cells B7 down to C28 and click on the chart wizard in the title bar, choose “Line graph” then Next > Click on the Series Tab and then in the box labeled “Category (X) axis labels” On the spreadsheet highlight cells A8 to A28 Next > Add an appropriate title for the graph and label both axis Business Planning Breakeven for Xtreme RelaX 2500 2000 1500 1000 500 0 Revenue Total Cost $ 10 20 30 40 50 60 70 80 90 0 Sales When finished you are required to calculate the breakeven point from the original if the following independently happen. Scenerio Working e.g. Increase price to $24.95 =250.50/(24.94-13.85) A Have registration fee paid by other fundraising B Make the shirts rather than paying someone the $3 C Don’t include packaging D Don’t include logo E All of the above (answers on the following page) In D3 of your graph type the word “Breakeven” In E3 type “=C6/(C4-C5) As you change the figure the rest of the spreadsheet will change also, including the graphs. New BE Point 23 shirts Calculating Net Profit from Breakeven Lets assume that the selling price is $24.95 and all other costs are included. From the previous exercise the breakeven is calculated at 22.56757 Shirts needing to be sold to break even. The Net Profit is equal to (Number of Shirts sold - the breakeven quantity) X the contribution margin Expected Sales 65 15 40 100 200 1,000 10,000 Working = 65 – 23 x (24.95-13.85) Net Profit 466.20 Eg A B C D E F 10 0 Business Planning (Variable costs would also be lower on the last one because of economies of scale) You can calculate the exact amount easily by adding a column in your spreadsheet The Profit/loss column is equal to the Revenue less the Total Cost Total Revenue Cost 0 250.5 124.75 319.75 249.5 389 374.25 458.25 499 527.5 623.75 596.75 748.5 666 873.25 735.25 998 804.5 1122.75 873.75 1247.5 943 1372.25 1012.25 1497 1081.5 1621.75 1150.75 1746.5 1220 1871.25 1289.25 1996 1358.5 2120.75 1427.75 2245.5 1497 2370.25 1566.25 2495 1635.5 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Output 0 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 Profit/loss -250.5 -195 -139.5 -84 -28.5 27 82.5 138 193.5 249 304.5 360 415.5 471 526.5 582 637.5 693 748.5 804 859.5 Answers (Breakeven) A 23 shirts, B 28 shirts, C 34 shirts D 25 shirts E 5 shirts Answers (Profit/loss) A $84 loss, B $193.50 profit, C $859.50 profit D $1,969.50 Profit, E $10,849.50 Profit, F $110,749.50 profit. You are required to do the following for your business. a) Investigate the costs for your team and divide them into variable and fixed b) Calculate and graph the breakeven at three different price levels c) Present and explain these findings back to the team meeting. You should have a copy of this for each member in your team. d) You should make a recommendation based on the findings e) Your graphs, calculations and recommendations will form part of the financial report in the business plan and should be discussed at the oral presentations in August as well.