If you have monthly financial data that is feeding into your model do you typically build your models with everything calculating monthly or just aggregate and have your model calculate everything quarterly or annually?
And if you do calculate everything monthly, how in the world do you build the model so that it can actually calculate sensitivity tables in a reasonable time? I could see quarterly being a compromise but that still seems like it a ton of calculations that would take quite a long time.
I'm presuming you are referring to sensitivity tables in an LBO model?
yeah. The number of calculations from the sensitivity tabels balloons pretty quickly
You can only do monthly with access to confi'd management. I wouldn't get too hung up on it.
When I build preliminary LBO models for our deals, I'll just do yearly projections and go 5 years out. If we decide to sign an LOI, I'll include monthly calculations for everything (IS, BS, cash flows, and debt schedule). Sometimes I'll do a revenue build that feeds into all of it, where I'll calculate and show monthly historical, actual, and projected rev/gp by customer. I'll usually just do monthly projections a year out from time of close, then yearly after that.
I'll usually leave sensitivity tables out of the LBO model for monthly. I have an IRR page that does a terminal value calc, ownership summary, and equity IRR based on the model output and ownership structure. There's really no need to do sensitivity calcs by month if you are only projecting monthly one year out.
Could you provide an example of your model?
How do you model quarters and years together? (Originally Posted: 12/26/2011)
If you model QQQQYQQQQYQQQQY you can't drag formulas straight across and also have to skip four columns to calculate yearly ratios, which wastes time. Is there a better way to do this?
One friend suggested building different tabs--quarters in one tab and then their sums in another tab. This could work, but seems cumbersome.
Another suggestion I received is to model the quarters first and then add in years (Year = sum(Q1,Q2,Q3,Q4)), but that also seems cumbersome and makes repairing formulas difficult for the same original reason.
Any thoughts or experience? How do most bankers do it?
Recently had this pop up for me as well - built out a quarterly operating model but had to show outputs etc in annual. Simple solution is having a quarterly operating model and then some sort of annual output tab where you roll up the quarterly data using a "sum-if" function. So for example, you would have 2011 - 2014 as your headers and then use these values to drive your sum-if calculation. Trick is to have a grouped row in your quarterly operating model that spits out the "year" for each quarter (think a "year" function should do this). Hope this helps.
With shortcuts this shouldn't take you long at all...
Q's in one row Y's in one down do repeat action then shift the Y's up and the actions will follow
This is seems to be a difference between bankers and capital markets people - bankers, realizing the pain-in-the-arse nature of doing what you're describing, will do QQQQ QQQQ QQQQ and then YYY off to the side - cap. markets people prefer the other way.
in terms of modeling, i would do the quarters first with a spacer for the annuals if you MUST do it in this format, and then do the annuals later.
^ what he said, just interlace the Qs and Ys for the optics
Leave the data as is. Add 1s and 4s. Sumproduct.
easiest way I have done it is just do the full model with quarters with =sum(q1,q2,q3,q4) then at the end add the full years just for display purposes. just make sure your CF statment still works
that makes it much easier to calc LTM numbers too
Great advice, almost as good as necro'ing a dead thread.
Voluptatem ut recusandae occaecati blanditiis laudantium cum non. Et voluptatem odit excepturi quasi suscipit quae eveniet explicabo. Ipsa earum nihil et nobis et cumque.
Praesentium omnis ea deleniti quo animi dolores. Mollitia est laboriosam libero id eos. Ipsam aut architecto assumenda aut et.
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...