8.1 利润规划业务概述
本章学习目的：
·了解利润规划在财务管理信息化中的作用和地位
·掌握利用Excel工具软件进行利润规划的思路及方法
·掌握利润规划模型的分析和制作
本章关键词：
财务预测、利润规划、线性规划
在财务管理工作中，收入预测及利润规划是重要的内容之一。收入是指企业在日常经营活动中形成的，会导致所有者权益增加的，与所有者投入资本无关的经济利益的总流入。利润规划是现代企业科学管理方法之一。它通过对企业未来一段期间内，经过努力应达到的最优化利润即目标利润进行科学的预测，控制、规划、掌握其影响因素及变化规律，为管理者提供决策信息的活动。
企业进行目标利润规划主要适用的方法是本量利分析法。这种方法能通过业务量、成本利润之间的关系对企业生产经营活动进行规划和控制。它是目标利润管理的基本方法。Excel提供了丰富的方法支持企业利润规划模型的建立和使用。
8.1.1 预测及预测方法
财务管理的关键在于决策，决策的基础在于预测。预测分析是根据相关资料的信息，运用专业知识、经验和科学的方法，对事件的未来趋势做出估计和推测的过程。
预测分析的方法较多，但基本方法可以归纳为两类。
1.定量分析法
定量分析法主要应用数据方法和现代化的计算工具对经济信息进行科学的加工与处理，建立预测分析数学模型，揭示各有关变量之间的规律性联系，并做出预测结论。定量分析法可以分为以下两种类型。
（1）趋势预测分析法
亦称为时间序列分析法，它是将预测对象的历史数据按照时间序列排列，应用数学方法处理、计算，以此预测其未来发展趋势的分析方法。具体计算方法有算术平均法、移动加权平均法、指数平滑法、回归分析法、二次曲线法等。
（2）因果预测分析法
根据预测对象与其他相关指标之间的相互依存、相互制约的规律性联系，建立相应的因果分析数学模型进行预测分析的方法。它的实质是根据事物发展的“相关性原则”，推测事物发展的趋势。具体方法有本量利分析法、投入产出法等。
2.定性分析法
定性分析又叫做经验判断法，是主要依靠预测人员的经验、知识、判断和分析能力，推断事务的性质和发展趋势的方法。在缺乏历史数据或者影响历史数据的环境发生重大变化时，可以采用定性分析的方法。但定性分析法并不适合采用工具软件建立模型来进行分析和预测，一般情况下主要采用讨论、推理等方法实现。
在实际工作中，常常将两种方法结合起来。以定性分析为指导，以定量分析为验证。在财务管理信息化工作中，主要是借助于Excel强大的数据处理功能，通过定量分析的方法，进行财务趋势预测和因果预测。
8.1.2 利用Excel实现趋势预测
1.趋势分析图形的制作
利用Excel强大的图形制作能力，可以建立关于分析对象的图表，以观察其变化趋势。但这种方法只能简单地观测到对象变化的趋势，精确程度不高。因此，趋势分析图形常常作为简单的趋势预测。
例8-1某企业各年的主营业务收入，制作图表预测该公司未来三年的主营业务收入的变化趋势。
制作步骤如下。
（1）建立原始数据表，输入各年度主营业务收入数据。
（2）单击“图表向导”按钮，在弹出的“图表向导—步骤4之1—图表类型”对话框中，选择折线图。
（3）在“图表向导—步骤4之2—图表数据源”对话框中，选择数据源区域。
（4）单击【完成】按钮，可以得到粗略的主营业务收入的折线图。
（5）为了进一步体现主营业务收入的变化趋势，可以添加趋势线。鼠标单击上图中数据线的任意位置，在弹出的快捷菜单中选择“添加趋势线”命令，弹出相应的对话框。
（6）在“类型”选项卡中选择“线性”图形；在“选项”选项卡中的“趋势预测”选项组中调节“前推”微调按钮为3。
（7）单击【确定】按钮，可以得到下所示的趋势分析图形，并可据此预测以后三年的主营业务收入。
在选择图形类型时，当所描述的数据源数据量较大时，可以采用散点图。方法同上，利用散点图，可以得到平滑的曲线，可以较为清楚地判定两个变量之间的函数关系。例如，能够判定时间序列和主营业务收入之间是否是线性关系。
2.算术平均法的应用
算术平均法的计算模型如下。
预计销售收入=各期销售收入之和/期数
这种方法计算简单，但忽略了各期数据波动对预测结果的影响。适用于预测对象波动不大、比较稳定的企业。
例8-2某商品1～11月份销售额，按照算术平均法预测12月份销售额。
在12月份销售额单元格内定义公式“=sum（B3：L3）/11”，得到12月份预测结果为24。
3.移动平均法的应用
移动平均法是根据近期数据推测对预测值影响较大的事实，把平均期逐期移动。其计算模型为：
如果时间序列没有明显的趋势变动，使用一次移动平均法就能够准确地反映实际情况，那么就可以进行预测了。具有线性趋势的时间序列数据，通过一次移动平均后，比原始曲线平滑了很多，但还不能满足要求，必须在一次移动平均的基础上再做二次移动平均，找出曲线发展的方向和趋势，然后建立直线趋势的预测模型进行预测。
例8-3已知2006年1～11月份的主营业务收入，使用移动平均法预测2007年1月份的主营业务收入。模型制作过程如下。
（1）在Excel中，建立基础数据表。
（2）定义截距a和斜率b公式。在B16单元格定义公式“=C14*2-D14”，在B17单元格输入公式“=（C14-D14）*2/3”。
（3）在B18单元格内输入预测值公式“=B16+B17*2”，其中的系数2表示11月与预测月之间相距两个月。其中，由于尚未使用移动平均，则a、b的值均为0。
（4）第一次计算移动平均数。
第一步，选择“工具”→“数据分析”命令，弹出“数据分析”对话框，选择“移动平均”选项。
如果“工具”菜单下没有“数据分析”，必须在Excel中安装“分析工具库”。操作过程为：在“工具”菜单下选择“加载宏”，弹出的对话框。
选择分析工具库后，单击【确定】按钮返回，则可以在“工具”菜单下找到“数据分析”命令。如果在“加载宏”下没有“分析工具库”，则需要单击【浏览】按钮，定位到“分析工具库”所在文件夹（默认Microsoft OfficeOfficeLibraryAnalysis）中加载宏文件Analys32.xll，加载该功能。若没有找到该文件，则应补充安装该功能。
第二步，在弹出的“移动平均”对话框中，确定相关参数。
在输入区域中选择区域B4：B14，间隔为4，输出区域为C4：C14，单击【确定】按钮，则可生成第一次移动平均后的数据结果。
第三步，从上图可以看出，主营业务收入具有明显增长的趋势，因此，要进行预测，必须先做二次移动平均。二次移动平均是在一次移动平均的基础上进行的，可以按照上述方法进行二次移动平均，得到二次移动平均结果。
4.预测函数的应用
Excel中提供的预测函数有以下几个。
（1）FORECAST
功能：根据已有的数值计算或预测未来值。此预测值为基于给定的x值推导出的y值。已知的数值为已有的x值和y值，再利用线性回归对新值进行预测。可以使用该函数对未来销售额、库存需求或消费趋势进行预测。
语法：FORECAST（x， known_y's， known_x's）
参数说明：
x为需要进行预测的数据点。
known_y's为因变量数组或数据区域。
known_x's为自变量数组或数据区域。
如果x为非数值型，函数FORECAST返回错误值#VALUE！。
如果known_y's和known_x's为空或含有不同个数的数据点，函数FORECAST返回错误值#N/A。
如果known_x's的方差为零，函数FORECAST返回错误值#DIV/0！。
（2）TREND
功能：返回一条线性回归拟合线的值。即找到适合已知数组known_y's和known_x's的直线（用最小二乘法），并返回指定数组new_x's在直线上对应的y值。
语法：TREND（known_y's， known_x's， new_x's， const）
参数说明：
known_y's是关系表达式y=mx+b中已知的y值集合。
如果数组known_y's在单独一列中，则known_x's的每一列被视为一个独立的变量。
如果数组known_y's在单独一行中，则known_x's的每一行被视为一个独立的变量。
known_x's是关系表达式y=mx+b中已知的可选x值集合。
数组known_x's可以包含一组或多组变量。如果只用到一个变量，只要known_y's和known_x's维数相同，它们可以是任何形状的区域。如果用到多个变量，known_y's必须为向量（即必须为一行或一列）。
如果省略known_x's，则假设该数组为{1，2，3，……}，其大小与known_y's相同。
new_x's为需要函数TREND返回对应y值的新x值。
new_x's与known_x's一样，每个独立变量必须为单独的一行（或一列）。因此，如果known_y's是单列的，known_x's和new_x's应该有同样的列数。如果known_y's是单行的，known_x's和new_x's应该有同样的行数。
如果省略new_x's，将假设它和known_x's一样。
如果known_x's和new_x's都省略，将假设它们为数组{1，2，3，……}，大小与known_y's相同。
const为一逻辑值，用于指定是否将常量b强制设为0。
如果const为TRUE或省略，b将按正常计算。
如果const为FALSE， b将被设为0（零），m将被调整以使y=mx。
例8-4依例8-2，预测12月份的销售收入，步骤如下。
第一步，建立基础数据表。
第二步，在M3单元格中，定义TREND公式。单击图标，在统计函数类别中，选择TREND函数。
第三步，单击【确定】按钮，在弹出的“参数选择”对话框中，选择对应的参数。其中known_y’s参数选择区域B3：L3，为各月销售额；known_x’s参数选择区域B2：L2，为对应的月份（时间序列数），new_x’s为M2，表示对应的预测月份值，const参数可以省略。
参数选择完毕后，单击【确定】按钮，生成最后的结果为12月份预测值27.236。
（3）LINEST
功能：使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函数返回数值数组，所以必须以数组公式的形式输入。
直线的公式为：
y=mx+b或
y=m1x1+m2x2+……+b（如果有多个区域的x值）
式中，因变量y是自变量x的函数值；m值是与每个x值相对应的系数；b为常量。注意y、x和m可以是向量。LINEST函数返回的数组为{mn， mn-1，……m1，b}。LINEST函数还可返回附加回归统计值。
语法：LINEST（known_y's， known_x's， const， stats）
参数说明同TREND。
8.1.3 利用Excel进行因果分析
1.单变量求解
“单变量求解”是一组命令的组成部分，这些命令有时也称做假设分析工具。如果已知单个公式的预期结果，而用于确定此公式结果的输入值未知，则可使用“单变量求解”功能，通过选择“工具”→“单变量求解”命令即可使用“单变量求解”功能。当进行单变量求解时，Microsoft Excel会不断改变特定单元格中的值，直到产生此单元格的公式返回所需的结果为止。
例8-5利用单变量求解计算盈亏平衡点。
某公司生产电子元件，固定成本为3000元，单位可变成本2元，单位售价5元，计算该公司生产该元件的盈亏平衡点。
在Excel中单变量求解常用来考察在目标值一定的情况下，影响该目标值的要素的取值。计算盈亏平衡点时，实际上是假设利润为零的情况下，考察产量信息。根据上述资料，可以在Excel中建立表，并可定义相关单元格公式。
在上述表格中，应用单变量求解。选择“工具”→“单变量求解”命令，弹出的对话框。
在该对话框中，确定目标单元格为利润所在单元格，目标值为0，可变单元格为产量所在单元格，单击【确定】按钮，弹出“单变量求解状态”对话框，确定后生成盈亏平衡点数据，计算结果为产量1000。
2.模拟运算表的应用
运算表是一个单元格区域，用于显示公式中某些值的更改对公式结果的影响。运算表提供了一种快捷手段，它可以通过一步操作计算出多种情况下的值；同时它还是一种可以查看和比较工作表中不同变化所引起的各种结果的有效方法。根据影响目标结果的变量的数量，可以分为单变量模拟运算表和双变量模拟运算表。
（1）单变量模拟运算表
单变量模拟运算表可以考察单个变量的变动，对目标结果的影响。依例8-5，企业管理者想了解当单位变动成本对利润的影响程度，则可以通过单变量模拟运算表进行分析。
第一步，建立基础数据表格。
在图中，首先建立变量变动数据区域D4：D14，并采用自动填充按照等差数列填入单位可变成本的变动数据范围。同时在E4单元格定义公式，“=（B5-B4）*B7-B3”。一般来讲，目标单元格应该与变动数据区域单元格存在直接的公式联系。
第二步，选择模拟运算区域为D3：E14。
第三步，选择“数据”→“模拟运算表”命令，弹的对话框。
目前，模拟数据是按列组织的，因此，选择“输入引用列的单元格”，并指定单元格地址为单位变动成本所在单元格地址B4。单击【确定】按钮，生成最终结果。
（2）双变量模拟运算表
双变量模拟运算表考察两个变量对目标值的影响。依例8-5，可将单位售价作为第二个变量。双变量模拟运算表实际上是建立以变量为维度的二维表格。例8-5中，生成横向为不同单位售价，纵向为不同单位可变成本的二维表格，表格中每一个单元格的值，即为在不同单位售价和单位可变成本组合下利润的值。其制作过程如下。
第一步，建立基础数据表格，列方向按照单位变动成本展开，行方向按照单位售价展开，形成二维表格。数据均可使用自动填充方式得到。同时在B12单元定义利润计算公式“=（B5-B4）*B7-B3”。注意，该公式定义时，必须和行、列数据通过公式相联系。
第二步，选择模拟单元格区域，即B12：H23。
第三步，选择“数据”→“模拟运算表”命令，在弹出的对话框中，设置“输入引用行的单元格地址”为B5，设置“输入引用列的单元格地址”为B4，单击【确定】按钮，生成最终结果。
3.规划求解
“规划求解”是一组命令的组成部分，这些命令有时也称做假设分析工具。借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式的最优值。“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。通过调整所指定可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。在创建模型过程中，可以对“规划求解”模型中的可变单元格数值采取约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。使用可通过更改其他单元格来确定某个单元格的最大值或最小值。规划求解实际上是在一系列约束条件下，实现对方程的求解过程。
例8-6某公司生产和销售A、B两种产品，它们的生产与销售数据。
该公司在机器与人工两种资源上的每月生产能力分别为机器68 250小时和人工47 250小时，假定所有产品只要生产出来都能按照预定价格销售出去。
要求：在限定的生产条件下，分别确定使公司利润达到极大值时，对两种产品销售量的最优安排。
采用规划求解对该问题求解如下。
问题分析：
设产品A、产品B的销售量为x？和x？，销售总利润为y，则根据上述条件，可以列出求利润极大值的方程。
Max y=（11.3-6.3）x？+（16.4-10）x？-300-700
Max y=5x1+6.4x？-1000
约束条件为：
76x？+120x？＜=68250
85x？+60x？＜=47250
x？＞=0，x？＞=0
（3）建立求解问题的Excel表格。
其中定义有关公式。
在本模型中，产品销售数量C8：D8是可变单元格，E3：E4是约束条件所在单元格，C13是目标单元格。
（4）采用规划求解
选择“工具”→“规划求解”命令，弹出“规划求解参数”对话框。如果菜单中没有“规划求解”，则需要选择“工具”→“加载宏”命令，添加“规划求解”菜单进行调用，或运行补充安装。
在该对话框中，设置目标单元格为C13，即利润总额单元格。在使用规划求解时，目标单元格必须定义公式，目标单元格的值与变动单元格之间直接或间接连接，并选择最大值，可变单元格定义为C8：D8。
单击【添加】按钮，可以添加约束条件。弹出“添加约束”对话框。
选择单元格引用位置E3，约束值单元格为F3，表示两种产品生产所需的总机器小时数不超过总机器小时数。由于在单元格引用位置不允许进行公式计算，因此，在进行表格设计时，应添加辅助运算的单元格，以完成约束条件值的计算。
单击【添加】按钮，可以继续添加新的约束条件，单击【确定】按钮可以返回规划求解定义状态。在本例中，需要定义的约束条件有四个，定义完毕后，规划求。
（5）结果生成
在上图中，单击【求解】按钮，可以生成最终的运算结果。
（6）变更规划求解条件
企业经营环境的变化，必将带来企业决策条件的变化。规划求解提供了对约束条件的修改能力。用户可以使用该功能实现决策约束条件的维护。
1）修改约束条件
上例中，假定可提供的单位机器小时数提高到70000，单位人工小时数提高到50000，则可以直接修改对应单元格数据，并重新运行规划求解生成结果。
2）修改规划求解的限制式
上例中，主管希望A产品至少生产300件，其余的资源用于B产品生产，则需要修改规划求解的限制式。
选择“工具”→“规划求解”命令，弹出“规划求解参数”对话框。
选择需要修改的限制式，并单击【更改】按钮，弹出“改变约束”对话框。
输入修改后的约束值，单击【确定】按钮返回。重新求解，即可生成最终结果。
3）设置整数约束
假定上例中，要求A、B两种产品的最终产量均以整数表示，则需要在约束条件中增加“取整”的约束。在打开的“改变约束”对话框中，添加C8：D8单元取整的约束条件，并重新求解。
（7）规划求解报告的生成
规划求解得出最优解后，可以生成相应的报告提交给企业的管理者。Excel中提供了三种可供选择的报告：运算结果报告、敏感性报告和极限制报告。
在“规划求解结果”对话框中，可以选择生成的报告。
1）运算结果报告
运算结果报告列出目标单元格、可变单元格及约束条件的取值情况。
2）敏感性报告
提供有关求解结果对“规划求解参数”对话框的“目标单元格”框中所指定的公式或约束条件微小变化的敏感程度的信息。含有整数约束条件的模型不能生成该报告。对于非线性模型，该报告提供递减梯度和拉格朗日乘数；对于线性模型，该报告中将包含递减成本、阴影价格、目标式系数（允许的增量和允许的减量）及约束右侧的区域。
3）极限值报告
列出目标单元格和可变单元格及其各自的数值、上下限和目标值。含有整数约束条件的模型不能生成该报告。下限是在保持其他可变单元格数值不变并满足约束条件的情况下，某个可变单元格可以取到的最小值。上限是在这种情况下可以取到的最大值。
8.2 利润规划模型应用
8.2.1 销售额预测应用案例
例8-7某超市连锁公司是拥有10家连锁店的零售企业。该公司拟在一居民小区附近新开一家连锁店，需要对该店的销售额进行预测。为此，该公司整理了已开的10家店的销售额和附近居民数量的数据，希望能够发现二者之间的联系，并作为新开店预测销售收入的依据。
10家店销售额与附近居民数量的数据表。
说明：
销售额为单位表示，每单位销售额表示1000元，居民数每单位表示1000人。
解题步骤如下。
制作基础数据表，并根据基础数据表绘制趋势分析图。
从上表不难发现，周围居民较多的店销售量较大，但两者之间是怎样的关系呢？通过绘制趋势分析图，可以初步判定两个变量之间的关系。
选择散点图，可以初步判断各变量之间的关系。将居民数作为横坐标，将销售额作为纵坐标，得到点图。
为该图形添加趋势线，可以看出居民数与销售量之间基本上是线性相关，即居民数较多的区域销售量较大，仔细观察发现，很少有数据可以直接落在直线上，但可以使用最小二乘法，推导出回归方程为：
也就是说，上述预测可以使用FORECAST函数来进行销售量的预测。假定，需要预测居民数为30时的销售额，则可定义公式。
为了进一步增强模型的通用性，可以通过增加“窗体”工具栏中的微调按钮，建立动态分析模型，帮助用户使用。建立好的模型。
8.2.2 利润规划应用案例
例8-8利民公司是一家生产化工产品的公司，该公司生产和销售甲、乙两种产品。在生产研讨会议之后，预计每月可使用的原料总额为1200千克，可占用的人工工时为800小时。以制造部门目前的生产效率来看，每生产1千克的甲产品，需要消耗人工工时2小时，耗费原料4.5千克，并能获得260元毛利，其他成本费用不计；每生产1千克的乙产品，需要消耗人工工时3小时，耗用原料3.5千克，并可获得300元毛利，其他成本费用不计。
利民公司要获得最大利润，每个月应各生产多少千克的甲产品和乙产品。
解题步骤如下。
（1）明确给定问题的求解模型。
设甲产品与乙产品每月生产量分别为x1，x2千克，预期总利润为y，则可以列出利润求解方程：
（2）根据上述模型，构建基础数据表。
（3）在上图中，定义相关计算公式。
（4）应用规划求解，确定规划求解相关参数。
在本例中，设置目标单元格为C16，即总收益所在单元格；设定可变单元格为C8：D8，表示求解甲、乙两种产品所在单元格，约束条件即约束方程表示内容。
（5）返回求解结果。
（6）结论。
经规划求解可知，本月生产计划做如下安排，则利润最大：安排甲产品生产123.08千克，安排乙产品生产184.62千克。当表中相关参数发生变化时，可修改相关参数，重新运行规划求解即可。
习题
1.某饭店拥有近三年的各月销售数据。
分别使用移动平均法、趋势分析法、指数平滑法预测第四年度各月的销售额，并分析各种预测方法之间的差异。
2.某公司制造甲、乙两种产品均需经过两个车间的加工过程。为两种产品的生产时间及对利润的贡献。
在下一个生产周期内，公司又将900小时可用生产时间分配到两个车间。确定最佳的生产计划及生产时间分配方案，使得该公司获得的利润最大。
3.试利用线性规划方法对财务计划和投资组合分析的可行性及一般方法进行分析。
