Embed Size (px)
Citation preview
Este livro tem o objetivo de ajudar os contabilistas e gestores de empresas em geral a utilizar o Excel como uma ferramenta para suas análises fi nanceiras e contábeis. Em linguagem simples e didática, o autor demonstra como a Contabilidade, pela otimização deste progra-ma, pode se transformar em um instrumento efi caz de apoio admi-nistrativo e fi nanceiro na gerência dos negócios. Para tornar a obra completa, também são tratados teorias e procedimentos contábeis e, assim, o leitor terá condições de entender como aplicar as ferramentas do Excel no dia a dia contábil.
Nesta nova edição do Excel para Contadores, o conteúdo foi atualiza-do e foi inserido um capítulo de como utilizar o VBA para elaborar um formulário de cadastro de clientes. Como é a premissa dessa obra, a ideia é que a difi culdade vá aumentando gradativamente, portanto, esse novo capítulo demonstra que por meio do Excel e utilizando o re-curso de programação, o usuário poderá criar relatórios e formulários de acordo com sua necessidade. Somado à essa nova instrução todo o conhecimento já adquirido durante a leitura, o leitor terá condiçõesde utilizar os mais diversos recursos oferecidos pela ferramenta. Lem-brando que, além do conhecimento prático e teórico contido na obra, éfundamental que o usuário tenha curiosidade e arrojo para que seu diaa dia fi que cada vez mais prático, efi ciente e preciso.
O leitor poderá baixar as planilhas (modelo), pelo site www.excelparacontadores.com.br, depois de digitar o código de usuário e senha. Além disso, o leitor poderá testar o conhecimento adquirido a cada capítulo estudado utilizando os exercícios de fi xação que se encontram no fi nal de cada capítulo da obra.
9 7 8 8 5 3 7 9 2 8 1 1 0 >
ISBN 978‐85‐379‐2811‐0
Exce
l par
a Co
ntad
ores
• 9
a edi
ção
EXCEL PARA CONTADORES
Mário SebaStião de azevedo Pereira
Atua há mais de 20 anos na área de consultoria contábil e Administrativa. É autor dos Livros Excel para Contadores, Excel Avançado para Contadores, Guia Prático do Contabilista Normas Contábeis Teoria e Prática, Gestão de Custos, Gestão Tri-butária, todos editados pela IOB. Ministra cursos pelo centro de treinamentos IOB.
SUMÁRIO
PREFÁCIO ................................................................................................ 9
INTRODUÇÃO ........................................................................................ 17
CAPÍTULO 1 - CONCEITOS BÁSICOS DO EXCEL ............................ 19
1.2 Construção das fórmulas matemáticas .................................. 25
Exercícios de fixação ....................................................................... 27
Gabarito ........................................................................................... 28
CAPÍTULO 2 - CONCEITOS BÁSICOS DE CONTABILIDADE ......... 29
2.1 Conceito geral de contabilidade ............................................ 29
2.2 Como funcionam os dados na contabilidade ........................ 31
2.3 Relatórios contábeis ............................................................... 33
Exercícios de fixação ....................................................................... 35
Gabarito ........................................................................................... 36
CAPÍTULO 3 - IMPORTAÇÃO DE DADOS ......................................... 37
3.1 Como importar os dados ....................................................... 39
3.2 Configuração dos dados ........................................................ 43
Exercícios de fixação ....................................................................... 48
Gabarito ........................................................................................... 49
CAPÍTULO 4 - INICIANDO UMA ANÁLISE ....................................... 51
4.1 Projetando um resultado ....................................................... 52
4.2 Copiando uma fórmula ......................................................... 54
4.3 Renomeando uma célula ....................................................... 55
4.4 Projetando a inflação ............................................................. 57
4.5 Ocultando uma coluna .......................................................... 59
Exercícios de fixação ....................................................................... 60
Gabarito ........................................................................................... 61
12 Mário SebaStião de azevedo Pereira
CAPÍTULO 5 - DEPRECIAÇÃO .............................................................. 63
5.1 Renomeando uma planilha .................................................... 66
5.2 Depreciação ........................................................................... 67
5.3 Planilha de depreciação ......................................................... 68
5.4 Personalizando uma tabela .................................................... 72
5.5 Contabilização da depreciação .............................................. 73
Exercícios de fixação ....................................................................... 79
Gabarito ........................................................................................... 81
CAPÍTULO 6 - APROPRIAÇÃO DE FÉRIAS E 13º SALÁRIO .............. 83
6.1 Conceito de férias .................................................................. 83
6.2 Conceito de 13º salário ......................................................... 85
6.3 Regime de caixa e de competência ........................................ 88
6.4 Lançamentos regime de competência .................................... 88
6.5 Lançamentos regime de caixa ................................................ 89
6.6 Planilha de controle de 13º salário e férias ............................ 90
6.7 Lançamentos contábeis ......................................................... 92
6.8 Por que a planilha é necessária? ........................................... 94
Exercícios de fixação ....................................................................... 97
Gabarito ........................................................................................... 98
CAPÍTULO 7 - BALANÇO ...................................................................... 99
7.1 Conceitos básicos .................................................................. 99
7.2 Como é formado o balanço? ................................................. 100
7.3 Como funciona o balanço? ................................................... 106
Exercícios de fixação ....................................................................... 107
Gabarito ........................................................................................... 108
CAPÍTULO 8 - INTRODUÇÃO ÀS ANÁLISES FINANCEIRAS .......... 109
8.1 Análise vertical ...................................................................... 111
8.2 Como o Excel faz o cálculo? ................................................ 113
8.3 Análise horizontal ................................................................. 115
8.4 Quocientes de liquidez .......................................................... 116
8.5 Outros quocientes ................................................................. 119
8.6 Comparação das análises ....................................................... 121
excel Para contadoreS 13
Exercícios de fixação ....................................................................... 123
Gabarito ........................................................................................... 124
CAPÍTULO 9 - GRÁFICOS ..................................................................... 125
9.1 Produzindo um gráfico .......................................................... 126
9.2 Gráficos no Excel 2007 ......................................................... 134
9.3 Outros modelos ..................................................................... 140
Exercícios de fixação ....................................................................... 142
Gabarito ........................................................................................... 143
CAPÍTULO 10 - APURAÇÃO DE RESULTADOS ................................. 145
10.1 Criando os dados ................................................................... 145
10.2 Lucro presumido ................................................................... 145
10.3 Configurando uma planilha .................................................. 148
10.4 Demonstração de resultado ................................................... 150
10.5 Cálculo dos impostos ............................................................ 152
10.6 Proteger uma planilha ........................................................... 155
10.7 Lucro real............................................................................... 159
10.8 Análise da melhor forma de pagamento ................................ 161
Exercícios de fixação ....................................................................... 171
Gabarito ........................................................................................... 173
CAPÍTULO 11 - ESTOQUE ..................................................................... 175
11.1 Formas de apuração .............................................................. 175
11.2 Como fazer as avaliações ....................................................... 176
11.3 Registro de inventário ........................................................... 181
Exercícios de fixação ....................................................................... 183
Gabarito ........................................................................................... 185
CAPÍTULO 12 - FORMAÇÃO DE PREÇO ............................................ 187
12.1 Preço de venda ...................................................................... 188
12.2 Custo ..................................................................................... 191
12.3 Lucro ..................................................................................... 193
12.4 Apropriação dos custos ......................................................... 194
12.5 Plano de contas ...................................................................... 196
14 Mário SebaStião de azevedo Pereira
12.6 Levantamento do custo de produção .................................... 200
12.7 Levantamento dos custos fixos e variáveis ............................ 205
12.8 Formação do preço do produto ............................................. 206
12.9 Atingindo metas .................................................................... 208
Exercícios de fixação ....................................................................... 213
Gabarito ........................................................................................... 215
CAPÍTULO 13 - CUSTO DE PRODUÇÃO ........................................... 217
13.1 Inventário e sua importância ................................................. 219
13.2 Movimentação de estoque ..................................................... 221
13.3 Informação do custo .............................................................. 223
13.4 Movimentação contábil ......................................................... 226
13.5 Contabilização da venda ........................................................ 233
13.6 PIS e Cofins ........................................................................... 236
Exercícios de fixação ....................................................................... 243
Gabarito ........................................................................................... 245
CAPÍTULO 14 - FERRAMENTAS DE BUSCA ........................................ 247
14.1 SOMASE ................................................................................ 248
14.2 PROCV .................................................................................. 257
14.3 Subtotais ................................................................................ 260
Exercícios de fixação ....................................................................... 265
Gabarito ........................................................................................... 266
CAPÍTULO 15 - CONCILIAÇÃO ........................................................... 267
15.1 Conciliação ............................................................................ 267
15.2 Natureza das contas ............................................................... 268
15.3 Contas redutoras ................................................................... 269
15.4 Passivo ................................................................................... 272
15.5 Patrimônio líquido ................................................................ 273
15.6 Despesa .................................................................................. 273
15.7 Receita ................................................................................... 274
15.8 Conciliando ........................................................................... 276
Exercícios de fixação ....................................................................... 279
Gabarito ........................................................................................... 280
excel Para contadoreS 15
CAPÍTULO 16 - MACROS ...................................................................... 281
16.1 O que é uma macro ............................................................... 281
16.2 Criando uma macro ............................................................... 282
16.3 Executando a macro .............................................................. 285
16.4 Unindo as macros .................................................................. 287
16.5 Configurando a planilha ........................................................ 288
16.6 Filtrando os produtos ............................................................ 290
16.7 Acumulando valores .............................................................. 293
16.8 Imprimindo o relatório .......................................................... 296
Exercícios de fixação ....................................................................... 298
Gabarito ........................................................................................... 299
CAPÍTULO 17 - TABELA DINÂMICA .................................................... 301
17.1 Criando uma tabela dinâmica................................................ 301
17.2 Configurando uma tabela dinâmica ...................................... 305
17.3 Visualizando a tabela ............................................................. 309
17.4 Atualizando os dados da tabela ............................................. 311
17.5 Alterando dados na tabela ..................................................... 313
17.6 Criando um gráfico dinâmico ............................................... 316
Exercícios de fixação ....................................................................... 318
Gabarito ........................................................................................... 319
CAPÍTULO 18 - FORMULÁRIOS .......................................................... 321
18.1 Dados para análise ................................................................. 322
18.2 Criando um formulário ......................................................... 324
18.3 Formatando os dados do formulário ..................................... 326
18.4 Controle giratório .................................................................. 332
18.5 Calculando o frete ................................................................. 333
18.6 Barra de rolagem .................................................................... 337
18.7 Calculando os juros ............................................................... 338
18.8 Verificando o ICMS ............................................................... 342
Exercícios de fixação ....................................................................... 345
Gabarito ........................................................................................... 346
16 Mário SebaStião de azevedo Pereira
CAPÍTULO 19 - INTRODUÇÃO AO VBA ............................................ 347
CAPÍTULO 20 - IMPOSTOS E SUA CONTABILIZAÇÃO .................. 353
20.1 Conceito de tributo ............................................................... 354
20.1.1 Classificação e elementos do tributo ........................ 354
20.2 Imposto de Importação ......................................................... 356
20.3 Imposto de renda ................................................................... 361
20.3.1 Lucro Presumido ...................................................... 361
20.3.2 Lucro Real ................................................................ 364
20.3.2.1 Lucro real trimestral ................................. 366
20.3.2.2 Lucro real anual ........................................ 367
CAPÍTULO 21 - SIMULADOR DE NOTA FISCAL ............................... 371
21.1 Layout da Nota Fiscal ............................................................ 371
21.2 Banco de Dados ..................................................................... 374
21.2.1 Destinatário .............................................................. 374
21.2.2 Produto ..................................................................... 375
21.2.3 Transportador ........................................................... 377
21.2.4 CFOP ........................................................................ 378
21.3 Funcionamento do Simulador ............................................... 378
21.3.1 Preenchimento campo Destinatário ......................... 379
21.3.2 Preenchimento campo Transportador ...................... 380
21.3.3 Preenchimento Dados do Produto ........................... 381
21.3.4 Preenchimento Cálculo do Imposto ......................... 383
CAPÍTULO 22 - CENÁRIO TRIBUTÁRIO ............................................. 397
22.1 Impostos Não Cumulativos ................................................... 388
22.2 ICMS ...................................................................................... 392
22.3 ISS .......................................................................................... 396
22.4 Cenários no Excel .................................................................. 398
22.4.1 Testes de Hipóteses ................................................... 398
22.4.2 Cenário Original ....................................................... 400
22.4.3 Cenários Diversos ..................................................... 404
excel Para contadoreS 17
CAPÍTULO 23 - FORMULÁRIOS EM VBA ........................................... 413
23.1 Introdução ............................................................................. 413
23.2 Cadastro de empresa ............................................................. 414
23.2.1 Alterar o nome do relatório ...................................... 417
23.2.2 Adicionando Campos no Formulário ....................... 419
23.2.3 Adicionando Caixa de Texto .................................... 421
23.3.4 Configurando os Textos ........................................... 423
23.2.5 Inserindo Caixa de Combinação e Botões ................ 426
23.2.6 Configurando os Botões ........................................... 428
23.2.7 Renomeando os Campos .......................................... 429
23.2.8 Programando o Formulário ...................................... 432
23.2.9 Preparando para Gravar ........................................... 437
23.2.10 Botão Salvar .............................................................. 438
23.2.11 Localizando Linhas sem Preenchimentos ................ 442
23.2.12 Escolhendo o Local para Gravação .......................... 443
23.2.13 Limpando o Formulário e Posicionando o Cursor ... 446
23.2.14 Caixa de Combinação ............................................... 447
23.2.15 Botão Fechar ............................................................. 452
23.2.16 Caixa Localizar ......................................................... 453
23.2.17 Preenchimento do Formulário - Caixa Localizar ..... 457
23.2.18 Botão Excluir ............................................................ 462
23.2.19 Impressão do Relatório ............................................. 465
23.3 Dicas e Sugestões ................................................................... 468
Cap í tU lO 1
conceitos Básicos do excel
No dia a dia corrido das empresas, algumas coisas passam sem serem percebidas, e entre elas estão os recursos oferecidos pelos softwares que temos na empresa. Um desses recursos é o Excel, que oferece facilidades enormes para a rotina administrativa e na maioria das vezes sua utilização não demonstra isso, nos parece que ele não pode colaborar tanto.
Uma situação muito comum é quando alguém se depara com algo produzido em Excel de uma forma mais avançada é dizer: “Nossa, o Excel faz isso? Eu nem imaginava que isso pudesse ser tão simples”, mas para chegarmos à utilização de um recurso mais avançado, não só do Excel, todavia, de qualquer sistema, é necessário conhecer tudo o que ele oferece. Isso inclui desde os passos mais básicos até chegar a situações mais complexas. Como saberemos se o que estamos vendo na célula é um valor digitado ou resultado de uma fórmula? Se eu precisar alterar o texto na caixa de nome, para onde vou? Essas são perguntas comuns para as pessoas que não têm muito contato com o Excel, e que durante a obra serão respondidas.
Neste capítulo, trabalharemos os conceitos básicos do Excel e, gradualmente aumentaremos o nível de dificuldade. Utilizaremos a versão do Excel 2007, mas sempre tendo como referência a versão anterior, procurando explorar todos os recursos pertinentes aos tra-balhos que serão executados.
22 Mário SebaStião de azevedo Pereira
Antes de começarmos a utilizar o Excel, é de fundamental im-portância sabermos os nomes dos campos da planilha. Isso porque em qualquer tutorial que você for usar eles utilizarão os nomes dos campos para descrever as ações que você deve tomar e isso inclui o nosso curso.
Além da nomenclatura de cada campo, é importante saber qual a sua utilidade para a utilização do programa. O primeiro item que vamos tratar aqui é a Barra de Menu. Essa barra fica localizada na parte superior da planilha e por ela podemos acessar todos os recursos oferecidos pelo Excel. Quando escolhemos um menu, o programa abre uma lista que chamamos de submenu; normalmente, o primeiro item da barra chama-se Arquivo, por ele podemos abrir planilhas, imprimir, salvar os trabalhos entre outras tarefas, por meio dos submenus.
Abaixo da Barra de Menu, temos vários ícones num local cha-mado Barra de Ferramentas. Esses ícones não são nada mais do que atalhos da Barra de Menu, é um acesso mais rápido aos recursos ofe-recidos pelo Excel. Podemos personalizar essa barra clicando com o botão direito do mouse e escolhendo numa lista quais os atalhos que queremos disponíveis.
Antes do grupo de células, temos a Caixa de Nome. Essa caixa é onde fica o nome da célula; normalmente, vemos uma letra e um nú-mero, que indicam onde a célula se localiza na planilha, mas podemos renomear esse campo e assim teremos uma célula com o nome que acharmos mais adequado. Ao lado da caixa de nome, temos a Barra de fórmulas. Nela podemos ver o conteúdo da célula, porém, quando temos uma fórmula na célula, veremos a fórmula e visualizaremos na célula o resultado da operação.
Na região das células, temos a coluna que é representada por letras e as linhas representadas por números serão as coordenadas das células ou podemos chamar também de endereço da célula; esse endereço aparecerá na caixa de nome. Na parte inferior da planilha, temos algumas abas. Quando se abre uma planilha ela normalmente está com o nome de Plan1. Nessa aba, você determina o nome de
excel Para contadoreS 23
cada planilha dentro de uma pasta e, para renomear, basta que você de um clique duplo dentro da aba e determine o nome que achar mais adequado ao trabalho. Agora você já conhece os campos da planilha e pode efetuar os cálculos necessários.
Para executarmos os cálculos, necessitamos informar ao pro-grama o que queremos fazer. No Excel, essa informação vem por meio de um sinal de igual “=” dentro da célula em que queremos que apareça determinado resultado. Quando colocamos esse sinal na célula, o programa entende que depois dele será digitada uma ins-trução, e assim ele fará a operação desejada. Esse sinal e o conteúdo da célula aparecerão na barra de fórmula. Na figura abaixo, temos a representação do que é a célula, onde estão seu nome ou endereço e a barra de fórmula:
24 Mário SebaStião de azevedo Pereira
O Excel pode efetuar praticamente qualquer tipo de cálculo matemático, desde cálculos simples, como veremos a seguir, passando pelos cálculos financeiros, até os mais complexos, como os cálculos de trigonometria.
Para que o Excel execute nossos comandos com perfeição, precisamos informar os parâmetros corretos para isso. O Excel além dos cálculos matemáticos nos permite apenas digitar valores em suas células, criarmos tabelas, textos, enfim, são muitas as funções que podemos utilizar nas planilhas Excel, embora os cálculos sejam a finalidade maior desse programa.
Podemos tanto digitar a operação completa dentro da célula, por exemplo, somar dois valores e o resultado já aparecerá, como podemos usar a célula para executar uma operação utilizando valores contidos em células distintas e ter o resultado nessa célula específi-ca. Todas as fórmulas contidas nas células poderão ser visualizadas na barra de fórmula, sempre que selecionada a célula, onde podem ser alteradas, caso seja verificado algum erro na fórmula ou alguma alteração para atualizar um resultado ou testar outro. Podemos de-senvolver tanto fórmulas simples como uma soma de dois valores contidos em células diferentes, como também criar fórmulas mais complexas.
Vale dizer que na construção das fórmulas muitas vezes devemos utilizar os parênteses assim como nas expressões matemáticas que aprendemos a resolver conforme suas regras específicas e o Excel segue a mesma ordem na solução da expressão digitada, portanto, para um resultado correto, devemos sempre ter atenção na hora de elaborarmos cada uma das fórmulas.
Os símbolos que indicam as operações matemáticas no Excel são os seguintes: “+” adição, “-” subtração, “*” multiplicação e “/” divisão. Esses símbolos são os mais usados; porém, existem muitos símbolos e fórmulas a serem utilizados no Excel, que serão explorados aos poucos. Para um melhor acompanhamento do funcionamento do Excel, criaremos uma planilha e desenvolveremos nela todas as funções
excel Para contadoreS 25
necessárias para os profissionais das áreas financeiras e contábil. Esses procedimentos serão seguidos passo a passo.
Primeiro abra o Excel e salve uma planilha com o nome TESTE. Para isso, abra o programa e clique na opção Arquivo/Salvar. Pronto, temos uma planilha na qual desenvolveremos todo o nosso trabalho. Para nos habituarmos ao uso do Excel, vamos demonstrar algumas contas simples. As células são identificadas com coordenadas de linha e coluna; as linhas são sempre representadas por números e as colu-nas por letras. Para saber em que linha e coluna você está, observe o campo da planilha que recebe o nome de: Nome da Célula, como já foi visto anteriormente.
Agora vá à célula A1 e peça para o Excel calcular 10 + 5. Para isso, digite primeiro o sinal de “=” para informar que ali terá uma fórmula e, depois, digite os valores e a operação. Observe no exemplo abaixo:
Sempre que executar uma operação ou alteração, você deve salvar o trabalho efetuado para não perder suas informações, ou atualizações, caso ocorra algum problema no seu computador. Para salvar, você deve seguir alguns desses caminhos: clique no menu Arquivo, depois no submenu Salvar, ou então, no ícone de salvar na barra de ferramentas que é representada pela imagem de um disquete, e tem ainda a opção pelo teclado pressionando a tecla Ctrl+B.
Vamos ver agora outras operações matemáticas resolvidas pelo Excel: na coluna A, temos o resultado da operação, na coluna B, as
