Movimentação de saldos de estoques

[asampaio]

Pessoal,
Preciso de ajuda com a planilha em anexo que se refere a movimentação de saldos de estoques com base em um estoque inicial e uma previsão de vendas mensal.
Elaborei a planilha em anexo com detalhes específicos para facilitar o entendimento da dinâmica dos dados.
Agradeço quem puder ajudar.
Atenciosamente,
Albert

Movimentação de saldo mensal entre estoques_v2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2		Movimentação de saldo mensal de estoques
3
4		Mês de ponto de corte	out-2022
5
6		Mês			jan-2022	fev-2022	mar-2022	abr-2022	mai-2022	jun-2022	jul-2022	ago-2022	set-2022	out-2022	nov-2022	dez-2022	jan-2023	fev-2023	mar-2023	Período
7		Vendas Realizadas			100	200	250	0	50	500	50	300	150	0	0	0	0	0	0	1,600
8		Previsão de Vendas			0	0	0	0	0	0	0	0	0	200	350	100	75	100	50	875
9		Variação			100	200	250	0	50	500	50	300	150	-200	-350	-100	-75	-100	-50	725
10
11		Saldo de estoque em 1-out-22			Movimentação de saldo mensal de estoques															Saldo de estoque após execução da previsão de vendas
12		Saldo de produto disponível no Estoque 01	450		0	0	0	0	0	0	0	0	0	200	250	0	0	0	0	0
13		Saldo de produto disponível no Estoque 02	250		0	0	0	0	0	0	0	0	0	0	100	100	50	0	0	0
14		Saldo de produto disponível no Estoque 03	100		0	0	0	0	0	0	0	0	0	0	0	0	25	75	0	0
15		Total de saldo de produto disponível	800		0	0	0	0	0	0	0	0	0	200	350	100	75	75	0	0
16
17
18		UMA BREVE DESCRIÇÂO SOBRE A PLANILHA:
19
20		Olá, nobre colega. Obrigado pelo seu interesse neste assunto e aqui vai uma breve explicação sobre a planilha em três passos (1, 2 e 3):
21
22		No exemplo desta planilha, as vendas realizadas (linha 07) se encerraram em Setembro (M7) e, a partir de Outubro (C4 e N8), se iniciou uma nova previsão de vendas mensal (linha 08).
23
24		E, juntamente com a nova previsão de vendas (linha 08), também é informado o saldo de produto disponível (C12:C15) para suportar a previsão de vendas naquele momento. Este saldo (C12:C15) pode ser maior ou menor que a previsão de vendas, ou até mesmo inexistente (zero).
25
26		Até aqui, estou apenas explicando um pouco sobre a planilha para você e não há necessidade da criação de fórmulas nos intervalos citados até aqui marcados com bordas em azul (B4:T9 e B11:C15) porque a planilha já receberá estes dados preenchidos no início de cada mês.
27
28		Então observe que o nosso problema real começa a partir do que se descreve a seguir:
29
30		O PROBLEMA:
31
32		A movimentação dos saldos em estoque (E12:S15) deve ocorrer conforme a previsão de vendas (linha 08). É NESTE INTERVALO (E12:S15) QUE ENTRARÁ A SUA CONTRIBUIÇÃO COM O DESENVOLVIMENTO DE FÓRMULAS PARA O CÁLCULO AUTOMÁTICO DOS DADOS.
33
34		A dinâmica que deve acontecer no intervalo de saldo mensal de estoques (E12:S15) é a seguinte:
35
36		- Havendo saldo de produto disponível em estoque (C12:C14), o consumo de produtos deve sempre começar pelo estoque 01 (C13), depois pelo estoque 02 (C14) e por último pelo estoque 03 (C15), nesta sequência.
37		- Ou seja, neste exemplo, começamos o mês (C4) com um saldo no estoque 01 de 450 produtos (C12) e vamos consumir parte deste saldo no mês de Outubro (N12 e N15), cuja previsão de vendas é 200 produtos (N8). Com isto sobrarão 250 produtos no final de Outubro.
38		- Em Novembro, como a previsão de vendas é 350 produtos (O8), consumiremos o saldo que sobrou do estoque 01 ao final de Outubro (250 produtos) (O12). Com isto, o saldo do estoque 01 foi zerado (T12) e agora usaremos 100 produtos (O13) dos 250 produtos disponíveis no estoque 02 (C13) para completar previsão de vendas de 350 produtos (O8 e O15).
39		- Logo, em Novembro, já podemos notar que houve uma transição do consumo do estoque 01 para o estoque 02; uma vez que o saldo de produto no estoque 01 foi zerado (T12).
40		- Aqui já se pode concluir que quando o estoque 01 (C13) estiver zerado, então deve-se consumir o estoque 02 (C14) e assim por diante.
41		- Continuando. Em Dez a previsão de vendas é 100 produtos (P8), e sabemos que sobraram 150 unds do estoque 02 no final de Nov (=C13-O13). Portanto consumiremos 100 unds do estoque 02 em Dez (P13) para completar a previsão de vendas (P8) e então sobrará 50 produtos ao final do mês de Dez (=C13-O13-P13).
42		- Em Jan-2023, a previsão de vendas é 75 produtos (Q8) e consumiremos os 50 produtos que sobraram do estoque 02 no final do mês de Dez (=C13-O13-P13) na célula Q13. Com isto, o estoque 02 foi zerado (T13). Mas ainda faltam 25 produtos para compor a venda prevista de Jan-2023 (Q8), então consumiremos 25 produtos do saldo de estoque 03 (C14).
43		- No início de Fev-2023, teremos um saldo de produtos no estoque 03 de 75 produtos (=C14-Q14) e como a venda prevista é de 100 produtos (R8), vamos consumir estes 75 produtos e zerar o estoque 03 (T14).
44		- Chegamos ao fim da dinâmica da movimentação dos saldos de estoque (E12:S15). Apesar de haver mais previsão de venda em Fev-2023 e Mar-2023, não há mais saldo e portanto os números ficam zerados (S14).
45
46
47		A SOLUÇÃO ESPERADA:
48
49		ESPERO QUE VOCÊ CONSIGA DESENVOLVER FÓRMULAS NO INTERVALO E12:S15 DE FORMA QUE OS SALDOS DO INTERVALO C12:14 SEJAM DISTRIBUÍDOS NO INTERVALO E12:S15 CONFORME A DINÂMICA DA PREVISÃO DE VENDAS (LINHA 8) (EXPLICAÇÃO ACIMA). BOA SORTE.
50
51
52
53
Explicação_Exemplo Out-2022

Cell Formulas
Range	Formula
T7:T8	T7	=SUM(E7:S7)
E9:T9	E9	=E7-E8
B11	B11	=CONCATENATE("Saldo de estoque em ",TEXT(C4,"[$-pt-BR]d-mmm-aa "))
E11	E11	=B2
T12:T15	T12	=C12-SUM(E12:S12)
C15,E15:S15	C15	=SUM(C12:C14)

Cells with Data Validation
Cell	Allow	Criteria
C4	List	=$E$6:$P$6

 

[Leo Skywalker]

Olá,
Tente isso na célula E12:
Copie para baixo e para a direita.

E12:

=MIN($C12-SUM($D12:D12),E$8-SUM(E$11:E11))

 

[asampaio]

Olá,
Tente isso na célula E12:
Copie para baixo e para a direita.

E12:

=MIN($C12-SUM($D12:D12),E$8-SUM(E$11:E11))

Caro @Leo Skywalker Sua solução funcionou perfeitamente. Estou muito feliz pela sua ajuda.

 

[Leo Skywalker]

Caro @Leo Skywalker Sua solução funcionou perfeitamente. Estou muito feliz pela sua ajuda.

De nada! Que bom que pude ajudar!

 

[asampaio]

@Leo Skywalker Olá, Leo. Espero que você esteja bem. Desculpe-me incomodá-lo. Mas, se for possível, você poderia olhar o meu último thread por favor? O link é Soma mensal acumulada / Accumulated Monthly Sum

Agradeço muito se novamente você puder trazer uma solução.

Atenciosamente,
Albert