{"text_format":"1","workbook":"xlsx","n_sheets":10,"sheets":["Points","Data on Credit Spreads","Ratings and Debt to Cap","Ke and Bd - No Tax Case","Ke and Bd - Tax Case","Unlever and Re-lever Beta","Cost of Capital from Article","Re-lever Beta","Sheet1","Sheet5"],"not_rendered":{"charts":1,"images":18,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Points","index":0,"n_rows":19,"n_cols":2,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[null,"Key Points"]
[]
[null,"You can compute derived debt beta from historic credit spreads and debt to capital ratios. These are nothing like typical Bd's applied"]
[]
[null,"The standard formula for computing WACC and Bu requires the beta on debt and cost of debt. Without taxes -- Bu = Debt/EV * debt Beta + Equity/EV * equity Beta"]
[]
[null,"Given that the Bu is in theory independent of the capital structure without taxes, you can derive Be as a function of Bu -- Be = (Bu - Debt/EV * Bd)/(Equity/EV)"]
[]
[null,"In the above formula, if the Bd is higher, the Be and the cost of equity is LOWER"]
[]
[null,"As to cost of debt is observed unlike the cost of equity, the Bd can be derived from the credit spread. CS = Bd * EMRP and Bd = CS/EMRP"]
[]
[null,"The credit spread and the implied Bd vary with the cost of capital. You can use S&P standards to estimate Bd. This can be done on an aggregate basis or by business risk"]
[]
[null,"Once you have the Bd, you can interpolate and lookup to vary the Bd according to the capital structure"]
[]
[null,"You can then correct the computation of Bu -- unlevered beta for a group of companies"]
[]
[null,"You can re-lever with and without taxes"]

{"sheet":"Data on Credit Spreads","index":1,"n_rows":18,"n_cols":10,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Credit Spreads from Interest Rate File"]
[]
[]
[null,null,null,null,null,null,0.04]
[null,null,null,null,"Last","Average","EMRP","Debt Beta:"]
[null,null,null,null,null,null,null,"Spread/EMRP"]
[null,"Merrill Lynch B Adj Spread",null,null,5.0199999999999996,5.508101265822785,{"$f":"$G$5","v":0.04},{"$f":"F8/100/G8","v":1.3770253164556963},null,{"$f":"_xlfn.FORMULATEXT(H8)","v":"=F8/100/G8"}]
[null,"Merrill Lynch BB Adj Spread",null,null,3.23,3.8681012658227854,{"$f":"$G$5","v":0.04},{"$f":"F9/100/G9","v":0.96702531645569634},null,"=F9/100/G9"]
[null,"Merrill Lynch BBB Adj Spread",null,null,1.85,2.1416455696202532,0.04,0.5354113924050633,null,"=F10/100/G10"]
[null,"Merrill Lynch A Adj Spread",null,null,1.1299999999999999,1.3591139240506329,0.04,0.33977848101265823,null,"=F11/100/G11"]
[null,"Merrill Lynch AA Adj Spread",null,null,0.87,1.0217721518987344,0.04,0.2554430379746836,null,"=F12/100/G12"]
[null,"Merrill Lynch AAA Adj Spread",null,null,0.84,0.7048101265822786,0.04,0.17620253164556965,null,"=F13/100/G13"]
[]
[]
[null,"Credit Spread = Debt Beta  x EMRP"]
[]
[null,"Debt Beta = Credit Spread/EMRP"]

{"sheet":"Ratings and Debt to Cap","index":2,"n_rows":35,"n_cols":20,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,null,null,null,null,null,null,"Business Profile",null,null,2]
[]
[null,null,null,null,null,null,null,null,null,null,"AAA","AA Low","AA High","A Low","A High","BBB Low","BBB high","BB High","BB Low","BB"]
[null,null,null,null,null,null,null,null,null,1,null,0.48,0.55000000000000004,0.55000000000000004,0.6,0.6,0.7,1,1]
[null,null,null,null,null,null,null,null,null,2,null,0.45,0.52,0.52,0.57999999999999996,0.57999999999999996,0.68,1,1]
[null,null,null,null,null,null,null,null,null,3,null,0.42,0.5,0.45,0.55000000000000004,0.55000000000000004,0.65,0.65,0.70000000000000007]
[null,null,null,null,null,null,null,null,null,4,null,0.38,0.45,0.42,0.52,0.52,0.62,0.62,0.68]
[null,null,null,null,null,null,null,null,null,5,null,0.35000000000000003,0.42,0.4,0.5,0.5,0.6,0.6,0.65]
[null,null,null,null,null,null,null,null,null,6,null,0.32,0.4,0.38,0.48,0.48,0.57999999999999996,0.57999999999999996,0.62]
[null,null,null,null,null,null,null,null,null,7,null,0.3,0.38,0.35000000000000003,0.45,0.45,0.55000000000000004,0.55000000000000004,0.6]
[null,null,null,null,null,null,null,null,null,8,null,0.25,0.35000000000000003,0.32,0.42,0.42,0.52,0.52,0.57999999999999996]
[null,null,null,null,null,null,null,null,null,9,null,0,0,0.25,0.4,0.4,0.5,0.5,0.55000000000000004]
[null,null,null,null,null,null,null,null,null,10,null,0,0,0,0.35000000000000003,0.35,0.48,0.48,0.52]
[]
[null,null,null,null,null,null,null,null,null,{"$f":"INDEX(J5:J14,$M$2)","v":2},0,{"$f":"INDEX(L5:L14,$M$2)","v":0.45},{"$f":"INDEX(M5:M14,$M$2)","v":0.52},{"$f":"INDEX(N5:N14,$M$2)","v":0.52},{"$f":"INDEX(O5:O14,$M$2)","v":0.57999999999999996},{"$f":"INDEX(P5:P14,$M$2)","v":0.57999999999999996},0.68,{"$f":"INDEX(R5:R14,$M$2)","v":1},{"$f":"INDEX(S5:S14,$M$2)","v":1}]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"Surplus Cash","AAA Start","AA Start","A Start","BBB Start","BB Start","B Start"]
[null,null,null,null,null,null,null,null,null,null,null,"Debt to Capital",-1,0,{"$f":"L16","v":0.45},{"$f":"N16","v":0.52},{"$f":"P16","v":0.57999999999999996},{"$f":"R16","v":1},{"$f":"S16","v":1}]
[null,null,null,null,null,null,null,"P/B",2,null,null,"Market Debt/Cap",-1,{"$f":"N20/$I$21","v":0},{"$f":"O20/$I$21","v":0.22500000000000001},0.26,0.28999999999999998,0.5,0.5]
[null,null,null,null,null,null,null,null,null,null,null,"Debt Beta",0,{"$f":"'Data on Credit Spreads'!H13","v":0.17620253164556965},{"$f":"'Data on Credit Spreads'!H12","v":0.2554430379746836},{"$f":"'Data on Credit Spreads'!H11","v":0.33977848101265823},{"$f":"'Data on Credit Spreads'!H10","v":0.5354113924050633},{"$f":"'Data on Credit Spreads'!H9","v":0.96702531645569634},{"$f":"'Data on Credit Spreads'!H8","v":1.3770253164556963}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"AAA","AA","A","BBB","BB","B","CCC"]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"L20","v":"Debt to Capital"},0.12,0.28299999999999997,0.375,0.42499999999999999,0.53700000000000003,0.75900000000000001,1.135]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"L22","v":"Debt Beta"},{"$f":"N22","v":0.17620253164556965},0.2554430379746836,0.33977848101265823,0.5354113924050633,0.96702531645569634,1.3770253164556963]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Interpolate Lookup"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Business Risk",null,"Simple"]
[null,null,null,null,null,null,null,null,null,null,null,"Debt to Capital",null,0.6,null,{"$f":"N31","v":0.6}]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Lookup",null,{"$f":"LOOKUP(N31,N20:S20,N22:S22)","v":0.5354113924050633},null,{"$f":"LOOKUP(P31,M25:R25,M26:R26)","v":0.96702531645569634}]
[null,null,null,null,null,null,null,null,null,null,null,"Interpolate Linear",null,{"$f":"interpolate_look_up_linear(N31,N20:S20,N22:S22)","v":0.55596443640747439},null,{"$f":"interpolate_look_up_linear(P31,M25:S25,M26:S26)","v":1.0833766678070476}]
[null,null,null,null,null,null,null,null,null,null,null,"Interpolate Comp",null,{"$f":"interpolate_look_up(N31,N20:S20,N22:S22)","v":0.55069839345837779},null,{"$f":"interpolate_look_up(P31,M25:S25,M26:S26)","v":1.0690544184860591}]

{"sheet":"Ke and Bd - No Tax Case","index":3,"n_rows":43,"n_cols":19,"rows_kept":43,"cut":false,"hidden":false,"merged":[]}
[]
["Begin with No Tax Case"]
[null,null,"Constant Bd",false,null,null,null,null,null,null,"Rf",0.02]
[null,null,null,null,null,null,null,null,null,null,"EMRP",{"$f":"'Data on Credit Spreads'!G5","v":0.04},null,"Business Risk",null,{"$f":"'Ratings and Debt to Cap'!$M$2","v":2}]
[null,null,"Assumed Comparison Companies"]
[null,null,null,null,null,null,"Bu = Be * E/Cap + Bd * D/Cap",null,null,null,"Bd","Debt/Cap","Bu","Ku","Kd",null,"Be Implied","Be Computed"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Ke"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"D23","v":0.11840000000000002},null,{"$f":"#REF!*L4+L3","v":{"$error":"#REF!"}},{"$f":"R8*(1-E18)+(D14*L4+L3)*E18","v":{"$error":"#REF!"}}]
[null,null,null,null,null,null,null,"Bu Comp=",{"$f":"D12*E19+D14*E18","v":0.625},null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L9,'Ratings and Debt to Cap'!$N$20:$S$20,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.17620253164556965},0,{"$f":"$I$12","v":0.8},{"$f":"$L$3+M9*$L$4","v":5.2000000000000005E-2},{"$f":"K9*$L$4+$L$3","v":2.7048101265822785E-2},{"$f":"(N9-O9*L9)/(1-L9)","v":5.2000000000000005E-2},{"$f":"(P9-$L$3)/$L$4","v":0.8},{"$f":"(M9-K9*L9)/(1-L9)","v":0.8}]
[null,null,null,null,null,null,null,"Ku Comp=",{"$f":"I9*$L$4+L3","v":4.4999999999999998E-2},null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L10,'Ratings and Debt to Cap'!$N$20:$S$20,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.19381153305203941},0.1,{"$f":"$I$12","v":0.8},{"$f":"$L$3+M10*$L$4","v":5.2000000000000005E-2},{"$f":"K10*$L$4+$L$3","v":2.7752461322081576E-2},{"$f":"(N10-O10*L10)/(1-L10)","v":5.4694170964213161E-2},{"$f":"(P10-$L$3)/$L$4","v":0.86735427410532906},{"$f":"(M10-K10*L10)/(1-L10)","v":0.86735427410532895}]
[null,null,"Tax Rate",0,null,null,null,null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L11,'Ratings and Debt to Cap'!$N$20:$S$20,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.21142053445850917},0.2,0.8,5.2000000000000005E-2,2.8456821378340366E-2,5.7885794655414907E-2,0.94714486638537254,0.94714486638537276]
[null,null,"Be",{"$f":"'Cost of Capital from Article'!C27","v":1},null,null,null,"Bu =  ",0.8,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L12,'Ratings and Debt to Cap'!$N$20:$S$20,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.22902953586497893},0.3,0.8,5.2000000000000005E-2,2.9161181434599157E-2,6.1788065099457515E-2,1.0447016274864378,1.0447016274864378]
[null,null,null,null,null,null,null,"Ku =",8.1500000000000003E-2,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L13,'Ratings and Debt to Cap'!$N$20:$S$20,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.24663853727144872},0.4,0.8,5.2000000000000005E-2,2.9865541490857947E-2,6.6756305672761376E-2,1.1689076418190343,1.1689076418190345]
[null,null,"Constant Debt Beta",null,null,0.2,null,null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L14,'Ratings and Debt to Cap'!$N$20:$S$20,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.31568264014466546},0.5,0.8,5.2000000000000005E-2,3.2627305605786618E-2,7.1372694394213398E-2,1.2843173598553348,1.2843173598553346]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L15,'Ratings and Debt to Cap'!$N$20:$S$20,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.55596443640747439},0.6,0.8,5.2000000000000005E-2,4.2238577456298977E-2,6.6642133815551549E-2,1.1660533453887887,1.1660533453887885]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L16,'Ratings and Debt to Cap'!$N$20:$S$20,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.65872965641952985},0.7,0.8,5.2000000000000005E-2,4.6349186256781194E-2,6.5185232067510562E-2,1.1296308016877639,1.1296308016877639]
[null,null,null,"Amount","Percent","Cost","WACC",null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L17,'Ratings and Debt to Cap'!$N$20:$S$20,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.76149487643158542},0.8,0.8,5.2000000000000005E-2,5.0459795057263418E-2,5.8160819770946365E-2,0.95402049427365898,0.95402049427365876]
[null,null,"Debt",{"$f":"E18*D20","v":37500},{"$f":"'Cost of Capital from Article'!C22","v":0.375},{"$f":"$L$3+$D$14*L4","v":0.02},{"$f":"E18*F18","v":7.4999999999999997E-3},null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L18,'Ratings and Debt to Cap'!$N$20:$S$20,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.86426009644364088},0.9,0.8,5.2000000000000005E-2,5.4570403857745642E-2,2.8866365280289248E-2,0.2216591320072312,0.22165913200723258]
[null,null,"Equity",{"$f":"E19*D20","v":62500},{"$f":"1-E18","v":0.625},{"$f":"G19/E19","v":0.11840000000000002},{"$f":"G20-G18","v":7.400000000000001E-2}]
[null,null,"EV",100000,1,null,{"$f":"I13","v":8.1500000000000003E-2}]
[null,null,null,null,null,null,null,null,null,null,null,"Ku = D/Cap * Kd + E/Cap * Ke"]
[null,null,null,null,null,null,null,null,null,null,null,"Ke = (Ku - D/Cap * Kd)/(E/Cap)"]
[null,null,"Ke",{"$f":"(I13-(D14*L4+L3)*E18)/E19","v":0.11840000000000002}]
[null,null,"Be",{"$f":"(I12-D14*E18)/E19","v":1.28},null,null,null,null,null,null,null,"Ke = [Ku-(Bd*EMRP-RF)*D/Cap]/(E/Cap)"]
[null,null,null,null,null,null,null,null,null,null,null,"Be = (Bu - Bd * D/Cap)/E/Cap"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Ke","Bd"]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"L9","v":0},null,null,null,{"$f":"P9","v":5.2000000000000005E-2},{"$f":"K9","v":0.17620253164556965}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"L10","v":0.1},null,null,null,{"$f":"P10","v":5.4694170964213161E-2},{"$f":"K10","v":0.19381153305203941}]
[null,null,null,null,null,null,null,null,null,null,null,0.2,null,null,null,5.7885794655414907E-2,0.21142053445850917]
[null,null,null,null,null,null,null,null,null,null,null,0.3,null,null,null,6.1788065099457515E-2,0.22902953586497893]
[null,null,null,null,null,null,null,null,null,null,null,0.4,null,null,null,6.6756305672761376E-2,0.24663853727144872]
[null,null,null,null,null,null,null,null,null,null,null,0.5,null,null,null,7.1372694394213398E-2,0.31568264014466546]
[null,null,null,null,null,null,null,null,null,null,null,0.6,null,null,null,6.6642133815551549E-2,0.55596443640747439]
[null,null,null,null,null,null,null,null,null,null,null,0.7,null,null,null,6.5185232067510562E-2,0.65872965641952985]
[null,null,null,null,null,null,null,null,null,null,null,0.8,null,null,null,5.8160819770946365E-2,0.76149487643158542]
[null,null,null,null,null,null,null,null,null,null,null,0.9,null,null,null,2.8866365280289248E-2,0.86426009644364088]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,0]

{"sheet":"Ke and Bd - Tax Case","index":4,"n_rows":23,"n_cols":25,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
[]
["Begin with No Tax Case"]
[null,null,"Const",false,null,null,null,null,null,null,"Rf",0.02]
[null,null,null,null,null,null,null,null,null,null,"EMRP",{"$f":"'Data on Credit Spreads'!G5","v":0.04},null,"Business Risk",null,{"$f":"'Ratings and Debt to Cap'!$M$2","v":2}]
[null,null,"Assumed Comparison Companies"]
[null,null,null,null,null,null,"Bu = Be * E/Cap + Bd * D/Cap",null,null,null,"Bd","Mkt Debt/Cap Gross","Bu","Ku","Kd - Providers of Funds","Equity/Cap Gross","Tax Effect","Equity/Net Capital","Debt/Capital Net","Ke","Be Implied","Be Computed","WACC","Ku Verify","Ke Verify"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"D11","v":0.3}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"D23","v":0},null,{"$f":"#REF!*L4+L3","v":{"$error":"#REF!"}},null,{"$f":"V8*(1-E18)+(D14*L4+L3)*E18","v":{"$error":"#REF!"}}]
[null,null,null,null,null,null,null,"Bu Comp=",{"$f":"D12*E19+D14*E18","v":0},null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L9,'Ratings and Debt to Cap'!$N$21:$S$21,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.17620253164556965},0,{"$f":"$I$12","v":0.8},{"$f":"$L$3+M9*$L$4","v":5.2000000000000005E-2},{"$f":"K9*$L$4+$L$3","v":2.7048101265822785E-2},{"$f":"1-L9","v":1},{"$f":"L9*$Q$7","v":0},{"$f":"P9/(1-Q9)","v":1},{"$f":"1-R9","v":0},{"$f":"(N9-S9*O9)/R9","v":5.2000000000000005E-2},{"$f":"(T9-$L$3)/$L$4","v":0.8},{"$f":"(M9-S9*K9)/R9","v":0.8},{"$f":"T9*P9+L9*O9*(1-$Q$7)","v":5.2000000000000005E-2},{"$f":"T9*R9+S9*O9","v":5.2000000000000005E-2},{"$f":"V9*$L$4+$L$3","v":5.2000000000000005E-2}]
[null,null,null,null,null,null,null,"Ku Comp=",{"$f":"I9*$L$4+L3","v":0.02},null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L10,'Ratings and Debt to Cap'!$N$21:$S$21,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.21142053445850917},0.1,{"$f":"$I$12","v":0.8},{"$f":"$L$3+M10*$L$4","v":5.2000000000000005E-2},{"$f":"K10*$L$4+$L$3","v":2.8456821378340366E-2},{"$f":"1-L10","v":0.9},{"$f":"L10*$Q$7","v":0.03},{"$f":"P10/(1-Q10)","v":0.92783505154639179},{"$f":"1-R10","v":7.2164948453608213E-2},{"$f":"(N10-S10*O10)/R10","v":5.3831136115017976E-2},{"$f":"(T10-$L$3)/$L$4","v":0.84577840287544948},{"$f":"(M10-S10*K10)/R10","v":0.84577840287544936},{"$f":"T10*P10+L10*O10*(1-$Q$7)","v":5.0440000000000006E-2},{"$f":"T10*R10+S10*O10","v":5.2000000000000005E-2},{"$f":"V10*$L$4+$L$3","v":5.3831136115017969E-2}]
[null,null,"Tax Rate",0.3,null,null,null,null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L11,'Ratings and Debt to Cap'!$N$21:$S$21,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.24663853727144872},0.2,0.8,5.2000000000000005E-2,2.9865541490857947E-2,0.8,0.06,0.85106382978723416,0.14893617021276584,5.5873530239099856E-2,0.89683825597749622,0.89683825597749645,4.888E-2,5.2000000000000005E-2,5.5873530239099856E-2]
[null,null,"Be",{"$f":"'Cost of Capital from Article'!C27","v":1},null,null,null,"Bu =  ",0.8,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L12,'Ratings and Debt to Cap'!$N$21:$S$21,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.55596443640747439},0.3,0.8,5.2000000000000005E-2,4.2238577456298977E-2,0.7,0.09,0.76923076923076916,0.23076923076923084,5.4928426763110313E-2,0.87321066907775768,0.87321066907775768,4.7320000000000001E-2,5.2000000000000005E-2,5.4928426763110313E-2]
[null,null,null,null,null,null,null,"Ku =",8.1500000000000003E-2,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L13,'Ratings and Debt to Cap'!$N$21:$S$21,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.76149487643158542},0.4,0.8,5.2000000000000005E-2,5.0459795057263418E-2,0.6,0.12,0.68181818181818177,0.31818181818181823,5.2718762306610413E-2,0.8179690576652604,0.81796905766526029,4.5760000000000002E-2,5.2000000000000005E-2,5.2718762306610406E-2]
[null,null,"Constant Debt Beta",null,null,0.1,null,null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L14,'Ratings and Debt to Cap'!$N$21:$S$21,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.96702531645569634},0.5,0.8,5.2000000000000005E-2,5.8681012658227852E-2,0.5,0.15,0.58823529411764708,0.41176470588235292,4.7323291139240511E-2,0.68308227848101277,0.68308227848101266,4.4200000000000003E-2,5.2000000000000005E-2,4.7323291139240511E-2]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L15,'Ratings and Debt to Cap'!$N$21:$S$21,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.96702531645569634},0.6,0.8,5.2000000000000005E-2,5.8681012658227852E-2,0.4,0.18,0.48780487804878048,0.51219512195121952,4.4984936708860765E-2,0.62462341772151908,0.62462341772151897,4.2639999999999997E-2,5.2000000000000005E-2,4.4984936708860765E-2]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L16,'Ratings and Debt to Cap'!$N$21:$S$21,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.96702531645569634},0.7,0.8,5.2000000000000005E-2,5.8681012658227852E-2,0.30000000000000004,0.21,0.379746835443038,0.620253164556962,4.1087679324894523E-2,0.52719198312236304,0.52719198312236293,4.1080000000000005E-2,5.2000000000000005E-2,4.1087679324894516E-2]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L17,'Ratings and Debt to Cap'!$N$21:$S$21,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.96702531645569634},0.8,0.8,5.2000000000000005E-2,5.8681012658227852E-2,0.19999999999999996,0.24,0.26315789473684204,0.73684210526315796,3.3293164556962032E-2,0.33232911392405079,0.33232911392405023,3.952E-2,5.2000000000000005E-2,3.3293164556962011E-2]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF($D$3,$F$14,interpolate_look_up_linear(L18,'Ratings and Debt to Cap'!$N$21:$S$21,'Ratings and Debt to Cap'!$N$22:$S$22))","v":0.96702531645569634},0.9,0.8,5.2000000000000005E-2,5.8681012658227852E-2,9.9999999999999978E-2,0.27,0.13698630136986298,0.86301369863013699,9.9096202531645632E-3,{"$f":"(T18-$L$3)/$L$4","v":-0.2522594936708859},-0.25225949367088701,3.7960000000000001E-2,5.2000000000000005E-2,9.9096202531645198E-3]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Ku = Net D/Net Cap * Kd + E/Net Cap * Ke"]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Be = (Bu - Bd * Net Debt/Net Cap)/E/Net Cap"]

{"sheet":"Unlever and Re-lever Beta","index":5,"n_rows":24,"n_cols":15,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,null,null,"Re-levering",null,null,"Gross Equity",{"$f":"1-I2","v":0.65},null,"Debt Beta",{"$f":"IF(C2,F2,interpolate_look_up_linear(I2,'Ratings and Debt to Cap'!M20:S20,'Ratings and Debt to Cap'!M22:S22))","v":0.1}]
[null,"Constant Debt Beta",true,null,null,0.1,null,"Gross Debt/Capital",0.35,null,"Tax Effect",{"$f":"I3*I2","v":0.13999999999999999},null,"Equity Beta",{"$f":"(1-O1*L4)/L3","v":1.2907692307692309}]
[null,"EMRP",{"$f":"'Data on Credit Spreads'!G5","v":0.04},null,null,null,null,"Tax Rate",0.4,null,"Net Equity",{"$f":"L1/(1-L2)","v":0.7558139534883721},null,"Cost of Equity",{"$f":"O2*C3+C4","v":7.1630769230769234E-2}]
[null,"Rf",{"$f":"'Ke and Bd - No Tax Case'!L3","v":0.02},null,null,null,null,"Beta on Assets",{"$f":"AVERAGE(L8:L24)","v":0.65615775607820126},null,"Net Debt",{"$f":"1-L3","v":0.2441860465116279}]
[null,"Cost of Equity and Overall Cost of Capital from CAPM"]
["Source",null,null,null,"Gross",null,"Gross","Tax","Net Equity","Net Debt","Debt"]
["Row #",null,null,"Equity Beta","Debt/Capital","Tax Rate","Equity/Capital","Shield","Capital","Capital","Beta","Asset Beta"]
[8,"Anheuser-Busch Inbev SA (USD) ",null,1.1399999999999999,0.230230111690716,0.20837043633125557,{"$f":"1-E8","v":0.76976988830928406},{"$f":"F8*E8","v":4.7973148829588197E-2},{"$f":"G8/(1-H8)","v":0.80855901003520758},{"$f":"1-I8","v":0.19144098996479242},{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E8,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},{"$f":"K8*J8+I8*D8","v":0.94090137043661581}]
[9,"Ambev SA (USD,BRL) ",null,0.73,-7.0533920403539523E-3,0.21986674742580253,{"$f":"1-E9","v":1.0070533920403539},{"$f":"F9*E9","v":-1.5508063662316683E-3},{"$f":"G9/(1-H9)","v":1.0054940654424576},{"$f":"1-I9","v":-5.494065442457563E-3},{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E9,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},{"$f":"K9*J9+I9*D9","v":0.73346126122874822}]
[10,"SAB Miller",null,0.72,0.11064842996665464,0.39023979591836733,0.88935157003334542,4.3179420728875068E-2,0.9294862477882998,7.0513752211700198E-2,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E10,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.67628147362874591]
[11,"Molson Coors Brewing Company ",null,0.87,2.0183035916116073E-3,0.15,0.99798169640838841,3.0274553874174109E-4,0.99828392241229635,1.716077587703646E-3,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E11,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.86867862025746811]
[13,"Boston Beer",null,0.65,-1.9789584644292636E-2,0.36513773304948066,1.0197895846442926,-7.2259240750078262E-3,1.0124735278044223,-1.2473527804422346E-2,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E12,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.6568604402924324]
[14,"Constellation Brands",null,0.26,0.28257250217101165,0.29373333333333335,0.71742749782898829,8.3000962971031822E-2,0.78236450515086486,0.21763549484913514,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E13,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.22517832082413838]
[15,"Craft Brew Alliance",null,0.7,8.6995162153389996E-2,0.40322580645161288,0.91300483784661002,3.507869441668951E-2,0.94619616393969441,5.3803836060305588E-2,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E14,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.66771769836381656]
[16,"Willamett Valley",null,0.82,-3.1527093596059118E-2,0.40251572327044027,1.0315270935960592,-1.2690150881432601E-2,1.0186008945561791,-1.8600894556179082E-2,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E15,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.83339264408044889]
[17,"Coca Cola",null,1.1000000000000001,0.19775861391081506,0.30889341479972843,0.80224138608918494,6.1086333556972738E-2,0.8544357322312599,0.1455642677687401,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E16,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.95443573223125988]
[18,"Coca Cola Botteling",null,0.01,0.80036543540767713,0.34382566585956414,0.19963456459232287,0.27518617876002455,0.27542874975921122,0.72457125024078883,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E17,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},7.5211412521671003E-2]
[19,"Cott",null,0.83,0.85333814023856835,0.5,0.14666185976143165,0.42666907011928418,0.25580664171030387,0.74419335828969613,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E18,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.28673884844852182]
[20,"Dr Pepper",null,0.62,0.15979822818353046,0.3559322033898305,0.8402017718164696,5.6877335455154908E-2,0.89087220931325461,0.10912779068674539,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E19,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.56325354884289236]
[22,"Pepsi Co",null,0.69,0.14529110483955263,0.260752688172043,0.85470889516044735,3.7885046154399471E-2,0.88836463017662481,0.11163536982337519,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E20,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.62413513180420854]
[23,"Heineken",null,0.9,0.5428701669201722,0.26104868913857676,0.4571298330798278,0.14171554544695131,0.5326087763268158,0.4673912236731842,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E21,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.52608702106145266]
[25,"Kirin Holdings Company, Limited",null,0.52,0.44913056700127119,0.5,0.55086943299872881,0.22456528350063559,0.71040078716823907,0.28959921283176093,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E22,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.3983683306106604]
[26,"Tsingtao Brewery Company Limited",null,1.27,-0.29001031411157996,0.29093238434163698,1.29001031411158,-8.4373392168149042E-2,1.1896366357092831,-0.18963663570928313,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E23,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},1.4918748637798613]
[27,"Diageo",null,0.72,0.16901924766152482,0.18787878787878787,0.83098075233847513,3.1755131378831937E-2,0.85823408857496519,0.14176591142503481,{"$f":"IF($C$2,$F$2,interpolate_look_up_linear(E24,'Ratings and Debt to Cap'!$M$21:$S$21,'Ratings and Debt to Cap'!$M$22:$S$22))","v":0.1},0.63210513491647835]

{"sheet":"Cost of Capital from Article","index":6,"n_rows":60,"n_cols":7,"rows_kept":60,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Debt",0.375]
[]
[null,"Rf",5.5E-2]
[null,"Tax",0.35]
[null,"EMRP",0.04]
[null,"Be",1]
[null,"Bd",0.1]
[]
[null,"Int Rate",{"$f":"C24+C26*C28","v":5.8999999999999997E-2}]
[null,"Equity Cost",{"$f":"C27*C26+C24","v":9.5000000000000001E-2}]
[]
[null,null,"Amt","Pct","Cost","At Cost","WACC"]
[null,"Equity",10000,{"$f":"C34/$C$36","v":0.625},{"$f":"C31","v":9.5000000000000001E-2},{"$f":"E34","v":9.5000000000000001E-2},{"$f":"F34*D34","v":5.9374999999999997E-2}]
[null,"Debt",6000,{"$f":"C35/$C$36","v":0.375},{"$f":"C30","v":5.8999999999999997E-2},{"$f":"E35*(1-C25)","v":3.8350000000000002E-2},{"$f":"F35*D35","v":1.4381250000000002E-2}]
[null,null,{"$f":"SUM(C34:C35)","v":16000},null,null,null,{"$f":"SUM(G34:G35)","v":7.3756249999999995E-2}]
[]
[null,"BU"]
[null,null,"Amt","Pct","Beta","Weight"]
[null,"Equity",10000,{"$f":"C40/C42","v":0.625},{"$f":"C27","v":1},{"$f":"E40*D40","v":0.625}]
[null,"Debt",6000,{"$f":"C41/C42","v":0.375},{"$f":"C28","v":0.1},{"$f":"E41*D41","v":3.7500000000000006E-2}]
[null,null,{"$f":"SUM(C40:C41)","v":16000},null,null,{"$f":"SUM(F40:F41)","v":0.66249999999999998}]
[]
[null,"Tax Shield",null,{"$f":"D41","v":0.375},{"$f":"C25","v":0.35},{"$f":"E35","v":5.8999999999999997E-2},{"$f":"D44*E44*F44","v":7.743749999999998E-3}]
[]
[null,"Ku",null,null,null,null,{"$f":"G44+G36","v":8.1499999999999989E-2}]
[]
[]
[null,"Initial Investment",null,null,100]
[null,"After Tax All Equity Return",null,null,7.4999999999999997E-2]
[null,"Debt to Capital",null,null,0.6]
[null,"Debt Beta",null,null,0.15,{"$f":"E52*C26+C24","v":6.0999999999999999E-2},{"$f":"F52-C24","v":5.9999999999999984E-3}]
[]
[]
[null,"Re-Levering"]
[]
[null,null,"Amt","Pct","Cost","At Cost","WACC"]
[null,"Equity",{"$f":"E49","v":100},{"$f":"C58/C60","v":0.625},{"$f":"F58","v":0.10660999999999998},{"$f":"G58/D58","v":0.10660999999999998},{"$f":"G60-G59","v":6.6631249999999989E-2}]
[null,"Debt",{"$f":"C58*E51","v":60},{"$f":"C59/C60","v":0.375},{"$f":"F52","v":6.0999999999999999E-2},{"$f":"E59*(1-$E$44)","v":3.9649999999999998E-2},{"$f":"F59*D59","v":1.486875E-2}]
[null,null,{"$f":"SUM(C58:C59)","v":160},null,null,null,{"$f":"G46","v":8.1499999999999989E-2}]

{"sheet":"Re-lever Beta","index":7,"n_rows":39,"n_cols":28,"rows_kept":39,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,"Tax Rate",{"$f":"'Cost of Capital from Article'!C25","v":0.35}]
[null,"Comparable Company",null,"Pct","Beta","Weighted",null,null,null,"Comparable Company",null,"Pct","Beta","Weighted",null,null,null,null,"Be = Bu + (1-t) x (Bu-Bd) x D/E"]
[null,"Equity",{"$f":"'Cost of Capital from Article'!C34","v":10000},{"$f":"C22/C24","v":0.625},{"$f":"'Cost of Capital from Article'!C27","v":1},{"$f":"D22*E22","v":0.625},null,null,null,"Equity",{"$f":"C22","v":10000},{"$f":"K22/K24","v":0.625},{"$f":"E22","v":1},{"$f":"L22*M22","v":0.625},null,null,null,null,"Be = Bu +  (Bu-Bd) x D/E x (1-t) "]
[null,"Debt",{"$f":"'Cost of Capital from Article'!C35","v":6000},{"$f":"C23/C24","v":0.375},{"$f":"'Cost of Capital from Article'!C28","v":0.1},{"$f":"D23*E23","v":3.7500000000000006E-2},null,null,null,"Debt",{"$f":"C23","v":6000},{"$f":"K23/K24","v":0.375},{"$f":"'Cost of Capital from Article'!K28","v":0},{"$f":"L23*M23","v":0},null,null,null,null,"Be = Bu +  Bu x D/E x (1-t) -Bd x D/E x (1-t)  "]
[null,"Assets",{"$f":"'Cost of Capital from Article'!C36","v":16000},null,null,{"$f":"SUM(F22:F23)","v":0.66249999999999998},null,null,null,"Assets",{"$f":"C24","v":16000},null,null,{"$f":"SUM(N22:N23)","v":0.625},null,null,null,null,"Be = Bu  x ( 1 +  D/E x (1-t)) -Bd x D/E x (1-t)  "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Bu  x ( 1 +  D/E x (1-t)) = Be + Bd x D/E x (1-t)  "]
[null,null,null,null,null,null,null,null,null,"Beta Unlevered = 1/[1+D/E x (1-t)] x Beta Equity",null,null,null,null,null,null,null,null,"Bu  = (Be + Bd x D/E x (1-t))  / ( 1 +  D/E x (1-t))",null,null,null,null,null,null,null,"Bu = (E/(Vl-t*d))*Be + (D/(Vl-t*d))*Bd * (1-t)"]
[null,"Re-Levering",null,"Pct","Cost"]
[null,"Equity",{"$f":"'Cost of Capital from Article'!C58","v":100},{"$f":"C28/$C$30","v":0.625},null,null,null,null,null,"D/E",null,{"$f":"K23/K22","v":0.6},null,null,null,null,null,null,"Be",{"$f":"L30","v":1},null,null,null,null,null,null,"Be",{"$f":"T28","v":1}]
[null,"Debt",{"$f":"'Cost of Capital from Article'!C59","v":60},{"$f":"C29/$C$30","v":0.375},null,null,null,null,null,"1-t",null,{"$f":"1-K20","v":0.65},null,null,null,null,null,null,"Bd",{"$f":"E23","v":0.1},null,null,null,null,null,null,"E/(Vl-t*d)",{"$f":"(C28/(C30-K20*C29))","v":0.71942446043165464}]
[null,null,{"$f":"SUM(C28:C29)","v":160},null,null,null,null,null,null,"Be",null,{"$f":"M22","v":1},null,null,null,null,null,null,"D/E",{"$f":"L28","v":0.6},null,null,null,null,null,null,"Bd",{"$f":"T29","v":0.1}]
[null,null,null,null,null,null,null,null,null,"1/[1+D/E x (1-t)]",null,{"$f":"1/(1+L28*L29)","v":0.71942446043165464},null,null,null,null,null,null,"1-t",{"$f":"L29","v":0.65},null,null,null,null,null,null,"1-t",{"$f":"T31","v":0.65}]
[null,null,null,null,null,null,null,null,null,"Bu",null,{"$f":"L30*L31","v":0.71942446043165464},null,null,null,null,null,null,"D/E x (1-t)",{"$f":"T30*T31","v":0.39},null,null,null,null,null,null,"D/(Vl-t*d)",{"$f":"C29/(C30-C29*K20)","v":0.43165467625899279}]
[]
[null,null,null,null,null,null,null,null,null,"Check",null,null,null,null,null,null,null,null,"Bu",{"$f":"(T28+T29*T32)/(1+T32)","v":0.74748201438848905},null,null,null,null,null,null,"Bu",{"$f":"AB29*AB28+AB32*AB30*AB31","v":0.74748201438848916}]
[null,null,null,null,null,null,null,null,null,"Beta Equity = [1+D/E x (1-t)] x Beta Unlevered"]
[null,null,null,null,null,null,null,null,null,"D/E",null,{"$f":"K23/K22","v":0.6},null,null,null,null,null,null,"Check"]
[null,null,null,null,null,null,null,null,null,"1-t",null,{"$f":"L29","v":0.65},null,null,null,null,null,null,"Bu",{"$f":"T34","v":0.74748201438848905}]
[null,null,null,null,null,null,null,null,null,"Be ",null,{"$f":"(1+L36*L37)*L32","v":1},null,null,null,null,null,null,"1-t",{"$f":"T31","v":0.65}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Be",{"$f":"T37+T38*(T34-T29)*T30","v":0.99999999999999978}]

{"sheet":"Sheet1","index":8,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Sheet5","index":9,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}