{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Kellogg valuation template","Kellogg income statement","Kellogg balance sheet","Kellogg CSCF","Kellogg WACC","Kellogg dividends"],"not_rendered":{"charts":0,"images":0,"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":"Kellogg valuation template","index":0,"n_rows":35,"n_cols":8,"rows_kept":35,"cut":false,"hidden":false,"merged":[[0,0,0,7],[19,0,19,6]]}
["KELLOGG CASH FLOW VALUATION TEMPLATE"]
["Current free cash flow (FCF)",{"$f":"'Kellogg CSCF'!B46","v":1138.3331408775982}]
["Growth rate of FCF, years 1-5",0.08]
["Long-term FCF growth rate",0.02]
["Weighted average cost of capital, WACC",{"$f":"'Kellogg WACC'!B23","v":5.5038575851243152E-2}]
[]
["Year","End\n2011",2012,2013,2014,2015,2016]
["FCF",null,{"$f":"B2*(1+B3)","v":1229.3997921478062},{"$f":"C8*(1+$B$3)","v":1327.7517755196309},{"$f":"D8*(1+$B$3)","v":1433.9719175612015},1548.6896709660978,1672.5848446433856,{"$f":"getformula(G8)","v":"<--  =F8*(1+$B$3)"}]
["Terminal value",null,null,null,null,null,{"$f":"G8*(1+B4)/(B5-B4)","v":48690.236406275748},{"$f":"getformula(G9)","v":"<--  =G8*(1+B4)/(B5-B4)"}]
["Total (sum of FCF+terminal)",null,{"$f":"SUM(C8:C9)","v":1229.3997921478062},{"$f":"SUM(D8:D9)","v":1327.7517755196309},1433.9719175612015,1548.6896709660978,50362.821250919136,"<--  =SUM(G8:G9)"]
[]
["Enterprise value",{"$f":"NPV(B5,C10:G10)","v":43356.381818173497},{"$f":"getformula(B12)","v":"<--  =NPV(B5,C10:G10)"}]
["Add back initial cash",{"$f":"'Kellogg balance sheet'!B5","v":460},{"$f":"getformula(B13)","v":"<--  ='Kellogg balance sheet'!B5"}]
["Subtract out debt",{"$f":"'Kellogg balance sheet'!B17+'Kellogg balance sheet'!B18+'Kellogg balance sheet'!B22+'Kellogg balance sheet'!B23+'Kellogg balance sheet'!B24+'Kellogg balance sheet'!B25","v":7821},"<--  ='Kellogg balance sheet'!B17+'Kellogg balance sheet'!B18+'Kellogg balance sheet'!B22+'Kellogg balance sheet'!B23+'Kellogg balance sheet'!B24+'Kellogg balance sheet'!B25"]
["Equity value",{"$f":"B12+B13-B14","v":35995.381818173497},"<--  =B12+B13-B14"]
["Number of shares outstanding",{"$f":"'Kellogg WACC'!B2","v":357.3},"<--  ='Kellogg WACC'!B2"]
["Share value from our model",{"$f":"B15/B16","v":100.74274228428071},"<--  =B15/B16"]
["Actual stock price (30 Dec 2011)",50.57,"<--  50.57"]
[]
[{"$f":"\"Data table:  Model share price as function of LT growth and WACC\"&\", short-term FCF growth rate = \"&TEXT(B3,\"0%\")&CHAR(10)&\"Highlighted:  model prices within 20% of actual market price of \"&TEXT(B18,\"$0.00\")","v":"Data table:  Model share price as function of LT growth and WACC, short-term FCF growth rate = 8%\nHighlighted:  model prices within 20% of actual market price of $50.57"}]
[null,null,"Long-term growth rate ↓"]
[{"$f":"gggetformula(B22)","v":"=IF(B5>B4,B17,\"NMF\")  -->"},{"$f":"IF(B5>B4,B17,\"NMF\")","v":100.74274228428071},0,{"$f":"C22+3%","v":0.03},{"$f":"D22+3%","v":0.06},0.09,0.12]
[null,2.5000000000000001E-2,163.58237422133209,"NMF","NMF","NMF","NMF"]
[null,0.03,132.40433606870718,"NMF","NMF","NMF","NMF"]
[null,3.5000000000000003E-2,110.14232371942826,809.46393246641219,"NMF","NMF","NMF"]
["WACC -->",0.04,93.452650291081113,393.56443346781657,"NMF","NMF","NMF"]
[null,4.4999999999999998E-2,80.477716775593223,254.942373876418,"NMF","NMF","NMF"]
[null,0.05,70.102967947775625,185.63946593161342,"NMF","NMF","NMF"]
[null,5.5E-2,61.619145056698621,144.06405712079462,"NMF","NMF","NMF"]
[null,0.06,54.553412176344757,116.35226732467832,"NMF","NMF","NMF"]
[null,6.5000000000000002E-2,48.578424244955215,96.562436986008507,720.35460261970047,"NMF","NMF"]
[null,7.0000000000000007E-2,43.460365948538431,81.723739155977924,349.56735160805414,"NMF","NMF"]
[null,7.4999999999999997E-2,39.027775054439566,70.185716846644127,225.97542580766694,"NMF","NMF"]
[null,0.08,35.152056997923239,60.958097974997266,164.18226188329328,"NMF","NMF"]
[null,8.5000000000000006E-2,31.734876676284085,53.410711249324791,127.10854879766313,"NMF","NMF"]

{"sheet":"Kellogg income statement","index":1,"n_rows":20,"n_cols":4,"rows_kept":20,"cut":false,"hidden":false,"merged":[[16,0,16,3],[0,0,0,3],[1,0,1,3]]}
["Kellogg Company and Subsidiaries"]
["INCOME STATEMENT\n(millions except share data)"]
[]
[null,2011,2010,2009]
["Net sales",13198,12397,12575]
["Cost of goods sold",7750,7108,7184]
["Selling, general and administrative expense",3472,3299,3390]
["Operating profit",1976,1990,2001]
["Interest expense",233,248,295]
["Other income (expense), net",-11,"—",-22]
["Income before income taxes",1732,1742,1684]
["Income taxes",503,502,476]
["Net income",1229,1240,1208]
["Net loss attributable to noncontrolling interests",-2,-7,-4]
["Net income attributable to Kellogg Company",1231,1247,1212]
[]
["Per share amounts:"]
["Basic",3.4,3.32,3.17]
["Diluted",3.38,3.3,3.16]
["Dividends per share",1.67,1.56,1.43]

{"sheet":"Kellogg balance sheet","index":2,"n_rows":41,"n_cols":4,"rows_kept":41,"cut":false,"hidden":false,"merged":[[31,0,31,2],[0,0,0,3],[1,0,1,3],[3,0,3,2],[15,0,15,2],[25,0,25,2],[26,0,26,2]]}
["Kellogg Company and Subsidiaries"]
["CONSOLIDATED BALANCE SHEET\n(millions except share data)"]
[null,2011,2010]
["Current assets"]
["Cash and cash equivalents",460,444]
["Accounts receivable, net",1188,1190]
["Inventories",1132,1056]
["Other current assets",247,225]
["Total current assets",3027,2915]
["Property, net",3281,3128]
["Goodwill",3623,3628]
["Other intangibles, net",1454,1456]
["Other assets",516,720]
["Total assets",11901,11847]
[]
["Current liabilities"]
["Current maturities of long-term debt",761,952]
["Notes payable",234,44]
["Accounts payable",1189,1149]
["Other current liabilities",1129,1039]
["Total current liabilities",3313,3184]
["Long-term debt",5037,4908]
["Deferred income taxes",637,697]
["Pension liability",560,265]
["Other liabilities",592,639]
["Commitments and contingencies"]
["Equity"]
["Common stock, $.25 par value, 1,000,000,000 shares authorized"]
["Issued: 419,484,087 shares in 2011 and 419,272,027 shares in 2010",105,105]
["Capital in excess of par value",522,495]
["Retained earnings",6721,6122]
["Treasury stock at cost"]
["62,182,500 shares in 2011 and 53,667,635 shares in 2010",-3130,-2650]
["Accumulated other comprehensive income (loss)",-2458,-1914]
["Total Kellogg Company equity",1760,2158]
["Noncontrolling interests",2,-4]
["Total equity",1762,2154]
["Total liabilities and equity",11901,11847]
[]
[]
["Net shares outstanding",{"$f":"419484087-62182500","v":357301587},{"$f":"419272027-53667635","v":365604392},{"$f":"getformula(C41)","v":"<--  =419272027-53667635"}]

{"sheet":"Kellogg CSCF","index":3,"n_rows":46,"n_cols":5,"rows_kept":46,"cut":false,"hidden":false,"merged":[[0,0,0,3],[1,0,1,3]]}
["Kellogg Company and Subsidiaries"]
["CONSOLIDATED STATEMENT OF CASH FLOWS\n(millions)"]
[]
["Operating activities",2011,2010,2009]
["Net income",1229,1240,1208]
["Adjustments to reconcile net income to operating cash flows:"]
["Depreciation and amortization",369,392,384]
["Deferred income taxes",84,266,-40]
["Other",-22,97,13]
["Postretirement benefit plan contributions",-192,-643,-100]
["Changes in operating assets and liabilities:"]
["Trade receivables",-100,59,-75]
["Inventories",-76,-146,-13]
["Accounts payable",40,72,-59]
["Accrued income taxes",132,-192,112]
["Accrued interest expense"]
["Accrued and prepaid advertising, promotion and trade allowances",4,-12,91]
["Accrued salaries and wages",89,-169,42]
["All other current assets and liabilities",45,35,85]
["Net cash provided by operating activities",{"$f":"SUM(B5:B19)","v":1602},{"$f":"SUM(C5:C19)","v":999},{"$f":"SUM(D5:D19)","v":1648}]
[]
["Investing activities",2011,2010,2009]
["Additions to properties",-594,-474,-377]
["Other"]
["Net cash used in investing activities",{"$f":"SUM(B23:B24)","v":-594},{"$f":"SUM(C23:C24)","v":-474},{"$f":"SUM(D23:D24)","v":-377},{"$f":"getformula(D25)","v":"<--  =SUM(D23:D24)"}]
[]
["Financing activities",2011,2010,2009]
["Net increase (reduction) of notes payable, with maturities less than or equal to 90 days"]
["Issuances of notes payable, with maturities greater than 90 days"]
["Reductions of notes payable, with maturities greater than 90 days"]
["Issuances of long-term debt"]
["Reductions of long-term debt"]
["Net issuances of common stock"]
["Common stock repurchases"]
["Cash dividends"]
["Other"]
["Net cash used in financing activities",{"$f":"SUM(B28:B36)","v":0},{"$f":"SUM(C28:C36)","v":0},{"$f":"SUM(D28:D36)","v":0},{"$f":"getformula(D37)","v":"<--  =SUM(D28:D36)"}]
[]
[]
["Effect of exchange rate changes on cash and cash equivalents",-35,6,-12]
["Increase in cash and cash equivalents",{"$f":"B37+B25+B20+B40","v":973},{"$f":"C37+C25+C20+C40","v":531},{"$f":"D37+D25+D20+D40","v":1259},{"$f":"getformula(D41)","v":"<--  =D37+D25+D20+D40"}]
[]
["Interest Expense",{"$f":"'Kellogg income statement'!B9","v":233},{"$f":"'Kellogg income statement'!C9","v":248},{"$f":"'Kellogg income statement'!D9","v":295},{"$f":"getformula(D43)","v":"<--  ='Kellogg income statement'!D9"}]
["Tax rate",{"$f":"'Kellogg income statement'!B12/'Kellogg income statement'!B11","v":0.29041570438799075},{"$f":"'Kellogg income statement'!C12/'Kellogg income statement'!C11","v":0.28817451205510908},{"$f":"'Kellogg income statement'!D12/'Kellogg income statement'!D11","v":0.28266033254156769},{"$f":"getformula(D44)","v":"<--  ='Kellogg income statement'!D12/'Kellogg income statement'!D11"}]
[]
["FCFF",{"$f":"B41+B43*(1-B44)","v":1138.3331408775982},{"$f":"C41+C43*(1-C44)","v":707.53272101033292},1470.6152019002375,{"$f":"getformula(D46)","v":"<--  =D41+D43*(1-D44)"}]

{"sheet":"Kellogg WACC","index":4,"n_rows":23,"n_cols":7,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,0,0,2],[1,3,1,5]]}
["COMPUTING THE WACC FOR KELLOGG"]
["Shares outstanding",357.3,"<-- Million","Kellogg debt and rD"]
["Share price, 30 December 2011",50.57,null,"Debt",2011,2010]
["Equity value, E",{"$f":"B2*B3","v":18068.661},{"$f":"getformula(B4)&\", million $\"","v":"<--  =B2*B3, million $"},"Financial debt",6032,5904]
["Net debt financial debt, D",{"$f":"E6","v":5572},"<-- Million $","Cash",460,444]
["Tax rate, TC",{"$f":"'Kellogg CSCF'!B44","v":0.29041570438799075},{"$f":"getformula(B6)","v":"<--  ='Kellogg CSCF'!B44"},"Net financial debt",{"$f":"E4-E5","v":5572},{"$f":"F4-F5","v":5460},{"$f":"getformula(F6)","v":"<--  =F4-F5"}]
["Cost of debt, rD",{"$f":"E9","v":4.2240754169688179E-2},{"$f":"getformula(B7)","v":"<--  =E9"}]
["Expected market return, E(rM)",0.08,null,"Net interest paid",{"$f":"'Kellogg income statement'!B9","v":233}]
["Risk free rate, rf",0.01,null,"rD",{"$f":"E8/AVERAGE(E6:F6)","v":4.2240754169688179E-2},{"$f":"getformula(E9)","v":"<--  =E8/AVERAGE(E6:F6)"}]
["Equity beta, b",0.36]
[]
["WACC based on Gordon per-share dividends"]
["Current dividend/share",{"$f":"'Kellogg dividends'!E6","v":1.72},{"$f":"getformula(B13)","v":"<--  ='Kellogg dividends'!E6"}]
["Growth rate",{"$f":"'Kellogg dividends'!E12","v":5.4471371830663307E-2},{"$f":"getformula(B14)","v":"<--  ='Kellogg dividends'!E12"}]
["Cost of equity, rE",{"$f":"B13*(1+B14)/B3+B14","v":9.033632653797477E-2},{"$f":"getformula(B15)","v":"<--  =B13*(1+B14)/B3+B14"}]
["WACC",{"$f":"B5/(B5+B4)*B7*(1-B6)+B15*(B4/(B4+B5))","v":7.6109044079690102E-2},{"$f":"getformula(B16)","v":"<--  =B5/(B5+B4)*B7*(1-B6)+B15*(B4/(B4+B5))"}]
[]
["WACC based on classic CAPM"]
["Cost of equity, rE",{"$f":"B9+B10*(B8-B9)","v":3.5200000000000002E-2},{"$f":"getformula(B19)","v":"<--  =B9+B10*(B8-B9)"}]
["WACC",{"$f":"B5/(B5+B4)*B7*(1-B6)+B4/(B4+B5)*B19","v":3.3968107622796201E-2},{"$f":"getformula(B20)","v":"<--  =B5/(B5+B4)*B7*(1-B6)+B4/(B4+B5)*B19"}]
[]
[]
["Estimated WACC?",{"$f":"AVERAGE(B16,B20)","v":5.5038575851243152E-2},{"$f":"getformula(B23)","v":"<--  =AVERAGE(B16,B20)"}]

{"sheet":"Kellogg dividends","index":5,"n_rows":44,"n_cols":6,"rows_kept":44,"cut":false,"hidden":false,"merged":[[2,3,2,5],[8,3,8,5],[16,3,16,5],[0,0,0,5]]}
["TEN YEARS OF KELLOGG DIVIDENDS"]
[]
["Date","Dividends",null,"Computation of Gordon rE"]
["2001-11-28",0.253,null,"Current dividend"]
["2002-02-28",0.253,null,"Quarterly",{"$f":"B44","v":0.43}]
["2002-05-29",0.253,null,"Annual",{"$f":"E5*4","v":1.72},{"$f":"getformula(E6)","v":"<--  =E5*4"}]
["2002-08-28",0.253,null,"Current stock price",50.57]
["2002-11-26",0.253]
["2003-02-27",0.253,null,"Dividend growth rate"]
["2003-05-28",0.253,null,"10-year growth"]
["2003-08-27",0.253,null,"Quarterly",{"$f":"(B44/B4)^(1/(COUNT(B4:B44)-1))-1","v":1.3348195241285277E-2},{"$f":"getformula(E11)","v":"<--  =(B44/B4)^(1/(COUNT(B4:B44)-1))-1"}]
["2003-11-25",0.253,null,"Annual",{"$f":"(1+E11)^4-1","v":5.4471371830663307E-2},{"$f":"getformula(E12)","v":"<--  =(1+E11)^4-1"}]
["2004-03-02",0.253,null,"5-year growth"]
["2004-05-27",0.253,null,"Quarterly",{"$f":"(B44/B24)^(1/(COUNT(B24:B44)-1))-1","v":1.9714919021449173E-2},{"$f":"getformula(E14)","v":"<--  =(B44/B24)^(1/(COUNT(B24:B44)-1))-1"}]
["2004-08-27",0.253,null,"Annual",{"$f":"(1+E14)^4-1","v":8.1222546372250237E-2},{"$f":"getformula(E15)","v":"<--  =(1+E14)^4-1"}]
["2004-11-23",0.253]
["2005-02-25",0.253,null,"Gordon rE"]
["2005-05-27",0.253,null,"Based on 10-year growth",{"$f":"E6*(1+E12)/E7+E12","v":9.033632653797477E-2},{"$f":"getformula(E18)","v":"<--  =E6*(1+E12)/E7+E12"}]
["2005-08-30",0.27800000000000002,null,"Based on 5-year growth",{"$f":"E6*(1+E15)/E7+E15","v":0.11799736898961766},{"$f":"getformula(E19)","v":"<--  =E6*(1+E15)/E7+E15"}]
["2005-11-29",0.27800000000000002]
["2006-02-27",0.27800000000000002,null,null," "]
["2006-05-30",0.27800000000000002]
["2006-08-30",0.29099999999999998]
["2006-11-29",0.29099999999999998]
["2007-02-27",0.29099999999999998]
["2007-05-30",0.29099999999999998]
["2007-08-29",0.31]
["2007-11-29",0.31]
["2008-02-29",0.31]
["2008-05-29",0.31]
["2008-08-28",0.34]
["2008-12-01",0.34]
["2009-02-27",0.34]
["2009-05-28",0.34]
["2009-08-28",0.375]
["2009-11-30",0.375]
["2010-02-26",0.375]
["2010-05-27",0.375]
["2010-08-30",0.40500000000000003]
["2010-11-29",0.40500000000000003]
["2011-02-25",0.40500000000000003]
["2011-05-27",0.40500000000000003]
["2011-08-30",0.43]
["2011-11-29",0.43]