EXCEL在会计与财务管理中的应用-坏账成因分析

EXCEL在会计与财务管理中的应用

坏账成因分析任务一单因素影响分析任务二可重复双因素方差分析为了提高销售收入，提高市场占有率，企业在销售实践中普遍采用赊销的方式，但赊销在很大程度上是基于对交易对手的商业信用，由于在交易中存在很多信息不对称行为，企业对交易对手的了解不够详细，对其还款能力和还款意愿评估做不到尽善尽美，因此会不可避免地形成坏账。客观来讲，坏账的成因是多方面的，每个企业的坏账也有自身具体的特点，本章主要以具体案例的形式，使用Excel2010中的方差分析方法，对企业的坏账影响因素进行分析，旨在发现其中的规律，为企业做好销售回款管理提供有益的参考借鉴，进而为企业经营管理提供必要的智力支持。项目十坏账成因分析【知识准备】一、单因素方差分析（一）单因素方差的基本概念试验中要考察的指标称为试验指标，影响试验指标的条件称为因素，因素所处的状态称为水平，若试验中只有一个因素改变则称为单因素试验，若有两个因素改变则称为双因素试验，若有多个因素改变则称为多因素试验。方差分析就是对试验数据进行分析，检验方差相等的多个正态总体均值是否相等，进而判断各因素对试验指标的影响是否显著，根据影响试验指标条件的个数可以区分为单因素方差分析、双因素方差分析和多因素方差分析。任务一单因素影响分析【知识准备】在方差分析中，我们将要考察的对象的某种特征称为试验指标，影响试验指标的条件称为因素，因素可分为两类，一类是人们可以控制的(如原材料、设备、学历、专业等因素)；另一类人们无法控制的(如员工素质与机遇等因素)。下面所讨论的因素都是指可控制因素。每个因素又有若干个状态可供选择，因素可供选择的每个状态称为该因素的水平。如果在一项试验中只有一个因素在改变，则称为单因素试验；如果多于一个因素在改变，则称为多因素试验。因素常用大写字母A，B，C，…来表示，因素A的水平用A1,A2,A3来表示。任务一单因素影响分析【知识准备】（二）假设前提1.各个观察值是独立的，即各组观察数据是从相互独立的总体中抽取的；2.每个总体都应服从正态分布且方差相等，即各组观测数据是从具有相同方差的正态分布总体中抽取的简单随机样本。（三）单因素方差分析的数据结构1.在单因素试验中，假设因素A共有r个水平，r表示单因素的分类数目，每个水平的样本容量为n，则共有n*r个观察值，单因素试验的结果以r行n列表示，构成单因素分析的数据结构。任务一单因素影响分析【知识准备】1.提出假设设因素力有不同水平A1,A2,…Ar,各水平对应的总体服从正态分布N,在水平Ar进行Nr次试验，假定所有试验都是独立的，因为在A水下的样本观测值与总体服从相同的分布，如果因素A对试验结果影响不显著，则所有样本观测值可以看作是来自同一总体，即各自变量取值分类组的均值相等。提出假设：原假设H0：U1=U2=...=Ur，即因素A对观测变量无显著影响。备择假设H1：U1=U2=...=Ur不全相等，即因素A对观测变量有显著影响。若拒绝原假设，则表示自变量对因变量有显著影响；若接受原假设，则表示自变量对因变量没有显著影响。2.构造检验统计量任务一单因素影响分析【知识准备】3.离差平方和（1）总离差平方和，也称总平方和（SumofSquaresforTotal,SST）,反映全部实验数据之间的离散状况，是全部观察值与总平均值的离差平方和。（2）组间离差平方和，也称因素A平方和（SumofSquaresforFactorA,SSA）,反映各个总体的样本均值之间的差异程度，即每组数据均值和总平均值之间的离差平方和。（3）组内离差平方和，也称误差项离差平方和(SumofSquaresforError,SSE),反映每个样本各观测值之间的离散状况，即组内数据和组内平均值之间的随机误差。任务一单因素影响分析【知识准备】4.判断与结论有两种方法可以用来判定是否接受原假设Ho：（1）将统计量F与给定的显著性水平a的临界值Fa比较，可以做出拒绝或接受原假设Ho的判断：若F≥Fa,则拒绝原假设Ho,表明因素A对观察值有显著影响；若F＜Fa，则接受原假设Ho，表明因素A对观察值无显著影响。（2）利用F值计算出P值，当P<a时，拒绝Ho，表明均值之间有显著差异，即因素A对观察值有显著影响；当P>a时，则接受原假设Ho，表明均值之间无显著差异，因素A对观察值无显著影响。任务一单因素影响分析【任务目标】1.了解单因素方差分析；2.学会使用Excel2010数据分析工具中方差分析模块单因素方差分析命令；3.学会如何使用单因素方差分析方法对企业坏账影响因素问题进行研究。任务一单因素影响分析【任务资料】福源公司是一家生产制造企业，自2010年以来，公司主要经营的产品是A、B、C三种产品。相关资料数据如表10-1所示。要求：1.利用图表分析方法分析福源公司2010年至2021年主营业务产品坏账数据。任务一单因素影响分析【任务操作】

一、数据输入设计1.打开Excel,将工作簿保存为“坏账成因.xlsm”。将Sheetl工作表重命名为“坏账成因分析”，完成建立“坏账成因分析”工作表，然后在其中录入福源公司2010年至2021年主营业务产品坏账数据。录入完成后，相关的数据如图10-1所示。【任务操作】

二、通过图表分析方法分析福源公司2010年至2021年主营业务产品坏账数据。这里使用“带平滑线和数据标记的散点图”和“雷达图”比较适合。（一）带平滑线和数据标记的散点图1.首先选中A2〜D14单元格，然后在“插入”选项卡中选择“图表”子选项卡，再选择“散点图”中的“带平滑线和数据标记的散点图”,如图10-2所示。【任务操作】

2.单击后，即可弹出基于福源公司2010年至2021年主营业务产品坏账数据的“带平滑线和数据标记的散点图”,形成的图表如图10-3所示。【任务操作】

3.然后将标题修改为“分产品坏账成因分析”,单击“分产品坏账成因分析图中的横坐标轴，即弹出如图10-4所示的“设置坐标轴格式”对话框，我们在其中的“坐标轴选项”最小值中填写2010.0，在最大值中填写2021.0，在主要刻度单位中填写2.0，在次要刻度单位中填写1.0。【任务操作】

4.单击“关闭”按钮后，最终形成的“分产品坏账成因分析”图，如图10-5所示。结论：从“带平滑线和数据标记的散点图”中，我们可以较为直观地看A、B、C三中产品之间还是存在一定差异的，尤其是在2010年以来，B产品坏账金额要高于A产品坏账金额，再高于C产品坏账金额。【任务操作】

（二）雷达图1.首先选中A2〜D14单元格，然后在“插入”选项卡中选择“其他图表”下方箭头选项，再找到雷达图，选择雷达图中“雷达图”，如图10-6所示。【任务操作】

2.单击“确定”按钮即可弹出如图10-7所示的基于福源公司2010年至2021年A、B、C三种主营业务产品坏账数据的雷达图。【任务操作】

三、通过单因素方差分析方法分析福源公司2010年至2021年A、B、C三种主营业务产品坏账数据与主营业务产品品种之间的关系。1.选择单因素方差分析工具。在工具栏中选择“数据”-“分析”-“数据分析”命令，随即弹出“数据分析”对话框，在“分析工具”菜单中选择“方差分析：单因素方差分析”选项，如图10-8所示。【任务操作】2.单击“确定”按钮，随即弹出“方差分析：单因素方差分析”对话框，在对话框中，首先设置“输入”内容，单击“输入区域”后面的折叠按钮，并选取单元格区域$B$2:$D$14。因为我们本例中研究的是A、B、C三种产品之间的方差分析，而且三者数据都以“列”的形式在Excel2010中展示的，所以在“分组方式”选项组中要选择“列”复选框。此外，因为我们在“输入区域，中选中了B2〜D14,所以数据中是包含标志项的，需要选中“标志位于第一行”复选框，其他采用系统默认设置。然后设置“输出选项”,在“输出区域”中选择“$A$16”。如果选中“新工作表组”,则表示将输出结果显示在一个新的工作表上，如图10-9所示。【任务操作】3.最后，点击“确定”按钮，得到针对A、B、C三种产品之间的方差分析：单因素方差分析结果，如图10-10所示。【任务操作】结论：“方差分析：单因素方差分析”结果图包括两部分，上方是参与方差分析的变量（包括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。【任务操作】结论：下方为单因素方差分析的结果，单因素方差分析的原假设为参与分析的变量之间不存在显著差异，从结果中可以明确地看出单因素方差分析的F值为5.524004344,显著性P值为0.008525039，远远小于0.05,说明要显著拒绝原假设，也就是说福源公司2010年至2021年A产品坏账金额、B产品坏账金额和C产品坏账金额等主营业务产品坏账数据与主营业务产品品种之间是有着显著影响关系的，或者说，不同主营业务产品品种产生的坏账金额之间有着非常显著的不同。【知识准备】一、双因素方差分析的类型（一）无重复的双因素方差分析，它假定因素A和因素B的效应之间是相互独立的，不存在交互关系，也称无交互作用的双因素方差分析1.XXX（二）可重复的方差分析，它假定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分类组的均值,U.1.,U.2.,…,U.k.分别表示因素B分类组的均值；用Pij表示因素A的第i水平与因素B的第j水平的交互效应。在可重复的双因素方差分析中，要检验的假设有3个，即分别对因素A和因素B以及因素A、B之间的交互效应提出假设。任务二可重复双因素方差分析【知识准备】1.对因素A的假设原假设H01：U1..=U2..=…,Un.，即因素A对观测变量无显著影响。备择假设H11:U1..,U2..,…,Un.不全相等，即因素A对观测变量有显著影响。2.对因素B的假设原假设H02：U.1.=U.2.=…,U.k.，即列因素B对观测变量无显著影响。备择假设H12：U.1.,U.2.,…,U.k.不全相等，即因素B对观测变量有显著影响。3.对因素A和因素B的交互效应的假设原假设H03：对一切i和j,有Pij=0，即因素A与因素B之间不存在交互效应。备择假设H13：对一切i和j,Pij不全为零，即因素A与因素B之间存在交互效应。任务二可重复双因素方差分析【知识准备】（二）构造检验统计量由于相互作用的存在，可重复的双因素方差分析要比无重复的双因素方差分析多一个交互作用项平方和，此时总离差平方和SST将被分解为4部分：SSA、SSB、SSAB和SSE,分别代表因素A的组间差异，因素B的组间差异，因素A、B的交互效应和随机误差的离散状况，任务二可重复双因素方差分析【知识准备（三）判断与结论与无重复的双因素方差分析一样，在可重复的双因素方差分析中，既可以将统计量的值F与临界Fa值已进行比较，从而做出拒绝或接受原假设的决策，也可以利用F值计算出P值，再进行判断。具体判断方法如下：利用F值进行判断：若FA≥Fa,则拒绝原假设H01,表明因素A对观察值有显著影响，否则接受原假设H01；若FB≥Fa(k-\则拒绝原假设H02,表明因素B对观察值有显著影响，否则接受原假设H02；若FAB≥F,则拒绝原假设H03,表明因素A、B的交互效应对观察值有显著影响，否则接受原假设H03。同理，利用P值进行判断：当P<a时，拒绝Ho；当P>a时，接受原假设Ho任务二可重复双因素方差分析【任务目标】1.理解Excel2010中可重复双因素方差分析的基本原理；2.掌握可重复双因素方差分析方法在Excel2010中的实现；3.学会应用这些统计分析方法来解决实际中遇到的财务管理问题。任务二可重复双因素方差分析【任务资料】福源公司是一家生产制造企业，自2010年以来，公司主要经营的产品是A、B、C三种产品。2010年至2021年A、B、C三种主营业务产品分客户所在地区的坏账数据如表10-2所示。任务二可重复双因素方差分析年份客户所在地区A产品坏账金额（万元）B产品坏账金额（万元）C产品坏账金额（万元）2010东部79.940.5687中部20.6845.88107西部93.36119.04111.52011东部52.96100.32109.25中部41.30422.416101.35西部91.497.248106.5752012东部27.365634.04103.515中部29.8629.5625.0175西部136.8107.0451.13352013东部109.52219.3614.25中部92.92147.2125.5西部177.32225.449.752014东部172.8105.76114.5中部292.36348.12107.75西部200.4253.84119.752015东部202.56238.7273.5中部216.4136.12128.25西部75.94128.88115.232016东部120.12115.23127.5中部154.23228148西部188173140.252017东部112144122中部188180135西部102.696130.944122.652018东部122110.352120.175中部145.4344199111.485西部100.54115117.23252019东部230237113.8665中部350411221西部2553551862020东部280441210中部358552199西部3565522052021东部330425256中部339560199西部320.44235220【任务操作】

一、录入数据1.使用Excel2010打开“坏账成因.xlsm”工作簿。然后新建或者选择其中一张空白工作表并重命名为“坏账成因分析（双因素）”,完成建立“坏账成因分析（双因素）”工作表，然后在其中录入福源公司2010年至2021年主营业务产品分客户所在地区坏账数据，如图10-11所示。【任务操作】

二、通过图表分析方法分析福源公司2010年至2021年主营业务产品分客户所在地区坏账数据。这里主要介绍Excel2010中“数据透视图”的用法。（一）数据透视图1.首先在“坏账成因分析（双因素）”工作表中，选中A2-E37单元格，然后在“插入”选项卡中选择“表格”子选项卡，单击“数据透视表”下方的箭头，选择其中的数据透视图”菜单命令，如图10-12所示。【任务操作】

2.单击“数据透视图”后弹出“创建数据透视表及数据透视图”，选择“新工作表”（命名为“数据透视图”）再点击“确定”，如图10-13所示。【任务操作】

3.在“数据透视图”工作表会弹出“数据透视图字段”对话框，我们把“客户所在地区”选入“轴字段（类别）”列表框，把“A产品坏账金额”“B产品坏账金额”“C产品坏账金额”都选入“数值”列表框，如图10-14所示。【任务操作】4.然后在“数值”列表框中选中“求和项：A产品坏账金额”,即弹出如图10-15所示的“值字段设置”提示。【任务操作】

5.单击“值字段设置”提示即弹出如图10-16所示的“值字段设置”对话框，我们在“值字段设置”对话框中把计算类型由“求和”改为“平均值”,即设置为“平均值项：A产品坏账金额”。按照同样的操作方式，把“求和项：B产品坏账金额”设置为“平均值项：B产品坏账金额”,把“求和项：C产品坏账金额”设置为“平均值项：C产品坏账金额”【任务操作】

6.设置完毕后即显示如图10-17所示的基于福源公司2010年至2021年A产品坏账金额分客户所在地区平均值项、B产品坏账金额分客户所在地区平均值项、C产品坏账金额分客户所在地区平均值项的数据透视图【任务操作】

结论：从上述数据透视图中可以非常明显地看出，从主营业务产品品种来看，东部地区中B产品坏账金额平均值项要高于A产品坏账金额平均值项，再高于C产品金额平均值项；西部地区中B产品坏账金额平均值项要高于A产品坏账金额平均值项，再高于C产品坏账金额平均值项。【任务操作】

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

3.最后单击“