Opening Stock (2003 All Updates)

Topic

New Reply

I use data from an accounting system that has an intriguing way of storing opening stock balances for stock items. Each item has an opening stock balance for each financial year that it existed. Thus during year ended 2007, item 123 which was first used in 2005 would have a 2005 opening balance of 100, 2006 opening balance of 10, and a 2007 opening balance of 45. I presume this speeds up any queries as not all transactions need to be analysed to get a current balance, just those in the year in question.

Once a financial year end is run (and just to make things nicely complicated, our financial year end is June), all items have an entry created in the table to show the opening balance for the new financial year. Thus in the example above, a new entry would be generated showing a 2008 opening balance of say 60 once the 2007 year end process has been run.(only the year is stored, not the month)

No problem, I can link these dates to the company data file which records what financial year we are in, and obviously changes to the current year once I run a year end.
Except:
Quite often (in fact most of the time), the year end is not run until many months after the effective date – to allow for late accounting adjustments etc. Thus the item will still show an opening balance as at last year.
No problem except if new stock is added after the recorded year end, it has an opening balance dated in the next financial year. In our example it would now have a 2008 opening balance entry but only for the new items, older items will have 2007 as the year end has not been run
.
I use date ranges to calculate balances at a particular point of time and need to have a way of determining which opening balance to use. Thus if my date range is from 1st Feb 07 to 3st of July 07, my opening balance would be the one dated 2007 to which I would add all transactions since that date. However if I wanted a date range of 1st of July 07 to 31st July 07 my opening balance would be the one dated 2007 if I had not run the year end or 2008 if the year end had been run or if it was a new item
In summary, I need a method of determining which year end number to use as my starting point depending on what date parameters I am using

Sorry for the length of the explanation but any help would be appreciated
Steve

Reply | Quote

Replies

In all this, you haven’t told us what the actual structure of the table is…

Reply | Quote

If the table structure is like the one in the attached database, you can use a set of two queries. I have created a form to demonstrate it: enter an item and a start date (the end date is not used), then click the Update button to see the result.

Reply | Quote

Thanks for the suggestions.
Sorry Hans, I thought I had explained it properly but I was probably overdoing the detail. I attach a spreadsheet showing some examples.

Basically I need to start with a valid opening Balance and I then add and subtract sales, purchases and adjustments or location movements for the set period to come up with a current closing balance for ALL items

In my example spreadsheet:
Item ID 520 has a quantity of 11 in year 2007 because I have not run the year end and there have been no transactions for this item in 2008
Item ID 652 has a quantity of 3 in year 2007 and a quantity of 2 in year 2008 because there have been transactions for this item in 2008.

I had sorted the year to use process you described but now need help on the following:

If I am doing the stocktake for a period starting in the 2007 year, everything is fine. All items have a 2007 table entry as I have run the 2006 year end or they have been added since then or had transactions since.

However if I am running a stocktake for a period in starting in 2008, I need to use the 2008 balance for item 652 but the 2007 balance for item 520 as there is no 2008 balance yet.

Steve
Hope this is clearer

Reply | Quote

As you see from my previous reply, this is a subject I don’t understand at all. I hope that John Hutchison’s reply is helpful.

Reply | Quote

Hans, Thank you for your help
John (who, by the way Hans, actually has a lot of experience with this particular accounting package) thank you for thinking more laterally than me. After a bit of pain, your suggestion worked like a charm.
Thanks again
Steve.

Reply | Quote

Sorry Steve

I could not get to look at this over the weekend.

It seems to me that what you are trying to do can be done from within MYOB itself, using one of the standard reports.

Reply | Quote

I am assuming you are talking about MYOB here.

The items table actually has a “QuantityonHand” field in it. The opening balance values are in the related table MYOB_ItemOpeningBalance.

It seems to me that if you want to calculate a quantity on hand at some point in time earlier than now you can either go back to the last Opening Balance, and work forward adding in transactions that occurred after the opening balance, or start at the present and work backwards removing transactions.

The second method may be easier as the starting point would be the same for all items, and the problem of determining which opening balance to use would not apply.

Reply | Quote

Sorry John – had replied to Hans before I saw your note.
Yes it is MYOB . The go backwards method may work – let me test it. Thanks
Steve

Reply | Quote