Quarterly Financial Model
I'm looking to create a sell-side equity research style report for a stock I'm researching (to eventually get a job in ER). I've built an annual DCF model and would like to add a quarterly EPS forecast.
Do I need to re-do my model on a quarterly basis (i.e. create a quarterly B/S) or can I just hardcode my quarterly forecasted I/S numbers based on prior year trends and make sure they add up to my annual forecasted numbers?
If anyone knows a good resource (e.g. text book or website) or can send an example model, I'd greatly appreciate it.
If you can build an annual model...you can build a quarterly model without much added work/complexity.
Do not hardcode forecasts. start the forecast from scratch and have the final column just sum up the 4 quarters. As for quarterly modeling it is a but more tricky as seasonal trends take into affect. If you have access to ER reports somehow I would recommend looking through them. Otherwise there is really no guide for quarterly modeling so just apply annual guides to quarterly. I used WSP to really enhance my modeling
bump
It will require more work as there are seasonal differences. Also, generally when I'm making a quarterly model I like to have half-years in as well where H1 is Q1 + Q2 and the full year is H1 + H2...seems the most intuitive. Don't hardcode in the forecast
I'm also interested in the layouts used for quarterly modeling in the US for equity research. As a foreigner, we only have semi-annual reporting and the disclosures for semi-annual periods tend to be much lower quality than full year disclosures, this means that if you use semi-annual periods as the base for your model you tend to have less granular information with which to forecast.
As such, we tend to generate annual models and then do some limited side work to come up with LTM run rates, and track half yearly results relative to trend.
It seems that in the US the 10q levels of disclosure are significantly better, as such, I'm particularly interested in whether in the US, you: 1. Generate the full model on a quarter by quarter basis, i.e. using the level of detail available in the segment disclosure in the 10Q?; 2. Generate annual period models and segment / forecast 1-2 annual periods around the current balance date?; or 3. Do something similar to what I outlined above and do the majority of the work on an annual basis and then do some limited quarterly work to assess trend / run-rate etc?
I'm also keen to understand best practice for generating these quarterly forecasts in your model, do you have a quarterly model tab and a separate annual model tab or are the two merged in some way?
This is something that doesn't seem to be addressed in modeling courses as they tend to only be superficial annual period models with simple hardcoded growth rates for key drivers, I'm keen to get an understanding of how people actually work on real investment ideas.
Any thoughts for those in the industry would be much appreciated.
Modeling with Quarters (Originally Posted: 09/27/2016)
For 3 Statement Models, where quarters are used, how do you usually organize the financial statements? Q1|Q2|Q3|Q4|Annual|Q1|Q2|Q3|Q4|Annual or Q1|Q2|Q3|Q4|Q1|Q2|Q3|Q4| |Annual|Annual|
Do you need to link the statements twice, once for quarters and once for annuals? Or do you just link the quarters and then sum up to get the annuals? It seems like usually quarters are only projected for a few years, and after that it's on an annual basis, so you would have to link the 3 statements twice?
Thanks
I usually have a separate tab to keep things clean, with for example "Q_FS" containing the 3 statements on the qty basis and "A_FS" summing the "Q_FS" lines for the IS, CFS, just be careful you don't sum balances in your annual balance sheet
I usually do the latter and then also have a separate summary output page showing just annuals.
I usually use the first style, and group the quarterlies so I can just show it annually if I want.
Sunt quia blanditiis sit quisquam minima qui omnis. Aliquid omnis voluptas magni explicabo provident officiis. Totam alias eum omnis iure consequatur rerum.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...