专业估值模型-EXCEL模版

最新修改增加了FCFE估值、DDM估值、APV估值、EVA估值和AE估值。原先的估值对应FCFF估值。其他估值模型有些数是链接到FCFF的，因此FCFF的估值要做全更改了自由现金流定义＝EBIT×（1-所得税率）＋折旧－资本支出－非现金性流动资本变化预测前首先需要自己输入的数据3主营收入及成本：各公司收入及成本的预测方法不尽相同，需要分析师自己做4预测假设：各种假设条件5固定资产预测：历史折旧和摊销7损益表及利润分配：2003-2004A利润数据8资产负债表：2003-2004历史数据7损益表及利润分配、8资产负债表：无公式的单元格需要手动填入，如一些特殊会计科目DCF估值（FCFF）：分析日期、股本结构，假设条件中的“第二阶段增长率、长期增长率、应付债券利率、β”。注意：第二阶段(2010-2017)年数不可更改。其他估值模型：各假设条件（第二阶段年数也不可更改）11输出结果：2003-2004年经营活动现金流量、投资活动现金流量、融资活动现金流量增加了FCFE估值、DDM估值、APV估值、EVA估值和AE估值。原先的估值对应FCFF估值。其他估值模型有些数是链接到FCFF的，因此FCFF的估值要做全DCF估值（FCFF）：分析日期、股本结构，假设条件中的“第二阶段增长率、长期增长率、应付债券利率、β”。注意：第二阶段(2010-2017)年数不可更改。估值过程中所用的固定参数参数数值无风险利率Rf5.00%平均风险股票必要报酬率Rm11.00%一般情景预测名称20022003产品分产品收入尿素收入32,261.0832,356.92DMF收入5,800.3724,055.91甲醇/甲醛/三甲胺等化工品收入7,048.498,786.03醋酸收入尿素收入增长率0.30%DMF收入增长率314.73%甲醇/甲醛/三甲胺等化工品收入增长率24.65%醋酸收入增长率分产品价格市场尿素月均价1,261.001,384.00市场DMF月均价市场甲醇月均价1,842.002,396.00醋酸月均价尿素价格上涨幅度9.75%DMF价格上涨幅度#DIV/0!甲醇/甲醛/三甲胺等价格上涨幅度30.08%醋酸价格上涨幅度分产品产量尿素产量增长率-8.62%DMF产量增长率#DIV/0!甲醇/甲醛/三甲胺等化工品等化工品产量增长率-4.17%醋酸产量增长率0.00尿素产量DMF产量甲醇/甲醛/三甲胺等化工品等化工品产量醋酸产量主要产品收入合计（万元）45,109.9465,198.86其它产品收入（万元）0.000.00其它产品占主营收入比例0.00%0.00%主营业务收入（万元）45,11065,199调整比例0.00%0.00%主营业务收入（万元）调整后45,11065,199主营收入增长率%44.5%主营业务成本预测20022003尿素利润6,969.755,923.59DMF利润1473.165494.36历史数据区甲醇/甲醛/三甲胺等化工品利润838.852,218.79醋酸利润尿素毛利率21.60%18.31%DMF毛利率25.40%22.84%甲醇/甲醛/三甲胺等化工品毛利率11.90%25.25%醋酸毛利率主营业务利润9281.7613636.74主营业务利润增长率46.92%主营业务毛利率20.58%20.92%子公司收入预测并表子公司股权比例20022003子公司利润预测并表子公司股权比例20022003合计少数股东权益调整后少数股东权益200420052006E2007E2008E2009E44,671.0366,824.0099,801.64109,033.30137,763.57157,050.4740,632.0945,839.0075,634.35104,224.13114,646.55108,914.2214,044.4422,559.0023,574.1629,467.6932,414.4632,414.4636,000.00111,600.0038.06%49.59%49.35%9.25%26.35%14.00%68.91%12.81%65.00%37.80%10.00%-5.00%59.85%60.63%4.50%25.00%10.00%0.00%1,653.001,855.721,911.391,815.821,725.031,638.780.000.000.000.000.002,800.003,904.323,709.103,709.103,709.103,709.106,000.006,200.0019.44%12.26%3.00%-5.00%-5.00%-5.00%#DIV/0!-13.22%10.00%6.00%0.00%-5.00%16.86%39.44%-5.00%0.00%0.00%0.00%15.59%33.25%45.00%15.00%33.00%20.00%#DIV/0!30.00%50.00%30.00%10.00%0.00%36.79%15.19%10.00%25.00%10.00%0.00%0.000.000.000.006.0018.0099,347.56135,222.00199,010.15242,725.12320,824.58409,979.150.000.000.000.000.000.000.00%0.00%0.00%0.00%0.00%0.00%99,348135,222199,010242,725320,825409,9790.00%0.00%0.00%0.00%0.00%0.00%99,348135,222199,010242,725320,825409,97952.4%36.1%47.2%22.0%32.2%27.8%200420052006E2007E2008E2009E8,669.1013,749.0021,332.5423,305.8129,446.8833,569.459652.256883.0015210.0724758.6927234.5624579.19历史数据区显性期预测2,529.404,762.004,976.295,307.125,837.845,837.840.008,551.8925,185.3019.41%20.57%21.37%21.37%21.37%21.37%23.76%15.02%20.11%23.76%23.76%22.57%18.01%21.11%21.11%18.01%18.01%18.01%23.76%23.76%22.57%20850.7525394.0041518.9053371.6271071.1789171.7852.90%21.79%63.50%28.55%33.16%25.47%20.77%18.78%20.86%21.99%22.15%21.75%20042005E2006E2007E2008E2009E20042005E2006E2007E2008E2009E一般情景预测1、经营活动2003200420052006E2007E主营业务收入同比增长率44.53%52.38%36.11%47.17%21.97%主营业务成本/主营业务收入79.08%79.23%81.22%79.14%78.01%主营业务税金及附加/主营业务收入（营业税率）0.50%0.55%0.49%0.49%0.49%营业费用/主营业务收入1.12%1.47%1.41%1.41%1.41%管理费用/主营业务收入3.22%2.49%1.94%1.94%1.94%实际税率23.95%17.16%19.63%19.63%19.63%股利分配比例25.44%19.86%9.51%9.51%9.51%2、营运资金2003200420052006E2007E货币资金/主营业务收入49.67%34.84%27.05%27.05%27.05%应收帐款周转天数(相对于主营业务收入)0.010.000.000.000.00应收票据周转天数(相对于主营业务收入)6.381.665.055.055.05存货周转天数(相对于主营业务成本)38.2247.4248.1848.1848.18应付票据周转天数(相对于主营业务成本)0.000.009.119.119.11应付帐款周转天数(相对于主营业务成本)39.31116.7245.4645.4645.46预付帐款增加/主营业务成本2.29%-0.60%1.16%1.16%1.16%待摊费用增加/营业费用和管理费用-0.13%0.03%2.06%2.06%2.06%预收帐款(负债)增加/主营业务收入1.62%0.91%3.33%3.33%3.33%预提费用增加/营业费用和管理费用0.00%0.00%0.49%0.49%0.49%其他流动资产增加/主营业务收入增加0.00%0.00%0.00%0.00%0.00%其他流动负债增加/主营业务收入增加0.00%0.00%0.00%0.00%0.00%其他长期资产增加/主营业务收入增加0.00%0.00%0.00%0.00%0.00%其他长期负债增加/主营业务收入增加0.00%0.00%0.00%0.00%0.00%坏帐准备/应收帐款0.00%0.00%0.00%0.00%0.00%短期投资跌价准备#DIV/0!#DIV/0!#DIV/0!0.00%0.00%存货跌价准备(含工程亏损准备)0.00%0.00%0.00%0.00%0.00%长期投资减值准备#DIV/0!#DIV/0!#DIV/0!0.00%0.00%固定资产减值准备0.00%0.00%0.00%0.00%0.00%在建工程减值准备0.00%#DIV/0!0.00%0.00%0.00%3、资本性投资12年残值率为5.00%年折旧率=7.92%2003200420052006E2007E55,760.3160,852.5051,739.0060,000.0030,000.004、债务和利息2003200420052006E2007Ea、假定固定资产采用直线法折旧b、现有固定资产和新建固定资产的折旧年限平均为c、各年的固定资产投资如下：a、最低货币资金为主营业务收入的34.84%b、银行长期贷款利率为6.00%c、现金不足时增加短期贷款，利率为5.50%d、现金存款利率为1.35%预计银行长期贷款增加额（万元）42,700.0049,400.0032,333.0020,000.00(40,000.00)2008E2009E32.18%27.79%77.85%78.25%0.49%0.49%1.41%1.41%1.94%1.94%19.63%19.63%9.51%9.51%2008E2009E27.05%27.05%0.000.005.055.0548.1848.189.119.1145.4645.461.16%1.16%2.06%2.06%3.33%3.33%0.49%0.49%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%2008E2009E3,000.003,000.002008E2009E(35,000.00)(35,000.00)经营情景分析情景选择2一般情景预测会计年度截止日：12/312005E2006E1销售收入增长率36.11%47.17%2销货成本/销售收入81.22%79.14%3管理费用/销售收入1.94%1.94%营业费用/销售收入1.41%1.41%股利分配比例9.51%9.51%1乐观情景预测销售收入增长率39.72%51.89%销货成本/销售收入79.60%77.55%管理费用/销售收入1.84%1.84%营业费用/销售收入1.34%1.34%股利分配比例14.51%14.51%2一般情景预测销售收入增长率36.11%47.17%销货成本/销售收入81.22%79.14%管理费用/销售收入1.94%1.94%营业费用