Explore BrainMass

Spreadsheet problem

See attached files.

Using the spreadsheet and the data therein, calculate the values for the 30 cells in the three boxes at the bottom of the sheet.

First calculate the FCFs, starting with Operating Income After Tax (sometimes called NOPAT, the acronym for Net Operating Profit After Tax) and make the necessary calculations to arrive at the Free Cash Flows (FCFs) for the years 2001-2006. Assume that Free Cash Flows beyond 2006 grow at a rate of 6%, using the FCF in 2006 as the base.

Then calculate a terminal value two ways - using a constant growth rate DCF model and a TEV/EBITDA multiple. For the latter, use a multiple of 7.3X, which should tie back to the Comparable Company data. Next, use the data provided in the left-hand column at the bottom of the page to calculate the estimated WACC for ABC. Finally, calculate the PV of the two sets of FCFs ( FCFs for 2001-2006 and the two terminal values). How do these values compare to one another and to the valuations obtained from the Comparable Company Valuation Analysis?