First of all I would like to congratulate all the participants for their sincere efforts in the Linkedinâ€™s 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â€™s no right or wrong solution of 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 any 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 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 Singhi from IIMC