How it all started
I was doing my summer internship in Delhi. As part of my summer internship program, I was asked by my mentor to do an evaluation of the IPO of Kings Digital. Frankly speaking, initially I was nervous. I had just completed first year. Although my basics of Corporate finance we good, but then reading out a S-1 form and then using financial modelling to evaluate an IPO was something new that I was going to do.
I started by going through the videos of financial modelling which my mentor gave me. These videos formed the basic blocks of a good financial modelling. Believe me, after watching these videos, my work on excel sheet became more professional. These videos helped me to know how to build an excel sheet that will serve the purpose of a good financial model.
I, then studied the F-1 form for Kings digital. This was another challenging part of the task. A typical S-1 or F-1 form is above 100 pages. Not all items can be understood or need to be understood. You should maintain a separate sheet which maintains a tab of all important items of S-1 form and their page no. This helps in searching the S-1 form easier. You need to go through the S-1 form several times and as such maintaining a table of contents for yourself helps.
Your first few essential steps while building a model
The first step for successfully building any financial model is to build a good excel layout. You should have the layout consistent across sheet i.e. if column E represents FY10, it should represent Fy10 in all sheets. First column should be first table of contents. Next Column second table of contents and so on. This makes navigation easier.
How to do this? Make the layout as said in one sheet. Replicate the layout by ctrl+ drag. This way you get several sheets with same layout. Check that you navigate the table of contents of same level using CTRL up/down.
Next important thing is reading the S-1 form. Some of the important parts that you should go through are what are growth drivers, the risk to business, latest trends of the industry. All these things are there in the management discussion of the S-1 form and will help you to build the revenue and cost sheets.
Also look for the historical statements. They will tell you more about the cost ad revenue pattern the company is witnessing. Make sure that you capture the subtle hints that come out of the historical statements.
Let’s say in Kings Digital the MAU (Monthly active Users) increased by 492% year-on-years. This is too high a number to be used for forecasting next year’s growth. This means I shall have to look for quarterly growth of users for forecasting for subsequent years.
Learning few excel shortcuts and custom formatting before we start
While building the financial modelling ,using excel short-cuts saves a lot of time because there are few functionalities that we will be using again and again. I have come across some short-cuts that will be frequently used and will be of great help.
CTRL+B- To make bold
CTRL+SHIFT+5- to format in percentage
CTRL+1- To Open the format window
Alt+= To insert auto-sum formula
CTRL+SHIFT+Arrows – To select continuous rows
CTRL+ pg up/Pg down- To switch worksheets
Although there are several shortcuts, I as a beginner, used these frequently.
I have also used custom formatting in my excel sheets. The short-cut is CTRL+1. Then under the tab custom you can add your own formatting.
The formatting is given as A;B;C;D
A- Format for positive numbers
B- Format for negative numbers
C- Format for zero
D- Format for text
As you can see from the image below I have used custom formatting to show my negative numbers in red and enclosed in brackets.
In this blog we discussed on how to get started with building a financial model. In the next blog, we will be discussing the on the various steps to build the model i.e. how to build Assumption sheet, Revenue build-up, Cost build-up, Profit and Loss, Balance sheet, Valuation sheet and Trading Comparables etc.
Download: Beginners guide to create Kings Digital Financial Model
Comments