第八章销售与利润管理模型设计优秀PPT.ppt
第八章销售与利润管理模型设计第一页，本课件共有17页第一节第一节 销售与利润管理模型概述：1、销售与利润管理模型的基本功能销售流向分析销售流向分析模型是将销售明细信息按销售人员销售流向分析模型是将销售明细信息按销售人员,客户客户,地地区进行分类汇总区进行分类汇总.分析企业所经营的产品或服务的销售流向分析企业所经营的产品或服务的销售流向,帮帮助管理者进行产品销售流向分析助管理者进行产品销售流向分析.销售业绩分析销售业绩分析模型是将各地各分公司在不同的时期和不同的产销售业绩分析模型是将各地各分公司在不同的时期和不同的产品进行分析比较品进行分析比较.利用利用EXCELEXCEL的公式和图表功能的公式和图表功能,为管理者有效地进为管理者有效地进行销售业绩评价提供支持行销售业绩评价提供支持.第二页，本课件共有17页销售预测回归方程预测(单变量)销售销售(Y)=A*(Y)=A*日期日期(X)+B(X)+B因果分析预测(多元回归)销售销售(Y)=A1*X1+A2*X2+B(Y)=A1*X1+A2*X2+B利润分析和管理盈亏临界分析当利润为当利润为0 0时的经营状况时的经营状况因素变动分析各因素的变动对销售利润的影响各因素的变动对销售利润的影响目标利润管理确定企业的目标利润的情况下确定企业的目标利润的情况下,对各因素的管理对各因素的管理第三页，本课件共有17页2、主要技术和工具数据透视表（数据透视表（PIVOTTABLEPIVOTTABLE）数据透视表是EXCEL的一种对大量数据快速汇总和建立交叉分析表的数据分析技术与工具.宏（宏（MacroMacro）和控件）和控件利用VBA宏语言编写程序,自动完成各种分析.数据分析数据分析利用利用EXCELEXCEL的数据排序的数据排序,数据筛选数据筛选,数据分类汇总等数据分类汇总等技术工具进行各种数据分析技术工具进行各种数据分析.单变量求解技术和工具单变量求解技术和工具确定目标值,求其中一个自变量的值.第四页，本课件共有17页第二节销售流向分析模型的建立、获取销售流向分析模型的基础数据、获取销售流向分析模型的基础数据(结合案例分析结合案例分析)集中式财务管理模式下的销售数据的获取方法.A.利用EXCEL的MicrosoftQuery从外部获取数据(新建数据库查询,第四章内容)B.B.利用利用EXCELEXCEL的的数据透视表技术从外数据透视表技术从外部数据库获取数据.第五页，本课件共有17页集团总部中心数据库获取销售数据销售分析模型江苏分公司集团总部中心数据库集团总部中心数据库集团总部中心数据库集团总部中心数据库集团总部中心数据库集团总部中心数据库集团总部中心数据库湖南分公司北京分公司新疆分公司集中式财务管理模式第六页，本课件共有17页分散式财务管理模式销售分析模型Excel表Excel表分公司分公司发送生成生成数据库数据库第七页，本课件共有17页、按客户进行销售流向分析、按客户进行销售流向分析a,a,对销售数据明细表进行排序对销售数据明细表进行排序.b,b,用分类汇总进行汇总用分类汇总进行汇总.C,C,取消分类汇总取消分类汇总、按销售员进行销售流向分析、按销售员进行销售流向分析、按地区进行销售流向分析、按地区进行销售流向分析第八页，本课件共有17页第三节销售业绩分析模型的建立、数据透视表、销售业绩分析模型的数据透视表、销售业绩分析模型的数据透视表、界面设计、数据更新集团总销售分析北京东北新疆湖南第九页，本课件共有17页1,数据透视表a,建立数据透视表的条件-完整的表体结构完整的表体结构.EXCELEXCEL表中表头为文本型表中表头为文本型,不存在空单元格不存在空单元格-规范的列数据规范的列数据.同一列的数据应具有相同的数据格式同一列的数据应具有相同的数据格式 b,准备数据源-存放在存放在excelexcel中的数据清单或数据库中的数据清单或数据库-外部数据外部数据()()-经过多重合并计算后的数据区域经过多重合并计算后的数据区域-另一张数据透视表另一张数据透视表c,通过向导创建数据透视表()d,建立数据透视关系第十页，本课件共有17页、销售业绩分析模型的数据透视表(例例:销售与利润管理模型销售与利润管理模型)、界面设计、界面设计a,a,在新工作表中插入地图在新工作表中插入地图b,在地图中建立宏按钮c,建立分析数据透视表数据的链接、数据更新、数据更新选择选择“数据数据”菜单中菜单中“更新数据更新数据”自动更新数据自动更新数据透视表透视表第十一页，本课件共有17页第四节销售预测模型设计1,销售预测方法直线趋势法直线趋势法销售销售(Y)=A*(Y)=A*日期日期(X)+B(X)+B曲线趋势法曲线趋势法销售销售(Y)=A1*(Y)=A1*日期日期(X)+A1*(X)+A1*日期日期2 2(X(X2 2)+B)+B因果分析法因果分析法(多元回归多元回归)销售销售(Y)=m1*X1+m2*X2+B(Y)=m1*X1+m2*X2+B2,预测函数linestlinest函数用于直线回归分析函数用于直线回归分析(包括可化为直线回归的曲线趋势分析包括可化为直线回归的曲线趋势分析)功能功能:使用最小二乘法计算对已知数据进行最佳直线拟合，并返回描述此直线的使用最小二乘法计算对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函数返回数值数组，故必须以数组公式的形式输入数组。因为此函数返回数值数组，故必须以数组公式的形式输入第十二页，本课件共有17页直线的方程为：直线的方程为：y=mx+b y=mx+b 或或 y=m1x1+m2x2+.+b y=m1x1+m2x2+.+b（如果（如果 x x 值是多重的）值是多重的）式中的因变量式中的因变量 y y 是自变量是自变量 x x 的函数值。的函数值。M M 值是与每个值是与每个 x x 值相对应的系数，值相对应的系数，b b 是常数。是常数。注意注意 y y、x x 和和 m m 可以是向量。函数可以是向量。函数 LINEST LINEST 返回的数组是返回的数组是 mn,mn-1,.,m1,b mn,mn-1,.,m1,b。函数。函数 LINEST LINEST 还可返回附加回归统计值。还可返回附加回归统计值。语法语法语法语法:LINEST:LINEST(known_ysknown_ys,known_xs,const,stats),known_xs,const,stats)Known_ysKnown_ys是关系表达式是关系表达式y=mx+by=mx+b中已知的中已知的yy值集合。值集合。uu如果数组如果数组known_ysknown_ys在一列中，则在一列中，则known_xsknown_xs的每一列都被当作单独的变量。的每一列都被当作单独的变量。uu如果数组如果数组known_ysknown_ys在一行中，则在一行中，则known_xsknown_xs的每一行都被当作单的每一行都被当作单独的变量。独的变量。uu数组数组known_xsknown_xs中包括一个或多个变量集合。如果只用到一个变量，中包括一个或多个变量集合。如果只用到一个变量，只要只要known-ysknown-ys和和known-xsknown-xs维数相同，它们可以是任何形状的选维数相同，它们可以是任何形状的选定区域。如果用到不只一个变量，定区域。如果用到不只一个变量，known_ysknown_ys必须是向量（就是说，必须是向量（就是说，必须是一行或一列的区域）。必须是一行或一列的区域）。uu如果省略如果省略如果省略如果省略 known_xs known_xs，则假设该数组是，则假设该数组是，则假设该数组是，则假设该数组是 1,2,3.1,2,3.，其大小与，其大小与，其大小与，其大小与 known_ys known_ys 相同。相同。相同。相同。第十三页，本课件共有17页ConstConst为一逻辑值，指明是否强制使常数为一逻辑值，指明是否强制使常数bb为为00。uu如果如果constconst为为TRUETRUE或省略，或省略，bb将被正常计算。将被正常计算。uu如果如果constconst为为FALSEFALSE，bb将被设为将被设为00，并同时调整，并同时调整mm值使值使y=mxy=mx。StatsStats为一逻辑值，指明是否返回附加回归统计值。为一逻辑值，指明是否返回附加回归统计值。uu如果如果statsstats为为TRUETRUE，函数，函数LINESTLINEST返回附加回归统计值，这时返回的数组返回附加回归统计值，这时返回的数组为为mn,mn-1,.,m1,b;sen,sen-mn,mn-1,.,m1,b;sen,sen-1,.,se1,seb;r2,sey;F,df;ssreg,ssresid1,.,se1,seb;r2,sey;F,df;ssreg,ssresid。uu如果如果statsstats为为FALSEFALSE或省略，函数或省略，函数LINESTLINEST只返回系数只返回系数mm和常数项和常数项bb。对于对于stats=turestats=ture的二维数组附加回归统计值如下：的二维数组附加回归统计值如下：M参数值参数值B参数值参数值M值估计标准差值估计标准差B值估计标准差值估计标准差相关系数平方值相关系数平方值R2Y值估计标准差值估计标准差统计量统计量F自由度自由度D.F回归平方和回归平方和估计值残值平方和估计值残值平方和第十四页，本课件共有17页对于对于stats=turestats=ture的多维数组附加回归统计值如下：的多维数组附加回归统计值如下：se1,se2,.,sense1,se2,.,sen系数系数m1,m2,.,mnm1,m2,.,mn的标准误差值。的标准误差值。SebSeb常数项常数项bb的标准误差值（当的标准误差值（当constconst为为FALSEFALSE时，时，seb=#N/Aseb=#N/A）R2R2判定系数。判定系数。YY的估计值与实际值之比，范围在的估计值与实际值之比，范围在00到到11之间。如果为之间。如果为11，则样本有很好的相关性，则样本有很好的相关性，YY的估的估计值与实际值之间没有差别。而在另一方面，如果判定系数为计值与实际值之间没有差别。而在另一方面，如果判定系数为00，则回归方程不能用来预测，则回归方程不能用来预测YY值。关于计值。关于计算算r2r2的方法的详细信息，请参阅本节后面的的方法的详细信息，请参阅本节后面的“说明说明”。SeyYSeyY估计值的标准误差。估计值的标准误差。FFFF统计值或统计值或FF观察值。使用观察值。使用FF统计可以判断因变量和自变量之间是否偶尔发生过观察统计可以判断因变量和自变量之间是否偶尔发生过观察到的关系。到的关系。d.fd.f自由度。用于在统计表上查找自由度。用于在统计表上查找FF临界值。所查得的值和函数临界值。所查得的值和函数LINESTLINEST返回的返回的F F统计值的比值可用来判断模统计值的比值可用来判断模型的置信度。型的置信度。SsregSsreg回归平方和。回归平方和。SsresidSsresid残差平方和。残差平方和。第十五页，本课件共有17页要将参数值返回要将参数值返回要将参数值返回要将参数值返回,需用需用需用需用INDEXINDEX函数函数函数函数.例例例例:书上书上书上书上3,3,销售趋势分析与因果分析模型设计销售趋势分析与因果分析模型设计销售趋势分析与因果分析模型设计销售趋势分析与因果分析模型设计例例例例:第五节第五节本量利分析模型设计与目标利润分析本量利分析模型设计与目标利润分析第十六页，本课件共有17页作业作业:建立表格如下建立表格如下:用排序用排序 筛选筛选 分类汇总分类汇总 数据透视表等工具数据透视表等工具.1,1,按部门和利润进行排序按部门和利润进行排序,生成部门利润排名表生成部门利润排名表2,2,按部门进行汇总按部门进行汇总,生成各部门的销售信息汇总表生成各部门的销售信息汇总表3,3,利用筛选功能挑选销售收入和利润利用筛选功能挑选销售收入和利润,生成某一特定值的部门名单生成某一特定值的部门名单第十七页，本课件共有17页
