Excel在会计与财务管理中的应用 课件 项目十 坏账成因分析

Excel在会计与财务管理中的应用项目十坏账成因分析(1)理解单因素方差分析方法的基本概念与分析的步骤；(2)掌握Excel数据分析工具中方差分析模块的单因素分析方法的具体应用；(3)学会应用Excel数据分析工具中的方差分析方法对企业财务管理中的其他具体问题进行研究。学习目标为了提高销售收入，提高市场占有率，企业在销售实践中普遍采用赊销的方式，但赊销在很大程度上是基于对交易对手的商业信用，由于在交易中存在很多信息不对称行为，企业对交易对手的了解不够详细，对其还款能力和还款意愿评估做不到尽善尽美，因此会不可避免地形成坏账。客观来讲，坏账的成因是多方面的，每个企业的坏账也有自身具体的特点。本项目主要以具体案例的形式，使用Excel2010中的方差分析方法，对企业的坏账影响因素进行分析，旨在发现其中的规律，为企业做好销售回款管理提供有益的参考借鉴，进而为企业经营管理提供必要的智力支持。情景引入目录CONTENTS单因素影响分析1可重复双因素方差分析2任务一单因素影响分析知识准备试验中要考察的指标称为试验指标，影响试验指标的条件称为因素，因素所处的状态称为水平，若试验中只有一个因素改变则称为单因素试验，若有两个因素改变则称为双因素试验，若有多个因素改变则称为多因素试验。方差分析就是对试验数据进行分析，检验方差相等的多个正态总体均值是否相等，进而判断各因素对试验指标的影响是否显著。方差分析根据影响试验指标条件的个数可以区分为单因素方差分析、双因素方差分析和多因素方差分析。1.单因素方差的基本概念知识准备在方差分析中，我们将要考察的对象的某种特征称为试验指标，影响试验指标的条件称为因素。因素可分为两类，一类是人们可以控制的(如原材料、设备、学历、专业等因素)，另一类是人们无法控制的(如员工素质与机遇等因素)。1.单因素方差的基本概念知识准备下面所讨论的因素都是指可控制因素。每个因素又有若干个状态可供选择，因素可供选择的每个状态称为该因素的水平。如果在一项试验中只有一个因素在改变，则称为单因素试验;如果多于一个因素在改变，则称为多因素试验。因素常用大写字母A，B，C，…表示，因素A的水平用A1、A2、A3表示。1.单因素方差的基本概念知识准备(1)各个观察值是独立的，即各组观察数据是从相互独立的总体中抽取的。2.假设前提(2)每个总体都应服从正态分布且方差相等，即各组观测数据是从具有相同方差的正态分布总体中抽取的简单随机样本。知识准备在单因素试验中，假设因素A共有r个水平，r表示单因素的分类数目，每个水平的样本容量为n，则共有nr个观察值，单因素试验的结果以r行n列表示，构成单因素分析的数据结构。3.单因素方差分析的数据结构知识准备(1)提出假设。设因素有不同水平A1,A2,.......,Ar,各水平对应的总体服从正态分布N，在水平Ar,进行Nr,次试验。假定所有试验都是独立的，因为在水平的样本观测值与总体服从相同的分布，如果因素A对试验结果影响不显著，则所有样本观测值都可以看作来自同一总体，即各自变量取值分类组的均值相等。4.单因素方差分析的步骤知识准备提出假设:4.单因素方差分析的步骤原假设H0:U1=U2=…=Ur,即因素A对观测变量无显著影响。备择假设H1:U1=U2=…=Ur,不全相等，即因素A对观测变量有显著影响。若拒绝原假设，则表示自变量对因变量有显著影响；若接受原假设，则表示自变量对因变量没有显著影响。知识准备4.单因素方差分析的步骤(2)构造检验统计量。(3)离差平方和。①总离差平方和也称总平方和(SumofSquaresforTotal,SST)，反映全部试验数据之间的离散状况，是全部观察值与总平均值的离差平方和。②组间离差平方和也称因素A平方和(SumofSquaresforFactorA,SSA)，反映各个总体的样本均值之间的差异程度，即每组数据均值和总平均值之间的离差平方和。③组内离差平方和也称误差项离差平方和(SumofSquaresforError,SSE)，反映每个样本各观测值之间的离散状况，即组内数据和组内平均值之间的随机误差。知识准备(4)判断与结论。有两种方法可以用来判定是否接受原假设H0:4.单因素方差分析的步骤将统计量F与给定的显著性水平a的临界值Fa比较，可以做出拒绝或接受原假设H0的判断:若F≥Fa，则拒绝原假设H0，表明因素A对观察值有显著影响;若F<Fa，则接受原假设H0，表明因素A对观察值无显著影响。知识准备利用F值计算出P值，当P<a时，拒绝，表明均值之间有显著差异，即因素4对观察值有显著影响；当P>a时，则接受原假设H0，表明均值之间无显著差异，因素A对观察值无显著影响。4.单因素方差分析的步骤任务目标(1)了解单因素方差分析；(2)学会使用Excel2010数据分析工具中的方差分析模块单因素方差分析命令；(3)学会使用单因素方差分析方法对企业坏账影响因素进行研究。任务资料福源公司是一家生产制造企业，自2010年以来，公司主要经营的产品是A、B、C。公司2010-2021年A、B、C三种主营业务产品的坏账数据如表10-1所示。要求：利用图表分析方法分析福源公司2010—2021年主营业务产品坏账数据。任务操作一、输入数据打开Excel，将工作簿保存为“坏账成因.xlsm”。将Sheetl工作表重命名为“坏账成因分析”，建立“坏账成因分析”工作表，在其中输入福源公司2010-2021年主营业务产品坏账数据，如图10-1所示。图10-1坏账分析数据任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品坏账数据这里使用“带平滑线和数据标记的散点图”和“雷达图”比较适合。(一)带平滑线和数据标记的散点图(1)选中A2:D14单元格区域,选择“插入”选项卡,执行“图表”组“散点图”中的“带平滑线和数据标记的散点图”命令,如图10-2所示。任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品坏账数据(一)带平滑线和数据标记的散点图图10-2选择带平滑线和数据标记的散点图任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品坏账数据(一)带平滑线和数据标记的散点图图10-3分产品坏账成因分析图(2)打开基于福源公司2010—2021年主营业务产品坏账数据的带平滑线和数据标记的散点图，如图10-3所示。任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品坏账数据(一)带平滑线和数据标记的散点图(3)将标题修改为“分产品坏账成因分析”,单击分产品坏账成因分析图中的横坐标轴，打开如图10-4所示的“设置坐标轴格式”对话框，在“坐标轴选项”的“最小值”中输入2010.0，在“最大值”中输入2021.0，在“主要刻度单位”中输入2.0，在“次要刻度单位”中输入1.0。(4)单击“关闭”按钮，最终形成的分产品坏账成因分析图如图10-5所示。任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品坏账数据(一)带平滑线和数据标记的散点图图10-5最终形成的分产品坏账成因分析图图10-4“设置坐标轴格式”对话框任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品坏账数据(一)带平滑线和数据标记的散点图结论:从带平滑线和数据标记的散点图中可以较为直观地看出，A、B、C三种产品之间还是存在一定差异的,尤其是自2010年以来,B产品坏账金额要高于A产品坏账金额,再高于C产品坏账金额。任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品坏账数据(二)雷达图(1)选中A2:D14单元格区域，选择“插入”选项卡，执行“图表”组中“其他图表下的“雷达图”命令，如图10-6所示。(2)打开如图10-7所示的基于福源公司2010-2021年A、B、C三种主营业务产品坏账数据的雷达图。任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品坏账数据图10-6选择雷达图图10-7分产品坏账成因分析雷达图任务操作三、通过单因素方差分析方法分析福源公司2010—2021年A、B、C三种主营业务产品坏账数据与主营业务产品品种之间的关系(1)选择单因素方差分析工具。选择“数据”选项卡,执行“分析”组中的“数据分析”命令，打开“数据分析”对话框，在“分析工具”列表框中选择“方差分析:单因素方差分析”选项，如图10-8所示。图10-8“数据分析”对话框任务操作三、通过单因素方差分析方法分析福源公司2010—2021年A、B、C三种主营业务产品坏账数据与主营业务产品品种之间的关系(2)单击“确定”按钮,打开“方差分析:单因素方差分析”对话框。首先设置“输入’内容，单击“输入区域”后面的折叠按钮，并选中$B$2:$D$14单元格区域。因为本例中研究的是A、B、C三种产品之间的方差分析，而且三者数据都以“列”的形式在Excel2010中展示,所以在“分组方式”选项组中要选中“列”复选框。任务操作三、通过单因素方差分析方法分析福源公司2010—2021年A、B、C三种主营业务产品坏账数据与主营业务产品品种之间的关系此外,因为在“输入区域中选中了B2:D14单元格区域，所以数据中是包含标志项的，需要选中“标志位于第一行复选框，其他采用系统默认设置。然后设置“输出选项”，在“输出区域”中选中$AS16单元格区域。如果选中“新工作表组”单选按钮，则表示将输出结果显示在一个新的工作表上，如图10-9所示。图10-9“方差分析：单因素方差分析”对话框任务操作三、通过单因素方差分析方法分析福源公司2010—2021年A、B、C三种主营业务产品坏账数据与主营业务产品品种之间的关系(3)单击“确定”按钮，得到针对A、B、C三种产品之间的单因素方差分析结果，如图10-10所示。图10-10单因素方差分析结果任务操作三、通过单因素方差分析方法分析福源公司2010—2021年A、B、C三种主营业务产品坏账数据与主营业务产品品种之间的关系结论:“方差分析:单因素方差分析”结果图包括两部分，第一部分是参与方差分析的变量(包括A产品坏账金额、B产品坏账金额和C产品坏账金额)的描述性分析统计结果，其中A产品坏账金额观测样本数为12，总和为10579.92，平均值为881.66，方差为119924.9255;B产品坏账金额观测样本数为12，总和为13199.76，平均值为1099.98，方差为229170.1156;C产品坏账金额观测样本数为12，总和为7488.25，平均值为624.0208333，方差为20830.83475。任务操作三、通过单因素方差分析方法分析福源公司2010—2021年A、B、C三种主营业务产品坏账数据与主营业务产品品种之间的关系第二部分为单因素方差分析结果，单因素方差分析的原假设为参与分析的变量之间不存在显著差异，从结果中可以明确地看出单因素方差分析的F值为5.524004344，显著性P值为0.008525039，远远小于0.05，说明要显著拒绝原假设，即福源公司2010-2021年A产品坏账金额、B产品坏账金额和C产品坏账金额等主营业务产品坏账数据与主营业务产品品种之间是有着显著影响关系的，或者说，不同主营业务产品品种产生的坏账金额之间有着非常显著的不同。任务二可重复双因素方差分析知识准备(1)无重复的双因素方差分析:假定因素A和因素B的效应之间是相互独立的，不存在交互关系，也称无交互作用的双因素方差分析。(2)可重复的方差分析:假定A、B两个因素不是独立的，而是相互起作用的，并且两个因素共同起作用的结果不是其各自作用的简单相加，而是会产生一个新的效应(如效果会成倍增加)，也称有交互作用的双因素方差分析。本任务集中介绍可重复双因素方差分析的理论与操作实例。一、双因素方差分析的类型知识准备假设两个因素分别是A和B，因素A共有n个水平，因素B共有k个水平，在水平组合(Ai,Bj)下的试验结果Xij,服从N,i=1,…,n;B=1,…,k。假设这些试验结果相互独立,要对两个因素的交互作用进行分析，每个水平组合下需要至少进行两次试验，若在每个水平组合(Ai,Bj)下重复t次试验，则每次试验的观测值用Xijr(r=1,…,t)表示。二、可重复的双因素方差分析的数据结构知识准备与无重复的双因素方差分析的模型基本一样，只是可重复的双因素方差分析需要考虑两个因素之间的交互作用，因此需要提出假设以检验两个因素之间的交互效应。在可重复的双因素方差分析中,如果用U表示均值,则U1,U2,…,Un,分别表示因素A分类组的均值,U1,U2,…,Uk分别表示因素B分类组的均值;用Pij

表示因素A的第i水平与因素B的第j水平的交互效应。在可重复的双因素方差分析中，要检验的假设有三个，即分别对因素A和因素B以及因素A、B之间的交互效应提出假设。三、可重复的双因素方差分析的步骤(一)提出假设知识准备原假设H01：U1=U2=…=Un，即因素A对观测变量无显著影响。三、可重复的双因素方差分析的步骤(一)提出假设1.对因素A的假设备择假设H11：U1,U2,…,Un不全相等，即因素A对观测变量有显著影响。知识准备原假设H02：U1=U2=…=Uk，即因素B对观测变量无显著影响。三、可重复的双因素方差分析的步骤(一)提出假设2.对因素B的假设备择假设H12：U1,U2,…,Uk不全相等，即因素B对观测变量有显著影响。知识准备原假设H03：对一切i和j，有Pij=0，即因素A与因素B之间不存在交互效应。三、可重复的双因素方差分析的步骤(一)提出假设3.对因素A和因素B的交互效应的假设备择假设H13：对一切i和j，Pij不全为零，即因素A与因素B之间存在交互效应。知识准备由于相互作用的存在，可重复的双因素方差分析要比无重复的双因素方差分析多一个交互作用项平方和，此时总离差平方和SST将被分解为四部分:SSA、SSB、SSAB和SSE，分别代表因素A的组间差异，因素B的组间差异，因素A、B的交互效应和随机误差的离散状况。三、可重复的双因素方差分析的步骤(二)构造检验统计量知识准备与无重复的双因素方差分析一样，在可重复的双因素方差分析中，既可以将统计量的值F与临界值Fa,进行比较，从而做出拒绝或接受原假设的决策；也可以利用F值计算出P值，再进行判断。其具体判断方法如下:利用F值进行判断:若FA≥Fa，则拒绝原假设H01，表明因素A对观察值有显著影响,否则接受原假设H01；若FB≥Fa,则拒绝原假设H02,表明因素B对观察值有显著影响,否则接受原假设H02；若FAB≥Fa，则拒绝原假设H03，表明因素A、B的交互效应对观察值有显著影响，否则接受原假设H:。同理，利用P值进行判断:当P<a时，拒绝H0;当P>a时，接受原假设H0。三、可重复的双因素方差分析的步骤(三)判断与结论任务目标(1)理解Excel2010中可重复双因素方差分析的基本原理；(2)掌握可重复双因素方差分析方法在Excel2010中的实现；(3)学会应用这些统计分析方法解决实际中遇到的财务管理问题。任务资料福源公司是一家生产制造企业，自2010年以来，公司主要经营的产品是A、B、C。2010-2021年A、B、C三种主营业务产品分客户所在地区的坏账数据如表10-2所示（完整表格查看教材297页）。任务操作使用Excel2010打开“坏账成因.xlsm”工作簿，新建或者选择其中一张空白工作表并重命名为“坏账成因分析(双因素)”，建立“坏账成因分析(双因素)”工作表，在其中输入福源公司2010-2021年主营业务产品分客户所在地区坏账数据，如图10-11所示。一、输入数据

图10-11输入相关数据任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品分客户所在地区坏账数据

图10-12选择“数据透视图”命令这里主要介绍Excel2010中数据透视图的用法。(1)在“坏账成因分析(双因素)”工作表中选中A2:E37单元格区域，选择“插入选项卡执行“表格”组中“数据透视表”下的“数据透视图”命令，如图10-12所示。任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品分客户所在地区坏账数据

图10-13创建数据透视图(2)打开“创建数据透视表及数据透视图”对话框，选中“新工作表”(命名为“数据透视图”)单选按钮，单击“确定”按钮，如图10-13所示。任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品分客户所在地区坏账数据(3)打开“数据透视表字段列表”对话框,把“客户所在地区”移到“轴字段(分类)”列表框,把“A产品坏账金额(万元)”“B产品坏账金额(万元)”“C产品坏账金额(万元)”移到“数值”列表框，如图10-14所示。(4)在“数值”列表框中选择“求和项：A产品坏账金额(万元)”，选择如图10-15所示的“值字段设置”命令。任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品分客户所在地区坏账数据

图10-14选择数据透视表字段

图10-15选择“值字段设置”命令任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品分客户所在地区坏账数据(5)打开如图10-16所示的“值字段设置”对话框,“计算类型”选择“平均值”，即设置为“平均值项:A产品坏账金额(万元)”。按照同样的操作方式，把“求和项:B产品坏账金额(万元)”设置为“平均值项:B产品坏账金额(万元)”，把“求和项:C产品坏账金额(万元)”设置为“平均值项:C产品坏账金额(万元)”。

图10-16设置值字段任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品分客户所在地区坏账数据

图10-17数据透视图(6)设置完毕后，即显示如图10-17所示的基于福源公司2010-2021年A产品坏账金额分客户所在地区平均值项、B产品坏账金额分客户所在地区平均值项、C产品坏账金额分客户所在地区平均值项的数据透视图。任务操作二、通过图表分析方法分析福源公司2010—2021年主营业务产品分客户所在地区坏账数据结论:从数据透视图中可以非常明显地看出，从主营业务产品品种来看，东部地区中B产品坏账金额平均值项要高于A产品坏账金额平均值项，再高于C产品坏账金额平均值项;西部地区中B产品坏账金额平均值项要高于A产品坏账金额平均值项，再高于C产品坏账金额平均值项。任务操作三、通过可重复双因素方差分析方法分析福源公司2010—2021年A产品坏账金额、B产品坏账金额、C产品坏账金额等主营业务产品坏账数据与主营业务产品品种、客户所在地区之间的关系(1)选择可重复双因素方差分析工具。选择“数据”选项卡，执行“分析”组中的“数据分析”命令，打开“数据分析”对话框，在“分析工具”列表框中选择“方差分析:可重复双因素分析”选项，如图10-18所示。

图10-18选择分析工具任务操作三、通过可重复双因素方差分析方法分析福源公司2010—2021年A产品坏账金额、B产品坏账金额、C产品坏账金额等主营业务产品坏账数据与主营业务产品品种、客户所在地区之间的关系(2)单击“确定”按钮,打开“方差分析:可重复双因素分析”对话框。首先设置“输入”内容，单击“输入区域”后面的折叠按钮，并选中$B$2:$E$37单元格区域。在本任务中研究的是A产品坏账金额、B产品坏账金额、C产品坏账金额以及分客户所在地区之间的方差分析，其中三个主营业务品种都以不同的列进行展示，称为“列数据”;客户所在的不同地区都以不同的行展示，称为“样本数据”，每个样本数据都有12行数据参与分析,所以在“每一样本的行数”中输入“12”。任务操作三、通过可重复双因素方差分析方法分析福源公司2010—2021年A产品坏账金额、B产品坏账金额、C产品坏账金额等主营业务产品坏账数据与主营业务产品品种、客户所在地区之间的关系“置信度”复选框默认为0.05。然后设置“输出选项”，在“输出区域”中选中$A$39单元格区域。如果选中“新工作表组”单选按钮，则表示将输出结果显示在一个新的工作表上，如图10-19所示。

图10-19“方差分析：可重复因素分析”对话框任务操作三、通过可重复双因素方差分析方法分析福源公司2010—2021年A产品坏账金额、B产品坏账金额、C产品坏账金额等主营业务产品坏账数据与主营业务产品品种、客户所在地区之间的关系(3)单击“确定”按钮，得到针对分客户所在地区的A产品坏账金额、B产品坏账金额、C产品坏账金额之间的“方差分析:可重复双因素分析”结果，如图10-20和图10-21所示。图10-20展示的是分客户所在地区的A产品坏账金额、B产品坏账金额、C产品坏账金额的描述性统计分析情况。图10-21展示的是“方差分析:可重复双因素分析”的分析结果，我们可以看到在最左侧列中有“样本”“列”“交互”，分别代表的是样本之间的差异效应、列之间的差异效应以及样本与列之间的交互差异效应。任务操作三、通过可重复双因素方差分析方法分析福源公司2010—2021年A产品坏账金额、B产品坏账金额、C产品坏账金额等主营业务产品坏账数据与主营业务产品品种、客户所在地区之间的关系

图10-20方差分析(结果一)

图10-21方差分析(结果二)任务操作三、通过可重复双因素方差分析方法分析福源公司2010—2021年A产品坏账金额、B产品坏账金额、C产品坏账金额等主营业务产品坏账数据与主营业务产品品种、客户所在地区之间的关系在本案例中，样本差异反映的是不同客户所在地区对于坏账金额的影响，可以发现样本的F值为45.52860137,显著性P值为9.53164x10-15,远远小于设定的显著性标准0.05,说明要显著地拒绝原假设，即不同客户所在地区对于坏账金额的产生是有着显著影响的。任务操作三、通过可重复双因素方差分析方法分析福源公司2010—2021年A产品坏账金额、B产品坏账金额、C产品坏账金额等主营业务产品坏账数据与主营业务产品品种、客户所在地区之间的关系列差异反映的是不同主营业务产品品种对于坏账金额的影响，可以发现列的F值为9.581913576，显著性P值为0.000157021，远远小于设定的显著性标准0.05，说明