Financial Modeling Help?

I don’t have much experience with financial modeling and was wondering if there are any ways to get better? Besides what I have learned in the level 2 curriculum, I’m self taught and try to build my own models at work to help with the research and analysis I do on companies. I’ve read quite a bit of material and watched videos on it, but I feel like they always leave something out. I get the basic concepts and how you you create them, but without being around people who know more than me (no one in my department has modeling skills), I’m not really sure if I’m making mistakes or not. I’d like to become very proficient at modeling so I can add it to my resume. Any suggestions on how to get better?

There are financial courses you can buy. I’m used a free website called Macabacus. They have an operating, merger, and LBO model and step by step instructions on how to build each.

I think the best way to learn financial modeling is to find a good model and then re-create it from scratch.

I just looked at macabacus’ operating model and I definitely need to do a bit more reading… My financial statement modeling is a bit simpler than the one I saw on there.

all you need is

EBIT

tax rate

CapEx

Change in NetWorkingCapital

depreciation/amortization

calc FCF and discount back.

disco time

So what I do is I project out all 3 of the financial statements, link specific line items to in my projections (please tell me if this is bad) to my DCF model to calculate FCF and discount them back. I don’t really go into as much detail like the operating model on macabacus does with the debt schedule, or deprecation schedule. Is that too little?

The only thing I can be certain on a model that I create is that it will be wrong no matter what. You can make your model better by having sufficient / reasonable inputs and having debt schedule, depreciation schedule and etc are all important, but at the end of the day, the accuracy of your model and investment thesis will depend more on things that impact your EBITDA and Free Cash Flow.

So when building a model, I like to answer some key questions and build for the following:

Revenue: what are the key drivers and the KPI’s of your business that you can model? example: # of widgets x price

Variable Expenses and Fixed Expenses so that I know what my margins look like if my revenue (demand for the price of your widgets) takes a hit. Your variable cost should probably be driven by your KPIs as well (ie. material expense per widget produced). I see some people make variable costs be a %'tage of the revenue… but I think that’s a pretty poor approach since there is no way to tell what your margins will look like if your revenue takes a hit due to pricing.

Capital Expenditures: maintenance vs. growth capital is important. Also, how does that growth cap ex impact the future revenue / profitability of the business (ie. does it help you produce more widgets or help you produce it cheaper?). This is also important since it will impact your future years profitability and you can also calculate FCF after just maintenance cap ex to assess the true cash generating ability of the business if the business is in a growth phase and cap ex is out-pacing operating cashflow in the near term.

The rest of the stuff is all fairly straight forward: depreciation (% of your assets / non-cash item), interest expense (effective interest on your debt balance), taxes (current and future), balance sheet (use DSO, Inventory TO, DPO and etc to help calculate your working capital investment).

Two suggestions

  1. Project revenues with solid assumptions

  2. Make sure that changes in debt/ebitda (both gross and net) are gradual and as per expectation.

Everything else should fall in place

Yeah I believe that understanding a business (and therefore being able to model it) is for the most part understanding the first 2 lines of the P&L statement. And by understanding I mean understanding the product and what drives :

  • the demand for that product

  • its pricing and

  • what directly goes in that product (COGS).

Obviously these are only the first 2 steps, but they are the most difficult to get right and if you are wrong about it, your forecast will be completely wrong.

I can’t really vouch for either of these pages, but they’ve been in my bookmarks for a long time, so at least at 1 point I thought they were interesting:

http://pages.stern.nyu.edu/~adamodar/New_Home_Page/spreadsh.htm

http://www.asimplemodel.com/default.aspx