FIFO - Demo attached works to consume stock correctly, but how to calculate profit

[writeprivate]

Acknowledgments:


An old thread on

here

that OP posed the question on how to calculate inventory on a FIFO basis. The OP included a sample database written by a 3rd party (unknown) which is very good work
@Orange - you helped the OP and have worked on this file before in 2013. You updated the sample db with additions to the OP's code were very helpful to fix two bugs and properly comment on the code.


Request for help
I am looking to be able to calculate the profit on a sale of inventory spanning two or more purchase batches with different costs. I am writing a database to track personal money investments.
Can anyone on here help to update this database with this new functionality


Example scenario
Let's say I buy Microsoft shares as follows


PURCHASE ORDER

Invoice	Transaction Date	Quantity	Purchase Price
1	Jan 20	1000	$1
2	Feb 21	500	$2
3	Jun 22	500	$3

SALES ORDER

Invoice	Transaction Date	Quantity	Sale Price
100	Jan 23	500	$5
200	Feb 23	1000	$7

GOOD NEWS
The attached demo db works really well to calculate the consumption of inventory over batch quantities. It shows the amount of inventory left from each batch consuming the first purchased stock first. This is the hardest bit. Now I need to calculate the cost of the sold items.


BUT HOW CAN I CALCULATE THE COST ASSOCIATED WITH THE SALE

• Looking at Sales Invoice 200, comprising 1000 shares sold at $7.
• This transaction will be selling 500 shares with a cost of $1. And 500 shares with a cost of $2.
• So an average purchase cost for that transaction of $1.50 and therefore a total cost of $1500.
• This would mean the profit on the transaction can be calculated at $7,000 - $1,500
• I would also know that the remaining holding of 500 shares would have a cost value of $3.
  
  
  SCREEN SHOT OF THE ABOVE SCENARIO
  Ideally the Yellow highlight below will show the associated cost for that sale and the profit
  
  

I know this is a tough challenge so I am really grateful in advance for any help here.

[writeprivate]

ps. Ideally the updated db would show the "CostOfSale" and "ProfitOfSale" (new fields) on the SaleMain and/or SaleDetail records as extra colums populated by the function.

[orange]

Please note that the sample you are referencing is from the 2008 to ~2012 era. As mentioned in those related posts I am not expert in Inventory Management nor FIFO. I am familiar with Access and database and participate in various forums offering advice and guidance to posters with specific questions/problems. What I did know of FIFO and inventory was gleaned from a number of youtube videos and articles. I did add a few records and some comments to the code but that was about 10 years ago.

There are some sample calculations in this thread that may help with your calculations. If they don't help, then please post back with that you have and what you need. I'll look at it but it has been a long time since reviewing this sort of thing.

The original FIFO info was associated with this post.

[writeprivate]

Thanks for replying. Yeah I appreciate what you're saying. You're bekng modest, I compared the two versions and your input was valuable.

I am a novice at Access and frankly get lost once it gets beyond queries and into VBA. I was hoping that you may be able to recheck the code and see if the addition of cost would be a relatively simple thing to add. I'm trying to work it out myself but the VBA script is just too complex for me.

I understand you want a specific problem. Apologies for a rather nebulous request. Would it be possible to show me how to add cost where the yellow highlight is on my screenshot.

Thanks

[writeprivate]

For clarity, I've looked at all your links and read them before I posted. I understand what I want in plain English (ie. Your sample calculations) but don't know how to effect it in code on that sample dB. I've tried to provide a clear plain English request as you asked for on that sample calculation link.

Basically, I'd like to add to the functionality of the sample database by adding the 'sales cost' of each sale. The sample database already knows the purchase stock rows used to consume stock on the FIFO basis - and the purchase cost is on those rows. So hopefully it should be possible to output the 'sales cost'

Thanks for any help.

[orange]

Do you have a database that has those Purchase and Sale Dates shown in your graphic?
Or have you deleted FifoStock table and added your own data?

[writeprivate]

Do you have a database that has those Purchase and Sale Dates shown in your graphic?
Or have you deleted FifoStock table and added your own data?

Yeah - i just deleted all the data and entered in the 3x purchase transactions and 2x sales transactions

[orange]

Update: I just located another database that may be useful to you.
Fifo Batch Allocation JED_Backup_19Oct21.accdb looks like I converted some mdb databases to accdb.
There is a reference in this database to haroon.mansha

This has more comments and I must admit, I'm sure the data/coding is from 2014 era. If I recall correctly I changed from Access 2010 to O365 and went thru a process to convert all the mdb databases to accdb.

The reports may already show Cost and Profit? I have not investigated further.

I have attached the database and a png of the screens in the Fifoaccdb.zip file.

Update again: In the database see report Profit Report by Individual Transactions --I chose these dates because it dealt only with product "Apple". Here is a picture of the report.
Again, I want to emphasize, this is from a data base created by someone else, I adjusted some code and added comments and a comment table to help a poster.

[ArviLaanemets]

In Sales table, you have to remove Qty
Sales: SalesInvNo, Rate, ...;
You have to add a table e.g. SalesRows
SalesRows: SalesRowID, SalesInvNo, PurchaseInvNo, Qty

So you'll have:
Purchases
PurchaseInvNo =2, Qty = 500, Rate = 2, ...
PurchaseInvNo =3, Qty = 500, Rate = 3, ...

Sales
SaleInvNo = 101, Rate = 7, ...

SalesRows
SalesRowID = ?1, SalesInvNo = 101, PurchaseInvNo = 2, Qty = 500, ...
SalesRowID = ?2, SalesInvNo = 101, PurchaseInvNo = 3, Qty = 500, ...

And now calculating profit will be a simple query. It will be even more simple, when you consolidate both purchases and sales into single table (e.g. tblTransactions) with additional field determining the row being for purchase or sale (e.g. -1 for purchase and 1 for sales).

This design also assumes, you may sell not the whole quantity in purchase invoice with single sales invoice. I.e. you can sell products purhased with same purchase invoice stepwise.

[writeprivate]

BLIMEY - that is brilliant - it works. Thank you Jack, you've saved the day. Below is a screenshot of the report with the same sample data/example i have been providing above.

This is going to be harder to integrate but its the first time i've seen something actually work! This has been such a huge problem, it is worth me reconfiguring the rest of my draft application to this. I was hoping to have this kind of output in the form view rather than a report, but I think this will be sufficient.

I need a strong cup of coffee and a cold towel to properly go thru this and understand it. But it works!!! Thanks.

[writeprivate]

Hi there Avril - thanks so much for replying and taking the time to help me.

1. Just so I understand, are your suggestions relating to the first database that I submitted OR the one Orange has added?
2. Would it be possible (I know this is a big ask) for you to download that sample db, make the changes and repost here. It would be amazing to have two working options and then I can have reports and form view.

Thanks in advance if possible

[orange]

Glad it works for you. Happy to help.

[writeprivate]

Does this report work for you - blank on mine (not sure if it is my sample data) but there should be something in stock for it to calculate. There are 2000 units purchased and 1500 units sold. So there should be 500 units of remaining stock.

[orange]

No it is not working. Not sure it ever did. I'll look, but not sure what it is collecting and reporting.
I get no values regardless of the date chosen???

Update: Try adding this criteria <=[forms]![frmreports]![TDate]
to PDate field in query QryStockAvailableSub

The revised SQL for QryStockAvailableSub is

SELECT Purchase.BatchNo, Purchase.PDate, Purchase.Product
, Purchase.PQty, Purchase.PPrice, [pruntot]-[pqty]+1 AS Popn, Purchase.PRunTot
FROM Purchase
WHERE (((Purchase.PDate)<=[forms]![frmreports]![TDate]));

Here's a sample ofthe StockValue Report using endDate(Tdate) of Jan 14/2014, know there are qty 50 (Apple) that are not sod until Jan15/2014



The database in the revised zip file has the updated version of QryStockAvailableSub, and the StockValue Report now works.

[writeprivate]

No it is not working. Not sure it ever did. I'll look, but not sure what it is collecting and reporting.
I get no values regardless of the date chosen???

Update: Try adding this criteria <=[forms]![frmreports]![TDate]
to PDate field in query QryStockAvailableSub

The revised SQL for QryStockAvailableSub is

SELECT Purchase.BatchNo, Purchase.PDate, Purchase.Product
, Purchase.PQty, Purchase.PPrice, [pruntot]-[pqty]+1 AS Popn, Purchase.PRunTot
FROM Purchase
WHERE (((Purchase.PDate)<=[forms]![frmreports]![TDate]));

Here's a sample ofthe StockValue Report using endDate(Tdate) of Jan 14/2014, know there are qty 50 (Apple) that are not sod until Jan15/2014

That's brilliant thanks. I'd use this for sure.

And I'll probably adapt that for another report to show stock value based on a live stock market price which I import from csv daily.