EXCEL Economia de fórmulas de cálculo de escala

Question

Eu preciso de uma fórmula para calcular uma taxa de assinatura reduzida com base no número de assinaturas adquiridas, mas à medida que mais assinaturas diferentes forem adicionadas, elas terão uma taxa mensal diferente.

B3 é o número de inscrições do item 1 Há uma taxa de assinatura mensal com base em # inscritos 1-4 = $ 599 por mês, 5-19 = $ 399 por mês, 20 + = $ 299 por mês

B4 é o nº de inscrições do item 2 1-4 = $ 524 por mês, 5-19 = $ 349 por mês, 20 + = $ 249 por mês

O truque da pergunta é que, se eles comprarem 7 itens # 1s e 25 itens # 2s

Exemplo: 4 * $ 599 * 12mth + 3 * $ 399 * 12mth, em seguida, + 12 * $ 349 * 12mth + 13 * $ 249 * 12mth = $ 102.216

Nós sempre adicionamos as inscrições para o item 1 primeiro, depois adicionamos as inscrições do item 2 (começando onde o item número 1 parou)

Esta é a fórmula de trabalho que usei quando havia apenas um item.

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

    

Answer 1

Aqui está uma abordagem:

Mantive os componentes separados para facilitar o acompanhamento.

As células B3: B4 contêm as quantidades do pedido no seu exemplo.

As células D3: F4 contêm os custos de assinatura anual por item por suporte (acabei de multiplicar as taxas de assinatura por 12 meses).

As células D6: F7 contêm as quantidades por item para cada suporte:

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))

Pode ser possível simplificar algumas das fórmulas, mas estas funcionam.

O custo total na célula B9 é apenas a soma das quantidades vezes os preços:

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

Manter isso detalhado é mais fácil de solucionar e modificar se as coisas mudarem. Você pode apenas ocultar as colunas D: F. Você sempre pode consolidar isso e eliminar os valores intermediários substituindo as fórmulas das células pelas referências de célula.