jagomart
digital resources
picture1_Dcf Template


 119x       Filetype XLSX       File size 0.03 MB       Source: web.stanford.edu


File: Dcf Template
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 lineitems on ...

icon picture XLSX Filetype Excel XLSX | Posted on 16 Aug 2022 | 3 years ago
Partial file snippet.
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












Sheet 2: Income Statement
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


Sheet 3: Working Capital Calculation
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.


The words contained in this file might help you see if this file matches what you are looking for:

...Sheet dashboard note to the blyth user whoever reading this as a template please that for each dcf you do breakdown of lineitems on financial statements will vary from company and ought be customized accordingly consult sec filings bloomberg or googleyahoo finance obtain statement data rigorous further break down conventional gaap line items incorporate even more granular assumptions we strongly suggest so if have any questions not hesitate contact director your portfolio manager help happy modeling andrew han in mm s source k filing google blue are webmd ticker wbmd current price terminal growth rate market risk premium nyu prof estimate httppeoplesternnyueduadamodar riskfree yr treasury httpwwwbloombergcommarketsratesbondsgovernmentbondsus wacc fair appreciation thesis notes patent cliff is facing many pharmaceutical companies cause them slash marketing budgets which poses real threat revenue where would important ve included model income mms historical projected projections highligh...

no reviews yet
Please Login to review.