求教如何用excel 分析股票投资组合

【IT168 专稿】投资组合优化问题是研究在满足某些要求的前提下，如何选择对象使得投资收益最大化或风险最小。本文通过实例运用EXCEL的规划求解功能进行投资组合优化问题的分析。

打开Excel软件，将股票收盘价数据粘贴到Excel中，根据公式：月收益率＝[（本月收盘价－上月收盘价）/上月收盘价]×100%，就可以计算出股票的月收益率，用同样的方法可以计算出大盘收益率。将股票收益率和市场收益率放在同一张Excel中，这样在Excel表格中我们得到两列数据：一列为个股收益率，另一列为大盘收益率。选中某一个空白的单元格，用Excel的“函数”－“统计”－“Slope()函数”功能，计算出股票的β系数。如果有2 w数据呢？

如何分析财务报表

1. 财务报表主要透露了一个企业的四种基本面：

一种是获利能力，一种是经营效率，一个是财务结构，还有一个成长潜力。看企业的获利能力要看财务报表的毛利率、纯益率和股东权益汇报率。企业的经营效率要看存货周转率、应收账款周转率。财务结构可以看报表上的负债比率、流动比率和速动比率。最后一项成长潜力可以从营收增长率和盈余增长率里体现出来。

2..财务报表分析的要点： 财务分析的对象是财务报表，财务报表主要包括资产负债表、财务状况变动表和利润及利润分配表。

从这三种表中应着重分析以下四项主要内容：

（1）公司的获利能力。公司利润的高低、利润额的增长速度是其有无活力、管理效能优劣的标志。作为投资者，购买股票时，当然首先是考虑选择利润丰厚的公司进行投资。所以，分析财务报表，先要着重分析公司当期投入资本的收益性。

（2）公司的偿还能力。目的在于确保投资的安全。具体从两个方面进行分析：一是分析其短期偿债能力，看其有无能力偿还到期债务，这一点须从分析、检查公司资金流动状况来下判断；二是分析其长期偿债能力的强弱。这一点是通过分析财务报表中不同权益项目之间的关系、权益与收益之间的关系，以及权益与资产之间的关系来进行检测的。

（3）公司扩展经营的能力。即进行成长性分析，这是投资者选购股票进行长期投资最为关注的重要问题。

（4）公司的经营效率。主要是分析财务报表中各项资金周转速度的快慢，以检测股票发行公司各项资金的利用效果和经营效率。

4. 评价企业财务状况的指标：

（1）销售利润率：反映企业销售收入的获利水平。计算公式： 销售利润率 = 利润总额/产品销售净收入 × 100% 产品销售净收入：指扣除销售折让、销售折扣和销售退回之后的销售净额。

（2）总资产报酬率：用于衡量企业运用全部资产获利的能力。计算公式为： 总资产报酬率 = （利润总额+利息支出）/平均资产总额 × 100%

（3）资本收益率：是指企业运用投资者投入资本获得收益的能力。计算公式： 资本收益率 = 净利润/实收资本 × 100%

（4）资本保值增值率：主要反映投资者投入企业的资本完整性和保全性。计算公式： 资本保值增值率 = 期末所有者权益总额/期初所有者权益总额 × 100% 资本保值增值率 = 100%，为资本保值；资本保值增值率大于100%，为资本增值。

（5）资产负债率：用于衡量企业负债水平高低情况。计算公式： 资产负债率 = 负债总额/资产总额 × 100%

（6）流动比率：衡量企业在某一时点偿付即将到期债务的能力，又称短期偿债能力比率。计算公式： 流动比率 = 流动资产/流动负债 × 100% 速动比率：是指速动资产与流动负债的比率，它是衡量企业在某一时点运用随时可变现资产偿付到期债务的能力。速动比率是对流动比率的补充。计算公式： 速动比率 = 速动资产/流动负债 × 100%

（7）应收帐款周转率：也称收帐比率，用于衡量企业应收帐款周转快慢。计算公式： 应收帐款周转率 = 赊销净额/平均应收帐款余额 × 100% 赊销净额 = 销售收入 - 现销收入 - 销售退回、折让、折扣。由于企业赊销资料作为商业机密不对外公布，所以，应收帐款周转率一般用赊销和现销总额，即销售净收入。 平均应收帐款余额 = （期初应收帐款余额 + 期末应收帐款余额）÷ 2

（8）存货周转率：用于衡量企业在一定时期内存货资产的周转次数，反映企业购、产、销平衡的效率的一种尺度。计算公式： 存货周转率 = 产品销售成本/平均存货成本 × 100% 平均存货成本 = （起初存货成本 + 期末存货成本）÷ 2

（9）社会贡献率：是衡量企业运用全部资产为国家或社会创造或支付价值的能力。 社会贡献率 = 企业社会贡献总额/平均资产总额 × 100%

（10）社会积累率：衡量企业社会贡献总额中多少用于上交国家财政。计算公式： 社会积累率 = 上交国家财政总额/企业社会贡献总额 × 100%

现金流是所有财务报表中最重要的指标之一。它主要记录了企业在销售商品、提供劳务、购买商品、接受劳务、支付税收等活动的现金流动情况，也就是反映了主营业务的现金收支状况。

利润表上的利润可以通过增减折旧或暂不记坏帐等手段来改变，但要同时修改利润和营运资本项目就没有那么容易。公司宣布破产之前，连续多年净利润为正数，这样的情况并不少见；但该公司的经营现金流总是在破产前的几年里就开始恶化。如果我们十分关注公司的经营现金流，就可以预测公司的风险。因此，如果你想分析一家公司的财务状况，又感到时间紧迫，你可以先看一眼它的经营现金流，因为它比任何其它财务数据更说明问题。

看财务报表，主要看每股收益和净资产收益率（看公司的盈利能力如何）、负债率（看公司的经营是否安全），还有上面所说的现金流（看公司的可持续经营能力如何）.这要看你分析财务报表的用途，用途不一样，采用的指标也有差别，因为从分析目的而言，你没必要分析财务报表的每一个指标。以下是分析财务报表普遍用到的指标，要注意从同行业普遍指标、历年指标变动趋势、指标结构变化、横向纵向比较等多方面考虑，才能得出正确的分析结果。

一、短期偿债能力分析

1.营运资本＝流动资产－流动负债

2.流动比率＝流动资产÷流动负债

3.速动比率＝（流动资产－存货）÷流动负债

4.保守速动比率＝(货币资金＋短期证券投资净额＋应收账款净额)÷流动负债

5.现金比率＝（货币资金＋短期投资净额）÷流动负债

二、长期偿债能力分析

1.资产负债率＝（负债总额÷资产总额）×100%

2.①产权比率＝（负债总额÷所有者权益总额）×100%

②产权比率＝资产负债率÷（1－资产负债率）

3.有形净值债务率＝[负债总额÷(股东权益－无形资产净值)]×100%

4.利息偿付倍数＝息税前利润÷利息费用

其中：息税前利润＝税前利润＋利息费用＝税后利润＋所得税＋利息费用

5.长期债务与营运资本比率＝长期债务÷（流动资产－流动负债）

6.①固定支出偿付倍数＝（税前利润＋固定支出）÷固定支出

②固定支出偿付倍数＝(息税前利润＋租赁费中的利息费用)÷[利息费用＋租赁费中的利息费用＋优先股股息÷(1－所得税税率)]

三、资产运用效率分析

1.总资产周转率＝主营业务收入÷总资产平均余额

其中：总资产平均余额＝（期初总资产＋期末总资产）÷2

总资产周转天数＝计算期天数÷总资产周转率

2.流动资产周转率＝主营业务收入÷流动资产平均余额

其中：流动资产平均余额＝（期初流动资产＋期末流动资产）÷2

流动资产周转天数＝计算期天数÷流动资产周转率

3.固定资产周转率＝主营业务收入÷固定资产平均余额

其中：固定资产平均余额＝（期初固定资产＋期末固定资产）÷2

固定资产周转天数＝计算期天数÷固定资产周转率

4.长期投资周转率＝主营业务收入÷长期投资平均余额

其中：长期投资平均余额＝（期初长期投资＋期末长期投资）÷2

长期投资周转天数＝计算期天数÷长期投资周转率

5.其他资产周转率＝主营业务收入÷其他资产平均余额

其中：其他资产平均余额＝（期初其他资产＋期末其他资产）÷2

其他资产周转天数＝计算期天数÷其他资产周转率

6.①应收账款周转率＝主营业务收入÷应收账款平均余额

②应收账款周转率＝赊销净额÷应收账款平均余额

其中：应收账款平均余额＝（期初应收账款＋期末应收账款）÷2

应收账款周转天数＝计算期天数÷应收账款周转率

7.①成本基础的存货周转率＝主营业务成本÷存货平均净额

②收入基础的存货周转率＝主营业务收入÷存货平均净额

其中：存货平均净额＝（期初存货净额＋期末存货净额）÷2

①成本基础的存货周转天数＝计算期天数÷成本基础的存货周转率

②收入基础的存货周转天数＝计算期天数÷收入基础的存货周转率

四、获利能力分析

1.①销售毛利额＝主营业务收入－主营业务成本

销售毛利率＝销售毛利额÷主营业务收入×100%

②单一产品毛利额＝销量×（销售单价－单位销售成本）

单一产品毛利率＝（销售单价－单位销售成本）÷销售单价

③多种产品毛利额＝∑单一产品毛利额

或＝主营业务收入总额×综合毛利率

综合毛利率＝∑（某产品销售比重×该产品毛利率）

2.①营业利润＝主营业务利润+其他业务利润－营业费用-管理费用-财务费用

②营业利润＝贡献毛益－固定成本

其中：贡献毛益=主营业务收入-变动成本=销量×(单价-单位变动成本)

3.营业利润率＝营业利润÷主营业务收入×100%

4.销售净利率＝净利润÷主营业务收入×100%

5.①经营杠杆系数＝营业利润变动率÷产销量变动率

②经营杠杆系数＝基期贡献毛益÷基期营业利润

6.总资产收益率＝收益总额÷平均资产总额×100%

其中：收益总额＝息税前利润

7.长期资本收益率＝收益总额÷长期资本额×100%

其中：收益总额＝息税前利润－短期利息

或＝税前利润＋长期资本利息

长期资本额＝长期负债平均余额+所有者权益平均余额=(期初长期负债+期末长期负债)÷2+(期初所有者权益+期末所有者权益)÷2

五、投资报酬分析

1.①财务杠杆系数＝普通股每股收益变动率÷息税前利润变动率

②财务杠杆系数＝变动前的息税前利润÷变动前的税前利润

2.①净资产收益率＝净利润÷平均所有者权益×100%

其中：平均所有者权益＝（期初所有者权益＋期末所有者权益）÷2

②净资产收益率＝资产净利率×权益乘数

其中：资产净利率＝销售净利率×总资产周转率

权益乘数＝1÷（1－资产负债率）

③净资产收益率＝销售净利率×总资产周转率×权益乘数

④净资产收益率＝资产净利率÷（1－资产负债率）

⑤净资产收益率＝[资产收益率＋(资产收益率－负债利息率)×(负债÷净资产)]×(1－所得税率)

净资产收益率的变动率＝资产收益率的变动率×财务杠杆系数

3.①简单资本结构的每股收益＝（净利润－优先股股利）÷发行在外的加权平均普通股股数

②复杂资本结构的基本每股收益＝（净利润－优先股股利）÷（流通在外的普通股股数＋增发的普通股股票＋真正稀释的约当股数）

③复杂资本结构的充分稀释的每股收益＝（净利润－不可转换优先股股利）÷（流通在外的普通股股数＋普通股股票等同权益）

4.股票获利率＝（普通股每股股利＋每股市场利得）÷普通股每股市价×100%

其中：每股市场利得＝期末股票市价－期初股票市价

5.市盈率＝普通股每股市价÷每股收益

6.市净率＝普通股每股市价÷每股净资产

其中：每股净资产＝（股东权益总额－优先股权益）÷发行在外的加权平均普通股股数

六、现金流量分析

1.现金流量与当期债务比＝经营活动现金净流量÷流动负债×100%

2.债务保障率＝经营活动现金净流量÷（流动负债＋长期负债）×100%

3.偿还到期债务比率＝经营活动现金净流量÷本期到期负债×100%

4.每元销售现金净流入＝经营活动现金净流量÷主营业务收入

5.每股经营现金流量＝（经营活动现金净流量－优先股股利）÷发行在外的普通股股数×100%

6.全部资产现金回收率＝经营活动现金净流量÷全部资产×100%

7.现金流量适合比率＝一定时期经营活动现金净流量÷（同期资本支出＋同期存货净投资额＋同期现金股利）×100%

8.现金再投资比率＝（经营活动现金净流量－现金股利－利息支出）÷（固定资产原值＋对外投资＋其他资产＋营运资金）×100%

9.现金股利保障倍数＝经营活动现金净流量÷现金股利额

10.营运指数＝经营活动现金净流量÷经营所得现金×100%

其中：经营所得现金＝净收益－非经营净收益＋非付现费用

七、业绩计量与评价

1.净收益＝净利润－优先股股利

2.①净现金流量＝净收益＋非现金费用

②经营活动现金净流量＝净现金流量－流动资产增加额＋流动负债增加额－非经营活动收益

③自由现金流量＝经营活动现金净流量－资产投资支出

3.经济增加值＝息前税后经营收益－使用的全部资金×资本成本率＝税后经营收益－使用的股权资金×股权成本率

基本经济增加值＝息前税后经营利润－报表总资产×资本成本率

4.市场增加值＝总市值－总资本

5.经济收益＝预期未来现金流入现值－净投资的现值

6.资本积累率＝本年所有者权益增长额÷年初所有者权益×100%

其中：本年所有者权益增长额＝年末所有者权益－年初所有者权益