Excel

Excel Skills
Skills || Management
Management Accounts
Accounts Template
Template

About
About this
this template
template
This
This template
template enables
enables users
users to
to compile
compile comprehensive
comprehensive monthly
monthly management
management accounts
accounts from
from any
any trial
trial balance
balance rega
rega
accounts
accounts that
that are
are included
included in
in the
the trial
trial balance.
balance. The
The management
management accounts
accounts are
are compiled
compiled by
by linking
linking each
each account
account in
in the
the
reporting
reporting class
class code
code and
and all
all the
the amounts
amounts that
that are
are included
included in
in the
the management
management accounts
accounts are
are automatically
automatically calculated
calculated bas
ba
The
The template
template includes
includes an
an income
income statement,
statement, cash
cash flow
flow statement
statement and
and balance
balance sheet
sheet and
and compares
compares the
the actual
actual results
results to
t
year-to-date
year-to-date basis.
basis. Prior
Prior year
year results
results are
are also
also included
included on
on all
all the
the financial
financial reports.
reports.
About
About our
our unique
unique templates
templates
Our
Our unique,
unique, practical
practical templates
templates produce
produce results
results that
that are
are more
more comparable
comparable to
to software
software solutions
solutions than
than regular
regular Excel
Excel tem
tem
only
only contain
contain limited
limited functionality
functionality but
but our
our templates
templates produce
produce complex
complex automated
automated reports
reports based
based on
on limited
limited user
user input.
input. YY
Excel
Excel skills
skills in
in order
order to
to use
use our
our templates
templates -- all
all our
our templates
templates only
only require
require basic
basic user
user input
input and
and include
include comprehensive
comprehensive step
step
About
this
sample
file
About this sample file
This
This Excel
Excel document
document is
is only
only aa sample
sample of
of the
the management
management accounts
accounts template.
template. Weve
Weve created
created this
this sample
sample to
to enable
enable cus
cus
features
of
this
template.
You
will
therefore
not
be
able
to
use
this
version
of
the
template
the
full
version
of
features of this template. You will therefore not be able to use this version of the template - the full version of the
the temp
tem
after
after buying
buying the
the template
template or
or aa full
full membership
membership of
of the
the Excel
Excel Skills
Skills website.
website.
Our
Our full
full membership
membership includes:
includes:
Access
Access to
to all
all 40+
40+ of
of our
our unique
unique Excel
Excel templates
templates
365
365 days
days access
access to
to our
our 300+
300+ Excel
Excel video
video tutorials
tutorials
Buy this template
click here

counts from
ccounts
from any
any trial
trial balance
balance regardless
regardless of
of the
the structure
structure of
of the
the
mpiled
mpiled by
by linking
linking each
each account
account in
in the
the trial
trial balance
balance to
to aa pre-defined
pre-defined
unts are
ounts
are automatically
automatically calculated
calculated based
based on
on the
the linked
linked class
class codes.
codes.
et
et and
and compares
compares the
the actual
actual results
results to
to aa forecast
forecast on
on aa monthly
monthly and
and

are solutions
ware
solutions than
than regular
regular Excel
Excel templates.
templates. Most
Most Excel
Excel templates
templates
ports based
eports
based on
on limited
limited user
user input.
input. You
You also
also don't
don't need
need advanced
advanced
nput
nput and
and include
include comprehensive
comprehensive step
step by
by step
step instructions.
instructions.

ve
ve created
created this
this sample
sample to
to enable
enable customers
customers to
to view
view the
the layout
layout and
and
emplate
the
full
version
of
the
template
can
only
be
downloaded
emplate - the full version of the template can only be downloaded

template
here

Register for a full membership

click here

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com
This template enables users to compile comprehensive monthly management accounts from any trial balance regardless
of the structure of the accounts that are included in the trial balance. The management accounts are compiled by linking
each account in the trial balance to a pre-defined reporting class code and all the amounts that are included in the
management accounts are automatically calculated based on the linked class codes. The template includes an income
statement, cash flow statement and balance sheet and compares the actual results to a forecast on a monthly and yearto-date basis. Prior year results are also included on all the financial reports.

On
Onthis
thissheet:
sheet:

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

The following sheets are included in the template:

Set-up - enter your business name, enter the current reporting year, select the appropriate financial year end period and
select the current reporting period. The business name is used as a heading on all the sheets and the reporting period
selections are used to determine the monthly periods that need to be included on the trial balance & forecast sheets and
the periods on which the monthly and year-to-date calculations on the income statement, cash flow statement and balance
sheet reports need to be based.
Classes - this sheet contains all the pre-defined reporting classes that should be used to link the trial balance accounts on
the Key sheet to the calculations on the income statement, cash flow statement and balance sheet. The classes on this
sheet are provided in order to simplify the linking of accounts and are not linked to any of the other sheets. Changes that
are made to the pre-defined classes on this sheet will therefore have no effect on the template calculations.
Key - all the individual accounts that are included on the trial balance needs to be copied or entered onto this sheet and
each account number then needs to be linked to the appropriate reporting class. All the calculations on the income
statement, cash flow statement and balance sheet are automatically updated based on the reporting class that is linked to
each account on this sheet.
TBPY - all the account numbers and appropriate monthly account balances that are included in the trial balance for the
prior year reporting period needs to be included on this sheet. The reporting classes in column A can be added to each
account by simply copying the formula into all the rows that contain an account number. Note that the balance sheet
balances for the year before the prior year reporting period also need to be specified in order to facilitate accurate cash
flow calculations for the prior year financial period.
TBCY - all the account numbers and appropriate monthly account balances that are included in the trial balance for the
current reporting period need to be included on this sheet. The reporting classes in column A can be added to each
account by simply copying the formula into all the rows that contain an account number.
Forecast - a cumulative monthly forecast balance needs to be specified for each account that is included in the current
period trial balance on the TBCY sheet. Note that all the forecasted balances need to be entered on a cumulative basis
(the same basis which is used in order to compile any trial balance) in order for the forecast balances to be included
correctly on the income statement, cash flow statement and balance sheet reports. Income, equity & liability account
balances should be represented by negative values while expenses & asset balances should be represented by positive
values.
IS - this sheet contains an income statement which is automatically calculated based on the reporting classes that have
been linked to the accounts on the Key sheet. No user input is required on this sheet but you can customize the report if
necessary. The monthly & year-to-date periods for which the income statement is compiled is determined based on the
period selections on the Set-up sheet.
CFS - this sheet contains a cash flow statement which is automatically calculated based on the reporting classes that have
been linked to the accounts on the Key sheet. No user input is required on this sheet but you can customize the report if
necessary. The monthly & year-to-date periods for which the cash flow statement is compiled is determined based on the
period selections on the Set-up sheet.
BS - this sheet contains a balance sheet which is automatically calculated based on the reporting classes that have been
linked to the accounts on the Key sheet. No user input is required on this sheet but you can customize the report if
necessary. The monthly & year-to-date periods for which the balance sheet is compiled is determined based on the period
selections on the Set-up sheet.
ISMonth - this sheet contains a monthly income statement which is automatically calculated based on the reporting
classes that have been linked to the accounts on the Key sheet. No user input is required on this sheet but you can
customize the report if necessary. The monthly income statement is based on the actual results that are represented by
the account balances on the TBCY sheet and the monthly periods for which the income statement is compiled is
determined based on the period selections on the Set-up sheet.
TBCheck - when you copy a trial balance from another Excel workbook 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 appropriate trial balance before copying the trial balance data into this template.

Page 3 of 29

This is the only

protected sheet in the
full version of the
template!

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com
Set-up
On
Onthis
thissheet:
sheet:

The input cells that contain a yellow cell background on the Set-up 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
management accounts need to be compiled. The cells with a light blue cell background contain formulas which should not
be replaced!
The appropriate year end period needs to be selected in cell B6. The year end month that is selected in this cell
determines which months are included in the year-to-date calculations and also determines which monthly periods are
included on the trial balance sheets & the monthly income statement on the ISMonth sheet.
The financial year for which the management accounts are compiled needs to be specified in cell B8. The year end date
that is displayed in cell B10 is determined based on the year end period that has been selected in cell B6 and the financial
year that has been entered in cell B8.
The current reporting period needs to be selected from the list box in cell B12. The months that are included in the list box
are determined by the year end and financial year selections in cells B6 and B8. The current reporting period that is
specified determines which monthly and year-to-date periods are included in the income statement, cash flow statement
and balance sheet reports. You can therefore change the period for which the management accounts are compiled by
simply selecting a new period from the list box in this cell.
The reporting period end date and the prior year reporting period which is displayed in cells B14 and B16 are determined
based on the current reporting period that is selected in cell B12. These cells contain formulas which should not be
replaced!
Note: If any of the user input cells on the Set-up sheet are not completed or if the data that is 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.
Example: If you change the financial year in cell B8 and the current reporting period which has been specified in cell B12
falls outside of the new financial year, the cell background of cell B12 will be highlighted in red in order to indicate that a
new current reporting period needs to be selected. When you select a valid period from the list box, the red highlighting
will be removed automatically.

Default Reporting Classes

The default reporting classes on which the management accounts are based have been included on the Classes sheet.
These reporting class codes and descriptions have been provided for information purposes only and are not linked to any
of the other sheets. We have only included this list of reporting classes in order to make it easier to understand to which
income statement and balance sheet categories the default reporting classes that are used in this template refers to.
Note: The default reporting classes that are used in this template are consistent with the default reporting classes which
are used in the Financial Statements template. This consistency therefore makes it easy to use the Management Accounts
template in conjunction with the Financial Statements template.
When users initially link each trial balance account to a reporting class on the Key sheet, users should therefore refer to
the Classes sheet in order to determine which reporting class code relates to which income statement or balance sheet
category.

Linking Classes to Accounts

All the general ledger accounts that form part of the trial balance should be linked to a reporting class in order to facilitate
compiling automated income statement, cash flow statement and balance sheet reports. The management accounts in
this template include actual results for the current financial period, forecasted results for the current financial period and a
comparison to prior year results which are all automatically calculated based on the account balances that are included on
the TBCY, Forecast and TBPY sheets.

Page 4 of 29

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com
The accounts that are included on these three trial balance sheets therefore all have to be linked to the appropriate
reporting classes. It is however essentially the same accounts that need be included on all three sheets and instead of
requiring users to link accounts to reporting classes on all three sheets, we have set up a separate sheet for this purpose
and included a formula in column A on the three trial balance sheets in order to look up the appropriate reporting classes
from this sheet.
All the account numbers that are included in the trial balance as well as the appropriate account descriptions therefore
need to be entered or copied onto the Key sheet in columns A and B. Users then need to link each account to the
appropriate reporting class in column C (refer to the Classes sheet for the appropriate codes and descriptions that can be
used to link an account to an income statement or balance sheet category).
Note: All the entries on the Key sheet must form part of a continuous cell range - you should not add any blank rows
between rows that contain data otherwise the formula in column D will not flag all the invalid reporting class codes that are
included in column C.
Note: We strongly recommend using the default reporting class structure that we have provided on the Classes sheet
because these codes have been included in column A on the income statement, cash flow statement and balance sheet
reports. If you change the reporting codes on the Classes sheet, it will therefore have no effect on the template
calculations.
You can use any trial balance regardless of whether the account format is consistent with our standard template or not.
Our default account numbers have been derived from our accounting templates account format but can be replaced with
the appropriate account format which is used in your own trial balance by simply entering or copying your data over our
default data. Each account should then be linked to our default reporting classes which creates the consistency that is
required in order for the reports to be automatically compiled.
Note: We strongly recommend sticking to the default reporting classes that we have listed on the Classes sheet when
linking accounts otherwise you will also need to enter the new reporting codes in column A on the income statement, cash
flow statement and balance sheet reports.

It is important to note that all the accounts that are included in the trial balance should be linked to a valid reporting class
otherwise the template calculations may not be accurate and the management accounts may therefore not balance. We
have added a formula in column D which should be copied for all the new rows that are added to the Key sheet.
The formula in the Status column displays a nil value if the class code in column C is included on the income statement,
cash flow statement or balance sheet and displays a value of 1 if the class code is not included on any of these reports (in
column A on the IS, CFS or BS sheets). If a reporting code is not included on any of these sheets, it probably means that
the code that has been specified in column C is invalid.
Note: The column heading of the Status column will also be highlighted in red if an invalid status code is present in this
column. All invalid status codes (with values of 1) should be investigated and amended if necessary in order to ensure that
all accounts are linked to valid reporting class codes.

Note: If you create new main or sub reporting codes and you don't include the new codes on either the income statement,
cash flow statement or balance sheet, the new reporting codes will be flagged as invalid on the Key sheet. We therefore
recommend that you only create new reporting codes if a new line item is required on one of these reports.
New Reporting Class Codes
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.

Page 5 of 29

On
Onthis
thissheet:
sheet:

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com

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 code and link it to the appropriate accounts on the Key sheet. All
new income statement codes should however 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.

Example: If we want to create a new reporting class code for short term loans, we would assign a code of "B-SLOANS" to
this balance sheet category. The new code does not clash with any of the existing reporting class codes because there is
no shorter class code which contains essentially the same characters. A new code of "B-LOANSH" will however clash with
the long term loans reporting class ("B-LOAN") because the first 6 characters are the same (reporting classes which clash
may lead to inaccuracies in the template calculations). We therefore recommend that you ensure that at least the first four
characters of a new reporting class are unique when compared to the default reporting classes.
Note: New reporting classes are essentially created when the appropriate classes are entered in column C on the Key
sheet thereby linking the new code to the appropriate account number. It is not required to add the new reporting class to
the Classes sheet but it may be a good idea to do so if you want other users to be able to easily understand to which
income statement or balance sheet category each reporting class refers to.
An unlimited number of new reporting class sub codes can also be created by simply linking the relevant accounts to the
appropriate sub code. In terms of the standard template design, new sub codes may be required in order to increase the
number of individual expense accounts which are included on the income statement or to add an additional line item to the
cash flow statement.

Example: The standard template provides for 21 individual expense accounts on the income statement which have been
linked to 21 sub reporting classes - 19 accounts which have been linked to the "I-ADM" income statement class, 1 account
which has been linked to the "I-ADV" income statement class and 1 account which has been linked to the "I-OTX" income
statement class. These sub codes have been created by simply adding a two digit number to the main reporting class
code and the individual sub codes have then been included in column A on the IS sheet (which contains the income
statement). We can therefore add 10 additional expense accounts to the income statement by simply linking sub codes "IADM20" to "I-ADM29" to the appropriate accounts on the Key sheet, inserting 10 new rows on the income statement,
entering the new sub codes in column A and copying the formulas in all the other columns from one of the existing
expense account rows.

Example: If we wanted to add an additional line item to the cash flow statement, the same principle would essentially be
followed but the sub code that we would create would not necessarily be included as an individual item on the income
statement. If we wanted to add back a profit on a revaluation which has been included in the "Other income" income
statement category (with a main class code of "I-REVO"), we can simply link the appropriate account on the Key sheet to
a sub class of "I-REVO01", insert the appropriate new row on the cash flow statement, enter the new sub code in column
A and copy the formulas from one of the existing rows for a similar item. The individual account balance will then be
included on the cash flow statement but the account will still form part of the Other Income line on the income statement.

Prior Year Trial Balance

A complete trial balance for the prior year financial period should be copied or entered onto the TBPY sheet. The account
balances that are included on this sheet are used to update the "Prior Year" columns on the income statement, cash flow
statement and balance sheet reports.
Note: All the accounts that are included in your trial balance should be entered in a continuous cell range on the TBPY
sheet - there should not be any blank rows between rows that contain data otherwise all the accounts may not be included
in the management accounts calculations and your balance sheet may therefore not balance!
The reporting class in column A and the account description in column C are automatically looked up based on the entries
on the Key sheet. The formulas which have been included in these columns therefore need to be copied for all the rows
on the TBPY sheet which contain account numbers in column B.

Page 6 of 29

On
Onthis
thissheet:
sheet:

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com

Note: If an account number is included in the trial balance but the account number has not been linked to a valid reporting
class on the Key sheet, the appropriate cell in column A will contain a "no key!" message and the column heading in
column A will be highlighted in red. If any of the three trial balance sheets contain accounts that are not linked to valid
reporting classes, your management accounts may not balance. If the "no key!" message is therefore encountered, you
should add the appropriate account number to the Key sheet and link the account number to a valid reporting class. Once
this step has been completed, the red highlighting in the column heading cell will be removed automatically.
The monthly periods that are included in the column heading row are automatically determined based on the financial year
end and reporting year that are specified in cells B6 and B8 on the Set-up sheet. All the appropriate account balances for
these monthly periods need to be entered or copied into the cells in the appropriate columns but you should not edit any of
the column heading cells! If you want to change the dates that are included in the column headings, you should do so by
changing the reporting period selections on the Set-up sheet.
The monthly management accounts can only be as accurate as the trial balance data that is included on the three trial
balance sheets. If your trial balance therefore does not balance, your monthly management accounts will also not balance!
You should therefore always ensure that all the monthly totals that we have included above the column heading row equal
nil - we have also added conditional formatting to these cells which highlights all the monthly totals that are not equal to nil
in red.
Note: You may notice that while you are adding accounts to the trial balance on this sheet (by inserting new rows in the
appropriate location based on the account number sequence), the totals are highlighted in red - this formatting simply
indicates that empty rows have been inserted and will automatically disappear when an account number is entered in
column B for all the blank rows.
Note: If you are setting up management accounts for a new business with no prior financial year, we recommend that you
still add all the trial balance accounts to the TBPY sheet but that you include nil values in all the monthly columns. This will
ensure that all the "Prior Year" columns in the management accounts reflect nil values.

You may notice that the TBPY sheet includes an additional column that has a column heading which has been formatted
with an orange cell background and contains the date of the year end month which precedes the prior financial year. This
column is required in order to produce accurate cash flow statement calculations for the prior financial year.
It is not necessary to include a complete trial balance for this period in column D - you can only include the appropriate
balance sheet account balances as at the end of this period (income statement accounts can be omitted). The trial
balance will however only balance if the retained earnings balance as at the end of this period is included in the account
which is linked to the retained earnings reporting class.
Note: A trial balance usually only includes the retained earnings balance at the beginning of the appropriate financial
period because the profit or loss for the current financial period is in effect included in the individual income statement
account balances but because we are not including the income statement accounts in this column, the retained earnings
account balance needs to be adjusted in order to reflect the retained earnings balance at the end of the financial period.
This adjustment is therefore only required in this column - no adjustments are required for any of the other trial balances
which form part of this template.

Note: You can check the adjusted retained earnings balance by comparing it to the retained earnings account balance for
the subsequent months on the TBPY sheet - the retained earnings balance in column D should be the same as the
retained earnings account balances in columns E to P. This is because these columns include the retained earnings
balance at the beginning of the prior financial year which will be the same balance as at the end of the month in column C.

Current Year Trial Balance

A complete trial balance for the current financial period should be copied or entered onto the TBCY sheet. The account
balances that are included on this sheet are used to update the "Actual" columns on the income statement, cash flow
statement and balance sheet.

Page 7 of 29

On
Onthis
thissheet:
sheet:

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com
Note: All the accounts that are included in your trial balance should be entered in a continuous cell range on the TBCY
sheet - there should not be any blank rows between rows that contain data otherwise all the accounts may not be included
in the management accounts calculations and your balance sheet may therefore not balance!
The reporting class in column A and the account description in column C are automatically looked up based on the entries
on the Key sheet. The formulas which have been included in these columns therefore need to be copied for all the rows
on the TBCY sheet which contain account numbers in column B.

Note: If an account number is included in the trial balance but the account number has not been linked to a valid reporting
class on the Key sheet, the appropriate cell in column A will contain a "no key!" message and the column heading in
column A will be highlighted in red. If any of the three trial balance sheets contain accounts that are not linked to valid
reporting classes, your management accounts may not balance. If the "no key!" message is therefore encountered, you
should add the appropriate account number to the Key sheet and link the account number to a valid reporting class. Once
this step has been completed, the red highlighting in the column heading cell will be removed automatically.
The monthly periods that are included in the column heading row are automatically determined based on the financial year
end and reporting year that are specified in cells B6 and B8 on the Set-up sheet. All the appropriate account balances for
these monthly periods need to be entered or copied into the cells in the appropriate columns but you should not edit any of
the column heading cells! If you want to change the dates that are included in the column headings, you should do so by
changing the reporting period selections on the Set-up sheet.
The monthly management accounts can only be as accurate as the trial balance data that is included on the three trial
balance sheets. If your trial balance therefore does not balance, your monthly management accounts will also not balance!
You should therefore always ensure that all the monthly totals that we have included above the column heading row equal
nil - we have also added conditional formatting to these cells which highlights all the monthly totals that are not equal to nil
in red.
Note: You may notice that while you are adding accounts to the trial balance on this sheet (by inserting new rows in the
appropriate location based on the account number sequence), the totals are highlighted in red - this formatting simply
indicates that empty rows have been inserted and will automatically disappear when an account number is entered in
column B for all the blank rows.

Forecast Trial Balance

A complete trial balance for the current financial period forecast or budget should be copied or entered onto the Forecast
sheet. The account balances that are included on this sheet are used to update the "Forecast" columns on the income
statement, cash flow statement and balance sheet.
Note: All the accounts that are included in your trial balance should be entered in a continuous cell range on the Forecast
sheet - there should not be any blank rows between rows that contain data otherwise all the accounts may not be included
in the management accounts calculations and your balance sheet may therefore not balance!
The reporting class in column A and the account description in column C are automatically looked up based on the entries
on the Key sheet. The formulas which have been included in these columns therefore need to be copied for all the rows
on the Forecast sheet which contain account numbers in column B.

Note: If an account number is included in the trial balance but the account number has not been linked to a valid reporting
class on the Key sheet, the appropriate cell in column A will contain a "no key!" message and the column heading in
column A will be highlighted in red. If any of the three trial balance sheets contain accounts that are not linked to valid
reporting classes, your management accounts may not balance. If the "no key!" message is therefore encountered, you
should add the appropriate account number to the Key sheet and link the account number to a valid reporting class. Once
this step has been completed, the red highlighting in the column heading cell will be removed automatically.

Page 8 of 29

On
Onthis
thissheet:
sheet:

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com
The monthly periods that are included in the column heading row are automatically determined based on the financial year
end and reporting year that are specified in cells B6 and B8 on the Set-up sheet. All the appropriate account balances for
these monthly periods need to be entered or copied into the cells in the appropriate columns but you should not edit any of
the column heading cells! If you want to change the dates that are included in the column headings, you should do so by
changing the reporting period selections on the Set-up sheet.
The monthly management accounts can only be as accurate as the trial balance data that is included on the three trial
balance sheets. If your trial balance therefore does not balance, your monthly management accounts will also not balance!
You should therefore always ensure that all the monthly totals that we have included above the column heading row equal
nil - we have also added conditional formatting to these cells which highlights all the monthly totals that are not equal to nil
in red.
Note: You may notice that while you are adding accounts to the trial balance on this sheet (by inserting new rows in the
appropriate location based on the account number sequence), the totals are highlighted in red - this formatting simply
indicates that empty rows have been inserted and will automatically disappear when an account number is entered in
column B for all the blank rows.
Important: Trial balances are always compiled on a cumulative basis - this means that the monthly movement in account
balances are added together when calculating the appropriate month end trial balance amounts. Forecasts are however
usually not compiled on a cumulative balance basis which means that you may have to convert your forecast balances to
cumulative balances before copying or entering the appropriate balances on the Forecast sheet otherwise your
management accounts will not be accurate (this conversion is usually only necessary for income statement accounts
because balance sheet account balances are always cumulative).

Income Statement

The income statement on the IS sheet is automatically compiled based on the trial balances that are included on the
TBCY, TBPY and Forecast sheets and the financial reporting periods that have been specified on the Set-up sheet. All the
amounts in the Forecast columns are calculated from the Forecast sheet, all the amounts in the Actual columns are
calculated from the TBCY sheet and all the amounts in the Prior Year columns are calculated from the TBPY sheet.
The income statement is calculated automatically and no user input is required on this sheet. If you want to change the
period for which the monthly and year-to-date balances are calculated, you need to select a new current reporting period
from the list box in cell B12 on the Set-up sheet.
The monthly section is based on the appropriate month which has been selected and the year-to-date section is based on
all the months from the beginning of the financial year up to the selected period. Note that the financial year end month is
also specified on the Set-up sheet.
The calculated amounts for each individual line item on the income statement are based on the reporting classes that
have been 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 Expenses section in order to include expense items individually on the income
statement.
Expense Accounts
The standard template provides for 21 individual expense accounts to be included on the income statement. All of the
appropriate expense accounts have therefore been linked to 21 sub reporting classes on the Key sheet - 19 accounts
have been linked to the "I-ADM" income statement class, 1 account has been linked to the "I-ADV" income statement
class and 1 account has been linked to the "I-OTX" income statement class.

These sub codes have been created by simply adding a two digit number to the main income statement reporting class
codes (as per the Classes sheet). As with all the other income statement calculations, the line items on the income
statement are calculated based on the reporting class code in column A. The formulas in each line in the expenses section
of the income statement are therefore exactly the same - it is only the code in column A which differs from line to line.

Page 9 of 29

On
Onthis
thissheet:
sheet:

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com
When you initially start using the template, you can link the expense accounts that you want to include individually on the
income statement to these pre-defined sub classes or you can create your own expense reporting classes by linking the
appropriate accounts on the Key sheet to your class codes and then entering the appropriate class codes in column A on
the IS sheet.
Note: It should not be too complex to change the format of the reporting classes in the expenses section of the income
statement but we don't recommend changing the default reporting codes for the other income statement and balance
sheet categories because this may result in inaccurate template calculations!

You can add an unlimited number of additional expense accounts to the income statement quite easily - simply insert the
appropriate number of additional rows, enter a new reporting class sub code in column A, link the new reporting class to
the appropriate expense account on the Key sheet and copy the formulas in columns B to M from one of the other rows in
the expenses section. All the income statement calculations will be updated automatically and the description of the
expense will be updated based on the expense account description which has been included on the Key sheet.
Note: If you don't require all 21 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 account balances that are entered on the TBPY,
TBCY and Forecast sheets. No user input is required on this sheet and the reporting period is based on the current
reporting period selection in cell B12 on the Set-up sheet.
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 simply inserting a new row, entering the appropriate 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 in row 14, enter the reporting class to which the new non-cash expense account has been
linked on the Key sheet in column A, enter a description for the non-cash transaction in column B and copy the formulas in
row 14 into the new blank row (for columns C to M).
New cash flow statement items need to be added to the CFS sheet by using a reporting class sub code 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. You may also need to the
review the reporting codes of the items which form part of the cash flow statement in the standard template in order to
ensure that the correct reporting class codes have been specified in column A.
Example: If the depreciation account has been linked to a different reporting class sub code on the income statement, the
default reporting class code in the depreciation line of the cash flow statement will need to be amended. If an additional
other income item needs to be added to the cash flow statement, a sub code under the "I-REVO" reporting class needs to
be created for this purpose, for example "I-REVO08". If the sub code which is created is not already included on the
income statement, your balance sheet will probably not balance.
We have included a control total 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 control total in row 45 does not agree to the
closing balance at the end of the period, the appropriate closing balance will be highlighted in red. The highlighting will
only be removed once the imbalance has been rectified.

Page 10 of 29

On
Onthis
thissheet:
sheet:

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com
Balance Sheet
On
Onthis
thissheet:
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 account balances that are entered on the TBPY, TBCY and
Forecast sheets. No user input is required on this sheet and the reporting period is based on the current reporting period
selection in cell B12 on the Set-up sheet.
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 simply inserting a new row, entering the appropriate 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.

Example: If we wanted to add another current liability line to the balance sheet, we would insert a new row at the
appropriate location in the current liabilities section of the balance sheet, enter the reporting class to which the new current
liability category has been linked on the Key sheet in column A, enter a description for the current liability in column B and
copy the formulas from one of the other current liability categories into the new blank row (for columns C to M).
Note: It is imperative that the new reporting class that is used for the new line on the balance sheet does not clash with
any of the default reporting classes. Refer to the Linking Classes to Accounts section of these instructions for more
information about the calculation errors that may be encountered when reporting classes clash.
We have added conditional formatting to the rows that contain the total assets and total equity & liabilities calculations in
order to highlight the appropriate 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.

Monthly Income Statement

The monthly income statement on the ISMonth sheet is automatically compiled based on the account balances that are
entered on the TBCY sheet. No user input is required on this sheet and the reporting period is based on the current
reporting period selection in cell B12 on the Set-up sheet.
The calculated amounts for each individual line item on the monthly income statement are based on the reporting classes
that have been 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 Expenses section in order to include expense items individually on the income
statement.
Note: If you've amended the accounts and reporting class codes which are included in the Expenses section of the income
statement on the IS sheet, the same amendments should also be implemented on the ISMonth sheet in order for both
income statements to be calculated consistently.

Trial Balance Check

If you use a trail balance export file in order to compile management accounts, the trial balance export file will have to be
amended so that the account numbers are in the exact same order as the accounts on the appropriate trial balance sheet
before you will be able to copy the account balances onto the appropriate sheet. This is a necessity in order to ensure that
the correct account balances are included next to the correct account numbers.
New accounts may also have been added during the current financial period which may not be included in the previous
trial balance on the TBCY 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 added
the TBCheck sheet to assist users in simplifying this exercise.

Page 11 of 29

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com
Note: All the accounts that are included on the TBCheck sheet should form part of a continuous cell range - there should
not be any blank rows between rows that contain data otherwise all the account balances may not be included in the total
calculation above the column heading row.
The following steps need to be completed in order to ensure that the relevant trial balance is correctly included on the
appropriate trial balance sheet:
Select the trial balance that you need to amend from the list box in cell E3. You can select the Current option in order to
check the account sequence on the TBCY sheet, the Prior option to check the account number sequence on the TBPY
sheet or the Forecast option to check the account number sequence on the Forecast sheet.
Sort the data on the appropriate trial balance sheet (TBCY, TBPY or Forecast sheet) in an ascending order based on the
account numbers in column B.
Sort the data in the trial balance export file in an ascending order based on the account number column.
Copy the account number and account balance from the export file and paste the data into column A and B on the
TBCheck sheet (replace the existing data on this sheet).
Copy the formula in cell C6 and paste it into all the rows that contain an account number in column A. Note that all the
cells in column C will now contain formulas and formatting which will assist you in identifying the changes that need to be
made to the existing account structure on the TBCheck sheet before the account balances can be copied onto the
appropriate trial balance sheet.
Review the contents in column C - 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 TBCheck sheet is not the same as the sequence of
accounts on the appropriate trial balance sheet.
Check the contents of all the rows that contain errors by comparing the account sequence on the TBCheck sheet to the
account sequence on the appropriate trial balance sheet (TBCY, TBPY or Forecast).
Where an account number is included on the TBCheck sheet but not on the trial balance sheet, determine whether the
account number has been included on the Key sheet. If the account number cannot be found on the Key sheet, insert a
new row in the appropriate location on the Key sheet, add the account number and account description to the Key sheet
and link the account to a valid reporting class code.
Then also insert a new row on the appropriate trial balance sheet (TBCY, TBPY or Forecast), copy the account number
into column B and enter a nil value into all the columns on the trial balance sheet. 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 appropriate trial balance sheet but not on the TBCheck sheet, insert a new row on
the TBCheck sheet, copy the account number from the appropriate TB sheet and paste it into column A, enter a nil value
in column B and copy the formula in column C from one of the existing cells in column C. Column C should now contain a
green "ok" message.
Repeat the previous three steps until the TBCheck sheet contains no errors.
Ensure that the total of the amounts in column B on the TBCheck sheet is nil - the total calculation is included in the row
above the column headings. If the total of all the account balances on the TBCheck sheet 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 blank rows may cause
an inaccurate total calculation).
If the total row contains a nil value, the amounts in column B can be copied and pasted as values into the appropriate
column on the relevant trial balance sheet (TBCY, TBPY or Forecast). After completing this step, the appropriate trial
balance has successfully been included in the template.

Roll Forward
The following steps need to be completed in order to roll the management accounts template forward for the next financial
period:
Save the workbook under a new file name in order to create an exact copy of the template which can be used for the
next financial period (use the File Save As feature).
Open the TBPY sheet and copy the account balances in column P into column C. This step effectively transfers the prior
financial period account balances into the period which precedes the prior financial period.
Compare the account number sequence on the TBPY and TBCY sheets and ensure that all the accounts that are
included on the TBCY sheet have also been added to the TBPY sheet. The account number sequence on both of these
sheets should be exactly the same.
Open the TBCY sheet and copy all the account balances in columns D to O into columns E to P on the TBPY sheet (but
do not copy the column heading row!). This effectively transfers the current financial period account balances into the prior
year financial period.
Open the TBCY sheet and replace all the existing account balances with nil values in order to replace the results for the
previous financial period with nil values.

Page 12 of 29

On
Onthis
thissheet:
sheet:

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com
Open the Forecast sheet and replace the forecast for the previous financial period with the forecast for the current
financial period (you may have to use the TBCheck sheet for this purpose).
Open the Set-up sheet and change the reporting year in cell B8 to the current reporting year.
Select the first monthly period from the list box in cell B12.
Check that the Actual columns on the IS sheet now contain nil values. Also check that the Forecast and Prior Year
columns contain values and that all the balance sheets balance. If you encounter any imbalances, refer to the
Troubleshooting section of these instructions.
The monthly trial balances for the current financial period can now be added to the TBCY sheet after each month end
and the template has now effectively been rolled forward for the new financial year.

Troubleshooting
The following troubleshooting steps need to be completed if any of the balance sheets that 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
appropriate sheet). If the balance sheet in the Prior column does not balance, refer to the trial balance on the TBPY sheet;
if the balance sheet in the Forecast sheet does not balance, refer to the Forecast sheet and if the balance sheet in the
Actual column does not balance, refer to the TBCY sheet.
Open the Key sheet and check whether there are any rows which do not contain a nil value in the Status column. If the
status for any account number is 1, it means that the account has been linked to a reporting class which does not form
part of the income statement, cash flow statement or balance sheet reports. This may therefore mean that the appropriate
account has been linked to an invalid reporting class. All invalid reporting classes need to be amended and there should
be no accounts on the Key sheet which contain a status of 1.
Review all three trial balance sheets (TBPY, TBCY and Forecast sheets) and ensure that there are no "no key!" error
messages in column A. If the Code column heading is highlighted in red, it indicates that a "no key!" message is present in
the column. These error messages indicate that the appropriate account has not been included on the Key sheet and is
therefore not linked to a valid reporting class. The errors can be fixed by adding the account to the Key sheet and linking it
to a valid reporting class.
Open the IS sheet and check whether all your expense accounts have been added to the Expenses section on the
income statement. If an expense account is omitted, the appropriate balance sheet will not balance. Also make sure that
all the expense accounts on the IS sheet have been added to the ISMonth sheet.
If you encounter a #REF! error in the management accounts, 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 management accounts. 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 your balance sheets all balance but your cash balance at the end of the period does not agree to the cash balance on
the balance sheet, check that the retained earnings balance in the first column on the TBPY sheet equals the retained
earnings for the other monthly periods on this sheet. If these balances are not equal, you may encounter an imbalance on
the cash flow statement.
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, cash flow statement and balance sheet reports. All reporting classes that have been
linked to accounts on the Key sheet should form part of the management accounts.
Once you have completed the above steps and you still have an imbalance on any of your balance sheets, contact our
Support function for assistance.

Help & Customization

If you experience any difficulty while using this template and you are not able to find the appropriate guidance in these
instructions, please e-mail us at [email protected] for assistance. This template has been designed with flexibility
in mind to ensure that it can be used in most business environments. If however you need an Excel based template that is
customized specifically for your business requirements, please e-mail our Support function and provide a brief explanation
of your requirements.

Page 13 of 29

On
Onthis
thissheet:
sheet:

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Excel Skills | Management Accounts Template

Instructions
www.excel-skills.com
Copyright
On
Onthis
thissheet:
sheet:

This template remains the intellectual property of www.excel-skills.com and is protected by international copyright laws.
Any publication or distribution of this template outside the scope of the permitted use of the template is expressly
prohibited. In terms of the permitted use of this template, only the distribution of the template to persons within the same
organisation as the registered user or persons outside the organisation who can reasonably be expected to require access
to the template as a direct result of the use of the template by the registered user is allowed. Subsequent distribution of
the template by parties outside of the organisation is however expressly prohibited and represents an infringement of
international copyright laws.

Page 14 of 29

This
This sheet
sheet includes
includes detailed
detailed instructions
instructions
on
on setting
setting up
up and
and using
using this
this template.
template.

Management Accounts Template

Set-up
www.excel-skills.com
Business Name

ABC Trading (Pty) Limited

Year End Period

February
On
On this
this sheet:
sheet:

Reporting Year

2016

Year End Date

29 February 2016

Current Reporting Period

Feb-2016

Reporting Period End Date

29 February 2016

Prior Year Reporting Period

Feb-2015

Enter
Enter your
your business
business name
name and
and select
select the
the appropriate
appropriate year
year end
end period,
period,
reporting
reporting year
year and
and current
current reporting
reporting period.
period. The
The periods
periods that
that are
are selected
selected on
on
this
this sheet
sheet determine
determine which
which monthly
monthly periods
periods are
are included
included on
on the
the trial
trial balances
balances
and
and forecast
forecast and
and also
also determine
determine the
the current
current month
month and
and year-to-date
year-to-date periods
periods
that
that are
are included
included on
on the
the income
income statement,
statement, cash
cash flow
flow statement
statement and
and balance
balance
sheet
sheet reports.
reports.

January
February
March
April
May
June
July
August
September
October
November
December

Page 15 of 29

Management Accounts Template

Default Reporting Classes
www.excel-skills.com
Class

Description

Income Statement
I-REVS

Sale of goods

I-REVO

Other revenue

I-COS

Cost of sales

I-DIS

Distribution costs

I-ADV

Advertising & marketing costs

I-ADM

Administrative expenses

I-OTX

Other expenses

I-FIN

Finance costs

I-TAX

Income tax expense

I-DIV

Dividends

Balance Sheet - Assets

B-PPE

Property, plant & equipment

B-IPA

Intangible assets

B-VEST

Investments

B-DTAX

Deferred tax

B-INV

Inventory

B-DEBT

Trade debtors

B-CASH

Cash on hand

B-BANK

Cash at bank

On
On this
this sheet:
sheet:

This
This sheet
sheet contains
contains all
all the
the default
default financial
financial statement
statement reporting
reporting classes
classes that
that should
should be
be used
used in
in
order
order to
to link
link the
the accounts
accounts on
on the
the Key
Key sheet
sheet to
to the
the calculations
calculations on
on the
the income
income statement,
statement, cash
cash
flow
flow statement
statement and
and balance
balance sheet
sheet reports.
reports. The
The default
default classes
classes are
are provided
provided in
in order
order to
to simplify
simplify
the
the linking
linking of
of accounts
accounts and
and are
are consistent
consistent with
with the
the classes
classes that
that are
are used
used in
in the
the Financial
Financial
Statements
Statements template
template which
which therefore
therefore makes
makes itit easy
easy to
to use
use this
this template
template in
in conjunction
conjunction with
with the
the
Financial
Financial Statements
Statements template.
template. The
The cells
cells in
in column
column A
A are
are not
not linked
linked to
to the
the class
class codes
codes on
on the
the
report
sheets
changing
the
default
classes
on
this
sheet
will
therefore
have
no
effect
on
the
report sheets - changing the default classes on this sheet will therefore have no effect on the
template
template calculations.
calculations.

Balance Sheet - Equity

B-SCAP

Share capital

B-RET

Retained earnings at start of year

Balance Sheet - Liabilities

B-LOAN

Long term loans

B-FLEASE

Finance leases

B-CREDT

Trade payables

B-PROV

Provisions

B-INT

Interest payable

B-DIV

Dividends payable

B-TAX

Current tax liability

Page 16 of 29

ABC Trading (Pty) Limited

Report Key
www.excel-skills.com
Acc No

Account Description

BS-100

Property, Plant & Equipment

B-PPE

BS-150

Accumulated Depreciation

B-PPE

BS-200

Investments

B-VEST

BS-300

Cash - Other

B-CASH

BS-399

Cash Transfer Control

B-BANK

BS-400

Debtors - Other

B-DEBT

BS-500

Creditors - Other

B-CREDT

BS-510

Interest Payable

B-INT

BS-520

Dividends Payable

B-DIV

BS-530

Provision for Taxation

B-TAX

BS-550

Provisions

B-PROV

BS-600

Sales Tax Control

B-CREDT

BS-700

Long Term Liabilities

B-LOAN

BS-800

Share Capital

B-SCAP

BS-900

Reserves

B-RET

IS-100

Turnover

I-REVS

IS-200

Stock Purchases

I-COS

IS-305

Accounting Fees

I-ADM01

IS-310

Advertising & Marketing

I-ADV01

IS-315

Bank Charges

I-ADM02

IS-320

Commission

I-ADM03

IS-325

Computer Expenses

I-ADM04

IS-330

Consumables & Cleaning

I-ADM05

IS-335

Entertainment

I-ADM07

IS-340

Insurance

I-ADM08

IS-345

Office Expenses

I-ADM09

IS-350

Office Rent

I-ADM10

IS-355

Postage

I-ADM11

IS-360

Professional & Legal Fees

I-ADM12

IS-365

Salaries & Wages

I-ADM13

IS-370

Stationery

I-ADM14

IS-375

Subscriptions & Memberships

I-ADM15

IS-380

Telephone & Internet

I-ADM16

IS-385

Training

I-ADM17

IS-390

Travelling & Accommodation

I-ADM18

IS-395

Utilities

I-ADM19

IS-400

Depreciation

I-ADM06

IS-500

Interest Paid

I-FIN

IS-600

Taxation

I-TAX

IS-650

Dividends Paid

I-DIV

IS-700

Other Expenses

I-OTX01

IS-800

Other Income

I-REVO

IS-810

Dividends Received

I-REVO02

IS-820

Interest Received

I-REVO03

IS-830

Profit on Disposal of Asset

I-REVO04

BS-BANKB1

B1 Bank Account

B-BANK

BS-BANKB2

B2 Bank Account

B-BANK

BS-BANKB3

B3 Bank Account

B-BANK

BS-PCASHPC

Petty Cash

B-CASH

BS-DEBTORS

Trade Debtors

B-DEBT

BS-CREDITORS

Trade Creditors

B-CREDT

BS-INVENTORY

Inventory

B-INV

IS-INV MOVEMENT Inventory Movement

I-COS

BS-RETAINED

B-RET

Retained Earnings

Class

Status

On
On this
this sheet:
sheet:

This
This sheet
sheet enables
enables users
users to
to link
link each
each account
account in
in the
the trial
trial balance
balance to
to the
the
appropriate
appropriate reporting
reporting class.
class. All
All account
account balance
balance are
are automatically
automatically included
included on
on
the
the income
income statement,
statement, cash
cash flow
flow statement
statement and
and balance
balance sheet
sheet reports
reports based
based on
on
the
reporting
class
that
is
specified
for
the
appropriate
account
on
this
sheet.
the reporting class that is specified for the appropriate account on this sheet.
Note
that
the
account
numbers
that
are
included
in
the
trial
balances
can
be
in
Note that the account numbers that are included in the trial balances can be in
any
any format
format because
because the
the accounts
accounts are
are linked
linked to
to our
our pre-defined
pre-defined reporting
reporting
classes
classes for
for reporting
reporting purposes.
purposes.

Page 17 of 29

ABC Trading (Pty) Limited

Monthly Trial Balance - Prior Year
www.excel-skills.com

Feb-2014

Mar-2014

Apr-2014

May-2014

Jun-2014

Jul-2014

Aug-2014

Sep-2014

Oct-2014

Nov-2014

Dec-2014

Jan-2015

Class

Acc No

Account Description

Feb-2015

B-PPE

BS-100

Property, Plant & Equipment

B-PPE

BS-150

Accumulated Depreciation

B-VEST

BS-200

Investments

B-CASH

BS-300

Cash - Other

B-BANK

BS-399

Cash Transfer Control

B-DEBT

BS-400

Debtors - Other

B-CREDT

BS-500

Creditors - Other

B-INT

BS-510

Interest Payable

B-DIV

BS-520

Dividends Payable

B-TAX

BS-530

Provision for Taxation

B-PROV

BS-550

Provisions

B-CREDT

BS-600

Sales Tax Control

B-LOAN

BS-700

Long Term Liabilities

B-SCAP

BS-800

Share Capital

B-RET

BS-900

Reserves

I-REVS

IS-100

Turnover

-44,215.79

-89,959.47

-142,017.36

-202,233.14

-254,299.20

-306,561.04

-387,396.05

-462,496.05

-522,939.25

-581,478.72

-642,383.98

-725,898.84

I-COS

IS-200

Stock Purchases

20,561.06

42,537.24

69,275.33

99,282.49

128,582.48

165,806.29

211,106.29

251,438.68

280,418.67

315,570.11

348,603.44

391,915.50

I-ADM01

IS-305

Accounting Fees

631.58

1,263.15

1,894.73

2,526.30

3,157.88

3,789.45

4,421.03

5,052.60

5,684.18

6,315.75

6,947.33

7,578.90

I-ADV01

IS-310

Advertising & Marketing

947.37

1,894.73

2,842.10

3,789.47

4,736.84

5,684.20

6,631.57

7,578.94

8,763.15

9,947.36

11,131.57

12,315.78

I-ADM02

IS-315

Bank Charges

39.47

78.95

118.42

157.90

197.37

236.84

276.32

315.79

355.27

394.74

434.21

473.69

I-ADM03

IS-320

Commission

I-ADM04

IS-325

Computer Expenses

I-ADM05

IS-330

Consumables & Cleaning

I-ADM07

IS-335

Entertainment

I-ADM08

IS-340

Insurance

I-ADM09

IS-345

Office Expenses

I-ADM10

IS-350

Office Rent

I-ADM11

IS-355

Postage

I-ADM12

IS-360

Professional & Legal Fees

I-ADM13

IS-365

Salaries & Wages

10,000.00

20,000.00

30,000.00

40,000.00

50,000.00

60,000.00

70,000.00

I-ADM14

IS-370

Stationery

81.32

157.10

232.89

313.42

468.15

537.62

652.89

I-ADM15

IS-375

Subscriptions & Memberships

947.37

947.37

947.37

947.37

947.37

I-ADM16

IS-380

Telephone & Internet

2,640.79

3,505.26

4,424.99

5,343.95

6,251.06

7,173.95

I-ADM17

IS-385

Training

I-ADM18

IS-390

Travelling & Accommodation

I-ADM19

IS-395

Utilities

I-ADM06

IS-400

Depreciation

I-FIN

IS-500

Interest Paid

300.00

I-TAX

IS-600

Taxation

1,440.00

I-DIV

IS-650

Dividends Paid

I-OTX01

IS-700

Other Expenses

I-REVO

IS-800

Other Income

-7,200.00

-7,200.00

-7,200.00

-7,200.00

I-REVO02

IS-810

Dividends Received

-500.00

-500.00

-500.00

-500.00

I-REVO03

IS-820

Interest Received

I-REVO04

IS-830

Profit on Disposal of Asset

B-BANK

BS-BANKB1

B1 Bank Account

7,471.45

8,932.46

14,043.12

19,853.91

22,817.56

7,874.06

20,988.77

6,904.68

6,397.38

B-BANK

BS-BANKB2

B2 Bank Account

1,200.00

1,200.00

1,200.00

1,200.00

1,200.00

1,200.00

1,200.00

1,200.00

1,200.00

B-BANK

BS-BANKB3

B3 Bank Account

B-CASH

BS-PCASHPC

Petty Cash

900.00

900.00

900.00

900.00

900.00

900.00

900.00

900.00

900.00

900.00

900.00

900.00

900.00

B-DEBT

BS-DEBTORS

Trade Debtors

45,200.00

50,100.00

49,200.00

55,300.00

66,750.00

62,900.00

31,200.00

92,100.00

85,200.00

60,750.00

65,800.00

62,300.00

81,540.00

B-CREDT

BS-CREDITORS

Trade Creditors

-27,340.00

-26,780.00

-29,320.00

-36,320.00

-41,200.00

-33,100.00

-33,800.00

-60,300.00

-42,800.00

-31,870.00

-35,210.00

-34,560.00

-63,100.00

B-INV

BS-INVENTORY

Inventory

21,620.00

20,220.00

22,700.00

22,440.00

27,880.00

28,013.00

32,210.00

31,810.00

28,210.00

26,740.00

23,310.00

24,660.00

22,310.00

I-COS

IS-INV MOVEMENT

Inventory Movement

-1,400.00

-3,880.00

-3,620.00

-9,060.00

-9,193.00

-13,390.00

-12,990.00

-9,390.00

-7,920.00

-4,490.00

-5,840.00

-3,490.00

B-RET

BS-RETAINED

Retained Earnings

-1,808.00

-1,808.00

-1,808.00

-1,808.00

-1,808.00

-1,808.00

-1,808.00

-1,808.00

-1,808.00

-1,808.00

-1,808.00

-1,808.00

30,100.00

30,100.00

30,100.00

30,100.00

30,100.00

45,100.00

45,100.00

45,100.00

45,100.00

45,100.00

45,100.00

45,100.00

45,100.00

-22,600.00

-23,100.00

-23,600.00

-24,100.00

-24,600.00

-25,100.00

-25,800.00

-26,500.00

-27,200.00

-27,900.00

-28,600.00

-29,300.00

-30,000.00

5,000.00

5,000.00

5,000.00

5,000.00

5,000.00

8,000.00

8,000.00

On
On
thissheet:
sheet:
- this

A
A complete
complete trial
trial balance
balance for
for the
the prior
prior year
year reporting
reporting period
period should
should be
be copied
copied or
or entered
entered
- the year before
onto
onto this
this sheet.
sheet. The
The balance
balance sheet
sheet balances
balances at
at the
the end
end of
of the year before the
the prior
prior year
year
reporting
period
also
need
to
be
included
in
the
first
column
(for
cash
flow
statement
reporting period also need to be included in the first column (for cash flow statementcalculation
A
- purposes).
- formula
- the
calculation
purposes). The
The
formula in
in column
column
Aneeds
needs to
to-be
be copied
copied for
for all
all
the new
new accounts
accounts
that
and
the
that are
are-added
added to
to this
this sheet
sheet
and determines
determines
the reporting
reporting
class which
which is
linked to
to each
each
- class
-is linked
account
on
the
Key
sheet.
All
the
account
balances
on
this
sheet
are
automatically
updated
account on
sheet. All the account
on this sheet are
automatically-9,690.00
updated
- the Key-1,440.00
-3,230.00balances-4,250.00
-8,890.00
to
to the
the income
income statement,
statement, cash
cash flow
flow statement
statement and
and balance
balance sheet
sheet reports
reports in
in the
the "Prior
"Prior Year"
Year"
-3,000.00
-3,000.00
-3,000.00
-3,000.00
-3,000.00
columns.
columns. -3,000.00

-3,000.00

-4,465.00

-6,765.00

-8,865.00

-9,395.00

-6,125.00

-4,200.00

-4,200.00

-4,200.00

-4,200.00

-3,380.00

-2,700.00
-5,000.00
-4,200.00

-2,863.45

-3,706.05

-1,995.76

-3,357.28

-2,984.75

-4,865.17

-629.21

-4,380.38

-3,144.34

864.22

-1,908.46

-4,483.40

-4,907.16

-48,780.00

-48,160.00

-47,540.00

-46,920.00

-46,300.00

-45,680.00

-45,060.00

-44,440.00

-43,820.00

-43,200.00

-42,580.00

-50,200.00

-49,380.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-1,808.00

315.79

252.63
6,315.79

872.37

500.00

631.58

505.26
12,631.57

1,760.53

1,000.00

947.38

757.89
18,947.36

444.47
-

1,120.63

1,120.63

1,120.63

1,120.63

1,120.63

1,120.63

1,120.63

1,120.63

1,120.63

77.36

77.36

77.36

118.41

118.41

159.46

159.46

236.83

303.14

1,010.52
25,263.14

444.47
-

1,263.15
31,578.93

603.16
-

1,515.78

1,768.41

2,021.04

2,273.67

2,526.30

2,794.73

3,063.15

1,261.58

1,261.58

1,261.58

1,261.58

1,261.58

1,261.58

1,261.58

37,894.72

44,210.50

50,526.29

56,842.07

63,157.86

69,473.65

75,789.43

743.69
-

100,000.00

130,000.00

150,000.00

802.89

859.73

1,003.42

1,076.05

1,162.89

1,305.20

1,305.20

1,305.20

1,305.20

1,305.20

8,112.64

9,082.90

10,037.38

10,983.96

532.90

532.90

532.90

532.90

532.90

3,112.11

3,112.11

3,270.01

3,375.31

3,375.31

900.00

1,200.00

1,500.00

1,800.00

2,100.00

2,400.00

2,700.00

3,000.00

3,300.00

3,600.00

4,250.00

8,890.00

9,690.00

11,290.00

15,755.00

18,055.00

20,155.00

20,685.00

17,415.00

13,615.00

Page 18 of 29

6,700.00

600.00

14,491.77

6,000.00

3,230.00

5,300.00

2,500.00

4,600.00

2,500.00

2,000.00

3,900.00

90,000.00

1,500.00

3,200.00

743.69

80,000.00

7,400.00

5,000.00

800.00

20,800.00

-7,200.00

-8,500.00

-8,500.00

-500.00

-500.00

-500.00

20,826.96

17,874.57

18,336.94

1,200.00

1,200.00

1,800.00

1,800.00

10,000.00

10,000.00

10,000.00

10,000.00

ABC Trading (Pty) Limited

Monthly Trial Balance - Current Year
www.excel-skills.com

Mar-2015

Apr-2015

May-2015

Jun-2015

Jul-2015

Aug-2015

Sep-2015

Oct-2015

Nov-2015

Dec-2015

Jan-2016

Class

Acc No

Account Description

Feb-2016

B-PPE

BS-100

Property, Plant & Equipment

B-PPE

BS-150

Accumulated Depreciation

B-VEST

BS-200

Investments

B-CASH

BS-300

Cash - Other

B-BANK

BS-399

Cash Transfer Control

B-DEBT

BS-400

Debtors - Other

B-CREDT

BS-500

Creditors - Other

B-INT

BS-510

Interest Payable

B-DIV

BS-520

Dividends Payable

B-TAX

BS-530

Provision for Taxation

B-PROV

BS-550

Provisions

B-CREDT

BS-600

Sales Tax Control

B-LOAN

BS-700

Long Term Liabilities

B-SCAP

BS-800

Share Capital

B-RET

BS-900

Reserves

I-REVS

IS-100

Turnover

I-COS

IS-200

I-ADM01

IS-305

I-ADV01

IS-310

Advertising & Marketing

I-ADM02

IS-315

Bank Charges

I-ADM03

IS-320

Commission

I-ADM04

IS-325

Computer Expenses

I-ADM05

IS-330

Consumables & Cleaning

I-ADM07

IS-335

Entertainment

I-ADM08

IS-340

Insurance

I-ADM09

IS-345

Office Expenses

I-ADM10

IS-350

Office Rent

I-ADM11

IS-355

Postage

I-ADM12

IS-360

Professional & Legal Fees

I-ADM13

IS-365

Salaries & Wages

I-ADM14

IS-370

Stationery

I-ADM15

IS-375

Subscriptions & Memberships

I-ADM16

IS-380

Telephone & Internet

I-ADM17

IS-385

Training

I-ADM18

IS-390

Travelling & Accommodation

I-ADM19

IS-395

Utilities

I-ADM06

IS-400

Depreciation

I-FIN

IS-500

Interest Paid

I-TAX

IS-600

Taxation

I-DIV

IS-650

Dividends Paid

I-OTX01

IS-700

Other Expenses

1,000.00

1,000.00

1,000.00

1,000.00

1,000.00

1,000.00

1,000.00

I-REVO

IS-800

Other Income

-10,000.00

-10,000.00

-10,000.00

-20,877.19

-20,877.19

-20,877.19

-20,877.19

I-REVO02

IS-810

Dividends Received

-1,000.00

-1,000.00

-1,000.00

-1,000.00

-1,000.00

-1,000.00

-1,000.00

I-REVO03

IS-820

Interest Received

-523.00

-624.00

-710.00

-807.00

-909.00

-1,014.00

-1,122.00

I-REVO04

IS-830

Profit on Disposal of Asset

B-BANK

BS-BANKB1

B1 Bank Account

13,987.00

15,450.74

21,816.32

19,866.60

28,155.02

63,117.02

15,222.99

20,751.74

B-BANK

BS-BANKB2

B2 Bank Account

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

B-BANK

BS-BANKB3

B3 Bank Account

600.00

600.00

600.00

600.00

600.00

600.00

600.00

600.00

13,000.00

13,000.00

13,000.00

13,000.00

B-CASH

BS-PCASHPC

Petty Cash

1,100.00

1,100.00

1,100.00

1,099.55

1,100.55

1,100.55

1,100.00

1,000.00

1,100.00

1,000.00

900.00

1,000.00

B-DEBT

BS-DEBTORS

Trade Debtors

54,200.00

55,460.00

63,200.00

73,200.00

63,210.00

23,450.00

98,230.00

91,200.00

64,240.00

70,900.00

73,800.00

87,515.00

B-CREDT

BS-CREDITORS

Trade Creditors

-26,073.26

-27,571.42

-38,985.72

-37,208.58

-36,360.00

-45,868.58

-55,560.00

-49,598.86

-36,276.00

-43,681.72

-41,140.00

-68,948.46

B-INV

BS-INVENTORY

Inventory

23,210.00

25,690.00

25,430.00

30,870.00

31,003.00

35,200.00

34,800.00

31,200.00

29,730.00

26,300.00

27,650.00

25,300.00

I-COS

IS-INV MOVEMENT

Inventory Movement

-900.00

-3,380.00

-3,120.00

-8,560.00

-8,693.00

-12,890.00

-12,490.00

-8,890.00

-7,420.00

-3,990.00

-5,340.00

-2,990.00

45,100.00

45,100.00

50,100.00

50,100.00

50,100.00

50,100.00

50,100.00

50,100.00

50,100.00

50,100.00

50,100.00

50,100.00

-30,700.00

-31,400.00

-32,400.00

-33,400.00

-34,400.00

-35,400.00

-36,400.00

-37,400.00

-38,400.00

-39,400.00

-40,400.00

-41,400.00

8,000.00

8,000.00

8,000.00

8,000.00

8,000.00

8,000.00

8,000.00

8,000.00

8,000.00

8,000.00

8,000.00

8,000.00

A
A complete
complete trial
trial balance
balance for
for the
the current
current reporting
reporting period
period should
should be
be copied
copied or
or entered
entered
- sheet.
-A needs
onto
in
for
onto this
this
sheet. The
The formula
formula
in column
column A
needs to
to be
be copied
copied
for all
all the
the-new
new accounts
accounts that
to
and
the
- class
that are
are added
added
to this
this sheet
sheet
and determines
determines
the reporting
reporting
class which
which is
is- linked
linked to
to each
each account
All
balances
account
on the
the Key
Key sheet.
sheet.
All the
the account
account
balances on
on- this
this sheet
sheet are
are automatically
- on
-automatically updated
the
income
statement,
cash
flow
statement
and
balance
sheet
reports
in
the
updated to
to
the
income
statement,
cash
flow
statement
and
balance
sheet
reports
in
the"Actual"
"Actual" columns.
columns.

On
On this
thissheet:
sheet:

-1,570.00

-3,490.00

-4,575.00

-9,455.00

-8,835.00

-4,000.00

-3,000.00

-5,000.00

-4,000.00

-3,000.00

-4,000.00

-3,440.00

-4,540.00

-9,420.00

-7,820.00

-7,600.00

-5,000.00

-3,000.00

-5,000.00

-4,000.00

-4,500.00

-3,900.00
-10,000.00
-5,000.00

-3,901.11

-2,100.80

-3,533.98

-3,141.84

-5,121.23

-662.33

-4,610.93

-3,309.83

909.71

-2,008.90

-4,719.37

-5,165.43

-49,380.00

-48,760.00

-48,140.00

-47,520.00

-46,900.00

-46,280.00

-45,660.00

-45,040.00

-44,420.00

-43,800.00

-43,180.00

-42,560.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-47,543.86

-96,192.98

-151,631.57

-215,842.09

-271,289.46

-326,947.36

-413,114.03

-493,114.03

-557,354.03

-619,547.01

-684,283.85

-773,332.09

Stock Purchases

21,643.22

44,776.04

72,921.40

104,507.88

135,349.98

174,532.94

222,217.15

264,672.29

295,177.55

332,179.06

366,950.99

412,542.63

Accounting Fees

701.75

1,403.50

2,105.25

2,807.00

3,508.75

4,210.50

4,912.25

5,614.00

6,315.75

7,017.50

7,719.25

8,421.00

1,052.63

2,105.26

3,157.89

4,210.52

5,263.15

6,315.78

7,368.41

8,421.04

9,736.83

11,052.62

12,368.41

13,684.20

43.86

87.72

131.58

175.44

219.30

263.16

307.02

350.88

394.74

438.60

482.46

526.32

350.88

280.70
7,017.54

701.76

561.40
14,035.08

10,000.00

20,000.00

90.35

174.56

969.30

700.00

1,956.14

1,052.64

842.10
21,052.62

30,000.00

1,245.14

1,245.14

1,245.14

1,245.14

1,245.14

1,245.14

1,245.14

1,245.14

1,245.14

85.96

85.96

85.96

131.57

131.57

177.18

177.18

263.14

336.82

1,122.80

1,403.50

28,070.16

40,000.00

35,087.70

55,000.00

1,684.20

1,964.90

2,245.60

2,526.30

2,807.00

3,105.25

3,403.50

1,401.75

1,401.75

1,401.75

1,401.75

1,401.75

1,401.75

1,401.75

42,105.24

49,122.78

56,140.32

63,157.86

70,175.40

77,192.94

84,210.48

70,000.00

85,000.00

100,000.00

115,000.00

130,000.00

150,000.00

170,000.00
1,292.10

258.77

348.24

520.17

597.36

725.43

892.10

955.26

1,114.91

1,195.61

1,052.63

1,052.63

1,052.63

1,052.63

1,052.63

1,450.22

1,450.22

1,450.22

1,450.22

1,450.22

2,934.21

3,894.73

4,916.66

5,937.72

6,945.62

7,971.06

9,014.04

10,092.11

11,152.64

12,204.40

493.86
-

493.86

670.18

826.32
-

826.32
-

592.11

592.11

592.11

592.11

3,457.90

3,633.34

3,750.34

3,750.34

5,400.00

440.00

880.00

1,320.00

1,760.00

2,200.00

2,640.00

3,080.00

3,520.00

3,960.00

4,400.00

4,840.00

5,280.00

1,570.00

3,490.00

4,575.00

9,455.00

8,835.00

9,605.00

13,045.00

14,145.00

19,025.00

17,425.00

17,205.00

19,955.00

-89.00
-

-177.00
-

-258.00
-

-338.00

-428.00

Page 19 of 29

37,806.88

39,031.88

10,400.00

4,400.00

9,400.00

3,400.00

8,400.00

2,400.00

7,400.00

1,400.00

6,400.00

592.11
3,457.90

35,189.16

11,400.00

10,000.00

55,584.16

ABC Trading (Pty) Limited

Monthly Trial Balance - Current Year
www.excel-skills.com

Class

Acc No

Account Description

B-RET

BS-RETAINED

Retained Earnings

Mar-2015
-28,600.00

Apr-2015
-28,600.00

May-2015
-28,600.00

Jun-2015

Jul-2015

-28,600.00

-28,600.00

Page 20 of 29

Aug-2015
-28,600.00

Sep-2015
-28,600.00

Oct-2015
-28,600.00

Nov-2015
-28,600.00

Dec-2015
-28,600.00

Jan-2016
-28,600.00

Feb-2016
-28,600.00

ABC Trading (Pty) Limited

Monthly Forecast
www.excel-skills.com

Mar-2015

Apr-2015

May-2015

Jun-2015

Jul-2015

Aug-2015

Sep-2015

Oct-2015

Nov-2015

Dec-2015

Jan-2016

Class

Acc No

Account Description

Feb-2016

B-PPE

BS-100

Property, Plant & Equipment

B-PPE

BS-150

Accumulated Depreciation

B-VEST

BS-200

Investments

B-CASH

BS-300

Cash - Other

B-BANK

BS-399

Cash Transfer Control

B-DEBT

BS-400

Debtors - Other

B-CREDT

BS-500

Creditors - Other

B-INT

BS-510

Interest Payable

B-DIV

BS-520

Dividends Payable

B-TAX

BS-530

Provision for Taxation

B-PROV

BS-550

Provisions

B-CREDT

BS-600

Sales Tax Control

B-LOAN

BS-700

Long Term Liabilities

B-SCAP

BS-800

Share Capital

B-RET

BS-900

Reserves

I-REVS

IS-100

Turnover

I-COS

IS-200

I-ADM01

IS-305

I-ADV01

IS-310

Advertising & Marketing

I-ADM02

IS-315

Bank Charges

I-ADM03

IS-320

Commission

I-ADM04

IS-325

Computer Expenses

I-ADM05

IS-330

Consumables & Cleaning

I-ADM07

IS-335

Entertainment

I-ADM08

IS-340

Insurance

I-ADM09

IS-345

Office Expenses

I-ADM10

IS-350

Office Rent

I-ADM11

IS-355

Postage

I-ADM12

IS-360

Professional & Legal Fees

I-ADM13

IS-365

Salaries & Wages

I-ADM14

IS-370

Stationery

I-ADM15

IS-375

Subscriptions & Memberships

I-ADM16

IS-380

Telephone & Internet

I-ADM17

IS-385

Training

I-ADM18

IS-390

Travelling & Accommodation

I-ADM19

IS-395

Utilities

I-ADM06

IS-400

Depreciation

I-FIN

IS-500

Interest Paid

I-TAX

IS-600

Taxation

I-DIV

IS-650

Dividends Paid

I-OTX01

IS-700

Other Expenses

1,000.00

1,000.00

1,000.00

1,000.00

1,000.00

1,000.00

1,000.00

I-REVO

IS-800

Other Income

-500.00

-500.00

-500.00

-23,000.00

-23,000.00

-53,000.00

-53,000.00

I-REVO02

IS-810

Dividends Received

-500.00

-500.00

-500.00

-500.00

-500.00

-500.00

-500.00

I-REVO03

IS-820

Interest Received

-600.00

-700.00

-800.00

-900.00

-1,000.00

-1,100.00

-900.00

I-REVO04

IS-830

Profit on Disposal of Asset

B-BANK

BS-BANKB1

B1 Bank Account

27,320.00

40,750.00

30,580.00

35,610.00

24,140.00

-7,710.00

-10,530.00

61,200.00

B-BANK

BS-BANKB2

B2 Bank Account

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

1,800.00

B-BANK

BS-BANKB3

B3 Bank Account

600.00

600.00

600.00

600.00

600.00

600.00

600.00

600.00

23,000.00

23,000.00

23,000.00

23,000.00

B-CASH

BS-PCASHPC

Petty Cash

1,100.00

1,100.00

1,100.00

1,100.00

1,100.00

1,100.00

1,100.00

1,000.00

1,100.00

1,000.00

900.00

1,000.00

B-DEBT

BS-DEBTORS

Trade Debtors

54,200.00

30,000.00

45,000.00

66,000.00

82,500.00

97,500.00

120,000.00

70,000.00

65,000.00

80,000.00

101,000.00

115,000.00

B-CREDT

BS-CREDITORS

Trade Creditors

-26,100.00

-13,500.00

-21,000.00

-30,000.00

-40,500.00

-52,500.00

-66,000.00

-65,000.00

-50,000.00

-59,000.00

-69,000.00

-83,000.00

B-INV

BS-INVENTORY

Inventory

23,310.00

25,810.00

25,310.00

30,810.00

31,310.00

35,310.00

34,810.00

31,310.00

29,810.00

26,310.00

27,610.00

25,310.00

I-COS

IS-INV MOVEMENT

Inventory Movement

-1,000.00

-3,500.00

-3,000.00

-8,500.00

-9,000.00

-13,000.00

-12,500.00

-9,000.00

-7,500.00

-4,000.00

-5,300.00

-3,000.00

45,100.00

45,100.00

55,100.00

55,100.00

55,100.00

55,100.00

55,100.00

55,100.00

60,100.00

60,100.00

60,100.00

60,100.00

-30,700.00

-31,400.00

-32,400.00

-33,400.00

-34,400.00

-35,400.00

-36,400.00

-37,400.00

-38,400.00

-39,400.00

-40,400.00

-41,400.00

8,000.00

8,000.00

10,000.00

10,000.00

10,000.00

10,000.00

10,000.00

10,000.00

10,000.00

10,000.00

10,000.00

10,000.00

1,400.00

On
On this
this sheet:
sheet:

A
A monthly
monthly forecast
forecast or
or budget
budget for
for the
the current
current reporting
reporting period
period should
should be
be copied
copied or
or
- onto
- The
- column
- to
entered
entered
onto this
this sheet.
sheet.
The formula
formula in
in
column A
Aneeds
needs
to be
be copied
copied-for
for all
all the
the new
new accounts
- determines
- reporting
- which
accounts- that
that are
are added
added-to
to this
this sheet
sheet and
and
determines the
the
reporting class
class
which is
is linked
linked
to
to each
account on
on
the Key
Key sheet.
sheet.-All
All the
the account
account-balances
balances on
on this
this
sheet are
are
-each account
- the
- sheet
automatically
updated
to
the
income
statement,
cash
flow
statement
and
balance
sheet
automatically
updated to
cash flow
statement and
- the income statement,
- balance sheet
reports
reports in
in the
the "Forecast"
"Forecast" columns.
columns.
-2,500.00

-4,550.00

-4,830.00

-11,830.00

-9,930.00

-4,300.00

-5,000.00

-9,600.00

-8,600.00

-7,600.00

-6,600.00

2,000.00

4,400.00

-880.00

-7,255.00

-8,530.00

-3,835.00

-14,110.00

-4,600.00

-5,600.00

-7,600.00

-5,600.00

-5,000.00

1,000.00
-10,000.00
-4,600.00

-3,900.00

-2,000.00

-3,500.00

-3,000.00

-5,000.00

-650.00

-5,000.00

-3,500.00

1,000.00

-2,000.00

-5,000.00

-5,000.00

-59,380.00

-58,760.00

-58,140.00

-57,520.00

-56,900.00

-56,280.00

-55,660.00

-55,040.00

-54,420.00

-53,800.00

-53,180.00

-52,560.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-100.00

-50,000.00

-100,000.00

-150,000.00

-220,000.00

-275,000.00

-325,000.00

-400,000.00

-500,000.00

-550,000.00

-600,000.00

-670,000.00

-750,000.00

Stock Purchases

21,000.00

45,000.00

70,000.00

100,000.00

135,000.00

175,000.00

220,000.00

265,000.00

300,000.00

330,000.00

365,000.00

410,000.00

Accounting Fees

700.00

1,400.00

2,100.00

2,800.00

3,500.00

4,200.00

4,900.00

5,600.00

6,300.00

7,000.00

7,700.00

8,400.00

1,100.00

2,200.00

3,300.00

4,400.00

5,500.00

6,600.00

7,700.00

8,800.00

10,100.00

11,400.00

12,700.00

14,000.00

50.00

100.00

150.00

200.00

250.00

300.00

350.00

400.00

450.00

500.00

550.00

600.00

400.00

300.00
7,000.00

800.00

600.00
14,000.00

10,000.00

20,000.00

100.00

200.00

1,000.00

700.00

2,000.00

1,200.00

900.00
21,000.00

30,000.00

1,400.00

1,400.00

1,400.00

1,400.00

1,400.00

1,400.00

1,400.00

1,400.00

1,400.00

100.00

100.00

100.00

100.00

100.00

100.00

100.00

200.00

300.00

1,200.00

1,500.00

28,000.00

40,000.00

35,000.00

55,000.00

1,800.00

2,100.00

2,400.00

2,700.00

3,000.00

3,300.00

3,600.00

1,400.00

1,400.00

1,400.00

1,400.00

1,400.00

1,400.00

1,400.00

42,000.00

49,000.00

56,000.00

63,000.00

70,000.00

77,000.00

84,000.00

70,000.00

85,000.00

100,000.00

120,000.00

140,000.00

160,000.00

180,000.00
1,500.00

600.00

700.00

800.00

1,000.00

1,100.00

1,300.00

1,400.00

1,100.00

1,100.00

1,500.00

1,500.00

1,500.00

1,500.00

1,500.00

3,000.00

4,000.00

5,000.00

6,000.00

7,000.00

8,000.00

9,000.00

10,100.00

11,200.00

12,300.00

500.00
-

800.00

1,100.00

4,550.00

400.00

400.00

1,100.00

2,500.00

-200.00

300.00

2,400.00

1,100.00

1,400.00

-100.00

500.00

700.00

900.00
-

900.00
-

3,400.00

4,400.00

5,400.00

6,400.00

1,200.00

1,600.00

2,000.00

2,400.00

2,800.00

4,830.00

11,830.00

9,930.00

5,730.00

6,610.00

-300.00
-

-400.00

-500.00

Page 21 of 29

600.00

600.00

600.00

600.00

600.00

3,500.00

3,500.00

3,700.00

3,800.00

3,800.00

7,400.00

9,400.00

3,200.00

3,600.00

4,000.00

4,400.00

4,800.00

12,985.00

14,260.00

9,565.00

19,840.00

20,065.00

29,330.00

12,660.00

10,400.00

8,400.00

33,090.00

11,400.00

10,000.00

24,785.00

ABC Trading (Pty) Limited

Monthly Forecast
www.excel-skills.com

Class

Acc No

Account Description

B-RET

BS-RETAINED

Retained Earnings

Mar-2015
-28,600.00

Apr-2015
-28,600.00

May-2015
-28,600.00

Jun-2015

Jul-2015

-28,600.00

-28,600.00

Page 22 of 29

Aug-2015
-28,600.00

Sep-2015
-28,600.00

Oct-2015
-28,600.00

Nov-2015
-28,600.00

Dec-2015
-28,600.00

Jan-2016
-28,600.00

Feb-2016
-28,600.00

ABC Trading (Pty) Limited

Income statement for the period ended 29 February 2016
www.excel-skills.com

Current Month
Forecast

Actual

Diff

Year-to-Date
Diff%

Prior Year

Forecast

Actual

Diff

Diff%

Prior Year

Revenue

80,000

89,048

9,048

11.3%

83,515

750,000

773,332

23,332

3.1%

725,899

Cost of sales

47,300

47,942

(642)

(1.4%)

45,662

407,000

409,553

(2,553)

(0.6%)

388,426

Gross profit

32,700

41,107

8,407

25.7%

37,853

343,000

363,779

20,779

6.1%

337,473

Gross profit %

40.9%

46.2%

5.3%

45.3%

45.7%

47.0%

1.3%

Other Income

(200)

108

308

154.0%

54,400

22,999

(31,401)

(57.7%)

9,000

31,650

31,599

51

0.2%

50,239

325,800

314,918

10,882

3.3%

297,467

(21)

(0.3%)

7,579

316

2.3%

12,316

74

12.3%

474

155

11.1%

1,121

(37)

(12.3%)

303

7,400

Expenses
Accounting Fees
Advertising & Marketing
Bank Charges

700
1,300
50

Commission

Computer Expenses

Consumables & Cleaning

Depreciation
Entertainment
Insurance
Office Expenses

100
1,000

On
Onthis
thissheet:
sheet:

702
(2) on
632
8,400
8,421
The
sheet
calculated
based
The income
income statement
statement
on this
this(0.3%)
sheet is
is automatically
automatically
calculated
based on
on the
the
reporting
periods
that
are
specified
on
the
Set-up
sheet
and
the
account
balances
1,316
(16)
(1.2%)
1,184
14,000
13,684
reporting periods that are specified on the Set-up sheet and the account balances
on
sheets.
on the
the trial
trial balance
balance and
and
forecast
sheets. No
No user
user
input is
is required
required
on this
this sheet
sheet
44
6 forecast
12.3%
39input
600 on
526
but
customise the
line items that
are included on
statement
but you
you can
on the
the income
income
statement
-can customise
- the line items
- that are included
by
by creating
creating the
the appropriate
appropriate reporting
reporting classes
classes on
on the
the Key
Key sheet,
sheet, entering
entering the
the
1,400
1,245
appropriate
appropriate classes
classes in
in column
column A
A and
and copying
copying the
the formulas
formulas from
from one
one of
of the
the
74
26
26.3%
300
337
existing
existing rows.
rows. 66
1,000

700

11,400

11,400

46.5%

300

298

0.6%

268

3,600

3,404

197

5.5%

3,063

1,400

1,402

(2)

(0.1%)

1,262

7,000

7,018

(18)

(0.3%)

6,316

84,000

84,210

(210)

(0.3%)

75,789

Postage

Professional & Legal Fees

20,000

20,000

20,000

180,000

170,000

10,000

5.6%

150,000

100

96

3.5%

87

1,500

1,292

208

13.9%

1,163

1,500

1,450

50

3.3%

1,305

1,100

1,052

48

4.4%

947

12,300

12,204

96

0.8%

10,984

Training

600

592

1.3%

533

Travelling & Accommodation

3,800

3,750

50

1.3%

3,375

Utilities

Other Expenses

20,000

1,000

1,000

20,800

Profit / (Loss) before finance costs

850

9,615

8,765

1,031.2%

(12,386)

71,600

71,860

260

0.4%

49,007

Finance costs

400

440

(40)

(10.0%)

300

4,800

5,280

(480)

(10.0%)

3,600

Profit / (Loss) before tax

450

9,175

8,725

1,939.0%

(12,686)

66,800

66,580

(220)

(0.3%)

45,407

Income tax expense

225

2,750

(2,525)

(1,122.2%)

(3,800)

20,065

19,955

110

0.5%

13,615

Profit / (Loss) for the period

225

6,425

6,200

2,755.8%

(8,886)

46,735

46,625

(110)

(0.2%)

31,792

Retained earnings at start of period

75,110

68,800

(6,310)

(8.4%)

42,486

28,600

28,600

1,808

Dividends

10,000

10,000

5,000

10,000

10,000

5,000

Retained earnings at end of period

65,335

65,225

(110)

(0.2%)

28,600

65,335

65,225

(110)

(0.2%)

28,600

Office Rent

Salaries & Wages

Stationery
Subscriptions & Memberships
Telephone & Internet

Page 23 of 29

ABC Trading (Pty) Limited

Statement of cash flows for the period ended 29 February 2016
www.excel-skills.com

Current Month
Forecast

Actual

Diff

Year-to-Date
Diff%

Prior Year

Forecast

Actual

Diff

Diff%

Prior Year

Cash flows from operating activities

Profit / (Loss) for the period

225

6,425

6,200

2,755.8%

(8,886)

46,735

46,625

(110)

(0.2%)

- Finance costs

400

440

40

10.0%

300

4,800

5,280

480

10.0%

3,600

- Income taxes

225

2,750

2,525

1,122.2%

(3,800)

20,065

19,955

(110)

(0.5%)

13,615

- Interest Received

200

(108)

(308)

(154.0%)

(900)

(1,122)

(222)

(24.7%)

(500)

(1,000)

(500)

(100.0%)

(500)

7,400

400

100.0%

1,200

(36,340)

- Dividends received
Non-cash income and expenses:
- Depreciation

1,000

- Amortisation

- Increase / (Decrease) in provisions

(400)

Cash flow included in investing activities:

- (Gains) / Losses on sale of equipment

Changes in working capital:

On
On this
this sheet:
sheet:

The
The cash
cash flow
flow statement
statement on
on this
this sheet
sheet is
is automatically
automatically calculated
calculated based
based on
on the
the
1,000 periods
700sheet
11,400
11,400
reporting
account
reporting
periods that
that -are
are specified
specified -on
on the
the Set-up
Set-up
sheet and
and the
the
account balances
balances
on
No
is
- balance
-on this
-but
on the
the trial
trial
balance and
and- forecast
forecast sheets.
sheets.
No user
user input
input
is required
required on
this sheet
sheet but
you
the
that
flow
you can
can
customise 900
the line
line items
items
that are
are included
included
on the
the cash
cash400
flow statement
statement
by
500customise
225.0%
820 on
800by
creating
creating the
the appropriate
appropriate reporting
reporting classes
classes on
on the
the Key
Key sheet,
sheet, entering
entering the
the
appropriate
appropriate classes
classes in
in column
column A
Aand
and copying
copying the
the formulas
formulas from
from one
one of
of the
the existing
existing
- rows.
rows.

31,792

- Decrease / (Increase) in receivables

(10,600)

(13,715)

(3,115)

(29.4%)

(19,240)

(34,460)

(5,975)

28,485

82.7%

- Decrease / (Increase) in inventories

2,300

2,350

50

2.2%

2,350

(3,000)

(2,990)

10

0.3%

(690)

14,000

32,155

18,155

129.7%

31,664

17,293

7,307

(9,986)

(57.7%)

40,504

Cash generated from operations

7,350

31,797

24,447

332.6%

3,907

61,833

80,280

18,447

29.8%

60,580

Interest paid

(400)

(440)

(40)

(10.0%)

(300)

(4,800)

(5,280)

(480)

(10.0%)

(3,600)

(14,335)

(10,350)

3,985

27.8%

(2,325)

(20,065)

(19,955)

110

0.5%

(13,615)

(7,385)

21,007

28,392

384.5%

1,282

36,968

55,045

18,077

48.9%

43,365

Purchases of property, plant & equipment

(15,000)

(5,000)

10,000

66.7%

(15,000)

Purchases of intangible assets

Purchases of investments

(2,000)

2,000

100.0%

(8,000)

(200)

108

308

154.0%

900

1,122

222

24.7%

500

1,000

500

100.0%

500

(200)

108

308

154.0%

(15,600)

(2,878)

12,722

81.6%

(22,500)

- Increase / (Decrease) in payables

Income tax paid

Net cash from operating activities
Cash flows from investing activities

Interest received
Dividends received
Net cash used in investing activities
Cash flows from financing activities
Proceeds from issuance of shares

(620)

(620)

(820)

3,180

(6,820)

(10,000)

(314.5%)

600

Repayment of finance leases

Dividends paid

(5,000)

(5,000)

(620)

(620)

(820)

(1,820)

(11,820)

(10,000)

(549.5%)

600

(8,205)

20,495

28,700

349.8%

462

19,548

40,347

20,799

106.4%

21,465

Proceeds / (Repayment) of loans

Net cash used in financing activities

Net increase / (decrease) in cash

Page 24 of 29

ABC Trading (Pty) Limited

Statement of cash flows for the period ended 29 February 2016
www.excel-skills.com

Current Month
Forecast

Actual

Diff

Year-to-Date
Diff%

Prior Year

Forecast

Actual

Diff

Diff%

Prior Year

Cash equivalents at beginning of period

58,790

50,889

(7,901)

(13.4%)

30,575

31,037

31,037

9,571

Cash equivalents at end of period

50,585

71,384

20,799

41.1%

31,037

50,585

71,384

20,799

41.1%

31,037

Page 25 of 29

ABC Trading (Pty) Limited

Balance sheet at 29 February 2016
www.excel-skills.com

Current Month
Forecast

Actual

Diff

Year-to-Date
Diff%

Prior Year

Forecast

Actual

Diff

Diff%

Prior Year

ASSETS
Non-current assets
Property, plant & equipment
Intangible assets
Investments

18,700

8,700

(10,000)

(53.5%)

15,100

18,700

8,700

(10,000)

(53.5%)

10,000

8,000

(2,000)

(20.0%)

8,000

10,000

8,000

(2,000)

(20.0%)

8,000

16,700

(12,000)

On this sheet:
(41.8%)
23,100

28,700

16,700

(12,000)

(41.8%)

23,100

28,700
Current assets
Inventories
Trade and other receivables
Bank
Other cash accounts

25,310
116,000
49,585
1,000
191,895

Total assets

220,595

On this sheet:

The
The balance
balance sheet
sheet on
on this
this sheet
sheet is
is automatically
automatically calculated
calculated based
based on
on the
the
reporting
reporting periods
periods that
that are
are specified
specified on
on the
the Set-up
Set-up sheet
sheet and
and the
the account
account
25,300 on
(10)
(0.0%)
22,310 No
25,310
25,300
balances
balance
forecast
is
on
balances
on the
the trial
trial
balance and
and
forecast sheets.
sheets.
No user
user input
input
is required
required
on
this
sheet
can
the
that
on
this87,515
sheet but
but you
you
can customise
customise
the line
line items
items
that are
are included
included
on the
the balance
balance
(28,485)
(24.6%)
81,540
116,000
87,515
sheet
by
the
reporting
sheet,
sheet
by creating
creating20,799
the appropriate
appropriate
reporting classes
classes on
on the
the Key
Key
sheet, entering
entering
70,384
41.9%
30,137
49,585
70,384
the
the appropriate
appropriate classes
classes in
in column
column A
Aand
and copying
copying the
the formulas
formulas from
from one
one of
of the
the
1,000
900
1,000
1,000
existing
existing rows.
rows.

15,100

(10)

(0.0%)

22,310

(28,485)

(24.6%)

81,540

20,799

41.9%

30,137

900

184,199

(7,696)

(4.0%)

134,887

191,895

184,199

(7,696)

(4.0%)

134,887

200,899

(19,696)

(8.9%)

157,987

220,595

200,899

(19,696)

(8.9%)

157,987

EQUITY & LIABILITIES

Equity
Share capital
Retained earnings

100

100

100

100

100

100

65,335

65,225

(110)

(0.2%)

28,600

65,335

65,225

(110)

(0.2%)

28,600

65,435

65,325

(110)

(0.2%)

28,700

65,435

65,325

(110)

(0.2%)

28,700

Non-current liabilities
Long term loans

52,560

42,560

(10,000)

(19.0%)

49,380

52,560

42,560

(10,000)

(19.0%)

49,380

Finance leases

Deferred tax liability

52,560

42,560

(10,000)

(19.0%)

49,380

52,560

42,560

(10,000)

(19.0%)

49,380

Current liabilities
Bank overdraft
Trade payables
Provisions
Interest payable
Dividends payable
Current tax liability

Total liabilities

88,000

78,014

(9,986)

(11.3%)

70,707

88,000

78,014

(9,986)

(11.3%)

70,707

4,600

5,000

400

8.7%

4,200

4,600

5,000

400

8.7%

4,200

10,000

10,000

5,000

10,000

10,000

5,000

102,600

93,014

(9,586)

(9.3%)

79,907

102,600

93,014

(9,586)

(9.3%)

79,907

155,160

135,574

(19,586)

(12.6%)

129,287

155,160

135,574

(19,586)

(12.6%)

129,287

Page 26 of 29

ABC Trading (Pty) Limited

Balance sheet at 29 February 2016
www.excel-skills.com

Current Month
Forecast

Total equity & liabilities

220,595

Actual
200,899

Diff
(19,696)

Year-to-Date
Diff%

Prior Year

(8.9%)

Page 27 of 29

157,987

Forecast
220,595

Actual
200,899

Diff
(19,696)

Diff%
(8.9%)

Prior Year
157,987

ABC Trading (Pty) Limited

Monthly Income Statement
www.excel-skills.com
Mar-2015

Apr-2015

May-2015

Jun-2015

Jul-2015

Aug-2015

Sep-2015

Oct-2015

Nov-2015

Dec-2015

Jan-2016

Feb-2016

Total

Revenue

47,544

48,649

55,439

64,211

55,447

55,658

86,167

80,000

64,240

62,193

64,737

89,048

773,332

Cost of sales

20,743

20,653

28,405

26,146

30,709

34,986

48,084

46,055

31,975

40,432

33,422

47,942

409,553

Gross profit

26,801

27,996

27,033

38,064

24,738

20,672

38,082

33,945

32,265

21,761

31,315

41,107

363,779

Gross profit %

56.4%

57.5%

48.8%

59.3%

44.6%

37.1%

44.2%

42.4%

50.2%

35.0%

48.4%

46.2%

47.0%

Other Income

89

88

81

80

90

11,095

101

86

10,974

102

105

108

22,999

21,207

21,218

23,056

21,425

26,467

28,753

26,278

29,910

26,511

26,773

31,721

31,599

314,918

702

702

702

702 On
702
On this
this sheet:
sheet:

702

702

702

702

702

702

702

8,421

1,316

1,316

1,316

1,316

13,684

44

44

44

44

526

###

###

###

###

1,245

Expenses
Accounting Fees
Advertising & Marketing
Bank Charges
Commission
Computer Expenses
Consumables & Cleaning
Depreciation
Entertainment
Insurance
Office Expenses

1,053
44
351
700

The
income
statement
this
automatically
calculated
The monthly
monthly
income
statement on
on
this sheet
sheet is
is
automatically
calculated based
based on
on the
the 1,053
1,053
1,053
1,053
1,053
1,053
1,053
reporting
periods
that
are
specified
on
the
Set-up
sheet
and
the
account
balances
on
reporting
periods
that
are
specified
on
the
Set-up
sheet
and
the
account
balances
on
44
44
44
44
44
44
44
the
the current
current year
year trial
trial balance
balance sheet.
sheet. No
No user
user input
input is
is required
required on
on this
this sheet
sheet but
but you
you
###
###
###
###
can
can customise
customise the
the line
line items
items that
that are
are included
included on
on the
the monthly
monthly income
income statement
statement by
by
351the
351 reporting
###
###
creating
classes
creating
the appropriate
appropriate
reporting193
classes on
on the
the -Key
Key sheet,
sheet, entering
entering the
the appropriate
appropriate
classes
the
rows.
### in
86 the
- from
###
classes
in column
column-A
A and
and copying
copying
the formulas
formulas
from one
one of
of
the existing
existing46
rows.
700

1,000

1,000

1,000

1,000

1,000

1,000

46

86

74

337

1,000

1,000

1,000

1,000

11,400

###

###

###

###

###

###

281

281

281

281

281

281

281

281

281

281

298

298

3,404

###

###

1,402

###

###

###

1,402

7,018

7,018

7,018

7,018

7,018

7,018

7,018

7,018

7,018

7,018

7,018

7,018

84,210

Postage

###

###

###

###

###

###

Professional & Legal Fees

###

###

###

###

###

###

10,000

10,000

10,000

10,000

15,000

15,000

15,000

15,000

15,000

15,000

20,000

20,000

170,000
1,292

Office Rent

Salaries & Wages

Stationery

90

84

84

89

172

77

128

167

63

160

81

96

###

1,053

###

###

398

###

###

1,450

969

987

978

961

1,022

1,021

1,008

1,025

1,043

1,078

1,061

1,052

12,204

Training

###

###

###

592

###

###

592

Travelling & Accommodation

###

494

176

156

2,632

175

117

3,750

Utilities

###

###

###

###

###

###

Other Expenses

###

###

1,000

###

###

###

1,000

5,683

6,866

4,058

16,719

(1,638)

3,014

11,905

4,121

16,728

(4,909)

(301)

9,615

71,860

440

440

440

440

440

440

440

440

440

440

440

440

5,280

Profit / (Loss) before tax

5,243

6,426

3,618

16,279

(2,078)

2,574

11,465

3,681

16,288

(5,349)

(741)

9,175

66,580

Income tax expense

1,570

1,920

1,085

4,880

(620)

770

3,440

1,100

4,880

(1,600)

(220)

2,750

19,955

Profit / (Loss) for the period

3,673

4,506

2,533

11,399

(1,458)

1,804

8,025

2,581

11,408

(3,749)

(521)

6,425

46,625

28,600

32,273

36,779

39,312

50,711

49,252

51,057

59,082

61,663

73,071

69,321

68,800

28,600

###

###

###

###

###

10,000

10,000

32,273

36,779

39,312

50,711

49,252

51,057

59,082

61,663

73,071

69,321

68,800

65,225

65,225

Subscriptions & Memberships

Telephone & Internet

Profit / (Loss) before finance costs

Finance costs

Retained earnings at start of period

Dividends
Retained earnings at end of period

Page 28 of 29

Management Accounts Template

Trial Balance Sequence Check

Select TB:

www.excel-skills.com

Current
-

Acc No

Amount

ok
Check

BS-100

50,100.00

ok

BS-150

-41,400.00

ok

BS-200

8,000.00

ok

BS-300

ok

BS-399

ok

ok

BS-400
BS-500
BS-510
BS-520
BS-530

-3,900.00
-10,000.00
-

ok
ok
ok
ok

BS-550

-5,000.00

ok

BS-600

-5,165.43

ok

BS-700

-42,560.00

ok

BS-800

-100.00

ok

BS-900

ok

IS-100

-773,332.09

ok

IS-200

412,542.63

ok

IS-305

8,421.00

ok

IS-310

13,684.20

ok

IS-315

526.32

ok

IS-320

ok

IS-325

1,245.14

ok

IS-330

336.82

ok

IS-335

ok

IS-340

3,403.50

ok

IS-345

1,401.75

ok

IS-350

84,210.48

ok

IS-355

ok

IS-360

ok

IS-365

170,000.00

ok

IS-370

1,292.10

ok

IS-375

1,450.22

ok

IS-380

12,204.40

ok

IS-385

592.11

ok

IS-390

3,750.34

ok

IS-395

ok

IS-400

11,400.00

ok

IS-500

5,280.00

ok

IS-600

19,955.00

ok

IS-650

10,000.00

ok

IS-700

1,000.00

ok

IS-800

-20,877.19

ok

IS-810

-1,000.00

ok

IS-820

-1,122.00

ok

IS-830

ok

BS-BANKB1

55,584.16

ok

BS-BANKB2

1,800.00

ok

BS-BANKB3

13,000.00

ok

BS-PCASHPC

1,000.00

ok

BS-DEBTORS

87,515.00

ok

BS-CREDITORS

-68,948.46

ok

BS-INVENTORY

25,300.00

ok

IS-INV MOVEMENT

-2,990.00

ok

-28,600.00

ok

BS-RETAINED

On
On this
this sheet:
sheet:

The
The formulas
formulas in
in column
column C
C on
on this
this worksheet
worksheet enable
enable users
users to
to check
check whether
whether the
the account
account numbers
numbers
that
that have
have been
been pasted
pasted into
into column
column A
A (from
(from another
another Excel
Excel workbook
workbook or
or export
export file)
file) are
are consistent
consistent
with
the
account
numbers
in
the
appropriate
trial
balance
in
this
template.
The
appropriate
with the account numbers in the appropriate trial balance in this template. The appropriate trial
trial
balance
balance that
that you
you want
want to
to check
check (Current,
(Current, Prior
Prior or
or Forecast)
Forecast) can
can be
be selected
selected from
from the
the list
list box
box in
in
cell
E3
and
an
error
message
will
be
displayed
in
column
C
if
the
account
number
sequence
cell E3 and an error message will be displayed in column C if the account number sequence
which
which has
has been
been pasted
pasted into
into column
column A
Ais
is not
not consistent
consistent with
with the
the selected
selected trial
trial balance.
balance. You
You can
can
then
then amend
amend the
the account
account numbers
numbers until
until no
no error
error messages
messages are
are reflected
reflected before
before pasting
pasting the
the trial
trial
balance
balance amounts
amounts into
into the
the appropriate
appropriate monthly
monthly period
period of
of the
the selected
selected trial
trial balance.
balance.

Page 29 of 29