Excel在财务管理中的高级应用.ppt

《Excel在财务管理中的高级应用.ppt》由会员分享，可在线阅读，更多相关《Excel在财务管理中的高级应用.ppt（111页珍藏版）》请在三一办公上搜索。

1、Excel在管理中的应用,主讲内容：,EXCEL的应用基础；,1、Excel的应用基础；,1.1数据录入1.2代码化1.3数据清单、数据结构和数据勾稽关系1.4引用的使用,1.1、数据录入,日期在EXCEL中其实质为数字1900-1-1代表1分数与日期输入5/8在EXCEL中被识别为5月8日分数5/8的输入方式：0+空格+5/8,1.2、代码化（数据标准化）,以电子计算机为计算工具处理财务数据的基本概念。代码化对系统原始数据按一定的规律进行编码的处理。数据处理代码化，缩短了数据项的长度，减少数据占用的存储空间，提高会计数据处理的速度和精度。方便计算机进行排序、分类、汇总等操作。输入方式：+代码

2、；如：现金科目代码输入方式：1001,1.3、数据清单、数据结构和数据勾稽关系,在Excel 中，数据库是作为一个数据清单来看待。我们可以理解数据清单就是数据库。在一个数据库中，信息按记录存储。每个记录中包含信息内容的各项，称为字段。Excel 提供了一整套功能强大的命令集，使得管理数据清单（数据库）变得非常容易。我们可以完成下列工作：排序在数据清单中，针对某些列的数据，我们可以用数据菜单中的排序命令来重新组织行的顺序。可以选择数据和选择排序次序，或建立和使用一个自定义排序次序。筛选可以利用“数据”菜单中的“筛选”命令来对清单中的指定数据进行查找和其它工作。一个经筛选的清单仅显示那些包含了某一

3、特定值或符合一组条件的行，暂时隐藏其它行。数据记录单一个数据记录单提供了一个简单的方法，让我们从清单或数据库中查看、更改、增加和删除记录，或用你指定的条件来查找特定的记录。自动分类汇总利用“数据”菜单的“分类汇总”命令，在清单中插入分类汇总行，汇总你所选的任意数据。当你插入了分类汇总后，Microsoft Excel自动为你在清单底部插入一个“总计”行。,数据结构和数据勾稽关系,数据结构是指同一数据元素类中各数据元素之间存在的关系。数据结构分别为逻辑结构、存储结构、物理结构和数据的运算。数据勾稽关系-数据逻辑结构合理的存储结构、物理结构，能够极大提高工作效率；数据逻辑结构和数据的运算，是编辑公

4、式的基础，善于利用数据逻辑结构和数据的运算，完成工作的自动校验工作。,EXCEL电子表格的规范化,建立和使用Excel数据库表格时，用户应遵循以下的规范：（1）一个数据库最好单独占据一个工作表，避免将多个数据库放到一个工作表上。（2）数据记录紧接在字段名下面，不要使用空白行将字段名和第一条记录数据分开。（3）避免在数据库中间放置空白行或空白列，任意两行的内容不能完全相同。（4）避免将关键数据放到数据库左右两侧，防止数据筛选时这些数据被隐藏。,EXCEL电子表格的规范化,（5）字段名的字体、对齐方式、格式、边框等样式，应当与其他数据的格式相区别。（6）条件区域不要放在数据库的数据区域下方。因为用

5、记录单添加数据时，Excel会在原数据库的下边添加数据记录，如果数据库的下边非空，就不能利用记录单添加数据。（7）不要用合并单元格（8）字与字之间及每一个字前后都不要有空格，即信息库中所有填写内容都不要有空格,EXCEL电子表格的规范化,（9）使用统一的表格样式。比如各部门的考勤计划表,由人资设计，下发，各部门填写完成后收回，对于统一的样式，可以方便的合并，集中处理。（10）使用一致的名称（11）采用计算机认同的数据格式，如日期格式应采用2009-5-23等，而不采用2009.5.23。（12）对数据进行分析处理时应建立副本（不在同一工作薄）进行操作，不破坏原始数据。,EXCEL电子表格的规范

6、化,数据有效性+名称+下拉菜单实验：电子表格的规范化,1.4、引用的使用；,引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿中不同工作表上的单元格和其他工作簿中的数据。引用不同工作簿中的单元格称为链接。相对引用绝对引用（混合引用）链接名称,1.4、引用的使用；,相对引用-在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。随公式位置的改变，其引用的单元格也会相应发生变化。绝对引用-复制公式时Excel不调整引用，如$C$1。包括

7、绝对引用单元格的公式，无论将其复制到什么位置，总是引用特定的单元格。链接-引用不同工作簿中的单元格。每次按F4键时，Excel会在以下组合间切换：绝对列与绝对行（例如，$A$1）；相对列与绝对行(A$1)；绝对列与相对行($A1)以及相对列与相对行(A1)，当切换到用户所需的引用时，按回车键确认即可。,1.4动态引用,OFFSET函数可以对动态数据进行查询，它以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数，其语法格式如下：OFFSET(reference,rows,cols,height,width)可以看出，该函数最多包含

8、五个参数，后两个参数为可选项。其中：Reference作为偏移量参照系的引用区域，它必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值#VALUE!。Rows为相对于偏移量参照系左上角单元格，上（下）偏移的行数。Cols 为相对于偏移量参照系左上角单元格，左（右）偏移的列数。Heigh 为高度，即所要返回的引用区域的行数。Width为宽度，即所要返回的引用区域的列数。,动态引用,COUNTA()SUM()实验：工资统计表B1=SUM(OFFSET(工资!M2,COUNTA(工资!A:A)B2=SUM(OFFSET(工资!L2,COUNTA(工资!A:A),2、公式和函数

9、的综合应用；,由用户自行设计对工作表进行计算和处理的公式。公式的组成：运算单元、运算符、函数及参数、引用、常数、文本、时间等公式中元素的结构或次序决定了最终的计算结果。Excel中的公式遵循一个特定的语法或次序：最前面是等号（=），后面是参与计算的元素（运算数），这些参与计算的元素又是通过运算符隔开的。每个运算数可以是不改变的数值（常量数值）、单元格或引用单元格区域、标志、名称、或工作表函数。,2.1.1公式中的运算符,（1）算术运算符：完成基本的数学运算。（2）比较操作符：比较运算符用于比较两个值。当用操作符比较两个值时，结果是一个逻辑值，不是TRUE就是FALSE。（3）文本运算符：使用和

10、号（&）可以将文本连接起来（4）通配符：“*”全通配符，“?”单通配符（5）引用操作符：引用运算符的作用是确定在公式中需要参与运算的数据在工作表中所处的位置，可以使用三个运算符：冒号、逗号和空格。,2.1.2公式中的运算符,：（冒号）：区域运算符，对两个单元格之间，包括两个单元格在内的所有单元格参与运算。，（逗号）：联合运算符，可将多个引用合并为一个引用。空格：交叉运算符，它是将同时属于两个引用的单元格区域进行引用，即两个单元格引用相重叠的区域。,2.1.3运算符及其优先级,2.1.4公式审核及出错检查,1追踪引用单元格【工具】【公式审核】【追踪引用单元格】【工具】【公式审核】【取消所有追踪箭

11、头】2追踪从属单元格【工具】【公式审核】【追踪从属单元格】【工具】【公式审核】【取消所有追踪箭头】3公式审核工具栏【工具】【公式审核】【显示“公式审核”工具栏】,2.1.5常见的公式错误信息,2.1.6数组公式,数组公式数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。Excel 中有常量和区域两类数组。前者放在“”(按下Ctrl+Shift+Enter 组合键自动生成)内部，而且内部各列的数值要用逗号“,”隔开，各行的数值要用分号“;”隔开。假如你要表示第1 行中的56、78、89 和第2 行中的90、76、80，就应该建立一个2 行3 列的常量数组“56,78,89;

12、90,76,80。区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时，它所引用的矩形单元格区域“B1:B3,A1:A3”就是一个区域数组。,2.1.6数组公式,1.数组公式的输入数组公式与一般公式不同之处在于它被括在大括号（）中，其输入步骤如下：（1）选中一个单元格或者单元格区域。说明：如果数组公式只是返回一个结果，需要选择保存用来保存结果的那一个单元格；如果数组公式返回多个结果，则需要选中需要保留数组公式计算结果的单元格区域。（2）按照前面介绍的公式输入规则，输入公式的内容。（3）公式输完后，按【Ctrl+Shi

13、ft+Enter】组合键结束操作。实验：工资K2:=E2:E37+F2:F37-H2:H37+I2:I37-J2:J37,2.2、函数的运用,Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数是以公式的形式出现，需在函数名称前面输入等号（=）。函数处理数据的方式与公式处理数据的方式是相同的，函数通过引用参数接收数据，并返回结果。大多数情况下返回的是计算的结果，也可以返回文本、引用、逻辑值、数值或工作表的信息。,2.2.1函数的语法格式,函数名（参数1，参数2，参数n）。在使用函数时，应注意以下几个

14、问题：（1）函数名与其后的括号“（”之间不能有空格。（2）当有多个参数时，参数之间要用逗号“，”分隔。（3）参数部分总长度不能超过1024个字符。（4）参数可以是数值、文本、逻辑值、单元格地址或单元格区域地址，也可以是各种表达式或函数。（5）函数中的逗号“，”、引号“”等都是半角字符，而非全角的中文字符。,2.2.2常用函数介绍,1求和函数（1）无条件求和SUM函数该函数的功能是计算所选取的单元格区域中所有数值的和。（2）条件求和SUMIF函数SUMIF函数的功能是根据指定条件对若干单元格求和。（3）SUMPRODUCT函数功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。（4

15、）DSUM函数功能是对数据库表格进行多条件汇总。实验：电器销售,2.2.2常用函数介绍,2条件函数IF函数也称条件函数，它根据参数条件的真假，返回不同的结果。3计数函数COUNT函数：计算给定区域内数值型参数的数目 COUNTIF函数：计算给定区域内满足特定条件的单元格的数目。,2.2.2常用函数介绍,4逻辑函数AND函数：表示逻辑与OR函数：表示逻辑或NOT函数：功能是对参数的逻辑值求反这三个函数一般与IF函数结合使用,2.2.2常用函数介绍,5查找函数（1）LOOKUP函数：返回向量（单行区域或单列区域）或数组中的数值。LOOKUP(lookup_value,lookup_vector,r

16、esult_vector)（2）VLOOKUP函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)（3）HLOOKUP函数：从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。HLOOKUP(lookup_value,table_array,row_index_num,range_lookup),2.2.3用函数快速制作工资条,MOD(number,divisor)求余数函数；Number 为被除数，Divis

17、or 为除数。row()求行号函数column()求列号函数index()引用函数INDEX函数：返回表格或区域中的数值或对数值的引用。INDEX(array,row_num,column_num),找出数据结构关系,2.2.3用函数快速制作工资条,=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,工资!A$1,INDEX(工资!$A:$N,(ROW()+4)/3,COLUMN()相关概念：函数的嵌套实验：工资,2.2.4应收账款的账龄分析,E4=IF(TODAY()-$B4=0,TODAY()-$B4=30,TODAY()-$B4=60,TODAY()-$B4=90,

18、$C4,)实验：应收账款账龄分析,2.2.5文本函数综合应用,LEFT或LEFTB用途：根据指定的字符数返回文本串中的第一个或前几个字符。语法：LEFT(text，num_chars)或 LEFTB(text，num_bytes)。参数：Text 是包含要提取字符的文本串；Num_chars 指定函数要提取的字符数，它必须大于或等于0。Num_bytes按字节数指定由LEFTB 提取的字符数。实例：如果A1=电脑爱好者，则LEFT(A1，2)返回“电脑”，LEFTB(A1，2)返回“电”。,2.2.5文本函数综合应用,LEN 或LENB 用途：LEN 返回文本串的字符数。LENB 返回文本串中

19、所有字符的字节数。语法：LEN(text)或LENB(text)。参数：Text待要查找其长度的文本。注意：此函数用于双字节字符，且空格也将作为字符进行统计。实例：如果A1=电脑爱好者，则公式“=LEN(A1)”返回5，=LENB(A1)返回10。,2.2.5文本函数综合应用,MID 或MIDB 用途：MID 返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB函数可以用于双字节字符。语法：MID(text，start_num，num_chars)或MIDB(text，start_num，num_bytes

20、)。参数：Text 是包含要提取字符的文本串。Start_num 是文本中要提取的第一个字符的位置，文本中第一个字符的start_num 为1，以此类推；Num_chars指定希望MID 从文本中返回字符的个数；Num_bytes指定希望MIDB 从文本中按字节返回字符的个数。实例：如果a1=电子计算机，则公式“=MID(A1，3，2)”返回“计算”，=MIDB(A1，3，2)返回“子”。,2.2.5文本函数综合应用,RIGHT 或RIGHTB 用途：RIGHT 根据所指定的字符数返回文本串中最后一个或多个字符。RIGHTB根据所指定的字节数返回文本串中最后一个或多个字符。语法：RIGHT(t

21、ext，num_chars)，RIGHTB(text，num_bytes)。参数：Text 是包含要提取字符的文本串；Num_chars 指定希望RIGHT 提取的字符数，它必须大于或等于0。如果num_chars 大于文本长度，则RIGHT 返回所有文本。如果忽略num_chars，则假定其为1。Num_bytes 指定欲提取字符的字节数。实例：如果A1=学习的革命，则公式“=RIGHT(A1，2)”返回“革命”，=RIGHTB(A1，2)返回“命”。,2.2.5文本函数综合应用,TRIM 用途：除了单词之间的单个空格外，清除文本中的所有的空格。如果从其他应用程序中获得了带有不规则空格的文本

22、，可以使用TRIM 函数清除这些空格。语法：TRIM(text)。参数：Text是需要清除其中空格的文本。实例：如果A1=FirstQuarterEarnings，则公式“=TRIM(A1)”返回“FirstQuarterEarnings”。,2.2.5文本函数综合应用,实验：人事管理自动判断性别=IF(LEN(E2)=15,IF(MOD(MID(E2,15,1),2)=1,男,女),IF(MOD(MID(E2,17,1),2)=1,男,女)实验：账务处理自动分类汇总G2:=SUM(IF(TRIM(B2)=LEFT(TRIM(凭证清单!$E$2:$E$99),LEN(TRIM(期末余额表!B2

23、),凭证清单!$H$2:$H$99),3、数据透析,数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表，而数据透视图是一种能够根据数据处理需要，查看部分数据的图表对比效果，有些类似前面介绍的动态图表功能，另外，Excel中还可以根据数据透视表制作不同格式的数据透视报告。数据透视分析就是从数据库的特定字段中概括信息，从而方便从各个角度查看、分析数据，并可对数据库中的数据进行汇总统计，它在Excel中的实现工具是数据透视表。,数据透视表可以做什么,1、数据透视表可以解决利用函数公式对超大容量的数据库进行数据统计带来的速度瓶颈。2、数据透视表可以通过行、列和页字段的转换进行多角度的数据分析

24、。3、数据透视表通过对字段的筛选可以对重点关注的内容进行专题分析。4、数据透视表可以不同的工作表和工作簿提取数据,甚至不用打开数据源文件。5、数据透视表可以生成动态报表,保持与数据源的实时更新。6、数据透视表可以通过添加计算字段或计算项进行差异分析。7、数据透视表可以随时调用相关字段的数据源明细数据。8、数据透视图可以自动生成动态分析图表。,什么情况不适用数据透视表,1、数据源中首行的标题字段空缺或出现合并的标题。-创建数据透视表后会出现空白字段2、每列数据的中数据类型不一致。-创建数据透视表后只按一种数据类型分类汇总,会出现数据丢失3、数据源中出现数据断行。-创建数据透视表后会出现数据丢失4

25、、数据源中有合并的单元格。-创建数据透视表后会出现数据丢失5、数据源中有空白的单元格。-创建数据透视表后会出现对数值的默认计数,创建数据透视表操作步骤：,步骤1单击数据源1中的任意一个单元格如C8,单击【插入】选项卡单击【数据透视表】按钮步骤2在弹出的【创建数据透视表】对话框中选择放置数据透视表的位置默认的选择是将数据透视表作为新的工作表，保持此选项不变,单击【确定】按钮即新建一个Sheet生成一张空的数据透视表。如果选择新建的数据透视表存放在已有的工作表,需要选择【现有工作表】单选按钮,在【位置】中确定存放位置,单击【确定】按钮即在指定位置生成一张空的数据透视表。步骤3在【数据透视表字段列表

26、】对话框内将部门字段移动至【列标签】区域、科目名称字段移动至【行标签】区域、勾选【金额】字段。,刷新数据透视表,1、源数据发生变化后,数据透视表并不会自动的刷新数据。要对数据透视表进行刷新,通常的做法就是在数据透视表上单击鼠标右键在弹出的扩展菜单中选择【刷新】命令。2、利用数据透视表工具也可以实现刷新数据透视表。单击数据透视表在【数据透视表工具】【选项】选项卡中单击【刷新】按钮。3、利用功能区的【数据】选项卡也可以实现刷新数据透视表。在【数据】选项卡中单击【全部刷新】按钮。,1.设置在打开文件时进行刷新,如果希望EXCEL在每次打开数据透视表所在的工作表时都进行刷新,可以在数据透视表中单击鼠标

27、右键【数据透视表选项】【数据】勾选【打开文件时刷新数据】。,2.刷新基于外部数据的数据透视表,步骤1 直接单击【数据】选项卡【连接】按钮步骤2 在弹出的【工作簿连接】对话框中单击【属性】按钮步骤3 在弹出的【连接属性】对话框中勾选【允许后台刷新】。如果希望数据透视表每隔一定时间就自动刷新，可以勾选【刷新频率】并设定刷新的间隔时间。如果希望数据透视表在打开时自动刷新，可以勾选【打开文件时刷新数据】。,数据透视表的字段设置,默认设置下,EXCEL对数据透视表数据区的数字字段应用求和函数,而对非数字字段或数据源中有空白单元格的情况下应用计数函数。在【数据透视表字段】对话框中有很多可供选择的计算方式。

28、1、改变数据透视表字段的汇总方式2.对同一字段应用多种汇总方式,使用自定义的值显示方式,除了上面列举的标准汇总函数外，EXCEL也提供了一套自定义的计算。通过他们，用户可以在数据透视表数据区中显示每项占同行或同列总值的百分比，或创建动态汇总，或显示每个数值占基准值的百分比。,使用自定义的值显示方式,操作方法：在“求和项:金额2字段上单击鼠标右键【字段设置】【值显示方式】选项卡单击值显示方式的下拉按钮选择占总和的百分比。,数据组合与取消组合,数据透视表通过对数字、日期、文本等不同数据类型的数据项采取多种组合方式，大大增强了数据透视表分类汇总的适应性。步骤1 在日期列(行标签)字段项中的任意一个单

29、元格上单击鼠标右键【组合】。(此外，也可以利用【数据透视表工具】【选项】【将所选内容分组】按钮)步骤2 在【分组】对话框中,步长选中“月”。步骤3 单击【确定】按钮。,数据透视表的计算字段和计算项,1、在数据透视表中插入计算字段计算字段是通过对数据透视表中现有的字段执行计算后得到的新字段。2、在数据透视表中插入计算项计算项是在数据透视的现有字段中插入新的项，通过对该字段的其他项执行计算后得到该项的值。,1、在数据透视表中插入计算字段,计算字段是通过对数据透视表中现有的字段执行计算后得到的新字段。,2、在数据透视表中插入计算项,计算项是在数据透视的现有字段中插入新的项，通过对该字段的其他项执行计

30、算后得到该项的值。,数据透析多重区域合并,用2007,发现数据透视表里面没有多重区域合并的选项了,那有什么办法用2007实现多表合并?快捷键ALT+D+P，调用数据透视表向导。实验：电器销售,4、数据的假设分析,手动假设分析单变量假设分析双变量假设分析多变量假设分析（方案管理器）,4.1手动假设分析,“单变量求解”是一组命令的组成部分，如果已知单个公式的预期结果，而用于确定此公式结果的输入值未知。实验：假设分析-单变量求解函数：PMT(rate，nper，pv，fv，type),4.2单变量假设分析,单变量模拟运算实验：假设分析-单变量模拟运算,4.3双变量假设分析,双变量数据表中的两组输入数

31、值使用同一个公式。这个公式必须引用两个不同的输入单元格。实验：假设分析双变量模拟运算,4.4多变量假设分析（方案管理器）,方案是 Excel 保存在工作表中并可进行自动替换的一组值。可以使用方案来预测工作表模型的输出结果。同时还可以在工作表中创建并保存不同的数值组，然后切换到任意新方案以查看不同的结果。实验：方案,5、EXCEL的预测应用,EXCEL的预测函数EXCEL的数据分析工具预测销售预测的准确度分析规划求解工具预测,5.1 预测概述,预测是用科学的方法预计、推断事物发展的必要性或可能性的行为，即根据过去和现在预计未来，由已知推断未知的过程。5.1.1 预测分析步骤5.1.2 预测分析方

32、法5.1.3 预测分析内容,5.1.1 预测分析步骤,（1）确定预测目标。（2）收集和整理资料。（3）选择预测方法。（4）分析判断。（5）检查验证。（6）修正预测值。（7）报告预测结论。,5.1.2 预测分析方法定量预测法,1定量预测法定量预测法是在掌握与预测对象有关的各种要素的定量资料的基础上，运用现代数学方法进行数据处理，据以建立能够反映有关变量之间规律性联系的各类预测模型的方法体系，可分为趋势外推分析法和因果分析法。,5.1.2 预测分析方法定量预测法,（1）趋势外推分析法。这种方法是将时间作为制约预测对象变化的自变量，把未来作为历史的自然延续，属于按事物的自身发展趋势进行预测的动态预测

33、方法。该方法的基本原理是：企业过去和现在存在的某种发展趋势将会延续下去，而且过去和现在发展的条件同样适用于未来，可以将未来视为历史的自然延续。因此，该方法又被称为时间序列分析法。（2）因果分析法。这种方法是根据变量之间存在的因果关系函数，按预测因素的未来变动趋势来推测预测对象（即因变量）未来的相关预测方法。该方法的基本原理是：预测对象受到许多因素的影响，这些因素之间存在着复杂的关系，通过对这些变量内在规律性的研究可以建立一定的数学模型，在已知自变量的条件下，可利用模型直接推测预测对象的未来值。,5.1.2 预测分析方法定性预测法,2定性预测法定性预测法是由有关方面的专业人员根据个人经验和知识，

34、结合预测对象的特点进行综合分析，对事物的未来状况和发展趋势做出推测的预测方法。它一般不需要进行复杂的定量分析，适用于缺乏完备的历史资料或有关变量之间缺乏明显的数量关系等情况下的预测。实际工作中常常将二者结合应用，相互取长补短，以提高预测的准确性和预测结论的可信度。,5.1.3 预测分析内容,预测分析的基本内容包括：（1）销售预测。（2）利润预测。（3）成本预测。（4）资金预测。,5.2 销售预测,狭义的销售预测是对销售量的预测。用于销售量预测的常用方法有判断分析法、趋势外推分析法、因果分析法和产品寿命周期推断法等。其中，判断分析法和产品寿命周期推断法属于定性预测法，趋势外推分析法和因果分析法属

35、于定量预测法。5.2.1 趋势外推分析法:5.2.2 因果分析法,5.2.1 趋势外推分析法,1算术平均法2移动平均法,1算术平均法,算术平均法又称简单平均法，它是直接将若干时期实际销售业务量的算术平均值作为销售预测值的一种预测方法。这种方法的优点是计算过程很简单，缺点是没有考虑远近期销售业务量的变动对预测销售状况的影响程度的不同，从而使不同时期资料的差异简单平均化，所以，该方法只适用于对各期销售业务量比较稳定，且没有季节性变化的食品和日常用品等的销售预测。,2移动平均法,（1）一次移动平均法。一次移动平均法根据时间序列逐项移动，依次计算包含一定项数的序时平均数，形成一个序时平均时间数序列，据

36、以进行预测。其预测模型的计算公式为：从上述公式可以看出，第t期的移动平均数只能作为t+1期的预测值，如果要预测数期以后的值，这种方法就无能为力了。但是，在实际工作中，当企业或商店要逐月预测下个月的成百上千种产品的进货或销售情况时，这种方法还是很适用的。,2移动平均法一次移动平均法,对于一次移动平均序列值的计算，可以使用递推算法来减少工作量。递推算法的计算公式为：上述公式说明，第t+1期的预测值是在第t期预测值t的基础上加上一个修正值得出的。不难看出，修正项的作用和n的大小有关。在实际应用中，移动平均时段n的选择带有一定的经验性，n过长或过短，各有利弊，可以多取几个n值，将取不同的n值得出的预测

37、结果分别同实际值比较，计算其预测误差，最后选用预测误差最小的n值。,一次移动平均法操作步骤,1在EXCEL中，以列方式将数据输入到工作表中，并建立一次移动平均法预测模型的基本结构。2打开“工具”菜单，选择“数据分析”命令，打开“数据分析”对话框。3在“数据分析”对话框中，选择“移动平衡”选项，单击“确定”，会打开“移动平均”对话框，4在“输入区域”中输入各月份销售量。5在“间隔”中输入“3”，即表示n=3。6在输出区域中输入“C3”。7在对话框中选择“图表输出”。8单击“确定”。9将C5：C13单元格区域复制到D6：D14单元格区域中。10选定E6单元格，输入公式：=（D6-B6）2。然后将此

38、公式分别复制到E7：E14单元格区域。,2移动平均法二次移动平均法,（2）二次移动平均法二次移动平均法是对时间序列计算一次移动平均数后，再对一次移动平均数序列进行一次移动平均运算。二次移动平均法的计算公式为：,2移动平均法二次移动平均法,二次移动平均预测模型为：二次移动平均数并不能直接用于预测，其目的是用来求出平滑系数。求解平滑系数估计值的公式为：,2移动平均法趋势平均法,趋势平均法是指在移动平均法计算n期时间序列移动平均值的基础上，进一步计算趋势值的移动平均值，进而利用特定基期销售量移动平均值和趋势值移动平均值来预测未来销售量的一种方法。其计算公式为：预测销售量=基期销售量移动平均值+基期趋

39、势值移动平均值*基期与预测期的时间间隔；某期的趋势值=该期销售量移动平均值-上期销售量移动平均值；基期趋势值移动平均值=最后一个移动期趋势值之和/趋势值移动的期数。,5.2EXCEL的数据分析工具预测,加载分析工具库的方法【工具】【加载宏】【分析工具库】实验：销售预测,5.3 利润预测,5.3.1 目标利润预测5.3.2 利润敏感性分析5.3.3 概率分析法在利润预测中的应用,5.3.1 目标利润预测,（1）调查研究，确定利润率标准。（2）计算目标利润基数。将选定的利润率标准乘上企业预期应该达到的有关业务量及资金指标，便可测算出目标利润基数。其计算公式为：目标利润基数=有关利润标准*相关指标（

40、3）确定目标利润修正值。（4）最终下达目标利润并分解落实纳入预算体系。最终下达的目标利润应该为目标利润基数与修正值的代数和，即：最终下达的目标利润=目标利润基数+目标利润修正值,5.3.2 利润敏感性分析,1利润敏感性分析的主要任务利润敏感性分析的主要任务是计算有关因素的利润灵敏指标，揭示利润与有关因素之间的相对数量关系，并利用灵敏指标进行利润预测。2利润敏感分析的假定条件利润敏感性分析的假定条件如下：（1）有限因素的假定。（2）单独变动的假定。（3）利润增长的假定。（4）同一变动幅度的假定。,5.3.2 利润敏感性分析,3利润敏感性指标的计算利润敏感性分析的关键是计算利润受各个因素影响的灵敏

41、度指标。某因素的利润灵敏度指标为该因素按上述假定单独变动1%后使利润增长的百分比指标，其计算公式为：第I个因素利润灵敏度指标（Si）=该因素的中间变量基数/利润基数*1%=Mi/P*1%。式中的中间变量是指同时符合以下两个条件的计算替代指标，即中间变量的变动率必须等于因素的变动率，中间变量变动额的绝对值必须等于利润的变动额。实验：利润敏感性分析,5.3.3 概率分析法在利润预测中的应用,蒙特卡罗模拟方法是一种比较实用的模拟预测方法。其基本原理是，通过对有关参数进行大量的模拟实验来了解所预测的指标的分布情况，在此基础之上做出合理的预测。实验：蒙特卡罗模拟RAND()RANDBETWEEN(),5

42、.4 成本预测与资金需要量预测,5.4.1 成本预测5.4.2 资金需要量预测,5.4.1 成本预测,成本预测是根据企业未来的发展目标和现实条件，参考其他资料，利用专门方法对企业未来成本水平及其发展趋势所进行的推测与估算。,5.4.2回归分析,（1）一元线性回归模型（2）一元非线性回归模型,5.4.2回归分析,（3）多元线性回归模型（4）多元非线性回归模型 例如，柯柏道格拉斯生产函数 式中：L和K分别为劳动力和固定资本,5.4.3EXCEL的预测函数,1 LINEST函数 2 LOGEST函数 3 TREND函数 4 GROWTH函数 5 FORECAST函数 6 SLOPE函数 7 INTE

43、RCEPT函数,5.4.3.1 LINEST函数,LINEST使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。LINEST(known_ys,known_xs,const,stats)直线的公式为：y=mx+b ory=m1x1+m2x2+.+b（如果有多个区域的 x 值）,5.4.3.2 LOGEST函数,在回归分析中，计算最符合数据的指数回归拟合曲线，并返回描述该曲线的数值数组。因为此函数返回数值数组，故必须以数组公式的形式输入。LOGEST(known_ys,known_xs,const,stats)此曲线的公式为：y=b*mx 或y=(b*(m1x1)*(m2x2)*

44、_)（如果有多个 x 值）,5.4.3.3 TREND函数,返回一条线性回归拟合线的值。即找到适合已知数组 known_ys 和 known_xs 的直线（用最小二乘法），并返回指定数组 new_xs 在直线上对应的 y 值。TREND(known_ys,known_xs,new_xs,const),5.4.3.4 GROWTH函数,根据现有的数据预测指数增长值。根据现有的 x 值和 y 值，GROWTH 函数返回一组新的 x 值对应的 y 值。可以使用 GROWTH 工作表函数来拟合满足现有 x 值和 y 值的指数曲线。语法GROWTH(known_ys,known_xs,new_xs,co

45、nst),5.4.3.5 FORECAST函数,根据已有的数值计算或预测未来值。此预测值为基于给定的 x 值推导出的 y 值。已知的数值为已有的 x 值和 y 值，再利用线性回归对新值进行预测。可以使用该函数对未来销售额、库存需求或消费趋势进行预测。语法FORECAST(x,known_ys,known_xs),5.4.3.6 SLOPE函数,返回根据 known_ys 和 known_xs 中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的重直距离与水平距离的比值，也就是回归直线的变化率。语法SLOPE(known_ys,known_xs),5.4.3.7 INTERCEPT函数,利

46、用现有的 x 值与 y 值计算直线与 y 轴的截距。截距为穿过已知的 known_xs 和 known_ys 数据点的线性回归线与 y 轴的交点。当自变量为 0（零）时，使用 INTERCEPT 函数可以决定因变量的值。例如，当所有的数据点都是在室温或更高的温度下取得的，可以用 INTERCEPT 函数预测在 0C 时金属的电阻。语法INTERCEPT(known_ys,known_xs)实验：利润与成本预测,5.5规划求解,在计划管理中，经常会遇到各种规划问题，例如：人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批次的确定等。这类问题有一个共同要求，那就是：如何合理利用

47、各种约束资源实现最佳的经济效益，也就是达到常量最高、利润最大、成本最低、费用最省等目标。这就是本节要解决的在约束条件下寻求目标函数最优的规划问题。一般来讲，这类规划问题都具有如下三个特点：（1）所求问题都有单一的目标，如求生产的最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间以及求其他目标函数的最佳值等。（2）总是有明确的不等式约束条件。比如库存不能低于一定的数量，否则造成原料短缺或产品缺货；生产产品不能超过一定额度，否则会造成商品积压等。（3）问题都有直接或间接影响约束条件的一组输入值。,5.5规划求解,规划求解工具用于解决复杂的方程求值及各类线性或非线形有约束优化问题。规

48、划问题种类繁多。从数学角度来看，规划问题都有下述共同特征，这些特征也构成了Excel规划求解工具界面中的主要部分：决策变量：每个规划问题都有一组需要求解的未知数，称作决策变量。这组决策变量的一组确定值就代表一个具体的规划方案。在Excel规划求解工具中，可变单元格代表的是决策变量。约束条件：对于规划问题的决策变量通常都有一定的限制条件，称作约束条件。约束条件可以用与决策变量有关的不等式或等式来表示。目标：每个问题都有一个明确的目标，如利润最大或成本最小。目标通常可用与决策变量有关的函数表示。在Excel规划求解工具中，包含公式的目标单元格代表的是目标函数。,产品生产最优安排,规划求解：（1）可

49、以建立如下的（LP）模型 max(y)=140*X1+180*X26X1+9X2=0X1、X2,int实验：产品生产最优安排,产品生产最优安排,运输模型,运输模型,（1）根据运输问题的数学模型可以建立如下的（LP）模型 min w=1.5*X11+2*X12+0.3*X13+3*X14+7*X21+0.8*X22+1.4*X23+2*X24+1.2*X31+0.3*X32+2*X33+2.5*X34 X11+X12+X13+X14=9 X21+X22+X23+X24=8 X31+X32+X33+X34=6 X11+X21+X31=7 X12+X22+X32=5 X13+X23+X33=8 X1

50、4+X24+X34=3 Xij0且Xij为整数（i=1，2，3；j=1，2，3，4）,6、EXCEL运用中的几个小技巧,把Excel的表格或图表复制到PPT中实现 PPT及Word中来自Excel 的数据的自动更新汇总不同工作表的数据区域汇总多个工作簿的工作表处理外部数据,处理外部数据,如何打开非Excel格式的外部数据文件通过将相关外部数据文件转换为TXT文本文件，再导入；DBF、DB等数据库文件部分可直接导入，无法导入的，可在相应程序中进行处理后导入。,7、EXCEL VBA的灵活运用；,VBA程序设计基础数据类型删除奇数行高效的按单元格颜色求和自定义函数,7.1VBA程序设计基础,录制简