Stock Usage And Sales with Price Changes

[Meinthecorner]

I have never done a stock \ sales database before, normaly just Customer Service or Booking ones.

I'm still in the process of designing this new request and was wondering how you tackle historic data for sales items?

At the moment I have a [parts] table that holds the PartID, decription and current £Cost. The VAT etc is simply calculated on the fly and shown as a calculation on the Form and Report.

The problem that I have is that Part Prices fluctuate daily, so what was sold yesterday may be at a different price today.

How would I keep a record of the Price the part was sold for, for historic reporting?

My First though was to run an Append Query and log the Sales Date, CustomerID and PartID and Value to a separate table?

Better suggestions are very welcome or if there is a standard design rule example that would be appreciated!

 

[pbaldy]

Presumably you would have a transactions table, and you would store the price for that transaction with each. Normally I would expect 2 tables, one for the main sale data like customer, date, etc, and a second with the items sold with fields for product, quantity, price etc. These would have a one to many relationship based on invoice number.

 

[Meinthecorner]

Thanks for the reply

Yes I have a Transactions Table, at the moment this is storing the SalesRef, PartID, Quantity Sold, Date

I was using the Transactions table to also Hold information of Inbound Stock to calculate the Stock on hand via a seperate query.

There is a separate Parts table that holds the current price of the part, part number and Descriptions.

We don't need customer details, just a reference from an Extranet System that hold customer Details.

So at the moment I have One To many relationship to the Transaction Table as a subform from the Main Booking screen, which simply contains the Extranet Ref, The Sales Person, Date and time.

 

[The_Doc_Man]

Search this forum for "Inventory" and narrow down the search for "Price Changes" - this topic has appeared on the forum before. You might have to remove the time-limit for search hits. I think by default they are set for 1 year.

 

[neileg]

The recent change in VAT rates reminds us that hard coding the rate is not an option. Store the VAT rate, too, on the transaction.

 

[gemma-the-husky]

in fact is the business vat registered?

in VAT Registered businesses, its very unusual to store VAT amounts in ledger transaction records, as the VAT does not form part of the cost

in unregistered businesses, its also unusual, as the cost would normally be grossed up to include VAT, and VAT not shown separately

there is a place for VAT, but its not in costing records

--------------
thinking about it further, there does appear to be some confusion - you may need to be aware of sales prices, and may need to monitor changing purchasing prices, but they also are not really anything to do with the cost of your stock as such.

 

[neileg]

Hi gemma. I was thinking about sales records, not inventory. I should have been more explicit.

 

[gemma-the-husky]

probably my bad, neil

I was thinking the original post was mainly about an inventory management system - so i thought the issues of VAT, and selling prices were somewhat of red herrings

however as far as this goes, these two areas are distinct. Stock/Inventory is not the same as sales. Stock is held at a cost value - Sales are made at selling price. Profit is the difference between sales and cost. Now the issue comes in terms of deciding what the cost (of a given sale) actually IS, when costs fluctuate over time, and there are numerous costing methods to deal with this - FIFO, LIFO, AVCO, Standard costing being a few.

Unless you have something like a car dealership, where every item is distinct and unique, you have to fnid a way to produce a cost calculation to strike a profit.

 

[neileg]

Steady on, girl. If you're not careful, you're going to start on about fungible assets!