COMO MUDAR O IDIOMA DO BALANÇO PATRIMONIAL

COMO MUDAR O IDIOMA DO BALANÇO PATRIMONIAL

Como estruturar a mudança automática do idioma do Balanço Patrimonial sem comprometer o tamanho do seu demonstrativo e evitar erros e constantes ajustes no layout e na forma de apresentação?

Suponha que você tenha que estruturar uma planilha de Balanço Patrimonial e tenha que trocar constantemente o idioma dependendo da pessoal que precisa ler e analisar esse demonstrativo;

Digamos que essa troca tenha que ser feita de uma forma automática entre o idioma Português e o Inglês;

Se você desenvolver uma nova planilha espelho em uma mesma planilha, mas com idiomas diferentes ela poderá duplicar o tamanho do seu arquivo e a probabilidade de ocorrer erros nas fórmulas, formatações e forma de apresentações serão constantes. Isso poderá duplicar o seu trabalho no uso do Excel.

Vejamos como tudo isso pode ser solucionado para que seu Balanço, elaborado em Português, seja automaticamente grafado em Inglês, conforme segue:

			31-dez-12	R$1.000
		LIABILITIES
65.329	34,7%	CURRENT LIABILITIES	51.506	27,3%
16.743	8,9%	Suppliers of goods and services - accounts payable	$20.706	11,0%
28.364	15,1%	Taxes	$2.384	1,3%
-16.600	-8,8%	Salaries	$2.501	1,3%
-4.732	-2,5%	Income tax/Social contribution	$468	0,2%
7.032	3,7%	Statutory interest, Dividends and interest on capital	$96	0,1%
39.359	20,9%	ST - Loan	$25.291	13,4%
2.051	1,1%	Other liabilities	$60	0,0%
144	0,1%			0,0%
72.361	38,4%	LONG TERM LIABILITIES	22.309	11,8%
14.891	7,9%	LT - Loan	$22.186	11,8%
2.100	1,1%	Others liabilities	$123	0,1%
50.727	26,9%	MINORITARY INTEREST	13.149	7,0%
11.699	6,2%			0,0%
3.562	1,9%	SHAREHOLDERS´ EQUITY	101.453	53,8%
	0,0%	Share capital	$53.097	28,2%
54.755	29,1%	Capital reserve	$6.015	3,2%
-26.165	-13,9%	Reevaluation reserve	$8.478	4,5%
28.590	15,2%	Reserve Earnings	$23.618	12,5%
	0,0%	Retained Earnings	$10.245	5,4%
14.513	7,7%			0,0%
-7.637	-4,1%			0,0%
6.876	3,6%			0,0%
188.417	100,0%	TOTAL LIABILITIES	188.417	100,0%

Informações 

• Suponha que você tenha que estruturar uma planilha de Balanço Patrimonial e tenha que trocar constantemente o idioma dependendo da pessoal que precisa ler e analisar esse demonstrativo; 
• Digamos que essa troca tenha que ser feita de uma forma automática entre o idioma Português e o Inglês; 
• Se você desenvolver uma nova planilha espelho em uma mesma planilha, mas com idiomas diferentes ela poderá duplicar o tamanho do seu arquivo e a probabilidade de ocorrer erros nas fórmulas, formatações e forma de apresentações serão constantes. Isso poderá duplicar o seu trabalho no uso do Excel. 

O que fazer? 

• Para trocar o idioma do Balanço Patrimonial e outros demonstrativos entre o Português e Inglês de forma automática? 
• Para criar uma “Planilha Espelho”? 
• Para que as pessoas “tenham dificuldade de descobrir esse truque”? 
• Para incluir e excluir linha no Balanço Patrimonial sem que comprometa a estrutura original desse demonstrativo? 
• Para destacar em vermelho os percentuais negativos da análise vertical de cálculo de proporcionalidade entre cada conta do Balanço Patrimonial e o Total do Ativo e Passivo, respectivamente? 

Problema 

• Como estruturar a mudança automática do idioma do Balanço Patrimonial sem comprometer o tamanho do seu demonstrativo e evitar erros e constantes ajustes no layout e na forma de apresentação?
• Baixe planilha para praticar: Baixar planilha

FUNÇÕES DO EXCEL - TENDÊNCIA

FUNÇÃO TENDÊNCIA

O Que é?

Após ajustar um modelo de regressão para um conjunto de dados, podemos prever o valor médio de uma variável dependente de outra. Isto pode ser feito através do uso da função TENDÊNCIA do Excel, que devolve valores em uma tendência linear a partir de um conjunto dado de pontos.

A regressão linear aproxima uma reta y(x) = ?x + c a um conjunto de pontos dados onde há dependência de uma variável em relação a outra. A reta é calculada de forma a minimizar a distância entre seus pontos e os valores observados.

Por exemplo, sabendo-se que há uma relação entre as vendas de uma loja e sua quantidade de clientes em determinado dia, e que esta relação pode seguir um modelo linear, dados obtidos por regressão podem servir para a estimativa das vendas de acordo com a quantidade esperada de clientes em uma data.

A diferença entre as funções TENDÊNCIA e PREVISÃO é que TENDÊNCIA pode ser aplicada em forma matricial, de forma a calcular vários valores simultaneamente.

Sintaxe da função TENDÊNCIA

=TENDÊNCIA(val_conhecidos_y; val_conhecidos_x; novos_x; constante)

• Val_conhecidos_y são valores de y que você já conhece na relação y(x) = ?x + c.
• Val_conhecidos_x são valores de x que você já conhece na relação y(x) = ?x + c. Para cada valor de x há um valor de y correspondente de acordo com a relação acima.
• Novos_x são os novos valores de x para os quais você deseja que a função TENDÊNCIA devolva valores y(x)correspondentes. Você pode trabalhar em forma matricial se quiser fornecer mais de uma célula a este argumento, ou utilizar apenas um valor para novos_x;
• Constante é um valor lógico que força a constante c a se igualar a zero. Este valor é opcional e definido por padrão como verdadeiro. Caso o valor utilizado seja falso, a constante será zerada de forma a obtermos a relação y(x) = ?x: utilize esta opção para obrigar a reta da regressão a passar pelo ponto (0; 0).

Suponha que o gerente de vendas de uma loja quer prever as vendas futuras com base na quantidade de clientes estimados para as próximas semanas. O departamento de vendas praparou uma tabela que relaciona o número de clientes com vendas totais efetuadas pela loja no período de 20 semanas. Observe a planilha:

Neste caso, nossa variável independente (x) é o número de clientes; a variável dependente (y) é o volume de vendas. Note que a tabela do departamento não possui os dados para as semanas 21, 22 e 23. O objetivo do gerente é preparar uma função que estime a tendência das vendas para estas semanas.

Veja abaixo um gráfico dos dados disponíveis. Ele contém os pares (clientes; vendas) da listagem e a reta que melhor representa estes valores:

Ao usar a função TENDÊNCIA, o Excel devolverá o valor de y(x) para o x dado. Note que elaborar um gráfico não é um passo necessário para o uso desta função.

Calcularemos a tendência para a semana 21 na célula H24. Siga os passos abaixo:

• Selecione a célula H24;
• Abra o assistente de função;
• Selecione a categoria estatística;

Exemplo

• Selecione a função TENDÊNCIA;
• Insira os argumentos da função. Os valores conhecidos de y são H4:H23; os valores associados de x sãoG4:G23 e o valor novo de x está em G24. Observe o preenchimento dos campos:

Ao preencher os dados do assistente, você deve ter a seguinte fórmula na célula H24:

Repita os passos acima em H25 e H26 para obter os valores estimados para as próximas três semanas:

Pratique!

Ver exemplo em vídeo

Baixar planilha

FERRAMENTAS DO EXCEL - SOLVER

FERRAMENTAS EXCEL - SOLVER

O que é?

O Solver é uma ferramenta poderosa do Excel que permite fazer vários tipos de simulações na sua planilha, sendo utilizado principalmente para análise de sensibilidade com mais de uma variável e com restrições de parâmetros.

Quando encontramos mais de uma variável em um problema, com necessidade de limites e restrições, o Atingir Meta não poderá solucioná-lo, pois tem limites de parâmetros para simulação. Para isso, devemos utilizar o recurso Solver.

Importante: Para ativar o Solver na sua planilha e liberar a utilização, você deverá ativá-lo em Suplementos, conforme explicado em Suplementos solver.

Com o Solver, você pode localizar um resuldado ideal para uma fórmula em uma célula na sua planilha, chamada decélula de destino, tendo disponível as seguintes possibilidades:

• Maximizar valores;
• Minimizar valores;
• Atingir uma meta de valor específico.

Ele trabalha com um grupo de células relacionadas direta ou indiretamente com a fórmula na célula de destino. Ou seja, todas as células que influenciam no resultado da célula destino poderão ser alteradas pelo próprio Excel, desde que sejam fórmulas interrelacionadas e atinjam a meta desejada, avaliando todas a restrições e atingindo o resultado mais próximo possível.

O Solver ajusta simultaneamente as variáveis nas células que você especificar, chamadas de células ajustáveis, para atingir o resultado esperado por você através da célula de destino, a qual nunca pode ser uma fórmula e sim um input para que o Solver possa ser executado.

Importante: As células variáveis são sempre dados imputados que podem alterar o resultado das células destino. Portanto as células variáveis só podem ser input, caso contrário o Excel irá retornar um erro de consistência.

A melhor forma de entender o Solver é realmente através de um exemplo prático. Então vejamos:

Um empresário decide reduzir o seu preço unitário de venda em 20% para que ele possa se igualar ao principal concorrente em termos de preço. Porém esse mesmo empresário não quer que o seu lucro estimado de $24.500 seja reduzido.

Mas, se o preço unitário for reduzido em 20%, conforme planejado, o Lucro Líquido cairá para $13.860,00.

Considerando as prováveis variáveis, pergunta-se:

• Qual o percentual de aumento do volume de vendas para compensar a redução do preço?
• Qual o percentual possível de redução do custo variável?
• Qual o percentual possível de redução do custo fixo?

Veja a planilha abaixo com os resultados projetados originalmente pela empresa, antes de efetuar a redução dos preços:

Considerando a planilha acima, qual a melhor solução se eu quiser maximizar o meu resultado considerando as células variáveis todas em conjunto e simultâneas? Vejamos como isso pode ser feito no Solver:

Exemplo

1º Passo – Especificar a célula de destino que se deseja minimizar, maximizar ou ajustar para um determinado valor. Neste caso $C$13:

• Acesse o menu Ferramentas/Solver;
• Em Definir célula de destino informe $C$13;
• Em Igual a selecione Máx;

2º Passo – Especificar as células variáveis a serem ajustadas até uma solução ser encontrada:

• Em Células variáveis informe $C$3:$C$5, que são as células que irão sofrer alterações para que o Lucro Líquido possa ser maximizado. Veja a seguir:

Importante: Se você clicar no botão Estimar o Excel irá incluir no campo Célula variáveis todas as células que são inputs e que podem influenciar o resultado final da Célula de destino. Portanto esse botão deve ser utilizado com muito cuidado e atenção, pois nem sempre queremos que outras variáveis imputadas sejam ajustadas pelo Solver.

3º Passo – Especificar as células de restrição que devem ficar dentro de determinados limites ou satisfazer os valores de destino. Vejamos:

• O volume de vendas não pode ser superior a quantidade em estoque no período. Sendo assim, $C$3 não pode ser superior a 230 unidades;
• O custo variável unitário não pode ser inferior ao que poder ser negociado com o fornecedor, principalmente visando manter a qualidade do produto final a ser vendido. Então nesse caso $C$4 não pode ser inferior a $175, que foi o melhor nível negociado com o fornecedor;
• O custo fixo total não pode ser inferior a uma estrutura mínima necessária para que a empresa possa funcionar adequadamente. Nesse caso, o valor mínimo em $C$5 é atingir uma redução de no máximo 5% dos custos fixos atuais, passando então de $5.000 para atingir um valor mínimo de até $4.750.

O Solver trabalha com a metodologia de estatística avançada tentando encontrar a "melhor solução" para o problema apresentado. Para tanto utiliza-se de problemas lineares e não lineares que podem ser especificados pelo botão Opções.

Para problemas lineares, não existe limite ao número de restrições.

Já para problemas não-lineares, cada célula ajustável pode ter as seguintes restrições: uma restrição binária; uma restrição inteira mais limites inferior, superior ou ambos; ou limites superior, inferior ou ambos; e você pode especificar um limite superior ou inferior para até 100 outras células.

Importante: O botão Opções apresentada diversos parêmtros estatísticos avançados para os problemas lineares e não-lineares, que podem ser ajustados manualmente ou deixar que o Solver apresente a "melhor solução". Para mais detalhes veja o artigo Solver: Opções.

Como aplicar as restrições no Solver:

Você pode submeter a restrições as células ajustáveis (variáveis), a célula de destino ou outras células direta ou indiretamente relacionadas com a célula de destino incluindo na estrutura Solver abaixo:

Os operadores abaixo podem ser usados em restrições:

• <= Menor que ou igual a
• >= Maior que ou igual a
• = Igual a
• núm Inteiro (aplica-se somente a células ajustáveis)
• bin Binário (aplica-se somente a células ajustáveis)

Veja como podemos incluir as restrições acima descritas do nosso exemplo no Solver:

• Clique no botão Adicionar e você verá a estrutura para incluir a primeira restrição, onde $C$3 (volume de vendas) não poderá ser superior a 230 (quantidade máxima em estoque por período);

• Clique novamente no botão Adicionar da tela de restrições para incluir mais o limite de redução dos custos variáveis unitários, onde $C$4 não poderá ser inferior a $175;

• Clique mais uma vez em Adicionar para incluir a última restrição no nosso exemplo, onde só poderemos reduzir o custo fixo total em, no máximo, 5%, o que significa que a célula $C$5 deverá ser maior ou igual a $4.750;

• Agora clique em OK para finalizar as restrições.

4º Passo – Solicitar que o problema seja resolvido pelo Solver do Excel, considerando todos os parâmetros e restrições. Vejamos:

• Clique em Resolver e você verá a seguinte tela:

Importante: Se o Solver conseguir resolver o problema considerando todos os parâmetros e restrições apresentados ele apresentará uma tela como a demonstrada acima. Se "estourar" o número de interações de cálculo ele irá informar que não será possível resolver, a não ser que os parâmetros e restrições sejam revistos.

Nessa tela você terá as seguintes opções:

• Manter solução do Sover: para manter os resultados que foram atingidos pela ferramenta Solver;
• Restaurar valores originais: para restaurar os valores originais;
• Relatórios: para ter acesso aos relatórios comparativos sobre as modificações executadas na planilha (para mais detalhes veja Solver: Relatórios);
• Salvar cenário: No botão Salvar cenário será possível salvar a solução atual do Solver como um cenário (opcional);
• Para finalizar, clique em OK para manter os novos valores estimados pelo Solver, siga o resultado abaixo:

Conclusão: o máximo que o modelo pode apresentar com os parâmetros e restrições incluídas foi um Lucro Líquido de $17.444.

Pratique

Ver exemplo em vídeo

Baixar planilha

COMO PREVER PAGAMENTOS

COMO PREVER PAGAMENTOS

• Suponha que você tenha que elaborar uma planilha de planejamento de pagamentos. Como determinar os valores a serem pagos na semana, mês e ano corrente destacado de uma lista de duplicatas a pagar?
• Como proceder se você quiser saber o dia da semana em que deverá ser feito o pagamento? E se, por motivo de espaço, quiser saber o dia da semana de forma abreviada (três letras)?
• Como fazer se você quiser destacar o dia do pagamento, além do dia da semana, quanto ao mês, dia e ano?
• E se você quiser destacar os pagamentos da semana, mês ou ano corrente, destacando duplicata por duplicata?
• E se você quiser saber separadamente o valor total a ser pago no ano e no próximo ano também?
• Nesse caso existem milhares de maneiras de se estruturar uma planilha adequada para obter as informações desejadas. Vejamos uma sugestão de como isso pode ser feito.

PREVISÃO DE PAGAMENTOS
Número da	Data	Valor
Duplicata	completa	a Pagar
DP-199	04/03/2014	R$   16.626,00
DP-659	29/01/2014	R$   74.796,00
DP-154	16/05/2014	R$   99.142,00
DP-542	28/10/2013	R$   52.025,00
DP-783	09/06/2013	R$   92.010,00
DP-990	27/07/2013	R$   23.413,00
DP-894	01/07/2013	R$   26.779,00
DP-896	12/08/2013	R$   36.450,00
DP-006	10/03/2014	R$   23.712,00
DP-006	04/01/2014	R$   83.678,00
DP-897	29/08/2013	R$   71.202,00
DP-898	08/09/2013	R$   94.553,00
DP-454	08/11/2013	R$   41.932,00
DP-455	07/04/2014	R$    81.163,00
DP-456	02/02/2014	R$   53.236,00
DP-457	10/08/2013	R$   50.423,00
Total		921.140,00

Informações 

• Suponha que você tenha que elaborar uma planinha de planejamento de pagamentos na semana, mês e ano; 
• Para testar bem a planilha, o Excel deverá apresentar datas aleatórias (função ALEATÓRIOENTRE) entre a data de hoje (função HOJE) e a data daqui a 12 meses (função DATAM); 
• Para explorar esse teste na planilha, o Excel deverá apresentar valores aleatórios (função ALEATÓRIOENTRE) entre R$99 e R$99.999; 

O que fazer?

• Se eu quiser saber o dia da semana em que deverá ser feito o pagamento? E se, por motivo de espaço, quiser saber o dia da semana de forma abreviada (três letras)?
• Se eu quiser destacar o dia do pagamento, além do dia da semana, quanto ao mês, dia e ano?
• Se eu quiser destacar os pagamentos da semana, mês ou ano corrente, destacando duplicata por duplicata?
• Se eu quiser saber separadamente o valor total a ser pago no ano e também no próximo ano?

Problema 

• Como determinar os valores a serem pagos na semana, mês e ano corrente destacado de uma lista de duplicatas a pagar? 
• Baixar planilha