MIM700

ValuationFIN 449Michael Dimond

Michael DimondSchool of Business Administration Valuation The Big Picture

Michael DimondSchool of Business Administration

Start from the bottomFor now, put the FCFE figures on row 250, as shownAdd the appropriate labels and supporting data, then build cells to compute discounted valuesComplete the valuation detailsSum of PVsCashTotal value, per share value, market price, etc.

Michael DimondSchool of Business Administration

Create an area for sensitivity analysisThe top row references the terminal growth rate in your valuation, with an incremental change on either side.The Ke figures are driven by the Rf and MRP shown, but is replaced with the New in column E. Again, this is driven with some incremental change.

Michael DimondSchool of Business Administration

How the sensitivity analysis worksEach cell references the FCFE figures and discounts them, then adds the cash to the total, then divides by the number of shares.Use absolute references (such as $H$253) so you can fill the formula into the entire table.The yellow cell in the middle has this formula inside:=($H$253+$I$250/(1+$F266)^$I$249+$J$250/(1+$F266)^$J$249+$K$250/(1+$F266)^$K$249+$L$250/(1+$F266)^$L$249+ $M$250/(1+$F266)^$M$249+($M$250*(1+J$261)/($F266-J$261))/(1+$F266)^$M$249)/$H$255

Michael DimondSchool of Business Administration The formula is not as daunting as it seemsThe formula in the yellow cell is easier to build with point & click than with typing=($H$253+$I$250/(1+$F266)^$I$249+$J$250/(1+$F266)^$J$249+$K$250/(1+$F266)^$K$249+$L$250/(1+$F266)^$L$249+$M$250/(1+$F266)^$M$249+($M$250*(1+J$261)/($F266-J$261))/(1+$F266)^$M$249)/$H$255

All of that divided by the number of sharesClose the parentheses to group the total valueOpen parentheses to group the total valueCashFCFE1 discounted for 1 periodFCFE2 discounted for 2 periodsFCFE3 discounted for 3 periodsFCFE4 discounted for 4 periodsFCFE5 discounted for 5 periodsTerminal valuediscounted for 5 periods

Michael DimondSchool of Business Administration

Fill the formula into the cells in the tableYour result should give the same values as the example above.

Michael DimondSchool of Business Administration

Good work! Now save your changesThe next step is to build the area which drives our FCFE & FCFF figures. This will be placed above the area you just built.

Michael DimondSchool of Business Administration Label the rows which will detail FCFF & FCFEI used rows 190 240 for the details. Starting from the top of this section, my labels are as shown:

Michael DimondSchool of Business Administration Valuation The Big Picture

Michael DimondSchool of Business Administration