Excel-Skills.com
100% UNIQUE EXCEL TEMPLATES
TEMPLATE CATEGORIES / ACCOUNTING / FINANCIAL STATEMENTS

01 Financial Statements Template

Use our annual financial statements template to compile a set of comprehensive financial statements in Excel based on International Financial Reporting Standards for small & medium enterprises (IFRS for SME's). Include trial balance amounts, link each account in the trial balance to one of our financial reporting classes and the financial statements are automatically compiled.

  • Comprehensive financial statements in Excel - based on IFRS for SME's
  • Accommodates any account structure and trial balance
  • Link account codes to pre-defined financial statement reporting classes
  • Incorporates non-trial balance financial data
  • Roll financial statements forward or back by changing a single input cell
  • Automatically compiles most of the financial statements in Excel
  • Includes trial balance checking and import feature
  • Customizable for any type of business

How to use the Financial Statements template

Download the sample or trial version when reviewing these instructions

This template enables users to compile comprehensive financial statements in accordance with International Financial Reporting Standards for Small & Medium Enterprises (IFRS for SME's) based on any trial balance regardless of the account structure. The financial statements are compiled by linking each account in the trial balance to a pre-defined financial statement reporting class and all the amounts that are included on the financial statements are automatically calculated based on the linked reporting classes. The template also facilitates including financial information which is not derived from a trial balance and can easily be rolled forward or back by simply changing the reporting year in a single input cell.

Disclaimer: The aim of this template is to assist users in compiling a standard set of financial statements and we can therefore not guarantee that all the financial statement disclosures that are required for the particular business will be accommodated in the template. The user therefore remains solely responsible for ensuring the accuracy and completeness of financial statement disclosure.

Note: The standard template has been designed for companies but you can use the same template functionality for other entity types by saving a separate version of the template and changing the financial statement terminology to suit the required entity type.

The template includes the following sheets:
Setup - enter your business name, add a currency symbol and specify the financial year-end month and the reporting year. The reporting year that is specified determines which annual periods will be included in the current and comparative periods on the financial statements.
Classes - this sheet contains all the pre-defined financial statement reporting classes that should be used to link the accounts on the trial balance to the calculations on the financial statements. The class descriptions that are included on this sheet are displayed on the financial statements and the sheet also includes error checking in the columns with light blue column headings which highlights errors in the linking of the reporting classes.
TB - a complete trial balance for each annual period should be included on this sheet and each account in the trial balance should be linked to the appropriate financial statement reporting class by entering or copying the appropriate class code into column B. The amounts that are included on the financial statements are automatically updated based on the classes that are specified on this sheet.
FinInfo - this sheet contains financial statement information that is incorporated in the financial statements but not derived from the trial balance. Users are therefore required to enter the appropriate values for each annual period that is included in the template. The values that are specified on this sheet are automatically included on the financial statements based on the pre-defined code that is included in column B.
AFS - this sheet includes a complete set of financial statements which is automatically compiled based on the reporting year that is selected on the Setup sheet, the account balances that are included on the TB sheet and the financial information that is included on the FinInfo sheet. The reporting class codes that are used to populate each line item are included in column A.
TBImport - the calculations on this sheet enables users to review the sequence of trial balance accounts before copying the account balances to the TB sheet. The sequence of accounts can be amended until no errors are reflected and the calculations in column E round all trial balance amounts to the nearest integer value thereby ensuring that all the totals on the financial statements are accurate.

In order to start using the template, you basically need to complete the following steps:

  • Change the default settings in the cells with yellow cell backgrounds on the Setup sheet.
  • If you do not want to use your own account structure and you would rather use our standard template setup, you only need to include your annual trial balance values on the TB sheet. All the reporting classes will automatically be linked to the appropriate account balances and populated on the financial statements (AFS sheet).
  • If you want to use your own account structure, replace our default data by copying the account numbers, account descriptions and balances of all the accounts on your trial balance onto the TB sheet and then link each individual account to the appropriate reporting class by copying or entering the class code into column B. The full list of all the classes that are linked to the financial statements are included on the Classes sheet.
  • We recommend using the TBImport sheet when copying trial balance data into the template. This sheet checks whether the sequence of accounts on the sheet and the sequence of accounts on the TB sheet are the same which makes it easier to copy your account balances. The sheet also rounds all balances to the nearest integer which ensures that the totals on the financial statements are always displayed correctly.
  • Once you have added your trial balance data, add the appropriate non-trial balance financial statement data to the FinInfo sheet.
  • Review your financial statements on the AFS sheet and customize the data that needs to be entered on this sheet (as indicated in the columns next to the financial statements). The reporting class codes that relate to each line on the financial statements are included in column A which makes it easy to determine which account balances or other financial information is included in each line item.
  • You can now hide the lines that you do not want to include in your financial statements and then move the column headings to the appropriate locations based on your amended page setup.

Note: These are only the main steps that need to be completed and we strongly recommend that you go through the detailed instructions below in order to obtain a more thorough understanding of the template design.

Setup

The input cells with yellow cell backgrounds on the Setup sheet need to be completed in order to set the template up for your business. The business name is included as a heading on all the sheets and also as a heading on all the financial statement pages.

The currency symbol is included below the year headings on the financial statements. The year-end month is used to determine the appropriate year-end date and previous year-end date which are included in a number of notes to the financial statements.

The reporting year determines which annual period is used as the current reporting period in the financial statements. The comparative year is also determined based on the input in this cell and all the values that are included on the TB sheet and the FinInfo sheet are included in the automated financial statement calculations based on the year that is entered in this cell.

Note: You can therefore automatically roll the financial statements forward or back by simply changing the reporting year on the Setup sheet. All the calculations on the financial statements will be updated automatically after editing the value in this cell.

The reporting date and the first day, last day and last day text next to it are all determined based on the year-end month and reporting year which have been specified. These dates are used in the headings of the financial statements and in multiple notes.

The total number of pages is only used to determine the total number of pages that needs to be included in the table of contents on the financial statements.  The default value is 18 but may need to be changed after customizing the financial statements and determining the total number of pages which will be printed.

The input in the date of approval cell is used as the financial statement approval date below the table of contents in the financial statements. If the cell is left blank, the month-end date of the month which is two months after the year-end date is used.

Reporting Classes

The default reporting classes that are used to link the trial balance values to the financial statement line items are included on the Classes sheet. These reporting classes need to be linked to each account in the trial balance and are included in column A on the AFS sheet next to the line items that are calculated based on the respective reporting classes.

Note: We strongly recommend using our default reporting classes and only adding new classes if they are required. Changing the structure of the reporting class codes may lead to inaccuracies in the financial statements.

The first letter of all reporting class codes should start with the type of account which it will be used for. All income statement accounts should start with an "I" and all balance sheet accounts should start with a "B". This is followed by a hyphen, a two-digit account group number, a two-digit account number (if needed) and all reporting class codes should end with a "G".

Note: There are exceptions to this general reporting class code structure which will be covered later on in these instructions. The code convention that is used in this template is also consistent with the code convention that we use in some of our other accounting templates.

Most of the line items on the financial statements are automatically populated based on the reporting classes that are included in column A on the AFS sheet. We have used different colours to indicate the type of reporting class that is used for each line item - there are basically 5 types of reporting class codes:
GREEN - these reporting class codes are based on the trial balance values on the TB sheet and use a universal formula which can be copied to any line on the financial statements.
ORANGE - these reporting class codes are based on the financial information on the FinInfo sheet but also use a universal formula which can be copied to any line on the financial statements. The sheet from which the calculation is sourced is different but the formula is exactly the same as the green class formula. The formulas for orange and green items are therefore inter-changeable.
BLUE - these reporting class codes are based on the trial balance values on the TB sheet but use a formula which is specific for the note on the financial statement that the reporting class forms part of. These formulas should only be copied within the same note if you need to add additional items and cannot be copied to any other location in the financial statements otherwise it will result in inaccuracies.
RED - these reporting class codes are based on the financial information on the FinInfo sheet and use a formula which is specific for the note on the financial statement that the reporting class forms part of. These formulas should only be copied within the same note if you need to add additional items and cannot be copied to any other location in the financial statements otherwise it will result in inaccuracies.
PURPLE - these calculations are balancing calculations which means that they have been included in order to ensure that the financial statements balance. The calculations should therefore always result in nil values. These items are typically used where the appropriate balance sheet item is included in one account on the trial balance but require multiple lines on the financial statements. The multiple lines are then included individually and the balancing entry is added to ensure that the multiple lines add up to the account balance on the trial balance. If a balancing line item therefore contains values, it means that there is something wrong!

The reporting classes that are included on the Classes sheet should only be used to link the trial balance values on the TB sheet to the financial statements. The reporting classes that should be used for the financial information on the FinInfo sheet are covered later on in the instructions.

The default reporting class that is used for each income statement and balance sheet item that is included on the trial balance are based on the same reporting classes that we use in our other accounting templates. Each account group has its own two-digit account number as indicated on the Classes sheet.

Linking account groups to financial statement items is the main premise of the template design but we have also accommodated variations of the account group links in order to facilitate flexible reporting functionality. The flexibility of these variations is best explained by way of a few examples:

Example 1 - Account Group Links

Account groups can be identified by the two-digit number which follows the hyphen in the reporting class code. The reporting class for the sales account group is IS-01 and the reporting class of the cost of sales account group is IS-02. If you want to include all the accounts that form part of an account group in a single line on the financial statements, you can use the account group followed by a "G" (because all reporting classes need to end with a "G") as the reporting class and link multiple accounts in the trial balance to the same reporting class code.

For example, there are three cost of sales accounts on our default trial balance and all three accounts are linked to the same reporting class which means that they will report in one line on the income statement in the financial statements (AFS sheet). Refer to the cost of sales line on the income statement (AFS sheet) and you will see the IS-02G code in column A.

Example 2 - Account Number Links

A good example of these links are the operating expense accounts that we have included in the detailed income statement which we have added to the AFS sheet. If we used the account group code for operating expenses (IS-03G), linked each IS-03 account on the trial balance to this reporting class and included this reporting class on the income statement on the AFS sheet, all operating expenses would have been included in one line.

We wanted to include a detailed income statement and we therefore had to separate the IS-03 account group into multiple reporting class codes so that we could include multiple lines for the same account group on the income statement. We did this by simply adding another two-digit number after the account group number and before the "G" and included the new reporting classes in column A next to the income statement on the AFS sheet.

Note: The four-digit reporting class number does not need to be the same as the account numbers on the trial balance - it can be any number as long as it is unique. You can also link multiple trial balance accounts to these account level reporting classes by using the same reporting class codes for the appropriate individual accounts on the trial balance.

Example 3 - Additional Disclosure

In some instances, you may want to be able to include an account in the financial statements as part of an account group but also to include the account individually as an additional disclosure. In cases like these, you need to link the account to a reporting class which has the same characters as the account group reporting class and to then include additional unique characters at the end of the reporting class code.

For example, a loss on the disposal of property, plant & equipment needs to be included in the other expenses account group total but also as an additional disclosure under the profit before tax note. The first part of the reporting class therefore needs to be the same as the other expenses account group reporting class (I-08G) and we then add additional characters to this class which results in a unique reporting class for this account.

We therefore use a reporting class of I-08G02G for this account. Note that the first 5 characters are the same as the account group reporting class which means that this account will be included in the other expenses total on the income statement but the "02" makes it unique and enables us to include just the amount for this item under the profit before tax note. We end with another "G" because all reporting classes need to end with a "G".

New reporting classes

If you want to create a new reporting class for an item which we have not provided for in our standard template setup, you can add the new reporting class in the appropriate location on the Classes sheet by inserting a new row (anywhere above the "End of list" row), entering a new unique reporting class code in column A (remember to end with a "G") and entering a description for the reporting class. You also need to copy the formulas in the other columns on the Classes sheet to ensure that error checking is applied to your new reporting class.

Once you have added your new reporting class to the Classes sheet, you can use it to link accounts in your trial balance (on the TB sheet) to the reporting class by simply copying the new reporting class into column B next to the appropriate account numbers on the TB sheet.

You also need to add the new reporting class to the AFS sheet to ensure that the appropriate amounts are included in your financial statements. If the new reporting class needs to be included on the face of the income statement or balance sheet, you can insert a new row in the appropriate location, copy the formulas from any row with a green reporting class and replace the copied class in column A with your new reporting class code.

If you want to include the new reporting class in the notes to the financial statements, you need to insert the appropriate number of rows that will be required for your new note, copy one of the other notes with a similar structure, change the reporting class code in column A to your new reporting class code and then link your note to the appropriate row on the income statement or balance sheet.

Class Descriptions

The line item formulas in column B on the AFS sheet include the descriptions in column B on the Classes sheet as the line item descriptions for all lines that are linked to a green reporting class. If you therefore want to edit the line item descriptions on the financial statements, you need to edit the class descriptions in column B on the Classes sheet.

Error Checking

The Classes sheet also contains four columns with light blue column headings which include formulas that are used for error checking. These formulas should be copied for all new reporting classes that are added to the sheet.

The Code Status column contains formulas which check that all reporting classes start with either an "I" for income statement accounts or a "B" for balance sheet accounts and that all codes end with a "G". If this is not the case, the appropriate row will contain an error status.

The TB Status column indicates whether the reporting class code has been linked to an account on the trial balance (TB sheet). If the reporting class is used on the TB sheet, the status will display "ok". The AFS Status column indicates whether the reporting class code has been added to the financial statements on the AFS sheet. If the reporting class has been added to the AFS sheet, the status will display "ok".

The Error Status column checks whether all reporting class codes which have been linked to accounts on the TB sheet are included on the AFS sheet. If a class has been linked to an account on the TB sheet and not included on the AFS sheet, an "add!" status will be displayed.

Note: If classes are linked to accounts on the TB sheet and not included on the AFS sheet, your financial statements will not balance. We have therefore included conditional formatting on the Classes sheet which highlights the Error Status column heading in red if there are any errors in this column. If the Error Status column heading is highlighted in red, find the class which displays an "add!" status in this column and add it in the appropriate location on the financial statements (AFS sheet).

Trial Balance

A complete trial balance should be included on the TB sheet for each annual reporting period that needs to be included in the financial statements. All the account numbers and account descriptions that form part of the trial balance need to be included in column C and D on the TB sheet and the appropriate account balances need to be entered into the columns from column E onwards.

Each account in the trial balance needs to be linked to one of the pre-defined reporting classes on the Classes sheet by entering or copying the appropriate class code into column B. These class codes determine where in the financial statements the appropriate account balances will be included.

Note: The Status column (A) contains a formula which needs to be copied for all new lines that are added to the TB sheet. This column will contain a "class!" status if the reporting class that has been included in column B is not included on the Classes sheet and an "add!" status of the reporting class has not been included in the financial statements (column A of the AFS sheet). If the reporting class is included on the Classes and AFS sheets, the status will reflect "ok".

There are two ways of including your trial balance data into the template. You can use our existing account numbers and descriptions and just include your annual trial balance values in the other columns on the TB sheet which means that all the accounts will already be linked to the financial statements or you can replace our default data with your own trial balance data.

If you follow the second approach, each account in your trial balance needs to be linked to one of the pre-defined financial statement reporting classes that are listed on the Classes sheet. The template will contain default data on the TB sheet when you use it for the first time but you can replace all the account numbers and descriptions with your own and clear all the class codes in column B before you commence with the exercise of linking your accounts to the appropriate pre-defined financial statement reporting classes.

After clearing all the class codes from column B, you need to refer to the Classes sheet in order to determine which accounts need to be linked to which financial statement reporting classes. Note that you do not need to use all the default classes if they are not required but it is imperative that each account is linked to a class. After completing the linking exercise, there should therefore be no blank values in column B.

Note: All the accounts that are included in your trial balance should be entered in a continuous cell range on the TB sheet - there should not be any blank rows or columns in between account numbers or financial periods otherwise all the accounts or periods may not be included in the financial statement calculations and your balance sheet may therefore not balance!

We have also included totals above the column headings which will indicate if the sum of the account balances for any annual period does not equal nil. If the total of all the account balances in a trial balance does not equal nil, it means that the financial statements will not be accurate and that the balance sheet will not balance. You should therefore always ensure that the trial balance for each annual period adds up to a nil value.

New businesses

When compiling financial statements for a new business, you only need to include the account balances of the current financial reporting period on the TB sheet. All the amounts for the comparative financial year on the financial statements will be nil.

The TB sheet contains a number of default years - you can therefore change the column heading in column E to the appropriate current financial year and change all the subsequent columns to the appropriate subsequent years. Also remember to enter the correct reporting year on the Setup sheet.

Note: The financial statements on the AFS sheet contain a current and comparative period. If you use the financial statements for a new business and only include values for one year, the comparative year will contain zero values in all rows. You cannot delete these values but you can format them as white text so that they are not visible when you print the financial statements.

Existing businesses

When compiling financial statements for an existing business, you need to include the trial balances of at least the last two financial years on the TB sheet but you will also need to include the balance sheet balances of a third year so that the cash flow calculations in the financial statements are accurate.

We therefore recommend that users include the complete trial balances of at least three financial years on the TB sheet when using the template to compile financial statements for existing businesses. If you only include the balance sheet of the third financial year (the year prior to the comparative financial period), it is absolutely essential that the trial balance still balances and that the retained earnings balance is calculated accurately, otherwise your financial statements for the comparative year may not be accurate.

The TB sheet contains four financial periods by default and in most instances, these financial periods will represent the financial periods that are required for existing businesses. If you are compiling financial statements for previous periods, you may however need to change the column headings accordingly (start with the year before the comparative period in column E). Also remember to enter the correct reporting year on the Setup sheet so that the financial statements are compiled for the correct period!

Adding new financial periods

After using the template to compile your first set of financial statements, you can add subsequent financial periods by simply copying the last column on the TB sheet to the next available column, changing the column heading to the next financial period and clearing all the account balances in the new column (we recommend replacing the existing values with nil values before entering or copying the appropriate account balances into the new column).

Note: The same procedure can be followed to also add the new financial period to the FinInfo sheet but before you replace all the values in the new column with nil values, note that some of these values will need to remain unchanged for the next financial period.

Note: All the trial balance data on the TB sheet should form part of a continuous cell range and you should therefore not include any blank rows or columns between rows or columns that contain data otherwise the financial statement calculations will not be accurate.

Trial balance check & import

If you use a trail balance export file in order to compile financial statements, the trial balance export will have to be converted so that the account numbers are in the exact same sequence as the accounts on the TB sheet before you will be able to copy the balances onto the TB sheet. This is an absolute necessity in order to ensure that the correct account balances are included next to the correct account number.

New accounts may also have been added during the current financial period which may not be included in the previous trial balance on the TB sheet. These accounts therefore need to be identified and inserted in the correct row position before the account balances can be copied. This could be quite a time-consuming exercise and we have therefore created the TBImport sheet to assist users in simplifying this exercise.

The following steps need to be completed in order to ensure that the trial balance for the new financial period is correctly included on the TB sheet:

  • Sort the data on the TB sheet in an ascending order based on the account number in column C.
  • Sort the data on the trial balance export file in an ascending order based on the account number column.
  • Copy the account number, account description and account balance from the export file and paste the data into column A to C on the TBImport sheet (replace the existing data on this sheet).
  • Copy cells D5 and E5 and paste these cells into all the rows in column D and E that contain account numbers. Note that these two cells contain formulas and formatting that will assist you in identifying new accounts or changes that need to be made to the sequence of the accounts in the export file.
  • Review the contents in column D - a row that contains a green "ok" cell is in the correct sequence but if an "error" is displayed in orange, it means that the sequence of accounts on the TBImport sheet is not the same as the accounts on the TB sheet.
  • Compare the contents of all the rows that contain errors by comparing the account sequence on the TBImport sheet with the account sequence on the TB sheet.
  • Where an account is included on the TBImport sheet but not on the TB sheet, insert a new row on the TB sheet, copy the account number and description into column C and D and enter a nil value into all the columns that relate to previous financial periods. After completing these steps, the error in the particular row will automatically be removed from the TBImport sheet.
  • Where an account is included on the TB sheet but not on the TBImport sheet, insert a new row on the TBImport sheet, copy the account number & description from the TB sheet and copy the formulas in column D and E from one of the existing rows. Column D should now contain a green "ok" message.
  • Repeat the previous two steps until the TBImport sheet contains no errors.
  • Ensure that the total of the amounts in column E on the TBImport sheet is nil - the total calculation is included in the row above the column headings. If the total is not nil, the difference will be reflected in orange in the cell above the column headings with an "error" message next to it. Note that the difference may be attributable to rounding which is covered in the next section of these instructions.
  • If the column E total is not nil and the difference cannot be attributable to rounding, review your trial balance export and make sure that the total of the export file is in fact nil. Also review the TBImport sheet and ensure that all the accounts on the trial balance export have been added to this sheet.
  • Rounding errors can be corrected by amending the trial balance amounts of selected accounts in column C. When the orange cell background is removed from the total calculation and the "error" text is replaced by an "ok" message, it means that the trial balance has been corrected.
  • The amounts in column E can now be copied and pasted as values in the appropriate column (for the new period) on the TB sheet. After completing this step, the trial balance has successfully been imported into the template.

Rounding in financial statements

Rounding can have a significant impact on financial statements because the amounts that are displayed on the financial statements may not always add up to the totals that are displayed. Even the most insignificant of errors could result in financial statements appearing to be inaccurate and as a consequence also result in the value of the financial information that is included in the financial statements being questioned.

Checking all the calculations in financial statements and making the appropriate adjustments could be quite a time consuming and cumbersome approach and we therefore recommend making the required adjustments on a trial balance level instead. This approach will ensure that the calculations that are included in the financial statements are always accurate and negate the need to go through the entire cumbersome (manual) calculation exercise.

The problem with rounding in financial statement terms is that a trial balance typically includes two decimal values while financial statements are typically compiled to the nearest integer value. When two amounts that contain decimal values are rounded and added, the result is not necessarily the same as the rounded value of the total but if the decimal values are rounded to the nearest integer value and then added, the total that is calculated is guaranteed to be accurate.

This approach is followed in the calculations in column E on the TBImport sheet in that the trial balance amounts in column C are rounded to the nearest integer value in column E. If you therefore use the TBImport sheet to round all the amounts on the trial balance to the nearest integer values before correcting any rounding differences that may exist and copying & pasting the values onto the TB sheet, your financial statements are guaranteed to be accurate. We therefore recommend always using the TBImport sheet before including the appropriate trial balance amounts for a new financial period on the TB sheet.

Financial Information

The FinInfo sheet contains the financial information that needs to be included in our standard financial statements but can typically not be derived from the amounts on a trail balance. These values therefore need to be entered for each financial period that is included in the financial statements and for which a trial balance is therefore included on the TB sheet.

In terms of new and existing businesses and the addition of new columns, the same procedure that needs to be followed in order to add new columns to the TB sheet needs to be completed for the FinInfo sheet. Refer to the Trial Balance section of the instructions for guidance on how to amend the default columns and how to add new columns to this sheet.

Note: Some of the financial information that is listed on the FinInfo sheet may not be applicable to all businesses but we recommend that you retain all the data on this sheet and that you simply enter nil values for the items that are not applicable. Do not therefore delete any of these items as you may need some of them in future.

Note: All the data on the FinInfo sheet should form part of a continuous cell range and you should therefore not include any blank rows or columns between rows or columns that contain data otherwise the financial statement calculations may not be accurate.

The values on the FinInfo sheet will have no bearing on whether the financial statements balance or not - this aspect of the financial statements is entirely determined by the account balances that are included on the TB sheet. The values on the FinInfo sheet are mainly used to provide additional disclosures that may be required and in some instances to include amounts that are required to be disclosed but typically do not get allocated to separate accounts in a general ledger (and therefore trial balance).

Note: We are not going to cover each individual item on the FinInfo sheet in these instructions but you can trace the codes in column B to column A on the AFS sheet in order to see where the information is included in the financial statements. Note that all the codes for the financial information that originates from the FinInfo sheet are displayed in orange or red on the AFS sheet.

Note: The reporting class codes of the items on the FinInfo sheet that require negative values to be entered have been formatted with orange text. Most of the items in black text require positive values to be entered. Some items like deferred tax can be positive or negative.

All the values on the FinInfo sheet are included on the financial statements based on the reporting class code that is included in column B. There are four types of line items that are included on the FinInfo sheet which we will now cover individually.

Items with descriptions that are based on specific TB reporting classes

The items that are listed in this section all relate to specific trial balance reporting classes which have been included on the Classes sheet. The line item descriptions in column C can therefore be determined based on the original trial balance reporting classes which are included on the Classes sheet. These items basically all relate to three balance sheet items namely property, plant & equipment, intangible assets and provisions.

The property, plant & equipment and intangible assets items basically have the same reporting classes as the corresponding classes that are listed on the Classes sheet but each of these codes on the FinInfo sheet starts with an "F" and ends in a different character (the items on the Classes sheet end with a "G").

The "F" at the start is the default reporting class character for the items on the FinInfo sheet but the character at the end has a special meaning and purpose:
R - if the code ends in a "R", the value is included in the accounting policies section of the notes to the financial statements and refers to the depreciation or amortisation rate of the appropriate item.
D - if the code ends in a "D", the value is included in the disposals column of the appropriate item in the property, plant & equipment or intangible asset note. All values must be entered as negative values and there are separate codes for the cost and accumulated depreciation or amortisation sections which are based on the appropriate account group numbers.
I - if the code ends in an "I", the value is included in the impairment column of the appropriate item in the property, plant & equipment or intangible asset note. All values must be entered as negative values and there are separate codes for the cost and accumulated depreciation or amortisation sections which are based on the appropriate account group numbers. Net impairment totals are also included in a paragraph below the appropriate note.

Note: If the description in column C contains the text "PPE Class" or "Intangibles Class" at the end of the description, it means that the code on the FinInfo sheet could not be matched to the code on the Classes sheet. You therefore need to check the code which has been included in this section to determine why it has not been matched to the Classes sheet. The relevant code on the classes sheet will have a "B" at the start of the code and a "G" at the end of the code but all the other characters will be the same.

The section on the FinInfo sheet also contains classes for the Provisions balance sheet item. These classes again contain the same characters as the code for Provisions on the Classes sheet with only the first and last characters being different. The code again starts with an "F" and ends in either a "C" or a "R". The values that are entered for codes ending in a "C" and a "R" are included in the Charges & Reversals column of the Provisions note. Both need to be entered as positive values.

The provisions line items have been included on the FinInfo sheet because provisions are typically recorded in one account on the trial balance and then need to be separated into individual items for financial statement purposes. We have included three default items in the template and the description of each is included in the next section of the FinInfo sheet. These descriptions can be amended and you can add additional items if required.

Items with descriptions and balances that are used on the financial statements

The items that are included in this section of the FinInfo sheet are typically included in one account on the trial balance but are required to be separated into multiple line items for financial statement purposes. There are only two balance sheet items in this section - deferred tax and provisions.

The deferred tax and provisions notes contain a total line which is based on the trial balance values and individual lines that are based on the values that are entered on the FinInfo sheet. We have therefore also included a balancing line to ensure that imbalances between the FinInfo sheet values and the trial balance values do not cause balance sheet imbalances.

The values that are entered in this section of the FinInfo sheet represent the closing balances for each of the line items. For deferred tax, positive values represent deferred tax liabilities and negative values represent deferred tax assets. For provisions, values should be entered as negative values which represent liabilities.

Each section contains three default line items and the descriptions of the items on the financial statement are based on the description of the items that are included in this section on the FinInfo sheet. You can therefore change the descriptions in this section if you want to change the item description on the financial statements.

If you need more than three line items for deferred tax or provisions, you can insert a new row below the last item, copy one of the existing rows, change the reporting class & description of the new item and replace the copied item's balances with nil values. You then also need to add the new item to the note in the financial statements by inserting a new row below the last default item, copying one of the existing items and entering the new class code in column A on the AFS sheet.

Note: If a new line item is created for provisions, the new line item also needs to be added to the previous section on the FinInfo sheet by creating reporting codes which end in a "C" and a "R" for the new item.

Items with balances that are linked to specific reporting classes (as per Classes sheet)

This section of the FinInfo sheet provides for items which are included with other items in trial balance accounts but need to be disclosed separately on the financial statements. If you therefore want to remove an item from the trial balance calculation and disclose it separately on the financial statements, you can include it in this section.

What is important to note is that the formulas on the AFS sheet will automatically remove the item as long as you enter the correct reporting class on the FinInfo sheet which corresponds to the reporting class which has been included on the Classes sheet and linked to the appropriate accounts on the trial balance.

The reporting class that you need to use should be the same as the reporting class on the Classes sheet but for income statement accounts, the "I" at the start of the code needs to be replaced with an "E" and for balance sheet items, the "B" needs to be replaced with an "F".

Examples of these types of entries have been set up in our default template design and include related party transactions that are typically included in trade debtors and trade creditors, short term loans which need to be included separately on financial statements, interest paid on leases which may be included with other interest paid amounts in a single trial balance account and interest received which may be included with interest paid amounts in a single trial balance account.

You can however use the same principle that is applied in this section for any income statement or balance sheet item but it is important to remember to include the new class which is created on the FinInfo sheet on the AFS sheet otherwise your financial statements will not balance.

Note: Values should be entered consistently with the type of balance that is included in the trial balance. If the item has a debit balance, a positive value should be entered and if the item has a credit balance on the trial balance, a negative value should be entered.

Items that are used in other disclosures

The last section on the FinInfo sheet basically includes all other non-trial balance related disclosures that need to be included on the financial statements. Refer to the matching reporting classes in column A on the AFS sheet to see where these items are included on the financial statements.

Error Checking

The formula in column A on the FinInfo sheet need to be copied for all new items that are added to the sheet. Different formulas are used in the different sections of the sheet and you therefore need to copy the formula from one of the existing lines in the appropriate section.

The formula in the first section checks whether there is a corresponding trial balance class on the Classes sheet. If an error is displayed for any item, you need to check the Classes sheet for a corresponding item which has the same class code but starts with a "B" and ends in a "G". You should either fix the class code on the FinInfo sheet or add the appropriate item to the Classes sheet in order to fix the error.

The formula in the second section also checks the Classes sheet for a corresponding code and the same process should be followed to resolve the error. An "add!" status will be displayed if the items cannot be matched to the Classes sheet.

The formula in the third section displays a "no G!" status if the reporting class code on the FinInfo sheet does not contain a "G" at the end. It displays a "class!" status if a corresponding class cannot be found on the Classes sheet and it displays an "add!" status if the class on the FinInfo sheet has not been added to the AFS sheet. Note that an "add!" status may result in a financial statement imbalance because the appropriate value will have been removed from the trial balance amount but not added to the financial statements on the AFS sheet.

The formula in the last section displays a "missing!" status if the appropriate reporting class on the FinInfo sheet cannot be found on the AFS sheet. If it is a new reporting class, you need to add it to the appropriate location on the AFS sheet but if it is not a new class, it indicates that you may have deleted the row which included the reporting class code.

Annual Financial Statements

The annual financial statements are included on the AFS sheet. All the amounts that are included in the financial statements are calculated from the trial balance that is included on the TB sheet and the additional financial information that is included on the FinInfo sheet based on the reporting class codes that are included in column A on the AFS sheet. It is therefore imperative that you do not delete these codes (this column does not form part of the printed pages).

The codes that are included in column A on the AFS sheet can be used to trace the appropriate amounts back to the source sheet. Codes that are displayed in orange or red text can be found on the FinInfo sheet (all these codes begin with an "E" or "F") and codes that are displayed in green or blue text can be found on the TB sheet (all these codes begin with either an "I" for income statement or a "B" for balance sheet).

Note: In some instances, multiple accounts on the Trial Balance may be linked to the same financial reporting class code. We recommend that you filter the TB sheet based on the appropriate code by using the filter feature in the column heading row in column B on the TB sheet. The totals that are displayed above the column headings are calculated by including only filtered data and should therefore agree to the appropriate amount on the financial statements. Where a reporting class consists of account group and account number codes, you will need to select all the codes that form part of the account group code when filtering data.

Note: All the financial statement reporting class codes are pre-defined - you should therefore not amend any of these default codes because this will result in an amendment in the appropriate calculation which may lead to inaccuracies in the financial statement calculations.

Basic Principles

The print range of the financial statements is from column B to column J. The reporting classes in column A and the data in the columns after column J therefore do not appear on the financial statements but play an important role in the automated calculations and should not be deleted.

Do not delete any of the contents on the AFS sheet because you will not be able to recover the data and some of the data may also affect other calculations on this sheet. If you therefore do not want to include any of the rows or notes in your version of the financial statements, we recommend hiding the appropriate rows instead of deleting them. This will ensure that none of the other calculations are affected.

If you hide an entire note, you also need to remove the note number by deleting the formula next to the note heading in column A. Note numbers are calculated based on the previous maximum value and if you do not delete the note numbers of the notes that are not visible, the notes in your financial statements will not be sequential.

The input for rows which contain data in a paragraph layout has been included in column P. The financial statements consist of multiple columns and it is not possible to merge and wrap text in a single cell in Excel. We have therefore wrapped the paragraph text in a single column in column P and used a formula to display the text in the financial statements based on the text wrapping in this column. If you want to edit any of the data that forms part of a paragraph, you need to edit the data in column P.

All the calculations of amounts on the AFS sheet are automated but some areas (especially the general information page) requires user input on the AFS sheet. We have indicated where user input is required in the columns next to the financial statements.

Customizing the standard financial statement data

After linking your trial balance to the reporting class codes on the TB sheet and entering all the required values on the FinInfo sheet, we recommend that you review the standard financial statements on the AFS sheet. You can then hide all the content that is not applicable to your business and delete the note numbers next to the note headings if you hide entire notes. You should hide data and not delete anything from the AFS sheet!

Note: We also recommend unhiding all hidden data before you start with the financial statements for a new year. This will ensure that you have not hidden rows or notes which contain amounts for the new reporting year.

It is imperative that you read through all the contents that are included on the AFS sheet in order to determine whether all the standard information is applicable to your business. This is especially necessary for the notes on the accounting policies that have been applied in compiling the financial statements because these policies may very well differ between different types of businesses.

It is practically impossible to include financial statement information that will be suitable to all types of businesses in a financial statement template and the responsibility for ensuring that all the required disclosures are included in the financial statements remains that of the user. We will also not be able to assist you in adding additional notes to the standard financial statements - you should be able to do this on your own by following the steps that we covered earlier on in these instructions.

If you need to add new reporting classes to the template which are based on either the trial balance data or the financial information on the FinInfo sheet, you need to insert a new row in the appropriate location, copy one of the existing lines with an orange or green reporting class code in column A and change the reporting class in column A to the new reporting class. The formulas in the rows with green or range reporting classes are all the same and copying them will result in the correct calculations being included for the new reporting class.

Printing Financial Statements

The full set of financial statements that are compiled with this template are included on the AFS sheet and you therefore only need to print this sheet in order to print your full set of financial statements. After hiding some rows or even entire notes or making other changes, the page setup of your version would have changed from the page setup of our standard version and you will need to make some amendments especially for printing purposes.

It is mostly the notes section of the financial statements which will be affected because all the pages before this section fit onto a single page and should therefore not be affected by any of your changes. The notes section contains headings which consist of the company name, the notes section heading and the date for which the financial statements are compiled in three separate rows.

After hiding content, you need to review where these headings are printed and move the rows to the appropriate row locations so that the headings are printed at the top of all of the pages. If you need more headings than what we have included in the standard template, you can simply copy one of the existing headings.

If you've hidden notes, you should also check your note numbers to see that they are all in sequence. If not, you have probably hidden a note without removing its note number.

The final aspect of printing that we need to mention is the rows that contain paragraphs. All of these rows should be wrapped automatically but in isolated cases, the row height may not be correct when the pages are printed. You therefore need to review your printed financial statements to ensure that there are no extra spaces. If you note any of these occurrences, you can fix the issue by changing the row height of the affected row.

AFS Components

In this section of the instructions, we provide guidance on the information that is included in each section of the financial statements on the AFS sheet. One of the most important points to note is that the current and comparative periods are determined based on the year that is specified on the Setup sheet. Users therefore don't need to change the individual dates on the AFS sheet when compiling financial statements for a new period.

Cover page

The first page which forms part of the financial statements is the cover page which is included at the top of the AFS sheet. The business name is specified on the Setup sheet and the registration number is included in the general information section of the financial statements. The cover page also includes a logo placeholder which can be replaced with your company logo.

Table of contents

Most of the information on this page is fixed. The only items that you may need to change is the number of pages and the financial statements approval date which can be specified on the Setup sheet.

General Information

All the details on this sheet need to be entered in column E.

Compilation Report

We have included a standard compilation report on this page. If you need to include a different report or if you are setting up financial statements for a different type of entity which requires a different type of report, you can replace our report with your own.

Note: Remember that all paragraph type text needs to be edited in column P. You should therefore replace the data in column P to ensure that it displays correctly on the financial statements.

The accountant details at the bottom of the sheet should also be replaced with your own data.

Directors' Report

We have included a standard directors report on this page. The only user input that is required on this sheet is to enter the names of the directors. You can also make changes to the default content and hide any items that may not be applicable to your business.

Statement of comprehensive income and retained earnings

The revenue, other income, finance costs and income tax line items are calculated in the respective notes to the financial statements. Cost of sales, dividends paid and the default operating expenses are calculated on the statement of comprehensive income and retained earnings. The reporting class codes for these calculations are included in column A.

Note: Descriptions of all line items that are linked to the trial balance reporting classes (green or blue text in column A) can be edited on the Classes sheet and the descriptions of all the financial information reporting classes (orange and red text) can be edited on the FinInfo sheet.

Note: If the number of expense line items are not sufficient, additional expenses line items can be added by inserting a new row, copying one of the existing rows and entering a new reporting class code in column A.

Note: If the line item description contains a "no class!" error, it means that the appropriate reporting class in column A has not been added to the Classes sheet. If the description is "Add to FinInfo sheet!", it means that the code in column A starts with an "E" or a "F" but cannot be matched to a class on the FinInfo sheet.

The retained earnings balances at the start of the year are calculated based on the prior year's trial balances by adding the balance sheet retained earnings to the total of all income statement accounts for the appropriate year. If this calculation differs from the current year's trial balance value for retained earnings (which represents the opening balance), the difference will be displayed in the prior year adjustments line.

Note: If no prior year adjustments have been processed in your trial balance, balances in the prior year adjustment line indicate that there is an inconsistency between the retained earnings opening balance for the current year (as per the trial balance) and the retained earnings closing balance for the prior year.

We have also included control totals next to the profit or loss line next to the financial statements. If any of these control totals contain a balance, it indicates that not all income statement accounts have been included in the income statement. You should then review your trial balance data on the TB sheet in order to identify the accounts that may not have been included in the financial statements.

Statement of financial position

Most of the amounts that are included on the balance sheet are calculated in the notes to the financial statements. The only exceptions are the interest payable, dividends payable and the provision for taxation lines which are all calculated on the balance sheet based on the reporting codes that are included in column A. The retained earnings amounts are calculated on the income statement.

Note: The balance sheet includes three years but only the current and comparative years are included in the printed pages. The third year is only included in order to facilitate accurate cash flow calculations for the comparative financial year.

Note: If the balance sheet is not in balance, the appropriate total assets and total liabilities & equity totals will be highlighted in orange. We suggest that users review the guidance in the Troubleshooting section in order to resolve any imbalances that may have occurred.

Statement of changes in equity

The retained earnings calculations are based on the amounts that are included on the income statement and the share capital and reserves calculations are based on the amounts that are calculated in notes. If you do not need to include reserves, you can move the content in the share capital column into this column to replace the data and also delete the rows that contain the movements in reserves. The note for reserves can then also be hidden.

Statement of cash flows

The profit / (loss) for the year is calculated in the income statement while the income tax and finance costs are also calculated in the income statement and are added back because the amounts that have been paid are included separately on the cash flow statement. Interest and dividends received are included in other income and deducted from profit because both items are included in the cash flow from investing activities.

Depreciation, amortisation and fair value adjustments / impairment losses are calculated in the property, plant & equipment and intangible assets notes. The movements in provisions and reserves are calculated on the balance sheet and the prior year adjustments are calculated on the income statement. All of these amounts do not represent actual cash flow movements and are therefore added back in the operating cash flow calculation.

Gains / (Losses) on the sale of equipment are added back in the operating cash flow calculation because the proceeds from the sale of equipment is included under the cash flow from investing activities section. Gains on the sale of equipment are calculated in the other income note and losses are calculated in the profit before tax note.

Working capital movements are calculated on the balance sheet - note that a balance sheet for 3 financial years is required in order to calculate these amounts accurately. If you therefore only include a trial balance for 2 financial years in the template, the entire closing balances of these working capital items will be included in the cash flow statement for the comparative financial period.

The interest and income tax paid amounts are calculated by deducting the opening balances and income statement charges from the closing balances of the respective income tax and interest payable amounts. The net result is that only the interest and income tax amounts that have actually been paid during the financial period are included in the cash flow statement.

The proceeds from the sale of equipment are calculated by adding or deducting the profit or loss on the sale of equipment from the book value of the equipment that has been sold. The profit on the sale of equipment is included in other income, the loss on the sale of equipment is calculated in the profit before tax note and the book value of the equipment that has been sold is calculated in the property, plant & equipment note.

The purchases of equipment amounts are calculated by deducting the new finance leases amounts from the additions to property, plant & equipment. The new finance leases amounts do not represent actual cash flow and need to be specified as part of the financial info on the FinInfo sheet. The purchases of intangible assets and investments are calculated in the intangible assets and investment notes respectively. The dividends and interest received amounts are calculated in the other income note.

The proceeds from the issuance of shares amounts are calculated on the balance sheet. The proceeds from new loans need to be specified on the FinInfo sheet and the repayment of loan amounts are calculated based on the balance sheet movement of long term and short term loans and the proceeds from new loans.

The repayment of finance leases amounts are calculated based on the balance sheet movement of the long term and short term finance lease liabilities and the new finance leases amounts that are specified on the FinInfo sheet. New finance lease amounts are therefore removed from the additions to property, plant & equipment and also removed from the finance lease liabilities in order to ensure that only the actual cash repayments of finance leases are included in the cash flow statement.

The dividends paid amounts are calculated by deducting the opening balance and income statement charges for dividends from the closing balance of dividends payable. The net result is that only dividends that have actually been paid during the financial period are included in the cash flow statement.

The cash equivalents at the beginning of the year are calculated based on the balance sheet movements of the cash & cash equivalents and bank overdraft lines. This is also an important point to note in terms of the closing cash balance - the closing balance on the cash flow statement should agree to the cash & cash equivalents note and includes the bank overdraft balance.

Note: We have included control totals next to the closing cash balance on the cash flow statement to indicate whether the calculated balances agree to the cash & cash equivalents note (and therefore also the balance sheet amounts relating to cash). If these control totals contain values, it means that the cash flow statement does not balance. Refer to the Troubleshooting section for guidance on how to resolve imbalances in this template.

Notes to the financial statements

This section deals with all the default notes that have been added to the financial statements. It is imperative that users review all the default notes in order to ensure that all the information that is included in the default notes are applicable to their businesses. Users may also need to add additional disclosure to this section of the financial statements and it is imperative that any changes that are made are incorporated correctly within the existing template structure otherwise the amended financial statements may contain errors!

Note: Financial statement notes that are not applicable to your business should not be deleted otherwise some of the other financial statement calculations may become inaccurate or contain errors. Instead, all notes that you do not want to include should be hidden by hiding the entire rows of the note. Before you hide notes, you need to delete the formula which calculates the note number of the note that you are hiding otherwise the remaining visible notes will not be in the correct sequence.

Note: We have included a page heading which consist of the business name and a financial statement notes title in the appropriate rows based on the notes that have been included in the standard financial statements. If you hide some of the default notes or add additional notes, you may have to move the rows which contain the page headings to new row locations so that the page headings are included at the top of each printed page.

Note: If the text that is included in a note is in a paragraph layout, the text needs to be edited in column P. We have included the text in that column because Excel does not allow for multiple columns to be merged and then wrapped according to the text or row height.

Note: If you need to add additional lines to any section of the notes, you can do so by inserting the required number of new rows, copying one of the existing lines with either a green (for a trial balance item) or an orange (for a FinInfo sheet item) reporting class in column A and changing the reporting class to the appropriate class which relates to the new item. The line description will be determined automatically based on the class which has been added to the Classes or FinInfo sheets.

General information and accounting policies

The accounting policy notes need to be reviewed carefully and the user needs to ensure that the descriptive text is correct and that all the accounting policies are applicable to the business. Additional sections can be included if required.

The country which is included in the currency units statement needs to be specified as part of the general information page and the depreciation and amortisation rates need to be included on the FinInfo page (reporting classes are included in column A). If you need to add additional items, copy one of the existing lines and change the reporting code in column A.

Note: If you need to add additional lines to any section of the notes, you can do so by inserting the required number of new rows, copying one of the existing lines with either a green (for a trial balance item) or an orange (for a FinInfo sheet item) reporting class in column A and changing the reporting class to the appropriate class which relates to the new item. The line description will be determined automatically based on the class which has been added to the Classes or FinInfo sheets.

Revenue

This note includes two revenue lines by default but you can add additional lines if required.

Other Income

The default note includes four other income items but also includes two additional lines for interest received. The way in which interest received is included in the financial statements depends largely on where it is allocated to in the trial balance. Different businesses use different methods of recording interest received and we have provided for three alternatives in our default financial statements.

The item with the green reporting class is the simplest because it handles scenarios where interest received has its own account on the trial balance which can then simply be linked to the note by using the provided reporting class. If interest received is however combined with interest paid or other income in the trial balance, this method cannot be used because the interest received balances are not included separately on the trial balance.

You then need to add the interest received amounts to the FinInfo sheet and use a reporting class which removes the interest received amounts from the account which contains the interest received balance in order to reflect it separately on the financial statements. This method is best explained by way of two examples.

In the first example, we assume that the interest received has been included with interest paid in the trial balance and that the interest paid account has been linked to the I-06G reporting class. We therefore add the interest received line to the FinInfo sheet and link it to a reporting class (E-06G02G) which starts with an "E" instead of an "I" but includes the same characters up to the first "G". The result is that the formulas in the amounts columns will automatically remove the interest received from where the trial balance class is included in the financial statements and include the interest received amounts in other income where the E-06G02G class has been added.

In the second example, we assume that the interest received has been included with other income in the trial balance and that the other income account has been linked to the I-0900G reporting class. We therefore add the interest received line to the FinInfo sheet and link it to a reporting class (E-0900G) which starts with an "E" instead of an "I" but includes the same characters as the trial balance reporting class. The result is that the formulas in the amounts columns will automatically remove the interest received from where the trial balance class is included in the financial statements and include the interest received amounts in a separate line. Both lines are included in other income in this example so we have just included interest received in a separate line.

Note: The class in the second example only has a first character which differs from the trial balance reporting class but the reporting class in the first example has three additional characters at the end of the code which are not included in the trial balance reporting class. The reason for this is that we are removing multiple line items from the interest paid class and we therefore need to include multiple unique classes on the FinInfo sheet. We therefore use the same code as the trial balance class (aside from the "E") to start off with but add uniqueness after the first "G" by including a two-digit number followed by another "G" (because all codes must end with a "G").

Note: You can use any of the three interest received lines in the other income note and simply hide the other two lines which will contain zero values.

Note: The same principle as explained in the examples above can be used to remove any amount from a trial balance account and include it as a separate line item. For more information on the reporting classes that need to be used, refer to the Financial Information section of the instructions.

Finance Costs

The finance costs note includes two line items - one that is linked to the trial balance account for interest paid and one that is linked to the interest paid on finance leases which is included on the FinInfo sheet. We have therefore assumed that all interest paid amounts are allocated to one trial balance account and that we need to use the FinInfo sheet in order to include the interest paid on leases in a separate line on the financial statement by following the same approach as explained in the other income section above.

If your trial balance includes separate accounts for each type of interest paid, it is not necessary to add the interest paid on leases to the FinInfo sheet - you can simply link the trial balance account to a unique class and include this class in the note. For example, interest on loans could be linked to a class of I-0601G and interest on leases can be linked to a class of I-0602G and these two classes can then be included in column A on the AFS sheet to include the two separate accounts in the note.

Profit before tax

All the expense items that require separate disclosure should be included under this note. The cost of sales amount is calculated in the income statement but all the other items are calculated based on the reporting codes which have been included in column A.

The profit before tax note is unique in that it does not affect the balancing of the financial statements and is not linked to the income statement in any way. The items that are included in this note need to be displayed as separate line items but also be included in another line item on the income statement. In order to display the items separately, each item needs to have a unique class which is why some of the items contain two "G's" in the class code. This is best explained by an example.

Foreign exchange losses are linked to other expenses on the income statement together with a number of other individual trial balance accounts. All of these accounts contain a code which starts with "I-08G" and are updated to other expenses in the income statement because the reporting code for that line item is "I-08G". We have therefore had to add uniqueness to the code for foreign exchange losses so that we can display it as an individual item in the profit before tax note but we still needed to have the same code to include it in other expenses on the income statement. We therefore added a two-digit number for uniqueness and ended with a second "G" because all class codes need to end with a "G".

Income Tax Expense

The total of the income tax expense note is calculated based on all the accounts that are linked to the reporting class in the total line. The deferred tax charges for the year are calculated in the Deferred Tax note and these amounts are deducted from the total in order to calculate the current taxation amounts. The income tax percentages that are included below these calculations need to be specified on the FinInfo sheet.

The tax rate reconciliation provides an explanation of the difference between the corporate tax rate (the tax rate specified on the FinInfo sheet) and the effective rate which is calculated based on the income statement tax amount and the profit before tax. All the lines that you want to include in this rate reconciliation need to be added to the FinInfo sheet where the rate differences can be entered. We have also included a balancing line to ensure that the reconciliation always balances to the effective tax rate.

Property, Plant & Equipment

The property, plant & equipment note includes six default categories of assets. Each of these categories is included in a separate row and linked to a separate class code in column A. The line items also have separate accounts in the trial balance which are linked to these reporting classes and the description of each line item is included based on the descriptions that are included on the Classes sheet.

The opening and closing balances in both the cost and accumulated depreciation sections are calculated based on the trial balance values, the disposal & impairment totals are based on the values that are specified on the FinInfo sheet (the disposals class ends in "D" and the impairment class ends in "I") and the additions & depreciation totals are the balancing values.

If you need to add additional property, plant & equipment items you therefore only need to insert the required number of additional lines in the cost and accumulated depreciation sections, copy the formulas from one of the existing lines and change the reporting code in column A. You also need to add the appropriate additional lines to the FinInfo sheet if you want to record disposal or impairment values for the new lines.

We have also included paragraphs detailing the total impairment values and total assets under finance leases below the carrying value calculation. These values are also included based on the values that are recorded for the appropriate reporting classes on the FinInfo sheet.

Intangible Assets

The intangible assets note is compiled in exactly the same way as the property, plant & equipment note. The only difference between the two notes is that the reporting classes differ. Opening and closing balances are calculated based on trial balance values and the disposals and impairment values need to be included on the FinInfo sheet. Additions and depreciation are again the balancing figures.

Investments

We have included two lines in the note and also included the income from investments below the annual totals. Note that we have again included three interest received lines - refer to the other income note instructions for the reason why there are three lines and how to decide which of these to use and which to hide.

Deferred Tax

The deferred tax note is based on the assumption that all deferred tax amounts are allocated to one account on the trial balance. The financial statement disclosure for deferred tax requires that each item that contributes to deferred tax needs to be included separately.

We have therefore included three deferred tax items in the default template and the appropriate deferred tax balances at the end of each annual period can be included on the FinInfo sheet. Item descriptions are also populated based on the descriptions that are entered for the appropriate classes on this sheet.

The note also contains a line for a balancing entry which calculates the difference between the trial balance amounts and the amounts that are entered on the FinInfo sheet for the individual items. If the values balance, there will be zero values in this row and it can then be hidden.

Note: You can add as many additional line items as required by inserting the required number of additional rows above the balancing item, copying one of the existing lines and changing the reporting code in column A. You also then need to add values for the additional items to the FinInfo sheet.

Inventories

This note consists of three items for raw materials, work in progress and finished goods. All three lines are based on trial balance values.

Trade and other receivables

The default note contains 4 items which are based on trial balance reporting classes and one item for related party amounts which is included on the FinInfo sheet.

Note: The reporting class for the related party item is the same as the trade debtors item (aside from the first character) which means that the related party amounts on the FinInfo sheet will be deducted from the trade debtors trial balance values and displayed as a separate line item.

Cash and cash equivalents

The cash and cash equivalents balances contain cash on hand and bank account balances. The sum of these two line items is included in the balance sheet under current assets. The note also contains a line for bank accounts which are in overdraft and linked to the bank overdraft reporting class. The bank overdraft line is included in current liabilities on the balance sheet.

Note: The note totals should agree to the cash & cash equivalents balances on the cash flow statement.

Note: The information about the security that is provided for the bank overdraft and the interest rates that are in effect are provided as an example. You should therefore review these details and amend the text to what is applicable to your bank overdraft.

Share Capital

The issued share capital which is reflected in the note is calculated from the trial balance values. The note also contains the number of shares issued, par value of shares and the authorized number of shares. The par value of shares and the number of authorized shares need to be included on the FinInfo sheet.

Reserves

The reserves note has been provided as an example. If your trial balance does not include reserves, you can hide the note.

Loans

This note contains both long term and short term loan balances. We have assumed that the same trial balance account is used for both long term and short term values and the short term values are therefore removed from the trial balance values by including the same reporting classes (aside from the first characters) on the FinInfo sheet and in the note.

Note: You therefore need to include the short term values on the FinInfo sheet and use the same reporting classes as the ones used in the trial balance but replace the "B" with a "F". This will result in the short term loans being included separately for financial statement purposes.

If short term loans are included in separate trial balance accounts, you can simply link the appropriate trial balance accounts to a unique code and include this code in column A on the AFS sheet. The short term loan balances will then be taken directly from the trial balance.

The long term portion of the long term loans are included under the non-current liabilities on the balance sheet while the short term portion of long term loans are included under current liabilities on the balance sheet.

Note: The information about the security that is provided for the long term loans and the interest rates that are in effect are provided as an example. You should therefore review these details and amend the text to what is applicable to your long term loans.

Finance Leases

The finance leases note works in much the same way as the loans note in terms of the reporting classes that need to be used for the non-current and current liability lines. The note includes additional information on the future minimum lease payments which need to be included on the FinInfo sheet - refer to the class codes in column A of the note to determine where to include this information on the FinInfo sheet.

The non-current portion of the finance leases are included under the non-current liabilities on the balance sheet while the current portion of finance leases are included under current liabilities on the balance sheet.

Commitments under operating leases

The operating lease commitments that have been recognised as an expense during the current and comparative periods need to be entered on the FinInfo sheet. The average lease period also needs to be specified on this sheet. Future contracted operating lease payments need to be disclosed in the same ageing categories that have been covered under the finance lease section and should also be included on the FinInfo sheet.

Note: If the operating lease payments for the current and prior years are included in a single account on the trial balance, you can also enter this class in column A in order to populate these values and it will therefore not be necessary to enter these values on the FinInfo sheet.

Trade and other payables

The default note contains 3 items that are based on trial balance reporting classes and one item for related party amounts which is included on the FinInfo sheet.

Note: The reporting class for the related party item is the same as the trade creditors item (aside from the first character) which means that the related party amounts on the FinInfo sheet will be deducted from the trade creditors trial balance values and displayed as a separate line item.

Provisions

The provisions note is based on the assumption that all provision amounts are allocated to one account on the trial balance. The financial statement disclosure for provisions requires that each item which contributes to the total provisions balance needs to be included separately.

We have therefore included three provisions items in the default template and the appropriate provisions balances at the end of each annual period can be included on the FinInfo sheet. Item descriptions are also populated based on the descriptions that are entered for the appropriate classes on this sheet.

The note also contains a line for a balancing entry which calculates the difference between the trial balance amounts and the amounts that are entered on the FinInfo sheet for the individual items. If the values balance, there will be zero values in this row and it can then be hidden.

Note: You can add as many additional line items as required by inserting the required number of additional rows above the balancing item, copying one of the existing lines and changing the reporting code in column A. You also then need to add values for the additional items to the FinInfo sheet.

The provisions financial statement disclosure also requires that additions, charges and reversals need to be recorded separately. We have therefore included a column for charges & reversals which is calculated based on the amounts that are included on the FinInfo sheet. The additions column is the balancing figure between the opening & closing balances and the charges & reversals.

The individual item descriptions, opening balances and closing balances in the note are calculated based on the values that are included in the second section on the FinInfo sheet and the charges and reversals are based on the values that are included at the bottom of the first section on the FinInfo sheet. The reporting classes for charges end in a "C" and the reporting classes for reversals end in a "R".

If your provisions are all included in separate trial balance accounts, we still recommend linking all of these accounts to a single class and using the note as indicated above. It is a lot simpler than changing the reporting classes to accommodate multiple provision accounts in the trial balance.

Contingent Liabilities

This note is provided as an example of the information that needs to be included in a contingent liability note and can be hidden if it is not required. The information in the default note needs to be replaced by your own info if the note is to be retained in the financial statements.

Events after the end of the reporting period

This note is provided as an example of the information that needs to be included in this type of note and can be hidden if it is not required. The information in the default note needs to be replaced by your own info if the note is to be retained in the financial statements.

Related Party Transactions

This note includes related party and remuneration amounts which are based on the reporting classes in column A and the values that are entered for these classes on the FinInfo sheet.

Troubleshooting

If your balance sheet or cash flow statement does not balance, please complete the following steps in order to resolve the imbalances.

  • Check that the appropriate totals above the column headings on the TB sheet are nil. If your trial balance does not balance, your balance sheet will also not balance. Also ensure that there are no blank rows that have been inserted between rows that contain data. All trial balance data needs to be entered in a continuous cell range otherwise all the rows on the TB sheet may not be included in the financial statement calculations.
  • Check the Classes sheet and make sure that there are no errors in the columns with light blue column headings. If there are, it may indicate that you have linked an account on the trial balance to a class which has not been included in the financial statements.
  • Check whether there are any cells in column A on the TB sheet that contain an "add!" status. If a cell contains this status, it means that the class that has been entered in column B has not been linked to the financial statements. You then need to include the affected class in the appropriate location on the AFS sheet.
  • Check whether there are any cells in column A on the FinInfo sheet that contain an "add!" status. If a cell contains this status, it means that the class that has been entered in column B has not been linked to the financial statements. You then need to include the affected class in the appropriate location on the AFS sheet.
  • If some of the calculations on the AFS sheet contain a #REF! error, it means that you've deleted cells that form part of the financial statement calculations. In order to resolve this issue, you will have to undo the delete action or revert back to the downloaded version of the template. Remember, we recommend hiding the appropriate sections of the financial statements instead of deleting them.
  • Refer to the income statement and ensure that the control totals next to the income statement are nil. If the control totals contain values, it indicates that you may not have included all income statement accounts in the financial statements. You then need to review the TB sheet to determine whether all the reporting codes which have been linked to accounts are included in the financial statements.
  • Refer to the cash flow statement and ensure that the control totals next to the cash flow statement are nil. If these control totals contain balances, it means that the appropriate closing balance on the cash flow statement does not agree to the cash & cash equivalents balance in the notes to the financial statements. If you have added new balance sheet items to the financial statements, it may mean that you have not added the cash flow movements of these items to the cash flow statement.
  • If none of these steps resolve the imbalance, we suggest filtering the TB sheet by individual allocation codes and tracing the totals at the top of the sheet to the appropriate amounts on the financial statements. Start with the income statement items and make sure that you include all sub codes in your filter selections. This method of reconciling trial balance values to financial statement values should enable you to find the cause of the imbalance.
financial statements template sheet 1
Financial Statements Template - Sheet 1
financial statements template sheet 2
Financial Statements Template - Sheet 2
financial statements template sheet 3
Financial Statements Template - Sheet 3
financial statements template sheet 4
Financial Statements Template - Sheet 4
financial statements template sheet 5
Financial Statements Template - Sheet 5
financial statements template sheet 6
Financial Statements Template - Sheet 6