Question
In your Excel spreadsheet, please add 2 tabs: one having a bullish valuation, and one having a bearish valuation. Your rows should be: revenue growth
In your Excel spreadsheet, please add 2 tabs: one having a bullish valuation, and one having a bearish valuation. Your rows should be: revenue growth (2011-2015); operating margin (2011-2015); NWC turnover (2011-2015); FA (Fixed Assets) turnover (2011-2015); WACC (1 value); tax rate (1 value); revenues (2011-2015); EBIT (2011-2015), NWC (2011-2015); Fixed Assets (2011-2015); ROC (2011-2015); NOPAT (2011-2015), -delta NWC (the change in net working capital from last year (2012-2015)), -delta FA (the change in fixed assets from last year (2012-2015)), and the sum of the 3 previous lines should be your FCF. Remember that FCF is your goal in valuation (there are two flavors of FCF - we focus on the firm-wide FCF. We will see later equity-FCF, but both flavors should give the same valuation). The following formula might help: firm-wide FCF = NOPAT - delta NWC - delta FA. Come up with a plausible terminal value, discount to time zero and sum. Subtract the debt, divide by the number of shares outstanding, and that will give you your implied price. Compare it to the current market price.
Pleas help me complete the tables
bullish | ||||||||
2011 | 2012 | 2013 | 2014 | 2015 | WACC | 9,61% | ||
Revenue Growth | 5,3% | 1,0% | 1,5% | 2,0% | 2,5% | Tax Rate | 39% | |
Operating Margin | 9,4% | 9,0% | 9,0% | 9,0% | 9,0% | |||
NWC turnover | 5,02 | 6,00 | 6,50 | 7,00 | 7,50 | |||
Fixed Assets turnover | 1,95 | 1,95 | 1,95 | 1,95 | 1,95 | |||
Revenues | 1677 | |||||||
EBIT | 157 | |||||||
NWC | 334 | |||||||
Fixed Assets | 859 | |||||||
ROC | ||||||||
NOPAT | ||||||||
Delta NWC | ||||||||
Delta FA | ||||||||
FCF |
bearish | ||||||||
2011 | 2012 | 2013 | 2014 | 2015 | WACC | 9,61% | ||
Revenue Growth | 5,30% | 0,00% | 0,00% | 0,00% | 0,00% | Tax Rate | 39% | |
Operating Margin | 9,40% | 8,00% | 7,00% | 6,00% | 5,00% | |||
NWC turnover | 5,02 | 6,00 | 6,50 | 7,00 | 7,50 | |||
Fixed Assets turnover | 1,95 | 1,95 | 1,95 | 1,95 | 1,95 | |||
Revenues | 1677 | |||||||
EBIT | 157 | |||||||
NWC | 334 | |||||||
Fixed Assets | 859 | |||||||
ROC | ||||||||
NOPAT | ||||||||
Delta NWC | ||||||||
Delta FA | ||||||||
FCF |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started