Hey there, I'm trying to improve a spreadsheet that I've made for my small business, in the SOLD table I use MAXIFS formula to calculate profit but sometimes I bought products cheaper than the previous month then the formula give me a wrong result. Is there a way to get the more recent cost using date column?
MacBook Air 13″, macOS 12.6
I agree 100% with the other statements.
The internal cost will normally be a mix of multiple purchase dates and not only the cost from the last time you bought the product.
But if you really just want the internal cost from the last purchase, here is an option.
Use XLOOKUP and search from "last to first", that will give you the lasted internal cost.
Ralf
I agree 100% with the other statements.
The internal cost will normally be a mix of multiple purchase dates and not only the cost from the last time you bought the product.
But if you really just want the internal cost from the last purchase, here is an option.
Use XLOOKUP and search from "last to first", that will give you the lasted internal cost.
Ralf
Same questions as and concerns as Badunit.
To calculate the per-item profit of a specific product over a specific period you'll need to specify the beginning and end of that period, your purchase price and the number of items in stock at the beginning of the period, your purchase price and the number of items purchased by you during the period, and the number of sales of that item over the period, and the per item sale price of the items sold.
Your first table shows you bought 48 units of item 1 January 30, at a unit price of $6.50. There's no mention of how many items were in stock on the day of that purchase. For the description & calculations below, we'll assume this was an initial stock purchase of the item, and the in-stock total after that purchase was 48 units.
The second table says you sold four units of item 1 on (or by) February 5 at a price of $8.00 each,( leaving you with 44 units in stock). On these four units, the average profit (not including any unspecified costs) was &2.00.
No other sales of this item are recorded prior to your next purchase, so your stock-on-hand on the morning of February 12 was 44 items, all purchased at a unit price of $6.50.
On February 12, you purchased another 96 units of this item at a per unit price of 4.00, bringing your stock in hand to 140 units, 44 of which had cost you $6.50 per unit and 96 that had cost you $4.00 per unit.
The average cost per unit could be determined in two different ways. The calculations below assume a 'first in, first out scenario, where the per unit profit calculations use the actual cost and actual sale prices of each block of inventory. In this case, that means that all the cost price of the first 48 items sold is $6.50 each and the price at which each is sold is the sale price in effect that day.
An alternate method is to calculate the average cost per unit of the 44 remaining items and the 96 added items, then use that average value as the average per unit cost for all sales from then until the next stock purchase.
On February 13, you recorded another sale.
Like the previous one, this one was a sale of 4 units, which lowered the stock on hand to 136 units.
But unlike the February 5 sale, these four items sold for a unit price of $6.00.
As they were part of the original inventory, the per unit purchase price of these units (if one does not use the 'averaged cost' as described above) was $6.50, making the average per unit profit on these 4 units -$0.50, and the average profit of the eight items sold (4*2.00+4*-0.50)/8 = $0.75
Regards,
Barry
Are you sure that is what you want? If you use only the most recent price, you will be ignoring the cost of the items you bought at the earlier price(s). It seems you want either a first-in-first-out price or an average price. Average might be easiest but what are the accounting rules for calculating profit?
How to get the more recent value
