First of all, I would like to congratulate all the participants for their sincere efforts in the Linkedin IPO Contest.
Most of you were very close to the completely integrated model just missing the target by one or two steps.
I am giving you the complete solution of the problems given to you so that you can check where you fell short.
Problem & Solution
You can download the solution sheet of the model here.
Following were the 4 problems with their solutions:
Revenue Build-Up Sheet:
- Making the projections for Number of registered Members and Corporate Solution Customers
- There no right or wrong solution to this, as this part is very subjective. More you know about the industry and company better the assumptions would be.
- Calculating the Quarterly Revenue from each head: Hiring Solution Revenue, Marketing Solution Revenue, Premium Solution Revenue
- Here we were supposed to introduce the growth rates in the number of customers.
- Finally consolidating quarterly revenues into yearly numbers and updating the P&L statement.
- Most of you have selected the data for the quarters in the year and then summed them up. This is not a good Modeling Technique as it doesn’t allow you to copy that formula forward. Sumif is what I expected.
Cash Flow Statement:
- Preparing the complete Cash flow statement from the Profit & Loss statement and Balance sheet.
- This was correct for most of you. You can have a look at the solution if you found a problem somewhere.
- Updating the balance sheet by linking the Cash and Cash Equivalents row.
- This is the last step of updating the Balance sheet numbers
- Calculating the cost of Equity
- CAPM equation was the answer.
- Calculating the FCFF and FCFE and then discounting these free cash flows to get the Equity Value of the firm.
- All have done it correctly.
Balance Sheet and P&L Sheet:
- The Cash balance on the balance sheet will also earn some interest rate on it. Let’s suppose 4% per annum. Also if the cash balance of the company goes negative, they have an overdraft facility which charges at 6% per annum. You need to incorporate this interest calculation in the model.
- This was the most interesting part and most of you committed a very small mistake of not taking the overdraft liability into the balance sheet. A simple check on this was, increasing the assets for FY 2012, will disturb the relation of Assets=Liabilities + Equity.
Winners will receive the certificate of Excellence.
Maharajan from IIFT
Anand Singh from IIMC