Welcome to my DCF Model! The posted script currently is only functional for forecasting on Apple's Discounted FCF. The script is still very much a work in progress but I encourage anyone to look at it for inspiration and to take as they please. The script currently pulls archived 10-K data using Financial Modeling Prep's free APIs (link -> https://financialmodelingprep.com/developer/docs/).
After creating a data frame and running a vertical analysis on some metrics used in FCF forecasting, the model builds out Revenue, EBIT, Taxes, Dep & Amtz, CapEx, and NWC over a five-year period. Free cash flow, or FCF, is calculated as EBIT - Taxes + Dep&Amtz + CapEx + NWC. FCF is then discounted using PV$1 table using the U.S. Treasury 10-yr rate. Some methods discount FCF using WACC and TGR and in the future I plan on implementing a feature to support that analysis as well. The next step in the analysis is to calculate the PV of terminal value (TV). This is done by taking the PV (Final Year FCF * 1+TGR / (WACC - TGR)). The next step is to calculate the enterprise value (Sum of Discounted FCF + PV(TV)). Next, we calculate the equity value which is equal to enterprise value + cash/equiv - debt. Finally, Equity Value / Shares outstanding = Implied Share Price.
#Planned Improvements:
- Rev Growth for index 0 is currently a hard coded number. The reason for this is because revenue growth is calculated using the current years revenue and last years. Financial Modeling Prep's free API only allows for 5 years of 10-K data which leaves out calculating that first years Rev Growth. To fix this one could swap to the pro version of Financial Modeling Prep's API or web scrape the data.
- TGR, WACC, and 10yr rate are currently user inputs/hard coded. This could be easily fixed using a pro version of Financial Modeling Prep's API or web scrapped.
- The build-too rates for Rev Growth, Tax Rate, and EBIT%Rev are user inputs. This is not an immediate problem but does require some professional judgment to determine fair growth rates.
- Currently the script only supports a DCF for Apple using 10-Ks. With a couple tweaks the script could easily be adapted to model any desired public company.