Financial Statements: A Step-by-Step Guide to Extracting Historical Data

Annual Report recce

We have now configured the spreadsheet. Going ahead, we can extract data from the yearly report to this empty sheet. Here’s how it should look: 

Additionally, an equivalent page should be established for a profit and loss statement.

Before we begin extracting the financial statements data from the annual report to the excel sheet, it is essential that we first survey it. This is because our financial model requires five years of historical financial data for its primary input.

It is important to make sure the data of the past five years is consistent and that there are no omissions in the records. To illustrate this, let’s provide a quick example.

Revenue can be found on this part of the company’s profit and loss statement. This is where the money generated by the business will be shown.

Year 1 –

Gross Income

Duties

Net Income

Other income

Year 2

Net income

Other income

The company has reported the Gross income and duties paid in year one, while they opted to report net income directly in the following year. This kind of inconsistency can be a concern when modelling as it produces multiple discontinuities in the model. Consequently, before we move to inputting the data from the annual report onto the excel sheet, we ought to glance through the past five years’ annual report to ensure that the statements are consistent during our observation period.

Let’s proceed to do this immediately.

In the previous chapter, we discussed two models: the ‘main model’, which will be used to plot out a financial model from start to finish; and the ‘helper model’, which will lend insight into concepts regarding the financial model.

Therefore, it’s time to present the company that will serve as the inaugural ‘Helper Model’.

We are working with Relaxo Footwear, a leading manufacturer of footwear in the nation.

To begin, I am gathering the past five years of Relaxo’s annual reports and organizing them in one directory. Generally, you can find these documents in the ‘Investors’ section of a corporation’s website, so the same can be done with Relaxo’s.

I’m sharing a photo of my folder that contains the yearly reports for clarification. This is extremely simple, but I thought it would be helpful.

I made the decision to rename these documents according to a style that suits my preferences. Afterward, I can simply open them simultaneously.

Take note that we only work with the combination of financial statements, not the individual ones.

I’ll begin by taking a look at their consolidated balance sheet. On cursory glance, I can tell that they’ve made an alteration to the accounting system as of 2018. How did I spot this? Well, have a look at the images provided.

Balance sheet as recorded in March 2016:

Balance sheet as recorded in March 2017:

It’s clear from the two snapshots that every annual report includes a statement of financials for both the current year and last. The Financial Year 2018-19 is no exception.

The company has revised the Balance sheet for the financial years 2016, 2017, and 2018. Consequently, I’m disregarding the financial statement from the 2016, 2017 Annual report and using the revised numbers from 2018 Annual report instead.

For replicating the Balance sheet on excel, I’d take the line items from the 2020 Annual Report snapshot. I need to use those since it is most up-to-date. Here is the balance sheet from that report –

Under current liabilities, ‘Lease Liabilities’ is listed in the 2020 balance sheet, and wasn’t on either of 2018 or 2019. I must factor this in and include it in the excel sheet- by taking into account that there’ll be a value of 0 against this line item for 2016 up to 2019, but INR 27.61 Cr will be entered for 2020.

It’s likely that if you consider the line items in the most recent annual report, you’ll have almost everything covered. However, this approach might not always work.

– Data extraction

Now that we’ve done the setup let’s start to compile the data from the Annual Report. It’s a time-consuming process, but unfortunately, there is no other choice. We have simply constructed some foundations in the sheet. 

To begin, I entered the line items of the balance sheet on the left side of the excel sheet. As you can see from the snapshot below, I have listed out the assets from the balance sheet. The order used is consistent with how they are reported on a statement.

By indexing columns, A and B, I’ve included headings and subheadings. Emphasising the primary and secondary titles is what I have done here.

In column C, I’ve noted the exact details about the line item for two main purposes –

Organising the financial statements with headings and subheadings is a superb way of presenting them. It is aesthetically pleasing and allows for more comprehensive data capture.

Navigation becomes easy

I want you to understand how easy it is to navigate through data in Excel. To do that, I suggest you do a little exercise. This will give you an appreciation of the power of navigation in this application.

I’m confident that you have already entered the asset side of the balance sheet in your excel sheet like I did. If you haven’t already done so, I strongly recommend doing it. 

Move your cursor to cell B5, which has ‘Non – Current Assets’ entered. Press the control key and down arrow on your keyboard, and it will go straight to the next indexed cell, labelled ‘Financial Assets’.

Jumping quickly helps you to get around faster, allowing you to concentrate on critical data more efficiently.

I’m now ready to input the values on the liabilities side of the balance sheet. Here is a glance at it, though the picture may be slightly compressed – that’s the only way I could share it all in this image.

After attaining this point, the next move should be to replicate the data from the annual report onto the excel sheet. I have to stress; I’m interested in examining the 2018 balance sheet to copy the info for 2016, 2017, and 2018.

We can begin with ‘Non – Current Assets’, as seen in the annual report. Here is the overview –

I’ll make a copy of that in my excel sheet.