Excel在财务预测中的应用

60/60第4章Excel在财务预测中的应用

4.1财务预测概述

财务预测，是指对企业未来的收入、本钱、利润、现金流量及融资需求等财务指标所作的估计和推测。财务预测是编制投资和融资方案的基础，是公司制订成长战略的根本要素。称职的财务管理人员应该能够充分利用公司的有关信息资料，预测公司的财务需要并做出相应的安排。公司成长主要由销售增长来决定，销售增长需要相应的资产增长，如果企业已经是满负荷运转，不仅流动资产、而且固定资产都要增长，而资产增长需要相应的融资增长。同时，企业进行对外投资和调整资本结构，也需要筹措资金。企业所需要的这些资金，一局部来自企业内部，另一局部通过外部融资取得。由于对外融资时，企业不但需要寻找资金提供者，而且还需做出还本付息的承诺或提供企业盈利前景等信息，使资金提供者确信其投资是平安的并可获利，这个过程往往需要花费较长的时间。因此，企业需要预先知道自身的财务需求，确定资金的需要量，提前安排融资方案，以免影响资金周转。财务预测有助于改善企业的投资决策。虽然投资是决定筹资与否和筹资多少的重要因素，但是根据销售前景估计出的融资需求，并不一定能够得到全部满足。这时，就需要根据可能筹措到的资金来安排销售增长以及有关的投资工程，使投资决策建立在可行的基础上。财务预测一般按以下几个步骤进行。1．销售预测销售预测是指根据市场调查所得到的有关资料，通过对有关因素的分析研究，预计和测算特定产品在未来一定时期内的市场销售量水平及变化趋势，进而预测企业产品未来销售量的过程。企业的一切财务需求都可以看作是因销售引起的，销售量的增减变化，将会引起库存量、现金流量、应收与应付账款以及公司其他资产和负债的变化。因此销售预测在企业预测系统中处于先导地位，它对于指导利润预测、本钱预测和资金预测，进行长短期决策，安排经营方案，组织生产等都起着重要的作用。2．估计收入、费用和利润收入和费用与销售量之间也存在一定的函数关系，因此，可以根据销售数据估计收入和费用，并确定净利润。净利润和股利支付率，共同决定了内部留存收益所能提供的资金数额。3．估计需要的资产资产通常是销售收入的函数，根据历史数据可以分析出二者之间的函数关系。根据预计销售收入和资产与销售之间的函数关系，可以预测所需资产的总量。某些流动负债也是销售收入的函数，相应地也可以预测负债的自发增长额，这种增长可以减少企业外部融资的数额。4．估计所需融资根据预计资产总量，减去已有的资金来源、负债的自发增长和内部提供的留存收益，可得出所需的外部融资数额。第4章Excel在财务预测中的应用

4.2财务预测的分析方法

预测分析的方法有很多种，企业应根据不同的需要选择不同的预测方法。总的来说，预测分析方法可分为两大类：定量预测法和定性预测法。4.2.1

定量预测法定量预测法是指在掌握与预测对象有关的各种要素的定量资料的基础上，运用现代数学方法进行数据处理，从而建立起能够反映有关变量之间关系的各类预测模型的方法。在财务预测中，经常使用的定量预测法主要有以下几种。4.2.1.1

移动平均法移动平均法是一种改进的算术平均法，是一种最简单的自适应预测模型。它根据近期数据对预测值影响较大，而远期数据对预测值影响较小的事实，把平均数逐期移动。移动期数的大小视具体情况而定，移动期数少，能快速地反映变化，但不能反映变化趋势；移动期数多，能反映变化趋势，但预测值带有明显的滞后偏差。常用的移动平均法主要有一次移动平均法和二次移动平均法。1．一次移动平均法一次移动平均法是根据时间序列，逐期移动，依次计算包含一定项数的时间序列平均数，形成一个平均时间数序列，并据此进行预测。预测模型为式中—第t+1期的预测值；、、…、—将被平均的n个观测值；n—移动平均的项数，即移动期数。在实际预测中，可以多取几个n数，并将得到的预测值与实际值进行比较，选用误差最小的n值。2．二次移动平均法二次移动平均法是对时间序列计算一次移动平均数后，再对一次移动平均数序列进行一次移动平均运算。预测模型为。式中—二次移动平均数；—第t+1期的预测值，即。二次移动平均法解决了一次移动平均法只能预测下一期的局限性，它可以进行近、短期的预测。但它仍不能解决中长期的预测问题。4.2.1.2

指数平滑法指数平滑法实际上也是一种加权平均法，是一种改进的加权平均法，预测模型为式中

—平滑系数，0≤≤1。在指数平滑法中，确定适宜的值和初始值是非常重要的。越大，t期的实际值对新预测值的奉献就越大；越小，t期的实际值对新预测值的奉献就越小。一般情况下，可以取几个不同的值进行预测，比较它们的预测误差，选择预测误差最小的值。4.2.1.3

回归分析预测法回归分析预测法是通过研究两组或两组以上变量之间的关系，建立相应的回归预测模型，对变量进行预测的一种预测方法。1．回归分析预测法的根本程序进行回归分析的步骤如下：（1）收集有关资料。将各种可能的影响因素的有关数据尽可能多地收集起来。（2）判断趋势。根据收集到的数据，判断其变化趋势，从而为建立相应的数学模型做准备。对于变量不多的问题，可以通过绘制散点图来判断变化趋势。（3）建立预测数学模型。根据历史数据的变化趋势，选择相应的描写该问题的数学模型，并采用相关的计算技术来估计数学模型的参数。（4）相关检验。对建立的预测数学模型，必须进行有关的检验，主要是通过计算预测模型的相关系数、方差（或标准差）以及显著性等指标，来判断预测模型的准确性、是否需要修正、采用何种方法修正等。2．回归模型建立的方法建立回归模型的一般方法是采用最小二乘法，其原理如下：考虑m个自变量x1、x2、…、xm和因变量y的关系，现有n组观测数据，不同xki（k=1,2,…,m；i=1,2,…,n）下的y的观测值为yi，函数y=f(xk)的待估计参数为ak（k=1,2,…,m+1，这里，每个自变量有一个待估计系数，还有一个待估计常数，故有m+1个待估计参数），通过回归预测模型得到不同xki下的预测值为，则有：残差平方和SE：剩余标准差SS：相关系数R2：y为观测值yi的平均值：那么，最小二乘法的原理就是寻找最优的待估计参数ak，使残差平方和最小。3．财务预测中常用的几种回归模型（1）一元线性回归模型当只有两个变量（一个自变量和一个因变量），并且它们之间存在线性关系时，可以用一元线性回归模型来描述。一元线性回归模型为式中a、b—回归系数，其中a代表截距，b代表斜率。（2）一元非线性回归模型当变量x和y之间的关系不能用线性关系来描述时，则需要建立一元非线性回归模型。根据变量x和y之间的关系，一元非线性回归模型常见的几种情况有：对数模型：指数模型：乘幂模型：双曲线模型：以上几种一元非线性模型均可通过数学变换化成一元线性模型。（3）多元线性回归模型当自变量有两个或两个以上，且因变量与这些自变量之间呈线性组合关系时，它们就构成了多元线性回归模型，模型形式为式中a、b1、b2、…、bm—估计参数；x1、x2、…、xm—自变量。（4）多元非线性回归模型多元非线性回归模型用来描述因变量与多个自变量之间呈非线性组合关系的情况。例如，柯柏—道格拉斯生产函数就是典型的多元非线性模型：式中：L和K分别为劳动力和固定资本；a、b、c为系数。4.2.1.4

模拟法在企业的实际经济活动中，各种经济参数往往并不是确定的，而是随机变化的，比方产品的销售量往往随市场的变化而变化，在这种情况下，就需要对这些参数的不确定性进行分析，而对其预测也就需要采用与传统确实定性分析不同的方法来进行。一般情况下，可以采用模拟法来解决不确定性情况下的财务预测问题，概率法、蒙特卡罗模拟方法就是较实用的方法。4.2.2

定性预测法定性预测法是由有关方面的专业人员或专家根据自己的经验和知识，结合预测对象的特点进行综合分析，对事物的未来状况和开展趋势作出推测的预测方法。定性预测法由于带有较多的个人主观性，因而在实践中最好作为一种补充的预测方法。第4章Excel在财务预测中的应用

4.3Excel中的有关预测函数及其应用(1)

Excel提供了关于估计线性模型和指数模型参数的几个预测函数。线性模型和指数模型的数学表达式如下：线性模型：y=mx+b或y=m1x1+m2x2+…+b指数模型：或式中，y为因变量；x是自变量；m、m1、...、mn-1、mn、b分别为预测模型的待估计参数。Excel提供的预测函数主要有LINEST函数、LOGEST函数、TREND函数、GROWTH函数、FORECAST函数、SLOPE函数和INTERCEPT函数，它们所使用的参数都根本相同，现列于表4-1中，以供参考。表4-1

预测函数的参数及含义参数含义known_y's

因变量y的观测值集合known_x's

自变量x的观测值集合。它可以是一个变量（即一元模型）或多个变量（即多元模型）的集合。如果只用到一个变量，只要known-y's和known-x's维数相同，它们可以是任何形状的选定区域。如果用到不只一个变量，known_y's必须是向量（也就是说，必须是一行或一列的区域）。如果省略known_x's，则假设该数组是{1,2,3...}，其大小与known_y's相同const逻辑值，指明是否强制使常数b为0（线性模型）或为1（指数模型）。如果const为TRUE或省略，b将被正常计算。如果const为FALSE，b将被设为0（线性模型）或设为1（指数模型）stats逻辑值，指明是否返回附加回归统计值。如果stats为TRUE，则函数返回附加回归统计值，这时返回的数组为{mn，mn-1，...，m1，b；sen，sen-1，...，se1，seb，r2，sey；F，df；ssreg，ssresid}。如果stats为FALSE或省略，函数只返回系数预测模型的待估计参数m、mn、mn-1、...、m1和b。附加回归统计值返回的顺序见表4-2。表4-2中的各参数说明见表4-3。如果要得到附加回归统计值数组中的值，需用INDEX函数将其取出表4-2

附加回归统计值返回的顺序

1234561mnmn-1…m2m1b2sensen-1…se2se1seb3r2sey

4Fdf

5ssregssresid

表4-3

各参数说明参数说明se1,se2,...,sen系数m1,m2,...,mn的标准误差值Seb常数项b的标准误差值（当const为FALSE时，seb=#N/A）

参数说明r2相关系数，范围在0到1之间。如果为1，则样本有很好的相关性，Y的估计值与实际值之间没有差异。反之，如果相关系数为0，则回归方程不能用来预测Y值seyY估计值的标准误差FF统计值或F观察值。使用F统计可以判断因变量和自变量之间是否偶尔发生过观察到的关系Df自由度。用于在统计表上查找F临界值。所查得的值和函数LINEST返回的F统计值的比值可用来判断模型的置信度ssreg回归平方和ssresid残差平方4.3.1

LINEST函数LINEST函数的功能是使用最小二乘法计算对数据进行最正确线性拟合的直线方程，并返回描述此线性模型的数组。因为此函数返回数值为数组，故必须以数组公式的形式输入。函数公式为=LINEST（known_y's，known_x's，const，stats）下面举例说明LINEST函数的应用。1．一元线性回归分析LINEST函数可用于一元线性回归分析，也可以用于多元线性回归分析，以及时间数列的自回归分析。当只有一个自变量x（即一元线性回归分析）时，可直接利用下面的公式得到斜率和y轴的截距值以及相关系数：斜率：INDEX（LINEST（known_y's，known_x's）,1,1）；或INDEX（LINEST（known_y's,known_x's）,1）截距：INDEX（LINEST（known_y's，known_x's）,1,2）；或INDEX（LINEST（known_y's,known_x's）,2）相关系数：INDEX（LINEST（known_y's,known_x's,true,true）,3,1）【例4-1】某企业1～9月份的总本钱与人工小时及机器工时的数据如图4-1所示。假设总本钱与人工小时之间存在着线性关系，则在单元格B13中插入公式“=INDEX(LINEST(B2:B10,D2:D10),2)〞，在单元格B14插入公式“=INDEX(LINEST(B2:B10,D2:D10),1)〞，在单元格B15插入公式“=INDEX(LINEST(B2:B10,D2:D10,TRUE,TRUE),3,1)〞，即得总本钱与人工小时的一元线性回归分析方程为：Y=562.72756+4.41444X1，相关系数为R2=0.99801，如图4-1所示。图4-1

一元线性回归分析2．多元线性回归分析仍以例4-1的数据为例，首先选取单元格区域A17:D21，再以数组公式方式输入公式“=LINEST(B2:B10,C2:D10,TRUE,TRUE)〞，即得该二元线性回归的有关参数如图4-2所示，从而得到：图4-2

二元线性回归分析回归方程：Y=471.4366+3.6165X1+3.4323X2相关系数：R2=0.9990标准差：Sey=11.7792。4.3.2

LOGEST函数LOGEST函数的功能是在回归分析中，计算最符合观测数据组的指数回归拟合曲线，并返回描述该指数模型的数组。由于这个函数返回一个数组，必须以数组公式输入。LOGEST函数的公式为=LOGEST(known_y's,known_x's,const,stats)【例4-2】某企业12个月某产品的生产量（X）与生产本钱（Y）的有关资料如图4-3所示，假设它们之间有如下关系：。选取单元格区域B15:C18，输入公式“=LOGEST(C2:C13,B2:B13,TRUE,TRUE)〞（数组公式输入），即得回归参数，如图4-3所示，参数m=0.8887，参数b=1891.7729，生产本钱与生产量的回归曲线为：Y=1791.7729×0.8887X，相关系数R2=0.95885。图4-3

指数回归回归方程的系数及相关系数也可以利用下面的公式直接计算参数m：INDEX(LOGEST(C2:C13,B2:B13),1)=0.8887参数b：INDEX(LOGEST(C2:C13,B2:B13),1,2)=1791.77294.3.3

TREND函数TREND函数的功能是返回一条线性回归拟合线的一组纵坐标值（y值），即找到适合给定的数组known_y's和known_x's的直线（用最小二乘法），并返回指定数组new_x's值在直线上对应的y值。TREND函数的公式为=TREND(known_y's,known_x's,new_x's,const)式中

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一样。【例4-3】某企业过去一年的销售量为以下数据：{300，356，374，410，453，487，501，534，572，621，650，670}，将它们保存在单元格A1:A12中，则下一年的1、2、3月的销售量预测步骤为：选中单元格区域B1:B3，输入公式“=TREND(A1:A12,,{13;14;15})〞（数组公式输入），即得来年的1、2、3月份的销售量分别为710、743和777。这个公式默认{1;2;3;4;5;6;7;8;9;10;11;12}作为known_x's的参数，故数组{13;14;15}就对应其后的3个月份。4.3.4

GROWTH函数GROWTH函数的功能是返回给定的数据预测的指数增长值。根据的x值和y值，函数GROWTH返回一组新的x值对应的y值。可以使用GROWTH工作表函数来拟合满足给定x值和y值的指数曲线。GROWTH函数的公式为=GROWTH(known_y's,known_x's,new_x's,const)式中，各参数的含义同TREND函数。但需注意的是，如果known_y's中的任何数为零或为负，函数GROWTH将返回错误值#NUM!。【例4-4】以例4-3的资料为例，利用GROWTH函数预测来年的1、2、3月的销售量。预测步骤为：选中单元格区域B1:B3，输入公式“=GROWTH(A1:A12,,{13;14;15})〞（数组公式输入），即得来年的1、2、3月份的销售量分别为756、811和870。这个公式同样默认{1;2;3;4;5;6;7;8;9;10;11;12}作为known_x's的参数，故数组{13;14;15}就对应后面的3个月份。4.3.5

FORECAST函数FORECAST函数的功能是根据给定的数据计算或预测未来值。此预测值为基于一系列的x值推导出的y值。以数组或数据区域的形式给定x值和y值后，返回基于x的线性回归预测值。FORECAST函数的计算公式为a+bx式中，；。FORECAST函数的公式为=FORECAST(x,known_y's,known_x's)式中x—需要进行预测的数据点。需要说明的是：如果x为非数值型，函数FORECAST返回错误值#VALUE!。如果known_y's和known_x's为空或含有不同数目的数据点，函数FORECAST返回错误值#N/A。如果known_x's的方差为零，函数FORECAST返回错误值#DIV/0!。例如：FORECAST(30,{6,7,9,15,21},{20,28,31,38,40})=10.60725。4.3.6

SLOPE函数SLOPE函数的功能是返回根据known_y's和known_x's中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。SLOPE函数的公式为=SLOPE(known_y's,known_x's)说明：参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或引用参数里包含文本、逻辑值或空白单元格，这些值将被忽略。但包含零值的单元格将计算在内。如果known_y's和known_x's为空或其数据点数目不同，函数SLOPE返回错误值#N/A。例如：SLOPE({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})=0.305556。4.3.7

INTERCEPT函数INTERCEPT函数的功能是利用的x值与y值计算直线与y轴的截距。截距为穿过known_x's和known_y's数据点的线性回归线与y轴的交点。公式为=INTERCEPT(known_y's,known_x's)例如：INTERCEPT({2,3,9,1,8},{6,5,11,7,5})=0.0483871。第4章Excel在财务预测中的应用

4.4利用数据分析工具解决预测问题(1)

除了利用前面介绍的几个预测函数进行回归预测分析外，我们还可以使用Excel的数据分析工具库提供的统计观测分析工具来解决回归预测问题。Excel的数据分析工具库提供了3种统计观测分析工具，它们是移动平均法、指数平滑法和回归分析法。下面结合实例来说明这3种方法的具体应用。4.4.1

移动平均法【例4-5】某企业2000年12个月的销售额如图4-4所示，分别按3期、5期和7期移动平均所做的预测分析如图4-4中的C4︰E13区域所示。以3期移动平均为例为例，具体计算步骤如下：图4-4

一次移动平均法实例（1）从【工具】菜单中选中【数据分析】命令，则弹出【数据分析】对话框，如图4-5所示。图4-5

【数据分析】对话框（2）在【数据分析】对话框中的【分析工具】框中选中【移动平均】选项，则弹出【移动平均】对话框，如图4-6所示。图4-6

【移动平均】对话框（3）在【移动平均】对话框中，【输入区域】框中输入“$B$2︰$B$13〞，【间隔】框中输入“3〞，【输出区域】框中输入“$C$2〞，最后选中【图表输出】选项；（4）单击【确定】按钮，则运算结果就显示在单元格区域C4:C13中，如图4-4所示（图中的第13行预测数据即为下月即第13月的预测值），并自动出现输出图表，如图4-7所示。图4-7移动期数为3时的输出图表用同样的方法，可以分析当移动期数为5和7时的分析结果，如图4-4所示。4.4.2

指数平滑法【例4-6】某企业的有关销售数据如图4-8所示，利用指数平滑法进行预测分析，其步骤如下：图4-8指数平滑法实例（1）从【工具】菜单中选中【数据分析】命令，则弹出【数据分析】对话框，在【数据分析】对话框中的【分析工具】框中选中【指数平滑】选项，则弹出【指数平滑】对话框，如图4-9所示。图4-9

【指数平滑】对话框（2）在【指数平滑】对话框中，【输入区域】框中输入“$B$2:$B$13〞，【阻尼系数】框中输入“0.2〞，【输出区域】框中输入“$C$3〞，最后选中【图表输出】选项。（3）单击【确定】按钮，则运算结果就显示在单元格区域C3:C13中（图中的第13行预测数据即为下月即第13月的预测值），如图4-8所示，并自动出现输出图表，如图4-10所示。图4-10

指数平滑法预测输出图（阻尼系数0.2）用同样的方法，可以分析当阻尼系数为0.4和0.6时的分析结果如图4-8所示。需要注意的是，【数据分析】中的指数平滑法所使用的阻尼系数并不是4.2.1.2节介绍的指数平滑法预测方程中的平滑系数，二者的关系为：阻尼系数=1－。4.4.3

回归法利用Excel的回归工具进行预测分析有两种方法：一是图表法；二是回归分析法。4.4.3.1

图表法图表法仅能解决一元线性或非线性回归问题，不能解决多元回归问题。【例4-7】某企业连续9年的产品销售收入Y（万元）与广告支出X1（万元）和居民平均收入X2（元）的有关数据如图4-11所示，则利用图表法进行回归分析，其方法和步骤如下，这里仅以销售收入Y（万元）与广告支出X1（万元）的一元线性关系为例：图4-11

某企业的有关销售数据（1）选择单元格区域B2:C10。（2）单击工具栏上的【图表导向】按钮，在【图表导向—4步骤之1—图表类型】中选“XY散点图〞，其【子图表类型】选第1种，如图4-12所示。图4-12

准备作散点图（3）单击【下一步】按钮，出现【图表导向—4步骤之2—图表源数据】对话框，单击【系列】，在【名称】栏中填入“销售收入〞，在【X值】栏中输入“=Sheet1!$C$2:$C$10〞，在【Y值】栏中输入“=Sheet1!$B$2:$B$10〞（用鼠标拾取单元格区域），如图4-13所示。图4-13

填入源数据（4）单击【下一步】按钮，出现【图表导向—4步骤之3—图表选项】对话框，填入各标题文字，如图4-14所示。图4-14

填入各标题文字（5）单击【下一步】按钮，出现【图表导向—4步骤之4—图表位置】对话框，不作任何输入，单击【完成】按钮，则在工作表上看到输出的图形，对其进行必要的调整（如坐标、字体、位置等）。（6）在系列【数据点】上的任一点上，按鼠标左键，使各数据点出现记号，再单击【工具栏】上的【图表】按钮，选中【添加趋势线】项，或在数据点上按鼠标右键，选【添加趋势线】项，出现【添加趋势线】对话框，如图4-15所示。图4-15

【添加趋势线】对话框（7）在【添加趋势线】中的【类型】对话框中，有【线性】、【对数】、【多项式】、【乘幂】、【指数】和【移动平均】6个选项。通过观察XY散点图可知，产品销售收入与广告支出之间呈明显的线性关系，故这里选【线性】。（8）在【添加趋势线】中的【选项】对话框中，勾选【显示公式】、【显示R平方值】，如图4-16所示。图4-16

【添加趋势线】的【选项】设置（9）单击【确定】按钮，则在图形上显示出较粗的预测线、回归方程和R平方值，然后进行必要的调整，得到如图4-17的结果。图4-17输出图形用同样的方法还可以确定销售收入与居民平均收入的关系。4.4.3.2

回归分析法回归分析法可以对一元线性或多元线性以及某些可以转化为线性的非线性问题进行回归分析。1．线性回归【例4-8】仍以例4-7的有关资料为例，回归分析的步骤如下：（1）从【工具】菜单中选中【数据分析】命令，则弹出【数据分析】对话框，在【数据分析】对话框中的【分析工具】框中选中【回归】选项，如图4-18所示，则弹出【回归】对话框。图4-18

【数据分析】对话框（2）在【回归】对话框中，【Y值输入区域】中输入“$B$1:$B$10〞，【X值输入区域】中输入“$C$1:$D$10〞，在【输出选项】中勾选【输出区域】，填入“$A$12〞，然后根据实际需要，勾选其他需要的选项，如图4-19所示。图4-19【回归】选项（3）单击【确定】按钮，回归分析的摘要就输出在本工作表上，如图4-20所示。对这些数据进行分析可知：R平方值为0.9903，说明因变量与自变量之间相关性很高；F的显著值为2.961E-07，已达0.05的检验标准；其他统计检验也到达相应的标准。从而得到回归方程为：Y=229.8409+9.2794X1+0.0082X2。图4-20

回归分析的计算机输出当自变量只有一个（即一元回归）时，上述方法同样适用。2．非线性回归对于某些可以化为线性关系的非线性问题，同样可以进行回归分析。举例如下。【例4-9】某地区科研系统近10年的净收入Y（千万元）与研究经费X1（千万元）和研究人员数X2（万人）的统计资料，如图4-21所示，假设它们之间存在着以下的函数关系：式中，a、b、c为待估计参数。若利用回归工具求解此类非线性问题，解决的方法是将此方程进行数学变换，即对方程两边取对数，得，将各个观测值进行变换，如图4-21所示，即在单元格E2:E11中输入公式“=LN(B2:B11)〞（数组公式输入），然后将单元格E2:E11复制到单元格F2:F11和G2:G11中。图4-21某地区科研系统有关资料再对变换后的数据利用Excel的回归工具进行回归分析，具体步骤可参阅【例4-7】，其中【Y值输入区域】中输入“$E$1:$E$11〞，在【X值输入区域】中输入“$F$1:$G$11〞，在【输出选项】中勾选【输出区域】，填入“$A$12〞，得到如图4-22所示的分析结果，最后得到：a=e0.08214=0.9211，b=0.4477，c=0.6046，相关系数为0.9808（注意此相关系数是变换后的线性方程的相关系数，并不是原非线性方程的相关系数），回归方程为：。图4-22

回归分析结果第4章Excel在财务预测中的应用

4.5利用规划求解工具解决预测问题

虽然我们可以利用Excel提供的各种预测分析工具解决大多数财务预测中的实际问题，但这些预测分析工具并不是万能的，其预测误差也随着实际问题的复杂化而增大。比方对于一些非线性预测问题，常常是将其通过变量替换而转换为线性问题。但是，这种变换过程一方面增加了计算工作量，另一方面也可能导致分析精度下降，因为变换后的数据容易使观测数据的性质发生变化，导致自变量与因变量之间的关系发生扭曲，从而影响回归方程的精度，因此，这种将非线性转换为线性的做法是存在一定的缺陷的。此外，有些非线性问题根本无法直接转换为线性问题，除非作出大量的简化，这必然使得到的回归方程严重失真。因此，对于非线性回归问题，最好的方法是直接进行回归分析，即求解使残差平方和最小、或使相关系数最大的回归方程，但非线性回归过程是一个循环寻优过程，需要先设置回归方程系数的初值，然后计算观测值与预测值的残差平方和，不断寻找使残差平方和最小的回归方程系数，这实际上是一个优化问题，因此，可以利用Excel的规划求解工具求解非线性回归问题，当然也可以用来求解线性回归问题。在利用规划求解工具直接求解非线性回归问题时，需要使用以下几个计算公式：自由度df为式中，n为观测次数；m为待估计参数个数。残差平方和SE为式中，Yi、分别为第i个观测值和预测值（i=1,2,…,n）。剩余标准差SS为相关系数R2为式中，为观测值的平均值。下面结合实例说明在Excel上进行非线性回归的具体方法和步骤。【例4-10】以例4-9的有关资料为例，利用Excel的规划求解工具来求解非线性回归问题的方法和步骤如下：（1）如图4-23所示，单元格G2:G4为变动单元格，分别存放待估计参数a、b、c，其初值可设为0。（2）在单元格E2:E11中输入预测值公式“=G2*(C2:C11)^G3*(D2:D11)^G4〞（数组公式输入）。图4-23利用规划求解工具进行非线性回归分析（3）在单元格G5中输入观测值的平均值公式“=AVERAGE(B2:B11)〞；在单元格G6中输入自由度公式“=COUNT(B2:B11)-COUNT(G2:G4)〞；在单元格G7中输入残差平方和公式“=SUM((B2:B11-E2:E11)^2)〞（数组公式输入）；在单元格G8中输入剩余标准差公式“=SQRT(G7/G6)〞；在单元格G9中输入相关系数R2的计算公式“=1-G7/SUM((B2:B11-G5)^2)〞（数组公式输入）。（4）单击EXCEL工具菜单，选择【规划求解】项，出现【规划求解参数】对话框；（5）在【规划求解参数】对话框中，【设置目标单元格】设置为单元格“$G$7〞，即目标函数为残差平方和；【等于】设置为“最小〞；【可变单元格】设置为“$G$2:$G$4〞。然后单击【求解】，即可得到回归方程的系数a、b、c，出现“规划求解结果〞对话框，然后单出【确定】按钮，保存规划求解结果。需要注意的是，若系数a、b、c的初值设置不适宜的话，则一次求解过程（即在Excel上进行【工具】【规划求解】【求解】【确定】这样一个求解过程）可能得不到最优结果（或得不到解），这时需要进行屡次求解，即在第一次求解结果的基础上，再进行第二次求解，得到第二次求解结果，然后在第二次求解结果的基础上，再进行第三次求解，得到第三次求解结果，如此继续下去，直到求出的系数a、b、c的值不再变化、且残差平方和最小为止，即得到最优结果。在上例中，当a、b、c的初始值设为0时，经过2次求解过程即得到最优结果，如图4-23所示。若采用非线性转换为线性的方法，如例4-9所示，可得到有关系数如图4-22所示，将此系数代入回归方程，计算不同X1和X2下的预测值，进而计算出残差平方和与剩余标准差分别为157.1139和4.7376，与图4-23的直接非线性回归的结果（残差平方和154.03、剩余标准差4.6909）进行比较，可见非线性转换为线性的方法得出的结果误差要大于直接进行非线性回归的误差。第4章Excel在财务预测中的应用

4.6销售预测

销售预测的准确程度，对企业的兴衰成败会产生很重要的影响。销售预测比较准确，会使企业在有方案的财务安排下顺利运作；而如果销售预测与实际情况偏离很远，则会使企业遇到麻烦，甚至陷入困境。因此，销售预测是企业进行财务预测的首要工作，是企业制定财务方案的基础。

销售预测主要应根据市场需求的变化，结合企业的利润目标、实现企业市场份额的目标，并综合考虑企业内外部的各种限制条件的影响来进行。一般情况下，可首先分别对未来各期的销售量和销售价格进行预测，在此基础上，根据预测的产品销售价格乘以预测的销售量得到预测的销售额；也可以直接根据销售额的有关历史资料，采用适当的方法进行预测。4.6.1

销售预测的根本方法销售预测是一项比较复杂的工作，需要考虑的因素很多，作出准确的预测是非常困难的。通常可利用企业过去的数据进行统计分析，并结合经济环境对未来市场的影响以及企业内外部各种条件的限制，作出销售预测。进行销售预测的方法很多，常用的方法包括以下几种。1．时间序列预测法时间序列预测法，是指将观察或记录的一些历史数据，按时间的先后排列成数据系列，进行统计分析，找出过去长期的销售量或销售额的增减变化趋势，再根据此变化趋势分析的结果，预测未来时期的销售量或销售额。常见的时间序列的预测方法有简单平均法、移动平均法、指数平滑法、或以时间为自变量的回归分析法等，这些方法的根本原理可参阅前面的有关内容。2．因果关系预测法因果关系预测法，是指利用有关因素与产品销售量或销售额之间的固有因果关系，通过建立一定的数学模型来预测企业未来的产品销售水平的一种方法。企业产品销售水平的上下，往往受到诸多宏观或微观、外部或内部、客观或主观等因素的影响，通常可以通过回归分析的方法检验出哪些因素与销售水平之间具有因果关系，在此基础上可建立回归方程，进行销售预测。有关如何建立回归方程及进行相关检验的方法可参阅前面的有关内容。3．通过生产能力或订货合同进行销售量（销售额）预测企业生产的产品如果在市场占有稳定的份额或供不应求，则可按本企业的生产能力预测产品的销售量，计算公式如下：方案期销售量=方案期初库存量+方案期预计生产量-方案期末预计库存量4.6.2

销售预测模型及其应用在很多情况下，通过建立企业的销售预测模型，可以很方便地实现销售预测。下面介绍两个销售预测模型。4.6.2.1

一元线性（非线性）回归预测模型【例4-11】根据图4-24中所给的资料建立一元线性（非线性）回归预测模型。图4-24

一元线性（非线性）回归销售预测模型下面利用线性回归中的LINEST函数和指数回归中的LOGEST函数，来建立一元线性（非线性）回归预测模型。（1）首先建立销售预测模型，如图4-24所示，这里以过去12期的销售量为历史数据（可以是以年计算，也可以是以月计算，图4-24为以年计算）。（2）设置回归模型选择控件，控件的数据源区域为$A$7:$A$8，单元格链接为$B$7，下拉显示项数为2。（3）选取单元格区域B3:M3，单击【插入】【名称】【定义】命令，或直接单击编辑栏中的名称框，将影响因素所在的单元格区域B3:M3定义为“影响因素序列〞；用同样的方法，将销售量所在的单元格区域B4:M4定义为“销售序列〞。（4）在单元格E8中输入公式“=IF(B7=1,INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,2),INDEX(LOGEST(销售序列,影响因素序列,TRUE,TRUE),1,2))〞，计算系数A。（5）在单元格F8中输入公式“=IF(B7=1,INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,1),INDEX(LOGEST(销售序列,影响因素序列,TRUE,TRUE),1,1))〞，计算系数B。（6）在单元格G8中输入公式“=IF(B7=1,INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),3,1),INDEX(LOGEST(销售序列,影响因素序列,TRUE,TRUE),3,1))〞，计算相关系数R2。（7）在单元格J8:M8中输入公式“=IF(B7=1,E8+F8*J7:M7,E8*F8^J7:M7)〞（数组公式输入），计算未来第1～4期的预测值。在影响因素和销售量两列输入历史数据，并在J7:M7中输入未来4期的影响因素预测数值后，即可得到回归预测模型及未来的预测值。通过选择不同的回归模型，可以分别计算一元线性模型和一元指数模型下的回归结果及预测值。由计算结果可知，采用指数模型（相关系数为0.9742）要比线性模型（相关系数为0.9452）更为准确。4.6.2.2

多元线性回归预测模型【例4-12】根据图4-25中所给的资料建立多元线性回归预测模型。图4-25

多元线性回归销售预测模型当影响销售量（额）的因素不止一个时，就需要建立多元线性回归模型。下面就二元线性回归预测模型的建立进行说明，对于影响因素在两个以上的情况，可参照本模型建立。（1）首先建立销售预测模型，如图4-25所示，这里以过去12期的历史数据为依据（可以是按年计算，也可以是按月计算，图4-25为按月计算）。（2）选取单元格区域B3:M4，单击【插入】【名称】【定义】命令，或直接单击编辑栏中的名称框，将影响因素所在的单元格区域B3:M4定义为“影响因素序列〞；用同样的方法，将销售额所在的单元格区域B5:M5定义为“销售序列〞。（3）在单元格D9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,3)〞，计算系数A。（4）在单元格E9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,2)〞，计算系数B。（5）在单元格F9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,1)〞，计算系数C。（6）在单元格G9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),3,1)〞，计算相关系数R2。（7）在单元格J9:M9中输入公式“=D9+E9*J7:M7+F9*J8:M8〞（数组公式输入），计算未来第1～4期的预测值。在影响因素和销售额各列输入历史数据，并在J7:M8中输入未来4期的影响因素预测数值后，即可得到回归预测模型及未来的预测值，如图4-25所示第4章Excel在财务预测中的应用

4.7本钱预测

4.7.1

本钱预测的方法本钱是指企业为生产和销售产品所花费的全部费用。本钱可以按很多不同的标准进行分类，如常见的按经济职能分类和按本钱性态分类：本钱按经济职能划分可分为生产本钱和非生产本钱两大类。生产本钱又称制造本钱，包括生产过程中发生的直接材料、直接人工和制造费用三个工程；非生产本钱又称非制造本钱，包括为销售产品所花费的销售费用和为组织企业的生产所花费的管理费用。本钱性态又称本钱习性，是指本钱总额对业务量（如产销量）总数的依存关系。按本钱性态可将全部本钱分为变动本钱、固定本钱和混合本钱三类。变动本钱是指总额与业务量总数成正比例变动关系的本钱，如直接材料、直接人工、变动性制造费用等。固定本钱是指在一定的业务量范围内，总额不受业务量增减变动影响的本钱，如按直线法计提的固定资产折旧费、管理人员工资等。混合本钱是指总额随业务量总数发生变化、但不成正比例变动的本钱，通过采用适当的方法可以将混合本钱分解为变动本钱和固定本钱两大类。本钱预测是根据企业未来的开展目标和现实条件，参考其他资料，利用专门的方法对企业未来本钱水平及其变动趋势进行估算和预测。本钱预测可为本钱决策和实施本钱控制提供有用的信息。本钱预测的方法主要有：（1）历史本钱法。这种方法主要是根据本钱的历史资料来预测未来的本钱水平，常用的方法有上下点法和回归分析法。（2）目标利润推算法。这种方法主要是根据有关的经济预测（销售量、销售价格）和企业的目标利润等数据，来反算本钱的一种方法。（3）因素分析法。这种方法是根据预期影响本钱的各种因素的变化来预测未来本钱，它需要企业拥有较为详细的资料。（4）比例推算法。这种方法是利用企业的生产消耗与企业有关重大经营成果指标之间的依存关系，按被确认的报告年度本钱与这些指标的比例关系推算预测期的本钱水平。这种方法只是一种大概的推算方法。上述的本钱预测方法中，回归分析法较为科学也比较常用。下面主要介绍在Excel上利用回归分析法进行本钱预测的具体方法。4.7.2

本钱预测模型利用回归分析法进行本钱预测首先需要建立回归分析模型。回归分析模型就是将总本钱分解成与销售量无关的固定本钱和与销售量有关的变动本钱，从而根据未来的预计销售量对本钱进行预测。模型的数学表达式如下：一元一次模型（线性模型）：

销售本钱=固定本钱+单位变动本钱×销售量

一元二次模型（非线性模型）：销售本钱=固定本钱+单位变动本钱×销售量+混合本钱×销售量平方【例4-13】建立一元一次（二次）本钱预测模型。在Excel上建立销售本钱的回归分析模型方法可参阅前面介绍的回归分析方法，具体步骤如下：（1）首先建立本钱预测模型，如图4-26所示，这里假设已有过去12期的数据为历史数据；（2）建立线性或非线性模型选择控件，控件的数据源区域为$A$8:$A$9，单元格链接为$B$8，下拉显示项数为2。（3）在单元各B4:M4中输入销售量平方计算公式“=B3:M3^2〞（数组公式输入），以准备进行一元二次回归分析。图4-26销售本钱预测模型（4）在单元格E9中输入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),1,2),INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),1,3))〞，计算系数A。（5）在单元格F9中输入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),1,1),INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),1,2))〞，计算系数B。（6）在单元格G9中输入公式：“=IF(B8=1,0,INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),1,1))〞，计算系数C。（7）在单元格H9中输入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),3,1),INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),3,1))〞，计算相关系数R2。（8）在单元格K9:M9中输入公式：“=E9+F9*K8:M8+G9*K8:M8^2〞（数组公式输入），计算未来第1～3期的预测值。这样企业的一元线性及一元非线性本钱预测模型就建立起来了。在实际预测中，可以先对销售量及销售本钱的历史数据作出散点图，根据其趋势来选择一元线性或一元非线性预测模型。第4章Excel在财务预测中的应用

4.8利润预测(1)

利润预测是指在销售预测的基础上，根据企业未来开展目标和其他相关资料，预计、推测、或估算未来应该到达和希望实现的利润水平及其变动趋势的过程。一般可在对本钱、业务量（产销量）、利润之间的关系进行研究的基础上作出利润预测，这种预测方法称为本量利分析法。本节主要介绍单一品种情况下确定性本量利分析和不确定性本量利分析的具体方法和步骤，并采用规划求解方法对约束条件下的多品种本量利关系进行分析。4.8.1

确定性条件下单品种利润敏感性分析模型敏感性分析是一种定量分析方法，它研究当制约利润的有关因素发生某种变化时对利润所产生的影响，这对于利润预测分析，特别是对目标利润预测有着十分积极的指导意义。在企业只生产单品种产品，且产销平衡的条件下，本量利之间的根本关系为利润=销售量×（单价－单位变动本钱）－固定本钱在进行利润敏感性分析时，假定条件如下：利润只受销售量、产品单价、单位变动本钱和固定本钱的影响；上述各因素的变动均不会影响其他因素的变动。【例4-14】根据图4-27所给的资料建立确定性条件下单品种利润敏感性分析模型。下面就建立利润敏感性分析模型的具体步骤进行说明。（1）设计利润分析模型结构，如图4-27所示，这里假设只有一种产品。（2）首先设计销售量的【滚动条】控件。单击【视图】，选择【工具栏】，再单击【窗体】，出现【窗体】工具栏，单击【滚动条】按钮，然后在工作表的适宜位置（这里为E4～F4单元格）拖曳出一个矩形【组合框】控件，并调整其大小。图4-27

利润敏感性分析模型（3）将鼠标移到新建立的【滚动条】控件上，单击鼠标右键，出现快捷菜单，选择【设置控件格式】，出现【设置控件格式】对话框，选择【控制】项，在【当前解】栏中输入50，【最小值】栏中输入0，【最大值】栏中输入100，【步长】栏中输入1，【页步长】栏中输入5，在【单元格链接】栏中填入“E4〞，然后单击【确定】按钮，这就建立了销售量的【滚动条】控件。这里假设了利润的各个影响因素的变动百分比范围为50%。（4）其他工程的【滚动条】控件可按照上述方法进行设置。其中，单价、单位变动本钱和固定本钱【滚动条】的【单元格链接】分别为单元格E5、E6、和E7。（5）在单元格D4:D7中建立变动百分比与【滚动条】控件的联系，即选取单元格D4:D7并输入公式“=E4:E7/100-50%〞（数组公式输入）。说明：本例中各因素的变动范围为-50%～+50%，而滚动条控制按钮的值的变化范围为0～100，为了使滚动条控制按钮的变化表示为百分数的变化，这里将控制按钮的值除以100后再减去50%，则每次单击滚动条两端的箭头，单元格D4:D7中的变动百分比就变化1%，而当滚动条在中间位置，百分比恰好为零；当单击滚动框与滚动条两端之间时，每单击一次，变动百分比就增（或减）5%。（6）选取单元格C4:C7并输入变化后各工程数值的计算公式“=B4:B7*(1+D4:D7)〞（数组公式输入）。（7）在单元格A10中输入预计利润计算公式：“=B4*(B5-B6)-B7〞，并复制到单元格B10中，在单元格C10中输入公式“=B10-A10〞，在单元格D10输入公式“=C10/A10〞。这样，就得到了多因素变动对利润的综合影响。（8）设计单因素变动影响分析表格，如图4-27所示，在单元格B13:B16中输入公式“=A10〞（数组公式输入），在单元格C13:C16中输入公式“=D4:D7〞，在单元格D13～D16中分别输入销售量、产品单价、单位变动本钱和固定本钱单独变动时对利润的影响计算公式，分别为：单元格C13：“=C4*(B5-B6)-B7〞，单元格C14：“=B4*(C5-B6)-B7〞，单元格C15：“=B4*(B5-C6)-B7〞，单元格C16：“=B4*(B5-B6)-C7〞；在单元格E13:E16中输入公式“=D13:D16-B13:B16〞（数组公式输入）；在单元格F13:F16中输入公式“=E13:E16/B13:B16〞（数组公式输入）。这样利润敏感性分析模型就建立起来了。单击各个影响因素滚动条的箭头，改变其变动幅度，就可以很方便地了解各个因素变动对利润的单独影响程度以及综合影响程度。图4-27为各个影响因素的变动率及其对利润影响结果的一个例子，由图可见，当销售量单独增加10%时，利润增加12%；当产品单价单独增加10%时，利润增加40%；当单位变动本钱减少10%时，利润增加28%；当固定本钱单独减少10%时，利润增加2%。因此，产品单价对利润的影响最大，敏感性最强，其次是单位变动本钱和销售量，而固定本钱对利润的影响最小。当上述4个因素同时朝着使利润增加的方向变动10%时（见图4-27），则利润的增加幅度为88.80%。4.8.2

确定性条件下多品种本量利分析模型在多品种的情况下，假定各产品的品种结构一定，则可以利用加权平均法进行本量利分析，具体可参阅第8章的有关内容。传统的本量利分析方法存在着以下明显的缺陷：一是产销平衡的假设，实际上，除非企业接到一批确定的订单，并按订单核算企业是否有利润可赚，按订单安排生产，否则产销不平衡是经常存在的，产品结构也将发生变化；二是以各产品预计销售量来确定产品结构，进而采用有关方法来计算各产品的保本额和保本量，但是，预计销售量是企业根据市场来预测的，它具有很大的不确定性，预测的销售量也常常是一个区间，而不是一个固定数，故以此来确定产品结构不尽合理；三是没有考虑企业自身条件的限制，因为企业的产品结构受企业本身各种限制条件（如企业的现有生产技术工艺、设备生产能力、企业资源、销售渠道等）的影响，而且这些条件也是时常变化的，无论是为了实现保本平衡还是为了实现目标利润，企业在同时生产销售多种产品时的产品结构都必须依据实际情况进行相应的调整，不根据企业的具体情况及市场情况计算的保本点是没有实际意义的。因此，对多品种盈亏平衡分析的正确方法是考虑各种约束条件，采用规划方法来解决企业的保本平衡、保利平衡、最优经营决策等问题。根据不同的分析目的，可以建立以下几种多品种本量利分析模型。1．利润最大化模型假设企业的产品销售不受市场的限制，仅受企业自身条件的限制，即企业可以根据自身的条件随意安排产品生产，则可建立如下的利润最大化模型。目标函数：≤

约束条件：

（j=1,2,…,m）0≤Qi≤Qi，max

（i=1,2,…,n）式中—为企业的利润；pi、vi、Qi（i=1,2,…,n）—产品i的单价、单位变动本钱、销售量；n—为产品种类；ai,j—为每单位第i种产品消耗的第j种资源的数量；bj—为第j种资源的可用总量；m—为资源种类；Qi,max—第i种产品的最大生产能力。在本模型中，假设了各产品之间相互独立。在实际中，某些产品可能存在相互依赖的关系或互斥关系等某些特殊的关系，则在约束条件中需要参加这些条件。2．保利模型设企业的目标利润为，则企业为实现此利润的产品结构（各产品产销量）模型为

≤

（j=1,2,…,m）

0≤Qi≤Qi，max

（i=1,2,…,n）式中，各参数含义同上，其中Qi,max为第i种产品的最大产销量。3．保本模型当=0时，上述的保利模型即变为如下的保本模型：

≤

（j=1,2,…,m）

0≤Qi≤Qi，max

（i=1,2,…,n）式中，Qi,max可为第i种产品的最大销售量或生产量。4．本钱控制模型由于企业的许多产品是有同类产品比较的，故它们的价格不能高于同类产品的价格。在给定产品销售量及企业固定本钱的情况下，则可建立企业实现目标利润的各产品的变动本钱控制模型为目标函数：

约束条件：≤

（j=1,2,…,m）

pi≤pi*

（i=1,2,…,n）式中，V为变动本钱总额；pi*为第i种产品的最高售价。其他符号意义同上。在给定各产品的最高单价的情况下，利用上述模型可以确定各产品的最高单位变动本钱。同样，一般情况下根据上述模型得出的各产品的单位变动本钱组合有无数个，但在给定某些产品的最高单位变动本钱后，其他产品的单位变动本钱就可以求出。这样，根据实际不同的需要，分别利用Excel的规划求解工具求解上述模型，即可进行多品种本量利分析。下面举例说明多品种本量利分析的方法。【例4-15】某企业同时生产销售A、B、C三种产品，产品有关资料如图4-28所示。根据条件，可建立如下的利润最大化模型：图4-28利润最大化的本量利分析目标函数：max{}=(14-10)Q1+(20-16)Q2+(45-30)Q3﹣300000约束条件：5Q1+4Q2+4Q3≤4000003Q1+4Q2+5Q3≤3500004Q1+3Q2+4Q3≤3000000≤Q1≤400000≤Q2≤350000≤Q3≤35000式中，Q1、Q2、Q3分别为产品A、B、C的产销量，且为整数。如图4-28所示，求解上述模型的步骤如下：（1）单元格B14:D14作为可变单元格，存放产品A、B、C的产销量。（2）单元格B15为目标函数单元格，存放最大利润，计算公式为“=SUMPRODUCT(B14:D14,B3:D3-B4:D4)-B12〞。（3）在单元格E6中输入公式“=SUMPRODUCT($B$14:$D$14,B6:D6)〞，然后将其复制到单元格E7和E8，分别计算三种产品在甲、乙和丙车间的使用工时合计。（4）单击【工具】菜单中【规划求解】项，在【规划求解参数】对话框中，【设置目标单元格】选“$B$15〞；【等于】选“最大值〞；【可变单元格】选“$B$14:$D$14〞；【约束】中添加以下约束条件：“$E$6<=$B$9〞、“$E$7<=$B$10〞、“$E$8<=$B$11〞、“$B$14<=$B$5〞、“$C$14<=$C$5〞、“$D$14<=$D$5〞、“$B$14:$D$14>=0〞、“$B$14:$D$14=整数〞。（5）单击【求解】按钮，即得在满足上述约束条件下企业实现最大利润的最正确产品结构为：产品A：16429件，产品B：31429件，产品C：35000件，最大利润416428元，如图4-28所示。【例4-16】在例4-15中，假设企业的目标利润为35万元，且产品A、B、C的最大产销量均为30000件，如图4-29所示，而且要求实现此目标利润消耗最小总工时，则可得如下的目标利润优化模型：目标函数：min{总工时}=(5+3+4)Q1+(4+4+3)Q2+(4+5+4)Q3约束条件：(14-10)Q1+(20-16)Q2+(45-30)Q3-300000=3500005Q1+4Q2+4Q3≤4000003Q1+4Q2+5Q3≤3500004Q1+3Q2+4Q3≤3000000≤Q1≤300000≤Q2≤300000≤Q3≤30000Q1、Q2、Q3为整数单元格E6、E7和E8中的计算公式参见例4-29，而单元格B16中的计算公式为“=SUMPRODUCT(B3:D3-B4:D4,B15:D15)-B12〞，单元格B17中的公式为“=SUM(E6:E8)〞。图4-29

目标利润且总工时最少的优化模型在【规划求解参数】对话框中，【设置目标单元格】选“$B$17〞；【等于】选“最小值〞；【可变单元格】选“$B$15:$D$15〞；【约束】中添加以下约束条件：“$E$6<=$B$9〞、“$E$7<=$B$10〞、“$E$8<=$B$11〞、“$B$15<=$B$5〞、“$C$15<=$C$5〞、“$D$15<=$D$5〞、“$B$15:$D$15>=0〞、“$B$15:$D$15=整数〞、“$B$16=$B$13〞；然后求解，即得最正确销售量组合为：产品A：20000件，产品B：30000件，产品C：30000件，耗用总工时960000小时【例4-17】某企业同时生产A、B两种产品，有关资料如图4-30所示。假设产品A、B的预计销售量分别为9000件和7000件（它们必须满足工时限制条件），最高单价分别为22元/件和15元/件。当目标利润为6万元时，企业应如何控制变动本钱，才能实现6万元的目标利润？图4-30

实现目标利润的本钱控制优化模型由条件，可得如下的本钱控制优化模型：目标函数：max{变动本钱总额}=9000v1+7000v2约束条件：9000×(22-v1)+7000×(15-v2)-90000=600009000×6+7000×8≤1200009000×4+7000×9≤100000在单元格D4中输入公式“=SUMPRODUCT(B7:C7,B4:C4)〞，在单元格B10中输入公式“=SUMPRODUCT(B7:C7,B2:C2-B11:C11)-B8〞，在单元格B12中输入公式“=SUMPRODUCT(B7:C7,B11:C11)〞。然后在【规划求解参数】对话框中，【设置目标单元格】选“$B$12〞；【等于】选“最大值〞；【可变单元格】选“$B$11:$C$11〞；【约束】中添加以下约束条件：“$B$10=$B$9〞、“$D$3<=$B$5〞、“$D$4<=$B$6〞；然后求解，即得到企业为实现目标利润的最大变动本钱总额为153000元，从而产品A、B的最高单位变动本钱必须满足不等式：9000v1+7000v2≤153000，这样，只要给出任何一种产品的单位变动本钱，即可计算出另一种产品的最高单位变动本钱，例如，当产品A的单位变动本钱为10元/件时，则产品B的最高单位变动本钱为9元/件，也就是说，为实现6万元的目标利润，产品A、B的单位变动本钱分别不得超过10元和9元。图4-30中的单位变动本钱为某次优化计算的结果，它说明当产品A的单位变动本钱为10.59元/件时，产品B的单位变动本钱不能高于8.24元/件。因为取得最低变动本钱总额的产品A、B的单位变动本钱组合有无数个，当给予变动单元格B11:C11不同的初始值时，会得到不同的组合解，但目标函数（变动本钱总额）的结果是相同的。4.8.3

最优生产决策模型在企业得到一批订单后，应根据自身的具体条件来合理安排生产，以实现最大利润。这类最优生产决策问题，可以利用规划求解工具来解决。下面举例说明。【例4-18】假定某企业某月接到客户订单如图4-31所示，其中产品A和C来自客户甲，客户甲要求同时接受产品A和C，否则取消订单；产品B和D来自客户乙，客户乙对产品无特殊要求，接受产品B、D均可。那么，根据图4-31中企业的有关条件及各产品的有关资料，可以建立如下的最优生产决策模型：图4-31

最优生产决策模型约束条件：9000×6×xA+6000×8×xB+3000×14×xC+2000×10×xD≤120000

9000×4×xA+6000×9×xB+3000×10×xC+2000×8×xD≤100000

xA–xC=0

xA、xB、xC、xD=0或1式中，xi为0、1变量，当xi为1时表示i产品被选中，当xi为0时表示i产品被拒绝。在单元格F6中输入公式“=SUMPRODUCT(B9:E9,B11:E11,B6:E6)〞，在单元格B12中输入公式“=SUMPRODUCT(B9:E9,B4:E4,B11:E11)-B10〞，在单元格B13中输入公式“=B11-D11〞。在【规划求解参数】对话框中，【设置目标单元格】选“$B$12〞；【等于】选“最大值〞；【可变单元格】选“$B$11:$E$11〞；【约束】中添加以下约束条件：“$B$11:$E$11<=1〞、“$B$11:$E$11>=0〞、“$B$11:$E$11=整数〞、“$F$5<=$B$7〞、“$F$6<=$B$8〞、“$B$13=0〞。然后求解，得到计算结果为：xA=xC=xD=1，xB=0，即全部接受客户甲的订单，只接受客户乙的产品D订单，可实现利润9万元。4.8.4

目标利润分析模型所谓目标利润，是指企业在未来一定时期内，经过努力应该到达的最优化利润控制目标，它是企业经营活动的动力和目标。当企业根据生产经营中的实际情况规划了目标利润后，如何确保其完成显得极为重要。通过建立目标利润分析模型，即可以很方便地解决这类问题。在多品种生产的情况下，由于必须考虑很多约束条件，故可以建立保利模型，将销售量、产品单价、单位变动本钱或固定本钱中的任意一个作为决策变量，利用规划求解工具进行优化计算，本书不再介绍。下面仅以单一品种生产的情况为例，介绍目标利润的分析方法。【例4-19】根据图4-32所给的资料建立单一品种的目标利润分析模型。图4-32

目标利润分析模型在单一品种情况下，可以建立目标利润分析模型，如图4-32所示，在目标数值一列中，各单元格的计算公式为：单元格C3：“=(C8+B6)/(B4-B5)〞；单元格C4：“=(C8+B6)/B3+B5〞；单元格C5：“=B4-(C8+B6)/B3〞；单元格C6：“=B3*(B4-B5)-C8〞。则由图4-32可知，当销售利润由目前的50000元提高到70000元时，可采取的措施有：（1）将销售量由目前的10000件提高到10571件；（2）将产品单价由目前的100元/件提高到102元/件；（3）将单位变动本钱由目前的65元/件降低到63元/件；（4）将固定本钱由目前的300000降低到280000元。通过目标利润分析模型，可以很方便地找出实现目标利润的努力方向。在实际生产经营活动中，企业往往需要采取综合措施来实现目标利润，而不是仅靠一种措施，故需要进行综合分析和平衡。4.8.5

不确定性本量利分析模型在传统的本量利分析中，一般认为销售量、单价、单位变动本钱和固定本钱在一定时期内保持不变，因而是一种无风险确实定性分析。但在实际中，这些参数受很多因素的影响，市场的变化和企业内部条件的变化都可能导致产品的销售量、销售价格、单位变动本钱和固定本钱发生变化，在这种情况下，有必要进行风险型本量利分析。进行风险型本量利分析的常用方法是联合概率分析，即对销售量、单价、单位变动本钱及固定本钱确定其可能发生的数值及发生的概率，然后根据其可能值及发生概率进行计算。当只有一个参数（比方只有销售量）是不确定的，计算过程比较简单，但当多个参数均不确定时，计算就非常烦琐。假设我们估计的销售量、单价、单位变动本钱及固定本钱的可能发生概率次数分别为6次、5次、5次和5次，就需要计算6×5×5×5=750个组合，计算工作量非常大。利用蒙特卡罗模拟方法则可以在Excel上很方便地进行这种分析和计算。下面结合实例予以说明。【例4-20】某企业生产A产品的销售量、单价、单位变动本钱及固定本钱均为非确定性因素，在未来可能到达的水平及有关的概率情况如图4-33所示。则利用蒙特卡罗模拟在Excel上进行风险型本量利分析的方法和步骤如下：图4-33

产品的有关资料（1）首先根据图4-33各参数的累计概率确定随机数范围及对应的随机数，以便于在Excel上进行模拟计算（见图4-33）。（2）在单元格A26、C26、E26和G26中输入随机数公式RANDBETWEEN(0,99)，产生0～99的随机数，如图4-34所示；图4-34

模拟计算过程（3）利用VLOOKUP函数寻找符合随机数的销售量、单价、单位变动本钱和固定本钱，公式分别为：单元格B26：“=VLOOKUP(A26,$D$3:$E$9,2)〞；单；单元格H26：“=VLOOKUP(G26,$I$12:$J$16,2)〞，如图4-34所示。（4）在单元格I26中输入利润计算公式“=B26*(D26-F26)-H26〞；在单元格J26中输入保本量计算公式“=H26/(D26-F26)〞；在单元格K26中输入保利量计算公式“(H26+$B$19)/(D26-F26)〞，如图4-34所示。（5）选中单元格区域A26:K26，拖动单元格区域右下角的填充柄向下一直拖动到第5025行，即进行5000次模拟计算。（6）在单元格N3:R9中存放模拟计算结果，如图4-35所示，各单元格的计算公式分别为。图4-35

模拟计算结果各参数的期望值：单元格N3：“=AVERAGE(I26:I5025)〞；单元格N4：“=AVERAGE(J26:J5025)〞；单元格N5：“=AVERAGE(K26:K5025)〞；单元格N6：“=AVERAGE(B26:B5025)〞；单元格N7：“=AVERAGE(D26:D5025)〞；单元格N8：“=AVERAGE(F26:F5025)〞；单元格N9：“=AVERAGE(H26:H5025)〞。各参数的标准差：单元格O3：“=STDEV(I26:I5025)〞；单元格O4：“=STDEV(J26:J5025)〞；单元格O5：“=STDEV(K26:K5025)〞；单元格O6：“=STDEV(B26:B5025)〞；单元格O7：“=STDEV(D26:D5025)〞。单元格O8：“=STDEV(F26:F5025)〞；单元格O9：“=STDEV(H26:H5025)〞。各参数的最小值：单元格P3：“=MIN(I26:I5025)〞；单元格P4：“=MIN(J26:J5025)〞；单元格P5：“=MIN(K26:K5025)〞；单元格P6：“=MIN(B26:B5025)〞；单元格P7：“=MIN(D26:D5025)〞；单元格P8：“=MIN(F26:F5025)〞；单元格P9：“=MIN(H26:H5025)〞。各参数的最大值：单元格Q3：“=MAX(I26:I5025)〞；单元格Q4：“=MAX(J26:J5025)〞；单元格Q5：“=MAX(K26:K5025)〞；单元格Q6：“=MAX(B26:B5025)〞；单元格Q7：“=MAX(D26:D5025)〞；单元格Q8：“=MAX(F26:F