costs for all stock items and stocktake

[rainbows]

hi i have a Dilemma and i am not sure which way to go

so lets say i have 3000 stock items sample below , anyone of these items could come from any country in the world . lets say the first 2 items i purchase from one supplier in england, the next 3 in europe and the last 5 in china

so i create a purchase order and the p/o I send to the supplier must be in the currency for that country but the form could have the information "cost" and "total" in pounds and i convert it for the p/o into the correct amount using the currency i look for on the internet . but this is trial and error to get the correct pounds value into the form

or i can just put the value in the correct currency and not worry about working out any other costs

the problem i then have is

how do i get a pounds total value of all the stock items when i do a stock take that are in the stock list as they are from all over the world and are different currency

thank steve






Stocklist Stocklist

MaterialID	Type	StockNumber	Material	Cost
1​	film..	7511038-01	Film mPP 10.8 µm x 37.5 mm DEM 2.5 - High Isotactic	24.89​
2​	Foil	7805018-01	5 µm x 18 mm aluminium foil	41.32​
3​	film..	7508125-01	Film mPP 7.8 µm x 125 mm SEM 5.0 - High Isotactic	17.35​
4​	Solder	1596B1240	GS001 500gm 1.6 solder 60/40 KP GR400	23.5​
5​	Wire	3096B1330	60/40% tin/zinc wire 2mm dia	12.48​
6​	Wire	3096B1340	99.995Zn Zinc wire 2mm dia	5.06​
7​	Terminal	5036B9078CET	Terminal Boss	0.34​
8​	Terminal	5036B9078DET	Terminal Boss	1.35​
9​	Core	5096B000415	Ø9 mm x 41.5 mm winding core 5 mm	0.01​
10​	Resin	2096B1340	RX701C/BL Epoxy resin blue colour RAL 5017	5.25​

MaterialID	Type	StockNumber	Material	Cost
1​	film..	7511038-01	Film mPP 10.8 µm x 37.5 mm DEM 2.5 - High Isotactic	24.89​
2​	Foil	7805018-01	5 µm x 18 mm aluminium foil	41.32​
3​	film..	7508125-01	Film mPP 7.8 µm x 125 mm SEM 5.0 - High Isotactic	17.35​
4​	Solder	1596B1240	GS001 500gm 1.6 solder 60/40 KP GR400	23.5​
5​	Wire	3096B1330	60/40% tin/zinc wire 2mm dia	12.48​
6​	Wire	3096B1340	99.995Zn Zinc wire 2mm dia	5.06​
7​	Terminal	5036B9078CET	Terminal Boss	0.34​
8​	Terminal	5036B9078DET	Terminal Boss	1.35​
9​	Core	5096B000415	Ø9 mm x 41.5 mm winding core 5 mm	0.01​
10​	Resin	2096B1340	RX701C/BL Epoxy resin blue colour RAL 5017	5.25​

Stocklist Stocklist

MaterialID	Type	StockNumber	Material	Cost
1​	film..	7511038-01	Film mPP 10.8 µm x 37.5 mm DEM 2.5 - High Isotactic	24.89​
2​	Foil	7805018-01	5 µm x 18 mm aluminium foil	41.32​
3​	film..	7508125-01	Film mPP 7.8 µm x 125 mm SEM 5.0 - High Isotactic	17.35​
4​	Solder	1596B1240	GS001 500gm 1.6 solder 60/40 KP GR400	23.5​
5​	Wire	3096B1330	60/40% tin/zinc wire 2mm dia	12.48​
6​	Wire	3096B1340	99.995Zn Zinc wire 2mm dia	5.06​
7​	Terminal	5036B9078CET	Terminal Boss	0.34​
8​	Terminal	5036B9078DET	Terminal Boss	1.35​
9​	Core	5096B000415	Ø9 mm x 41.5 mm winding core 5 mm	0.01​
10​	Resin	2096B1340	RX701C/BL Epoxy resin blue colour RAL 5017	5.25​

 

[MajP]

Normally you would have a conversion table in your Db
tblCurrencyConverstion
-CurrencyID
-CurrencyName (MCX, USD, ..EUR
-CurrencySymbol (if you want to format)
-ConversionFactor ' multiplier to take from this to base

I have never done it but someone here can tell you how you can update this table from the internet automatically

Because this changes and depends when you want to convert from local currency to pounds you probably want to persist the converted value. So use an update query to save the converted value. May want to save the date of the conversion too.

So in your Purchase table or whatever table it is you select a currency ID and value. Then at some point in time you run you update query to save the value in pounds. Either at the time of issuing the PO or a latter date depending on your business rules. If it is at todays rate then run the update query when you run the form, or actually that is just a query.

 

[The_Doc_Man]

or i can just put the value in the correct currency and not worry about working out any other costs

When foreign exchange issues crop up, the EASIEST solution is to have at least two fields - one for the currency that YOU spent, the other for the currency native to the supplier. For strict normalization, we would record only one of those and would just convert the other one as needed - except that currency exchange rates tend to float a little over time, so you would need to know the exchange rate on the date of the transaction. The implied amount of storage for that can be intense. The choice is EITHER to record exchange rates long-term for every country in your list, OR to just record the result of the cost in the supplier's country then do the exchange rate computation to store the results in your preferred currency. Keeping the currency for the two countries - yours and theirs - for the time of the purchase takes less space than keeping exchange rate data and is not subject to misrepresentation when the currency floats again.

Having the correct price in pounds means you can do a summation query to get your costs in pounds. Having the correct price in your suppliers currency lets you recreate the order if needed, a record-keeping thing.

But of course, the real issue is which of those will you need? If my guess is correct, you would probably want both. And I want to clarify - just so you will understand my thought process here - keeping both the native price and your converted price means you know the exchange rate for the date of that purchase because you can back-calculate what it must have been. And that way, you DON'T have to store the rate.

 

[MajP]

And I want to clarify - just so you will understand my thought process here - keeping both the native price and your converted price means you know the exchange rate for the date of that purchase because you can back-calculate what it must have been. And that way, you DON'T have to store the rate.

This is why I suggest you store the conversion date when you do the conversion. Just in case there is a dispute and you have to convert to some other day.

 

[CJ_London]

The problem is you cannot know the exact cost to you to the penny until the money is taken from your account - that will include an exchange rate calculation plus fees from your bank and potentially the receiving bank (depends in the contract you have with the supplier as to who pays what fees). You may well put the fees into a separate cost centre. And rates can vary depending on how you transfer the money and the size of the transaction.

So at the time of raising the purchase order, the best you can do is estimate the cost of the overall transaction and divide that in some way between the items purchased. Once you have the details in your bank account you can then either modify those values, recalculate the cost per item or leave your initial estimate alone and create price and perhaps volume variances to reconcile between your estimate and the actual.

how do i get a pounds total value of all the stock items when i do a stock take that are in the stock list as they are from all over the world and are different currency

Depends on the policy you have for valuing stock. You might be using the standard pricing method (in which case you have price/volume variances), you might be using FIFO or LIFO. Or you might simply revalue based on the exchange rate on the date of stocktake - that is a question to ask your FD/CFO/FC and/or auditors.

 

[gemma-the-husky]

What @CJ_London just said.

This is an accounting problem more than a computer problem. Strictly speaking, stock should be valued on a FIFO basis, but I doubt auditors are going to argue so much if the difference it causes is not material.

Generally speaking whatever currency you use to buy materials, you should account for them in your host currency.

You don't have to adjust cost rates after the fact. You could just write off any currency differences. Again it depends on how material the adjustment is.

 

[jdraw]

Isladogs has info and sample Currency Exchange Rate tracker on his site.

 

[rainbows]

firstly we always get a quote on the order before Placing that order so we know the costs / total costs

and i know have 2 ways to try and deal with the stocktake list

1. always put the quote price in the P/o table in their currency then no exchange rate is needed for the p/o i send to the supplier
my problem i cant solve is them items are in the stocklist table . should i put in another column to state what currency that part is in ?

or

keep all the items in the stock list table in pounds and use the conversion factor to create my P/O using the qoute costs i got from the supplier

the exchange rate we would take on the day of the stocktake.

thanks
steve

 

[CJ_London]

That is a different question- the answer depends on whether you are storing prices against the product (not the stock) from a quotation or price book from the supplier

if I am a supplier supplying product to you at $10/unit that is what I expect to get paid. If you are taking that $10, converting to £ to store in your system at one rate then convert back to $ at a different rate what do you think my reaction would be if your order quoted $9.50?

 

[CJ_London]

firstly we always get a quote on the order before Placing that order so we know the costs / total costs

so you know the cost of the product, shipping and import duty and you know the currency - so you can determine the cost on the day you place the order - does that cost reflect in your bank statement? If not what are you going to do with the difference? Are shipping and import duty included in your product cost? or treated as an overhead (a cost of doing business).

These are all questions you should be asking your finance department

 

[Cotswold]

To my mind a stock take should show the current stock value of an item. The purchase cost is of no moment in the stock take value at all. How you arrive at the current stock value of an item will be a management decision. It may even be related to your sale price. If you bought an item then its difference to the value at stock take will show in the P&L as a loss or gain.

As a simple for instance.
If you purchased units in a fund each month over a year you will (usually) have invested the same amount each month at 12 different unit rates. So each month you will have purchased a varying number of units. The value of your investment on month 13 will be the total number of units X the unit value at that date. Which may be more or less than the 12 payments made, but that is the value of your investment at that date.

In the same way, your stock value should be the value as at the actual stock-take date. It is the function of the buyer to obtain stock at the best price and preferably less than its book value.

 

[gemma-the-husky]

To my mind a stock take should show the current stock value of an item. The purchase cost is of no moment in the stock take value at all. How you arrive at the current stock value of an item will be a management decision. It may even be related to your sale price. If you bought an item then its difference to the value at stock take will show in the P&L as a loss or gain.

As a simple for instance.
If you purchased units in a fund each month over a year you will (usually) have invested the same amount each month at 12 different unit rates. So each month you will have purchased a varying number of units. The value of your investment on month 13 will be the total number of units X the unit value at that date. Which may be more or less than the 12 payments made, but that is the value of your investment at that date.

In the same way, your stock value should be the value as at the actual stock-take date. It is the function of the buyer to obtain stock at the best price and preferably less than its book value.

I don't think that is right. The stock valuation should show the value of the stock calculated on a FIFO basis, so the original purchase cost is important. On that basis you should charge out used products at the FIFO cost. Now that's very difficult to do, so it's much easier to absorb costs at an average cost, or even a standard cost. You might need to compare the book stock value with a true FIFO stock value at the end of the year, and make a final adjustment. That's an accounting exercise rather than an IT issue. It all comes back to the policies designed and defined by your company. There's no absolute right and wrong.

 

[Cotswold]

I totally disagree.

A stock take should represent the value of the stock at the stock-take date. It is a part of the net worth of a company at its year end. To use historical prices would incorrectly adjust the company's value.

If an engineering company purchased a steel section last year at (say) £1,000 /tonne then this year at stock take the market rate was £1,500. Any stock held by the company should be calculated at £1,500. In fact in steel it is not unusual for cut-off lengths from a contract to remain for years, until it can be used with little waste. The steel will not deteriorate and the value of that steel will follow the market rate over various stock-takes.

Similarly, a property investment company will adjust its portfolio to take account of the market at year end. Recently some office blocks have dropped by 40% and they will not be on the books at their original purchase price but at market price. In that situation the company will have to include that loss in their balance sheet. To claim that the office block is worth 40% more than its value at year end would falsely adjust the YE accounts and prevent that loss from being offset against profits.

 

[arnelgp]

i must agree with post #12.
you value them as they come in (on current price).
you don't re-value your stocks.

yesterday you have 500 on your pocket
you buy an item for 300, that leaves 200 on your pocket.
today the price of the item is 350, if you value your stock as 350, how come you still have 200 on your pocket?

 

[AccessBlaster]

Unless the item depreciates over time.

 

[Cotswold]

i must agree with post #12.
you value them as they come in (on current price).
you don't re-value your stocks.

yesterday you have 500 on your pocket
you buy an item for 300, that leaves 200 on your pocket.
today the price of the item is 350, if you value your stock as 350, how come you still have 200 on your pocket?

Basically because you haven't, as yet sold any of your stock so your bank is still 200. If you had bought Apple shares years ago for 300 would you say I've only got 200? Because you've still only got 200 in your pocket. Or would you say I've got 200 plus 300 multiplied by the 30+ increase in the value of your Apple shares?

You have 200 in your account but the 300 spent is now worth 350. So your total assets are now 550. The stock you can sell at 350 plus your profit, then adding to your 200 in the bank. If you want some more you'll then have to pay 350 next time and not 300 because of the market increase. Which is why you add profit to the purchase price, otherwise eventually you will not have enough to buy new stock.

If I go out and buy 50 tonnes of something at 100 a tonne, I've shifted 5,000 from my bank account into stock on hand. If the value of the 50 tonnes rises to 200 per tonne, then I've doubled the business's current stock investment in the 50 tonnes asset. When I sell the 50 tonne my bank account will see an inflow of 10,000 plus my profit on the sale. Business isn't just the money in the bank or pocket.

I reiterate, the stock value at YE should represent the current stock value as a factual business asset. Not some airy-fairy price that was paid months or years ago. The annual stock-take should show the current stock value at YE.

AccessBlaster #15
Correct, if your purchase depreciates then you lose money. As my property investment example in #13.

 

[arnelgp]

Basically because you haven't, as yet sold any of your stock so your bank is still 200. If you had bought Apple shares years ago for 300 would you say I've only got 200? Because you've still only got 200 in your pocket. Or would you say I've got 200 plus 300 multiplied by the 30+ increase in the value of your Apple shares?

no, this is not about market stocks, gaining or lossing or profiting.
this is about stocks you will use for installation/construction.
the need to enter it as Face value he gained it.
If there would be profit/loss that would be the role of the firm's accountant.
for the part of the op, since he is not the accountant, he need to record it as is.
also again, the accountant will take care of any depreciations.
the accountant should have the last word of the book value of your stock.

also if this is a construction firm, the planning dept will take care of all cost
to be back-charged to the main contractor.

 

[rainbows]

My stocklist which could be 5000 items can and does include ,say soap, pens , pencils, gloves , paper , etc etc

The p/o does include the transport costs etc see raised p/o

so should the item i am purchasing in the form i an creating always be in pound or the correct currency . if in pounds i would have to use the currency on the day to try and match the the qoute amount that the supplier has agreed to


the first screen shot show me entering the costs from the supplier in there currency and the cost came from the stock item see botton of screen
but how would i know in a stock take that was in yuan , would i have to put the currency next to all 5000 items ? and calculate that at stocktake

or see screen shot 3 do i put the cost in pound and let the currency take care of the p/o see screen shot 4 i know it is not exact but is is just an example



issue is this if use the fist way , how do i do the stock stake with say the end of yeaer up to date currency as each item is in there currency
or just put is all in pounds and let the p/o just take care of the p/o

steve

 

[arnelgp]

so should the item i am purchasing in the form i an creating always be in pound or the correct currency

in your Local currency. I think management wouldn't care if you paid for it in chinese currency, or german. they would be interested on how much, in your local currency, did you pay for it.

but then again ask your management, you adhere to their business rule (and not on your own).

 

[rainbows]

they are interested how i will do the stocktake calculation thats why i ask should all the stock be in pounds ot in all of the currencies and how do i show that on the stocklist what currency it is in if i need to do that