Excel在财务管理中的应用课件：销售管理

销售管理6.1销售流向分类汇总6.1.1编制商品基本信息表和销售明细汇总表

图6-1进行销售流向分析,首先要获取基础数据,创建企业的商品基本信息表和销售明细汇总表。企业的商品基本信息表如图6-1所示,已在第5章中介绍,在此不再赘述。6.1.1编制商品基本信息表和销售明细汇总表

图6-2销售明细汇总表是对企业销售商品的供应信息和渠道信息的汇总,通常包含销售日期、销售地域、商品信息、供应商信息、销售人员、销售价格、销售金额等。图6-2所示为本章案例,企业可根据实际情况和需要灵活编制及录入。6.1.2编制销售流向分类汇总表

图6-3销售流向分类汇总表可以按客户、销售员或地区等进行汇总,通过Excel的分类汇总功能,可以从不同角度考察和分析销售数据。【例6-1】以图6-2为数据源,按客户进行销售流向汇总分析。【操作方法】第一步,单击数据源区域内的任意单元格,选择“数据”选项卡下“分级显示”组中的“分类汇总”选项。第二步,在“分类汇总”对话框中,选择以“客户简称”为分类字段,汇总方式为“求和”,选定汇总项为“金额”,并替换当前分类汇总,汇总结果显示在数据下方,单击“确定”选项,如图6-3所示。6.1.2编制销售流向分类汇总表

图6-4通过分类汇总功能,Excel工作表能够按客户简称对金额汇总,结果如图6-4所示。得到分类汇总结果后,可以看到工作表左侧显示分类汇总的三个级别,单击1级,只显示总计项;单击2级,显示各类汇总项和总计项;单击3级,显示所有明细。6.1.2编制销售流向分类汇总表

图6-5第三步,取消分类汇总。当分类汇总表使用完毕,可选择“数据”选项卡下“分级显示”组中的“分类汇总”选项,出现对话框后单击“全部删除”选项,如图6-5所示,即可恢复原来的销售明细汇总表。6.2销售流向分析6.2.1编制销售流向数据透视表

图6-6数据透视表是对大量数据快速汇总,并建立交叉分析表的数据分析工具。销售情况分析是对各销售网点或分公司的销售数据进行重新组合,按照需求进行统计和分析。因此,数据透视表对销售情况分析十分有用。本部分基于Excel的数据透视表功能对企业销售流向分析进行讲解。【例6-2】基于图6-2所示的销售明细汇总表中的数据信息,制作数据透视表。【操作方法】第一步,选中数据源区域,即A2至O12单元格区域,单击“插入”选项卡下“表格”组中的“数据透视表”选项。第二步,设置数据透视表字段。将“网点”字段放入“筛选”区域;将“商品名称”放入“列”区域;将“月份”和“客户简称”放入“行”区域;将“金额”放入“∑值”区域,如图6-6所示。6.2.1编制销售流向数据透视表

图6-7第三步,调整数据透视表布局。单击“设计”选项卡下“布局”组中的“分类汇总”选项,选择“在组的顶部显示所有分类汇总”;单击“布局”组中的“报表布局”选项,选择“以表格形式显示”。得到的数据透视表如图6-7所示。数据透视表可以对网点进行筛选,也可以对月份、客户简称、商品名称进行筛选,并进行数据分析。6.2.2创建销售流向数据透视图

图6-8数据透视图是基于数据透视表中的数据绘制的可视化图形,通过数据透视图能够更清晰地了解数据之间的关系。【例6-3】基于图6-7的数据透视表,绘制数据透视图。【操作方法】第一步,选中数据源区域,即A4至G16单元格区域,单击“插入”选项卡下“图表”组中的“数据透视图”选项。第二步,在“插入图表”对话框中选择需要插入的图表类型,本例以簇状柱形图为例。第三步,在“设计”选项卡下选择合适的图表样式,得到数据透视图,如图6-8所示。6.3销售预测销售预测是根据企业过去的销售相关数据,在尽量考虑未来各种可能影响因素的基础上,通过一定的分析方法制定销售目标。简单来说,就是对企业产品在未来一定时期内的销售数量与销售金额做出估计。在企业销售管理过程中,销售预测是极其重要的一个环节。一方面,科学地进行销售预测便于企业更加合理地制定销售计划;另一方面,销售预测是企业预算的基础,销售预测的失败会导致企业预算失败甚至全面预算管理的坍塌。6.3.1应用Excel进行销售预测概述企业进行销售预测的方法有很多,其中较常用的方法为趋势预测法和因果预测法。前者根据企业过去销售数据的时间趋势特征进行销售预测,后者则强调找出影响企业销售的最主要因素。趋势预测法包括移动平均法、指数平滑法等;因果预测法包括回归直线分析法、回归曲线分析法及多元线性分析法等,如表6-1所示。本节仅对应用移动平均法、指数平滑法、回归直线分析法和回归曲线分析法进行销售预测做详细讲解。6.3.1应用Excel进行销售预测概述Excel中有专门的数据分析工具可以简便快速地进行简单移动平均计算。在首次使用Excel数据分析工具之前,必须先加载分析工具库。加载分析工具库的操作步骤如下:第一步,在菜单栏中单击“文件”,选择“选项”;第二步,单击“加载项”,在“非活动应用程序加载项”中找到“分析工具库”,并单击“转到”选项,如图6-9所示。

图6-96.3.1应用Excel进行销售预测概述第三步,选择“分析工具库”选项,并单击“确定”选项,如图6-10所示。

图6-106.3.1应用Excel进行销售预测概述此时,在“数据”选项卡下分析组中有“数据分析”功能,如图6-11所示。

图6-11分析工具库包含描述统计、直方图、相关系数、移动平均、指数平滑、回归等19种数据统计分析方法。但是与专业的统计分析软件相比,可处理的数据量和模型复杂程度有限。下面对应用Excel的分析工具和函数工具进行销售预测的方法进行讲解。6.3.2移动平均法1.简单移动平均法移动平均法是一种简单平滑预测技术,根据时间序列资料逐项推移,一次计算包含一定项数的序时平均值,以反映长期趋势。因此,当时间序列的数值由于周期变动和随机波动的影响起伏较大,不易显示出事件的发展趋势时,使用移动平均法可以消除这些因素的影响,显示事件的发展方向和趋势(即趋势线),依据趋势线分析预测序列的长期趋势。应用Excel的数据分析工具能够直接根据历史数据进行预测并生成图表。【例6-4】图6-12为某公司2019年上海网点销售数据,要求:使用Excel的数据分析工具,以移动平均法预测该公司2020年销售额。

图6-126.3.2移动平均法【操作方法】第一步,单击“数据”选项卡下“分析”组中的“数据分析”选项。第二步,在“数据分析”对话框中选择分析工具为“移动平均”,并单击“确定”选项。第三步,在“移动平均”对话框中选择数据区域为2019年上海网点销售数据区域,即B3至M3单元格区域;选择“间隔”为2;选择输出区域,如B6单元格;选中“图表输出”和“标准误差”,并单击“确定”选项,如图6-13所示。注意,使用移动平均法需要设定“间隔”,移动平均数的计算限定在间隔数之内。若移动平均数为2,则每个移动平均数都是前2个原始数据的平均值。本例以间隔为2举例,在实际应用中,可根据具体需求更改间隔。

图6-136.3.2移动平均法第四步,通过上述操作能够得到2020年上海网点销售预测数据,为生成的表格添加标题,得到的结果如图6-14所示,其中第6行为Excel自动生成的预测数据。

图6-146.3.2移动平均法第五步,由于在第三步的“移动平均”对话框中选择了“图表输出”,因此,在得到2020年上海网点销售预测数据的同时,Excel会同时生成相应的图表,给生成的图表重命名其图表标题及坐标轴标题,得到的结果如图6-15所示。

图6-156.3.2移动平均法上述操作是以2为间隔的简单移动平均。在实际销售预测工作中,只需要改变上述分析工具中的间隔就可以得到任意间隔的销售预测情况。如果将间隔设置为3,重复上述操作后得到的2020年上海网点销售预测数据及销售预测图如图6-16所示。

图6-166.3.2移动平均法2.加权移动平均法加权移动平均法,是指根据同一个移动期内不同时期的数据对预测值的影响程度给予不同的权数,然后进行平均移动以预测未来值的方法。加权移动平均法与简单移动平均法的区别在于,简单移动平均法对移动期内的各时期数据同等看待,而加权移动平均法则考虑了移动期内不同时期的数据对预测值的影响程度不同,弥补了简单移动平均法的不足。通常情况下,近期的数据对预测值的影响程度较大,因此,加权移动平均法会对近期数据赋予较大权数,而对远期的数据赋予较小的权数。使用加权移动平均法时,首先需要对每个时期的数据赋予一定的权数,并且权数总和为1。【例6-5】现有2019年上海网点销售数据,根据近期数据权数大而远期数据权数小的特点,得到如图6-17所示数据。要求:使用Excel的数据分析工具,以加权移动平均法预测该公司2020年销售额。

图6-176.3.2移动平均法【操作方法】应用加权移动平均法进行销售预测时,需要使用SUMPRODUCT函数。SUMPRODUCT函数即乘积和函数(详见本书第3章3.3节)。第一步,在N1至Z4单元格区域内,编制2020年上海网点销售预测数据表,如图6-18所示。

图6-186.3.2移动平均法第二步,单击O4单元格,并输入“=SUMPRODUCT(B4:M4,$B$3:$M$3)”,函数返回结果即为2020年1月份的销售预测值。第三步,将鼠标移到O4单元格右下角,待光标变为十字形,即为填充柄状态,向右填充至Z4单元格,即可将单元格O4中的函数复制到P4至Z4区域并显示函数结果,得到的2020年上海网点销售预测数据如图6-19所示。

图6-196.3.3指数平滑法指数平滑法也是一种常见的预测方法,它是在移动平均法的基础上发展而来的,或者说,它就是加权移动平均法的一种特殊形式。指数平滑法通过计算指数平滑值,配合一定的时间序列预测模型对未来进行预测。指数平滑法的基本公式是:

St=αSt-1+(1-α)St-1(6-1)式中,St代表下期预测数;St-1代表本期实际数;St-1代表本期预测数;α为平滑指数且0<α<1;1-α为阻尼系数。上式是从公式St=St-1+α(St-1-St-1)变化而来的,后者表示指数平滑法的基本原理:在本期预测数的基础上加上用平滑指数调整过的本期实际数与本期预测数的差,即可求出下期预测数。平滑指数α的作用是在一定程度上消除偶然因素引起的历史数据的波动。α越大,近期历史数据对预测结果的影响越大。使用指数平滑法进行销售预测与移动平均法一样简单易行,只需录入销售历史数据和设置平滑指数。平滑指数可以根据历史资料中过去预测数与过去实际数的差距而定,差距大则可将平滑指数设置得大一些;反之,则可将平滑指数设置得小一些。根据平滑次数的不同,指数平滑法可分为一次指数平滑法、二次指数平滑法和三次指数平滑法等。在实际工作中,二次指数平滑法用得最多,而且二次指数平滑法必须在一次指数平滑法的基础上进行。下面分别对一次指数平滑法和二次指数平滑法进行讲解。6.3.2移动平均法1.一次指数平滑法【例6-6】现有2019年上海网点销售数据,使用Excel的数据分析工具,以指数平滑法预测该公司2020年销售额。【操作方法】第一步,单击“数据”选项卡下“分析”组中的“数据分析”选项。第二步,在“数据分析”对话框中选中“指数平滑”选项,并单击“确定”。第三步,在“指数平滑”对话框中选择数据区域为2019年上海网点销售数据区域,即B3至N3单元格区域;选择“阻尼系数”为0.3;选择输出区域,如B7单元格;选中“图表输出”和“标准误差”,并单击“确定”,如图6-20所示。

图6-206.3.2移动平均法通过上述操作能够得到2020年上海网点销售数据。对生成的数据添加标题,结果如图6-21所示。

图6-216.3.2移动平均法2.二次指数平滑法在一次指数平滑的基础上再次进行指数平滑操作,就是二次指数平滑法。二次指数平滑是对一次指数平滑的再平滑,因此在进行二次指数平滑时需要以一次指数平滑的结果作为基础数据。二次指数平滑法的操作过程与一次指数平滑法相同,只是在“分析工具”对话框中的输出区域和输入区域的选择上有所区别。在上例的基础上进行二次指数平滑操作,操作步骤如下:第一步,单击“数据”选项卡下“分析”组中的“数据分析”选项。第二步,在“数据分析”对话框中选中“指数平滑”选项,并单击“确定”。第三步,在“指数平滑”对话框中选择数据区域为2020年上海网点销售数据区域,即C7至N7单元格区域;选择“阻尼系数”为0.3;选择输出区域,如C22单元格;选中“图表输出”和“标准误差”,并单击“确定”,如图6-22所示。

图6-226.3.2移动平均法通过上述操作,能够得到用二次指数平滑法预测的2020年上海网点销售数据,如图6-23所示。

图6-236.3.4回归直线分析法回归直线分析法研究的因果关系只涉及一个自变量和一个因变量,并且该因果关系呈直线分布,即模拟为一条一元一次直线方程y=ax+b。如果将该直线延伸,可以得到较为直观的预测值。此处的回归直线分析法属于销售预测中的趋势预测法,即自变量是时间。后面还会讲解因果预测法中的回归直线分析法,其自变量是影响企业销售的最主要因素。运用回归直线分析法进行销售预测时,可以应用Excel函数,也可以应用Excel图表中趋势线和趋势线方程,以下分别讲解。6.3.4回归直线分析法1.应用函数进行预测我们可以应用FORECAST函数进行销售预测,也可以应用SLOPE函数和INTERCEPT函数,或者LINEST和INDEX嵌套函数进行销售预测。这几种方法的区别是:应用FORECAST函数只能得到预测值;而应用SLOPE函数和INTERCEPT函数,或者应用LINEST和INDEX嵌套函数则是通过求出直线方程的斜率a和截距b计算预测值。本节仅以应用FORECAST函数和应用LINEST和INDEX嵌套函数为例进行讲解。(1)FORECAST函数。FORECAST函数是统计函数,具体含义是,应用回归直线方程y=a+bx,根据给定的x值返回要预测的y值。该函数的语法为FORECAST(x,known_y's,known_x's)。其中,参数x为需要预测的数据点x值;参数known_y's为给定的已知y值集合;参数known_x's为给定的已知x值集合。【例6-7】现有某公司2019年1月至11月的上海网点销售数据,如图6-24所示。要求:使用FORECAST函数预测12月的销售额。

图6-246.3.4回归直线分析法【操作方法】将显示数据的单元格设置为B9单元格,单击B9单元格,输入“=FORECAST(12,B4:L4,B3:L3)”,即可显示2019年12月份的预测数据,如图6-25所示。

图6-256.3.4回归直线分析法(2)LINEST和INDEX嵌套函数。LINEST函数是统计函数,具体含义是通过使用最小二乘法计算与现有数据最佳拟合的直线,并返回由描述此直线的斜率a和截距b形成的数组。该函数的语法为LINEST(known_y's,[known_x's],[const],[stats])。其中,参数known_y's为给定的已知y值集合。参数known_x's为给定的已知x值集合,如缺省则表示该x值集合为{1,2,3,…},集合大小与known_y's相同。参数const是一个布尔值,用于指定是否将截距b强制设为0,如果const为TRUE或缺省,b将按正常计算;如果const为FALSE,b将被设为0。参数stats也是一个布尔值,用于指定是否返回附加回归统计值,如果stats为TRUE,则函数返回附加回归统计值;如果stats为FALSE或缺省,则函数只返回斜率a和截距b形成的数组。需要注意的是,LINEST函数返回的是数值数组,因此单独使用时必须以数组的形式输入,否则需要与其他函数嵌套使用。INDEX函数是查找与引用函数,具体含义是返回表格或区域中的值或值的引用。INDEX函数有两种使用方法:第一种是返回指定单元格或单元格数组的值,即数组形式;第二种是返回指定单元格的引用,即引用形式。6.3.4回归直线分析法INDEX的数组形式的功能是返回给定单元格或数组中行号列标的元素的值,其语法为INDEX(array,[row_num],[column_num])。其中,参数array是给定的单元格区域或数组。参数row_num是array中某行的行号,函数从该行返回数值,若array只包含一行,可省略row_num,但必须有参数column_num。参数column_num是array中某列的列标,函数从该列返回数值,若array只包含一列,可省略column_num,但必须有参数row_num。如果同时使用参数row_num和column_num,函数返回row_num和column_num交叉处的单元格的值;如果将row_num或column_num设置为0或缺省,函数分别返回整个列或行的值。INDEX的引用形式的功能是返回给定行列交叉处的单元格引用。如果引用由不连续的选定区域组成,可以选择某一选定区域,其语法为INDEX(reference,[row_num],[column_num],[area_num])。其中,参数reference为给定的对一个或多个单元格区域的引用,如果为引用不连续的区域,必须用括号括起来,例如,(A1:D3,A5:D7)。参数row_num代表reference中某行的行号,函数从该行返回一个引用。参数column_num代表reference中某列的列标,函数从该列返回一个引用。参数area_num代表选择引用中的一个区域,返回该区域中row_num和column_num的交叉区域。选中或输入的第一个区域序号为1,第二个为2,以此类推。缺省则表示函数使用区域1。6.3.4回归直线分析法【例6-8】现有某公司2019年1月至11月的上海网点销售数据,如图6-26所示。要求:应用回归直线分析法,使用LINEST和INDEX嵌套函数预测12月的销售额。

图6-266.3.4回归直线分析法【操作方法】使用LINEST和INDEX嵌套函数计算斜率a和截距b。第一步,单击显示斜率a的单元格,如B12单元格,输入“=INDEX(LINEST(B4:L4,B3:L3),1)”。第二步,单击显示截距b的单元格,如C12单元格,输入“=INDEX(LINEST(B4:L4,B3:L3),2)”。第三步,单击显示12月份销售额预测值的单元格,如D12单元格,输入“=B12*12+C12”。得到的结果如图6-27所示。

图6-276.3.4回归直线分析法2.应用趋势线和趋势线方程进行预测为了更加直观地反映销售额变化情况,在上例的基础上可以做一个折线图进行预测。【例6-9】在上例的基础上,插入折线图并添加趋势线。【操作方法】第一步,选中A4至L4单元格区域,单击“插入”选项卡,在“图表”组中选择“插入折线图或面积图”,选择“带数据标记的折线图”,即可生成一个销量图。第二步,单击图表右上方的十字形符号,选择“图例”,设置为“底部”,勾选“趋势线”,更改标题,选中生成的趋势线,在“设置趋势线格式”窗口中选择“趋势线选项”为“线性”,并选中“显示公式”和“显示R平方值”,如图6-28所示。

图6-286.3.4回归直线分析法通过上述操作步骤能得到一条线性的趋势线,如图6-29所示,这条趋势线代表计算出的直线回归方程。在进行销售预测时,只需要延长趋势线,即可得到未来某一时期的预测销售量。

图6-296.3.5回归曲线分析法回归曲线分析法与回归直线分析法的原理一致,只涉及一个自变量和一个因变量,但回归直线分析法是一元一次回归,而回归曲线分析法则是