With the recent release of the new Glove case and the subsequent (expensive) case-opening videos, it is becomes important to analyze when and at what price to purchase volumes of pricey digital goods. The easiest way to accomplish this feat is to use Microsoft Excel, which provides the analytical components needed to calculate the relevant information.
I separated the spreadsheet into two sections for clarity. The first part focusses on calculating the optimal quantity to purchase at a given price point, the second part focusses on calculating the optimal price at a given quantity. The first part also calculates the volume cost at any given price point.
First, create three variables: Case Cost, Key Cost1, and Budget. These variables are the only user inputs.
All of the math takes place below this. The blue table calculates the cost of opening a given number of cases using the formula =(B8*$B$5)+(B8*$C$5). This multiplies the case cost by the number of cases and adds it to the cost of buying the same number of keys. Thus, the table calculates the exact cost to open a given number of cases at the given price point.
To determine the proportion of the value of the key to the case, the formula =(B8*C$5)/C8 divides the cost of a given number of keys by the total cost of opening a case. This only happens once because the ratio does not change when the prices are constant.
An array formula determines the maximum number of keys purchasable within the given budget. This formula is far more complicated:
=IF(VLOOKUP(INDEX(B8:B155,MATCH(MIN(ABS(C8:C155-$D$5)),ABS(C8:C155-$D$5),0)),B7:C155,2,FALSE)>$D$5,INDEX(B8:B155,MATCH(MIN(ABS(C8:C155-$D$5)),ABS(C8:C155-$D$5),0))-1,INDEX(B8:B155,MATCH(MIN(ABS(C8:C155-$D$5)),ABS(C8:C155-$D$5),0)))This uses a nested INDEX MATCH to find the number of cases that has the smallest absolute value deviation from the given budget that is also less than the budget. The next cell over uses =VLOOKUP(E11,Table1,2,FALSE) to get the cost of opening that quantity of cases at the given price. This summarizes the relevant values from the table to make them more legible.
Finally, the spreadsheet calculates the most efficient price at which the possible quantity can be purchased at. For example, in the screenshot above, the current case price is $3. With the budget of $35, a maximum of six cases can be opened. The efficient prices is the lowest price at which the current possible number of cases can be purchased at. In the screenshot, the efficient price section states that the lowest cost to buy the six openable cases is $2.52. Compared to the current price, this would save $2.88. Thus, it is more efficient to wait until the price drops to $2.52 to open six cases with the given budget.
The calculation is somewhat complicated:
=ROUNDUP((D5-((E11+1)*C5))/(E11+1),2)+IF(ROUNDUP((D5-((E11+1)*C5))/(E11+1),2)=(D5-((E11+1.01)*C5))/(E11+1),1,0)+0.01This formula takes the given user inputs and calculates the cost of keys. It then solves for the maximum variable cost for the cases and divides this by the number of units desired, which results in the most efficient price. This does the exact same calculations as the price calculator below, only merged into one cell.
The price calculator allows users to define a quantity, budget, and key price and solves for an efficient price.
These formulas are far more straightforward. The first calculation uses =IF((K5+1)*K7>K6,"Costs more than budget!",(K5+1.01)*K7) to determine the cost of keys to open one more than the desired quantity of cases. If the cost of keys exceeds the given budget it returns an error.
Next, to calculate the maximum variable cost of one more than the desired quantity of cases, =K6-K10 finds the difference from the budget to the key cost. Finally, the third calculation divides the maximum variable cost of cases by the number of cases desired. This results in the minimum price at which the given number of cases can be purchased.
The results summarize the efficient price, the total cost of opening the given number of cases, and the ratio of the cost of a key to the cost of a case.
You can access the spreadsheet to do your own calculations here. Enjoy!
Case keys are $2.49, but the community market lists higher prices as a result of non-CSGO players trading. Community market keys also do not activate trade holds on uncrated items. ↩︎