financial modelling, finance report
company name: Kellogg Company link to firm: https://uk.finance.yahoo.com/q?s=K&ql=0
Objective: This assignment asks you to prepare a financial report for a real U.S.
firm 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.
Task description: 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 firm. In your report, you
should include the following components:
Company Profile and Financial Information
Present the profile of your firm including some recent relevant financial news that
could be interesting to its shareholders. Then choose one of the direct
competitors of your firm (can be found in Yahoo! Finance under the “COMPANY”
section of the main menu) and compare your firm’s capital structure to the capital
structure of the chosen competitor. Which of the two firms is expected to be more
flexible in the future and why?
(10 marks)
Cost of Equity and Cost of Debt
Compute the cost of equity of your firm using the Gordon Growth Model (GGM).
To estimate the dividend growth use a 6-year history of past dividends (assume
November 30, 2015 as the end date). Use the last annualised dividend paid as
the current dividend and the dividend-adjusted closing price of the company’s
stock on the day of the last dividend payment as the current stock price.
(5 marks)
Discuss the main limitations of using the GGM for computing the cost of equity of
the firm.
(5 marks)
Compute the Weighted Average Cost of Capital (WACC) of your firm (Hint:
Calculate the tax rate and the cost-of-debt first. For the stock price you can
consider the dividend-adjusted closing price on the day of the last dividend
payment as above).
(15 marks)
(Total: 25 marks)
2
Firm Valuation
Download the last available financial statements of your company from Yahoo!
Finance (Guidance on how to do this can be found in the “Supplementary
MaterialDownloading Data from Yahoo Finance” tutorial on the blackboard).
Compute the Free Cash Flows (FCF) of your company for the current financial
year.
(10 marks)
Assume that the FCFs are anticipated to grow at a constant rate of 5.5% for the
first 3 years, then at a rate of 4% for the following 3 years (i.e. years 4 to 6) and
finally at a rate of 2.5% from year 6 onwards. Then perform the following tasks:
(i) Project the FCFs to years 1 to 6
(ii) Obtain the terminal value
(10 marks)
Estimate the total value as well as the value per share of your firm. Would you
recommend buy or sell for your firm’s share to a potential investor and why?
(10 marks)
Briefly discuss how each one of the following is expected to affect the value of
your company everything else held constant (Note: No additional calculations are
required for this question).
(i) The company makes a share repurchase at the current stock price.
(ii) A rating agency announces that your firm might not be able to repay part
of its debt in the near future.
(10 marks)
(Total: 40 marks)
Investment Appraisal
Analyse the following project for the firm based on the Net Present Value (NPV)
rule. You should use the current Weighted Average Cost of Capital and the tax
rate computed above, respectively, for your calculations:
“The firm is considering investing in a new line of products for the next 6 years.
To this end, the company needs to invest in a new technologically advanced
machine. The cost of the new machine is $185,000 and its expected life is 10
years. The machine depreciates at a rate of 10% annually (straight line). The
CFO of the company believes that the machine can be sold for 5% higher than its
remaining book value at the end of year 6. The company is expecting to sell
13,500 of the new products by investing in the new machine. The new product
will be promoted through an advertising campaign which will cost $34,000 per
year. The company spent $27,500 for research and development for the new
product. The price of the new product is expected to be $25 for the first three
years and after year 3 it is expected to increase by 4% each year. The fixed
production cost is $30,000 annually. Variable costs are $13 per unit of product in
the first year, but they are expected to rise by 7.5% each year for the following
years due to increasing labour costs. The firm paid $4,500 to a marketing
company to conduct a customer survey for the new product”.
3
a) Should the firm introduce the new product?
(10 marks)
b) Determine the number of units of the new product the company should
produce in order for the project to be profitable.
(5 marks)
c) Suppose that the company decides to keep the new machine until it gets
fully depreciated. Determine the price per unit of the new product the
company should charge to achieve at least $250,000 of profit.
(10 marks)
(25 marks)
4
Important details
The file “Firm-Student ID.xlsx” provided in the Assignment 1 folder at the
Blackboard site of the module assigns a real U.S. firm to each student id.
Therefore, you should prepare the report for the firm assigned to you.
To find the data you need, you should use Yahoo! Finance. A link to
each firm’s webpage is provided in the above file as well.
The question is strongly related to the material covered in Lectures 1-9
and Labs 1-4 of the module and the associated chapters from Simon
Benninga’s textbook (“Principles of Finance with Excel”, 2nd edition).
You should prepare your report in Microsoft Word or in another word
processing software.
In each section of the report, you should also include details about the
data you used and the formulas you applied for your computations.
However, try to be brief and clear in your answers.
The questions should be answered using Microsoft Excel in a single
workbook with multiple worksheets. In the workbook, you should
include all the data you obtained from Yahoo! Finance.
Your submission should include a copy of your Excel workbook as
an appendix to your report. 30% of the coursework mark will be
subtracted from submitted assignments that do not include a copy of
the Excel workbook.
You should present your formulas and models in Word and Excel in a
helpful and clear way. You should also use an appropriate format for the
Excel cells. All graphs should be included in your report (Word file).
All citations to articles and textbooks should be explicitly mentioned in the
main text.
More details about coursework submission can be found at
http://www.uea.ac.uk/calendar/section3/regs(gen)/submission-of-work-forassessment-(taught-programmes)
Any questions related to the coursework should be submitted to the
“Assignment 1 Questions” forum within the Q&A forum on the
Blackboard Site of the module. The link to the Q&A forum is below the
link to Module Documents. To ask a question, click on Create a New
thread and post your question. You should also click on “Subscribe” to
receive an email when a new question is posted. You should visit this
forum regularly.
I will also be available during my usual office hours for any coursework
related enquiries.
Submission deadline: Thursday 21/01/2016 at 15:00 (3 PM)
Word limit: The report should not exceed 2,500 words. This limit excludes the
Appendix with the Excel workbook, but includes the references.
GOOD LUCK!
5
UG Coursework Assessment Criteria
In preparing your coursework, you should be aware that you will be assessed by
reference to the extent to which your answers meet the following criteria:
Criterion Weight
Identification and application of knowledge
to the question asked 20%
Evidence of reading and research 10%
Capacity to find data from online resources 10%
Ability to construct financial models related
to the question asked 15%
Ability to correctly implement these models
using spreadsheet software 25%
Demonstration of ability to structure and
present work in a clear and coherent way 15%
Clear and accurate expression 10%
Please preserve anonymity by using your university number, not your name.
This coursework counts for 50% of the assessment in this module.
You should be aware of the standards required for the award of marks for
coursework. These are available at
http://www.uea.ac.uk/learningandteaching/documents/assessment/Undergra
duateMarkingDescriptors
Referencing
Your coursework should clearly distinguish between your original words and
ideas, and those of others. When referring to the work of others, from books,
journals or any other source (including the internet), it is essential that you
make this clear by acknowledging your source and referencing correctly. Failure
to reference correctly will lose you marks and may constitute plagiarism or
collusion. Unless specified otherwise by the module organiser, Norwich
Business School uses the Harvard system of referencing.
University Policy on Plagiarism and Collusion
It is important that all students familiarise themselves with the rules and
regulations regarding plagiarism and collusion. Understanding these rules will
help you to avoid plagiarism, and to maintain the quality of your academic work.
You should ensure that you are familiar and up to date with the Universities rule
on Plagiarism and Collusion, available at
6
https://www.uea.ac.uk/learningandteaching/students/studying/plagiarism
You should also make sure that you are familiar with the Business School’s
policy on Harvard referencing which is available in your student handbooks.
Individual study skills support and advice on referencing is also provided by the
Learning Enhancement Service at the Dean of Students Office.
Where work has been submitted in word-processed format, you may be
requested to provide a digital copy of your coursework (after an initial
examination of the hard copy by the coursework assessors) for use with
plagiarism detection software. Instances where plagiarism is suspected will be
investigated by the NBS Plagiarism Officer. Students who are found to have
plagiarised will be penalised.
In proven cases offenders will be punished and the punishment may extend to
degree failure, temporary suspension or expulsion from further study if the case
comes before a Discipline Committee of the University. Work for assessment
should be submitted to the relevant Learning and Teaching Service Hub. Please
ensure that you know the specific location of the submission box in advance of
your first deadline.
General Advice
Before you submit any coursework you should make sure that you are clear and
up to date in your knowledge of university policy and practice.
The University’s policy on Submission of Work for Assessment (Taught
Programmes): Submission of Anonymised Work for Assessment, Word Limits
and Penalties, Extensions and Penalties for Unauthorised Late Submission,
Provisional Marks and Feedback, and Retention of Coursework is published in
the Calendar, and is available at:
https://www.uea.ac.uk/calendar/section3/regs%28gen%29/submission-of-workfor-assessment-%28taught-programmes%29
A penalty will be imposed for exceeding the word limit, which will be clearly
stated in the assignment. Students must include their word count on the
coursework coversheet when they submit their work. The word count for
coursework, written assignments, projects, reports and dissertations shall
include: Footnotes and endnotes (irrespective of whether they have been used
for the purpose of referencing or not, references (in the main text), tables and
illustrations and if applicable the abstract, title page and contents page. Any
appendicised material and the bibliography or reference list shall be excluded
from the word count.
7
Word Count Penalties
Up to 10% over word limit No Penalty
10% or more over the word limit Deduction of 10 marks off original mark
Failure to provide an electronic
copy when requested
Mark capped to the pass mark
Intentional misrepresentation of
the word count on the coversheet
Mark capped to the pass mark
Penalties for late submission
The deadline for submission of coursework is 15:00 (3pm) on the
submission/due day (21/1/2015). This is for both electronic submission and
paper copy submissions.
If you submit coursework after the published deadline, without an approved
extension, the following penalties will apply:
Work submitted Marks deducted
After 15:00 on the due date and before 15:00
on the day following the due date
10 marks
After 15:00 on the second day after the due
date and before 15:00 on the third day after the
due date
20 marks
After 15:00 on the third day after the due date
and before 15:00 on the 20th day after the due
date.
All the marks the work merits
if submitted on time (i.e. no
marks awarded)
After 20 working days Work will not be marked and
a mark of zero will be
entered
Saturdays and Sundays will not be taken into account for the purposes of
calculation of marks deducted.
For more information on coursework submission, see:
http://www.uea.ac.uk/calendar/section3/regs(gen)/submission-of-work-forassessment-(taught-programmes)
