FIFO Inventory

[orange]

Just noticed the post with FIFO in title. I haven't looked at this for 2 yrs. Suggest you work with the zip from post #14. You'll have to work out the logic for your weighted average. You could post a description of how you will calculate "weighted average" in plain English and perhaps others will offer advice.

Good luck. Glad you liked the sample.

[HAROON.mansha]

Hi all,
I have checked the file, infact the file is based on to take the fifo based available stock. I am trying to some different thing. I want to take report that sales were out of which purchase bill no.

Can anybody give me idea.

[PKNK]

Hi, I am new to this forum. Tried the FifOstock database. It works OK. However is it possible to get the Sale details in the FiFostock table? I need to know the exact sale invoice which caused the stock consumption? Many thanks in advance

[orange]

PKNK,

As you are aware, this thread is years old.
I do not work in stock control nor inventory management. I worked in database for years and respond to posts with a view to helping posters. I saw a few posts on FIFO but could find little to no samples (especially in Access). Using material found I tried provided some material based on varied requests.

Since this is your first post, I recommend that you tell us something about yourself. Your background, experience, database familiarity etc. And also that you detail your requirements based on your current situation. Give us some examples to help clarify things.

[PKNK]

Many thanks for your prompt response. I am an IT Professional with a lot of years of experience in development. Used MS Access recently for a project. I have a requirement to calculate the profit / loss by sale and the products are accounted in FIFO method (first in first out). Your FIFIStock DB is quite a good starter. But it takes into account the total sale quantity rather than each individual sale. Is there anyway you can throw some light on how to go about to get sale-wise profit / loss?

[orange]

Thanks for the

Your FIFIStock DB is quite a good starter.

but it isn't mine as such. I found it, opened it, added some comments and add some additional records.
The original author/creator did the work --but didn't provide much in terms of design or operational documentation.

From my very limited understanding of the subject, the transactions are always in batches (large amount vs individual items).
Profit would appear to be the difference in the Sale Price of Goods sold and Cost of Goods Sold.
In the database the Purchase Invoice and Sales Invoices are identified.

For consideration with any Inventory(and here working with 1 Product, you have possibly a Starting inventory on Day 1 say 20 units of Product @ $3.00/ea (TotalCost $60.00).
You have individual transactions ---Purchases and Sales.
For each Purchase you have a dated Invoice and related details re: Quantity of Product Purchased and UnitCost. Lets' say you Purchase 15 units of Product @$3.27 (TotalCost $49.05) on Day 1 +5 days.
Your current inventory is now 20 units @3.00 and 15 units @$3.27. Current Inventory Value is $109.05; Quantity is 35 units.

Suppose you make a Sale of 26 units on Day1 + 7 (just to put transactions into some timeframe) and you sell the units for $5.49ea. Using FIFO, you want to sell the older (first in) units of Product. So using your 35 units available for Sale, you select the 20 units@$3.00 and then, 6 of the remaining 15 units , each of which cost $3.27. So the Cost of Goods Sold (26 units) is 20@$3.00 + 6@3.27 which is ($60.00 + $19.62) or $79.62. And your current inventory is now 35-(20+6) = 9 units and these have associated unitcost of $3.27.

At this point, the value of the current inventory is 9 units @ $3.27 or $29.43.
Also, your Ending Inventory = StartingInventory +Sum(Purchases)- Sum(CostofGoodsSold), and in this sample
= 20@$3.00 +(15 @ $3.27) - (20@$3.00 + 6@3.27)
= $60.00 + $49.05 - ($60.00 + $19.62)
= $29.43

The Profit = TotalSales of Goods Sold - TotalCost of Goods Sold
= Sum (Quantity of Goods Sold * Selling Price) - Sum (Quantity of Goods Sold * Cost Price)
= 26 * $5.49 - (20 @3.00 +6 @$3.27)
= $142.74 - ($60.00 + $19.62)
= $142.74 -($79.62)
= $63.12

The above example uses "perpetual FIFO" as I understand things --you always keep the UnitCost with the units Purchased. Also, in the example you work out Sales in terms of Inventory Cost and use the Cost of Goods Sold when valuing your Inventory. Selling Price per unit of Goods Sold is used in calculating Profit.

The weighted cost of inventory is determined by considering the current inventory at any time, and summing the total units*Cost and dividing by the number of units (Quantity) to get the "weighted cost", and using the weighted cost in calculations.
For example, consider an initial inventory of 20 units @$3.00. Then a Purchase of 15 units at $3.27.
Using weighted cost, total cost/ total units = (20 *3.00 + 15 *3.27)/35 = (60.00 +49.05)/35 = $3.12. (approx with rounding)
So every time there is a sale, use the weighted cost to calculate Cost of Goods sold. For very purchase, calculate the weighted unit cost from current inventory and new purchase cost and dividing by units in Inventory + QuantityPurchased to get the latest weighted unit cost.

I can take a look at things if you can review the database and provide your specific requirements. Recognize that I haven't looked at this since 2014, so the more detailed info you can provide --even take a sample or 2 from the database and maybe we can work through it.

Your experience and current requirement could help expand on the info on FIFO available in the forum.

[orange]

For anyone who lands on this post, there are some basic tutorials that may be of some value to you.

FIFO
FIFO
LIFO
WeightedAverage