Planilha Trabalho individual ADMINISTRAÇÃO UNOPAR 5 E 6 SEMESTRE

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

planilha para atividade individ
	
	1o Passo - Elaborar a DRE e o Fluxo de Caixa
	
		1	2	3	4	5	6	7	8	9	10	11	12	Total
	Preço de Venda	* 6.00	* 6.00	* 6.00	* 6.00	* 6.00	* 6.00	* 6.00	* 6.00	* 6.00	* 6.00	* 6.00	* 6.00	* 72.00
	Quantidade	20,000	20,000	20,000	23,200	23,200	23,200	30,600	30,600	30,600	30,600	30,600	30,600	313200
	Receita Bruta	* 120,000.00	* 120,000.00	* 120,000.00	* 139,200.00	* 139,200.00	* 139,200.00	* 183,600.00	* 183,600.00	* 183,600.00	* 183,600.00	* 183,600.00	* 183,600.00	* 1,879,200.00
	ICMS	* 21,600.00	* 21,600.00	* 21,600.00	* 25,056.00	* 25,056.00	* 25,056.00	* 33,048.00	* 33,048.00	* 33,048.00	* 33,048.00	* 33,048.00	* 33,048.00	* 338,256.00
	PIS	* 1,980.00	* 1,980.00	* 1,980.00	* 2,296.80	* 2,296.80	* 2,296.80	* 3,029.40	* 3,029.40	* 3,029.40	* 3,029.40	* 3,029.40	* 3,029.40	* 31,006.80
	COFINS	* 9,120.00	* 9,120.00	* 9,120.00	* 10,579.20	* 10,579.20	* 10,579.20	* 13,953.60	* 13,953.60	* 13,953.60	* 13,953.60	* 13,953.60	* 13,953.60	* 142,819.20
	Comissão	* 2,400.00	* 2,400.00	* 2,400.00	* 2,784.00	* 2,784.00	* 2,784.00	* 3,672.00	* 3,672.00	* 3,672.00	* 3,672.00	* 3,672.00	* 3,672.00	* 37,584.00
	Receita Líquida	* 84,900.00	* 84,900.00	* 84,900.00	* 98,484.00	* 98,484.00	* 98,484.00	* 129,897.00	* 129,897.00	* 129,897.00	* 129,897.00	* 129,897.00	* 129,897.00	* 1,329,534.00
	CPV	* 63,000.00	* 63,000.00	* 63,000.00	* 73,080.00	* 73,080.00	* 73,080.00	* 96,390.00	* 96,390.00	* 96,390.00	* 96,390.00	* 96,390.00	* 96,390.00	* 986,580.00
	Lucro Bruto	* 21,900.00	* 21,900.00	* 21,900.00	* 25,404.00	* 25,404.00	* 25,404.00	* 33,507.00	* 33,507.00	* 33,507.00	* 33,507.00	* 33,507.00	* 33,507.00	* 342,954.00
	água - luz - telefone	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	25200
	prolabore	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	66000
	salário + encargos	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	100200
	transporte	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	21600
	marketing	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	12000
	depreciação	* 2,200.00	* 2,200.00	* 2,200.00	* 2,200.00	* 2,200.00	* 2,200.00	* 2,200.00	* 2,200.00	* 2,200.00	* 2,200.00	* 2,200.00	* 2,200.00	26400
	Lucro Operacional	* 950.00	* 950.00	* 950.00	* 4,454.00	* 4,454.00	* 4,454.00	* 12,557.00	* 12,557.00	* 12,557.00	* 12,557.00	* 12,557.00	* 12,557.00	* 91,554.00
	IR	* 142.50	* 142.50	* 142.50	* 668.10	* 668.10	* 668.10	* 1,883.55	* 1,883.55	* 1,883.55	* 1,883.55	* 1,883.55	* 1,883.55	* 13,733.10
	CS	* 85.50	* 85.50	* 85.50	* 400.86	* 400.86	* 400.86	* 1,130.13	* 1,130.13	* 1,130.13	* 1,130.13	* 1,130.13	* 1,130.13	* 8,239.86
	Lucro Líquido	* 722.00	* 722.00	* 722.00	* 3,385.04	* 3,385.04	* 3,385.04	* 9,543.32	* 9,543.32	* 9,543.32	* 9,543.32	* 9,543.32	* 9,543.32	* 69,581.04
	
	
	Contas a Receber	Valor	1	2	3	4	5	6	7	8	9	10	11	12
	1	* 120,000.00	* 40,000.00	* 40,000.00	* 40,000.00
	2	* 120,000.00		* 40,000.00	* 40,000.00	* 40,000.00
	3	* 120,000.00			* 40,000.00	* 40,000.00	* 40,000.00
	4	* 139,200.00				* 46,400.00	* 46,400.00	* 46,400.00
	5	* 139,200.00					* 46,400.00	* 46,400.00	* 46,400.00
	6	* 139,200.00						* 46,400.00	* 46,400.00	* 46,400.00
	7	* 183,600.00							* 61,200.00	* 61,200.00	* 61,200.00
	8	* 183,600.00								* 61,200.00	* 61,200.00	* 61,200.00
	9	* 183,600.00									* 61,200.00	* 61,200.00	* 61,200.00
	10	* 183,600.00										* 61,200.00	* 61,200.00	* 61,200.00
	11	* 183,600.00											* 61,200.00	* 61,200.00
	12	* 183,600.00												* 61,200.00
	Total		* 40,000.00	* 80,000.00	* 120,000.00	* 126,400.00	* 132,800.00	* 139,200.00	* 154,000.00	* 168,800.00	* 183,600.00	* 183,600.00	* 183,600.00	* 183,600.00
	
	
	Fornecedores	Valor	1	2	3	4	5	6	7	8	9	10	11	12
	1	* 63,000.00	* 31,500.00	* 31,500.00
	2	* 63,000.00		* 31,500.00	* 31,500.00
	3	* 63,000.00			* 31,500.00	* 31,500.00
	4	* 73,080.00				* 36,540.00	* 36,540.00
	5	* 73,080.00					* 36,540.00	* 36,540.00
	6	* 73,080.00						* 36,540.00	* 36,540.00
	7	* 96,390.00							* 48,195.00	* 48,195.00
	8	* 96,390.00								* 48,195.00	* 48,195.00
	9	* 96,390.00									* 48,195.00	* 48,195.00
	10	* 96,390.00										* 48,195.00	* 48,195.00
	11	* 96,390.00											* 48,195.00	* 48,195.00
	12	* 96,390.00												* 48,195.00
	Total		* 31,500.00	* 63,000.00	* 63,000.00	* 68,040.00	* 73,080.00	* 73,080.00	* 84,735.00	* 96,390.00	* 96,390.00	* 96,390.00	* 96,390.00	* 96,390.00
	
		1	2	3	4	5	6	7	8	9	10	11	12
	Saldo Inicial	0.0	* 205,700.00	* 219,900.00	* 222,822.00	* 227,104.00	* 226,289.04	* 23,187,408.00	* 2,319,613,804.00	* 2,319,610,747.32	* 2,319,622,490.64	* 2,319,634,233.96	* 2,319,645,977.28
	Entradas com vendas	* 40,000.00	* 80,000.00	* 120,000.00	* 126,400.00	* 132,800.00	* 139,200.00	* 154,000.00	* 168,800.00	* 183,600.00	* 183,600.00	* 183,600.00	* 183,600.00
	Capital Integralizado	* 200,000.00
	Saldo em Caixa	* 240,000.00	* 285,700.00	* 339,900.00	* 349,222.00	* 359,904.00	* 365,489.04	* 23,341,408.00	* 2,319,782,604.00	* 2,319,794,347.32	* 2,319,806,090.64	* 2,319,817,833.96	* 2,319,829,577.28
	Fornecedores - CPV	* 31,500.00	* 63,000.00	* 63,000.00	* 68,040.00	* 73,080.00	* 73,080.00	* 84,735.00	* 96,390.00	* 96,390.00	* 96,390.00	* 96,390.00	* 96,390.00
	água - luz - telefone		* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00	* 2,100.00
	prolabore		* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00	* 5,500.00
	salário + encargos		* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00	* 8,350.00
	transporte	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00	* 1,800.00
	marketing	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00	* 1,000.00
	IRPJ		* 142.50	* 142.50	* 142.50	* 668.10	* 668.10	* 668.10	* 1,883.55	* 1,883.55	* 1,883.55	* 1,883.55	* 1,883.55
	CSLL		* 85.50	* 85.50	* 85.50	* 400.86	* 400.86	* 400.86	* 1,130.13	* 1,130.13	* 1,130.13	* 1,130.13	* 1,130.13
	Comissão		* 2,400.00	* 2,400.00	* 2,400.00	* 2,784.00	* 2,784.00	* 2,784.00	* 3,672.00	* 3,672.00	* 3,672.00	* 3,672.00	* 3,672.00
	ICMS		* 21,600.00	* 21,600.00	* 21,600.00	* 25,056.00	* 25,056.00	* 25,056.00	* 33,048.00	* 33,048.00	* 33,048.00	* 33,048.00	* 33,048.00
	PIS		* 1,980.00	* 1,980.00	* 1,980.00	* 2,296.80	* 2,296.80	* 2,296.80	* 3,029.40	* 3,029.40	* 3,029.40	* 3,029.40	* 3,029.40
	COFINS		* 9,120.00	* 9,120.00	* 9,120.00	* 10,579.20	* 10,579.20	* 10,579.20	* 13,953.60	* 13,953.60	* 13,953.60	* 13,953.60	* 13,953.60
	Total de Saídas	* 34,300.00	* 117,078.00	* 117,078.00	* 122,118.00	* 133,614.96	* 133,614.96	* 145,269.96	* 171,856.68	* 171,856.68	* 171,856.68	* 171,856.68	* 171,856.68
	
	Fluxo de Caixa Livre	* 5,700.00	* (37,078.00)	* 2,922.00	* 4,282.00	* (814.96)	* 5,585.04	* 8,730.04	* (3,056.68)	* 11,743.32	* 11,743.32	* 11,743.32	* 11,743.32
	
	Saldo Final Acumul.	* 205,700.00	* 168,622.00	* 222,822.00	* 227,104.00	* 226,289.04	* 231,874.08	* 23,196,138.04	* 2,319,610,747.32	* 2,319,622,490.64	* 2,319,634,233.96	* 2,319,645,977.28	* 2,319,657,720.60
	
	
	
	Primeniro Passo - Realizar Orçamento de Vendas e de Produção, cf instruções.
	Administração Financeira e Orçamentária - Realizar o Orçamento de Vendas e o Orçamento de Produção conforme solicitado.
	
	Orçamento de Vendas
		1º Trimestre	2º Trimestre	3º Trimestre	4 Trimestre
	Previsão de Vendas (unidades)	60,000	69,600	91,800	91,800
	Preço unitário – R$	* 6.00	*
6.00	* 6.00	* 6.00
	= Total de vendas – R$ (Receita Bruta)	* 360,000.00	* 417,600.00	* 550,800.00	* 550,800.00
	Impostos (27,25%)	* 98,100.00	* 113,796.00	* 150,093.00	* 150,093.00
	Receita Líquida	* 261,900.00	* 303,804.00	* 400,707.00	* 400,707.00
	
	Orçamento de Produção
	
		1º Trimestre	2º Trimestre	3º Trimestre	4º Trimestre
	Previsão de Vendas (unidades)	60,000	69,600	91,800	91,800
	(+) Estoque Final	6,960	9,180	9,180	45,900
	Total Prev. Necessária	66,960	78,780	100,980	137,700
	(-) Estoque Inicial	6,000	6,960	9,180	9,180
	Previsão de Produção	60,960	71,820	91,800	91,800
	
	
	
	
	MERCADO DE CAPITAIS
	LPA	* 46.39
	Payout	* 0.35
	Dividendos	* 24,353.36
	Dividendos por ação	* 16.24
	Dividendo yield	* 0.73
	P/L	* 0.48