planilha 6° semestre adm unopar

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

Plan1
	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	313,200
	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	* 25,200.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	* 5,500.00	* 66,000.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	* 8,350.00	* 100,200.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	* 21,600.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	* 12,000.00
	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	* 26,400.00
	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
	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	* 1,883.55	* 13,733.10
	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	* 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.00
	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	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 - 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	171,544.00	175,826.00	175,011.04	180,596.08	189,326.12	186,269.44	198,012.76	209,756.08	221,499.40	233,242.72
	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	303,804	400,707	* 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	0
	Total Prev. Necessária	66,960	78,780	100,980	91,800
	(-) Estoque Inicial	0	6,960	9,180	9,180
	Previsão de Produção	66,960	71,820	91,800	82,620
	MERCADO DE CAPITAIS
	LPA	* 46.39
	Payout	* 0.35
	Dividendos	24353.364
	Dividendos por ação	16.235576
	Dividendo yield	0.7264239821
	P/L	0.4818122868