Once you have the template in place, the next step is to obtain historical financials. With historical data, we are interested in the balance sheet, profit and loss account, and cash flow statement. It is common, in the course of preparing financial statements, to have a number of initial drafts which may have content that will been superseded when the final statements are agreed. Ensure that the financials you are given are the final audited financial statements. The more information you have, the more accurate your projections. However, you must not get carried away, as too much information will make the model unnecessarily cumbersome. Generally, historical data is limited to five years, with another five years of projected financials. Try to get soft copies of the historical financials in Excel readable format, as this will significantly reduce the amount of time you will need to spend converting into your template format.
Inevitably, you will need to tidy up the data to bring formatting and arrangement in line with your model template and other anomalies. The actual figures from the historical financials will not change as you create your model; however, more often than not, you would have obtained the financials from a source that works with different preferences and priorities to yours. Moreover, the financials were not prepared with you and your financial model in mind. Imported data is therefore riddled with formatting or presentation anomalies, which make it difficult and sometimes impossible to utilize some Excel tools and shortcuts. This makes it necessary to retype some or all of the financials.
The following screenshot is the published balance sheet at August 31, 2016 of ACCENTURE PLC, extracted from the Accenture website (https://www.accenture.com/_acnmedia/PDF-35/Accenture-2016-Shareholder-Letter10-K006.pdf). It illustrates how even the most accomplished financials will need to be adjusted to suit your template:
Since we need five years' historical financial statements, we will need to download two more sets of accounts, for the years ended August 31, 2014 (which will include the 2013 figures) and 2012 so that we have accounts for the years 2012 to 2016. This means that you will have to repeat all of the corrections and adjustments on the other two sets of accounts. After correcting for formatting and presentation in the historical accounts, you should convert the historical financials into your template with the earliest year in column E followed by the next four years in subsequent columns. You should ensure that the balance check for these historical years is TRUE, which will give you confidence that the historical figures have been completely and accurately imported. The following screenshot illustrates how the balance check shows that the balance sheets are in balance:
