Hello
Im trying to build a stock card (perpetual inventory system) on MS Excel. So I need to ask something. I understand the perpetual inventory system (weighted average cost method).
I this method we record the purchases by entering the quantity of units purchased and its unit cost, by this we get the total cost of purchases.
The balance in hand updates itself by adding the quantity purchased and total cost purchased to the balances in hand. Therefore average cost is calculated by dividing the total purchases in hand by total quantity in hand.
Same way with Sales. In sales I enter the units sold and for unit cost I use the average unit cost that is prevailing at that time. Now the balance in hand will update itself. Sale causes quantity and total cost of units to decrease but the unit price remains unchanged.
Now how to record purchase returns or sales returns ? What should be the unit cost of returns in both cases (Sales / Purchases) and should the average unit cost of the Balance in Hand change ?
Have a look at this excel file. I got this from a website. I think the average cost is not being properly calculated when purchases & sales are returned and when stock lost is recorded.
http://http//www.geocities.com/farazazha...kcard1.xls
If you notice, the average cost doesnt move when sales or purchases are returned. Now im sure its supposed to move, but not sure in which case; sales or purchase returns.
Dear Kamran and all other senior members,
Although the qestion is simple but it is a valueable thread to educate members with reference to latest application of CO, IAS and other relevant procedures. In my opinion Weighted average cost, moving average cost, FIFO, LIFO should be addressed with reference to industry and trade etc. Certainly at the end Forum would be in a position to suggest the best costing method to either one alongwith merit & demerits thereof.
Thanks Mr.Faraz for putting question which has practical applications and doubts. Let look at the seniors actions.
wasim
Dear Fraz,
Following two things are separate from other.
1. Inventory System
2. Costing methods
<b>INVENTORY SYSTEM</b>
Two methods of maintaining Inventory in practice are given below;
<b>Perpetual Inventory System</b>
This is the system in which all purchases and issues are recorded when the transaction is occurred and accordingly balance is updated at each day-end regularly. Companies use this system.
<b>Periodic Inventory System
</b>
Purchases and issues are updated, may be on weekly, monthly basis or annually on periodic or random basis or as per requirement.
<b>COSTING METHODS
</b>
There three methods of costing and can be used under perpetual inventory system as well as under periodic inventory system.
1. First-in-first out (FIFO)
2. Last-in-first out (LIFO)
3. Moving Average Method.
I think you would well aware of above costing methods. IAS-2 has abolished the LIFO method therefore; other two methods are used for calculating cost of goods sold (costing).
Now, returning to your question, purchase or sale return should be recorded / updated in the store card by using the rate on which purchase or sale (issue) was originally done.
The excel document is need to customize for this purpose but the principal should be followed that all returns should be recorded by using the rate on which the transaction was actually executed.
Regards,
*
Alright, I've made my own stock card. It records the purchases/sales returns at the amount at which they were purchased/sold.
Have a look at this
http://http//www.geocities.com/farazazha...entory.xls
Check the moving cost in this. Do you think its stated fairly here?
Dear Faraz
Under the perpetual system the Inventory account is constantly (or perpetually) changing. When a retailer purchases merchandise, the retailer debits its Inventory account for the cost; when the retailer sells the merchandise to its customers its Inventory account is credited and its Cost of Goods Sold account is debited for the cost of the goods sold. Accounting coaches disregard to use this system in the manufacturing industry. It is adviseable in the trading business where price fluctuates at minimum.
secondly you are misunderstanding weighted average. Weighted Average Divides the total cost of all by the total number of units. so you are in the spread sheet using moving average rather exponential moving average.
Suppose have made transactions in a day at different times at the rate you provided in the spread sheet. obviously your cost of sales vary in a day at different times and at the end of day result would be different.
Value of your closing stock is higer meaning that your gross profit is higher. More over if we add closing stock in cost of sales, it do not agree with purchase value and visa versa.
Your presentation in the spread sheet also differ from the Benchmark treatment under IAS 2.
So In my opinion you should reconsider your spread sheet.
wasim
Yes im going to apply this to a trading business. In my opinion (correct me if Im wrong), we have the following rules for stock card under perpetual inventory
* Purchases
Inventory quantity and total purchase price is added the balance-in-hand. The weighted average cost (WAC) updates itself by dividing the total price by quantity in hand. This changes the WAC.
* Sales
The quantity sold is reduced from the balance-in-hand at the prevailing WAC rate in the stock card. As a result, WAC doesn't change in case of Sales.
* Sales Returns
The quantity is added back to the balance-in-hand at the rate at which they were sold (by rate I mean the cost, not sales price). This 'can' change the WAC. If there were no purchases made during the period of Sales and Sales Returns, then the WAC must be the same as before, therefore WAC will not change when Sales Returns are recorded. But if more purchases were made at different rates, then WAC will change as the Sales Returns (which is to be recorded at the rate at which they were sold; which might differ from current prevailing WAC rate) will modify the WAC.
* Purchase Returns
The quantity of Purchase Returns is to be reduced from balance-in-hand at the rate at which they were purchased. Therefore it is most likely that it will change the WAC also.
* Stock Lost
The stock destroyed is recorded by reducing the quantity in hand at the current prevailing WAC. Therefore it will not amend WAC.
I have gathered the above rules from referring various websites and books. Now please advise if they are true.
Mr. Faraz
It is upto you to make your own rules and you may.
Weighted average does not mean to change result in a day. IAS 2 paragraph 22 states that "under the weighted average cost formula, the cost of each item is determined from the weighted average cost of similar items at the begining of a period and the cost of samilar items purchased or produced during the period." Here "period" doest not mean hours or minutes. "During the period" has also its meaning. In my point of view you are not understanding the weighted average.
As I suggested earlier, Think that you made transactions in a day and there is no opning balance. no return. no loss etc. Now, Open your spread sheet. Make two transaction of 35 & 35 value of stock and cost of sales would be Rs. 1621 & 12378 respectively. Now interchange the purchase rate i.e 190 with 210 and visa versa. The value of stock will change as 1578 and 12422. Result is different. No change in the total purchase value and sales quantity but the result is different. Now understand yourself and apply weighted cost with its meaning.
Jani Websites and books are not wrong. What we understand and interprets is wrong. Nature of business may be different. The auther may be suggesting in the like form for investment in shares and securities where transaction is moving and sale / purchase is being done electronically. etc.
So think and think positively. Do not take results like child. slight delay in passing transaction may change your results and get results at day end closing and also try to understand CUT OFF PROCEDURES.
WASIM