Modelling Gurus, please share your wisdom...

Hi all,

New monkey here - I've been getting a lot of valuable advice from the WSO community and I hope I can build my career further and give back one day. Thank you Patrick!

I have a quick question on 3 financial statement modeling.

It seems like for modeling tests (for lateral hires) candidates are often asked to 'build an operating 3 statement model from scratch', after being given 10K / Qs and annual reports. I've been practicing this lately (I am not actively looking but I want to feel comfortable doing this, since my understanding is that this is what is expected of a first/second year analyst) but always run into a problem where I find items that don't fall into the usual category of what I expect to see in a BS / CFS. For instance, Pfizer has 'benefit plan contributions in excess of expense' and 'loss on sale of XXX assets' in the BS and Nike has 'Tax payments for net share settlement of equity awards' in the CFS.

I understand that due to time constraint and limited information I am supposed to 'bundle' some accounts and link the BS and CFS together (and I understand how to do this once I bundle the items), but I am not sure which ones I should bundle as 'other' (and keep it constant / 0, I suppose?). Because I don't know which of these rogue items in the CFS affect which item / items in the BS, I cannot decide on which to bundle and which not to, and my model would never balance as a result. Am I perhaps overcomplicating this?

I have searched around the forum / web to see if there is a good answer to the above but I was not able to find something particularly helpful.

Any help or advice would be greatly appreciated!
(For those who have an example of an operating model that was built from scratch based on a 10-K, I would be very thankful it if you could please share the file through DM)

Comments (12)

Most Helpful
Jun 12, 2019

My get MSed but on model tests unless I can create a supporting schedule for a BS item or its a NWC item (AR/AP/INV) then I usually group them into "other current/noncurrent assets/liabilities" and hold them constant.

    • 4
Jun 12, 2019

Thanks for the input. Really appreciate it.

Would you say the same for CFS items? Group them as 'other operating / investing items'?

Jun 12, 2019

Well the CFS items should reflect the balance sheet given you will need the CFS to get the BS to balance so you really just need to figure out the BS and the CFS will work itself out.

Jun 12, 2019

I think these are accounting questions.

Benefit plan contributions in excess of expense
I think this is pension accounting, but it looks like the excess contributions would make this an asset account and not affect your cash flow.

Loss on sale of XXX assets
This would be an expense item as a plug, but there's no cash movement except for the amount received on the sale.

Tax payments for net share settlement of equity awards
This would be an actual cash payment, which would debit expense and credit cash.

    • 1
Jun 12, 2019

Thanks, you are right. Understanding what these items actually are would be the best scenario.

May I ask how you would approach unusual items in a time-pressured environment? Surely you would not be expected to go read note 21 in a modeling test (or are you?). Also in most cases when I try to understand the BS movements through the historical CFS this is not super straightforward, since only the cash portion of a related BS item would show in the CFS.

I understand that the correct answer might be to know my accounting, but just wanted to confirm if this would be the expectation for a modeling test.

Thank you again for taking the time to look into this!

Jun 12, 2019

I come from an accounting background, but if you could discern from the descriptions what should and shouldn't move among the three statements that would help. Then if the change doesn't impact your model and you're short on time you can probably group it into an "other" item.

I would also know cash flow statements and what BS and IS items are actually cash movements. Try to think logically "is cash actually transferred?" when you see changes in accounts.

Jun 13, 2019

Good thread. Encountered the same issues while practicing modeling myself.

Jun 13, 2019

Even at an actual PE, unless those amounts are very significant, we usually group them together and depending on the nature of it, either (i) flatline it or (2) drive it off revenue or sth. No need to sweat over smallest details.

    • 1
Jun 21, 2019

Thanks for the insight! I've resolved the issue through i) bundling items as 'other assets/liabilities' or 'other operating items', ii) flat-lining and iii) feeding them to equity if unsure.

Jun 14, 2019
    • 1