Planilhas eletrônicas no Excel sobre lucratividade na venda de produtos

É sempre bom pode ajudar alguém. No final de dezembro passado, em um dos grupos que participo sobre planilhas no Excel e que possui questões e conteúdos matemáticos ensinados através do Excel, percebi a postagem de um pedido de auxílio com uma planilha eletrônica confeccionada no Excel referente à lucratividade sobre produtos vendidos durante o mês. Não me concentrei na explicação (é preciso imaginar a organização da planilha ou reconstruir a parte explicada, o que pode ser um resultado muito diferente do original) e fui logo pedindo que o membro do grupo me encaminhasse a planilha para que pudesse ser visualizada e que ele me repassasse o que desejava.

Foram quase duas horas de conversa sobre duas planilhas parecidas em que auxiliei. As planilhas são simples e referem-se basicamente à lucratividade, porcentagem e controle de compra e venda; resolvi por reproduzir as planilhas preservando a base das fórmulas e funcionalidades e disponibilizá-las aqui no blog.

Foi bom poder conhecer, mesmo que brevemente, o Márcio e poder ajudá-lo. Este é mais um exemplo da matemática na prática, nem tanto das planilhas, mas do uso da matemática. As dúvidas dele eram simples e ele mesmo constatou tal fato, sendo mais um entendimento necessário sobre aplicação da matemática que da linguagem empregada na codificação da planilha.

Parte da conversa está reproduzida na postagem com autorização do Márcio (membro que pediu auxílio). Esta reprodução é tida para que se registre o fato e que seja percebido um algo relevante quanto a aprendizagem e a aplicação de conhecimentos básicos sobre a matemática.

Características das planilhas no Excel

Originalmente as duas planilhas se encontravam em arquivos separados, mas após a edição para o blog elas foram agrupadas em um único arquivo.

Estas planilhas podem ser utilizadas em uma loja ou mercado, ambos de pequeno porte. Elas contribuem para melhor controle de entrada e saída de capital, permitindo ainda tomada de decisões como:

• colocar produtos em promoção;
• alterar a lucratividade de determinado produto, por conta de diversas variáveis: inflação, prejuízo, aumento do valor do produto recebido, etc.;
• observar quais despesas podem ser reduzidas ou cortadas;
• verificar qual a efetiva lucratividade e optar por polpar ou investir no negócio;

As planilhas não foram preparadas para o caso da compra ser realizada a prazo, por exemplo com cheque ou no crediário, em que poderia haver uma coluna indicando acréscimo no valor "à vista" em %. Elas podiam ainda estarem relacionadas a outras planilhas como uma planilha de controle de estoque (entrada e saída de mercadoria/produto).

Planilha 1 - CONT. MERCAD. E FATURAMENTO
As colunas que aparecem em amarelo são automática, ou seja elas foram configuradas para exibirem resultados a partir dos dados adicionados nas colunas em branco.

As colunas referentes ao cartão de crédito fazem registro mas não entram nos cálculos de faturamento e de lucro.

O auxílio realizado nesta planilha foi quanto as colunas "LUCRO %" e "À VISTA". Antes a planilha estava configurada para o usuário adicionar a porcentagem de lucro e a coluna "À VISTA" era preenchida automaticamente, o pedido foi que isso se invertesse, ou seja, que o usuário adicionasse o valor do produto à vista e a porcentagem de lucro fosse preenchida automaticamente. E isso foi feito apagando a fórmula da coluna "À VISTA" e na coluna "LUCRO %" adicionando a linha de código:

=SEERRO(SE(I6="";"";I6/G6 - 100%);"")

Interpretando a linha temos que o resultado é dado pela diferença entre o preço à vista dividido pela soma do preço do produto com o frete e a porcentagem inicial (100%).

Em fórmula matemática:$\cfrac {x }{ y +z} %\quad -\quad 100%$.

Colunas: $x$ = À VISTA, $y$ = PREÇO UND. e $z$ = FRETE.

Planilha 2 - COMP. REV. PROD. WEB
Novamente, as colunas que aparecem em amarelo são automáticas.

Esta planilha foi originalmente elaborada para compras e revenda de produtos disponíveis em lojas na web.

Nesta planilha o auxílio foi em adicionar código para a coluna "LUCRO %", que o Márcio disse que já havia tentado de vários modos e nunca saía como ele fazia na calculadora. Pelo que entendi, ele fazia o cálculo da calculadora corretamente, mas ao passar para a planilha não estava utilizando as colunas corretas e por isso o resultado não era o esperado. Nesta coluna o código é ainda mais simples, pois basta dividir o ganho pelo custo do produto:

=SEERRO(SE(E6="";"";I6/(E6+F6+G6));"")

Em fórmula matemática ficaria: $\cfrac { k }{ w + z + p } $.

Colunas: $k$ = GANHO, $w$ = PRODUTO, $z$ = FRETE e $p$ = OUTROS.

Foram realizadas algumas outras modificações nas planilhas para disponibilizá-las para download, como formatação condicional, tratamento de erro, formatação visual, congelamento de painel.

Download

As planilhas estão disponíveis para download nos dois servidores abaixo. Elas estão protegidas com a sequência de números de 1 a 6 como senha.

• Arquivos originados em Microsoft Excel 2010.

Google Drive

Box