Carry forward balance of account

[Glynn]

In my Accounting application I need to carry forward the balances of account,
at year-end close, to the new year, as Opening Balances in each ledger
account.
At present I achieve this by:
1) Copying the old batabase File to a back-up file - so as to
preserve to old
year in its entirety - all its transactions and reporting.
2) Deleting all the transactions in the current DB file - so as
to have a clean
start for the new year.
3) Manually entering Debit or credit entries to each Ledger
account, as
Opening balances for the new year.

This is not very smart - time consuming, and prone to unacceptable error.

There must be a way to programmatically 'carry forward' the balances of each
Ledger account from the old year to the new year. The 'balances' are
calculated amounts, being [Total Credits minus Total Debits] for each Ledger.

I would appreciate code, or being pointed in the right direction.

 

[pietlinden]

In my Accounting application I need to carry forward the balances of account,
at year-end close, to the new year, as Opening Balances in each ledger
account.
At present I achieve this by:
          1)  Copying the old batabase File to a back-up file- so as to
preserve to old
               year in its entirety - all its transactions and reporting.
          2)  Deleting all the transactions in the current DBfile - so as
to have a clean
               start for the new year.
          3)  Manually entering Debit or credit entries to each Ledger
account, as
               Opening balances for the new year.

This is not very smart - time consuming, and prone to unacceptable error.

There must be a way to programmatically 'carry forward' the balances of each
Ledger account from the old year to the new year.  The 'balances' are
calculated amounts, being [Total Credits minus Total Debits] for each Ledger.

I would appreciate code, or being pointed in the right direction.

How do you do this when you do it manually? Is it just your ending
balance from the previous year? If so, then you can create a query to
calculate the ending balance from the previous year and then appending
that record/those records (in the case of multiple accounts) to a
table in another database. One way to do it would be to use an append
query (turn your summary query that you use to get the ending balance
into an append or make table query and specify the destination
database.).

You could either use a linked table (flexible, but would require
caution) or hard code the destination database in SELECT INTO...
statement.

 

[Allen Browne]

Hmm. This is not really a simple question, Glynn.

At the most basic level, a normalized database would not have different
files for different years. The transaction date column would allow you to
determine the appropriate year, and so the transactions for different years
would all be in the same file. You can then use something like a DSum()
expression to get the opening balance for any year.

This normalized approach does avoid the problems associated with manually
entering the starting balance for each period. But it is not the approach
expected for double-entry accounting. Standard accounting procedures do
involve closing a period (typically a month), and locking the transactions.
You cannot go back into a previous period and just edit the data there. If
you discover an error, you must leave the closed periods exactly as they
are, and enter a reversal entry in the current period to fix the mistake. In
this system, there is a closing balance for each period, which becomes the
opening balance for the new period.

What you are doing by creating a new database file (hence locking away old
transactions) and manually entering the opening balance is therefore part
way towards the accounting model rather than the basic normalized database.
How far down this road you want to go will depend on factors such as your
knowledge of accounting, your knowledge of databases and normalization, and
how important it is for this database to follow standard accounting
practices.

In most cases, creating a double-entry accounting system in Access is not
cost-efficient. You can buy existing software at less cost than it would
take to reinvent the wheel yourself. Existing software is safer (more
tested, less chance of you getting sued for errors), and easier to maintain
(i.e. you get updates, and don't have to modify it with each change in the
tax law.)

Hopefully there are some useful grains amongst that to help you pin down
exactly what you need to do and why.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In my Accounting application I need to carry forward the balances of
account,
at year-end close, to the new year, as Opening Balances in each ledger
account.
At present I achieve this by:
1) Copying the old batabase File to a back-up file - so as to
preserve to old
year in its entirety - all its transactions and reporting.
2) Deleting all the transactions in the current DB file - so as
to have a clean
start for the new year.
3) Manually entering Debit or credit entries to each Ledger
account, as
Opening balances for the new year.

This is not very smart - time consuming, and prone to unacceptable error.

There must be a way to programmatically 'carry forward' the balances of
each
Ledger account from the old year to the new year. The 'balances' are
calculated amounts, being [Total Credits minus Total Debits] for each
Ledger.

I would appreciate code, or being pointed in the right direction.

 

[Chris O''''Neill]

I know this thread is a bit old (I've been having internet connectivity
problems), but I wanted to add something that (might?) be helpful...

The problem with moving prior period entries to an "archive" database is
that the data won't be readily available if needed. For instance, it will be
much more difficult doing year-to-year financial comparisons (e.g. last year
v.s. this year) in your reports. According to Allen Browne himself, the only
time you should archive old data is if it will never be needed again, or if
your database contains hundreds of thousands of transactions. See here for
more info:

http://allenbrowne.com/ser-37.html

Maybe a better way to go about it would be to leave the data all in one
database but limit the end-user's ability to "view only" (or whatever you
want) for prior period data. The way I'm doing that is I have a
CurrentFiscalYear field in my Company table... that's the table that
describes my company (name, address, etc.) In the BeforeUpdate or OnExit
events for my date fields, I use If/Then/Else statements that test the
inputted date against the CurrentFiscalYear field using the DatePart() and
DLookup() functions:

IF DatePart("yyyy",Me.ExpenseDate) <>
DLookup("[CurrentFiscalYear]","tblCompany") THEN

' Put code here for prior period transactions

ELSE

' Put code here for current period transactions

END IF

This works well if the company runs their fiscal year on a calendar-year
basis (which is what I do). If not, you can create a FiscalPeriods table
with twelve fields (period 1, period 2, etc.) that shows the start and end
dates for the company's monthly fiscal periods. Then, instead of doing your
test using the CurrentFiscalYear field, you test to make sure the inputted
date is >= the start date of period 1 and <= the end date of period 12. If
not, then it's either a prior period or future period transaction.

Btw, if you want to disallow entries to current year months once a month-end
has been done (i.e. prior month transactions), you can add a Closed field to
the FiscalPeriod table. Make it a Yes/No field and then set it to Yes as
part of the month-end routines. Then, test against that as part of your date
validation routines.

As for dealing with account balances, what I've done is added a
CurrentBalance field to my Accounts table. All transactions are inputted
into subsidiary ledgers (A/P, A/R, Adjustments, etc.) and then posted to the
GeneralLedger table. (Note: The G/L. is view only... users cannot make
direct entries to it!) This allows end-users to "play" with transactions
before they are posted, thereby (hopefully) minimizing the amount of
adjustment transactions that are required. Anyway...

When the end-user does post a transaction to the G/L, part of the posting
routine updates the CurrentBalance field in the Accounts table. I can then
use the CurrentBalance field for reporting purposes (trial balance, balance
sheet, income and expense statement, etc.). At year end, I do the following
with the CurrentBalance fields:

1. Use the Income and Expense balances to calculate a net profit or loss
for the year.

2. Update the G/L AccumulatedProfitOrLoss account accordingly.

3. Set all Income and Expense balances to zero.

Note that there is no need to carry forward balances for the G/L perpetual
accounts (Assets, Liabilities and Equity). The only G/L perpetual account
that is touched during year-end processing is the AccumulatedProfitOrLoss
account.

I hope this all has been a bit helpful to you. As Alan said, building your
own accounting application may not be the most cost-effective and efficient
thing to do, given the availability of ready made applications, but if you're
going to do it I hope my above tips have given you some food for thought.

Regards, Chris

P.S. I'm a newbie Access programmer with an accounting background. Also, I
have created the basic structure for the above, but haven't yet completed all
of the coding. So, based on that, you should take my programming advice with
a HUGE grain of salt as, in the end, all of the above might not work!

In my Accounting application I need to carry forward the balances of account,
at year-end close, to the new year, as Opening Balances in each ledger
account.
At present I achieve this by:
1) Copying the old batabase File to a back-up file - so as to
preserve to old
year in its entirety - all its transactions and reporting.
2) Deleting all the transactions in the current DB file - so as
to have a clean
start for the new year.
3) Manually entering Debit or credit entries to each Ledger
account, as
Opening balances for the new year.

This is not very smart - time consuming, and prone to unacceptable error.

There must be a way to programmatically 'carry forward' the balances of each
Ledger account from the old year to the new year. The 'balances' are
calculated amounts, being [Total Credits minus Total Debits] for each Ledger.

I would appreciate code, or being pointed in the right direction.