As complexity of valuation models increase, it becomes difficult to understand not only by the user, but many times by its creator too. The aim of this article is to provide a five point approach that will help new entrants of business valuation shift the burden of organizing from their brains to the excel sheet.
Star 1: Define The Objective
Before you begin with your valuation model, have clarity on what valuation approach is the best for your company.
- A Discounted Cash Flow approach is the most widely used method across the globe (majority of this discussion will revolve around this approach). However, certain times due to highly uncertain future, it becomes impossible to follow this approach, therefore it is helpful to consider other alternatives.
- A comparable companies method is used where relevant public listed companies are available that are similar in scale and operations to the subject company. Shortlisting comparable companies require maximum diligence and analysis.
- Precedent Transactions, Share Price method and Net Asset Value approach are some of the other methods used for valuation. Availability of data and contextual relevance are important factors to be considered for such approaches.
Star 2: It’s The Age of Modular Everything
Having clarity of objective equips you with the ability to think ahead. Lets illustrate the example of modular furniture to highlight the benefits of a modular financial model. Modular furniture is built in units; similarly these units in a financial model are blocks of calculation clearly marked and distinct. Examples of these blocks for a DCF model are depreciation schedule, working capital calculations, debt repayment schedule, retained earnings, WACC calculations, etc.
Just as in modular furniture, if a particular section is broken or faulty, the entire unit need not be replaced. When you have to make changes to the model, you don’t have to go through the entire model. It saves time and effort and you are much less likely to make errors.
Star 3: Consistency Is Key
Lets see the areas where we can be consistent throughout the model:
- Each sheet should start from the same row / column. The name should be concise and clearly identify the purpose of the sheet. This approach has three main benefits:
- It streamlines data which makes switching between sheets much easier
- It automates tasks with macros
- It formats the output for printing
- Direction of time: When modeling time, whether left to right or right to left, choose a direction that is consistent across sheets. It helps in making references easy in different parts of the model.
- Consistency in which columns contain what period: The same column or same row between sheets should contain the same year. For example if 2016 is in column D and 2017 is in column E, etc. other parts of the model should have the same layout in order to eliminate confusion.
Star 4: Color Coding Your Model:
Use pale colors for information while still maintaining the visual appeal of the model. Bright colors are visually fatiguing and can be difficult to decipher.
Strong Colors should be used when you are using warnings or reminders, for example if you are using hard coded test numbers in order to check your valuation output. It is even better to use these colors for the background instead of the font, to make the marked cells stand out. This is helpful when you want to back out of your changes, so you know which cells to change back.
Input / Output Colors
- Pale Yellow background for input cells
- Blue font is used for input values
- Green is used to indicate a linked or reference input
- Black for non-input formulas and labels in the model
- Red is used for negative numbers, for it to stand out and get noticed by the user
Star 5: Organize Your Outputs:
There are formulas throughout the model that produce results and they may be scattered across your valuation model. A good approach is to organize important output results within a block in a separate sheet. For example, in a DCF model FCFF, FCFE, Enterprise Value, Equity Value / Price per share can all be organized within a block.
It is also important to include major input drivers within this sheet for the user to analyze the relationship between input and output.