Hi,
I am learning the 3-Statement Model by myself and I have some difficulty making projections for some line items such as "marketable securities," "share-based compensation expense," and "common stock." I was wondering if someone who had already done some 3-Statement Models on big companies like Apple, Walmart, etc. could send some Excel files of them so I could use these spreadsheets as reference.
Thanks in advance
| +145 | How to stop feeling like shit for not making it in IB? | 55 | 4h | |
| +108 | If Tik Tok is forced to sell, what banks do you think would be involved in the deal? | 53 | 13m | |
| +67 | Updated LA Banking Scene (2024) | 49 | 14h | |
| +38 | Ranking banks that went under | 20 | 2h | |
| +34 | Burnt Out M&A ASO | 18 | 2s | |
| +32 | A strategy for SA applicants late to the game like myself | 15 | 7h | |
| +31 | UBS Actual Buyside Exits 2024 Post-Integration | 9 | 3d | |
| +29 | Relevance of A-Levels for U.K. London recruiting | 21 | 13h | |
| +28 | Recruiting in the Fall | 5 | 2h | |
| +26 | Series 79 Help / Tips to Pass The First Attempt | 11 | 1d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
Just keep them flat
If "Marketable Securities" is a straight line in the Cash Flow Statement, can I count it as part of "Cash" in the Balance Sheet? Also, should I count "Commercial Paper" as part of "Debt" in Liabilities?
I think often it will have a line for cash and marketable securities (which are cash like I believe).
Yes commercial paper can go in debt, since it is debt. You could separate it out or not.
Ok, thank you. The problem is that the Balance Sheet still doesn't balance, can I PM you the spreadsheet so you can look at what's wrong?
Cash and Marketable Securities are generally interchangeable. Marketable Securities are really just short-term assets like money market funds where corporates will put the excess cash they don't need immediately so that it isn't earning 0% interest. You could hold Marketable Securities flat or (probably better), just create a single line item "Cash and Marketable Securities".
Stock-based Compensation is a bit trickier as it is a component as it is a strategic decision by the company whether to pay employees in cash or shares. Overall you'll have a driver for your total salaries and a split between cash and share-based.
Common Stock should be impacted by and share buybacks you're modelling or equity issuances, but also (to the point above) stock-based compensation. Recall that because Stock-Based Compensation is an expense on your income statement, it reduces your net income. To make the balance sheet balance, you need to increase paid-upp capital (/ common stock)
I find Marketable securities in the Cash Flow Statement, if it is considered one line the the balance sheet (with cash), should I grow it with revenue or continue in a straight line? And is the value of marketable securities on the Cash Flow Statement going to be an input for any line of the balance sheet?
For the purposes of your modelling, I'd just consolidate the two line items and think of marketable securities like cash. Entries on the Cash Flow Statement reflect purchases or sales of marketable securities (which reduce (increase) cash, which is one current asset, and increase (reduce) marketable securities, another current asset). If we combine the line items, the net result is zero, so you don't need any future entries in your cash flow statement
Ipsa et enim vel adipisci voluptas nostrum ea. Autem fugiat blanditiis sint commodi. Debitis aut voluptatem sed quos totam ea qui.
Non soluta autem ex ut rerum sit et. Reprehenderit ut non dolores corrupti sint. Inventore quia sint et commodi ut error optio.
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...