Estimating the Value of a Company – Fiat Chrysler Automobiles (FCA) – in Excel
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.
Having Trouble Meeting Your Deadline?
Get your assignment on Estimating the Value of a Company – Fiat Chrysler Automobiles (FCA) – in Excel completed on time. avoid delay and – ORDER NOW
- 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.2022 | in $ million | |||||||||||||
| Years | 2016 | 2017 | 2018 | 2019 | 2020 | Years | 2016 | 2017 | 2018 | 2019 | 2020 | ||||
| Total Assests | 104343 | 96299 | 96873 | 98044 | 99730 | Total Assests | $117,907.59 | $108,817.87 | $109,466.49 | $110,789.72 | $112,694.90 | ||||
| Total Liability | 49469 | 47269 | 71970 | 63369 | 73869 | Total Liability | $55,899.97 | $53,413.97 | $81,326.10 | $71,606.97 | $83,471.97 | ||||
| No.of outstanding | 1526 | 1556 | 1568 | 1571 | 1572 | No.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 Flow | in million dollars | ||||||||||||||
| Year | 2018 | 2017 | 2016 | You will be using the most recent three years 2018,2019 and 2020 | |||||||||||
| Cash from Operations | 13,038 | 12031 | 9783 | ||||||||||||
| Capital Expenditure | -2463 | -2275 | -1621 | ||||||||||||
| Please note you may sometime have negative free cash flows. In that case you will not use | |||||||||||||||
| FCF | 10,575 | 9756 | 8162 | this 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 | |||||||||||||||
| Year | 1 | 2 | 3 | 4 | 5 | ||||||||||
| Projected FCF | 11526.75 | 12564.1575 | 13694.931675 | 14927.47552575 | 16270.9483230675 | ||||||||||
| PV of Projected FCF | |||||||||||||||
| Year | 1 | 2 | 3 | 4 | 5 | Total | |||||||||
| Projected FCF | 11526.75 | 12564.1575 | 13694.931675 | 14927.47552575 | 16270.9483230675 | ||||||||||
| Pvf at 10% | 0.90909 | 0.8264 | 0.75132 | 0.68301 | 0.62092 | ||||||||||
| PV | 10478.8531575 | 10383.019758 | 10289.276066061 | 10195.6150588425 | 10102.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 Value | 10102.9572X10 | 101,029.57 | |||||||||||||
| Total of PV of projected FCF and Terminal Value | 152,479.29 | ||||||||||||||
| Cash and Cash Equivalent | 1,778 | ||||||||||||||
| Intrinsic Value of company ( Total PV of Projected FCF+ Terminal Value+Cash and Cash Equivalents) | 154,257.29 | 154.2572936008 | Billions | ||||||||||||
| Inrinsic Value of a share | |||||||||||||||
| No. outstanding shares 1,103,903,507 | 1,104 millions | 139.725809421 | per share intrinsic value | ||||||||||||
| Or round it to | |||||||||||||||
| $140 per share |
