Chapter 9 break even analysis PDF

Preview

Summary

Download Chapter 9 break even analysis PDF

Description

Chapter 9 Break Even Analysis Introduction What we will learn in this chapter is how to program an Excel 2007 spreadsheet to calculate        

the break even point in values the break even point in volumes or units the contribution per unit the contribution to sales (C/S) ratio the margin of safety multi product break even analysis the break even points under uncertainty BEA applied to real data

We will find that break even analysis (BEA) for accountants is a very basic set of techniques, in mathematical terms. By the end of the chapter, however, we will have explored BEA under uncertainty which is more advanced. Without wishing to work through all of the background theory of BEA, in general we should say that accountants treat all costs and revenues as linear: that is constant selling prices, constant costs per unit and so on. Economists and others treat BEA in a more realistic way by accepting that selling prices rise and fall in response to supply and demand, costs per unit can change as a result of supply and demand, taxes, exchange rate fluctuations. One of the basic assumptions of BEA is that organisations make or provide only one product or service. We will work on multi product scenarios to see what we need to do in the multi product environment. A lot of work has also been done on uncertainty in BEA: meaning that whilst we can assume linearity or non linearity, they do not include uncertainty, where we might be, say, just 80% or 95% or 99% confident that what we have done is likely to happen. Finally, we will take a mist important step in BEA and apply the various models to live data: we will use a real company’s sales and cost data to plot their break even charts and so on.

Background to BEA From chapter 8 we know there are essentially three basic cost behaviour types as far as accountants are concerned:   

Variable cost Fixed cost Semi V Variable ariable cost

Remember the following three charts from chapter 8:

Page 1 of 30

Similarly, the revenue curve is assumed to be perfectly linear: that means it will look like a variable cost curve:  starts at the origin  is a straight line sloping upwards from left to right  the slope of the sales line depends on the selling price A basic break even chart, then, looks like the chart on the left below. A break even chart based on non linear costs and revenues looks like the chart on the right below.

We are sure you will see non linear break even charts in which there might be two or more break even point. Whilst that is theoretically true, it is not especially important for the purposes of this chapter.

Definitions  break even point point: the level of sales or output at which profits are exactly nothing  contribution contribution: the contribution that sales makes to fixed costs and profit. Contribution is also usually shown as a formula or equation: Contribution = Sales – Variable Costs = Fixed costs + Profit  contribution per unit (CPU) (CPU): total contribution divided by the total number of units of output  contribution to sales (C/S) rratio atio atio: contribution divided by sales  multi product scenario scenario: the situation in which an organisation makes or provides more than one product or service

Page 2 of 30

 margin of safet safety y (MOS) (MOS): the difference between to total or budgeted sales and sales at the break even point. the margin of safety can be expressed in values or volumes or as a percentage

BEA Formulas There are two basic formulas for BEA

Total Break Even Point (Value ) =¿ Costs+ Profit

Total Break Even Point (Units ) =¿ Costs+ Profit

¿ C Ratio S ¿ Controbution per Unit

Please notice: you will usually see these two formulas written in different ways ... usually without the profit in the denominator. It is efficient to present the formulas in the way we have: you will see why shortly. We should also say that we can use these formulas whether we know the total sales and costs or whether we have been given unit selling prices and costs. Let’s begin with something simple by finding the break even points given the following information: Exercise 1 SP/unit VC/unit Total Fixed Costs

17 13 12,500

Break Even Point (Value ) =

12,500 12,500+0 = ≈ £ 53,125 0.235294 17 −13 17

Break Even Point (Units ) =

12,500+ 0 12,500 = =3,125 units 17−13 4

(

)

You should have noticed that the value for profit is zero: that’s from the break even point, of course. In terms of an Excel 2007 spreadsheet:

Exercise 2 Selling price per unit Variable costs Page 3 of 30

5.5 3

Total fixed costs

75,000

Use the formulas and your spreadsheet to find the value and volume of the break even points from the above data.

Break Even Point (Value ) =

75,000 75,000+0 = ≈ £ 165,000 0.454545 5.5 −3 5.5

Break Even Point (Units ) =

75,000+ 0 75,000 = =30,000 units 5.5−3 2.5

(

)

Again, notice that the value for profit is zero: that’s from the break even point, of course.

Exercise 3 This is a more comprehensive question: however, the key is to identify the total fixed costs, the selling price per unit and the total variable costs per unit. Then this question is not too difficult! You should use your spreadsheet for this and the formulas only if you want!

selling price per unit direct materials per unit direct labour per unit production overheads, variable per unit production overheads, fixed, total distribution costs, variable total selling costs total distribution costs, fixed total administration costs

36 6 9 3 48,000 1 100,000 60,000 160,000

The following screenshot shows the solutions and note how we have included a further element in this solution: proof. The proof we have included is an attempt to prove that our answer is correct. What we have done is to divide the BEP (value) but the BEP (volume) and if we have done our work correctly, that should give us the selling price per unit. In this case: £779,294.12 ÷ 21,647.06 units = £35.999998 per unit ≈ £36 per unit and £36 is the selling price given in the question!

Page 4 of 30

Notice the formula in cell I41 which is will tell you if your BEP calculation is wrong. Exercise 4 Total Sales Total Fixed Costs Total Costs VC

90,000 25,000 79,000 54,000

Find the break even point value from the above

Exercise 5 Using the information in exercise 4, find the value of sales to be made to ensure that the company makes a profit of £7,500. You should use the appropriate BEP formula, from earlier in the chapter, as well as your spreadsheet. The formula solution follows and then the screenshot:

Break Even Point (Value ) =

25,000+7,500 32,500 ≈ £ 81,250 = 0.4 0.4

You should have noticed that we have now used the profit element of the formula so that the denominator is total fixed costs + profit where profit is a value other than zero.

Page 5 of 30

Exercise 6 Complete the following table Output ('000 units) Direct materials + Direct Labour per 1,000 units Selling and Administration Costs (fixed per week) Production overhead function Total Costs Total Sales TFC TVC

Unit information

6

7

8

9

580 2,750,000 0.1x0.75 + 300x + 27,000 1,300.0

Average Contribution C/S ratio BEP (£) BEP (Units) This exercise is not especially difficult in terms of spreadsheeting and mathematics and you do have enough knowledge of BEA to finish this exercise.

Break Even charts We have shown you a typical break even chart already, our task now is to prepare them using Excel 2007. All we need to prepare a Break Even Char (BEC) is  sales values or sales volumes  total costs That’s it! In the example we gave at the beginning of this chapter we included the fixed cost line on our chart but we did that out of habit. If you chart has the total costs on it then we can derive the fixed costs because it is the value of total costs where the that line cuts the vertical axis as we will see. The following table is just one example of the kind of table you might come across or need to prepare in order to prepare a break even chart.

Page 6 of 30

10

Sales (units) 0 1,000 2,000 3,000 4,000 5,000 6,000 7,000 8,000 9,000 10,000

Total Costs 25,000 27,500 30,000 32,500 35,000 37,500 40,000 42,500 45,000 47,500 50,000

Total Sales 0 7,500 15,000 22,500 30,000 37,500 45,000 52,500 60,000 67,500 75,000

Selling price per unit £7.5 Here is the break even chart and from that chart we can see:  the break even point is 5,000 units which represents sales values of £37,500  total fixed costs are £25,000

To prepare this BEC all we needed to do was: Set the  sales values as sales units (X) and sales values (Y)  total costs as sales units (X) and total costs (Y)

Exercise 8 Prepare a break even chart from your answer to exercise 6, above: it should look like this! Page 7 of 30

This is what you should find:

Exercise 9 From the following data you are required to prepare a BEC:

Sales Cost of sales Gross profit less: Selling and distribution Administration Net profit

August £ 80,000 50,000 30,000 8,000 15,000 7,000

September £ 90,000 55,000 35,000 9,000 15,000 11 ,000

You have found Total Fixed Costs to be £25,000. Your chart should include  total sales  total costs  fixed costs Hints Hints:  you should use the sales values as the horizontal (X) axis  to get you started with this exercise, you need to complete the following table: Sales TFC TC VC

0

80,000

This is what you should have found: Page 8 of 30

90,000

Profit Volume Chart The profit volume or P/V chart is a simplified version of the break even chart in that it tells us the break even point but it does so with just on line or curve on it. Let’s complete a P/V chart from the following data for exercise 10: Exercise 10 Sales Units

0 2,000 4,000 6,000 8,000 10,000

Profit

-10,000 -6,000 -2,000 2,000 6,000 10,000

The result you should have is as follows and from it you can see that the break even point is 5,000 units and the fixed costs are £10,000:

Page 9 of 30

The Guaranteed Break Even and Profit Volume Charts The following example should be used to prepare a break even chart and a profit volume chart that can never fail: that is, we can rely on the example we can prepare so that it will always give us a good set of outputs. The example itself is, however, not complex. Here is a screenshot of the template we have prepared to work through this example:

That is what we are aiming at:    

an input section basic calculations of break even and related answers a break even chart and profit volume chart calculation table a break even chart

Page 10 of 30

 a profit volume chart Here is the input section of that template with some specimen data already included:

Initially, your task is to program cells B9, B10, B12 and B14 to provide the answers required to construct the break even chart and profit volume chart we can see in the template above. Secondly, program the table that follows and that is included in outline in the template. This table is the foundation of the break even and profit volume charts we will come to next.

We need to point out that the titles in row 17 are cosmetic and you can use them in your charts. In the units column, column C, you should program rows 21, 22 and 23 to show 0 units, the break even units and the maximum units respectively. The rest of this table should be filled with formulas to provide the data required by the break even and profit volume charts. Finally, use the table you have just prepared to construct the break even and profit volume charts that could look like this:

Page 11 of 30

Please note, the template work sheet is already programmed to help you!

Margin of Safety The margin of safety (MOS) tells us the difference between the break even point and one or more of

  

Current sales Budgeted sales Maximum sales

The MOS is a useful idea in that it tells managers the leeway that they might have as they make their plans. For example, consider the case where the break even value and volume are £1,379,104.48 and 125,373.13 units respectively; but the required sales value and volume according to the budget are £1,625,373.13 and 147,761.19 units respectively. We could say, then, that the margin of safety in this case is:

Firstly, it should be clear that it doesn’t matter whether we use values or volumes to calculate the MOS percentage since they will give the same answer, 15.15% in this case. Secondly, now that we know the MOS is 15.15%, of required sales, we need to appreciate whether that is a good value or not. There are no hard and fast rules here: 15.15% means that if sales fell by 15.15% from the required level of £1,625,373.13, the company would be at their break even point. If the sales fell by more than 15.15% from the required level, then the company would begin to make losses. Example 10

Page 12 of 30

Using the templates provided, calculate the margin of safety perc percentage entage under each of the following conditions.

a The budgeted sales of Company X are £5 million at £12 per unit; their total fixed costs amount to £1 million and their variable costs are £9.5 per unit.

b The selling price per unit of Company Y is £15, total fixed costs are £25,000 and the variable c

costs per unit amount to £9. The company believes its maximum sales level is £158,153. Find the required leve levell of sales from the following: Company Z’s margin of safety is -9.88%, the C/S ratio is 0.18181818, the contribution per unit is 2 and the required profit is £7,500. The maximum sales value that the company can achieve is believed to be £162,681; and the maximum sales amount to 1.1831345 times the break even sales value

Example 10 Templates a Company X everything you need/should produce for this solution is here: budgeted sales selling price per unit variable costs per unit total fixed costs Contribution per unit C/S Ratio BEP (£) BEP (units) MOS % b Company Y everything you need/should produce for this solution is here: maximum sales selling price per unit variable costs per unit total fixed costs Contribution per unit C/S Ratio BEP (£) BEP (units) MOS % c Company Z There is no template provided for this part of the exercise but these are the solutions you need to find:

Page 13 of 30

The Multiple Product Profit Volume Chart One of the key assumptions of break even analysis is that usually only one product or service provided. In reality it is very unlikely that any but the simplest business will only provide one product or service. However, in based break even and profit volume charts there is usually one sales curve, one total cost line and one break even point. The multiple break even chart gives us an insight into the situation where we are producing and selling more than one product or service. For the sake of simplicity, we will be limiting our discussion to no more than three or four products but what we are about to do can be used on an infinite number of products … whether it’s useful to talk about so many products at the same time is open to doubt, though. Why do we need Multiple Prod Product uct Profit V Volume olume Charts? Multiple product break even charts are not so nice, so we will only talk here about profit volume charts (P/V charts). Let’s begin with an example of what a multi product situation looks like: Product

Rev Revenue enue (£)

1 2 3 4 Total Fixed Costs Profit

50,000 30,000 75,000 65,000 220,000

Variable Costs (£) 35,000 9,000 30,000 78,000 152,000

Contribution (£)

C/S Ratio (%)

Rank

15,000 21,000 45,000 -13,000 68,000 44,000 24,000

30.00% 70.00% 60.00% -20.00% 30.91%

3 1 2 4

In typical marginal or variable costing style, we have here a listing of four different products being made and sold by an organisation and each of the products has its own revenue and cost profile. But notice that the fixed costs are not shared between the four products. The C/S ratios are different from product to product because if they all had the same profiles and C/S ratios, there would be no need for a multiple product P/V chart! To plot the P/V chart, we need to put the products in rank order of profit starting where there are zero sales, where profit = fixed costs: as shown in the table that follows:

Rank 0 1

Cumulative Sales (£) 0 30,000

Page 14 of 30

Cumulative Profit -44,000 -23,000

2 3 4

105,000 155,000 220,000

22,000 37,000 24,000

Now we can draw the P/V chart by putting the cumulative sales on the X axis and the cumulative profit to the Y axis:

There is a trick to the diagram we have drawn above: you need to work out how to plot the average profit line, that is the red dotted line! We can see from this multi product P/V chart that each product provides us with its own section of the P/V line … at different slopes in the piecewise style where the slope of each section of the line equals the C/S Ratio and where the steeper the slope, the higher the C/S ratio. Notice that the final section of the line slopes downwards and that’s because of the negative C/S ratio of product 4. There is one more crucial aspect of the multi product P/V chart and that it that it usually shows us TWO break even points: in the above chart that’s one for the multi product blue line and one for the average, red line. The red line joins the fixed cost curve at zero output and goes straight to the final cumulative profit figure at maximum output. This new line is, in fact, the profit line we would have drawn if we had just had total organisation sales, costs and profit. Notice the major difference between the break even point we have with product by product information, BEP1, compared with the break even point when we have no such information, BEP2: with the single straight line, our estimate of the break even point is around £142,000 … we can prove this by finding the break even point by using the normal break even equation: Over Overall all BEP = fixed costs ÷ overall organisation C/S ratio = 44,000 ÷ 0.3091 = £142,372.88 Huge W Wa arning rning: the new break even point taken from the new multiple product profit line assumes that sales will be made in order of profitability . That is, we assume that all of Product 2 will sell first and then all of Product 3 then all of Product 1 and finally we will sell all of Product 4 … not very realistic, is it? Page 15 of 30

We could set up an experiment, or simulation, to try to test where the real break even point, BEPtrue, might be: in mathematical terms, we would expect that BEP1 ≤ BEPtrue ≤ BEP2 In reality, not only is the mixture and profitability of the different products important, but the order in which they are sold will have an impact on the organisation. Example 11 FKW Ltd provides three groups of service and their management accountant has provided you with data relating to all three groups and the company as a whole. From the data supplied, a b c

plot a multiple product profit volume chart and label the two break even points you find. interpret your chart discuss one serious limitation of your chart

Product Rev Re...