VIEWS: 26 PAGES: 13 POSTED ON: 9/1/2012
Cost Volume Profit Analysis Spreadsheet developed by Duncan Williamson The contents of this module are a fascination for many people: accountants and non accountants alike. Whilst some aspects of cost volume profit analysis are riddled with mathematics, calculating break even points and so on is exciting. This module is concerned with developing the module on cost analysis and then applying what we can learn from that module in a wide variety of settings. Have a look at the cost analysis module if you haven't alrady done so, or if what you see here is difficult for you. Cost Volume Profit Analysis: the behaviour of cost Spreadsheet developed by Duncan Williamson Accountants talk about the behaviour of costs: by this they mean that a cost can be a fixed cost, a variable cost or a semi variable cost. In brief, then, we the three basic cost behaviours are Fixed costs: costs that tend to remain unchanged even if output changes signficantly. Here Y= a Variable costs: costs that vary directly with output. A change in output will automatically lead to a change in a variable cost. Here Y = bX Semi variable cost: a cost that is partly fixed and partly variable at the same time. Here Y = a + bX As we saw in the cost analysis module, total costs tend to be semi variable because they are made up of fixed and variable parts. In the cost analysis module, we saw a couple of ways by which we can find out how a cost behaves: we used the high low method and we talked a bit about regression analysis. We won't go through cost analysis again, but we will be using the results of it. Go to Cost Behaviours Cost Volume Profit Analysis: three cost behaviours Spreadsheet developed by Duncan Williamson Just confirm that you are fully aware of these three cost behaviours: what they mean and try to think of examples of each of them. Fixed Costs Variable Costs Semi Variable Costs Output FC Output VC Output SVC 0 10000 5000 0 00 5000 1500 10000 8000 8000 Fixed Costs (£) Fixed Costs (£) 1 5000 1 150 1 5150 Fixed Costs (£) 6000 1000 6000 2 4000 5000 2 300 2 5300 4000 500 3 2000 5000 3 450 3 5450 2000 4 0 5000 4 600 4 5600 0 0 0 5 10 0 5 10 0 5 10 5 5 5000 Output (units) 750 5 5750 Output (units) Output (units) 6 5000 6 900 6 5900 7 5000 7 1050 7 6050 Fixed costs: rent, management salaries, depreciation Notice that all our cost lines are STRAIGHT 8 5000 8 1200 8 6200 Variable costs: raw materials, direct labour lines. Accountants like to assume that all costs Semi variable costs: electiricty,9gas, telephone 9 5000 9 1350 6350 are perfectly linear: it's easier that way! 10 5000 10 1500 10 6500 Go to Break Even Analysis (1) Cost Volume Profit Analysis: break even analysis Spreadsheet developed by Duncan Williamson Now, let's put our cost information on a graph with some sales information … this is what we call a Break Even Chart. It's called this because it shows us where sales = total costs. This is the break even point and here profit is exactly zero. Output Sales FC TC VC Profit Break even chart Click on each of the lines in turn to see where in the 0 - 10,000 10,000 - - 10,000 table on the left it comes from: which data series is which. 1 2,000 10,000 10,450 450 - 8,450 Sales, costs & profit 20,000 2 4,000 10,000 10,900 900 - 6,900 15,000 3 6,000 10,000 11,350 1,350 - 5,350 10,000 4 8,000 10,000 11,800 1,800 - 3,800 5,000 5 10,000 10,000 12,250 2,250 - 2,250 - 6 12,000 10,000 12,700 2,700 - 700 0 2 4 6 8 10 7 14,000 10,000 13,150 3,150 850 Output (units) 8 16,000 10,000 13,600 3,600 2,400 9 18,000 10,000 14,050 4,050 3,950 Sales FC TC 10 20,000 10,000 14,500 4,500 5,500 What's the break even point for this organisation? We can see from the table and the graph that it's between 6 and 7 units. Go to Break Even Analysis (2) Cost Volume Profit Analysis: break even analysis for you Spreadsheet developed by Duncan Williamson Plot the output, sales, fixed costs and total costs from the following table on a graph. If you're not sure what to do, just select all of cells A8 to D19 and then press the F11 key and take it from there. Label the graph, add a legend, format the lines as you like and place the graph over the orange area below. Don't forget, you'll have to move the graph to here once you've created it. Output Sales FC TC VC Profit 100 2,000 1,000 2,250 1,250 - 250 110 2,200 1,000 2,375 1,375 - 175 120 2,400 1,000 2,500 1,500 - 100 130 2,600 1,000 2,625 1,625 - 25 140 2,800 1,000 2,750 1,750 50 To plot construct this graph, use either the Chart Wizard or Insert Chart from the menu a the top of the screen... 150 3,000 1,000 2,875 1,875 125 160 3,200 1,000 3,000 2,000 200 170 3,400 1,000 3,125 2,125 275 180 3,600 1,000 3,250 2,250 350 190 3,800 1,000 3,375 2,375 425 200 4,000 1,000 3,500 2,500 500 Go to Break Even Analysis (3) What's the break even point for this organisation? We can see that it's between 130 and 140 units but … Cost Volume Profit Analysis: break even analysis and some arithmetic 1 Spreadsheet developed by Duncan Williamson Now we can prepare break even charts and we can see that we can tell where the break even point is but it's not very accurate is it? Sometimes, with a graph that's difficult to read, it can be a nightmare to tell what the true value of the break even point is. Luckily, some people who are good at these things, realised that with a bit of fairly simple arithmetic, we can calculate the break even point EXACTLY. There are really two formulae that help us to calculate the break even point: the first formula tells us how many units we need to make and sell to break even; and the second formula tells us the value of sales we need in order to break even. Break even point (in units) = total fixed costs Break even point (in £) = total fixed costs contribution per unit contribution/sales ratio Before we use these formulae, we need to define what the word CONTRIBUTION means. By contribution, we mean the contribution that sales makes towards fixed costs and profits. It's not a very nice word and definition but what it really means is Contribution = Sales - Variable Costs = Fixed Costs + Profit. Contribution per unit is exactly what it says; and the contribution/sales ratio is simply the contribution divided by the sales and is often known as the C/S ratio. Go to Break Even Analysis (4) Cost Volume Profit Analysis: break even analysis and some arithmetic 2 Spreadsheet developed by Duncan Williamson Let's look at a situation where we can work out the contribution and then the break even point values. I've done the first two for you, you do the other two. Sales VC Contribution Contribution/ Sales ratio For reasons that will become clear, 1,000 650 350 0.350 for break even calculations, we keep 80 10 70 0.875 the C/S ratio as a decimal rather than showing it as a percentage. 25,000 20,000 4 3 The two formulae we are using here are: Apart from HAVING to use TOTAL FIXED COST data, we can work on TOTAL sales and cost data and/or UNIT sales and cost data. Break even point (in units) = total fixed costs total fixed costs Break even point (in £) = contribution per unit contribution/sales ratio Prove the the BEP for the company in Break Even analysis (1) is 6.452 units and £12,903.23 Go to CS MOS (1) Cost Volume Profit Analysis: C/S ratio and the margin of safety 1 Spreadsheet developed by Duncan Williamson The C/S ratio is not difficult to calculate, I am sure we can all agree. However, there is more to this ratio than we have seen so far. In fact, the C/S ratio tells us the RATE at which profits are being made by an organisation or a product. It's a bit like the gross profit percentage and the net profit margin. the difference between other profitability ratios and the C/S ratio is that the C/S ratio is concerned with contribution and not profit. Let's have a look at the C/S ratio from a previous example: when the C/S ratio = £350/£1,000 = 0.35, we can say that for every £1 of sales, we are earning £0.35 contribution to fixed costs and profit. In economic terms, we would say that the higher the C/S ratio the better. In business terms, we would say that the optimal value of the C/S ratio is that which helps to maximise profits and/or optimises market share and/or optimises product growth ... The MARGIN OF SAFETY (MOS) is another useful idea that stems from cost volume profit analysis. There are several definitons of the MOS now but basically we calcualte it as follows: The MOS gives us an idea of how much leeway we have before we have any financial problems. That is, margin of safety = total sales -sales at break even point with a high MOS, we should have few problems, but if sales at break even point the MOS is low then we might find that just a small reduction in our sales could wipe out our profits Go to CS MOS (2) Cost Volume Profit Analysis: C/S ratio and the margin of safety 2 Spreadsheet developed by Duncan Williamson Let's look at a break even chart and the margin of safety in a bit more detail. We'll plot the following data on a graph first of all … and then calculate the values of the break even point, and the margin of safety. VC per unit is £12.50 and TFC = £1,000 Output Sales TC Break Even Chart: to illustrate the margin of safety 100 2,000 2,250 BEP (£) = Sales and Total Costs (£) 110 2,200 2,375 4,000 £1,000 3,500 ((£20 - 12.50)/£20) 120 2,400 2,500 3,000 130 2,600 2,625 2,500 = £1,000 140 2,800 2,750 2,000 0.375 1,500 = £2,666.67 150 3,000 2,875 1,000 500 MOS = 160 3,200 3,000 - (£4,000 - 2,666.67) 170 3,400 3,125 0 50 100 150 200 £2,666.67 180 3,600 3,250 Output (units) = 0.5 = 50% 190 3,800 3,375 Sales TC 200 4,000 3,500 Just checking: where did the £20 per unit selling price per unit that we used in the BEP calculation come from? Go to CS MOS (3) Cost Volume Profit Analysis: C/S ratio and the margin of safety for you to do Spreadsheet developed by Duncan Williamson Plot the following data on a graph first of all … and then calculate the values of the break even point, and the margin of safety. Create your graph and place it over the orange area below. The fill in the yellow table with you answers to the break even, C/S ratio (use the formula to calculate this rather than typing in your answer) and margin of safety calculations. Sales £900,000; Variable costs £525,000; Fixed costs £225,000 Break even point (£) C/S ratio Margin of safety (%) Set up an input table in cells A10:E12 or so and then put your graph over this orange area. If your graph looks terrible, check that your source data is in Columns rather than Rows (right click the graph, select Source Data, click on the Data Range tab if necessary, select Columns click OK). This is tricky I have to confess! Go to PV Chart (1) Cost Volume Profit Analysis: the Profit volume chart Spreadsheet developed by Duncan Williamson Just when you thought there couldn't be any more, here we are with the Profit volume chart or the P/V Chart. The P/V Chart looks different to the Break Even Chart but it gives us the same break even point and margin of safety information. To prove that, let's redraw the break even chart from the Break Even Analysis (1) sheet to prove this Output Profit FC The Profit Volume Chart 0 - 10,000 - 10,000 Prove that 1 - 8,450 - 10,000 6,000 Profit & Costs (£) 2 - 6,900 - 10,000 3,000 BEP = 6.452 units 3 - 5,350 - 10,000 - C/S ratio = 0.775 4 - 3,800 - 10,000 -3,000 0 2 4 6 8 10 MOS = 3.548 units 5 - 2,250 - 10,000 -6,000 6 - 700 - 10,000 -9,000 7 850 - 10,000 -12,000 8 2,400 - 10,000 Output 9 3,950 - 10,000 Profit FC 10 5,500 - 10,000 Go to PV Chart (2) Cost Volume Profit Analysis: the Profit volume chart for you to do Spreadsheet developed by Duncan Williamson Redraw the break even chart from the Break Even Analysis (2) sheet in P/V Chart format. Derive the BEP, the C/S ratio and the MOS, too. Place your graph over the orange area, as usual. Output Profit FC 100 - 250 - 1,000 Prove the 110 - 175 - 1,000 120 - 100 - 1,000 BEP = 130 - 25 - 1,000 C/S ratio = 140 50 - 1,000 MOS = 150 125 - 1,000 160 200 - 1,000 170 275 - 1,000 180 350 - 1,000 190 425 - 1,000 200 500 - 1,000 Cost Volume Profit Analysis: let's make some profit! Spreadsheet developed by Duncan Williamson In the BEA (3) sheet we said that "There are really two formulae that help us to calculate the break even point ..." and it then gave you two formulae ... take a look back if you need to. These formulae can be amended slightly to help us provide one more very useful aspect to our analysis ... what happens if we want to predict how many units to sell in order to make a profit, rather than just breaking even? The revised formulae we need are: Break even point (in units) = total fixed costs + profit Break even point (in £) = total fixed costs + profit contribution per unit contribution/sales ratio So, four examples: two for me and two for you! Note that target profit can be a loss and it generally represents the Budgeted Profit. TFC Target Profit CPU C/S ratio Target (units) Target (£) 500 200 5.00 0.40 140 1,750 1,000 550 15.50 0.25 100 6,200 300 -100 8.00 0.40 15 3 1.00 0.60