Need a formula for additional quantity needed at a set price to bring down cost avg to equal as close to said price

Question

We have 3 transactions of an item purchased at different prices . The current market price is 10.00 for a total of 6 items with a cost avg of 33.33. (Total investment divided by Quantity)

Is there a formula I can enter in excel or Sheets to spit out the additional quantity I would need to buy at the current price to get my cost avg to equal as close to the current price (within a whole dollar, don't care about the cents)

Currently I have to plug in random numbers to see how close I can get.

2 Examples: If the Current Price is 10.00 the answer is 135 more to bring the cost avg down to 10.99 If the Current Price is 20.00 the answer is 75 more to bring the cost avg down to 20.99

It doesn't have to be within 99 cents it can be closer its just important to specify as close to a dollar otherwise the calculation would be too large just to save a few pennies.

For example buying qty. 10,000 would bring cost avg to 20.01 at a total cost of $200K vs just buying qty. 75 for cost avg 20.99 which only cost $1,500

Note: I'm not sure why this is so hard to convey or find a solution. Its similar to the opposite scenario of having to figure out how many tests you need to score a 100 to get your avg score to equal blah blah.

Answer 1

This is a case in which the algebra you learned in high school comes in handy. So the answer to the eternal question, then, was "I need to know this so I can solve this kind of problem later in life."

Or more correctly, "Jeorje needs to know this so that HE can solve this kind of problem for me later in life. I, myself, don't still don't actually need to know algebra."

If one does the algebra, one can end up with a variety of ways to express the answer. Mine would starts out like so:

The sum of the total amount of money already spent plus the product of the quantity of items to buy at the current cost times that current cost, that result divided by the sum of the quantity already pruchased plus however many are to be bought at the current price, is to hopefully equal the target average price for all of them.

(Bear in mind, it may not be possible. You may have already spent so much that the average can never come down to the target. We'll end up in a situation in which that possibility will be the meaning of a result saying that "negative this many" must be bought.)

The above ends up as the following:

1. Cell A1 has the already purchased quantity

2. Cell A2 has the total paid for those already purchased items (so it is the SUM of your "Total Value" column, or the SUMPRODUCT of your "Quantity" and "Price at Tx" columns.

3. Cell B1 has the current price at which these to be purchased items will be bought.

4. Cell B2 has the hoped for End Average Price of all the items you would end up buying.

5. Cell C1 will have the resulting number of items to buy now at this price.

It would be nice to stop at this point and just write:

=(A2 + C1*B1) / (A1 + C1)

but C1 is not known so you'd be at the point you're currently at, of just having a convenient spot to plug in trial values until you reach a happy result.

But manipulate that algebraicly and you can end up with a variety of results but one simple one is the following. Excel is blindingly fast with arithmetic, as opposed to functions, and so there's not much point to seeking a slicker algebraic result.

=(A2 - A1*B2) / (B2 - B1)

This is not na integer result, normally, and you need to do quantum math here, buying in whole units, so round as you desire. Looks from your examples (135 and 75) that the usual "4/5" rounding is fine for your needs so:

=ROUND( (A2 - A1*B2) / (B2 - B1), 0 )

There is nothing special about any of the Excel work, no tricky functions, no neat tricks, nothing of the kind. It's all in word problem skills: thinking of how the problem would set up, seeing if you have been given, in an obvious "there are 4 pounds of cashews" kind of way or in some less obvious "there are 11 pounds of cashews and peanuts and 7 pounds of them are peanuts" kind of way, the information needed for the solution method you imagined. If so, it's just algebra, mechanical algebra with nothing special, to the end, then set that up in Excel using arithmetic. If you do not have the info to match your imagined solution, then the harder laying out of what you do understand you know and imagining a solution that can use the actually given info method is in play. Either way, your job is an endless series of "word problems" that sometimes need good old algebra, sometimes not as really some trick in Excel or a programming language is needed, and sometimes just has to be done with pencil and paper if you don't have better tools. But it all comes down to organizing each issue as a word problem, so to speak, and clunking your way to a solution with whatever mechanisms you have in hand after organizing/understanding each word problem. No one emphasizes that, but it is that understanding of what's the goal, what's available info-wise, what can be sought to add to that (it's a real world, not an algebra test, so we can seek extra info), and imagining a solution using what ends up available to use which solves each of a day's problems. Today, algebra.