Modeling Help

Guys, I am trying to build a financial model for Dell and I am having a pretty hard time with the Cash Flow statement and I would appreciate any help you guys can provide. The cash flow statement in Dell’s 10-K does not break down the items very clearly e.g they have a line item called “Change in non-current assets and liabilities” I need to know what Balance sheet accounts are used to calculate this line item so that I can link it to the appropriate Balance Sheet items. There are accounts called “non-current assets” and “non-current liabilities” in the Balance sheet but the increase/decrease of those accounts does not give the same number as in the Cash flow statement . How do I tackle this ?? Is there a way to figure out what non-current assets and liabilities have been used to calculate that cash flow item. Any help/guidance is appreciated. Thanks.

Have you checked the notes?

If the company didn’t break it down for you in the notes (which most of them don’t), then there is nothing much you can do. The historical cash flow statement, however, are not that important for modeling purposes. Knowing key items such as Capex, acquisition spending, working capital’s effect on cash flow, dividend and repurchase etc. should be sufficient for you to model out future cash flow.

Agreed with Zuran…for modeling purposes, it’s much more important that you link the appropriate rows in your CF statement with the corresponding rows on the balance sheet as you build out projections. Historically speaking, the reason why statement of cash flows doesn’t always reconcile with the balance sheet is because on the cash flow line, “change in non-current assets and liabilities” may include other assets and liabilities that are not included in the “non-current assets/liabilities” line of the balance sheet, or occasionally vice versa. There’s really no way to figure out how the internal accountants handled the reconciliation, but this really isn’t a big deal. You should be paying more attention to your projections, first and foremost to working capital items and secondarily to proper linking between the BS and CF for non-current items for the sake of “modeling best practices.”

Thanks for your inputs. Numi & Zuran, I am trying to do exactly as you have suggested. i.e link the “change in non-current assets/liabilities” in the CF to the “non-current items” in the BS but since I do not what components are used to calculate that line item in the CF , I would not know what to link to in the BS. If I just link to the “non-current assets/liabilities” in the BS , I won’t be getting the right numbers since the historical numbers do not reconcile for those items. e.g. Balance Sheet 2007 2006 Other Non-current assets 817 454 Other Non-current Liabilitues 2836 2407 Cash Flow 2007 Change in Non-Current A&L 132 The “132” does not reconcile with the BS numbers. Are you guys saying that I shouldn;t worry about historical reconciliation of these numbers and project the future numbers for Non-current A&L and use those numbers for the CF ?? Thanks.

AF screwed up the formatting of my prior post . i am going to repost the numbers to make it clearer: BS 2007 Other Non Current Assets=817 Other Non Current Liabilities = 2836 2006 Other Non Current Assets=454 Other Non Current Liabilities = 2407 Cash Flow 2007 Change in Non-Current Assets & liabilities= 132 Thanks.

When modeling I have never worried about footing historicals so that it flows, take it as a given. Especially when you are talking about general accounts like Other Assets & Liabilities, there are a bunch of accounting issues that can effect these and you will never be able to get enough information to guarentee that it will foot, make sure it is close or that if there is a huge difference you know the reason. Things such as unamortized debt issuance fees, tax issues and other things often run through these accounts in addition to reclassification from LT - ST or writedowns, these all may be dealt with indiviudally on the cash flow statement or lumped in with other line items. In short I agree with Numi and Zuran, it is not common practice to foot historical statements (I come from Ibanking background), its way more headache than its worth and the important part is to have a properly flowing projection model. In addition it is unlikley that you will be making large assumptions with these accounts anyway, at most you would project them as a % of sales, most analysts would actually just assume that they are flat going forward

good suggestions, JustLetItEnd. I agree with his post in that for non-impt balance sheet items, you can generally get by with forecasting them as a % of the appropriate P&L item – for example, non-current assets and non-important current assets as a % of sales, and non-current liabilities and non-important current liabilities as a % of COGS or opex. there’s no way to get any more granularity on some of these items, so generally it just makes the most sense to forecast it as a proportion of most relevant operating line item in hopes of capturing the recurring elements of the entity.

Thanks all for your suggestion.

Thanks all for your suggestions.

Like the others, I don’t worry about the historicals as you sometimes need a plug and it looks messy. In fact, I always shade where the historicals would fit and write “historicals not needed.” No-one has ever bothered me over this. And if they did, I’d tell them where to stick it.