This is likely to be a bit basic for you guys but I'm struggling to come up with an automated way to calculate which price plan I should put my SIMs on.
Here's the details:
Plan A costs $1$ per SIM and is only for SIMs that use $<100$ kb of data Plan B costs $1.70$ per SIM and is for SIMs in a 'Group Plan' that allows $1$ MB of data per SIM (more on how Group Plans work below) Plan C costs $2.25$ per SIM and is for SIMs in a 'Group Plan' that allows $3$ MB of data per SIM Plan D costs $2.83$ per SIM and is for SIMs in a 'Group Plan' that allows $3$ MB of data per SIM All of the group plans have an additional charge of $1.70$ per excess MB used
Group Plans allow for SIMs to be placed in a plan and, as long as the total allowance for all of the SIMs is not breached, there are no extra costs e.g. if I had $3$ SIMs in the $1$ MB Group Plan, that group would have a total included allowance of $3$ MB. If one SIM used $1.5$ MB and the other $2$ used $0.7$ MB, I would be charged $3 \times 1.70 = 4.10$ for the $3$ SIMs. If, however, they all used a total of $3.5$ MB, I would be charged $3 \times 1.70$ plus (excess data) $0.5 \times 1.70 = 4.95$
What I have to work with is a list of $10,000$ SIMs with each SIMs data usage, ranging from $0$ kb to $40$ MB, and I need a formula (that I can use in Excel) that will work out the best way to assign each SIM to a plan to minimise the total cost.
I have been assuming that it's best to have any SIM with $<100$ kb moved straight to Plan A and that I shouldn't allow any excess data charges to occur but I'm not convinced that this is the case.
Any insights on how to automate this to find the lowest total cost would be greatly appreciated.
