|
Urgent! Somebody Pleez help!! Inventory Valuation
I need to prepare a report on a periodical basis of inventory on hand Qty, value & age ( 0-30,31-60, 61-90 days etc.) In the database I will have few cost components of each purchase ( FOB, Duty, Freight etc.) by style #. Second table will be Sales(Date, Inv #, Cust, Style, Qty, Selling Px). I need to run the report usually on a month end but sometimes on any given date. Which functions/formulas I should use? I am bit confused. Some one please help with some sample codes/Functions/formulas. Thanks a bunch in advance!! :confused: -- aromaveda ------------------------------------------------------------------------ aromaveda's Profile: http://www.excelforum.com/member.php...o&userid=29449 View this thread: http://www.excelforum.com/showthread...hreadid=494868 |
Urgent! Somebody Pleez help!! Inventory Valuation
We are as confused as you!
Calm down and tell us the layout of the data and what summary features you need. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "aromaveda" wrote in message ... I need to prepare a report on a periodical basis of inventory on hand Qty, value & age ( 0-30,31-60, 61-90 days etc.) In the database I will have few cost components of each purchase ( FOB, Duty, Freight etc.) by style #. Second table will be Sales(Date, Inv #, Cust, Style, Qty, Selling Px). I need to run the report usually on a month end but sometimes on any given date. Which functions/formulas I should use? I am bit confused. Some one please help with some sample codes/Functions/formulas. Thanks a bunch in advance!! :confused: -- aromaveda ------------------------------------------------------------------------ aromaveda's Profile: http://www.excelforum.com/member.php...o&userid=29449 View this thread: http://www.excelforum.com/showthread...hreadid=494868 |
Urgent! Somebody Pleez help!! Inventory Valuation
Hi Bernard, The data lay out:- On worksheet 1 data for purchases - 6 columns containing transactional data- A)Date B) Style#, C) Qty, D) Price (FOB), E) Duty, F)Freight etc... F) Avg Cost. *On Worksheet # 2 * - Data related to sales - A) Date, B) Invoice #, C) Customer, D)Qty Sold, E)Style #, F)Rate, G)Total. Summary features needed - Inventory on hand as of month end ( If possible on any given date) Report could have columns A) Style # B) Total Qty On hand C) Cost Price ( for style per unit = Avg cost from WS 1 ), D) Inventory Aging - 0-30 days old , F) 31-60 Days old, G) 61-90 days old. The aging represents for how long the inventory on hand is sitting in the warehouse. The report should compute inv on hand by adding purchased qty for a month less sales qty and should lay down by the age of the inventory. If item A Qty = 1000 units was purchased in Aug 05, in Sept 500 were sold = Qty on hand should be 500 but should be shown in 60-90 days old column & valuation should be at avg cost. There might be several purchases in a month & so the sales. The qty reduced by sales shoud be on FIFO basis ( First In First Out) e.g. two sep purchases on 8/1/05 500 qty, on 9/1/05 qty 500, sales in Oct 05 qty 400, inv on hand should have two diff agings for balance 100 out of first purch in Aug & 500 in Sept. If purchase/Sales data needs to be on same WS I don't mind, for simplicity I kept it on two separate sheets. thanks Bernard Liengme Wrote: We are as confused as you! Calm down and tell us the layout of the data and what summary features you need. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "aromaveda" wrote in message ... I need to prepare a report on a periodical basis of inventory on hand Qty, value & age ( 0-30,31-60, 61-90 days etc.) In the database I will have few cost components of each purchase ( FOB, Duty, Freight etc.) by style #. Second table will be Sales(Date, Inv #, Cust, Style, Qty, Selling Px). I need to run the report usually on a month end but sometimes on any given date. Which functions/formulas I should use? I am bit confused. Some one please help with some sample codes/Functions/formulas. Thanks a bunch in advance!! :confused: -- aromaveda ------------------------------------------------------------------------ aromaveda's Profile: http://www.excelforum.com/member.php...o&userid=29449 View this thread: http://www.excelforum.com/showthread...hreadid=494868 -- aromaveda ------------------------------------------------------------------------ aromaveda's Profile: http://www.excelforum.com/member.php...o&userid=29449 View this thread: http://www.excelforum.com/showthread...hreadid=494868 |
Urgent! Somebody Pleez help!! Inventory Valuation
This is a big project. Why not get started yourself and tell us what goes
wrong. Start by reading a simple Excel book. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "aromaveda" wrote in message ... Hi Bernard, The data lay out:- On worksheet 1 data for purchases - 6 columns containing transactional data- A)Date B) Style#, C) Qty, D) Price (FOB), E) Duty, F)Freight etc... F) Avg Cost. *On Worksheet # 2 * - Data related to sales - A) Date, B) Invoice #, C) Customer, D)Qty Sold, E)Style #, F)Rate, G)Total. Summary features needed - Inventory on hand as of month end ( If possible on any given date) Report could have columns A) Style # B) Total Qty On hand C) Cost Price ( for style per unit = Avg cost from WS 1 ), D) Inventory Aging - 0-30 days old , F) 31-60 Days old, G) 61-90 days old. The aging represents for how long the inventory on hand is sitting in the warehouse. The report should compute inv on hand by adding purchased qty for a month less sales qty and should lay down by the age of the inventory. If item A Qty = 1000 units was purchased in Aug 05, in Sept 500 were sold = Qty on hand should be 500 but should be shown in 60-90 days old column & valuation should be at avg cost. There might be several purchases in a month & so the sales. The qty reduced by sales shoud be on FIFO basis ( First In First Out) e.g. two sep purchases on 8/1/05 500 qty, on 9/1/05 qty 500, sales in Oct 05 qty 400, inv on hand should have two diff agings for balance 100 out of first purch in Aug & 500 in Sept. If purchase/Sales data needs to be on same WS I don't mind, for simplicity I kept it on two separate sheets. thanks Bernard Liengme Wrote: We are as confused as you! Calm down and tell us the layout of the data and what summary features you need. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "aromaveda" wrote in message ... I need to prepare a report on a periodical basis of inventory on hand Qty, value & age ( 0-30,31-60, 61-90 days etc.) In the database I will have few cost components of each purchase ( FOB, Duty, Freight etc.) by style #. Second table will be Sales(Date, Inv #, Cust, Style, Qty, Selling Px). I need to run the report usually on a month end but sometimes on any given date. Which functions/formulas I should use? I am bit confused. Some one please help with some sample codes/Functions/formulas. Thanks a bunch in advance!! :confused: -- aromaveda ------------------------------------------------------------------------ aromaveda's Profile: http://www.excelforum.com/member.php...o&userid=29449 View this thread: http://www.excelforum.com/showthread...hreadid=494868 -- aromaveda ------------------------------------------------------------------------ aromaveda's Profile: http://www.excelforum.com/member.php...o&userid=29449 View this thread: http://www.excelforum.com/showthread...hreadid=494868 |
Urgent! Somebody Pleez help!! Inventory Valuation
I can get the net inventory on hand by using SumIf function but I cannot figure out how would I do the datewise aging. If you could give me some ideas I will try on my own and will seek your adivse when stuck Thanks Sanjay -- aromaveda ------------------------------------------------------------------------ aromaveda's Profile: http://www.excelforum.com/member.php...o&userid=29449 View this thread: http://www.excelforum.com/showthread...hreadid=494868 |
Urgent! Somebody Pleez help!! Inventory Valuation
Have a look at SUMPRODUCT. Good explanation at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "aromaveda" wrote in message ... I can get the net inventory on hand by using SumIf function but I cannot figure out how would I do the datewise aging. If you could give me some ideas I will try on my own and will seek your adivse when stuck Thanks Sanjay -- aromaveda ------------------------------------------------------------------------ aromaveda's Profile: http://www.excelforum.com/member.php...o&userid=29449 View this thread: http://www.excelforum.com/showthread...hreadid=494868 |
| All times are GMT +1. The time now is 03:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com