EXCEL Formula economies of scale calculation

I need a formula to calculate a subscription charge that reduces based upon the number of subscriptions purchased, but as additional different subscriptions are added they will be at a different monthly rate.

B3 is the # of item 1 subscriptions There is a monthly subscription charge based upon # subscribers 1-4=$599 per month, 5-19=$399 per month, 20+=$299 per month

B4 is the # of item 2 subscriptions 1-4=$524 per month, 5-19=$349 per month, 20+=$249 per month

The trick of the question is that if they buy 7 item #1s and 25 item #2s

Example: 4*$599*12mth + 3*$399*12mth then + 12*$349*12mth + 13*$249*12mth = $102,216

We always add in the subscriptions for item #1 first, then start adding in the subscriptions for item #2 ( starting where item number 1 left off )

This is the working formula that I used when there was only one item.

=IF(B4<5,6288*B4,IF(B4<=19,(B4-4)*4188+25152,IF(B4>19,(B4-19)*2988+87972)))

Solution 1:

Here's an approach:

I've kept the components separate to make it easier to follow.

Cells B3:B4 contain the order quantities in your example.

Cells D3:F4 contain the annual subscription costs per item per bracket (I just multiplied the subscription rates by 12 months).

Cells D6:F7 contain the quantities by item for each bracket:

D6:  =MIN(B3,4)
E6:  =MIN(B3-MIN(B3,4),15)
F6:  =B3-MIN(B3,19)
D7:  =MIN(4-MIN(B3,4),B4)
E7:  =MIN(B3+B4,19)-MIN(B3,19)-MIN(4-MIN(B3,4),B4)
F7:  =B4-MIN(B4,19-MIN(B3,19))

It might be possible to simplify some of the formulas, but these work.

The total cost in cell B9 is just the the sum of quantities times prices:

=D6*D3+E6*E3+F6*F3+D7*D4+E7*E4+F7*F4

Keeping this broken down is easier to troubleshoot, and modify if things change. You can just hide columns D:F. You can always consolidate this and eliminate the intermediate values by substituting the cell formulas for the cell references.