Calculating stock balance

[Demerit]

I wish to write a query that gives me the stock balance.
In my DB, I have a table Named "TblStock" and "TblStock2"
In these tables, I store data concerning products including; Purchases, Sales, Transfer inward, Transfer outward. Each of these elements is differentiated in the table TblStock under the Field "Nature" by

SA = Sales
PH = Purchases


TI = Transfer Inward
TW = Transfer Outward

Now I wish to know the balance of each stock item using the formula:
Available Stock = Purchases - Sales +Transfer Inward - Transfer Outward

To this effect I have created queries to sum up each item and finally, I have created a query named: " Available Stock"

This query uses the other queries i created to calculate the available stock.

The problem i have here is that available stock only appears for a stock item that is available in Purchases, sales, Transfer inward and Transfer outward.

Now what i want is that when a stock item does not exist in either Sales or Transfer inward for example, it should return a zero (0) so that available stock should be calculated using the zero (0).


Attached is the part of my DB

AvailableStock.zip

[orange]

I don't understand your data model.
I recommend you do an advanced search for inventory onhand or stock onhand since this topic comes up regularly.
You can also look at this link for ideas.

Good luck with your project.

[rpeare]

Demerit you still have a fundamental problem with this database.

You have not defined how many items are in a carton (that I can see) so you will NEVER be able to arrive at an accurate 'on hand' number of units. All you will ever be able to do with this database, as it stands right now, is to count items in the same way you receive them.

For instance:
Let's say you receive 3 cases of iphones plus 5 individual iphones.

In your database there is nothing indicating how many iphones form a case so all you will be able to know is that you have 3 cases of iphones and 5 individual iphones. If you break one of the cases into individual units you would then be breaking any sort of rule about how you could count items in your system.

Each item in your database needs to have a defined multiplier or you will never be able to arrive at an accurate stock count.

[Demerit]

I have redefined my Pricing tables to include Quantities in each unit. Attached are the new tables.
How can I make use of these quantities on TblPrice2?

Stock Balance.zip

[rpeare]

You're closer, but still not there

On the item with a priceID of 2 you have 3 items in tblPrice2 but your UNIT PRICE (I'm assuming that's what UP stands for) is not right

For instance you have a single item with a unit cost of 20
Then you have a unit of 500 with a unit cost of 2500, if you multiplied the unit cost times the number of units you'd get 1.25 million dollars of inventory
similarly if you multiplied the 2500 units by the unit cost of 12500 you'd end up with a 31.25 million dollar inventory

the UP (unit price) has to be a reflection of how much it costs you/you sell it for in each of the units you buy and sell it in (individual items/cases/pallets)

[Demerit]

Does this means that for the item with PriceID 2, The unit Price for all the units should be 20?

[rpeare]

it depends on what your actual unit cost for purchase/sale of that item is. I'm sure it's not only cheaper for you to buy items by the case, but to sell them to a customer as well so you have both an ingoing and outgoing cost (if you're interested in the overall financials. Setting a unit cost for both incoming and outgoing would give you a baseline that you could then modify on any purchases/sales of that item.

If your unit cost for a case of items is 15 (for example, your UP for the case should be 15)
if your unit cost for the sale of the same item is 23 you would want to record that as well

you need to identify your purchase and sale cost per item BASED ON THE PURCHASE/SALE QUANTITY and record those in your tblPrice2 table.

[Demerit]

Actually, TblPrice and TblPrice2 are made to record only the selling prices. The cost price of an item bought is entered in the TblStock2

The reasoning here is that, the unit selling price will take long to be changed but the unit purchase cost may change in different batch of purchases.

[Demerit]

I see with you, inorder to value the stock at the purchase cost, i was thinking of using the Waighted Average Cost in doing that but i never knew how that could be done.

[orange]

Did you look at the link I recommended earlier. Part of your issue, in my view, is for you to get a clear understanding of exactly WHAT you are trying to solve.
The initial business problem in the tutorial is the level of detail required of your "Business issue" in order for you to identify tables and relationships.

Your post #9 tells me you have really defined your "business".

Good luck with your project.

[Demerit]

Thanks Orange;

I have looked at the link. There are two items in my DB that are not in the Sample Function which as of now i cannot yet add those in that function to suit my need. The issues are these:

1. My DB has Units for products which is not there (e.g. Cartons, Packs) though in my DB(TblPrice2), I have included the quantities in each unit.

2. My DB has a treatment of transfers. That is, stock can be transfered from a branch to another branch and this should not be treated as a purchase and vice versa. This aspect I don't know how I can incorporate that in the Sample Function.

If there is a possibility of incorporating these items in the my DB then I can also go by that. And looking at the Sample Function, I notice that the function returns the Quantity in hand for each product. (Great!) So that made me think of the following:

1. Create a

[Demerit]

1. I create a table say "TblAverageCost". This table records the Weighted average cost(WAC) of products. The WAC is calculated at any time a new purchase takes place by taking:

• Stock in hand as at that date in value
• Add purchase in value
• Divide by the sum of their quantities

2. Since the Sample function returns the Stock in Hand in QUANTITY, To get the Stock in hand in Value, We just multiply this quantity by its WAC.


What do you think about this?