Hi,
I am hoping someone can help! I am trying to set up a spreadsheet for an online business that updates the 'cost of purchase' depending on which stock order has been sold.
For example, if I order 10 units of soap at $10 then the cost is $10. If I make a second order of 10 soaps at $12 then my overall average cost of soap has gone up to $11. What I am trying to show is that for each purchase made, I can look up and see (based on qty sold to date) whether the cost applicable to that particular soap bar is $10 or $12? Is this something that can be easily done?
I have attached a spreadsheet copy paste below that hopefully explains this a little more - the 'av cost' column on the right shows the answers I am looking for but I have just hard written the numbers in - ideally a formula to do this would be great!
<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>
Thanks so much!
Andrew
I am hoping someone can help! I am trying to set up a spreadsheet for an online business that updates the 'cost of purchase' depending on which stock order has been sold.
For example, if I order 10 units of soap at $10 then the cost is $10. If I make a second order of 10 soaps at $12 then my overall average cost of soap has gone up to $11. What I am trying to show is that for each purchase made, I can look up and see (based on qty sold to date) whether the cost applicable to that particular soap bar is $10 or $12? Is this something that can be easily done?
I have attached a spreadsheet copy paste below that hopefully explains this a little more - the 'av cost' column on the right shows the answers I am looking for but I have just hard written the numbers in - ideally a formula to do this would be great!
Cost | Revenue | |||||||||
Qty | Unit Cost | Total Cost | Qty | Unit Rev | Total Rev | Av Cost | ||||
Soap | 10 | $ 10.00 | $ 100.00 | 10 | Soap | 9 | $ 20.00 | $ 180.00 | $ 10.00 | |
Soap | 10 | $ 11.00 | $ 110.00 | Soap | 2 | $ 20.00 | $ 40.00 | $ 10.50 | ||
Bags | 10 | $ 15.00 | $ 150.00 | Soap | 9 | $ 20.00 | $ 180.00 | $ 11.00 | ||
Bags | 10 | $ 16.00 | $ 160.00 | Soap | 5 | $ 20.00 | $ 100.00 | $ 12.00 | ||
Soap | 10 | $ 12.00 | $ 120.00 | 20 | Soap | 3 | $ 20.00 | $ 60.00 | $ 12.00 | |
Bags | 10 | $ 17.00 | $ 170.00 | 30 | Soap | 2 | $ 20.00 | $ 40.00 | $ 12.00 |
<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>
Thanks so much!
Andrew