生产性企业财务分析EXCEL.xls

《生产性企业财务分析EXCEL.xls》由会员分享，可在线阅读，更多相关《生产性企业财务分析EXCEL.xls（22页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、附表1 建设投资估算表 单位：万元序号项 目年份合计1231固定资产22366.4522366.451.1设备13789.7713789.771.2土建工程8576.6828576.6821.3办公设备001.3.1 通信设备01.3.2 计算机网络设备01.3.3 其他办公设备01.3.4 消防设备02备用费用03土地费用300030004长期待摊费用7407404.1评审费50504.2可行性研究费10104.3样机研制开发费3003004.4样机生产定型费1801804.5装修200200合计26106.4500 26106.45附表2流动资金估算表 单位:万元序号项目最低周 周转年份转

2、天数 次数123101流动资产504.52322.55434.71.1应收账款1524103.9449.11019.115241.2存 货320.81865.14325.51.2.1 原辅材料 3012131.7845.81979.230121.2.3 在 产 品201898.9576.81333.920181.2.4 产 成 品152490.3442.51012.51.3周转现金301279.98.390.015242流动负债43.9281.9659.72.1应付账款103643.9281.9659.73流动资金(12)460.62040.54774.94流动资金增加额460.61579.9

3、2734.4附表3 投投资资计计划划与与资资金金筹筹措措表表 单位：万元序号项 目年 份合 计12341 1投投资资总总额额287672876720802080273427340 033581335811.1 建设投资2610600261061.2 流动资金4611580273447751.3 弥亏调整项220050027002 2资资金金筹筹措措287672876720802080273427340 033581335812.1 自有资金28767-2127774902.2 流动资金借款233571134244912.3 项目盈余资金16001600股东筹资总额为7490附表4借借款款还还本

4、本付付息息表表 单位：万元序号年份1234561借款与还本付息1.1年初欠款累计0.0 23357.0 19401.0 19616.70.01.1.1 长期借款1.1.2 流动资金0.0 23357.0 19401.0 19616.70.01.2本年借款23357.01134.40.01.2.1 长期借款1.2.2 流动资金23357.01134.40.01.2.3 建设期利息1.3本年付息0.01868.61552.11569.30.01.3.1 长期借款0.00.00.00.00.01.3.2 流动资金借款0.01868.61552.11569.30.01.4本年还本0.05090.4-2

5、15.8 19616.70.01.4.1 长期借款1.4.2 流动资金借款0.05090.4-215.8 19616.70.01.5本年还本付息合计0.06959.01336.3 21186.10.01.5.1 长期借款1.5.2 流动资金借款0.06959.01336.3 21186.10.02还本资金来源6959.01336.3 21186.12.1 折旧费与摊销费6959.01336.31336.32.2 流动资金回收19849.8说明：长期借款利率0.1流动资金借款利率0.0878910借借款款还还本本付付息息表表 单位：万元附表5固定资产折旧费估算表 单位:万元序号项 目折旧年限12

6、345671第1年设备101.1原值 22366 22366.5 22366.522366 22366.5 22366.5 22366.51.2折旧费1118 1118.32 1118.32 1118.3 1118.32 1118.32 1118.321.3净值21248 20129.8 19011.517893 16774.8 15656.5 14538.22第2年设备2.1原值 0000002.2折旧费0000002.3净值0000003第3年设备3.1原值 000003.2折旧费000003.3净值000004固定资产合计4.1原值 22366 22366.5 22366.522366

7、22366.5 22366.5 22366.54.2折旧费1118 1118.32 1118.32 1118.3 1118.32 1118.32 1118.324.3净值21248 20129.8 19011.517893 16774.8 15656.5 14538.2机器设备残值率0.5合计残值8910111222366.522366223661118.32 1118.3 1118.37828.2613419.91230211183#000000000000000000000000000000022366.52236622366001118.32 1118.3 1118.300 7828.2

8、613419.9123021118300#固定资产折旧费估算表 单位:万元附表6长期待摊费用摊销估算表 单位:万元序号项 目摊销原值年份年限12345合计1长期待摊费用57407401.1摊销1481481481481487401.2净值59244429614802开办费300030002.1摊销300030002.2净值03装修费3.1装修第1年2002002002002002003.1.1摊销40404040402003.1.2净值160120804004003.2装修第2年50505050503.2.1摊销10101010403.2.2净值403020101003.3装修第3年10010

9、01001003.3.1摊销202020603.3.2净值8060401804合计409040904.1摊销318819821821821840404.2净值75260448626850附表7 制造费用、销售费用、管理费用估算表 单位：万元制造费用销售费用管理费用项目年 份项目年 份项目1231012310工 资59.74 59.74 59.74 工 资29.87 29.87 29.87 工 资折旧费100610061006 市场开发100100100 折旧费耗材及水电费8880160 其 他166.7资金租金162192240 通信交通费303030 办公耗材及水电费其他166.7合 计32

10、6.5 159.9 159.9 通信交通费合 计131613381466长期待摊费用其 他合 计123人人工工成成本本149.4149.4 149.4149.4 149.4149.4工资按2:1:2核算工资117.6 117.6 117.6折旧按9:1核算社会保险11.76 11.76 11.76 占16%租金按3:2核算公共福利19.99 19.99 19.99 占14%办公消耗及水电按4:1核算备用资金按1:1:1核算年 份1231059.74 59.74 59.74111.8 111.8 111.81081281602220403030303188198218166.73686 547.

11、6 619.6123270320400110100200500管理费用附表8总总成成本本费费用用估估算算表表 单位：万元序号项 目年 份12345671生产成本2786.211388.225016.225016.225016.225016.2 25016.21.1 原辅材料1470.010050.023550.023550.023550.023550.0 23550.01.2 制造费用1316.21338.21466.21466.21466.21466.21466.2其中:物料消耗110.0100.0200.0200.0200.0200.0200.02销售费用326.5159.9159.915

12、9.9159.9159.9159.93管理费用3686.2547.6619.6619.6619.6619.6619.64利息支出0.00.01868.61552.11569.30.00.05总成本费用 6799.012095.727664.227347.827365.025795.7 25795.7其中:折旧费1118.31118.31118.31118.31118.31118.31118.3 摊销费3188.0198.0218.0218.0218.0 利息支出0.00.01868.61552.11569.30.00.06经营成本2492.710779.424459.424459.424459

13、.424677.4 24677.47固定成本5189.11915.83884.43567.93585.12015.82015.88可变成本1609.910179.923779.923779.923779.923779.9 23779.9说明：原辅材料、动力、物料消耗为可变成本，直接工资中固定成本为80%，其余为可变成本。891025016.2 25016.2 25016.223550.0 23550.0 23550.01466.21466.21466.2200.0200.0200.0159.9159.9159.9619.6619.6619.60.00.00.025795.7 25795.7 2

14、5795.71118.31118.31118.30.00.00.024677.4 24677.4 24677.42015.82015.82015.823779.9 23779.9 23779.9总总成成本本费费用用估估算算表表 单位：万元原辅材料、动力、物料消耗为可变成本，直接工资中固定成本为80%，其余为可变成本。附表9 损损 益益 表表 单位：万元序号项 目年 份1234561销售收入2290.015700.0 43200.043300.043400.0 43400.02销售税金及附加91.6628.01728.01732.01736.01736.03总成本费用6799.012095.7

15、27664.227347.827365.0 25795.74销售利润(123)-4600.62976.3 13807.814220.214299.0 15868.3税基-4600.6-1624.3 12183.514220.214299.0 15868.35所得税(33%)0.00.04020.54692.74718.75236.56税后利润0.00.08162.99527.69580.3 10631.87盈余公积金(6)10%0.00.0816.3952.8958.01063.28公益金(6)5%0.00.0408.1476.4479.0531.69可供分配利润-4600.6-1624.36

16、938.58098.48143.39037.010股利分配0.00.06938.58098.48143.39037.012未分配利润-4601-16240000合计7881043400.0 43400.0 43400.0 43400.0364890.01736.01736.01736.01736.014595.625795.7 25795.7 25795.7 25795.7230250.115868.3 15868.3 15868.3 15868.3120044.315868.3 15868.3 15868.3 15868.35236.55236.55236.55236.539614.6106

17、31.8 10631.8 10631.8 10631.880429.71063.21063.21063.21063.28043.0531.6531.6531.6531.64021.59037.09037.09037.09037.062140.49037.09037.09037.09037.068365.30000 损损 益益 表表 单位：万元附表10 资资金金来来源源及及运运用用 单位:万元序号 项 目年 份123451资金来源28472.86372.616278.515556.615635.31.1销售利润-4600.62976.313807.814220.214299.01.2折旧费111

18、8.31118.31118.31118.31118.31.3摊销费3188.0198.0218.0218.0218.01.4长期借款1.5流动资金借款0.023357.01134.40.01.6自有资金28767.1-21277.10.00.02资金运用26567.11579.919192.013051.732957.72.1建设投资26106.50.00.02.2流动资金460.61579.92734.40.02.3所得税0.00.04020.54692.74718.72.4职工福利费0.00.0408.1476.4479.02.5借款还本0.05090.4-215.819616.72.7股

19、利0.00.06938.58098.48143.33盈余资金(1-2)1905.74792.6-2913.52504.8-17322.44累计盈余资金1905.76698.43784.96289.7-11032.7合计67891016986.616986.616986.616986.616986.6167249.015868.315868.315868.315868.315868.3120044.31118.31118.31118.31118.31118.311183.24040.00.024491.47490.014805.114805.114805.114805.114805.116737

20、4.226106.54774.95236.55236.55236.55236.55236.539614.6531.6531.6531.6531.6531.64021.50.00.024491.49037.09037.09037.09037.09037.068365.32181.52181.52181.52181.52181.5-125.2-8851.2-6669.7-4488.2-2306.7-125.2 资资金金来来源源及及运运用用 单位:万元年 份附表11资资产产负负债债表表 单位:万元序号项 目年 份12341 1资资产产24410.424410.429754.729754.728717

21、.028717.029885.529885.51.1流动资产2410.29020.99219.511724.41.1.1 应收账款103.9449.11019.11019.11.1.2 存 货320.81865.14325.54325.51.1.3 周转现金79.98.390.090.01.1.4 累计盈余资金1905.76698.43784.96289.71.3固定资产净值21248.120129.819011.517893.21.4无形及递延资产净值752.0604.0486.0268.02 2负负债债及及投投资资人人权权益益合合计计24210.424210.429504.729504.7

22、28367.028367.029535.529535.52.1长期借款余额2.2流动负债总额43.923638.920060.720276.52.2.1 应付账款43.9281.9659.7659.72.2.2 流动资金借款余额23357.019401.019616.72.3投资人权益24166.55865.78306.39259.02.3.1 资本金28767.17490.07490.07490.02.3.4 累计盈余公积金0.00.0816.31769.02.3.5 累计未分配利润-4600.6-1624.30.00.0 1.资产负债率0.2%79.4%69.9%67.8%2.流动比率54

23、.920.380.460.58 3.速动比率47.610.300.240.36 200.0250.0350.0350.0567891011226.811226.812240.012240.013303.213303.214366.314366.315429.515429.516492.716492.7-5598.0-3416.5-1235.0946.53128.05309.51019.11019.11019.11019.11019.11019.14325.54325.54325.54325.54325.54325.590.090.090.090.090.090.0-11032.7-8851.2

24、-6669.7-4488.2-2306.7-125.216774.815656.514538.213419.912301.511183.250.010876.810876.811940.011940.013003.213003.214066.314066.315129.515129.516192.716192.7659.7659.7659.7659.7659.7659.7659.7659.7659.7659.7659.7659.70.00.00.00.00.00.010217.111280.312343.413406.614469.815533.07490.07490.07490.07490.

25、07490.07490.02727.13790.34853.45916.66979.88043.00.00.00.00.00.00.05.9%5.4%5.0%4.6%4.3%4.0%-8.49-5.18-1.871.434.748.05-15.04-11.74-8.43-5.12-1.821.49350.0300.0300.0300.0300.0300.0资资产产负负债债表表 单位:万元年 份附表12 财财务务现现金金流流量量表表（全全部部投投资资）单位:万元序号项 目年 份123451现现金金流流入入2290157004320043300 62909.241.1产品销售收入229015700

26、4320043300434001.2固定资产余值回收16774.841.3流动资金回收 2734.4032现现金金流流出出29151.3678612987.2734021.49 31935.19 31962.912.1建设投资26106.45223002.2流动资金460.631579.912734.402.3经营成本2492.68533310779.35224459.352 24459.35 24459.352.4销售税金91.66281728173217362.5所得税0.000.004637.175204.875236.552.6职工福利费支出462.56538.97531.013净净现

27、现金金流流量量-26861.367862712.739178.51 11364.81 30946.334累累计计净净现现金金流流量量-26861.36786-24148.63-14970.12-3605.32 27341.015折现系数0.86960.75610.65750.57180.49726净净现现金金流流量量现现值值-23357.712051.226035.026497.86 15385.807累累计计净净现现金金流流现现值值-23357.71-21306.50-15271.48-8773.616612.19计算结果：i0=0.15NPV 6612.19IRR敏敏感感性性分分析析：1

28、建设投资增加10%:NPV=2 经营成本增加10%:NPV=3 销售收入减少10%:NPV=4 建设期延长1年:NPV=6789104340043400434004340058435.95434004340043400434004340012301.552734.4032234.06 32181.49 32181.49 32181.4932181.4924677.35 24677.3524677.424677.424677.4173617361736173617365236.555236.555236.555236.555236.55584.16531.59531.59531.59531.59

29、11165.94 11218.51 11218.51 11218.5126254.4638506.95 49725.46 60943.98 72162.4998416.950.43230.37590.32690.28430.24724827.344217.453667.353189.006489.7011439.53 15656.98 19324.33 22513.3429003.0424.51%Pb=4.12Pb*=4.57 财财务务现现金金流流量量表表（全全部部投投资资）单位:万元年 份附表13 财财务务现现金金流流量量表表（自自有有资资金金）单位:万元序号项 目年 份12341现现金金流

30、流入入22901570043200433001.1 产品销售收入22901570043200433001.2 固定资产余值回收1.3 流动资金回收2现现金金流流出出31351.4-9869.740775.034033.12.1 建设投资(自有资金)28767.1-21277.10.02.2 流动资金(自有资金)0.00.01600.02.3 流动资金(占用折旧)0.00.01600.01336.32.4 经营成本2492.710779.424459.424459.42.5 销售税金91.6628.01728.01732.02.6 所得税0.00.04020.54692.72.7 职工福利费支出

31、0.00.0408.1476.42.8 借款本金偿还5090.4-215.82.9 借款利息支出0.01868.61552.13净净现现金金流流量量-29061.425569.72425.09266.94累累计计净净现现金金流流量量-29061.4-3491.6-1066.78200.35折现系数0.86960.75610.65750.57186净净现现金金流流量量现现值值-25270.819334.41594.55298.47累累计计净净现现金金流流现现值值-25270.8-5936.4-4341.9956.5计算结果：i0=0.15NPV 5428.01567891062909.24434

32、004340043400434005731843400434004340043400434004340016774.811183.22734.42734.453915.432181.532181.532181.532181.532181.51336.30.00.024459.424677.424677.424677.424677.424677.41736.01736.01736.01736.01736.01736.04718.75236.55236.55236.55236.55236.5479.0531.6531.6531.6531.6531.619616.70.00.00.00.00.01569.30.00.00.00.00.08993.811218.511218.511218.511218.525136.117194.128412.639631.150849.662068.187204.30.49720.43230.37590.32690.28430.24724471.54850.14217.53667.43189.06213.35428.010278.114495.518162.921351.927565.2IRR 28.43%Pb=3.12Pb*=3.82 财财务务现现金金流流量量表表（自自有有资资金金）单位:万元年 份