Being a financial analyst I understand the importance of creating models which are modular and scalable. It is one of the key skills to survive and thrive in the financial services industry.
If you’ve done financial modeling, you’ll know that there is a big difference in creating hypothetical models and creating the models of actual companies. When you start creating the models for an actual company and start entering the actual numbers in your model, hundreds of things can go wrong!
Income statement is one of the key starting points in building financial models for any company. Today we’ll learn how to create an Income Statement template along with complete Income Statement for LinkedIn.
Let’s get started!
Step 1: Getting Relevant Data for the Company
The numbers we need for creating the Income Statement of LinkedIn can be found in any of its annual reports or filings.
There are many sources on the Internet such as the SEC website, the company website etc. where you can find these reports. Have a look at the source we used: click here
This is the Investor Relations page of the LinkedIn website.
Download both the PDF file and the Excel file by clicking on the links as indicated in the above image
Now open the Excel file accompanying the Annual Report for FY2012, and navigate to the tab called Income Statement.
On the left side, there are number of parameters such as Net Revenue, Costs and Expenses etc. Copy these parameters and paste them into a new Excel sheet
Clean up the parameters to ensure that they occupy only one line per item.
Step 2: Creating the New Template in Excel
Paste the parameters you copied from the Annual report sheet to a new Excel sheet. Ensure that you leave a few empty lines above
Just above the parameters, mention the currency and unit of the values that you’ll enter into the income statement. Enter the first year of projection in the next column. In our case we start from 2010 and extend till 2012.
This is how the template will look :
It is important to format your income statement template properly
Select the Fiscal Years and Press Ctrl+1
Choose Custom in the Format Cell box and change the format to FY0. Finally mention the year end above the Fiscal years.
Now that our template is ready, we need to enter the values in it
Copy the values from the Annual Filing sheet into the template you have created
Ensure that you paste the values under the correct year.
Change the color of these to blue since these are hardcoded values
Step 3: Calculating Net Income for LinkedIn
Now all we need to complete our Income Statement is to enter formulas to calculate the sum totals and other derived values as shown below
Image: Calculating Income from Operations
Image: Calculating Income before Income Taxes
Step 4: Formatting the numbers
Now that completes our Income Statement
But there is still a small thing left. Any model that you build should be easily readable. To do this, let’s format the numbers. Select all the data where you want to change the format.
Press Ctrl+1 >> Format Cells window will appear
Select Number Tab
Select Last format of negative numbers
Change Decimal Places to 0.
That’s it! We’re done. We’ve created a full income statement of a real company from scratch!
Where can you Learn More?
In case you’re interested in learning in depth about how to create Income statement and complex financial models for companies, you can join our course on Financial ModelingÂ.
Templates to Download:
I have created an Income statement template for you, where the subheadings are given and you have to use the functions to get the right values for you! You can download the UnFilled Income Statement template from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
Also you can download this Filled Template and check, if the information you recorded, matches mine or not!For any queries regarding the cash impact or financial modeling, feel free to put the comments below.
Related links you will like:
Comments