ECEL在会计和财务管理中运用第11章.ppt

第11章经典实例：销售决策分析,11.1要点分析11.2通过建立决策模型进行营销决策11.2.1产品定价决策问题描述与解决的基本思路11.2.2构建销售利润最大化的产品定价决策模型11.2.3产品定价决策模型中有关变量的图表分析11.3数据表在净利润敏感度分析中的应用11.3.1产品销售净利润值敏感度分析,11.3.2价格调整引起的利润变化11.3.3价格与数量同时变动引起的利润变化11.4单变量求解在销售利润目标确定中的应用11.4.1确定目标利润对应的销售收入11.4.2确定新产品的保本销售量11.5使用“规划求解”工具分析营销决策,11.5.1规划问题的特点以及“规划求解”工具的组成11.5.2利用“规划求解”工具解决规划求解问题的流程11.5.3利用“规划求解”工具确定商品运输方案11.5.4利用“规划求解”工具分析设置快捷酒店网点布局,11.2通过建立决策模型进行营销决策,营销决策是指对有关产品市场经营和销售活动的目标、方针、策略等重大问题进行选择和决断的过程。营销决策是企业市场营销中的核心问题，它必须建立在充分的市场调查和市场预测的基础之上。,11.2.1产品定价决策问题描述与解决的基本思路,企业市场营销中的一个重要决策问题就是产品定价。而销售利润则是企业管理追求的一个重要目标，但是销售利润的大小与产品的定价具有重要关联关系。如果产品价格定得过高，那么单位产品的销售利润相应的也会增加，但是总的销售量可能会减少，从而会影响总的产品销售利润；反之产品价格定的过低，虽然销售数量可能会大幅的增长，但是因为单位产品的销售利润不在，也会影响到总的产品销售利润。下面是一个产品定价决策的问题描述，并给出相应的问题解决的基本思路。如表11-1所示，为某公司根据市场分析，对其生产的某产品在不同价格水平下的预测销售量。假设某公司生产该产品的全年固定成本为每年150万无，全年的生产能为55万套，每套产品的变动成为20元。请问：某公司应该如何对该产品定价？,11.1要点分析,问题分析：这里的产品定价，应该就是以销售利润最大化为目标，因此需要构造销售利润的计算公式。然后根据相关变量之间的关系，这里的销售利润计算的公式如下。L=S-C=PQ-(FVQ)(P-V)QF其中：L为全年销售利润。S为全年销售额。C为全年销售成本。P为销售价格。F为全年固定成本。V为单位变动成本。Q为预测的销售量。通过上面公式，就可以计算出来不同价格水平下，对应不同预测销售量时的销售利润；然后可以根据销售利润最大化的原则，即可将其对应价格作为最佳的决策价格。,11.2.2构建销售利润最大化的产品定价决策模型,下面就可以得用Excel建立模型，来进行以上的销售利润辅助计算，操作步骤如下。步骤1根据已知数据以及销售利润计算公式，制作如图11-1所示的表格。步骤2选取C9单元格，输入公式“C4”，获取第一个价格水平，然后向右拖动填充柄，一直复制公式到I9单元格获取其他的价格水平，如图11-2所示。,表11-1不同价格水平下的预测销售量,步骤3选取C10单元格，输入公式“C5”，获取第一个预测销售量，然后向右拖动填充柄，一直复制公式到I10单元格，获取其他预测销售量，如图11-3所示。步骤4选取C11单元格，输入公式“=C9*C10”，获取第一个不同销售量销售收入，然后向右拖动填充柄，一直复制公式到I11单元格，获取其他不同销售量销售收入，如图11-4所示。,图11-1销售利润最大化的产品定价所用表格,图11-2获取其他价格水平,图11-3计算出其他预测销售量,图11-4计算出不同销售量销售收入,步骤5选取C12单元格，输入公式“=C5*$I$6”，获取第一个总变动成本，然后向右拖动填充柄，一直复制公式到I12单元格，获取其他总变动成本，如图11-5所示。步骤6选取C13单元格，输入公式“=$D$6”，计算出固定成本，如图11-6所示。,图11-5计算出其他总变动成本,图11-6计算出固定成本,步骤7选取C14单元格，输入公式“=C12$C$13”，获取第一个总成本，然后向右拖动填充柄，一直复制公式到I14单元格，获取其他的总成本，如图11-7所示。步骤8选取C15单元格，输入公式“=C11-C14”，计算出第一个价格水平下的销售利润然后向右拖动填充柄，一直复制公式到I15单元格，计算出其他价格水平下的销售利润。通过以上的操作，计算出各种不同价格水平下的销售利润，效果娟秀图11-8所示。步骤9选取C17单元格，输入公式“=本产品售价应定为&INDEX(C10:I10,MATCH(MAX(C15:I15),C15:I15,0)&元,此时对应销售量为&INDEX(C4:I4,MATCH(MAX(C15:I15),C15:I15,0)&万套，预测销售利润为&MAX(C15:I15)&万元，得出销售利润最大化的产品定价决策模型的分析结果，如图11-9所示。,图11-7计算出其他的总成本,图11-8计算出不同价格水平下的销售利润,图11-9销售利润最大化的产品定价决策模型的分析结果,11.2.3产品定价决策模型中有关变量的图表分析,下面就来根据产品定价决策模型制作数据图表，操作步骤如下。步骤1选取B14:I14单元格区域，按下Ctrl键，依次选取B12:I12单元格区域、B14:I14单元格区域和B15:I15单元格区域。,步骤2在“插入”选项卡下的“图表”组中单击“折线图”按钮，在展开的下拉列表中单击“带数据标记的折线图”图标，如图11-10所示。步骤3如图11-11所示，经过上述操作，制作出的带数据标记的折线图的初始效果。,图11-11带数据标记的折线图的初始效果,步骤4适当调整图表放置位置，将其移动到适合位置。步骤5右击图表水平坐标轴，从快捷菜单中选择“删除”命令，如图11-12所示，删除纵坐标轴。步骤6右击图例，从快捷菜单中选择“设置图例格式”命令，如图11-13所示。步骤7弹出“设置图例格式”对话框，在“图例选项”选项卡的右侧窗格中单击图例位置“底部”单选按钮，在“边框颜色”选项卡的右侧窗格中单击“实线”单选按钮，并选择颜色“黑色”，如图11-14所示。步骤8在图表中，右击“价格水平（元）”折线条，从快捷菜单中选择“添加数据标签”命令，如图11-15所示，为数据添加标签。,图11-10单击“带数据标记的折线图”图标,图11-13选择“设置图例格式”命令,图11-12选择“删除”命令,图11-14“设置图例格式”对话框,图11-15选择“添加数据标签”命令,步骤9右击添加的数据标签，从快捷菜单中选择“设置数据系列格式”命令，如图11-16所示。步骤10弹出“设置数据系列格式”对话框，设置“系列选项”系列绘制在“次坐标轴”，如图11-17所示，从面建立双轴图表。,图11-17设置数据系列格式,图11-16选择“设置数据系列格式”命令,步骤11右击图表的绘图区，从快捷菜单中选择“设置绘图区格式”命令，如图11-18所示。步骤12弹出“设置绘图区格式”对话框，设置填充色为“纯色填充”，填充颜色为“浅橙色”，如图11-19所示。,图11-19设置绘图区格式,图11-18选择“设置绘图区格式”命令,步骤13添加图表标题“销售利润最大情况下产品定价决策的图表分析”，并进行一定的文字格式效果设置。,步骤14适当调整图表的大小，然后设置A1:S1,A18:S18,A2:A18,S2:S18单元格区域的填充色、调整宽度，做出模型周围的边框效果，构造出基于Excel的销售利润最大化的产品定价决策模型，如图11-20所示。,图11-20销售利润最大化的产品定价决策模型最终制作效果,11.3数据表在净利润敏感度分析中的应用,敏感度分析又称“What-if分析”，其用途是测定当一个参数发生变化时，由该参数所带的中间变量是如何变化，以及由中间变量引起的最终结果会发生什么样的变化。,11.3.1产品销售净利润值敏感度分析,在Sheet2工作表中新建如图11-21所示的表格，左边部分给出了某公司本月产品的简易损益表，而右边部分给出了从基本数据输入开始，到最终的净利润输出为止，中间的数据计算规则。,从图中可以看出，本月该产品的净利润约为840万元。现在假设一下，下个月想实现净利润达到870万元，请在只能改变销售价格和销售数量的情况下，对净利润值的敏感度进行分析。通过改变销售价格和销售数量，可以进行利润值敏感度的分析，引起利润值的变化，更加方便的选择达到即定值的数据。下面在Excel中利用“模拟运算表”工具分析解决该问题。,图11-21某公司某产品本月的简易损益表,11.3.2价格调整引起的利润变化,下面就以在上述的产品损益表中，根据销售价格的变化，来模拟净利润的变化为例，说明利用“模拟运算表”工具进行单因素敏感度分析的操作。具体操作步骤如下。步骤1将Sheet3工作表重命名为“单因素敏感度分析”，然后将某公司某产品本月的简易损益表复制到该工作表中，并在其右侧建立单变量数据表的初始结构，如图11-22所示。,步骤2在D5单元格和D6单元格分别输入75和76，然后选取D5:D6单元格区域，向下拖动填充柄一直到D20单元格，在E4单元格中输入“B21”，结果如图11-23所示。,图11-23利用公式和复制功能填入数据,图11-22单变量数据表的初始结构,步骤3给要进行数据敏感度分析的数据表范围设置填充色。首先选取D4:E20单元格区域，在“数据”选项卡下的“数据工具”组中单击“模拟分析”按钮，从弹出的菜单中选择“模拟运算表”命令，如图11-24所示。步骤4本例是在列上模拟销售价格的变化，销售价格的数据在B5单元格中存储，所以在弹出的“模拟运算表”对话框中在“输入引用列的单元格”文本框中输入“$B$5”，如图11-25所示。步骤5单击“确定”按钮，单变量数据表制作完成，效果如图11-26所示。在每一个价格数字的后面，已经计算出在其他变量都保持不变的情况下，对应净利润的金额。从图中可以看出，在保持其他变量都不变的情况下，要想实现净利润达到870万，只要将价格调整到81元，也就是上涨1元即可。此时对应的净利润为8761406元。,图11-24选择“模拟运算表”命令,图11-25“模拟运算表”对话框,图11-26单因素利润敏感度分析的结果,11.3.3价格与数量同时变动引起的利润变化,进行单变量敏感度分析，只是分析改变一个输入变量对公式计算结果的影响。如果想要分析两个变量对公式计算的影响，就需要进行双变量敏感度分析，这可以通过使用双变量数据来实现。,图11-27创建双变量数据表的框架,例如，前面通过改变销售价格一个参数，分析单变量的利润敏感度，其结果不一定完全准确。因为当价格变化时也可能会引起销售数量也发生一定的变化，所以有必要一起去考虑。下面同时考虑价格和数量两个因素，进行双变量利润敏感度分析，具体操作步骤如下。步骤1插入一个工作表，并重命为“双因素敏感度分析”。步骤2将某公司某产品本月的简易损益表复制到“双因素敏感度分析”工作表中，并在其右侧建立双变量数据表的初始结构，接着在F2:L2单元格区域中输入销售数量，并在E2单元格输入“B21”，结果如图11-27所示。,步骤3选取E2:L18单元格区域，在“数据”选项卡下的“数据工具”组中单击“模拟分析”按钮，在展开的下拉列表中选择“模拟运算表”命令，如图11-28所示。,图11-28选择“模拟运算表”命令,步骤4弹出“模拟运算表”对话框，在“输入引用行的单元格”文本框中输入“$B$6”，在“输入引用列的单元格”文本框中输入“$B$5”，如图11-29所示。步骤5单击“确定”按钮，结果如图11-30所示。,图11-29“模拟运算表”对话框,图11-30按照价格和数量得到的双变量数据表结果,图11-31单击“介于”按钮,步骤6为了将结果为870万元的数字用特殊效果显示，可以为模拟结果设置条件格式，选取E3:L18单元格区域，在“开始”选项下的“样式”组中单击“条件格式”按钮，在展开的列表中选择“突出显示单元格规则”选项，然后单击“介于”按钮，如图11-31所示。步骤7弹出“介于”对话框，设置条件格式，如图11-32所示，最后单击“确定”按钮。,图11-32“介于”对话框,步骤8返回工作表，在图11-33中可以看到，净利润大于870万元的“价格/数量”组合，都分布到整个区域对角线的右下部分。根据价格与数量之间的关系，其中只有一部分的组合具有较大的可能性。例如F15、H9、K3等单元格，都是已经达到了870万元的净利润，并且也是比较可行的几种方案。以F15单元格为例，意思就是“价格如果涨到87元，销售只要达到40万个，就可以实现净利润870万元”。而H9单元格则意味着“价格如果保持81元不动，销售量只要达到50万个，也可以实现净利润达到870万元”。,图11-33对双变量敏感度分析的结果进行条件格式化设置,11.4单变量求解在销售利润目标确定中的应用,在“营销决策.xlsx”工作簿中新插入一张工作表，在工作表中制作某公司编制的本月损益简表，如图11-35所示。在右边部分是各变量之间的相互关系及计算方法，左边部分“本月数字”一列中带有填充色的单元格是利用D列中的计算公式进行计算得出的结果。问题分析：从如图11-34可以看出，本月净利润为78万元左右。假如公司确定下个月净利润要达到80万元，其他原始数据和计算模型中各个参数都保持不变。那么，销售收入应该增长到多少，才能实现净利润达到80万元的目标呢？,图11-34某公司编制的本月损益简表,下面将介绍如何运用“单变量求解”工具来解决上述问题。,11.4.1确定目标利润对应的销售收入,下面采用单变量求解的方法确定目标利润对应的销售收入，具体操作步骤如下。步骤1选定要确定目标利润对应的销售收入的单元格，也就是净利润数据所在的B16单元格。步骤2在“数据”选项卡下的“数据工具”组中单击“模拟分析”按钮，在展开的下拉列表中选择“单变量求解”命令，如图11-35所示。步骤3弹出“单变量求解”对话框，在“目标值”文本框中输入“800000”，在“可变单元格”文本框中，输入这里需要确定的可变量销售收入所在的“$B$2”单元格，如图11-36所示。步骤4单击“确定”按钮，弹出如图11-37所示的“单变量求解状态”对话框，说明已经找到解，并且与目标值完全一致。,图11-35选择“单变量求解”命令,图11-36“单变量求解”对话框,步骤5单击“确定”按钮，得到单变量求解的结果，如图11-38所示，要实现净利润增长到800000.00元，销售收入需要增加到11217548.57元。,图11-37“单变量求解状态”对话框,图11-38单变量求解的结果,11.4.2确定新产品的保本销售量,新插入一张工作表，然后在工作表中制作“新开发产品的保本点计算”表格，如图11-39所示。其中，B4:C14单元格区域中是某公司为一种新产品进行销售利润测算，按照目前设定的数据，计算出来的销售利润为负数。下面利用“单变量求解”工具确定新产品保本销售。假设现在其他数据都不能改变，而唯一能够做出改进的是设法增大产品的销售数量。请问：销售数量必须增长到多少时，才能实现本产品的销售能够“保本”？下面就来介绍如何利用单变量求解解决这个问题，具体操作步骤如下。,图11-39“新开发产品的保本点计算”表格,步骤1将B4:C14单元格内容复制到B17:C27单元格区域。步骤2选定C27单元格，也就是销售利润所在的单元格，在“数据”选项卡下的“数据工具”组中单击“模拟分析”按钮，在展开的下拉列表中选择“单变量求解”命令，如图11-40所示。步骤3弹出“单变量求解”对话框，在“目标值”文本框中输入数字“0”，在“可变单元格”文本框中，输入这里需要确定的可变量销售收入所在的“$C$19”单元格，如图11-41所示。,图11-40选择“单变量求解”命令,图11-41“单变量求解”对话框,步骤4单击“确定”按钮，弹出如图11-42所示的“单变量求解状态”对话框，说明已经找到一个解，并且与目标值是完全一样的。步骤5单击“确定”按钮，得到单变量求解的结果，如图11-43所示，可以看出“保本点”销售为90000，也就是说如果其他各量都不变，必须销售90000个以上的本产品，才能实现赢利。,图11-42“单变量求解状态”对话框,图11-43利用单变量求解确定保本销售量,11.5使用“规划求解”工具分析营销决策,在实际的销售管理中，要完成一项目标任务的时候，就会经常会存在着多个因素，这些因素都具有一定的约束条件。下面将介绍如何在约束条件下进行最优确定。,11.5.1规划问题的特点以及“规划求解”工具的组成,对于规划求解问题的解决，在Excel中专门提供了“规划求解”加载宏工具。1规划问题的特点在计划管理当中，往往会遇到各种各样的规划问题，例如：人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批次的确定等。这类问题有一个共同的要求，那就是：如果合理的利用各种约束资源从而达到最佳的经济效益，也就是达到产量最高、利润最大、成本最低、费用最省等目标。一般来说，这类规划问题都具有如下三个特点。（1）所求的问题都有一个单一的目标，如求生产的最低成本、求运输的最佳的路线、求产品的最大盈利、求产品周期的最短时间以及求其他目标函数的最优值等。（2）总是有明确的不等式约束的条件。比如库存不能低于一定的数量，否则就会造成原料短缺或产品缺货的情况；生产的产品不能超过一定的额度，否则会造成商品积压等。（3）问题都有直接或间接影响约束条件的一组输入值。2Excel“规划求解”工具的组成规划求解就是在满足约束条件的前提下，调整决策变量，实现目标函数最优。因此，,可以说规划求解工具包括决策变量、目标函数和约束条件三部分。（1）决策变量：是指在实际的问题当有有一些待解决的未知因素，一个规划问题中可能会有一个决策变量，也有可能会有多个决策变量。一组决策变量代表一个规划求解的方案。在Excel的规划求解模型中，决策变量通常利用可变单元格进行表示。（2）目标函数：用来表示规划求解要达到的最终目标，如求最短路径、最大利润、最小成本、最佳产品组合等。在规划模型中，目标函数应该是决策变量的函数。也就是说，在Excel中目标函数与可变单元格有着直接或间接的联系，它可以进线性函数（对应规划问题称为“线性规划”），也可以是非线性函数（对应规划问题称为“非线性规划”）。（3）约束条件：是指实现目标的限制条件，规划求解是否有解，与约束条件有着密切的关系，它对可变单元格中的值起着直接限制的作用。约束的条件可以是等式，也可以不是等式。,11.5.2利用“规划求解”工具解决规划求解问题的流程,某电脑销售公司主要销售“戴尔”和“华硕”两种笔记本。因担心进货积压，公司规定“戴尔”笔记本一次性最多进货200台，“华硕”笔记本一次性最多进货200台。而受公司与资金的限制，两种笔记本的单次进货量不得超过300台。其中“戴尔”笔记本的平均利润为400/台，若“戴尔”笔记本每多进10台，单台“戴尔”笔记本的利润会增长5元。“华硕”笔记本的平均利润为350元/台，如果“华硕”每台笔记本多进10台，单台“华硕”笔记本的利润会上涨8元。那么该公司如何确定两种笔记本的单次进货量，才能使效益最大化呢？下面介绍如何使用“规划求解”工具来解决问题，具体操作步骤如下。步骤1在“开发工具”选项卡下的“加载项”组中单击“加载项”按钮，如图11-45所示。,步骤2弹出“加载宏”对话框，在“可用加载宏”列表中选中“规划求解加载项”复选框，最后单击“确定”按钮，如图11-46所示。,步骤3这时在“数据”选项卡下的“分析”组中会增加一个“规划求解”命令，说明加载成功，然后在Sheet5工作表中建立如图11-47所示的规划模型步骤4在B4单元格中输入公式“SUM(B2B3)”，按Enter键计算总进货量，接着在B6单元格中输入公式“=(400B2/10*5)*B2(350B3/100*8)*B3”，按Enter键计算总利润，如图11-48所示。步骤5单击“数据”选项，在“分析”组中选择“规划求解”命令，如图11-49所示。步骤6弹出“规划求解参数”对话框，在“设置目标”文本框中输入“$B$6”，然后在“通过更改可变单元格”文本框中输入“$B$2:$B$3”，接着选中“最大值”单选按钮，如图11-50所示，最后单击“添加”按钮。,图11-45单击“加载项”按钮,图11-46“加载宏”对话框,步骤7弹出“添加约束”对话框，在“单元格引用位置”文本框中输入“$B$2”，选择“=”约束条件，在“约束值”文本框中输入“0”，再单击“添加”按钮，添加第一个约束条件，如图11-60所示。步骤4在“单元格引用位置”文本框中输入“$B$5:$D$5”，选择“=”约束条件，在“约束值”文本框中输入“$B$7:$D$7”，再单击“添加”按钮，添加第二个约束条件，如图11-61所示。,图11-58运输成本最小问题的规划求解模型,图11-59“规划求解参数”对话框,图11-60设置第一个约束条件,图11-61设置第二个约束条件,步骤5在“单元格引用位置”文本框中输入“$E$2:$E$4”，选择“=”约束条件，在“约束值”文本框中输入“$G$2:$G$4”，如图11-62所示。步骤6单击“确定”按钮，返回“规划求解参数”对话框，单击“求解”按钮，如图11-63所示。步骤7弹出“规划求解结果”对话框，选中“保留规划求解的解”单选按钮，单击“确定”按钮，如图11-64所示。步骤8此时，在工作表中即可看到求解结果，如图11-65所示，可知最佳运输方案为:物流中心的L1的300吨货物全部运输到C3城市；物流中心的L2的200吨货物全部运输到,C1城市；物流中心的L3的300吨货物运输到C1城市70吨和C2城市230吨。这样各个城市的需求量都得到了满足，各个物流中心也没有超过发货数量，总运输成本为57150元。,图11-62设置最后一个约束条件,图11-63参数设置后的结果,图11-64“规划求解结果”对话框,图11-65规划求解的计算结果,表11-3各区域中每一个网点的初始投资额及其年利润的预测值,11.5.4利用“规划求解”工具分析设置快捷酒店网点布局,M快捷酒店计划进入T市进行网点布局，根据该市的整体状况，该公司营销将其分成了核心主城区、经济开发区、周边郊县区和高新技术区四个区域，并从这四个不同区域中拟订了12个网点设置Li(i=1，2，3，12)。根据市场调查，这12个网点位置的初始投资额和每年的利润预测值，如表11-3所示（单位:万元）。,目前，公司总部提出了如下的网点布局指导原则。在该市核心主城区，至少要从L1、L2、L3、L4中选择开发两个网点。在该市经济开发区，至少要从L5、L6中选择开发一个网点。在该市周边郊县区，至多要从L7、L8、L9中选择开发两个网点。在该市高新技术区，至多要从L10、L11、L12中选择开发两个网点。请问:在该市的总投资额度不能超过1000万元的前提下，该饭店的营销部应该选择哪几个地点来开发和建设快捷连锁酒店，才能使总的年利润最大？快捷连锁酒店经营地点的设置问题，是一种连锁企业经常遇到的网点布局问题，类似的还有超市的网点布局、银行储蓄所的网点布局等。对于每一个网点，都存在开发与不开发两种情况，所以这个问题一般用01整数规划问题来解决。,根据题目中的要求，要先建立网点布局问题的规划求解模型。设Xi为01的变量（其中i1，2，3，12），也说是说Xi=0或者是Xi=1。其中结果为0表示Li网点没有被选中，而结果为1则表示Li网点被选中。本问题的目标就是使总利润最大，因此，目标函数为：MAXz=32X145X230X328X464X576X696X725X842X932X1078X1150X12。约束条件有以下几条。（1）全部投资总预算不大于1000万，也就是说：100X1180X2116X3100X4210X5194X6242X788X8109X981X10178X11124X121000。（2）在核心主城区，至少选择两个，则有X1X2X3X42。（3）在经济开发区，至少选择一个，则有X5X61。（4）在周边郊县区，至多选择两个，则有X7X8X92。（5）在高新技术区，至多选择两个，则有X10X11X122。步骤1根据上述分析，建立规划求解问题的模型表格，并进行相关项目的格式设置和