cvp by lanyuehua

VIEWS: 26 PAGES: 13

									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

								
To top