You have recently been employed by an investment bank. Your line manager would like to test your skills, so he sets an initial task to you. You should prepare a financial report about a U.S. public fi

You have recently been employed by an investment bank. Your line manager would like to test your skills, so he sets an initial task to you. You should prepare a financial report about a U.S. public fi This assignment asks you to prepare a financial report for a real U.S. firm (Kellogg Company) using data from Yahoo! Finance and the financial models presented in the module. The main purpose of the assignment is to apply the valuation tools covered in the module to a real-world financial modelling problem. Valuation of Kellogg Company Weighted average cost of capital, WACC 6.50% <-- =WACC!B18 FCF growth (year 1-3) 5.50% FCF growth (year 4-6) 4% Long-term FCF growth 2.50% FCF current year (Jan. 2015) 1,575,880 <-- ='Free Cash Flow'!B10 FCF of year 6 (i.e. 2020) 2,081,519.77 <-- =G13 Terminal Value (beyond 2020) 53,404,740.21 <-- =B8*(1+B5)/(B2-B5) Projection of Free Cash Flows Year 2015 2016 2017 2018 2019 2020 FCF 1,662,553.40 1,753,993.84 1,850,463.50 1,924,482.04 2,001,461.32 2,081,519.77 <-- =F13*(1+$B$4) Terminal Value 53,404,740.21 <-- =B9 Total 1,662,553.40 1,753,993.84 1,850,463.50 1,924,482.04 2,001,461.32 55,486,259.98 <-- =G13+G14 Year Jan. 2015 PV of FCFs 45,634,414.57 <-- =NPV(B2,B15:G15) Add year Jan. 2015 (current year) cash 443,000 <-- ='Balance Sheet'!C9 Firm Value £46,077,414.57 <-- =B19+B20 Substract market value of debt -7,950,000 <-- =-WACC!B8 Equity value £38,127,414.57 <-- =B21+B22 Per-share equity valuation £107.58 <-- =B23/WACC!B2