# Breakeven Point Formula - Excel

Document Sample

```					         A              B             C              D                E                F              G               H                  I                J              K               L                 M
1   13model                                                    7/7/2010 18:02                                                          12/2/2002
2
3                                     Chapter 13. Model for Capital Structure and Leverage
4
5   In Chapter 5, we introduced the idea that risk has two principal components, market risk and stand-alone risk. Market
6   risk is measured by beta, while stand-alone risk consists of both market risk plus an element of risk that can be eliminated
7   through diversification. In this chapter, we introduce two new dimensions of risk, business risk and financial risk.
8   Business risk is the risk inherent in the firm's operations, and it would be there even if the firm used no debt. Financial risk
9   is the additional risk borne by the stockholders as a result of the use of debt.
10
11
12   BUSINESS RISK AND OPERATING LEVERAGE
13
14   Operating Leverage reflects the amount of fixed costs embedded in a firm's operations. Thus, if a high percentage of a firm's
15   costs are fixed, hence continue even if sales decline, then the firm is said to have high operating leverage. High operating
16   leverage produces a situation where a small change in sales can result in a large change in operating income. The following
17   example compares two operational plans with different degrees of operating leverage. Using the input data given below, we
18   examine the firm's profitability under two operating plans, in different states of the economy. The probabilities of the
19   economic states are also given in the example.
20
21                 Input Data                    Plan A            Plan B
22                                               Low FC           High FC
23                 Price                             \$2.00             \$2.00      Product sells at same price regardless of how it is produced.
24                 Variable costs                    \$1.50             \$1.00      Plan A has high variable costs; it doesn't use labor-saving equipment.
25                 Fixed costs                     \$20,000           \$60,000      Plan B has high fixed costs (depreciation) due to the use of labor-saving equipment.
26                 Total assets                   \$200,000          \$200,000
27                 Tax Rate                           40%                40%
28                                                                               A's break-even units = 40,000. See table.                          B's breakeven units = 60,000. See calculation below.
29   Operating Performance
30                                                                                          Plan A: Low Fixed, High Variable Costs                           Plan B: High Fixed, Low Variable Costs
31                                     Data Applicable to Both Plans                                           Net Op Profit   Return on                                         Net Op Profit  Return on
32                                               Units          Dollar             Operating      Operating     After Taxes      Equity              Operating     Operating      After Taxes     Equity
33   Demand                     Probability       Sold           Sales               Costs      Profit (EBIT)    (NOPAT)         (ROE)                 Costs      Profit (EBIT)    (NOPAT)        (ROE)
34   Terrible                       0.05           0               \$0               \$20,000       (\$20,000)      (\$12,000)        -6.0%               \$60,000       (\$60,000)      (\$36,000)      -18.0%
35   Poor                            0.2        40,000          \$80,000             \$80,000           \$0            \$0             0.0%              \$100,000       (\$20,000)      (\$12,000)       -6.0%
36   Average                         0.5       100,000         \$200,000            \$170,000        \$30,000        \$18,000          9.0%              \$160,000        \$40,000        \$24,000       12.0%
37   Good                            0.2       160,000         \$320,000            \$260,000        \$60,000        \$36,000        18.0%               \$220,000       \$100,000        \$60,000       30.0%
38   Wonderful                      0.05       200,000         \$400,000            \$320,000        \$80,000        \$48,000        24.0%               \$260,000       \$140,000        \$84,000       42.0%
39   Expected Values:                          100,000         \$200,000            \$170,000        \$30,000        \$18,000          9.0%              \$160,000        \$40,000        \$24,000       12.0%
40   Standard Deviation (SD):*                  49,396          \$98,793                            \$24,698                       7.41%                               \$49,396                      14.82%
41   Coefficient of Variation (CV):               0.49            0.49                               0.82                           0.82                               1.23                         1.23
42
A             B            C                D              E                F              G               H                                                      I          J              K               L               M
43   *We used the function wizard rather than the formula to calculate the standard deviation. Click fx>Statistical>STDEVP.                                                         You must scroll down the dialog box to make all the entries.
44   Then click on each entry of the item whose SD we seek some number of times that is consistent with the item's probability
45   weight. Thus, for units sold, 0 has a probability of 0.05, so we click on it once. 40,000 units has a probability of 0.2, which
46   is 4 times as large as 0.05, so we click on it 4 times. Continuing, we click on 100,000 10 times, 160,000 4 times, and
47   200,000 one time. We have a total of 20 entries. We show a picture of the dialog box on the screen to the right.
48
49   Which plan is better? Based on expected profits and the return on equity (ROE), Plan B looks better. However, Plan B
50   is also riskier as measured by the standard deviation (SD) and the coefficient of variation (CV). So, we face a tradeoff
51   between risk and return--B is more profitable, but A is less risky. Someone will have to choose between the two plans, but
52   at this point we have no basis for making the choice.
53
54   Note also that Plan A will break even at sales as low as 40,000 units, while Plan B would have a \$20,000 loss at that level.
55   B's breakeven point is 50% higher, at 60,000 units.
56
57   The results generated above are graphed here:
58
59
60
61                                           Plan A: Low Fixed Costs,                                                                     Plan B: High Fixed Costs,
62                                           Low Operating Leverage
High Operating Leverage
63
64                            \$200,000
Revenues and costs

Revenues and costs

65                                                                                                                         \$200,000
Revenues
66                            \$150,000
\$150,000
Revenues
67                                                                                   VC
\$100,000                                                                                     \$100,000                                      VC
68
FC
69                                                                                                                          \$50,000                                      FC
\$50,000
70
\$0
71                                 \$0
0           50000         100000
72                                       0           50000            100000
Sales (units)
73                                                  Sales (units)
74
75
76   (1) B has a much higher breakeven point, and (2) B has more operating leverage in the sense that a given change in sales
77   leads to a larger change in profits than for A.
78
79   We can see from the table that A's breakeven point is at 40,000 units. We can see from the table and also from the graph
80   that B's breakeven point is between 40,000 and 100,000 units, but we cannot tell the exact point. However, we can use the
81   following formula to find the exact breakeven point:
82
FC
83                                   Q BE     =          / (P - VC)    In words, the quantity at which a firm breaks even is found as Fixed costs divided by
84                                                                     the difference between Price and Variable costs.
85   Plan A
86   Q BE =                                FC             /                  (P                 -                           VC)
87   Q BE =                              \$20,000          /                \$2.00                -                          \$1.50
88   Q BE =                                40,000    Units.
89
90   Plan B
91   BE B =                                FC                 /              (P                 -                           VC)
92   BE B =                              \$60,000              /            \$2.00                -                          \$1.00
A      B           C   D   E   F   G   H   I   J   K   L   M
93 BE B =   60,000   Units.
A             B           C              D               E                 F              G             H                          I   J   K   L   M
94
95 At this point, we know that Plan B has a higher expected rate of return, but it is also more risky. Our analysis is based on
96    stand-alone risk. However, given a positive correlation between the firm's returns and that on the market, Plan B's higher
97    risk will result in a higher "unlevered beta." We do not calculate unlevered betas here, but in the next section we assume
98    that B's beta is 1.5, and we also assume that management believes that choosing Plan B would lead to a higher stock
99    price than Plan A.
100
101
102   FINANCIAL RISK AND LEVERAGE
103
104   Financial Leverage refers to the use of fixed-income securities (preferred stock and debt) in the capital structure. The
105   firm has a certain amount of business risk as discussed above in connection with operating leverage. This business risk is
106   measured by the firm's "unlevered beta," which is the beta it would have if it had no debt. If the firm uses no financial
107   leverage, i.e., no debt or preferred stock, then each stockholder would bear that business risk in proportion to his or her
108   share of the stock. However, if the firm uses debt, then the business risk will be concentrated on its stockholders, and each
109   will have to bear more of that risk than if the firm had remained debt free.
110
111   The risk of the stock is reflected in the stock's beta coefficient, and, as we discuss below, beta rises with the use of debt-- the
112   more debt, the higher the beta. The lowest beta is the one that would exist if no debt were used--this is the "unlevered
113   beta," and it reflects the firm's business risk as discussed above.
114
115   In the following example we illustrate all this, continuing with the situation described in our operating leverage example. We
116   assume that the company decided on Plan B and thus requires \$200,000 of capital. We also assume that the firm must
117   borrow in increments of \$20,000, and that its debt cannot exceed \$120,000 due to restrictions in its corporate charter.
118   Further, we assume that Bigbee Inc currently has 10,000 shares of common stock outstanding, and that the company will
119   use debt to repurchase stock at the current market price.
120
121   Discussions with its bankers indicate that Bigbee can borrow different amounts, but the more it borrows, the higher the cost
122   of its debt as shown in the table below:
123
124                            Debt Cost Schedule
125                               Amount
126                   D/A ratio borrowed      Cost of debt
127                     0%           \$0           8.0%
128                     10%       \$20,000         8.0%
129                     20%       \$40,000         8.3%
130                     30%       \$60,000         9.0%
131                     40%       \$80,000        10.0%
132                     50%      \$100,000        12.0%
133                     60%      \$120,000        15.0%
134
A          B            C             D             E              F                   G          H          I      J   K   L   M
135   OPTION 1: Finance Plan B entirely with common equity (Equity = 100%, Debt = 0%)
136
137   Assets                        \$200,000
138   Debt ratio                         0%
139   Equity Ratio                     100%
140   Debt                               \$0
141   Equity                       \$200,000
142   Interest rate                   8.00%    Interest rate from debt cost schedule above.
143   Tax rate                          40%
144   Shares outstanding              10,000
145
146
147                                                             Pre-tax         Taxes            Net
148   Demand        Probability     EBIT        Interest        Income           40%           Income       ROE       EPS
149   Terrible         0.05       (\$60,000)       \$0           (\$60,000)      (\$24,000)       (\$36,000)   -18.00%   (\$3.60)
150   Poor             0.20       (\$20,000)       \$0           (\$20,000)       (\$8,000)       (\$12,000)    -6.00%   (\$1.20)
151   Normal           0.50       \$40,000         \$0           \$40,000         \$16,000        \$24,000     12.00%     \$2.40
152   Good             0.20       \$100,000        \$0           \$100,000        \$40,000        \$60,000     30.00%     \$6.00
153   Wonderful        0.05       \$140,000        \$0           \$140,000        \$56,000        \$84,000     42.00%     \$8.40
154
155   Expected values:            \$40,000          \$0           \$40,000        \$16,000        \$24,000     12.00%    \$2.40
156   Standard Deviation:                                                                                 14.82%    \$2.96
157   Coefficient of Variation:                                                                             1.23     1.23
158
159   OPTION 2: Finance Plan B with \$100,000 of debt and \$100,000 of equity (50% equity, 50% debt)
160
161   Assets                       \$200,000
162   Debt ratio                        50%
163   Equity Ratio                      50%
164   Debt                         \$100,000
165   Equity                       \$100,000
166   Interest rate                     12%    Interest rate from debt cost schedule above.
167   Tax Rate                          40%
168   Shares outstanding               5,000   Assumes stock can be repurchased at the current price.
169
170
171                                                             Pre-tax         Taxes            Net
172   Demand        Probability     EBIT        Interest        Income           40%           Income       ROE       EPS
173   Terrible         0.05       (\$60,000)     \$12,000        (\$72,000)      (\$28,800)       (\$43,200)   -43.20%   (\$8.64)
174   Poor             0.20       (\$20,000)     \$12,000        (\$32,000)      (\$12,800)       (\$19,200)   -19.20%   (\$3.84)
175   Normal           0.50       \$40,000       \$12,000        \$28,000         \$11,200        \$16,800     16.80%     \$3.36
176   Good             0.20       \$100,000      \$12,000        \$88,000         \$35,200        \$52,800     52.80%    \$10.56
177   Wonderful        0.05       \$140,000      \$12,000        \$128,000        \$51,200        \$76,800     76.80%    \$15.36
178
179   Expected values:            \$40,000       \$12,000         \$28,000        \$11,200        \$16,800     16.80%    \$3.36
180   Standard Deviation:                                                                                 29.64%    \$5.93
181   Coefficient of Variation:                                                                             1.76     1.76
182
A              B            C              D              E               F               G               H                   I   J   K   L   M
183   Typically, financial leverage increases the expected rate of return on equity. In this case, the return on equity rises from
184   12% to 18%. However, this higher return comes at a price--the higher the debt ratio, the greater the risk as indicated by
185   the coefficient of variation, which rises from 1.23 to 1.76.
186
187   In the tables just above, we calculated the expected EPS and risk measures at zero debt and at 50% debt. We can use an
188   Excel Data Table to calculate these values at a number of different debt ratios, as shown below.
189
190                  D/A Ratio     Exp. EPS Std dev of EPS        CV of EPS
191                    50%           \$3.36       \$5.93              1.76
192                     0%           \$2.40       \$2.96              1.23              Minimum risk
193                    10%           \$2.56       \$3.29              1.29
194                    20%           \$2.75       \$3.70              1.35
195                    30%           \$2.97       \$4.23              1.43
196                    40%           \$3.20       \$4.94              1.54
197                    50%           \$3.36       \$5.93              1.76             Maximum EPS
198                    60%           \$3.30       \$7.41              2.25
199
200   There's a conflict between risk and return so we must decide on a tradeoff, i.e., must decide the optimal capital structure.
201
202
203   DETERMINING THE OPTIMAL CAPITAL STRUCTURE
204
205   The optimal capital structure is the one that maximizes the stock price. Also, that same capital structure minimizes the
206   WACC. To find--or, really, estimate--the optimal capital structure, we need information on how capital structure affects
207   the costs of debt and equity. The effects on debt are usually estimated by talking with bankers and investment
208   bankers--Bigbee's debt cost schedule as shown above was determined in this way. The effects on the cost of equity are
209   determined in various ways, but one logical starting point is the Hamada Equation, which is explained below.
210
211   THE HAMADA EQUATION
212
213   Hamada developed his equation by merging the CAPM with the Modigliani-Miller model. We use the model to determine
214   beta at different amounts of financial leverage, and then use the betas associated with different debt ratios to find the cost of
215   equity associated with each of those debt ratios. Here is the Hamada equation:
216
217     bL = bU x [1 + (1-T) x (D/E)]
218
219   Here bL is the leveraged beta, bU is the beta that the firm would have if it used no debt, T is the marginal tax rate, D is the
220   market value of the debt, and E is the market value of the equity.
221
222   In the table below, we apply the Hamada equation to Bigbee Electronics, given its unlevered beta and tax rate.
223
224   BU                 1.5
225   Tax rate          40%
226
227                     D/A           D/E            BL
228                     0.0           0.00           1.50
229                     0.1           0.11           1.60
230                     0.2           0.25           1.73
231                     0.3           0.43           1.89
A    B     C      D     E   F   G   H   I   J   K   L   M
232       0.4   0.67   2.10
233       0.5   1.00   2.40
234       0.6   1.50   2.85
A             B            C             D                E              F              G              H                  I   J   K   L   M
235
236   As the table shows, beta rises with financial leverage. With beta specified, we can determine the effects of leverage on the
237   cost of equity and then on the WACC. Here we assume that the risk-free rate is 6% and the required return on the market
238   is 10%. We also assume that Bigbee pays out all of its earnings as dividends, hence its earnings and dividends are not
239   expected to grow. Therefore, its stock price can be found by using the perpetuity equation, Price = Dividend/k s.
240
241   Risk-free rate, kRF:                 6%
242   Market return, kM:                  10%
243
244    D/A ratio     D/E ratio     A-T kD       EPS = DPS      Estimated beta Cost of equity   Est. Price     P/E Ratio         WACC
245      0%           0.00%         4.8%          \$2.40             1.50         12.0%          \$20.00          8.33            12.00%
246      10%         11.11%         4.8%          \$2.56             1.60         12.4%          \$20.65          8.06            11.64%
247      20%         25.00%         5.0%          \$2.75             1.73         12.9%          \$21.33          7.75            11.32%
248      30%         42.86%         5.4%          \$2.97             1.89         13.5%          \$21.90          7.38            11.10%
249      40%         66.67%         6.0%          \$3.20             2.10         14.4%          \$22.22          6.94            11.04%
250      50%         100.00%        7.2%          \$3.36             2.40         15.6%          \$21.54          6.41            11.40%
251      60%         150.00%        9.0%          \$3.30             2.85         17.4%          \$18.97          5.75            12.36%
252
253   We see that the stock price is maximized, and the WACC is minimized, if the firm finances with 40% debt and 60%
254   equity. This is the optimal capital structure.
255
256   Below, we graph the key data from the table above.
257
258                                             Cost of Capital Graph
259
260
261             20.0%
18.0%
262             16.0%
263             14.0%                                                                                  Debt
264             12.0%
265             10.0%                                                                               Equity
8.0%
266              6.0%
WACC
267              4.0%
268              2.0%
269              0.0%
270                     0%            20%            40%             60%            80%
271                                               Debt Ratio
272
273
274

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 113 posted: 7/8/2010 language: English pages: 8
Description: Breakeven Point Formula document sample