Inventory Balance ( Packet and Pcs ) & Party Cash Receivable Issue

Hi All,
hope you are doing best,
i am working a inventory IN / OUT and Balance sheet to maintain my own small business. i am trouble in to maintain inventory balance in Packet and Pcs qty. see my attached file in Inv-Balance sheet, i show my desired result. IN-OUT sheet shows inventory purchase and sale in packet and PCS and master code item mention there.
can anybody tell me how to maintain inventory balance packet and Pcs in Inv-Balance sheet, if i sale loose pcs then packet qty convert into pcs, example 2 packet = 40 Pcs, inventory balance after sale 5 Pcs , 1 Packet and 15 Pcs, which i do manual in inv-balance sheet desired result.

second, when i received cash from party P&L sheet column O then sheet break-up-investment cash increase and receivable decrease. total investment 60000.

hope you will understand my point.

Thanks

Thank to reply AVK,
there is an issue, Item Code = D-1, inventory should be 9 packet and 12 pcs loose, in your purchases 10 packet & 2 Pcs, and sale is 5 Pcs in change the type to check this, then Inv-Balance sheet "Result i want", column N,O packet 9, 17 loose, 1 packet = 20 pcs when i sale loose its means packet qty less.

please see the attached file.

Thanks

sorry for the mistake,
inventory of D-1 after sale 5 Pcs, ( i change the type of sale in sale sheet Pcs ). inv-balance sheet must be shown 9 packet and 17 loose, because 5 Pcs sale from packet so its means open a packet then sale. 1 packet = 20 Pcs in master sheet.

revised sheet attached.

Thanks to reply,
i can do this, but in Pcs, not possible to count physical, every time i have to convert Packet and loose inventory separately, here i did not mention color,for example pen, there is so many color, BLUE,BLACK,RED GREEN and some other items have 7 different color, so i dont want to enter in detail color. i just want to maintain simple packet and Pcs inventory, every time my sheet reveal how many packet and pcs of every items for physical verification.
second when i collect cash from party then cash increase and receivable decrease.

Thanks

Yes, Please provide any solution

Hi AVK,
according to your post 5, i convert into Pcs. but can you tell me how i can manage receivable amount from customer pending.
in sheet Receivable-Packet, customer FFF receivable amount 4500, when i change status "Pending" to "Received" P&L-Packet column "O".
in sheet "Break-up-investment-Packet" cash increase and receivable decrease.

Thanks

please see the attached new sheet

in this file, sheet "Inv-Balance-Packet" i am doing manually control Packet and Loose inventory in column K and L. kindly tell me by this formula.
in sheet "Sale-Stock" column M i am doing manually divide packet sale price, this will be done by formula.

Thanks

Hi all,
can anybody tell me Packet or Loose inventory balance maintain by formula column K,L in sheet "Inv-Balance-Packet" with help of sheet "IN-OUT-Packet"
that is item master sheet.

Thanks.

Thanks to reply,
yes i convert it into Pcs, but this is my working only excel sheet calculation to convert it quickly packet and Loose for physical verification stock at once. can you tell me by this formula. all qty is in Pcs. for example, item code D-1 check IN/OUT-Packet then divide / 12, its means 1 packet rest of qty 15 loose.

its working for easily verify stock

Thanks

Thanks JeteMC,
ites really perfect, cheer
can you please tell me two thing, 1st is to maintain Inventory on average cost. recent file code "Duster-1" purchased two time on different dates unit cost 41.67 and 39.17, so average cost would be 40.42 shown on Inv-Balance-Packet sheet column I highlighted item.
2nd , its typical, sale price item in Pcs must be issued on FIFO ( First In First Out ) basis. same item Duster-1 in Sale-Stock Sheet first sale 12 Pcs rate 41.67 then second 476 rate 41.67 and 14 Pcs in 39.17.
here in, i do it manually, actually i issued 490 Pcs at a time, can you tell how i can restrict entry type before issue the stock to check qty first then rate apply?
if stock Qty exceed, in this example stock is 500 qty = 500-24= 476 then purchased 470 total = 946 then - 490 = 456 balance .
the rate will be apply on FIFO basis.

hope you understand my point.

Thanks

OK,
Thanks a lot JeteMc.
i do it again a new post.

Thanks a lot