Estimating the Value of a Company – Fiat Chrysler Automobiles (FCA) – in Excel Calculate :  Asset valuation approach :   For this method, we determine t

Calculate : 

  • Asset valuation approach :   For this method, we determine the company’s book value or net asset value derived from the account balances on the balance sheet by taking the sum of the value of the assets and then deducting the total liabilities to arrive at a value for net assets.

 

  • Hybrid valuation approach: For this method we can combine the asset valuation approach with the income approach to arrive at an estimated value. This rather comprehensive estimate can be a challenge, as it ideally considers all aspects of a business, both tangible and intangible, and may be very different from the market value. This might also be referred to as estimating the company’s intrinsic value.

Sheet1

1.Assets valuation approach :
Net Assets method = Total Assets- Total liabilities
Net Assets per share = Net Assets / No. of outstanding share
Fiat Chrysler Automobiles N.V ( in € million )1 euro equals 1.13 United States Dollar as of 02.01.2022in $ million
Years20162017201820192020Years20162017201820192020
Total Assests10434396299968739804499730Total Assests$117,907.59$108,817.87$109,466.49$110,789.72$112,694.90
Total Liability4946947269719706336973869Total Liability$55,899.97$53,413.97$81,326.10$71,606.97$83,471.97
No.of outstanding15261556156815711572No.of outstanding$1,724.38$1,758.28$1,771.84$1,775.23$1,776.36
3. Hybrid valuation approach ( estimating the company’s intinsic value )
Home Depot ( as an example ) Fiat can be done same way but for 5 years
Intrinsic Value of a Company
Future cash Flowin million dollars
Year201820172016You will be using the most recent three years 2018,2019 and 2020
Cash from Operations13,038120319783
Capital Expenditure-2463-2275-1621
Please note you may sometime have negative free cash flows. In that case you will not use
FCF10,57597568162this method. This will be a good justification for not selecting this method. Of course you need to
show calculations.
You can use arbitrary growth rate and justify it
OR
Compute Compound Annual Growth Rate (CAGR)
CAGR=(EV/BV)^(1/N)-1
0.0901709251
Compute Projected FCF using the CAGR
Year12345
Projected FCF11526.7512564.157513694.93167514927.4755257516270.9483230675
PV of Projected FCF
Year12345Total
Projected FCF11526.7512564.157513694.93167514927.4755257516270.9483230675
Pvf at 10%0.909090.82640.751320.683010.62092
PV10478.853157510383.01975810289.27606606110195.615058842510102.9572327591
Toal PV of projected FCF$ 51,449.72
Terminal Value = PV of FCf of the year 5 X 10
(It is subjective. Generally take 10 or 12 times of the project FCF of 5th year)
Terminal Value10102.9572X10101,029.57
Total of PV of projected FCF and Terminal Value152,479.29
Cash and Cash Equivalent1,778
Intrinsic Value of company ( Total PV of Projected FCF+ Terminal Value+Cash and Cash Equivalents)154,257.29154.2572936008Billions
Inrinsic Value of a share
No. outstanding shares 1,103,903,5071,104 millions139.725809421per share intrinsic value
Or round it to
$140 per share

Looking for this or a Similar Assignment? Click below to Place your Order