Videos
How much does it cost to produce one unit?
What is the difference between total cost per unit and quantity sold methods?
How does an increase in production volume affect the cost per unit?
I am trying to work out a formula to get an average cost of stocks I have purchased. I have the amount purchased (QTY), the total cost (BASIS), and the average price per unit for each transaction (PER).
Trying to sum this all together, the problem I am facing is that a typical average formula for cost per unit is assuming that each transaction purchased only one unit of the stock.
For example in this made up data, in row 2, I purchased 4 units at $122 each, and in row 4, I purchased 6 units at $136 each. I need the averaging formula to account $122 four times and $136 six times.
What’s a formula I can use to account for this?
Let's say I'm trying to buy and sell apples for a profit, and my goal is determine the actual average value of every single apple.
In A1, I have the price of apples from Shop #1, which is 3.00. In B1, I put down 50 since Shop #1 has 50 apples.
In A2, I put down 4.00 since that's the price of apples from Shop #2. However, in B2, I only put down 1 because the shop only sells 1 single apple.
If I take the average of A1 and A2, it will give me 3.50 as the average cost of apples. However, this is incorrect. Clearly it should be closer to 3.00 since there is a total of supply of 51 apples, and 50 of them are priced at 3.00.
Example below:
| A (Price per apple) | B (amount of apples) | |
|---|---|---|
| 1 | 3.00 | 50 |
| 2 | 4.00 | 1 |
| AVERAGE PRICE PER APPLE | 3.5 (NOT CORRECT, but excel calculates it as so) |
How do I determine this in excel?