LoftlessOrganist
New Member
- Joined
- Jan 30, 2020
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
I hope someone will be able to help on this. On the face of it, this is quite a simple task, but there’s one part I can’t work out.
Average cost price = Weighted average cost price of units purchased
Note that the formula ignores sales of units. See the attached simplified transaction file which shows a simplified transaction file and calculations on the right. I’ve shown the formulae I’ve used for Day 1 of the calcs. These formulae have been copied down.
Day 1
5,000 units bought for £1 each. Average cost = £1
Day 2
2,500 units bought for £3 each. Average cost = £1.67 (12,500/7,500)
Day 3
3,000 units sold for £3.50 each. Average cost remains £1.67
Day 4
4,500 units sold for £4 each. Average cost remains £1.67
Day 5
6,000 units bought for £5 each. Average cost should be £5 because we hold 5,000 units which cost £5 each.
You’ll see from the example that the all of the above works, until the trader has sold all units and buys new stock from scratch.
Any ideas to make this work please?
Average cost price = Weighted average cost price of units purchased
Note that the formula ignores sales of units. See the attached simplified transaction file which shows a simplified transaction file and calculations on the right. I’ve shown the formulae I’ve used for Day 1 of the calcs. These formulae have been copied down.
Day 1
5,000 units bought for £1 each. Average cost = £1
Day 2
2,500 units bought for £3 each. Average cost = £1.67 (12,500/7,500)
Day 3
3,000 units sold for £3.50 each. Average cost remains £1.67
Day 4
4,500 units sold for £4 each. Average cost remains £1.67
Day 5
6,000 units bought for £5 each. Average cost should be £5 because we hold 5,000 units which cost £5 each.
You’ll see from the example that the all of the above works, until the trader has sold all units and buys new stock from scratch.
Any ideas to make this work please?