I was tired of using other's models so I built mine from the ground up, it's pretty much done other than the stub period. I am having a bitch of a time figuring out how to incorporate this. My date/calendarization inputs are all setup and the model automatically calculates the days between close and the next FYE (the "stub")... the issue is how to apply it relating to balance shit and cash flow items.
For example ... obivously income statement is simple, multiply next FYE revenues by the stub, keep margins constant, done. For interest i suppose you also multiply total int (from debt schedule) by stub, other than that there should be no income statement adjustments as everything else is driven by margins.
For BS and CF, here is where I am falling on my face. You cannot simply use the same working capital ssumptions for a period less than a year because since the items are driven off a stub revenue when you compare the stub period's BS items to the transaction close BS, you will have majorly distorted cash flows as it pertains to NWC items.
Basically, this model is done, but i cannot figure out the proper way to apply a stub period to BS and CF. I've deconstructed a few and feel that they mostly have done it the wrong way, keeping full year WC assumptions.
Whatever you come up with driven by income statement items, simply divide it by the stub factor. So if it is 3 months then you divide by .25. You have 1/4 of a year in revenues, so whatever number you come up with needs to be multiplied by 4, or divided by .25.
Cash flow should build off of the other two
Use LTM revenue and COGS for the working capital ratios. Cash flow should be driven off the other two except pp&e and any other unusual CF items
how the hell are you a vice president and not know how to do this?
Two reasons monaco, one.... i am on the buy side and don't have to build LBO models typically. I just decided to make one partially for grins and also because in the uncommon event i do use one, i don't like using other peoples models. Second reason is because most of the examples i have seen and explanations (such as above) are incorrect. If you are so fucking smart why don't you enlighten me?
The income statement part is simple, but you can't use balance sheet projections driven off of stub period income with the same working capital assumptions (DSO, DIH, DPO, etc.) or the change in working capital from the transaction date to the next FYE distorts your cash flows (for example, sales will be less since the stub is used instead of a full year, and you will show a corresponding decrease in current asset accounts, artifically inflating CFO).
Most models I have seen incorporate the stub into the income statement and do not adjust the balance sheet, which causes working capital to baloon.
The stub is primarily for presentation, so I would advise against forcing the model to flow through it. Keep it simple, build your model fiscal year by fiscal year, and when you're done, create an output sheet with a stub, and fill the fields manually like PTS said.
In my experience, people may glance at the LTM and the stub to make sure that everything is trending correctly. But, if you think about it, most of the figures in those columns are pretty meaningless. It's not like you can compare the figures from a 7 month stub to any of the annual figures surrounding it. So there's no reason to complicate the core model with it.
Nobody asked you, Mr. Two Years Late.
If you're using Excel 2010 or later, you can use excel's pro rata feature.
Copy the cell with the stub factor in it, highlight a cell you want to stub and Alt+F+C+N
Asperiores consequatur velit sint repellat occaecati et aut. Quod mollitia rerum omnis accusamus.
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...