segunda-feira, 30 de setembro de 2013

FORMATAÇÃO CONDICIONAL EM CATEGORIA DE GRÁFICOS






Formatação condicional em categorias de gráficos


Introdução

Nesta planilha especial, você conhecerá um método para simular o recurso de formatação condicional em um eixo de categorias de gráfico. No exemplo, temos lucros obtidos por uma empresa ao longo do ano e desejamos realçar as categorias dos meses nos quais os lucros foram inferiores a uma meta estipulada.

A formatação é simulada através da criação de duas séries de dados auxiliares que se sobrepõem ao eixo das categorias. Substituindo as categorias reais do gráfico pelas legendas destas duas séries de dados, podemos atribuir formatos para cada uma delas, de forma a simular o processo de formatação condicional. O modelo montado nesta planilha permite a inserção de qualquer número de formatos distintos para eixos.

Download

quarta-feira, 25 de setembro de 2013

FLUXO DE CAIXA - GESTÃO ESTRATÉGICA



FLUXO DE CAIXA - GESTÃO ESTRATÉGICA







  • Essa planilha apresenta um exemplo hipotético de entradas e saídas de caixa;
  • É preciso analisar detalhadamente cada grupo de conta pelo percentual de representatividade no grupo;
  • É preciso apresentar o fluxo de caixa em sintético e analítico;
  • É preciso analisar o fluxo de caixa pela sazonalidade das operações;
  • O que fazer para analisar as contas pelo grau da sua representatividade dentro de cada grupo?
  • Como fazer para visualizar o fluxo de caixa de forma sintética ou analítica?
  • Como fazer para focar nas contas que darão o maior retorno positivo para as empresa e minimizar os impactos negativos representados dentro do fluxo de caixa?
  • Enfim, é preciso determinar e pesquisar como fazer a gestão estratégica do fluxo de caixa de uma empresa? 

Vejamos a planilha abaixo:

FLUXO
DESCRIÇÃO
TOTAL
10
SALDO INICIAL DO CAIXA
9.700.959
11
(+) ENTRADAS
                 158.393.215
1111.1
Receitas Operacionais
                 157.863.651
1112
Adiantamento de Clientes
                               -  
1113
Cheques Devolvidos Recebidos
                         10.305
112
Rendimentos de Aplicações Financeiras
                       451.755
113
Outros Ingressos
                         67.504
12
(-) SAÍDAS
                 128.487.957
121
Mão de Obra
                  46.185.173
1211
Remuneração
                  24.748.026
1211.1
Salários
19.375.190
1211.10
Convênio Farmácia
12.425
1211.2
Férias
1.767.662
1211.3
Honorários da Diretoria
929.148
1211.4
Bolsa de Estágio
4.967
1211.5
Pensão Alimentícia
185.633
1211.6
Rescisões
976.831
1211.7
Cooperativa / Empréstimos externos
1.427.672
1211.8
Mensalidade/Contribuição Sindical
68.498
1211.9
Associação de Funcionários
0
1212
Encargos
                  10.045.178
1212.1
FGTS
2.533.047
1212.2
INSS
7.490.801
1212.3
IR sobre Salário
21.330
1213
Benefícios Sociais
                  10.536.883
122
Fornecedores
                  28.022.591
123
Impostos e Taxas
                  10.964.769
124
Despesas Operacionais
                  43.139.725
12447
Seguros
                    1.886.291
12447.1
Seguros
1.886.291
12447.2
DPVAT
0
125
Despesas Financeiras/Bancárias
                         69.737
13
DISPONIBILIDADE OPERACIONAL
                  29.905.258

DISPONIBILIDADE LIQUIDA
                  39.606.217
14
(+) Captações
                       150.106
141
Captações Bancárias
                               -  
142
Recursos de sociedade
                  19.424.906
143
Amortizações
                  19.274.800



19
SALDO  FINAL  DO  CAIXA
39.756.323

Informações 
  • Essa planilha apresenta um exemplo hipotético de entradas e saídas de caixa; 
  • É preciso analisar detalhadamente cada grupo de conta pelo percentual de representatividade no grupo; 
  • É preciso apresentar o fluxo de caixa em sintético e analítico; 
  • É preciso analisar o fluxo de caixa pela sazonalidade das operações;
O que fazer?
  • Para analisar as contas pelo grau da sua representatividade dentro de cada grupo? 
  • Para visualizar o fluxo de caixa de forma sintética ou analítica? 
  • Para focar nas contas que darão o maior retorno positivo para as empresa e minimizar os impactos negativos representados dentro do fluxo de caixa?
Problema 
  • Como fazer a gestão estratégica do fluxo de caixa de uma empresa?
  • Para ter acesso a solução do problema e conseguir montar a planilha, clique: Baixar planilha


segunda-feira, 23 de setembro de 2013

COMO PROJETAR VENDAS COM REGRESSÃO LINEAR MÚLTIPLA




COMO PROJETAR VENDAS COM REGRESSÃO LINEAR MÚLTIPLA






Muitas vezes buscamos uma forma mais técnica para projetar a receita de vendas através de modelos estatísticos mais adequados.

Se a empresa entende que exista uma alta correlação entre o aumento de receita das vendas com os gastos com propaganda e também o incentivo através de comissão de vendas, estaremos então diante de uma Regressão Linear Múltipla, pois a Receita de Vendas é influenciada por mais de uma variável "controlada" .

Nesse caso é possível utilizar tanto a função PROJ.LIN (que pode dar informações estatísticas mais detalhadas) ou mesmo a função TENDÊNCIA (que é utilizada quanto já tenho certeza do auto grau de correlação entre as variáveis).

Vejamos então como projetar a receita de vendas para esse mês, tendo como correlação duas variáveis que, a princípio, influenciam no resultado de receita de vendas, que, através da estimativas são determinadas pelos gastos com propaganda e também pela comissão de vendas.

A tabela seguinte será a referência para a planilha definitiva do nosso exemplo de hoje:






























Informações


A tabela acima demonstra a evolução das receitas de uma empresa ao longo dos últimos 20 meses;
A evolução anual das receitas apresenta uma relação razoável com a evolução dos Gastos com Propaganda e Comissão de Vendas simultaneamente;
Isso pode caracterizar uma alta relação entre as variáveis dependentes (Vendas (Y)) e as variáveis independentes (Gastos com Propaganda (M1) e Comissão de Vendas (M2)) que devemos confirmar através da correlação do r2, que pode ser calculado através da expansão da função matricial do PROJ.LIN e também da função TENDÊNCIA do Excel para determinar esse grau de correlação.
Nesse caso temos um problema que envolve a regressão linear múltipla, pois vendas estão relacionadas com duas variáveis, que nesse caso são Gastos com Propaganda e Comissão de Vendas. Se fosse relacionada a mais apenas uma variável, essa seria considerada como regressão linear simples.
Não aconselhamos que a variável dependente (X1) seja incluída na tabela como percentual, pois nesse caso o valor da fórmula retornará como incorreta. Aconselhamos que transforme em número absoluto, conforme demonstrado na coluna D da tabela.
Nesse caso é possível utilizar tanto a função PROJ.LIN (que pode dar informações estatísticas mais detalhadas) ou mesmo a função TENDÊNCIA (que é utilizada quando já tenho certeza do auto grau de correlação).
Importante: Quanto maior for a série histórica, melhor será a qualidade da reta de projetada.

O que fazer?

Se a empresa precisa projetar a receita de vendas de uma forma estatística mais técnica;
Se a empresa entende que exista uma alta correlação entre o aumento das vendas, os gastos com propaganda e o incentivo através de comissão de vendas;

Problema 

Como projetar a receita de vendas para esse mês, tendo como correlação duas variáveis que, a princípio, influenciam no resultado do aumento de receita de vendas, que, segundo estimativas, deveremos determinar os gastos com propaganda nem $95 mil e comissão de vendas em 2,5%?
Solução
Para que possamos ter acesso às informações que nos permitirão montar a planilha e obter as respostas para nosso problemas, clique :  Baixar planilha



quinta-feira, 19 de setembro de 2013

COEFICIENTE DE DETERMINAÇÃO RQUAD



Coeficiente de determinação (RQUAD)






O que é?

A função RQUAD devolve o quadrado da correlação entre duas séries de dados relacionadas; este valor é conhecido como coeficiente de determinação e indica o poder preditivo da sua reta de regressão associada. Este valor está no intervalo [0, 1]: a reta de regressão é um modelo perfeito quando r² = 1.
Sintaxe da função

=RQUAD(val_conhecidos_y; val_conhecidos_x)

Onde val_conhecidos_y é o intervalo de valores da série dependente de dados (os y(x)), e val_conhecidos_x é o intervalo de valores da série independente (os x).
Exemplo

No exemplo a seguir, vamos avaliar a adequação de um modelo de regressão linear realizado pela secretaria do meio ambiente de uma pequena cidade. Nesta cidade, a secretaria realiza medições da emissão anual de monóxido de carbono (CO, um gas tóxico resultante da queima incompleta de combustíveis fósseis) periodicamente. Estas medições são realizadas sempre que a frota de carros circulando pela cidade chega a um múltiplo de mil.

Após oito medições, a secretaria decide montar um modelo de regressão para predizer quais serão os níveis de CO observados quando a cidade tiver certo número de carros. Entretanto, pode ser que os níveis de CO despejados na cidade anualmente dependam de outros fatores, como a produção industrial. Como descobrir se o modelo de regressão para os dados disponíveis projeta adequadamente os níveis a partir de um aumento na frota?

Usaremos a função RQUAD para obter o coeficiente de determinação, que define se este modelo é adequado ou não. Quanto mais próximo de 1 for o resultado, melhor será sua habilidade em predizer valores desconhecidos. Observe as medições disponíveis:
Calcularemos o r2 na célula G12. Selecione-a e forneça os parâmetros adequados. Os dados dependentes são os valores em G4:G11, e os independentes, em F4:F11. Veja o preenchimento da fórmula:
Observe o resultado da aplicação de RQUAD:
O valor obtido, 0,96, indica que a reta de regressão é um ótimo modelo para predizer a emissão anual de CO para uma quantidade determinada de carros. Observe como a reta de regressão representa bem os valores medidos:

Pratique!

quarta-feira, 18 de setembro de 2013

DEPRECIAÇÃO - DPD




Depreciação (DPD)








O que é?


A função DPD calcula a depreciação de um ativo, dado seu valor de compra, resíduo e vida útil. Depreciação é um conceito utilizado em finanças associado à perda de valor de um bem ao longo do seu ciclo de vida: ele é utilizado principalmente em balanços patrimoniais para evitar que os valores declarados de bens duráveis, como por exemplo ativos fixos, fiquem além dos seus valores reais, levando em consideração seu desgaste ou obsolescência.
DPD calcula a depreciação linear do ativo em questão. Se você estiver montando um balanço, será necessário consultar as Normas Brasileiras de Contabilidade quanto a períodos, prazos e modelos de depreciação, e valores residuais regulados para cada tipo de ativo.

Como aplicar a função

Use a seguinte fórmula:

=DPD(custo; valor_residual; vida_útil)
Custo é o valor de compra do ativo;
Valor_residual é o valor do ativo no fim da sua vida útil (geralmente zero, e em outros casos seu valor de venda, deduzidos os custos);
Vida_útil é o número de períodos sobre o qual o ativo sofrerá depreciação; em vários casos, é o número de períodos de uso do ativo.

Exemplo

No exemplo a seguir, temos uma pequena tabela de bens e desejamos calcular a depreciação de cada um. Observe a planilha:
A coluna C contém o custo dos itens; a coluna D contém seus valores residuais, e E, a vida útil em meses. Para calcular a depreciação, use a seguinte fórmula:
Veja o resultado aplicado a todos os bens:

Pratique!


segunda-feira, 16 de setembro de 2013

COMO CALCULAR AS HORAS DE UM PROJETO



COMO CALCULAR AS HORAS DE UM PROJETO







Descrição


Suponha que você tenha que elaborar uma planinha para controlar o número de horas efetivamente trabalhadas e o respectivo valor de um projeto de implantação de Orçamento em Excel. Como podemos somar o valor das horas totais e calcular o valor monetário até então gastos e apresentando o valor por extenso sem que o Excel apresente erro?

Para que não haja nenhuma dúvida será necessário apresentar de forma automática o valor por extenso do valor do custo hora e também o valor total calculado.

No entanto, alguns problemas podem ser detectados na elaboração da sua planilhas como:

A formatação padrão de hora não funciona para obter o valor total das horas trabalhadas;
A formatação padrão de hora não permite que seja calculado o custo total de horas multiplicando apenas o valor total de horas efetivamente trabalhada e o valor do custo hora;
Não existe uma função padrão do Excel que possa apresentar sempre o valor por extenso do custo hora e também do valor total do projeto.

Isso poderá ser simples de solucionar desde que você tenha a função ou ferramenta adequado do Excel, ou ainda criar através de novos Suplementos no Excel. Vejamos como isso pode ser feito.

Importante: Não esqueça de colocar a segurança de macros no nível médio ou baixo além de clicar na opção "Ativar Macro" nas planilhas disponíveis abaixo quando você for abrí-la. Caso contrário você terá problemas para utilizar essas planilhas.

Informações 
  • Suponha que você tenha que elaborar uma planinha para controlar o número de horas efetivamente trabalhadas e o respectivo valor de um projeto de implantação de Orçamento em Excel; 
  • Para que não haja nenhuma dúvida será necessário apresentar de forma automática o valor por extenso do custo hora por dia, assim como o respectivo valor total calculado.
O que fazer? 
  • Se a formatação padrão de hora não funciona para a soma de datas? 
  • Se a formatação padrão de hora não permite que seja calculado o custo total de datas multiplicando apenas o valor total de horas efetivamente trabalhada e o valor do custo hora? 
  • Se for requisitado apresentar sempre o valor por extenso do custo hora e também do valor total do projeto?
Problema 

Como calcular o valor total de horas efetivamente trabalhadas assim como apresentar o respectivo valor por extenso? 

Baixe a planilha para praticar:Baixar planilha


sábado, 14 de setembro de 2013

SISTEMA MODELO PARA CÁLCULO DE BONIFICAÇÕES





SISTEMA MODELO PARA CÁLCULO DE BONIFICAÇÕES




Introdução


Esta planilha contém um sistema completo para cálculo de bonificações de funcionários através de metas definidas pelo usuário. A tabela de metas pode ser preenchida de forma muito flexível e admite até cinco metas distintas, com números variáveis de graus de satisfação da meta.


Para utilizar o sistema, basta preencher as metas desejadas na tabela, com cada uma das suas faixas e a porcentagem correspondente ao bônus pelo cumprimento da meta. Em seguida, preencha a tabela de funcionários, com seus nomes, a porcentagem que cada um cumpriu das metas e seus salários (para base de cálculo).


As células em texto na cor azul são editáveis

Metas (até cinco)


Código
Descrição
Mínimo
%
01
Satisfação do cliente



Sem bônus
00%
00%

Regular
70%
02%

Bom
80%
05%

Ótimo
90%
10%
02
Redução de custos



Sem bônus
00%
00%

Menor
02%
05%

Maior
05%
10%
03
Aumento nas vendas



Até 10%
00%
02%

Até 20%
10%
05%

Até 30%
20%
10%

Mais
30%
20%
Baixe a planilha para praticar, clicando:  Baixar planilha