Balance Carried Forward

[ddrew]

Hi,
ive created a balance shee for a football club as a continuous form with a balance being displayed in the footer along with total income and expenditure.

Because this is for a Football Club, I want to be able to do the balance sheets every season. To that end I have a included a date in the query.

Is there a way that I can carry a balance forward to the nesxt season, (next record). I want to display Last Seasons closing balance in the header of the new season and have the new balance include that?

 

[Rich]

Store the audited balance in a separate table and then either use a UnionQuery to include the latest balance or use DLookUp on a query that returns the last balance

 

[ScottGem]

Hi,
ive created a balance shee for a football club as a continuous form with a balance being displayed in the footer along with total income and expenditure.

Because this is for a Football Club, I want to be able to do the balance sheets every season. To that end I have a included a date in the query.

Is there a way that I can carry a balance forward to the nesxt season, (next record). I want to display Last Seasons closing balance in the header of the new season and have the new balance include that?

I agree with Pat, you should definitely not be storing this number. You can use queries or DSums to filter the transactions for a specific period Basically balances should be a running total. So if you want to get the balance fromt he last season, just set your date criteria to include all records prior to the the current season.

 

[Rich]

What's the difference between storing an audited balance and a stock take figure?

 

[andrew93]

It sounds like you have both the 'Profit and Loss' and 'Balance Sheet' reports on one page. At some point this is going to cause problems and you will probably need to separate the two reports. To do this you need to identify the 'Profit and Loss' accounts versus 'Balance Sheet' accounts. This can be done via a 'P&L' versus 'B/Sheet' indicator. As indicated in the other posts, the sum of transactions from Day 1 will give you the closing balance for the 'Balance Sheet' accounts, which is fine.

However, it is not common practice to sum 'Profit and Loss' accounts for periods exceeding 12 months (you may want to but it isn’t common practice). Typically you will be reporting on the earnings for this year versus last year (full year and year-to-date). The earnings for the current year will be the sum of the transactions in the 'Profit and Loss' accounts from the beginning of the current financial year up to (usually) the end of the last completed month. The comparative figures are typically phased for the corresponding period 12 months earlier (on both the starting and ending 'profit and loss' reporting dates). These can be done with queries.

However, IMO you will need to calculate and store one brought forward amount, and that is the retained earnings (or losses) account. I realise this breaches the normalisation rules, bf you don't then your 'Balance Sheet' will not balance. Retained earnings is a 'Balance Sheet' account that is the sum of all transactions in the non 'Balance Sheet' accounts from Day 1 up to the year end prior to the year you are currently reporting on. Yes I know this could be calculated, but IMO it is easier to store this one value once you have 'closed' a year (such that no further transactions can be entered into that financial year). Typically this is the procedure financial accounting systems go through when a system is 'rolled over' from one financial year to another (amonst other things). The amount 'stored' in this account each year, is the sum of the transactions in the 'Profit and Loss' accounts for the same period.

Just my thoughts
Andrew

 

[Rich]

The problem is not with storing an audited balance but with storing a running sum. Each individual transaction needs to stand alone.

I don't store the RunningSum, that's calculated in an unbound control on the form. The audited balance is derived from that control at specific points in time, it saves using a calculator, it does of course require manual interaction to store it.

 

[ddrew]

Flippin eck! I wish I'd never asked, thanks guys whilst I'm sure this is all really usful stuff, its all a bit over me ed. Any chance of someone posting a sample DB to see what I should be doing with this!

 

[FeridaOe]

Flippin eck! I wish I'd never asked, thanks guys whilst I'm sure this is all really usful stuff, its all a bit over me ed. Any chance of someone posting a sample DB to see what I should be doing with this!

LOL, I have same problem with you. I don't know the expression to carried the accumulated "balance" of the Balance Sheet every month so it is displayed in Account Ledger Report. At this stage I monthly make an append query to carry the balance forward. I wish there is a way not to repeat it every month.

 

[Simon_MT]

Technically, storing values is not correct as in accounting terms each year is distinct. Apart from Audit purposes, what happened last year is now only used for historical comparison. What happens to the Profit and Loss Balance - it gets posted into Reserves within the Balance Sheet in some systems the Profit and Loss Account balances are set to Zero.

Take budgets for the Profit and Loss accounts, a one figure estimate or the Total Revenue and Expenditure per Account. Surely, it would easier to store this value in a field rather that posting transcations to create the budget values.

Within the the Profit and Loss Accounts each new Accounting Year or Period, the intial value is Zero whilst the active balance Sheet Accounts will have Opening Balances so storing values is not alien within an Accounting System. Nor does storing values affect the integrity of a system and it can be argued that some balances are sacrosant[FONT=&quot][/FONT]. Once a Year or Period is closed the balances rarely change, it is rare that get into a Tardis and go back in time and back-date transactions.

Admittedly this is a rather antiquated way of looking at the Balances. The other benefit is once the Opening Balances are set they never have to be re-calculated until Year End so the overhead is slightly reduced. This used to matter but with the processing power and memory at our disposal this is less of an issue.

Simon

 

[FeridaOe]

Simon, how do you do to display balance forward each month in Account Ledger? I need to print the Cash and Bank ledger every month and have to run monthly append query (with different transaction type for annual financial report purpose) to the source table in order to display it.

Technically, storing values is not correct as in accounting terms each year is distinct. Apart from Audit purposes, what happened last year is now only used for historical comparison. What happens to the Profit and Loss Balance - it gets posted into Reserves within the Balance Sheet in some systems the Profit and Loss Account balances are set to Zero.

Take budgets for the Profit and Loss accounts, a one figure estimate or the Total Revenue and Expenditure per Account. Surely, it would easier to store this value in a field rather that posting transcations to create the budget values.

Within the the Profit and Loss Accounts each new Accounting Year or Period, the intial value is Zero whilst the active balance Sheet Accounts will have Opening Balances so storing values is not alien within an Accounting System. Nor does storing values affect the integrity of a system and it can be argued that some balances are sacrosant[FONT=&quot][/FONT]. Once a Year or Period is closed the balances rarely change, it is rare that get into a Tardis and go back in time and back-date transactions.

Admittedly this is a rather antiquated way of looking at the Balances. The other benefit is once the Opening Balances are set they never have to be re-calculated until Year End so the overhead is slightly reduced. This used to matter but with the processing power and memory at our disposal this is less of an issue.

Simon

 

[Simon_MT]

Sorry for the delay in replying but whilst I could calculate it I think it I had to do this in earnest I would store the value. This might be considered BAD!

To calculate which I haven't had to do recently, as I have gone away from implementing Accounting solutions but I would tackle the problem with creating a Period and Year for each transaction. I would create an Opening Balance and if I didn't hold a Period Balance to get the Period Opening Balance but using the Year Opening Balance plus any transaction value for any period excluding the current Period. Obviously ignoring any future transaction beyond the Current Period.

Simon