Share with your network!

I believe, there should not be a day without learning something new, so here at Edupristine, as a summer intern I made up my mind to learn something new that is not really taught at any B schools. So I tried my hand at financial modeling.

I remember my mentor walking in through the door, excited with an ET in his hand and starring at the front page; he stopped to ask if I had read the news!!! I acknowledged my ignorance and replied with an affirmative no. His expressions were like that of “the world is going to end”. I read the ET page and came to know the reason to his excitement; I read the article like a dyslexic “Alibaba, IPO, US, 1 billion USD” but still I got the content right.

alibaba ipo

My curiosity grew to an extent that I started with watching Financial Modeling videos from Edupristine website and started my journey to evaluate the IPO of Alibaba Group. And this is what I have to share about financial modeling and IPO evaluation per se.

The first thing about financial modeling is to reading about the IPO filing in this case it was the F-1 form that Alibaba group has filled with the SEC. I should caution you that it is a really drooling process where you might fall asleep anytime and anywhere between scrolling the pdf and reading it. Sometimes you tend to read between lines to realize what you missed is important and you get frustrated to go through it again. I learned from my mistake and made a few changes, I opened a new excel sheet and started recording all the important data along with the page number for future referencing.

The important thing I captured while going through the F-1 filing was the business model of Alibaba group, the revenue and what drives revenue. This is where I started; they formed my revenue driver from where I forecasted the future revenue growth.

alibaba ipo growth drivers

Since the major income/revenue was from the Chinese market, my main focus was to predict the revenue for the same. To my dismay it was quite easy since the F-1 form too acknowledged it by providing enough data about the Chinese market; to make my job a little difficult and fancier, I tried integrating regression analysis to validate the revenue drivers. And the results were in lieu with what I had read from the F-1 filling. So I made it a little interesting by searching for multiple revenue drivers and to my dismay, it failed since the drivers were interrelated and had less significance level as I moved forward.

(Here, GMV implies gross monetizing value)

There are certain assumptions I took after justifying my every “why?” question with a logical answer. First;, the total revenue is driven by GMV growth in the Chinese market. I assumed this since the e-commerce revenue from Chinese market is approximately 88% of the total e-commerce revenue and the F-1 filling has emphasized the growth to be based on the mobile based user growth in China.
Second, I kept the international e-commerce revenue constant as a percentage of the total revenue. This percentage was drawn from the historical data that the company has provided along with the filling.

Cost assumptions were again related to percentage of the total revenue.

From this experience I realized, using historical data to model and forecast the income statement is actually easy and fun to do and does not really require any financial expertise, in the end it is all about common sense sometimes that we tend to forget.

Balance sheet was the next hurdle to think about. Like the income statement, balance sheet was not difficult to predict, it was more fun since I got to use some of my B-school learning to it, few terms like DSO, inventory turnover etc. came in handy and I was through in no time. Then came the sad part, the only item that remained was, “Cash and Cash equivalent” which sat empty in my balance sheet and all I did then was, made a lay out for the cash flow statement and I plugged the final cash and cash equivalent from it to my balance sheet with a hope that it will balance.

Anxiously I had included a column to check the total assets to my total liabilities, two columns showed TRUE and I was happy that everything was going right. I started plugging in the values, when it was done I checked if it balanced and I could start my valuation. Then came the sad part where I had to wonder in wilderness and seek professional help, friends family mentor and then friends again. I left no stones unturned to find everyone has been through this phase. It was an eye opener; it is always difficult to balance anything in life.

With this thought I started probing for the validity of the balance sheet in the F-1 filling, the numbers really did not add up. There are certain items missing, and that was my “aha!!!” moment and I realized there is an exchange rate loss/gain. After a night out and missing a cricket match I was able to balance it in the end!!!

 validity of the balance sheet

The valuation was easy, though one can never be sure about the exact value unless you are an ally to Jack Ma to calculate the exact value I used others’ valuation to be my yardstick. My valuation and the fair value of the stock were close enough.

I have done the entire valuation and financial model in REM, the above firm value as well as the fair value of stock is also in REM. The exchange rate though pegged, vary slightly and is also given along with the prediction in the F-1 filling.

entire valuation and financial model in REM

The calculation was simple, i.e. the conversion and the fair value of a single Alibaba group stock is around USD 45.7.

There should be a way to measure the firms’ past and future, ratio analysis was again easy since you can find it by using Google as a tool, learn from the first click about all of them and what it really means. This is what I did and you do not need any course for ratio analysis at least.

The Dupont analysis for which I found the layout from the Edupristine quite helpful, but before that you need to really know what it means and what it measures in a firm. I would suggest google it as well.

Since Alibaba Group is not the only player in the market, this is actually a blessing in disguise for every financial modeler since you can do a competitive analysis and get to know about the beta value, the risk premium that the investors require as well as the debt structure.

Overall I would say, I was self-engrossed for a long time (days/weeks) and financial modeling helped me beyond boundaries in realizing me the value of patience, it has calmed me down and moreover I have learned something new to brag about!!!

Want to know more about Financial Modeling? Visit the course page