A minute for your Feedback please

Friday, September 23, 2011

Some useful excel features for fundamental valuation

The old school investing story(Graham/Buffet) would be to take up a balance sheet, calculate the breakup/minimum value at large margin of safety, and then buy the shares if trading below that price. While academically found reliable, the DCF valuation used to be too computationally cumbersome. So for those well versed at mental number crunching(I still know a few of the oldschool CAs who could whip any newbie at this) or with an army of assistants, DCF was possible. Otherwise for those with limited resources and time, this was not possible. But now, with a spreadsheet on every computer, DCF is no longer that difficult, if you know the right features/methods to configure. Below are some tips for the same
  1. Assumptions in separate cell/worksheet:-This ensures that one can change them en-masse, and the linked cells(and therefore valuations will be updated)
  2. Currencies/Rounding:-For different investors or purposes, one may desire to vary the currency/rounding(crores or millions). That is possible at a click
  3. Conditional formatting:-This allows to highlight, colour or format cells which are meeting the criteria. For example, if one needs to highlight the years with negative cash flow, conditional formatting does that for you at a click. 
  4. Extend the formula across cells:-This is probably the most time saving tool. Just use relative/fixed referencing properly, and a single formula often be dragged to fill the time periods. It is then easy to change the time period/formula without much ado.
  5. Data Table for Sensitivity Analysis:-Excel's feature data table allows one to present a sensitivity analysis without much computational burden. 
  6. Writing macros for data mining:-Often, large data sets('bhav copy' etc) are freely downloadable in Excel. One can write macros to automate the daily stock screens for price, trading volume, F&O liquidity etc. Of course, brokerage houses often do this better, so this is for those investors without access to those good brokerages.
  7. Goal Seek/Solver:-For those wishing to find out a breakeven projection(via goal seek!) or to fill the cash flow statement consistently(via Solver), these tools remove the requirement for endless iterations. Personally, I've found this most useful to calculate implied fundamental values for market prices, and to fill the cash flow statement after income statement and balance sheet are readied.
  8. Cross referencing and automatic recalculations:-This ensures that cascading effect of changes are applied consistently in the calculation
  9. Facility for external data feed to update:-For those with CIQ feeds etc, this allows dynamic updation of valuations

No comments: