View the step-by-step solution to:

Financial Analysis and Valuation Ford Motor Co. This template will form the basis for the financial valuation of a company of your choice. The...


I saw that you helped one of the students calculating the terminal value

I am doing the same exercise but I am using Ford Motors as an example

Below you can find the question. Also, I will appreciate solving and explaining the way to solve the terminal value

For Part II of your Module Project, the project outline, you will continue your analysis of your firm by estimating the terminal value for your firm. Calculations should include all scenarios possible using abnormal earnings methodology, as well as an equity value and asset value estimate based on Week 4’s projections and the terminal value estimated this week (refer to Table 8-6 on page 346 of your course text). Prepare a brief written summary of 2 to 3 pages, describing and justifying the methods you used in estimating terminal value and detailing your assumptions. In a table, summarise the terminal values that you will use in your final valuation analysis and that were incorporated in the spreadsheet used in previous weeks.

Financial Analysis and Valuaton Ford Motor Co. Spreadsheet cells with green text require input. Spreadsheet cells with black text are generally calculated. The Inputs tab is where most assump±on data must be entered. The Forecasts tab is generally calculated, but there are a number of forecast assump±ons that may be adjusted as appropriate. The valua±on summary tab provides the equity valua±on summary. The base model applies excess cash ²ow to debt reduc±on, as indicated by a 0 in the box to the right 1 This template will form the basis for the ³nancial valua±on of a company of your choice. The template uses Ford Motor Co. as the target company for purposes of demonstra±ng the use of the model. An important data source for company speci³c informa±on is Yahoo!Finance. Students should familiarize themselves with the extensive data available on Yahoo!Finance (or other sites like FT.COM, Google Finance, DailyFinance, etc.). The template speci³es the Yahoo! Finance pages from which informa±on can be retrieved. TO CHANGE THAT ASSUMPTION TO A CONSTANT DEBT RATIO, Enter 1 in the box to the right. Excess cash ²ow is then allocated to repurchasing stock in the future.
Background image of page 1
Ford Motor Co. 27-Apr-16 Data source: Yahoo!Finance under COMPANY > Key StaTsTcs Capital Structure Costs ±icker: F Risk free rate (10 year ±reasury) 2.38% Current stock price: $ 13.04 Equity risk premium (Historical average assumed) 7.00% Stock price date 13-Apr-16 Cost of equity 11.59% Forward annual dividend rate $ 0.60 Dividend yield 4.60% Net debt $ 97,678,000 Net interest cost $ 773,000 Shares outstanding 3,910,000,000 Cost of debt, pre tax 0.79% Equity market capitalizaTon $ 50,986,400,000 Cost of debt, a²er tax 0.57% Beta 1.31635 Data source: Yahoo!Finance underFINANCIALS > Balance Sheet Data source: Yahoo!Finance under FINANCIALS > Income Statement Balance sheet data for most recent Fscal year end 31-Dec-15 Income statement, Fscal year ended 31-Dec-15 Cash, cash equivalents, and short term investments $ 35,176,000 Sales $ 149,558,000 Net receivables $ 101,975,000 Inventory $ 8,319,000 Earnings before interest and taxes $ 11,025,000 Other current assets $ - Interest expense, net $ 773,000 ±otal current assets $ 145,470,000 Income before taxaTon $ 10,252,000 ±ax expense $ 2,881,000 Long term investments $ 3,224,000 Net income from conTnuing operaTons $ 7,371,000 Property, plant, and equipment $ 57,256,000 $ 2,000 Goodwill and intangible assets $ - Preferred stock dividends $ - Other assets + Deferred Long ±erm Asset Charges $ 18,975,000 Net income available to common stock $ 7,373,000 ±otal long term assets $ 79,455,000 Diluted earnings per share $ 1.89 ±otal assets $ 224,925,000 Net income as a % of sales 4.9% ±ax rate 28.1% Accounts payable $ 20,272,000 NOPA± $ 7,928,773 Short term debt $ - NOPA± Margin 5.3% Other current liabiliTes $ - Dividend payout rate 32% ±otal current liabiliTes $ 20,272,000 Data source: Yahoo!Finance under ANALYS± COVERAGE > EsTmates Long term debt $ 132,854,000 Analyst EsTmate Data Other liabiliTes $ 42,546,000 Current Year End 31-Dec-16 Deferred long term liabiliTes $ 502,000 EPS Average esTmate $ 1.96 Minority interest $ 15,000 Revenue EsTmate $ 14,607,000 ±otal long term liabliTeis $ 175,917,000 Next Year End 12/31/2017 $ 28,736,000 EPS Average esTmate $ 2.06 Revenue esTmate $ 148,880,000 ±otal liabiliTes & equity $ 224,925,000 Check (assets - liabiliTes and equity) $ - Next 5 years growth rate (per annum) 11.1% CalculaTons required for other tabs in the spreadsheet Net working capital $ 90,022,000 Current year esTmated net income $ 7,663,600,000.00 Other long term liabiliTes $ 43,063,000 Next year esTmated net incom $ 8,054,600,000.00 Non-recurrent income (loss) + Minority Interest (gain) Stockholders' equity + Misc Stocks OpTons Warrants
Background image of page 2
Show entire document
Sign up to view the entire interaction

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.


Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question