有效利用EXCEL进行管理和财务分析.pptx

有效利用EXCEL进行管理和财务分析.pptx

《有效利用EXCEL进行管理和财务分析.pptx》由会员分享，可在线阅读，更多相关《有效利用EXCEL进行管理和财务分析.pptx（65页珍藏版）》请在冰点文库上搜索。

第三部分：

有效利用EXCEL进行管理和财务分析I、EXCEL操作技巧II、EXCEL中公式和函数的使用方法III、如何运用数据透视表进行分析IV、各种财务报表及管理报告的制作与分析V、实际案例演练I、EXCEL操作技巧一、数据的输入及运算1、数据的输入2、数据的处理3、数据的运算及引用二、图表处理1、基本图表的处理2、组合图表3、趋势线三、假设分析工具1、方案管理器2、模拟运算表3、单变量求解4、规划求解数据的输入Excel的数据输入的数据输入*针对数据量较小的数据手工输入自动生成数据库导入Internet导入*针对有规律的数据*针对数据库已有数据*针对Internet搜索数据在金融应用中有大量数据来源于Internet,故我们着重讲解如何将数据从Internet导入。

数据的输入InternetInternet导入导入：

*选择IE中所需的数据，使用“复制”、“粘贴”键将数据导入到Excel中*导入Web页上的表格数据，也可通过IE打开数据所在页面，在表格处“右击-导出到MicrosoftExcel”命令，即可完成数据导入。

*创建Web查询，“数据”“导入Web查询”“新建Web查询”，打开“新建Web查询”对话框，在地址栏中输入数据源的web地址，打开网页，单击所需数据旁的“”标志，单击“导入”，可将数据导入到Excel中，此外，Excel中有“数据”“刷新”命令可以更新从Web中导入的数据。

数据的处理排序排序：

排序是按照某一或某几个特定字段重新排列数据*排序按钮排序按钮是一种比较简单的方法，选择要排序的单元格区域，点击“常用”工具栏上的排序按钮”“，即可对选择的区域进行升序或降序排列*排序命令首先选中整个数据区域（标题行除外），执行“数据”“排序”命令，打开“排序”对话框，在“主要关键字”、“次要关键字”、“第三关键字”中选择标题，选择要排序的次序。

在选项按钮打开“排序选项”对话框，对排序的进行设置，确定完成数据的排序。

在自定义排序中，可以事先在“选项”的“自定义序列”中设置自己的序列次序数据的处理筛选筛选：

筛选是查找和处理区域中数据子集的一种比较快捷的方法*自动筛选自动筛选“”“适用于简单的筛选。

选定要筛选的内容，执行数据筛选”“自动筛选”命令，工作表每列标题行的右侧会出现筛选按钮，点击筛选按钮，在下拉列表框筛选数据。

在主营行业的下拉列表框中有车类、摩托车、汽车制造、汽配等几项筛选的条件，筛选后的筛选按钮和行标签会呈现蓝色。

此外，可以进行多列筛选数据的处理筛选筛选：

筛选是查找和处理区域中数据子集的一种比较快捷的方法在自动筛选的下拉列表中有“自定义”筛选，在“自定义自动筛选方式”对话框中输入筛选的条件，可以对数据进行范围筛选，例如筛选每股收益大于0的上市公司数据的处理筛选筛选：

筛选是查找和处理区域中数据子集的一种比较快捷的方法*高级筛选高级筛选用于复杂条件的筛选。

执行“数据”“筛选”“高级筛选”命令，在高级筛选对话框中，选择要筛选的数据、筛选的条件以及筛选结果出现的区域数据清单筛选结果条件区域条件区域必须有列标志条件区域与数据清单间至少有一个空白行通常在条件区域的数据清单上至少插入3个空白行，以利于输入筛选条件数据的处理筛选筛选：

筛选是查找和处理区域中数据子集的一种比较快捷的方法表1高级筛选的重点在条件区域，在筛选数据的工作表中，在条件区域建立筛选条件。

不同变量的条件位于同一行，代表“并”；不同行代表“或”单列多个条件一列具有多个筛选条件，可以直接在一列从上到下键入条件，如表1，筛选主营业务利润率小于0或是大于10的纸业行业公司证券代码主营业务利润率（%）10证券代码主营业务利润率（%）销售毛利率（%）00表2证券代码主营业务利润率（%）销售毛利率（%）资产负债率（%）000表3多列具有单个条件在多列中查找需要的数据时，在条件区域的同一行键入条件，如表2，筛选主营业务利润率0,销售毛利率也0的公司多行或多列具有单个条件满足多行或多列条件的数据，需在条件区域的不同列中输入条件，如表3，筛选主营业务利润率0或者销售毛利率0或者资产负债率0的公司数据的处理筛选筛选：

筛选是查找和处理区域中数据子集的一种比较快捷的方法高级筛选妙用：

剔除重复数据在实务中，我们的数据经常会出现重复数据的情况，此时若逐一剔除，在大量数据的情况下就太太麻烦了。

我们可以巧妙地运用高级筛选来解决这一问题。

如下图，我们在条件区域不设任何条件，选择”数据”-“筛选”-“高级筛选“，在”高级筛选“对话框中勾选”选择不重复的记录“，确定后就可以得到无重复数据的结果数据的运算及引用相对引用与绝对引用相对引用与绝对引用相对引用单元格中的相对引用，在复制公式时，单元格引用会调整到新位置。

单元格D2=B2*C2，D3：

D4复制D2中的公式，引用位置相对发生变化，如下图绝对引用单元格中的绝对引用，如$A$1（通过按”F4“键实现），是指定位置引用单元格，在复制公式时，单元格引用绝对不会改变。

单元格E4=D4*$E$1，E5：

E6复制E4中的公式，绝对引用$E$1的位置没有发生变化，如下图数据的运算及引用命名命名：

所谓名称是指代表单元格、单元格区域、公式或常量值的单词或字符串。

与行列标志相比较，名称更直观，也更便于理解和运用*单元格和区域命名：

选中要命名的单元格或单元区域，在Excel名称框中输入名称并回车。

选中区域B1:

B5，在名称框中输入“单价”，Enter键结束Excel菜单中“插入”“名称”“定义”命令，输入名称和指定的区域。

在“当前工作簿中的名称中输入“物品”，引用位置中输入或选中要命名的区域=sheet1!

$A$1:

$A$5数据的运算及引用命名命名：

所谓名称是指代表单元格、单元格区域、公式或常量值的单词或字符串。

与行列标志相比较，名称更直观，也更便于理解和运用*单元格名称指定：

在有两列或两行数据，其中一列或行为文本标识（字段名），另一列或行为具体数值，可为数值自动命名。

选中区域，使用Excel菜单中“插入”“名称”“指定”命令，选择标识所在位置后确认。

如下图，选中A4:

E9，打开“指定名称”对话框，选择“最左列”复选框，确定结束，数据的运算及引用命名命名：

所谓名称是指代表单元格、单元格区域、公式或常量值的单词或字符串。

与行列标志相比较，名称更直观，也更便于理解和运用*常量命名：

此方法为不在单元格中出现的数值命名。

使用Excel菜单中“插入”“名称”“定义”命令，“在当前工作簿中的名称”中输入名称，“引用位置”输入数值。

在使用Excel工作表计算分析的过程中，多次使用变量“销售税=0.075”，但工作表中却未出现，打开“定义名称”对话框，在“当前工作簿中的名称中”输入“销售税”，引用位置中输入0.075，确定结束，如下图数据的运算及引用函数函数：

Excel作为计算的工具软件，是通过函数和内置工具计算处理数据的。

Excel提供了大量的内置函数，用于执行特定运算，具有强大的数据处理能力.Excel按功能和作用不同可分为：

财务、日期与函数、数学和三角函数、统计函数、查找和引用函数、数据库函数、文本函数、逻辑函数、信息函数、用户定义、工程函数。

*在后面的学习中会分别具体介绍这几类函数，这里只通过一种逻辑函数（IF函数）的学习介绍函数的使用，如图在单元格中通过输入“=”插入函数直接输入函数针对熟悉者点击打开函数库，选择所需函数输入函数所需参数，确定即可OR图表处理图表是使用Excel进行各种运算分析时一个非常有用的工具，通过图表，可以更加直观地提示数据之间的内在关系和变化趋势。

Excel中提供了14种内置的图表类型，其中一些类型还包含有其他变化。

在金融计算中，各种图表类型都有可能用到。

但从数量分析的角度来看，应用最多的是散点图和带有拆线或曲线的散点图。

图表处理基本图表处理基本图表处理*建立图表单击常用工具栏上的按钮，出现图表向导，选择正确的图表类型确定数据源设定图表选项图表处理基本图表处理基本图表处理*设置图表格式方法：

右击图表每一元素（如图表区、坐标轴、图表网格线图例等），在快捷菜单中逐一设置How?

右击图表区域，“图表区格式”-“填充效果”-“纹理”右击绘图区，“绘图区格式”-“填充效果”-“双色”图表处理组合图表组合图表所谓组合图表是指在同一幅图表中使用不同的图表类型表示不同组别的数据，它是在用图表表示多组数据之间的对比关系时的一个常用工具。

Forexample:

为考察KJIA30家企业当前的股利收益率和股利支付比例，将其绘制在同一图表内。

为区别两项不同的指标，分别用拆线图和柱形图表示股利收益率和股利支付比例。

操作方法：

在已建立图表的图表区右击,选择“源数据”-“系列”，点击“添加”，名称设为“股利支付比例”，“值”处输入数据，确定即可。

？

加入股利支付率这一系列后，由于其数值均较大而拉升了纵坐标坐标值，导致股利收益率的曲线图不能很好显示，此时，如何实现对比？

图表处理次坐标轴次坐标轴次坐标轴是指在组合图表中因多组数据的数据范围差别较大时，为更好地表示多组数据的对比关系而设立两条不同的纵坐标轴的技巧。

How?

Step1:

选中股利收益率系列，右击“图表类型”Step2:

在图表类型中选择柱形图Step3:

选中股利收益率系列，右击“数据系列格式”Step4:

在“数据系列格式”选项卡中选择“坐标轴”-“次坐标轴”图表处理趋势线趋势线Excel图表中的另一项重要功能是绘制趋势线。

从原理上讲，这一功能是基于统计学中的回归与预测理论。

它是金融定量分析中的常用工具Forexample:

为说明风险与收益之间的关系，我们计算了KJIA的30家企业在1990-2002年间的收益率平均值和标准差，并绘制成图表。

因事先不知二者到底有何关系，故我们使用散点图。

经观察，二者有线性关系，利用Excel的趋势线功能可以比较精确地从数学上验证这一推测Step1:

点击图表区，出现“图表”菜单栏，选择“添加趋势线”Step2:

选择合理的趋势线类型Step3：

具体设置趋势线“选项”，可以选择“显示公式”。

在预测中还有一重要功能，即“前推”，选择合适的单位即可假设分析工具方案管理器方案管理器所谓方案,是保存在工作表当中并可以在相互之间进行切换的数据。

例如，对于一组现金流量分别使用不同的贴现率计算净现值，就可以把不同条件下的结果保存在若干方案中，利用Excel的方案管理器在不同的结果间进行切换，以对比和评价各方案之间的差异Forexample:

假定某投资项目，投资金额为10000，项目周期为5年。

项目的预期现金流量和贴现率都受经济形势的影响：

如果经济不景气，则预期每年现金流量为1200，贴现率为6%；如果经济繁荣，则预期每年现金流量为2000，贴现率为10%；如果经济形势一般，则现金流量为1600，贴现率为8%。

要求对该项目进行评价。

Step1:

菜单“工具”-“方案”，打开方案管理器，点击“添加”Step2:

在编辑方案中，输入方案名和可变单元格Step3：

输入方案数值如是添加各方案Step4:

再次打开方案管理器，进行方案管理。

选择方案，点击“显示”随着显示方案的变化，Excel中的相应数值也会发生变化。

假设分析工具模拟运算表模拟运算表模拟运算表是一个非常理想的敏感性分析工具，它被广泛应用于金融领域中，如在资本预算中的敏感性分析等在上例中，我们通过方案管理器给出了3组不同条件下的参数值，得到了项目的评价结果。

通过模拟运算表，可以对项目净现值进行敏感性分析，更详细地研究贴现率和每年现金流量对评价结果的影响。

在B13单元格内输入净现值的计算公式，在公式单元格B13的右边输入一组贴现率的可能值，在其下侧输入一组每年现金流量的可能值。

用鼠标选中B13：

G19区域，从菜单中执行“数据”-“模拟运算表”，在模拟运算表的对话框中输入相关参数在得出模拟运算表后，根据其结果绘制图表可以更直观地表示评价结果对参数的敏感性假设分析工具模拟运算表模拟运算表模拟运算表是一个非常理想的敏感性分析工具，它被广泛应用于金融领域中，如在资本预算中的敏感性分析等假设分析工具单变量求解单变量求解单变量求解，用于在数量分析的过程中，可以通过改变公式的某一个变量值，求出其它参数变量的作用，有数学运算中反函数作用的效果。

在金融计算中存在着许多这类问题，例如在资本预算中求解内部收益率，在期权定价问题中根据期权的市场价格计算标的资产的波动性等。

Forexample:

在上例的投资评价中，假设我们求解在该项目的NPV=5000，Rate=10%的时候每年现金流量应为多少。

此时就可以用到单变量求解来解决此问题。

在菜单中点击“工具”-“单变量求解”，在“目标单元格”和“目标值”及“可变单元格”中输入相应参数即可II、EXCEL中公式和函数的使用方法一、常用函数1、统计函数2、金融函数1、终值与现值2、利率与期数3、年金4、名义利率与实际利率二、EXCEL在货币时间价值中的应用常用函数Excel中有70余个统计函数，大致可分为6类：

基本统计类函数，概率分布类，数字特征类，抽样分布类，统计检验类，相关、回归与预测类。

金融计算中常用的统计函数：

计算平均值的AVERAGE（）、计算方差的VAR（）或VARP（）、计算标准差的STDEV（）或STDEVP（）、标准正态分布NORMSDIST（）和NORMSINV（）；财务预测中有时会用到线性回归与预测函数LINEST（）、FORECAST（）、TREND（）、线性方程的斜率函数SLOPE（）、指数回归与预测函数LOGEST（）、GROWTH（）；在进行数据分析时会用到相关系数函数CORREL（）和乘积矩相关系数（也称为广义相关系数）函数RSQ（）。

常用函数Excel内置有50余个金融财务方面的函数。

计算货币时间价值的有9个函数，其中5个通用函数在金融计算中有着非常重要的作用：

现值PV（）、终值FV（）、利率RATE（）、每期现金流量PMT（）、期数NPER（）。

5个固定资产折旧类函数：

直线折旧法SLN（）、年限总和法SYD（）、固定余额递减法DB（）、双倍余额递减法DDB（）以及用余额递减法计算若干期内累积折旧额的VDB（）。

5个投资评价类函数：

净现值NPV（）、内部收益率IRR（）、修正内部收益率MIRR（）以及用于计算非定期现金流量净现值和内部收益率的函数XNPV（）和XIRR（）。

关于债券计算的有26个函数。

EXCEL在货币时间价值计算中的应用现值（现值（PVPV）：

）：

在市场经济条件下，资产的价值随时间变动而不断地变化。

现值（PV，presentvalue），又称“折现值”或“贴现值”，是未来资金按一定利率计算的现在价值，其中，折算过程称为贴现，利率称为贴现率。

通常都按复利计算，故亦称复利现值。

这种计算投资方案现金流量现值的方法称为贴现现金流量法（DCF，discountcashflow）.现值的计算公式：

现值的Excel实现函数形式：

PV（rate，nper，pmt，fv，type）。

例如，未来5年年底均有150元的收入，计算在利率为10%下的总现值，衡量这一项目的投资价值nrFVPV）1（EXCEL在货币时间价值计算中的应用终值（终值（FV）：

）：

终值（FutureValue）是本金按照给定利率在若干计息期后按复利计算的本利和。

终值是基于复利计息而计算出来的，终值与复利是一对对应的概念。

终值的计算公式：

终值的Excel实现函数形式：

FV（rate，nper，pmt，pv，type）。

例如，在未来5年内每年存入150元，在利率为10%下计算5年后存入的总值nrPVFV）1（EXCEL在货币时间价值计算中的应用利率与期数的计算利率与期数的计算：

利率计算的Excel实现函数形式：

RATE（nper，pmt，pv，fv，type，guess）。

例如，有一笔存款150元，5年后存款变为241.58元，计算存款的利率期数计算的Excel实现函数形式：

NPER（rate，pmt，pv，fv，type）。

例如，有一笔存款150元，存款的利率为10%，计算几年后存款变为241.58元EXCEL在货币时间价值计算中的应用年金年金（Annuity）（Annuity）：

年金是指在一定时期内连续发生的等额现金流量。

根据现金流量发生时间的不同，年金可分为普通年金和预付年金等年金现值计算的Excel实现函数形式：

PV（rate，nper，pmt，fv，type）。

例如，未来5年年底均有150元的收入，计算在利率为10%下的总现值，衡量这一项目的投资价值EXCEL在货币时间价值计算中的应用名义利率与实际利率名义利率与实际利率：

在普通复利计算以及技术经济分析中，所给定或采用的利率一般都是年利率，即利率的时间单位是年，在不特别指明时，一年计息一次。

在实际工作中，所给定的利率虽然是年利率，但计息周期可能是比年还短的时间单位，比如计息周期可以是半年、一个季度、一个月、一周或者为一天等等，一年内的计息次数就相应为2次、4次、12次、52次或365次等等。

这样，一年内计算利息的次数不止一次了，在复利条件下每计息一次，都要产生一部分新的利息，因而实际的利率也就不同了。

给定的年利率称为名义利率（nominalrate），名义利率除以一年的计息次数得到期利率（periodicrate），一年的实际利息与本金相比计算的利率称为有效利率（effectiverate），三者之间的关系如下两公式：

实际利率计算的Excel实现函数形式为：

EFFECT（nominal_rate，npery），其中，nominal_rate是名义利率，npery是一年的计息次数名义利率计算的Excel实现函数形式为：

NOMINAL（effect_rate，npery），其中，effect_rate是有效利率mrrnomper1）1（mperrEARIII、如何运用数据透视表进行分析一、分类汇总二、数据透视表和数据透视图分类汇总分类汇总通常用于多维类别数据的汇总比较分析中示例数据表中是债券利率数据。

在该数据表中，首先把债券分为3类：

国债、市政债券和企业债券；每种债券有不同的期限；对于市政债券和企业债券还有不同的评级。

因此，这是一个四维数据表，债券类别、期限、评级和利率。

如果要在同一个工作表中对它们进行分类比较分析，就需要用到分类汇总这类工具目标：

目标：

将债券利率数据按“类别-期限-评级”的优先顺序进行分类汇总比较。

Step1：

从菜单上执行“数据“-”分类汇总“，打开分类汇总对话框Step2：

如右图，在分类字段中选择“类别”，在汇总方式中选择“平均值”，在汇总项中选中“当日利率”，确定即可Step3：

按优先级顺序重复以上操作即可，同时在第二次分类汇总时应取消“替换当前分类汇总”选项分类汇总分类汇总通常用于多维类别数据的汇总比较分析中完成分类汇总后的工作表左侧，增添了一组分级显示的控制按钮、单击标志为加号的按钮，可以打开详细数据的显示；而单击标志为减号的按钮，则可以关闭对应类中的详细数据显示优点：

1、简单，易懂；2、汇总方式较为灵活，可以是计数、最值、标准差等局限性：

1、分类汇总所得结果为静态；2、如果改变分类标准和优先级，必须重新处理；3、当维度较多时处理过程过于繁琐数据透视表数据透视表数据透视表：

数据透视表是Excel中一项比较重要的高级功能，是Excel中从大型、详细的数据集中提取重点信息的最好工具之一，它是一种交互的、交叉制表的Excel报表，可对多种来源的数据进行分类汇总。

由于数据透视表是交互的，通过改变数据透视表中的行和列，查看更多汇总数据。

它是数据分析人员不可缺少的工具数据透视表是将Excel二维表扩展到三维甚至多维的利器。

要创建数据透视表，首先要理解行、列、页的概念，它们相当于三维坐标系中的X、Y、Z轴。

而行、列、页是由数据表中的字段构成的。

我们对上例分类汇总的源数据编制数据透视表页页行行列列数据透视表的高明之处在于其每个维度上均能动态进行分析汇总.点击箭头可得下拉菜单如下图数据透视表数据透视表的制作数据透视表的制作：

Step1：

从菜单上执行“数据“-”数据透视表和数据透视图“，打开数据透视表向导，选择数据来源Step2：

选择分类汇总数据区域Step3：

选择数据透视表生成区域，点击”布局”,在”布局“选项卡中将各字段分别拖至页、行、列及数据区域中。

确定即可生成数据透视表。

数据透视表数据透视图的生成数据透视图的生成：

可在已编制的数据透视表上右击，选择菜单中的“数据透视图”；也可以编制数据透视表时就选择同时生成数据透视图数据透视图可以比数据透视表更加直观地反映数据在多维分类下的比较汇总状况。

同数据透视表一样，数据透视图的页、行和列均提供了下拉列表以动态地分析数据Plus:

数据透视表的页、行和列均可以容纳多个字段使数据透视表能将二维的Excel数据表扩展到多维！

IV、各种财务报表及管理报告的制作与分析一、财务报表的获取与运算1、基本财务比率2、财务评价3、财务预测二、财务报表分析三、投资项目决策中EXCEL的应用1、基本评价指标2、敏感性分析财务报表的获取及运算随着互联网应用的发展，现在国内外都已经有许多专业化的金融服务网站,如新浪财经、Yahoo财经等。

这些金融网站为使用计算机进行金融分析和金融计算提供了很好的数据源。

以Yahoo的金融频道（http:

/）为例，介绍如何获取公众企业的财务报表，并将其导入Excel进行分析和保存在在IE中打开网页，中打开网页，于查询栏输入股票于查询栏输入股票代码，点击“确代码，点击“确定”。

定”。

（以金地以例）（以金地以例）在金地的主页面上点击“资产负债表”等所需报表用从Internet导入数据的方法将各类报表导入并保存财务报表的获取及运算基本财务比率流动比率流动比率是流动资产与流动负债的比值，计算公式为：

流动比率=流动资产/流动负债流动比率可以反映短期偿债能力，一般认为，合理的最低流动比率是2。

计算出来的流动比率，要和同行业平均流动比率、本企业的历史流动比率进行比较，找出过高或过低的原因和还需分析流动资产和流动负债所包括的因素，一般，营业周期、流动资产中的应收帐款和存货的周转速度是影响流动比率的主要因素变现能力比率变现能力比率：

变现能力是企业产生现金的能力，反映变现能力的比率主要有流动比率和速动比率速动比率速动比率是从流动资产中扣除存货再除以流动负债的比值，计算公式为：

速动比率=（流动资产-存货）/流动负债通常，正常的速动比率是1，低于1认为是短期偿债能力偏低基本财务比率营业周期营业周期=存货周转天数+应收帐款周转天数资产管理比率资产管理比率：

资产管理比率，又称运营效率比率，是衡量公司在资产管理方面的效率的财务比率，包括营业周期、存货周转率、应收帐款周转率、流动资产周转率和总资产周转率存货周转天数流动资产中，存活所占的比重较大，存货的流动性将直接影响企业的流动比率，一般用存货周转率或存货周转天数指标反映。

存货周转率是衡量和评价企业购入存货、投入生产、销售收回等各环节管理状况的综合评价指标，使销售成本和平均存货的比值，计算公式为：

存货周转率=销售成本/平均存货存货周转天数=360/存货周转率一般，存活周转速度越快，存货的占用水平越低，流动性越强，存货转换为现金、应收帐款等的速度越快基本财务比率应收帐款周转率应收帐款周转率反映应收帐款周转的速度，应收帐款周转天数是企业从取得应收帐款的权力到收回款项、转换为现金所需要的时间，计算公式为：

应收帐款周转率=销售收入/平均应收帐款应收帐款周