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