Final Valuation

Guides • Finance & Accounting • 08/09/2019

The assumptions and final pro-forma model are shown below:

Info: Worksheet (and page JavaScript) may take time to load

Tip: Double click a cell to view its formula

Income Statement and Balance Sheet

As the company is profitable, there is no need to model in any accumulated losses that could be used to reduce future taxable losses, and tax liabilities. If this were not the case, these would need to be factored into the pro-forma.

Valuing Caterpillar

Assuming that the WACC of Caterpillar is 10.84% and assuming that the long term unlevered FCF growth is 9%, this produces a valuation of 86.02 per Share.

Note: If you’re not familiar with certain Corporate Valuation concepts and methods, such as WACC, you can read more about the topic, and different valuation methods, often used in practice, here: Corporate Valuation Concepts

Note that while the NPV formula assumes that all cash flows occur at the end of the year, it is more logical to assume that these occur smoothly throughout the year. For discounting purposes, we should therefore discount cash flow, as if, on average, they occur in the middle of the year. This is why the NPV formula is multiplied by (1 + WACC) ^ 0.5

According to these values, Caterpillar is slightly overvalued (when comparing our computed DCF value, to the company’s Market Price per Share of 90.6 per Share)

Sensitivity Analysis

A sensitivity analysis on the long-term growth rate and weighted average cost of capital produces the sensitivity table shown (see above).

Cells with a valuation greater than the current market value of 90.60 are highlighted, and where the WACC is < long term growth, the cells have been blanked since that does no accord with the valuation model.

For some combinations, the model predicts the value of equity to be negative – meaning that Caterpillar’s year 0 debt outweighs the value of its equity.

Concluding Remarks

As we have seen from this exercixe, on the one hand here, creating such a pro-forma financial model can be an extraordinarily labour-intensive method for valuing a firm. On the other hand, when modelling Caterpillar, we have discovered much about the way the firm operates, and about how much we understand (and don’t), about its financial statements.

This kind of pro-forma modelling technique cannot be undertaken lightly. But if an intensive investigation into a firm’s workings is needed, this is the way to go!

Valuation Template

If you would like to open the Valuation Template illustrated throughout this guide, in your own local spreadsheet software, you can download the full file here: Corporate Valuation Template - Caterpillar