Financial Analysis Template

This template enables users to compare financial results for 3 annual periods. The user is required to add the appropriate annual trial balances to the template and to link each account on the trial balance to a pre-defined financial statement reporting class. The template produces automated financial reports consisting of an income statement, cash flow statement and balance sheet based on the linked trial balances and compares all amounts on the financial reports between annual periods in terms of the difference in value and percentage.

The template includes the following sheets:
Setup - enter your business name, select the year-end month and enter the reporting year. The business name is used as a heading on all the sheets and the reporting period selections are used to determine the annual periods which need to be included on the financial reports.
Classes - this sheet contains all the pre-defined reporting classes which should be used to link the trial balance accounts on the TB sheet to the calculations on the income statement, cash flow statement and balance sheet. The class descriptions included on this sheet are displayed on the financial statements and the sheet also includes error checking in the Status column 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 a financial statement reporting class by entering or copying the appropriate class code into column B. The amounts included on the financial reports are automatically updated based on the classes specified on this sheet.
IS - this sheet contains an income statement which is automatically calculated based on the reporting classes linked to the accounts on the TB sheet. No user input is required on this sheet but you can customize the report if necessary. The annual periods for which the income statement is compiled is determined based on the annual reporting period which is specified on the Setup sheet.
CFS - this sheet contains a cash flow statement automatically calculated based on the reporting classes linked to the accounts on the TB sheet. No user input is required on this sheet but you can customize the report if necessary. The annual periods for which the cash flow statement is compiled is determined based on the annual reporting period specified on the Setup sheet.
BS - this sheet contains a balance sheet automatically calculated based on the reporting classes linked to the accounts on the TB sheet. No user input is required on this sheet. The annual periods for which the balance sheet is compiled is determined based on the annual reporting period specified on the Setup sheet.
DB - The financial dashboard on this sheet is updated automatically based on the income statement, cash flow statement and balance sheet amounts on the other sheets. The flexible design of this unique financial dashboard also makes it relatively easy to replace calculations and charts which may not be applicable to your business.
TBCheck - when you copy a trial balance from another Excel file into this template, it is important to ensure that the account numbers on the source worksheet are the same as the account numbers on the target trial balance worksheet. We have therefore included this sheet to enable users to check whether their account number sequence is consistent with the TB sheet before copying the trial balance amounts into this template.

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 trial balance values on the TB sheet. All the financial statement reporting classes will already be linked to the appropriate account balances and populated on the income statement, cash flow statement and balance sheet.
• If you want to use your own account structure, replace our default data by copying the account numbers & descriptions 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. Note that a full list of all the main classes that are used on the income statement and balance sheet are included on the Classes sheet.
• The income statement, cash flow statement and balance sheet are automatically calculated based on the amounts included on the TB sheet. Simply select the appropriate reporting period on the Setup sheet and all calculations are updated automatically.

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 through understanding of the template design.

Setup

The input cells that contain a yellow cell background on the Setup sheet need to be completed in order to include your business name as a heading on all the appropriate sheets and to specify the appropriate reporting periods for which the financial reports need to be compiled. The cell with a light blue cell background contains a formula which should not be replaced!

The appropriate year-end month needs to be selected in cell B6. The year-end month that is selected in this cell determines the date that is included in the headings on the income statement, cash flow statement and balance sheet reports.

The financial year for which the financial reports are compiled needs to be specified in cell B8. This year will be used as the current financial year and be compared to the two years preceding it on the financial reports. If you change the year specified in this cell, the income statement, cash flow statement and balance sheet will be updated automatically.

The year-end date in cell B10 is determined based on the year-end period and the reporting year in cells B6 & B8 and is only used in the heading at the top of the income statement, cash flow statement and balance sheet.

Note: If any of the user input cells on the Setup sheet are not completed or if the data entered is invalid, the cell background of the appropriate cell will be highlighted in red. A red cell background therefore indicates that you should replace the data in the appropriate cell with valid input.

Default Reporting Classes

The default reporting classes on which the financial reports in this template are based have been included on the Classes sheet. These classes need to be linked to the individual accounts on the TB sheet in order for the financial reports to be automatically populated on the IS, CFS and BS sheets.

The descriptions included on the income statement and balance sheet are also based on the class descriptions included on the Classes sheet. If you therefore want to change any of the line item descriptions on either the income statement or balance sheet, you need to edit the appropriate reporting class description on the Classes sheet.

The default reporting classes can be edited but we recommend sticking to our coding convention and only adding classes if additional income statement or balance sheet items are required. Note that the codes for all income statement items start with an "I" and the code for all balance sheet items start with a "B". All our reporting codes also end with a "G".

Note: New reporting classes can be added to the Classes sheet by inserting a new row in the appropriate location, entering the new code and entering the description for the new class. You also then need to copy the formula in the Status column which checks for errors.

Error Checking

The formula in the Status column checks whether the appropriate class code in column A has been linked to any of the accounts on the TB sheet and if so, whether the appropriate class has been added to the income statement or balance sheet.

If a code has been linked to the trial balance and not added to the income statement or balance sheet, the balance sheet will probably not balance and it is therefore essential that all codes which have been linked to the trial balance are added to the income statement or balance sheet. An "add!" message will therefore be displayed in the Status column and the column heading will be highlighted in red if any of these items are found.

Once you have added the affected code to the income statement or balance sheet, the errors status will be removed automatically and replaced by an "ok" status.

Trial Balance

A complete trial balance should be copied or entered on the TB sheet for each annual reporting period included in the financial reports. All account numbers and account descriptions that form part of the trial balance need to be copied or entered into 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 then needs to be linked to one of the pre-defined financial statement reporting classes 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: We have included a formula in the Status column (column A) which displays a "class!" message if the appropriate class in column B has not been added to the Classes sheet or an "add!" message if the class has not been added to the income statement or balance sheet. The column heading will also be highlighted in red if any of these errors are found. The formula in this column should be copied for all new accounts that are added to the TB sheet.

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 probably not balance. You should therefore always ensure that the trial balance for each annual period adds up to a nil value.

The template has been designed to compare the financial results for 3 annual periods and you need to add trial balances for at least 3 annual periods to the TB sheet. The cash flow statement data for the 3rd annual period will however not be correct if only 3 annual periods are included and it is recommended to add balance sheets for at least 4 annual periods to the TB sheet.

Example: If the financial reports are to be compiled from the 2018 financial year and this year is specified on the Setup sheet, trial balances for 2018, 2017 and 2016 should be added to the TB sheet. The cash flow statement for the 2016 financial period will however not be accurate if the 2015 balance sheet is not also added as a trial balance on the TB sheet. If you therefore want to see accurate reports for 3 annual periods, we recommend that you also add the 4th financial period to the TB sheet.

The TB sheet contains five financial years by default and in most instances, these financial years will represent the financial periods that the user requires. If you are however performing an analysis of previous periods, you may need to change the financial year headings on the TB sheet. The automated income statement, cash flow statement and balance sheet will automatically pick up the appropriate trial balance amounts based on the year headings that you specify on the TB sheet.

Adding new reporting classes

If you require additional lines to be included on the income statement, cash flow statement or balance sheet, you may need to create additional reporting codes as main income statement or balance sheet categories or as sub codes below the existing reporting class codes.

If the account category that you want to create forms part of the income statement or balance sheet and it is not provided for on the Classes sheet, you can create a new reporting class code and link it to the appropriate accounts on the TB sheet. All new income statement codes should start with "I-" and all new balance sheet codes should start with "B-". You should also ensure that the new code that you create does not clash with any of the existing reporting class codes.

Our reporting class code convention contains a two-character account group in the first two numbers of the reporting class code. If you create a new reporting class for a new account group, you can simply use the next numbers that are available. Create the new class by adding a new row in the appropriate location on the Classes sheet, copying or entering it in column B on the TB sheet and then adding a new row to the appropriate location on the income statement or balance sheet.

You can also create multiple additional reporting classes under the same account group by adding two numbers to the existing account group reporting class code. We have used this method to include detailed expense accounts under the operating expenses section. The main account group class is I-03G and we have linked the individual expense accounts on the trial balance to reporting classes which start with this account group (without the "G") followed by another two numbers for each individual account and ending with a "G". Refer to the IS sheet and note the reporting classes for expenses in column A.

Adding new financial periods

After using the template to compile your first set of financial reports, 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 year 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: All the trial balance data on the TB sheet should form part of a continuous cell range and you should not include any blank rows or columns between rows or columns that contain data otherwise the template calculations will not be accurate.

Trial balance check & import

If you use a trail balance export file in order to compile your financial reports, 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 TBCheck 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 B.
• 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 TBCheck 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 light blue "ok" cell is in the correct sequence but if an "error" is displayed in orange, it means that the sequence of accounts on the TBCheck 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 TBCheck sheet with the account sequence on the TB sheet.
• Where an account is included on the TBCheck sheet but not on the TB sheet, insert a new row on the TB sheet, copy the account number and description into column B and C and enter a nil value into all the columns that relate to previous financial years. After completing these steps, the error in the particular row will automatically be removed from the TBCheck sheet.
• Where an account is included on the TB sheet but not on the TBCheck sheet, insert a new row on the TBCheck 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 light blue "ok" message.
• Repeat the previous two steps until the TBCheck sheet contains no errors.
• Ensure that the total of the amounts in column E on the TBCheck 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 red 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.
• 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 TBCheck 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 red 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 into the appropriate column (for the new period) on the TB sheet. After completing this step, the trial balance has successfully been added to the template.

Rounding in financial statements

Rounding can have a significant impact on financial reports because the amounts that are displayed on the financial reports may not always add up to the totals that are displayed. Even the most insignificant of errors could result in financial reports appearing to be inaccurate.

Checking all the calculations in financial reports 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 reports are always accurate.

The problem with rounding in financial reporting terms is that a trial balance typically includes two decimal values while financial reports 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 together, the total that is calculated is guaranteed to be accurate.

This approach is followed in the calculations in column E on the TBCheck 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 TBCheck 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 reports are guaranteed to be accurate. We therefore recommend always using the TBCheck sheet before including the appropriate trial balance amounts for a new financial period on the TB sheet.

Income Statement

The income statement on the IS sheet is automatically compiled based on the trial balances included on the TB sheet. The first reporting period is determined based on the reporting year specified on the Setup sheet. Aside from amendments to the default line items included on the income statement, the sheet requires no user input.

The calculated amounts for each individual line item on the income statement are based on the reporting classes entered in column A. Most of these line items are based on main reporting classes but we have provided for sub classes to be included in the operating expenses section in order to include expense items individually on the income statement.

Expense Accounts

The template provides for 23 expense accounts to be included on the income statement. Only one of these expense accounts has been linked to an account group class (I-08G for other expenses). All the other accounts have been linked to reporting classes with an additional two numeric characters which have been added to facilitate including individual expense accounts.

When you start using the template, you can use the same classes to link your expense accounts individually and simply edit the class descriptions on the Classes sheet so that the individual expense classes refer to the correct expense account descriptions.

If you need to include more than the default number of expense accounts, you can add additional expense account classes to the Classes sheet, include the appropriate expense account descriptions on this sheet, link the accounts on the TB sheet to the new classes, insert the required number of additional rows on the income statement, copy the formulas from one of the existing rows and change the key in column A to the new class.

Note: If you don't require all the default expense line items which have been included in the standard template, you can delete the items that you do not require by simply deleting the appropriate rows on the IS sheet.

Cash Flow Statement

The cash flow statement on the CFS sheet has been designed on the same basis as the income statement on the IS sheet. The cash flow statement is automatically compiled based on the trial balances included on the TB sheet. The first reporting period is determined based on the reporting year specified on the Setup sheet. Aside from amendments to the default line items included on the cash flow statement, the sheet requires no user input.

If you want to remove a line from the cash flow statement, we strongly recommend that you hide the appropriate line instead of deleting it. If you delete cells which are used in some of the other cash flow statement calculations, you may encounter a calculation error in some of the other rows on the report.

You can also add lines to the cash flow statement by inserting a new row, entering the reporting code on which the calculation should be based in column A and copying the formulas from one of the other similar lines on the cash flow statement.

Example: If we wanted to add another non-cash expense line to the cash flow statement, we would insert a new row below the amortisation expense, enter the reporting class to which the new non-cash expense account has been linked on the TB sheet in column A, enter a description for the non-cash transaction in column B and copy the formulas from the amortisation row into the new blank row (for columns C to J).

New cash flow statement items need to be added to the CFS sheet by using a reporting class which is consistent with the code that has been used to add the appropriate item to the income statement or balance sheet. This will ensure that the appropriate item is also updated on the income statement or balance sheet.

We have included a balance status at the bottom of the CFS sheet in order to check whether the cash balance at the end of the financial period agrees to the cash balance on the balance sheet. If the closing cash balance does not agree to the balance sheet, this row will contain an error message.

Balance Sheet

The balance sheet on the BS sheet has been designed on the same basis as the income statement on the IS sheet. The balance sheet is automatically compiled based on the trial balances included on the TB sheet. The first reporting period is determined based on the reporting year specified on the Setup sheet. Aside from amendments to the default line items included on the balance sheet, the sheet requires no user input.

If you want to remove a line from the balance sheet, we strongly recommend that you hide the appropriate line instead of deleting it. If you delete cells which are used in some of the other balance sheet calculations, you may encounter a calculation error in some of the other rows or on the cash flow statement.

You can also add additional lines to the balance sheet by inserting a new row, entering the reporting code on which the calculation should be based in column A and copying the formulas from one of the other similar lines on the balance sheet.

Note: If you add new lines to the balance sheet, you also need to add the new balance sheet items to the appropriate location on the cash flow statement otherwise the cash flow statement will not balance.

We have added conditional formatting to the rows which contain the total assets and total equity & liabilities calculations to highlight the cells in red if the balance sheet does not balance. Refer to the Troubleshooting section of these instructions for information on the cause of balance sheet imbalances. Once all the errors are fixed, the red highlighting will be removed automatically.

Financial Dashboard

The financial dashboard on the DB sheet is automatically updated based on the income statement, cash flow statement and balance sheet calculations on the other sheets. The flexible design of this unique financial dashboard also makes it relatively easy to replace calculations and charts which may not be applicable to your business.

All the data on which the dashboard calculations are based are included below the dashboard - this section has not been included in the print range and will not be printed. We have designed the dashboard components with as much flexibility as practically possible and also made it relatively easy to replace dashboard components if necessary.

We have included notes on the types of formulas used in column K which indicate how to amend the dashboard items if you wish to do so. We recommend saving a copy of the template before making any changes so that you can revert back to the copy if your changes result in any errors.

Where the notes indicate that the line item is based on an income statement formula, cash flow statement formula or a balance sheet formula, the line item can be based on any other item on the appropriate report by simply linking the line item description in column A to the intended target. The calculations in the other columns and the dashboard will be updated automatically.

Where the notes indicate that a line item formula is used, it means that the formula pertains specifically to the particular line item and that you need to add your own calculation in order to replace the default template calculation. The only other description used is the array formula and we do not recommend making any changes to the top 5 expenses section of the dashboard.

Note: The cells with light blue text are the cells which can be edited. We do not recommend making changes to the cells which are not indicated in light blue text as these contain formulas which are essential for the dashboard to be displayed correctly.

We have included additional flexibility in the charts for the gross profit percentage and the interest cover which results in the first reverting to an operating expense percentage calculation and the last to a taxation percentage calculation if the gross profit percentage or interest paid values are nil.

Roll Forward

The template can easily be rolled forward to include a new financial year by simply adding the trial balance for the appropriate financial year to the TB sheet and changing the year that is selected on the Setup sheet to the new year. All the calculations on the income statement, cash flow statement and balance sheet will be updated automatically to include the new financial year and the two years before it.

Adding Financial Periods

The template has been designed to incorporate 3 financial years in the default design. The formulas have however been designed in such a way that it is relatively easy to add additional financial years to the template.

Additional financial years can basically be added by copying the data in columns H to J on the income statement, cash flow statement and balance sheet and pasting the columns into the same relative position as they are when compared to the copied columns. This essentially means that one column should be left blank after each copied column range.

Example: You can add a fourth financial year to the template by copying columns H to J on the income statement, cash flow statement and balance sheet and pasting these copied columns into columns L to N. Note that one column (K) is left blank.

Example: You can add a fifth financial year to the template by copying columns L to N on the income statement, cash flow statement and balance sheet and pasting these copied columns into columns P to R. Note that one column (O) is left blank.

Troubleshooting

The following troubleshooting steps need to be completed if any of the balance sheets which are included on the BS sheet are not in balance:

• Check whether the total for the appropriate trial balance is nil (refer to the totals above the column heading row on the TB sheet for the appropriate financial year).
• Check the TB sheet for blank rows. No blank rows should be included between rows that contain data otherwise the template calculations may not be accurate.
• Check the Status column on the Classes sheet and ensure that there are no errors. If you see an error in this column, it indicates that the appropriate class has been linked to one or more accounts on the TB sheet and not included in the income statement or balance sheet reports. You then need to include the appropriate reporting class in the appropriate location on the income statement or balance sheet.
• Check whether there are any cells that contain an error message in column A on the TB sheet. If a cell contains an error message, it means that the account has been linked to a reporting class which does not form part of the income statement or balance sheet reports. This may therefore mean that the appropriate account has been linked to an invalid reporting class code. All invalid reporting classes need to be amended and there should be no accounts on the TB sheet which contain errors in column A.
• If you encounter a #REF! error anywhere in the template, it means that you've deleted a row which is used in the formulas that have been included in another row. As we stated earlier on in these instructions, you should rather hide a row instead of deleting it in order to remove the appropriate row from the financial reports. If you've deleted a row which you shouldn't have, you may have to revert back to the downloaded copy of the template or contact our support function for assistance.
• If you've added new reporting classes to the template, check whether all of your new reporting classes have been included on the income statement and balance sheet reports. All reporting classes that have been linked to accounts on the TB sheet should be included on either the IS or BS sheets.
• If your cash flow statement does not balance and you've added new items to the income statement or balance sheet, make sure that you have also added the appropriate items to the cash flow statement. You can insert a new row, copy one of the existing lines which are similar in nature and just change the reporting class in column A and the description in column B.
• If you have completed the above steps and you still have an imbalance on any of your balance sheets, contact our Support function for assistance.