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)

WSO Elite Modeling Package

  • 6 courses to mastery: Excel, Financial Statement, LBO, M&A, Valuation and DCF
  • Elite instructors from top BB investment banks and private equity megafunds
  • Includes Company DB + Video Library Access (1 year)

Comments (12)

Jun 12, 2019 - 2:41pm

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.

Learn More

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

Learn more
Jun 12, 2019 - 2:48pm

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 - 3:55pm

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 - 4:43am

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

Array

Start Discussion

Total Avg Compensation

May 2021 Investment Banking

  • Director/MD (9) $911
  • Vice President (35) $364
  • Associates (195) $233
  • 2nd Year Analyst (110) $151
  • Intern/Summer Associate (96) $145
  • 3rd+ Year Analyst (26) $145
  • 1st Year Analyst (404) $131
  • Intern/Summer Analyst (330) $82