Database design for Stock with cost different from time to time

Hi All,

I designed a database which initially has tables of TblProduct & TblSales. It works with user to input details of products( such as product name, cost and price)  so it can be used in daily sales recorded in TblSales.

User is now requesting to have information of quantity of each product so it can be replenished before out of stocks and cost may be different from time to time depends on order user made to their suppliers.

for user to control the quantity I plan to design with real time quantity  = initial quantity(to be input by user into TblProducts) + order quantity (from TblOrder) - sales quantity(From tblSales)

Questions:

User is using first in first our method in selling the product so is there anyway to design to have the database recognizes and calculates same product with different unit cost like situation as below:

Order Date Product name Cost Order quantity
1/1/2016 Product A 1000 10
2/3/2016 Product A 1500 8
Sales No. Sales date Product name Unit Price Sales quantity Unit Cost Unit Profit
1 2-Jan-16 Product A 2000 7 1000 1000
2 4-Jan-16 Product A 2000 3 1000 1000
2 4-Jan-16 Product A 2000 2 1500 500
3 5-Jan-16 Product A 2500 2 1500 1000

How to design the database with situation above?

Thanks in advance.

 

Question Info


Last updated May 24, 2018 Views 387 Applies to:

It's a matter of functional dependency.  As you want to compute the difference between cost and sale price at order level, the cost is an attribute of an OrderDetails table or similar, functionally dependant on the key, in the same way that the sale unit price is in a conventional ordering/invoicing database.  So, as well as assigning the current sale unit price to a column in OrderDetails the current unit cost would also be assigned when a row is inserted into the table.

 

The sample Northwind database is an example where the unit price of a product is assigned to each OrderDetails row, but the code is rather long-winded.  You'll find another example in Inventory.zip in my public databases folder at:

 

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

 

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. 

 

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

 

This little demo file illustrates basic inventory management, and includes functionality for sales orders.  In the AfterUpdate event procedure of the product combo box in the order details subform the current price of the selected product is assigned to a UnitPrice column in OrderDetails with the following code:

 

    On Error GoTo Err_Handler:

   

    Dim ctrl As Control

   

    Set ctrl = Me.ActiveControl

   

    ' assign current unit price to UnitPrice control

    Me.UnitPrice = ctrl.Column(1)

   

Exit_Here:

    Exit Sub

   

Err_Handler:

    MsgBox Err.Description, vbExclamation, "Error"

    Resume Exit_Here

 

By inserting the current cost unit price of the product into another column in the table in the same way it then becomes a trivial task to compute the 'profit' at any level of aggregation, be it order, customer, day, month, year, whatever, merely by subtracting the sum of (cost unit price * quantity) from the sum of (sale unit price * quantity).

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi,

From my understanding from your inventory database, it shows the way on how to make the database when  user changes price of new orders, it will not affect the previous orders.

while it does not shows the way on how database calculate automatically for the same product with different unit cost.

example:

1. user A as purchaser, order 2 times for product A : first time order with quantity of 2, unit cost 100 and second time order with quantity of 4, unit cost 150

2. User B as Seller, input a sales for product A with quantity of 5 unit price 200

3. User C as Business owner want to see :

  • A report shows quantity balance of 1 for product A
  • A report shows profit from the sales (5 x 200 - (2 x 100 + 3 x 150)) = 350

What I understand from your explanation, it helps me solves 1 and 2 when user changing cost or price of product A, it do not affect previous record, while I still can not figure out how to make it for no. 3.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

My demo does not concern itself with profits, but the principle is the same.  In my case the sale unit price of a product is functionally determined not only by the key of products, but also by the key of OrderDetails.  Consequently there is a UnitPrice column in both tables, in one case the value being the current price, in the other the price at the time of the order.

 

To provide a basis for computing profits exactly the same applies, so it would be necessary to add CostUnitPrice columns to both Products and OrderDetails and to assign the value of the former to the latter in the AfterUpdate event procedure of the product combo box in the order details subform, in the same way that the (sale) unit price is currently assigned.

 

The profit on each order line item would then simply be a matter of using the following expression:

 

Quantity * (SaleOrderPrice - CostOrderPrice)

 

You can easily return the profit at any level of aggregation in a grouped query with:

 

SUM(Quantity * (SaleOrderPrice - CostOrderPrice))

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi, sorry to take such a long time to ask about this,

in your inventory file - FrmStockMovements, there are 3 kind of movements of stock : 

  • Acquisition - is it to fill in all the refill of the stock? is it need to be filled each time the disposal & the stock take filled?
  • Disposal - is it need to be filled as well each time the Aquisition and the stock take filled?
  • Stock take - is it to fill with all the stock reduction as well for non disposal purpose? and is it need to be filled each time the Aquisition and disposal filled?

I am also confused about how the available stock calculate to get the number of the stock available.

Can you explain to me?

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I should make it clear that my referring you to my Inventory demo really had nothing to do with inventory management per se.  I only used my Inventory demo as an example because it includes a simple ordering component, in which the functional dependency of the UnitPrice of a product is a dual one, being determined by the key of products in the case of the current price, and by the key of OrderDetails in the case of the price charged per order.  By extending this so that both the purchase and sale price per product are independently functionally determined in the same way it becomes easy to compute the 'profit' per item at any level of aggregation as I explained in the final paragraph of my earlier response:

'By inserting the current cost unit price of the product into another column in the table in the same way it then becomes a trivial task to compute the 'profit' at any level of aggregation, be it order, customer, day, month, year, whatever, merely by subtracting the sum of (cost unit price * quantity) from the sum of (sale unit price * quantity).'

As you have now raised issues which are concerned with inventory management per se, I will briefly address these:

In essence, basic inventory management takes account of three things:

1.  The receipt (acquisition) of quantities of items into stock.  This would normally be by means of purchase orders, but for simplicity in my demo I have modelled this by a single table, StockAcqisitions, in which each row represents the receipt of a quantity of an item into stock by any means.

2.  The disposal of quantities of items from stock is normally by means of sales orders, modelled in my demo by the following tables:

Orders----<OrderDetails>----Products

Items might be disposed from stock by other means however, e.g. by writing off out of date or damaged items.  In my demo this is modelled by a single table StockDisposals, in which each row represents the disposal of a quantity of an item from stock by means other than a sales order.

3.  Positive or negative adjustments to the quantity of each item held in stock resulting from errors in the data for stock acquisitions or disposals, or from unrecorded disposals, e.g. as a result of theft by employees or others.  Current physical stock levels are recorded by periodic stock taking procedures, and these are modelled in my demo by the StockTakes table.

Data entry for sales orders is via the orders form and its order details subform.  Data entry for other disposals, all acquisitions and stock takes is  via the three sub forms in the stock movements form to which you refer.  The insertion of data into the three subforms is entirely independent of each other.  It is not akin to double entry accounting where a credit entry in one ledger requires a debit entry in another.

Computing current inventory is essentially a matter of subtracting the sum of all disposals, via sales orders or other means, from the sum of all acquisitions.  However, this would result in a discrepancy between the computed quantities in stock and the physical quantities held if no account is taken of errors in data entry or unrecorded disposals as a result of theft etc.  Consequently each computation of current inventory takes as its starting point the most recent stock take per item, and ignores any acquisitions or disposals prior to that.  This reduces any possible discrepancies between the computed quantities and physical quantities to those resulting from any errors or unrecorded disposals since the latest stock take.

I must emphasise that my demo illustrates only the basic methodology of very simple inventory management.  In all but the smallest enterprises inventory management is far more complex, particularly in a manufacturing environment, which is where my experience in Purchase and Supply was gained.  Here, it is not simply a case of acquisitions and disposals of atomic items.  In the company by which I was employed it was not just the purchase of the raw materials from which our products were made which needed to be recorded, but also the transformation of those materials into the finished products, which in turn became items of inventory.  We also needed to allow for returned items which were then reprocessed.  As our products were high turnover consumables we did not need to cater for back orders, but in other contexts that would also be an issue.  In addition to these product-related matters the purchase and use of ancillary items needed to be managed, such as packaging materials, engineering components for the manufacturing process, stationary etc.  Consequently our inventory system was very complex.
_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Understood, from your sample database of inventory I do settle the matter of price and cost changing from time to time.

However, the profit calculating problem still there. oldest inventory sold first and user want to know profit of every item sold based on actual cost of that particular item.

Example: Product A with unit cost of 100 has qty of 10 and Product A with unit cost of 150 has qty of 5, assume the selling price of product A is 200 and on day 1 customer A buy Product A with qty of 3 and day 2 Customer A buy Product A with qty of 10.

Inserting current cost in another column of in the table meaning the product has only 1 unit cost and it will effect the report of previous date and require the user to change it whenever the new order cost change,

Is there anyway to let let access know when to calculate with new cost of that particular product without effecting previous date report and user only need to input one time of the order cost, access will automatically update same product has another higher or lower cost?


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

It's possible, but not trivial, and I doubt its usefulness.  It would require you to compute the balances of each item at each purchase price at the time of each sale.  The costs per item sold would be computed by assigning the quantity of items from the earliest purchases.  If the current balance from that purchase is less than the quantity sold, then the costs at the time of the next subsequent purchase would be assigned to the balance of items being sold.  This process would be repeated as necessary until a positive balance remains.  Given time I'm sure I could devise a routine for this, but I'm afraid this would be beyond the scope of the time I can make feely available to this forum.

I do think this is a rather naïve accounting method, however.  What is served by this rather than computing the balance of income over expenditure within an accounting period?  This is very easily done, and over the length of any reasonable accounting period gives a perfectly adequate picture of the state of the business. The expenditure/income can easily be categorized per product or class of product to enable an assessment of the performance of different product lines.

If a large quantity of stock is purchased in one accounting period, followed by no purchases over a series of subsequent accounting periods in which sales are made.  The balance of income over expenditure in the first period will be negative, followed by positive balances in the subsequent periods.  The value of sales in those subsequent periods might have been enhanced by increases in both the purchase and sale price of the product.  Against this is the loss of income from the earning power of the money spent on the purchase in the first period.  It can thus be assessed whether the initial purchase of a large quantity of the product produced a better or lesser return than would have been obtained by more frequent purchases of smaller quantities of the product, bearing in mind that the latter might have involved the loss of any bulk purchase discount  available at the time of the initial larger purchase.
_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.