excel avanc aula5

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

Aula5/aula5_avanc1.xls
Posição de Vendas
		Indústria Química - Posição de Vendas 2004
		
		Código		Cliente		Cidade		Vendedor		Data		Produto		Qtde		Valor		Valor
Total		Comissão		Lucro
		001		Copebrás		Belém do Pará		Maurício Biondi		5-Jun		A		32		R$130.73		R$4,184.00		R$292.88		R$965.38
		001		Copebrás		Belém do Pará		Maurício Biondi		12-Apr		B		15		R$161.64		R$2,425.00		R$169.75		R$559.51
						Belém do Pará Total										R$292.36		R$6,609.00		R$462.63		R$1,524.88
		002		3M		Campinas		Renato Pires		23-Mar		D		250		R$73.34		R$18,334.00		R$733.36		R$4,230.90
						Campinas Total										R$73.34		R$18,334.00		R$733.36		R$4,230.90
		003		Ultrafértil		Cuiabá		Edson Xavier		4-Feb		C		46		R$102.17		R$4,700.00		R$235.00		R$1,084.53
						Cuiabá Total										R$102.17		R$4,700.00		R$235.00		R$1,084.53
		004		Refap		Porto Alegre		Flávio Chiara		1-Jun		F		78		R$156.73		R$12,225.00		R$489.00		R$2,821.06
		005		Confesul		Porto Alegre		Flávio Chiara		20-Jan		A		99		R$130.73		R$12,943.00		R$517.72		R$2,986.63
						Porto Alegre Total										R$287.45		R$25,168.00		R$1,006.72		R$5,807.69
		006		Petrobrás		Rio de Janeiro		Renato Pires		15-Jul		C		10		R$102.17		R$1,022.00		R$40.88		R$235.77
						Rio de Janeiro Total										R$102.17		R$1,022.00		R$40.88		R$235.77
		007		Detem Química		Salvador		Diogo Mainardes		30-Oct		J		28		R$116.39		R$3,259.00		R$162.95		R$752.07
		008		Politeno		Salvador		Diogo Mainardes		5-Sep		A		34		R$130.73		R$4,445.00		R$222.25		R$1,025.71
		009		OPP		Salvador		Diogo Mainardes		18-Jun		E		56		R$41.83		R$2,343.00		R$117.15		R$540.59
		009		OPP		Salvador		Diogo Mainardes		19-Mar		B		20		R$161.64		R$3,233.00		R$161.65		R$746.01
		007		Detem Química		Salvador		Diogo Mainardes		24-Feb		D		35		R$73.34		R$2,567.00		R$128.35		R$592.33
						Salvador Total										R$523.92		R$15,847.00		R$792.35		R$3,656.71
		010		Carbocloro		São Paulo		Renato Pires		13-Jan		I		21		R$15.75		R$331.00		R$13.24		R$76.31
		011		Indupa		São Paulo		Renato Pires		30-Jun		H		10		R$58.63		R$587.00		R$23.48		R$135.30
						São Paulo Total										R$74.38		R$918.00		R$36.72		R$211.61
						Total geral										R$1,455.78		R$72,598.00		R$3,307.66		R$16,752.08
		
														Total		R$2,837.19		R$144,278.00		R$6,578.60		R$33,292.56
		
		
														% Lucro no Total:		23%
														% Variável:		14%
														Lucro Ideal:		R$20,199
Coluna
Digitada.
De acordo com o código à esquerda, buscar o nome do Cliente.
De acordo com o código à esquerda, buscar a cidade do Cliente.
Utilizando-se da cidade do Cliente, buscar o nome do vendedor.
As coluna Data, Produto e Qtde são digitadas.
Pelo nome do Produto, buscar seu preço de venda.
Preço multiplicado pela Qtde. Arredondar o valor para cima.
Para calcular a comissão, buscar a % referente ao vendedor e multiplicá-la pelo Valor Total.
Preço de Venda subtraído pelo Preço de Custo multiplicado pela Qtde Vendida.
% que o lucro vale em relação ao Total.
Nesta célula aplicar o recurso CENÁRIO, onde indicará o valor do lucro para um período otimista,em que a % valerá 35% e o valor para um período pessimista, valendo 14%.
Lucro multiplicado pela % acima.
AO FINAL CRIAR:
SUBTOTAIS DE ACORDO COM A CIDADE;
UMA TABELA DINÂMICA E UM GRÁFICO DINÂMICO, SENDO EM LINHA COLOCAR O PRODUTO, EM DADOS, O VALOR, COLUNA O VENDEDOR E EM PÁGINA, A CIDADE.
Clientes
		
				Clientes										Vendedores
		
		
				Código		Cliente		Cidade		Região				Código		Região		Vendedores		Comissão
				001		Copebrás		Belém do Pará		Norte				001		Centro-Oeste		Edson Xavier		5%
				002		3M		Campinas		Sudeste				002		Nordeste		Diogo Mainardes		5%
				003		Ultrafértil		Cuiabá		Centro-Oeste				003		Norte		Maurício Biondi		7%
				004		Refap		Porto Alegre		Sul				004		Sudeste		Renato Pires		4%
				005		Confesul		Porto Alegre		Sul				005		Sul		Flávio Chiara		4%
				006		Petrobrás		Rio de Janeiro		Sudeste
				007		Detem Química		Salvador		Nordeste
				008		Politeno		Salvador		Nordeste
				009		OPP		Salvador		Nordeste
				010		Carbocloro		São Paulo		Sudeste
				011		Indupa		São Paulo		Sudeste
		
		
				Produtos
		
		
				Código		Produto		Valor
Unitário		Preço de
Venda		Qtde
Estoque Out/04
				0001		A		R$100.560		R$130.73		32
				0002		B		R$124.335		R$161.64		15
				0003		C		R$78.589		R$102.17		250
				0004		D		R$56.412		R$73.34		46
				0005		E		R$32.178		R$41.83		78
				0006		F		R$120.558		R$156.73		99
				0007		G		R$62.359		R$81.07		10
				0008		H		R$45.100		R$58.63		28
				0009		I		R$12.112		R$15.75		34
				0010		J		R$89.532		R$116.39		56
Todas tabelas digitadas, exceto o Preço de Venda do Produto que deverá ser o valor arredondado para cima do Preço Unitário acrescido de 30%.
DETALHE: cada uma deverá estar em uma planilha.
Consulta
		5.0
		4.0
		1.0
		
		
		
		
		
				Cidade		Região		Data da
Última Compra		Total Qtde
Comprada		Total
Gasto
				Porto Alegre		Sul		20-Jan-04		99		R$12,943.00
		
		
		
		
		
		
				Valor
Unitário		Preço
Venda		Qtde
Estoque		Qtde Vendida
Jan/Out		Lucro
Obtido Jan/Out
				R$56.41		R$73.34		46		285		R$4,823.23
		
		
		
		
		
		
				Região		% Comissão		Ganho
Jan/Out		Qtde Vendida
Jan/Out
				Centro-Oeste		0.05		R$235.00		46
Cliente
Produto
Vendedor
De acordo com o Cliente escolhido, exibir os dados referentes. Nos Totais, utilizar SomaSe.
De acordo com o Produto escolhido, exibir os dados referentes. Nos Totais, utilizar SomaSe.
De acordo com o Vendedor escolhido, exibir os dados referentes. Nos Totais, utilizar SomaSe.
Pedido
		
		
		
				2.0
		
		
						Cliente:								Produto:
		
						Código		Região		Cidade				Código		Preço Unitário		Preço de Venda		Qtde
						5		Sul		Porto Alegre				7		R$62.36		R$81.07		10
		
		
		
		
														Qtde Pedida:		10
						Vendedor:		Flávio Chiara						Valor Final:		R$810.67
		
		
		
														% Juros:		5%		Valor:						0.3
																		R$33.99
Cliente
Produto
Vendedor
Quantidade Pedida
Financiamento
12x
24x
A Vista
De acordo com o Cliente escolhido, exibir seus dados.
De acordo com o Produto escolhido, exibir os dados referentes.
Exibir o Vendedor referente a Região do Cliente.
Informar a Qtde pedida, levando em consideração a Qtde em Estoque. Exibir mensagem de erro caso não haja qtde disponível.
O Valor Final é o preço multiplicado pela Qtde (Levar em consideração se há alguma Contra Proposta).
Exibir o Valor do parcelamento de acordo com o plano escolhido e a taxa de juro informada.
Aula5/aula5_avanc2.xls
Tabela Dinâmica
		Responsável		(Tudo)
		
		Soma de Qtde em
Estoque		Categoria
		Produto		Biscoitos e Doces		Descartáveis		Dietéticos		Massas		Total geral
		Achocolatado pacote 500g		14								14
		Adoçante Silver extra grande						9				9
		Balas sabor Morango pacote 200g		6								6
		Biscoitos Framboesa		10								10
		Biscoitos Sequilho pacote 150g		23								23
		Biscoitos Violeta sabor Coco		18								18
		Biscoitos Violeta sabor Laranja		5								5
		Copos para cafezinho 50 ml transparentes				13						13
		Copos Plásticos transparentes 200 ml				4						4
		Leite em Pó pacote 400g		9								9
		Pão de Forma Light								7		7
		Total geral		85		17		9		7		118
Gráfico Dinâmico
		14		Achocolatado pacote 500g		Achocolatado pacote 500g		Achocolatado pacote 500g		Achocolatado pacote 500g
		Adoçante Silver extra grande		Adoçante Silver extra grande		9		Adoçante Silver extra grande		Adoçante Silver extra grande
		6		Balas sabor Morango pacote 200g		Balas sabor Morango pacote 200g		Balas sabor Morango pacote 200g		Balas sabor Morango pacote 200g
		10		Biscoitos Framboesa		Biscoitos Framboesa
Biscoitos Framboesa		Biscoitos Framboesa
		23		Biscoitos Sequilho pacote 150g		Biscoitos Sequilho pacote 150g		Biscoitos Sequilho pacote 150g		Biscoitos Sequilho pacote 150g
		18		Biscoitos Violeta sabor Coco		Biscoitos Violeta sabor Coco		Biscoitos Violeta sabor Coco		Biscoitos Violeta sabor Coco
		5		Biscoitos Violeta sabor Laranja		Biscoitos Violeta sabor Laranja		Biscoitos Violeta sabor Laranja		Biscoitos Violeta sabor Laranja
		Copos para cafezinho 50 ml transparentes		13		Copos para cafezinho 50 ml transparentes		Copos para cafezinho 50 ml transparentes		Copos para cafezinho 50 ml transparentes
		Copos Plásticos transparentes 200 ml		4		Copos Plásticos transparentes 200 ml		Copos Plásticos transparentes 200 ml		Copos Plásticos transparentes 200 ml
		Creme de Leite lata 365g		Creme de Leite lata 365g		Creme de Leite lata 365g		18		Creme de Leite lata 365g
		Leite Condensado lata 300g		Leite Condensado lata 300g		Leite Condensado lata 300g		10		Leite Condensado lata 300g
		9		Leite em Pó pacote 400g		Leite em Pó pacote 400g		Leite em Pó pacote 400g		Leite em Pó pacote 400g
		Pão de Forma Light		Pão de Forma Light		Pão de Forma Light		Pão de Forma Light		7
Biscoitos e Doces
Descartáveis
Dietéticos
Enlatados
Massas
Plan1
		
		
		Soma de Qtde em
Estoque		Categoria
		Produto		Biscoitos e Doces		Descartáveis		Dietéticos		Enlatados		Massas		Total geral
		Achocolatado pacote 500g		14										14
		Adoçante Silver extra grande						9						9
		Balas sabor Morango pacote 200g		6										6
		Biscoitos Framboesa		10										10
		Biscoitos Sequilho pacote 150g		23										23
		Biscoitos Violeta sabor Coco		18										18
		Biscoitos Violeta sabor Laranja		5										5
		Copos para cafezinho 50 ml transparentes				13								13
		Copos Plásticos transparentes 200 ml				4								4
		Creme de Leite lata 365g								18				18
		Leite Condensado lata 300g								10				10
		Leite em Pó pacote 400g		9										9
		Pão de Forma Light										7		7
		Total geral		85		17		9		28		7		146
Produtos
		Lista de Produtos
		
		Código		Produto		Categoria		Responsável		Preço		Qtde em
Estoque		Total em
Estoque		Qtde
Vendida		Preço de
Venda		Total
Vendido		Situação		Lucro
		001		Biscoitos Violeta sabor Laranja		Biscoitos e Doces		Maria Clara		* 1.20		5		* 6.00		2		* 1.80		* 3.60		Vendas Ok		* 1.20
		002		Biscoitos Framboesa		Biscoitos e Doces		Maria Clara		* 1.00		10		* 10.00		8		* 1.50		* 12.00		Vendas Ok		* 4.00
		003		Biscoitos Sequilho pacote 150g		Biscoitos e Doces		Maria Clara		* 1.35		23		* 31.05		13		* 2.03		* 26.33		Vendas Ok		* 8.78
		004		Copos Plásticos transparentes 200 ml		Descartáveis		Eric Mendes		* 3.45		4		* 13.80		1		* 5.18		* 5.18		Aumentar as Vendas		* 1.73
		005		Balas sabor Morango pacote 200g		Biscoitos e Doces		Maria Clara		* 2.89		6		* 17.34		5		* 4.34		* 21.68		Vendas Ok		* 7.23
		006		Biscoitos Violeta sabor Coco		Biscoitos e Doces		Maria Clara		* 0.98		18		* 17.64		10		* 1.47		* 14.70		Vendas Ok		* 4.90
		007		Pão de Forma Light		Massas		Afonso Cristiano		* 2.99		7		* 20.93		6		* 4.49		* 26.91		Vendas Ok		* 8.97
		008		Adoçante Silver extra grande		Dietéticos		Renata Garcia		* 3.10		9		* 27.90		7		* 4.65		* 32.55		Vendas Ok		* 10.85
		009		Refrigerante sabor Limão 2L		Bebidas		Luís Silva		* 1.87		24		* 44.88		14		* 2.81		* 39.27		Vendas Ok		* 13.09
		010		Copos para cafezinho 50 ml transparentes		Descartáveis		Eric Mendes		* 1.56		13		* 20.28		9		* 2.34		* 21.06		Vendas Ok		* 7.02
		011		Suco em pó Suks sabor maçã		Bebidas		Luís Silva		* 0.52		26		* 13.52		20		* 0.78		* 15.60		Vendas Ok		* 5.20
		012		Suco em caixa sabor Uva 1L		Bebidas		Luís Silva		* 3.65		5		* 18.25		2		* 5.48		* 10.95		Vendas Ok		* 3.65
		013		Leite Condensado lata 300g		Enlatados		Marcos Oliveira		* 1.70		10		* 17.00		5		* 2.55		* 12.75		Vendas Ok		* 4.25
		014		Creme de Leite lata 365g		Enlatados		Marcos Oliveira		* 1.54		18		* 27.72		13		* 2.31		* 30.03		Vendas Ok		* 10.01
		015		Achocolatado pacote 500g		Biscoitos e Doces		Maria Clara		* 2.36		14		* 33.04		8		* 3.54		* 28.32		Vendas Ok		* 9.44
		016		Leite em Pó pacote 400g		Biscoitos e Doces		Maria Clara		* 3.51		9		* 31.59		2		* 5.27		* 10.53		Aumentar as Vendas		* 3.51
		
								Totais		* 33.67		* 201.00		* 350.94		* 125.00		* 50.50		* 311.45
								Média		3		13		22		8		4		20
		
		
				Responsáveis
				Bebidas		Luís Silva														0.5
				Biscoitos e Doces		Maria Clara
				Descartáveis		Eric Mendes
				Dietéticos		Renata Garcia
				Enlatados		Marcos Oliveira
				Massas		Afonso Cristiano
Criar cenários:
Otimista: Preço de Venda será acréscimo de 80% ao Preço de Custo.
Pessimista: 20%
Colocar Subtotais de acordo com o Nome do Responsável.
Criar Tabela e Gráficos Dinâmicos.
Utilizar uma função de procura para exibir o nome do responsável.
Preço multiplicado pela quantidade.
Em alguma célula digitar 50%. O preço de venda deverá ser o Preço de custo acrescido desta %.
Preço de Venda multiplicado pela quantidade vendida.
Se o total vendido for inferior a 50% do total em estoque exibir "Aumentar as Vendas", senão, "Vendas ok".
Preço de Venda subtraído pelo Preço de Custo multiplicado pela Qtde Vendida.
Subtotal para que os dados sejam atualizados com o filtro.
Datas
		FERIADOS NACIONAIS 2004
		
		Data		Descrição				Data Inicial		Data Final		Dias		Fórmula Usada
		1-Jan-04		Confraternização Universal				8-May-04		19-Sep-04		93		Dia Trabalho Total
		23-Feb-04		Carnaval				3-Apr-04		23-Nov-04		4-Jun-04		Dia Trabalho
		24-Feb-04		Carnaval				12-Sep-04		2-Nov-04		51		Data Dif
		9-Apr-04		Paixão Cristo				4-Mar-04		27-Oct-04		30-Jun-04		Fim Mês
		21-Apr-04		Tiradentes
		1-May-04		Dia do Trabalho
		10-Jun-04		Corpus Christi
		7-Sep-04		Independência do Brasil
		12-Oct-04		N. S. Aparecida
		2-Nov-04		Finados
		15-Nov-04		Procl. República
		25-Dec-04		Natal
Calcula a Qtde de Dias a partir de uma data inicial e uma final, excluindo os feriados.
Retorna uma data a partir de uma Qtde de dias úteis, descontando feriados.
Calcula Qtde de Dias, Meses ou anos sem descontar feriados.
Retornar data final a partir de uma data inicial e uma Qtde de meses.
Aula5/aula5_avanc3.xls
Importação de Dados
		Câmbio – dólar EUA (em R$)
		Data		Compra		Venda
		05/09 (PTAX)		1.7297		1.7305
		05/09–17:39		1.7297		1.7305
IMPORTAÇÃO DE DADOS DA WEB
Com seu computador conectado à internet, vá em Menu Dados => Importar Dados Externos => Nova Consulta à Web.
No endereço coloque:
www.bcb.gov.br
Algumas setas amarelas aparecerão no site do Banco Central. Clique na seta que está na tabela com as taxas do dólar e depois no botão Importar. Os dados virão à planilha. Para atualizá-los, basta depois clicar no símbolo ! da barra que aparecerá.
Qualquer tabela de qualquer site que tenha as setas amarelas, pode ser trazida ao Excel.
Se possuir dados em Banco de Dados (Access, por exemplo, você pode utilizar as demais opções que estão em Menu Dados => Importar Dados Externos .