Filetype Excel XLSX | Posted on 16 Aug 2022 | 3 years ago
Authentication
digital resources
122x Filetype XLSX File size 0.03 MB Source: web.stanford.edu
Sheet 1: Dashboard
| Note to the Blyth user | |||||||||||||
| To whoever reading this as a template, | |||||||||||||
| * Please note that for each DCF that you do, the breakdown of the line-items on the financial statements will vary from company to company and ought to be customized accordingly. | |||||||||||||
| * Please consult SEC filings, Bloomberg, or Google/Yahoo! Finance to obtain financial statement data. | |||||||||||||
| * A rigorous DCF will further break down the conventional GAAP line items to incorporate even more granular assumptions. We strongly suggest that you do so. | |||||||||||||
| If you have any questions, please do not hesitate to contact a Director or your Portfolio Manager for help. | |||||||||||||
| Happy modeling, | |||||||||||||
| Andrew Han | |||||||||||||
| DCF Dashboard | |||||||||||||
| in $ mm's | |||||||||||||
| Source | 2013 10K SEC Filing, Google Finance, and Bloomberg | ||||||||||||
| In blue are assumptions | |||||||||||||
| Company | WebMD | ||||||||||||
| Ticker | WBMD | ||||||||||||
| Current Price | $48.35 | ||||||||||||
| Terminal Growth Rate | 2.00% | ||||||||||||
| Market Risk Premium | 6.00% | NYU prof estimate. http://people.stern.nyu.edu/adamodar/ | |||||||||||
| Risk-Free Rate | 2.30% | 10-yr treasury. http://www.bloomberg.com/markets/rates-bonds/government-bonds/us/ | |||||||||||
| WACC | 5.75% | ||||||||||||
| Fair Price | $44.05 | ||||||||||||
| Appreciation | -8.89% | ||||||||||||
| Thesis and Notes | |||||||||||||
| Thesis | Patent cliff that is facing many pharmaceutical companies will cause them to slash marketing budgets, which poses a real threat to WebMD revenue. | ||||||||||||
| Notes | This is where you would note important assumptions that you've included in your model |
| Income Statement | |||||||||||
| in mms | Historical | Projected | |||||||||
| 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | Notes | ||
| In blue are projections, highlighted in grey are assumptions | |||||||||||
| Public Portal Advertising and Sponsorship | 447 | 477.3 | 391.3 | 433.2 | 476.52 | 524.17 | 576.59 | 634.25 | 697.67 | These are WebMD specific revenue segments. Ideally you want to break it out for your company | |
| % change | 6.78% | -18.02% | 10.71% | 10.00% | 10.00% | 10.00% | 10.00% | 10.00% | |||
| Private Portal Services | 87.6 | 81.5 | 78.5 | 82.1 | 86.21 | 90.52 | 95.04 | 99.79 | 104.78 | This way it's a lot more granular than just projecting out total revenue | |
| % change | -6.96% | -3.68% | 4.59% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | I obviously didn't incorporate great assumptions (10% and 5%) | ||
| Revenue | 534.6 | 558.77 | 469.87 | 515.29 | 562.73 | 614.69 | 671.63 | 734.04 | 802.46 | Of course, revenue segment data is a lot harder to find. Dig in the SEC filings or go to the Bloomberg terminals | |
| % change | 4.52% | -15.91% | 9.67% | 9.21% | 9.23% | 9.26% | 9.29% | 9.32% | |||
| COGS | 187.83 | 201.68 | 216.36 | 209.74 | 230.72 | 252.02 | 275.37 | 300.96 | 329.01 | ||
| % revenue | 35.13% | 36% | 46% | 41% | 41% | 41% | 41% | 41% | 41% | Since it's September 2014 when I'm putting this together, two quarters have already passed in 2014. YTD information can help guide annual projections. Use common sense and compare your assumptions against YTD performance. | |
| Gross Profit | 346.77 | 357.09 | 253.51 | 305.55 | 332.01 | 362.67 | 396.26 | 433.08 | 473.45 | ||
| % gross margin | 64.87% | 64% | 54% | 59% | 59% | 59% | 59% | 59% | 59% | ||
| Sales & Marketing | 120.87 | 124.33 | 127.66 | 128.00 | 140.68 | 153.67 | 167.91 | 183.51 | 200.61 | ||
| % revenue | 22.61% | 22.25% | 27.17% | 24.84% | 25.00% | 25.00% | 25.00% | 25.00% | 25.00% | Assumption: they will need to increase marketing to drive advertising revenue to compete with FB and the like | |
| General & Administrative | 85.50 | 91.27 | 97.62 | 93.22 | 90.04 | 98.35 | 107.46 | 124.79 | 136.42 | ||
| % revenue | 15.99% | 16.33% | 20.78% | 18.09% | 16.00% | 16.00% | 16.00% | 17.00% | 17.00% | ||
| SGA | 206.37 | 215.60 | 225.28 | 221.22 | 230.72 | 252.02 | 275.37 | 308.30 | 337.03 | ||
| % revenue | 38.60% | 38.58% | 47.94% | 42.93% | 41.00% | 41.00% | 41.00% | 42.00% | 42.00% | ||
| R&D | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| D&A | 27.58 | 26.80 | 28.40 | 26.61 | 28.00 | 28.00 | 28.00 | 28.00 | 28.00 | Keeping this constant; fairly conservative given past numbers | |
| % revenue | 5.16% | 4.80% | 6.04% | 5.16% | 4.98% | 4.56% | 4.17% | 3.81% | 3.49% | ||
| EBITDA | 140.40 | 141.49 | 28.23 | 84.33 | 101.29 | 110.64 | 120.89 | 124.79 | 136.42 | ||
| EBIT, or Operating Income | 112.82 | 114.69 | -0.17 | 57.73 | 73.29 | 82.64 | 92.89 | 96.79 | 108.42 | ||
| Interest Income | 3.95 | 0.11 | 0.09 | 0.08 | 0.08 | 0.08 | 0.08 | 0.08 | 0.08 | Keeping this constant as an assumption | |
| Interest Expense | 11.45 | 20.65 | 23.33 | 22.83 | 22.83 | 22.83 | 22.83 | 22.83 | 22.83 | ||
| Net Loss (Gain) from Interest | 7.50 | 20.53 | 23.25 | 22.75 | 22.75 | 22.75 | 22.75 | 22.75 | 22.75 | ||
| Loss (Gain) on Convertible Notes | 23.33 | 0.00 | 0.00 | 4.87 | Struck out all of this because these are unusual expenses difficult to forecast individiually | ||||||
| Gain (Loss) on Investments | 9.52 | -18.52 | -8.07 | 0.00 | |||||||
| Restructuring | 0.00 | 0.00 | 7.58 | 0.00 | "Unusual expenses" are not really common | ||||||
| Transaction, severance, and other expense | 0.07 | 2.33 | 2.30 | 1.35 | |||||||
| Unusual Expenses | 32.92 | -16.19 | 1.80 | 6.22 | 2 | 2 | 2 | 2 | 2 | In general, recurring "unusual expenses" is a red flag for value investors | |
| Earnings Before Taxes (EBT) | 72.40 | 110.35 | -25.22 | 28.75 | 48.54 | 57.89 | 68.14 | 72.04 | 83.67 | ||
| Tax | 20.04 | 46.17 | -2.13 | 13.64 | 17.22 | 18.81 | 20.55 | 21.21 | 23.19 | ||
| % tax rate | 14.28% | 32.63% | 0.00% | 16.17% | 17.00% | 17.00% | 17.00% | 17.00% | 17.00% | Tax rate is zero if earnings are negative | |
| Extraordinary Items | -1.8 | -10.4 | -2.7 | 0 | 0 | 0 | 0 | 0 | 0 | Literally "extraordinary" so don't bother modeling it out unless there's some semblance of a recurring nature. Pretty much here only to reconcile historical income statements | |
| Net Income | 54.2 | 74.6 | -20.4 | 15.1 | 31.3 | 39.1 | 47.6 | 50.8 | 60.5 |
| Balance Sheet | |||||||||||||
| in $ mms | Historical | Projected | |||||||||||
| 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | Notes | ||||
| Accounts Receivable | 134.45 | 121.33 | 106.62 | 124.23 | 121.66 | 118.46 | 117.74 | 120.52 | 119.60 | Crude approximation through averaging | |||
| Inventory | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | There is no inventory, I'm guessing, because WebMD is a software company and therefore doesn't have actual goods to sell | |||
| Accounts Payable | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | Guess they don't have short-term debt either… | |||
| Working Capital | 134.45 | 121.33 | 106.62 | 124.23 | 121.66 | 118.46 | 117.74 | 120.52 | 119.60 | ||||
| Change in Working Capital | 13.12 | 14.71 | -17.61 | 2.57 | 3.20 | 0.72 | -2.78 | 0.93 | The working capital calculation is supposed to be non-cash working capital. | ||||
| which is calculated as AR - AP + Inventory. | |||||||||||||
| Note, this is different from the numbers you'd find on Google or Yahoo Finance | |||||||||||||
| and also different from the general definition of "current assets - current liabilities" | |||||||||||||
| The motivation for this is that we want to use non-cash working capital. |
no reviews yet
Please Login to review.
