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
Valuation Sheet:
- 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
Related posts:
- Financial Modeling Contest: Complete the LinkedIn Model Preparing an integrated financial model from the scratch for the equity valuation has always been a daunting task which requires both finance acumen and patience....
- CFA Exam Results out today! Clearing Level I is the first hurdle on the way to getting CFA certified. If done properly, it can open a lot of doors for...
- CFA Results to be declared on 26th July CFA Institute has announced the results declaration date to be 26th July, for the June 2010 Level 1 and Level II candidates. Unlike previous year...
- CFA June’ 2010 Results CFA Institute declared CFA Level 1 and Level 2 results on 26th July '2010. Pristine heartily congratulates all the students who have passed the exam....
- GARP releases FRM® 2009 Exam results Alternatively, GARP has published the list of passing students on their website. Please follow following link to check your results For Full exam : http://www.garp.com/frmexam/passingcandidatesFull.aspx...
| About Anil | |
 | Anil has developed end-to-end financial models for Real Estate, FMCG, Power, Roads and Telecom Sector and released them as Open Source. He has extensive experience in the financial services, analytics and training domain. Apart from making Financial Modeling simple and accessible for the masses, Anil loves playing volleyball and has a mean spike. |
Tags: contest, Financial Modeling, Financial Modeling in Excel, linkedin
