I was wondering if there's an easy way of calculating the profit of an item that has changing costs and changing prices constantly. I believe this would be considered something like Cost of goods sold but having a profit amount at the end of it.
For example, I buy item A at $20 and quantity of 100 on Oct-24th. I then sell it at various prices (and I'll have a list like this for example:
10qnt $29
10qnt $28.80
20qnt $28.50
30qnt $28.45
I then buy more (before I even sell all 100 qnt of my old stock) at $19.50 or so with another 100 qnt. on say Oct-28th
I continue to sell from Oct-28th below:
20qnt $27.50
20qnt $26
20qnt $25
etc. etc.
Anyways, I need to make excel know which qnt that I sold are going to be the price of $20 and which ones are going to be the price of $19.50 for profit.
so if you see above, I have 10qnt $29, 10qnt $28.80, 20qnt $28.50, 30qnt $28.45, 20qnt $27.50 (90qnt total so far)... all of these should be at the cost of $20... then I have the next one which is 20qnt $26 and this should be 10qnt at $20 cost (to make 100qnt total) and then another 10qnt at $19.50 COST. then the rest for 20qnt $26 profit and $19.50 cost.. etc...
Anyways, sorry for the long example. Basically, I'm going to have this on my excel for costs:
Item# Cost Qnt
ItemA $20 100
ItemA $19.5 100
ItemB $50 200
ItemB $45 100
and then for Sale Price table:
Item# Price Qnt
ItemA $29 10
ItemA $28.80 10
.....etc.
ItemB $55 20
ItemB $54 15
...etc.
The program just needs to know how to organize it in a way that it counts how many qnt has sold for ItemA (using the qnt inputted from "Sale Price" table and once it reaches 100qnt at $20 then it goes on to the $19.50 cost price with another 100qnt until there are 100 qnt of those sold on the "sale price" table. Then it should spit out the profits for each line. A new table could be used, or a "Profit" Column can be made on the "Sale Price" table in column C.
I hope this makes sense. This I'm sure is very common in the accounting world and I believe it calculates cost of goods sold but I needed to know what the profit is for each line. Thanks guys you are extremely helpful every single time!
Bookmarks