August 7, 2013
After reading this blog, you will be able to:
Make a balance sheet when given the names of the accounts
calculate total assets, liabilities, and net worth
Make predictions how the composition of the balance sheet changes when there are changes to assets and liabilities.
In financial accounting, a balance sheet or statement of financial position is a summary of the financial balances of a sole proprietorship, a business partnership, a corporation or other business organization, such as an LLC (Limited liability company) or an LLP (Limited Liability Partnership). Assets, liabilities and ownership equity are listed as of a specific date, such as the end of its financial year. A balance sheet is often described as a “snapshot of a company’s financial condition”. Of the three basic financial statements, the balance sheet is the only statement which applies to a single point in time of a business’ calendar year.
A standard company balance sheet has three parts: Assets, Liabilities and Ownership Equity. The main categories of assets are usually listed first, and typically in order of liquidity. Assets are followed by the liabilities. The difference between the assets and the liabilities is known as equity or the net assets or the net worth or capital of the company and according to the accounting equation, net worth must equal assets minus liabilities.
Another way to look at the balance sheet equation is that total assets equals liabilities plus owner’s equity. Looking at the equation in this way shows how assets were financed: either by borrowing money (liability) or by using the owner’s money (owner’s or shareholders’ equity). Balance sheets are usually presented with assets in one section and liabilities and net worth in the other section with the two sections “balancing”.
As an analyst it is not enough to understand the financial models created by others but develop the models which are modular and scalable. It is one of the key skills to survive and become successful in the financial services industry.
If you’ve done Financial Modelling, 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.
Creating a Balance Sheet Template has always been the pain point for an analyst. Today we’ll learn how to create a Balance sheet template and the Balance sheet for a Real Life Company ‘LinkedIn’.
Let’s get started!
Task 1 in Creating Balance Sheet Template: Getting Relevant Data for the Company
The numbers we need for creating the Balance sheet 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.
We have used the following source: CLICK HERE
This is the Investor Relations page of the LinkedIn website.
Download both the PDF file and the Excel file by clicking the links as indicated in the image
Now open the Excel file accompanying the Annual Report for FY2012, and navigate to the tab called Balance Sheet in Balance Sheet Template.
On the left side, there are number of parameters such as Current Assets, Property and Equipment, Current Liabilities, Shareholder’s equity 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.
Task 2 in Creating Balance Sheet Template: 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 you’ll enter into the Balance sheet. 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:
Task 3 in Creating Balance Sheet Template: Correct Formatting
Select the Fiscal Years and Press Ctrl+1
Choose Custom in the Format Cell box and change the format to FY 0
Finally mention the year end above the Fiscal years
Ensure that you mention as on the date above the years as balance sheet values are always on cumulative basis.
Now that our balance sheet 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.
Task 4 in Creating Balance Sheet Template:
Change the color of these hardcoded values to blue.
Task 5 in Creating Balance Sheet Template: Calculating Totals for LinkedIn
Now all we need to complete our Balance sheet is to enter formulas to calculate the sum totals and other derived values as shown below
Task 6 in Creating Balance Sheet Template: Create a Check!
Check will see if Total Assets = Total Liabilities & Stockholders’ Equity.
It is False now because we haven’t entered the cash values.
Task 7 in Creating Balance Sheet Template:Entering the Cash Values!
The Cash numbers must be linked to the Cash values calculated in the Cash Flow Statement. Since you don’t have the Cash Flow statement right now, you can enter the hardcoded values.
Once Cash values are entered the check turns true. That’s it! We’re done with the Balance Sheet Template.
In case you’re interested in learning in depth about how to create Balance Sheet template and complex financial models for companies, you can join our Financial Modeling course.
Templates to download
UnFilled Balance Sheet Template. I have created Balance sheet 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 same 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
Put your comments below
Take a look at our Financial Modeling Program.
You can also read our blog on
Our counsellors will get in touch with you with more information about this topic.
* Mandatory Field