Recently, I went trekking through mountains with my colleagues and one of them carried a tiny point and shoot camera, its sleek design coupled with the HD pics took my breath away. Turns out it’s one of GoPro’s successful models. I remember reading somewhere that GoPro has gone public, raising $100 million. I’ve been doing a bit of financial modeling and as soon as this IPO thought crossed my mind, almost simultaneously I wanted to create a financial model for GoPro’s IPO. The trip was great by the way!
Back in my study, our journey begins, just as the company’s IPO, with the S-1 form. S-1 form is an SEC filing (a group of formal documents or financial statements) filed by a company at security exchange commission (SEC) to register its securities with the SEC. This form contains important information required to make a decision by investors, includes Management discussion analysis, Historical Financial statements, Company risk factors, Growth opportunities, etc. I noted down some important assumptions in blank excel file. It is a rigorous but very important process.
Download: GoPro Financial Model.
After making assumption sheet, I created 3 more sheets:- Statement of operations, Balance sheet, Cash flow statement and I filled historical values give in S-1 form.
Initially, I was forecasting each item individually and when I moved to cash flow statement I found everything is unmatched with balance sheet, because I used the cash flow line items given in S-1.
Before we go further, let’s discuss S-1 Line items. S-1 form is made and filled by the company. Now there are too many items that can be used by company to present its financial statement, they can directly use Operating expenses instead of using sales and administrative expenses partially. The way out of my current situation was to use P&L and B/S line items to create the cash flow statement.
An important aspect of financial modeling is integrating the various pieces of information (which I put into different sheets) together. This helps in replicating any changes to be made to the assumptions across the model.
After clearing this, I found cash outflow of 242 which I didn’t find in P&L or B/S. So I looked into shareholder’s equity where you can find information about equity inflow and outflow and I found there outflow of 242 as retirement of common stock. Later, I forecasted my financial statements according to my assumptions which I try to elucidate one by one.
Company disclosure on revenue forecasting:-
I looked up for historical growth rate of revenue which is 125%(FY2012) & 87%(FY2013)here you can see the growth is declining by almost 30% (87%/125 – 1) by following this we can assume the constant declined in revenue growth by 35% each year. After revenue there is no helpful disclosure available on S-1 form about how they were predicting their Cost of revenue, Operating expenses, Operating income and other income. For the remaining items of P&L,I assumed them as a % of revenueand by doing this we are integrating our financial model.
Now we look at Balance Sheet.
Current assets & Liabilities: -I assumed them as a % of revenue because there is no specific information available in S-1 form and those items having a short term impact(Less than 1 year) are affected by revenue.
Now I have to prepare an Asset modeling sheet to forecast Long term assets as the first line item is
Property and Equipment & Other long term assets: – Here, I assumed Average of (2011 & 2012) as a base of forecast. If business grows, they will keep on increasing property and equipment proportionally.
Intangible assets and goodwill: -They have mentioned about annual assessment of goodwill in S-1 form, I highlighted and presented the screenshot of S-1 form below.
According to the information presented above, Iassumed goodwill would grow at 4%constantly for the next 5 years, because historical growth for the past two years is 4% each.
Now that we have P&L and Balance sheet assumptions & Forecasts, it’s time to look at Debt modelling. We usually make separate sheet for long term debt & asset forecasting.
I take a look again at S-1 form where I found some information about contractual obligation which I am enclosing below
I allocated the items in Balance sheet directly by proportioning them accordingly like this:-
Now we can find Price of equity per share by DCF valuation. Here I have find out some necessary value such as Risk free rate = 3.8%(US RFR by Bloomberg.com), Beta = 0.6(Cannon beta is .45 but Go Pro is start up and it is more sensitive to the market) , Expected return from market =9%( 5 year expected return world By MSCI), Cost of Equity( Calculated by CAPM). First we will calculate FCFE by Deducting Change in working capital, Capital expenditure, Debt repayment from Net income. Now we have FCFE and we can calculate terminal value by dividing the FCFE by years. Though consolidated equity value is $3,341,029.78(in Thousands).
Finally we have total no of shares 81899 and our price per share is $41.
Now we have integrated everything and the values are matching with one another which means that I am done with the model.
So creating the model wasn’t as easy as taking a selfie but it is just as much fun, if not more.
After a lot of hassle, I have finally completed the financial model for GoPro. They can be a huge pain or great joy depending on how much you are into modeling. If you don’t know the basics of financial modeling, you can check out our free resources and join our classroom/virtual online training.