Excel 在财务规划中的应用.ppt

1、第六章Excel 在财务规划中的应用,第一节 销售预测与预算 第二节 生产预算与规划求解 第三节 本量利动态分析,第一节 销售预测与预算,一、移动平均预测与印章制作 【技能储备】 1.移动平均预测是从 n 期的销售量中选取 m 期（mn/2）的数据，求其算术平均数，并不断向后移动，以最后一组平均数作为未来销售预测值的一种方法。 2. Excel 提供了大量的日期与时间函数，可进行日期或时间的计算。如年份函数 YEAR、月份函数 MONTH、日函数 DAY、当前日期函数 TODAY、当前日期和时间函数 NOW、星期函数 WEEKDAY、小时函数 HOUR、分钟函数 MINUTE、秒钟函数 SEC

2、OND 等。,返回,下一页,第一节 销售预测与预算,3. Excel 提供了绘图功能，绘图工具栏如图 61 所示。 5.网格线的显示与隐藏。Excel工作表中的网格线主要用于屏幕显示，以便进行表格编辑，但它不会被打印出来；若不愿显示，则选择“工具/选项”菜单命令，在“视图”页签中取消其显示。在该界面还可选择是否显示行号列标、水平和垂直滚动条、工作表标签，是否显示编辑栏、状态栏等；若取消这些显示，则编辑工作表时较麻烦。,返回,上一页,下一页,第一节 销售预测与预算,【案例 61】某产品近 8 月的销量如下表 61 所示，要求用 3 期移动平均法、5 期移动平均法预测第 9 月的销售量。 【操作提

3、示】 （1）设计表格，如图 63 所示。 （2）输入预测公式。在 E4 单元格中输入“=SUM(B3:D3)/3”，其中 SUM 为自动求和函数；在 G5 单元格中输入“=SUM(B3:F3)/5”；然后自动填充其他单元格的函数公式。则 J4、J5 单元格的值即为预测的 9 月份销量。,返回,上一页,下一页,第一节 销售预测与预算,（3）录入预测意见、插入日期。插入当前日期的方法如下。 a.选定第7行，单击上部的插入函数按钮，在“日期与时间”类别中选择当前日期函数 TODAY 进入“函数参数”界面，如图 64 所示；从该图可见，本函数不需要参数，是可变函数（即该日期会随计算机上时钟的改变而变化

4、），在该界面单击“确定”按钮即可。 b.设置日期格式。选择菜单“格式/单元格”命令进入“单元格格式”界面，在“数字”页签的分类中选择“日期”类，再设置相应的日期格式即可。,返回,上一页,下一页,第一节 销售预测与预算,（4）绘制圆圈图。图 63 的印章由艺术字、圆圈和五角星组成。其中的圆圈和五角星是用绘图工具栏设计的，绘制圆圈的操作方法如下。 a.选择“视图/工具栏/绘图”菜单命令，调出绘图工具栏。,返回,上一页,下一页,第一节 销售预测与预算,b.绘制圆形。单击绘图工具栏上的椭圆按钮，此时鼠标变“+”字状；按下键盘上的“Shift”键、同时用鼠标在工作表上拖动，绘出一个有填充色的正圆图形，如

5、图 65 上中部所示。 c.取消圆形图的填充色、设置线型与线条色。选定圆形图，选择绘图工具栏“填充色”按钮边的下拉箭头，在弹出的菜单中选择“无填充色”，则圆形图变为无填充色的圆圈图；选择绘图工具栏“线条色”按钮边的下拉箭头，在弹出的菜单中选择“红色”；选择绘图工具栏“线型”按钮，在弹出的菜单中列出了各种线条的粗细值，选择 2.25 磅。注意，以上过程也可选定圆形图，选择“格式”菜单（或右击圆形图）的相应命令，进入“设置自选图形格式”界面进行设置。,返回,上一页,下一页,第一节 销售预测与预算,（5）绘制五角星。选择绘图工具栏“自选图形/星与旗帜/五角星”菜单命令，在工作表中拖动一个五角星，如图

6、 66 所示。选择五角星，将其填充色设置为红色，将线条色设置为红色。通过五角星上的调节柄进行五角星大小、位置等的调整。 （6）插入艺术字，方法如下。 a.选择“视图/工具栏/艺术字”菜单命令，调出艺术字工具栏。 b.单击“插入艺术字”按钮进入“艺术字库”界面，在该界面选择“上弯型”艺术字样式，如图 67（1）所示；单击“确定”按钮进入“编辑艺术字文字”界面。,返回,上一页,下一页,第一节 销售预测与预算,c.在编辑界面选择“华文中宋”字体、20 字号，输入公司名称，单击“确定”按钮回到工作表界面，该艺术字被作为图片飘浮于工作表中，艺术字上有相关的调节柄。 d.选定艺术字，单击艺术字工具栏上的“

7、高度相同”按钮，使所有字体等高；再单击艺术字工具栏“字符间距”按钮，在下拉菜单中选择“稀疏”选项。 e.通过艺术字左部中间的调节柄、右部中间的调节柄向内拖动，通过下部中间的调节柄向下拖动，使艺术字变为向上弯曲的图形。通过拖动左部的绿色菱形调节柄扩大或缩小艺术字的大小、圆度等。 也可选定艺术字，单击艺术字工具栏的“形状”按钮，在下拉菜单中选“细上弯弧”，再按上述方法拖动调整。,返回,上一页,下一页,第一节 销售预测与预算,f.选定艺术字，通过绘图工具栏将其线条色设置为“红色”；也可通过艺术字工具栏的“格式”按钮进行设置。 g.用类似方法插入艺术字“财务专用章”，不同的是“艺术字库”中选等高直线型

8、样式。 （7）组合印章。将上述圆圈、五角星、两幅艺术字图片，组合为印章的方法如下。,返回,上一页,下一页,第一节 销售预测与预算,a.组图。拖动五角星到圆圈中心，再次调整其大小、高低；将上弯型艺术字拖到圆圈内，通过调节柄对其圆度、大小等进行调整；将直线型艺术字拖入圆圈内，调整其大小、高低。 b.组合。组图后的印章有 4 个对象，其中之一移动时，其他对象并不会移动，所以还应将其组合为一个图形。方法是：选定圆圈，按下键盘上的“Shift”键，再分别单击五角星、上弯艺术字、直线艺术字，使 4 个对象同时被选中；选择绘图工具栏“绘图”菜单选“组合”命令，则 4 个对象组合为一个图形。注意，组合也可通过

9、在选中的 4 个对象之一上右击，选择“组合/组合”命令进行。若组合不当需要重新加工，可右击该图选择“组合/取消组合”命令。 c.拖动。将印章拖动到工作表的适当位置。,返回,上一页,下一页,第一节 销售预测与预算,（8）取消网格线。默认情况下 Excel 要显示网格线；若不愿显示，则选择“工具/选项”菜单命令进入“选项”界面，在“视图”页签取消“网格线”前的复选框即可。,返回,上一页,下一页,第一节 销售预测与预算,二、回归直线函数预测法 回归直线法又称最小平方法，它是根据坐标系中时间序列中的销售量或销售额的观测数据，确定一条误差平方和最小的直线，据此直线预测分析未来销售量或销售额的方法。 【技

10、能储备】 1. Excel运用回归直线法的前提条件是，时间序列与销售数据之间，基本呈线性关系；因此，他们之间的关系可用直线方程 y =a+bX 描述。Excel 通过线性方程截距函数 Intercept确定参数 a 的值，线性方程斜率函数 Slope 确定参数 b 的值，它们的函数公式如下： = INTERCEPT (Known_ys, Known_xs) = SLOPE (Known_ys, Known_xs),返回,上一页,下一页,第一节 销售预测与预算,式中，Known_ys 表示数字型因变量数据点数组或单元格区域；Known_xs表示自变量数据点集合。这两个参数可以是数字，或者是包含数

11、字的名称、数组或引用；如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略，但包含零值的单元格将计算在内；如果 Known_ys 和 Known_xs 为空或其数据点个数不同（不匹配），则函数返回错误值“#N/A”。 2. Excel 回归预测时可能进行文本与数值的混合运算，使用四舍五入函数 ROUND、字符取位函数 MID 等，其使用方法请参阅本书第 3、第 4、第 5 章的介绍。 3. Excel对单元格区域的引用，可使用单元坐标，如 B2:G2、$B$3:$G$3 等方式进行单元格的相对引用或绝对引用；也可通过插入单元区域名称的方式进行单元区域的引用。单元名称的使用方法如下。

12、,返回,上一页,下一页,第一节 销售预测与预算,（1）选定要定义单元名称的单元格或单元格区域，进行该单元区域的名称定义。以后要引用该区域时，直接在公式中输入相应的单元名称即可。 （2）单元名称中的字符可以是字母、数字、句号和下划线；名称不能与单元坐标名称相同；名称可使用多个单词；名称中不能有空格；名称中不区分大小写；名称使用绝对单元格引用。 （3）名称运用于同一工作簿的所有工作表，即在本工作簿的不同工作表之间，可直接输入单元名称而实现不同工作表之间的引用；所以在同一工作簿中不要定义重复的单元名称，否则后定义的名称将替代以前的名称。 【案例 62】某产品最近 6 年销售量（万台）如表 62 所示

13、。要求用回归直线法预测第 7年、第 8 年的销售量。,返回,上一页,下一页,第一节 销售预测与预算,【操作提示】 （1）设计表格。在 A1:A7、B2:G3 单元格中输入文字、已知数据；合并 A1:G1、B4:G4、B5:G5、B6:G6、B7:G7 单元格区域；设置字体字号。如图 68 所示。 （2）定义单元区域名称。可用以下两种方法定义。 a.名称框定义法，选定 B2:G2 单元格区域，单击工作表上部的名称框，输入“回归年数”，然后按下键盘上的回车键“Enter”即可。,返回,上一页,下一页,第一节 销售预测与预算,b.菜单定义法，选定 B3:G3 单元格区域，选择菜单“插入/名称/定义”

14、命令进入“定义名称”界面，如图 69 所示，中部为本工作簿已定义的名称列表；下部的引用位置中将显示选定区域所在的工作表名称（用“！”分隔）及绝对引用的单元区域；在上部名称框中输入“回归销量”，单击“确定”按钮即可。 c.定义名称后，单击工作表上部名称框的下拉箭头，将会显示本工作簿中所有已定义的名称；若选定已定义名称的所有单元区域，则名称框中将显示其名称，部分选定或超范围选定不会显示名称；若需删除已定义的名称，应在“定义名称”界面中部选定该名称，单击“删除”按钮即可；若该名称已被引用则不要随意删除，否则引用的单元格将会因为引用的名称不存在而提示错误“#NAME?”。 （3）计算直线方程参数 a

15、的方法如下（单元格坐标引用法）：,返回,上一页,下一页,第一节 销售预测与预算,a.选定 B4 单元格，单击上部插入函数按钮，在“统计”类别中选择线性方程截距“INTERCEPT”函数进入“函数参数”界面，如图 610 所示。 b.在因变量Known_ys 参数中输入引用的单元格区域“B3:G3”，在自变量参数 Known_xs中输入引用的单元格区域“B2:G2”；单击确定按钮回到工作表界面。 （4）计算直线方程参数 b的方法如下（单元区域名称引用法）： a.选定 B5 单元格，单击上部插入函数按钮，在“统计”类别中选择线性方程斜率“SLOPE”函数进入“函数参数”界面，如图 611 所示。,

16、返回,上一页,下一页,第一节 销售预测与预算,b.在因变量 Known_ys 参数中输入引用的单元格区域名称“回归销量”，在自变量参数Known_xs 中输入引用的单元格区域名称“回归年数”；单击确定按钮回到工作表界面。 也可单击参数后的引用按钮到工作表中，选择菜单“插入/名称/粘贴”命令，在弹出的界面中选择相应的名称，实现参数对单元区域名称的引用。,返回,上一页,下一页,第一节 销售预测与预算,（5）混合运算显示预测公式。在 B6 单元格中输入等号“=”；输入文本“Y=“”；输入运算符“&”；输入对 B4 单元格取 0 位小数的四舍五入函数“ROUND(B4,0)”；输入运算符“&”；输入文

17、本字符“+“”；输入运算符“&”；输入对 B5 单元格取 0位小数的四舍五入函数“ROUND(B5,0)”；输入运算符“&”；输入文本字符“X“”。结果将显示为“Y=1343+276X”。 （6）销量预测。由于参数 a、b 值保留了若干位小数，应对预测结果用函数 ROUND 四舍五入保留 2 位小数；且第 7 年的“7”字在 A7 单元格第 2 位，应用字符函数 MID 取 1 位字符；所以在B7单元格中输入嵌套函数“=ROUND(B4+B5*MID(A7,2,1),2)”。预测结果为“3273.33”。,返回,上一页,下一页,第一节 销售预测与预算,三、相关性与回归直线分析 回归分析用模拟的

18、直线方程式 Y=a+bX 来预测销量，客观上需要知道销量（因变量）与时间序列（自变量）之间的相关程度。Excel 中可将直线拟合函数 LINEST 作为数值引用函数INDEX 的嵌套函数的方法，进行相关系数 R2、截距 a、斜率 b 的计算。 【技能储备】 1.直线拟合函数 LINEST 的函数公式如下： = LINEST(Known_ys, Known_xs, Const, Stats)式中，Known_ys 表示因变量；Known_xs 表示自变量。参数 Const 表示是否将截距 a 强制设为 0；如果为 TRUE 或省略则按正常的 a 值计算；如果为 FALSE 则将 a 设为 0，此

19、时直线公式为 y = bx。Stats 表示是否返回附加回归统计值；如果为 FALSE 或省略，则函数只返回系数a 和 b 的值；如果 Stats 为 TRUE，则函数可返回相关系数 R2等统计值。,返回,上一页,下一页,第一节 销售预测与预算,2.用嵌套函数计算斜率 b、截距 a、相关系数 R2的函数公式如下： b = INDEX(Linest(Known_ys,Known_xs,TRUE,TRUE),1,1) a = INDEX(Linest(Known_ys,Known_xs,TRUE,TRUE),1,2) 式中，INDEX 为数值引用函数，参见本书第 4 章的介绍；在 LINEST 返

20、回附加回归统计值的情况下，“1,1”为返回 b 值，“1,2”返回 a 值，“3,1”返回 R2值，所以用数值引用函数 INDEX提取这些值。 3.在统计学中，相关系数 R2的值为 1 则完全相关，为 0 则不相关；大于 0.8 则显著相关；在 0.50.8 之间则相关；小于 0.5 则弱相关。只有相关系数大于 0.5 时，回归直线法的预测结果才具有参考价值。,返回,上一页,下一页,第一节 销售预测与预算,【操作提示】 （1）设计表格。录入 A 列的文字；录入 B2:G3 单元区域的已知数据；合并 A1:G1 单元格；设置字体字号、调整行高列宽；如图 612 所示。 （2）单元区域命名。将 B

21、2:G2 单元区域定义名称为“相关法年份”；将 B3:G3 单元区域定义名称为“相关法销量”。 （3）计算相关系数。即 B4=INDEX(LINEST(相关法销量,相关法年份,TRUE,TRUE),3,1)，这是相关系数 R2的函数公式；结果为“0.9355”。 （4）用条件 IF 函数判断相关程度。在 B5 单元格中输入 IF 函数公式“=IF(B40.8,“显著相关“,IF(B40.5,“相关“,“弱相关“)”；结果是“显著相关”。,返回,上一页,下一页,第一节 销售预测与预算,（5）计算直线参数值。B6=INDEX(LINEST(相关法销量,相关法年份,TRUE,TRUE),1,2)，这

22、是截距参数 a 的函数公式；结果是“1343.3333”。B7=INDEX(LINEST(相关法销量,相关法年份,TRUE,TRUE),1,1)，这是斜率参数 b 的函数公式；结果是“275.7143”。（6）用字符运算显示预测公式。在 B8 单元格中输入“=”号；输入字符“Y=“”；输入运算符“&”；输入对 B6 单元格进行四舍五入并取 2 位小数的函数公式“ROUND(B6,2)”；输入运算符“&”；输入字符“+“”；输入运算符“&”；输入对 B7 单元格四舍五入并取 2 位小数的函数公式“ROUND(B7,2)”；输入运算符“&”；输入字符“X“”。结果为“Y=1343.33+275.7

23、1X”。,返回,上一页,下一页,第一节 销售预测与预算,（7）销量预测。在 B9 单元格中输入“=ROUND(B6+B7*MID(A9,2,1),2)”；结果为“3273.33”。在B10单元格中 输 入 “ =ROUND(B6+B7*MID(A10,2,1),2) ”； 结 果 为“3549.05”。 四、销售预算的编制 为了加强管理，企业应分月或分季编制未来一年的全面预算。全面预算从内容上看由销售预算、生产预算、直接材料预算、直接人工预算、制造费用预算、产品成本预算、存货预算、销售费用预算、管理费用预算、现金预算、预计资产负债表和预计利润表等组成。,返回,上一页,下一页,第一节 销售预测与

24、预算,【技能储备】 销售预算是全面预算的起点，它以销售预测为基础，确定未来一年各月或各季的销售量、单价、销售收入；同时还应确定销售收入中的现金收入预计数，它为本期（月、季）现销额加上本期收回上期赊销额之和。 【案例 63】某公司生产经营一种产品，产品销售单价为 240 元。2008 年 12 月中旬编制下年财务预算。经预测本年第 4 季度销售收入为 200 000 万元，2009 年各季销量（万台）分别为 850、780、800、840。该公司现销比例在 40%70%之间，其余为赊销款，在下一季度内全部收回。请编制 2009 年的销售预算。,返回,上一页,下一页,第一节 销售预测与预算,【操作

25、提示】 （1）建立一个工作表标签名为“销售预算”的工作表，并设计表格，录入已知数据，如图 614 所示。 链接中分别输入“40”、“70”、“1”、“10”、“$F$1”， 并勾选“三维阴影”选项。 c.代码取值与隐藏。设计代码的取值公式为 G1=F1/100。将 F1 单元格中的代码值居中隐藏于滚动条之后。 （3）输入计算公式。销售收入为销量乘以销售单价；收上季款为上季销售收入乘以赊销比例（1现销百分比）；收本季款为本季销售收入乘以现销百分比。单元公式请参阅图 614。,返回,上一页,第二节 生产预算与规划求解,一、生产预算的编制 【技能储备】 1.表间取数。企业全面预算的最大特点是以销定产

26、、以产定耗、以耗定购，所以生产预算必须根据销售预算编制。Excel 提供的表间取数方法非常适合这种数据之间的链接计算，当数据源变动时，引用这些数据的其他工作表也会随之变动，不必逐一重算。引用表间数据可用单元名称引用；也可用单元坐标引用，即甲工作表中需引用乙工作表的数据时，应在甲工作表的单元格中输入“=乙工作表!”，其中表名后要加英文的“!”，表示要引用的单元或单元区域。,返回,下一页,第二节 生产预算与规划求解,2.保护工作表。编制的预算表需要在企业的各个部门之间传递、执行，为了防止无权限者修改数据，可以进行工作表的保护。默认情况下，保护工作表后不能对各单元格进行编辑（即锁定），但可以选择单元

27、格或单元格区域，选定后工作表上部的编辑框中也会显示单元格的数值或公式。若不愿在编辑框中显示数值或公式，则应进行单元格内容的隐藏；隐藏是指隐藏数值、公式等在编辑框中的显示，工作表中各单元格的计算结果是不会隐藏的。 【案例 64】某公司 2009 年各季度预计销售量见案例 63。该公司每季末的库存为当季销量的 5%20%；2008 年第 4 季度预计销量为 830 万台。请编制该公司 2009 年的生产预算。,返回,上一页,下一页,第二节 生产预算与规划求解,【操作提示】 （1）设计工作表。录入 A1:A6、D1、B2:F2 的相关文字；合并 A1:C1 单元格区域；设置字体字号等。如图 616

28、所示。 （2）设计滚动条及代码取值。在 E1 单元格中拖动一个滚动条控件；控件格式设置中的最小值、最大值、步长、页步长、单元格链接分别为“5”、“20”、“1”、“2”、“$E$1”，并勾选“三维阴影”选项。代码取值公式为 F1=E1/100；将代码居中隐匿于滚动条之后。 （3）表间取数。由于生产预算与“销售预算”表（见图 614）中的预计销量是一致的，所以应进行表间取数。方法是：选定生产预算工作表的 B3 单元格，输入“=销售预算!C3”；再自动填充 C3:E3 单元格区域公式。,返回,上一页,下一页,第二节 生产预算与规划求解,（4）计算库存。季末库存量为本季销量乘以变动的百分比（即 F1

29、 单元格），并用四舍五入函数 ROUND 保留两位小数，所以第 1 季末库存计算公式为 B4=ROUND(B3*$F$1,2)；再自动填充其他 3 季末的库存公式；全年的期末库存为第 4 季末的，所以应为 F4=E4。本季初即为上季末的库存量，所以第 2 季初库存即为第 1 季末库存，第 2 季初库存公式为 C5=B4；然后自动填充第 3、第 4 季初库存；第 1 季初库存为上年第 4 季销量的百分比，所以公式为 B5=ROUND(830*F1,2)；全年期初库存即为第 1 季初库存，所以 F5=B5。 （5）计算生产量。本期生产量应为本期销量加期末库存减期初库存，所以第 1 季生产量公式为

30、B6=B3+B4B5；然后自动填充其他各季生产量。 （6）保护工作表。保护除 E1 单元格滚动条以外的单元格不被修改的方法如下：,返回,上一页,下一页,第二节 生产预算与规划求解,a.保护所有单元格。单击工作表左上角的全选按钮，选择“格式/单元格”菜单命令进入“单元格格式”界面，如图 617 所示；在“保护”页签中勾选“锁定”和“隐藏”两个复选框，然后单击“确定”按钮回到工作表界面。 b.取消不能保护的单元格。由于滚动条的代码值锁定后将无法使用，所以应取消锁定。方法是，选定 E1 单元格，选择“格式/单元格”菜单命令进入“单元格格式”界面；取消“锁定”复选框（可以不取消“隐藏”），然后单击“确

31、定”按钮回到工作表界面。 c.保护工作表。选择菜单“工具/保护/保护工作表”命令进入“保护工作表”界面，如图 618 所示；在此输入密码（若不使用密码则不必输入）；若允许其他人员对工作表的内容进行部分操作，则勾选下部的相应复选框（本例取默认设置）；单击“确定”按钮回到工作表界面。 。,返回,上一页,下一页,第二节 生产预算与规划求解,d按此法保护工作表后，由于 E1 单元格没有锁定，所以单击滚动条的上下箭头，F1、B4:F6 单元区域的数值也会随之变化。按上述方法保护后，任何单元均可单击选定，但工作表上部的编辑框中不会显示其数值、公式等，因为进行了单元格内容的“隐藏”；除 E1 单元格外不能编

32、辑（因为进行了“锁定”）。当双击这些单元格时，将会弹出不能编辑的提示界面，如图 619 所示；若要取消保护，则应选择“工具/保护/撤销工作表保护”，再输入相应的密码即可（若未设置密码，则不必输入）。需说明的是，进行工作表的保护后，单元格锁定、隐藏的功能才能发挥出来；也即是说，单独对单元格进行保护是不起作用。,返回,上一页,下一页,第二节 生产预算与规划求解,二、生产规划求解 【技能储备】 1. Excel提供了功能强大的单变量求解、双变量求解、规划求解、模拟运算表、数据透规划求解是一组命令的组成部分，这些命令有时也称为假设分析工具，它可以求出目标单元格中公式的最优值。从功能角度讲，规划求解可以

33、求解各种优化问题，如线形规划、整数规划和网络规划等。从工作原理角度讲，它是通过调整可变单元格中的数值，从目标单元格的公式中求得所需结果，这样重复迭代，直到获得最优的数值解。在迭代求取最优解的过程中，各种约束条件对获得最优解的效率和精度都有很大的影响；使用者可以对可变单元格的数值应用约束条件，而且约束条件可以引用其他单元格。,返回,上一页,下一页,第二节 生产预算与规划求解,2.工作簿的加密。工作簿（由多个工作表组成）的加密包括加密保存和加密保护。加密保存是用密码将工作簿保存起来，没有密码将无法打开工作簿，从而防止单位商业秘密的泄露。加密保护是指对工作簿编辑等权限的保护，加密保护工作簿后能够打开

34、工作簿，但限制其对工作簿的结构、窗口进行编辑或改变。其中的结构保护是指禁止对工作簿中的各工作表进行复制、移动、插入、重命名、隐藏和取消隐藏等操作。窗口保护是指禁止在每次打开工作簿时，改变工作簿的固定位置和大小；这种保护下，打开的 Excel窗口的右上角只有程序窗口的最大化、最小化等按钮，没有工作簿窗口的最大化、最小化、关闭等按钮。,返回,上一页,下一页,第二节 生产预算与规划求解,【案例 65】某公司在 3 个车间生产 3 种产品，各产品在车间耗用的工时、单台产品的销售利润、各车间能提供的总工时等。要求按利润最大化原则，安排产品生产。 （1）设计表格。录入文字、已知数据，合并单元格等，如图 6

35、20 所示。 （2）输入计算公式。在 C8 单元格中录入 3 种产品销售利润总额公式“=C6*C7+D6*D7+E6*E7”；在 F3 单元格中录入第 1 车间实用工时公式“=C3*$C$7+D3*$D$7+E3*$E$7”，再自动填充第 2、第 3 车间的实用工时；在 G3 单元格中录入第 1 车间剩余工时公式“=B3F3”，再自动填充第 2、3 车间剩余工时。,返回,上一页,下一页,第二节 生产预算与规划求解,（3）安装规划求解工具。Excel 默认情况下是没有安装规划求解工具的，所以应先行安装。方法是：将原 Microsoft Office 安装光盘放入光驱，选择菜单“工具/加载宏”命令

36、进入“加载宏”界面，如图 621 所示；选择“规划求解”，单击“确定”按钮即可。若原安装 MicrosoftOffice 软件时进行了全部安装，则不需要原安装光盘，但仍应加载宏。 （4）对最佳产量进行规划求解，方法如下。,返回,上一页,下一页,第二节 生产预算与规划求解,a.选定目标单元格C8，选择“工具/规划求解”菜单命令进入“规划求解参数”界面，如图 622 所示。 b.确定求解要求。因为按利润最大化安排各车间产品生产量，所以目标单元为“$C$8”（利润总额），选择“最大值”，求解的可变单元格是“$C$7:$E$7”（最佳产量）。,返回,上一页,下一页,第二节 生产预算与规划求解,c.添加

37、约束条件。产量不能有小数，单击下部“添加”按钮进入“添加约束”界面，如图 623 所示；选定引用位置“$C$7:$E$7”的约束条件为“INT 整数”，然后单击“确定”按钮。各车间的实际工时不能超过最大可提供工时数，单击下部“添加”按钮进入“添加约束”界面；选定引用位置“$F$3”的约束条件为“=$B$3”，然后单击“确定”按钮。再用类似的方法添加其他两个车间的工时约束条件。 d.求解最佳产量。单击规划求解参数界面的“求解”按钮，将弹出“规划求解结果”界面，如图 624 所示；单击该界面的“确定”按钮回到工作表界面，计算出 C7:E7 单元区域的最佳生产量，甲产品为 288、乙产品为 816、

38、丙产品为 1 152，C8 单元格的利润总额为 56 064，G4 单元格的第 2 车间剩余 4 小时（其他车间的工时被全部利用）。,返回,上一页,下一页,第二节 生产预算与规划求解,（5）处理规划求解结果。在“规划求解结果”界面可作如下处理。 a.选择“保存规划求解结果”并单击“确定”按钮，则回到工作表并将规划求解的结果 保存于工作表的相应单元格中。 b.选择“恢复为原值”并单击“确定”按钮回到工作表界面，将不进行求解。,返回,上一页,下一页,第二节 生产预算与规划求解,c.保存为报告。选择右部的“运算结果报告”并单击“确定”按钮，则在原有工作簿中 将新建“运算结果报告 1”，对目标单元、可

39、变单元、约束条件等进行相应的信息提示。若没有整数约束，则还可生成敏感性报告、极限值报告（本例有整数约束，不能生成这 2 个报告）。 （6）工作簿加密保护。选择“工具/保护/保护工作簿”菜单命令进入“保护工作簿”界面，如图 625 所示；选定要保护的元素，输入密码（也可不输入密码）；单击“确定”按钮回到工作表界面。加密保护后，若对此工作簿下部的工作表标签进行双击，将弹出“工作簿有保护不能更改”的信息提示框。取消工作簿保护方法是，选择“工具/保护/撤销保护工作簿”菜单命令。,返回,上一页,下一页,第二节 生产预算与规划求解,（7）工作簿的加密保存。工作簿加密保存有两种方法，操作方法如下。 a.另存

40、为加密法。选择“文件/另存为”菜单命令弹出“另存为”对话框，如图 626 所示；选择该界面右上角“工具/常规选项”进入“保存选项”界面（见图 626 中部），在其中输入打开密码、修改密码；单击“确定”按钮，再确认密码即可。,返回,上一页,下一页,第二节 生产预算与规划求解,b.选项工具保密法。选择“工具/选项”菜单命令进入“选项”界面，在“安全性”页签 中输入打开、修改的密码，并确认密码即可。 c.下次打开该工作簿时，将要求输入密码，若无打开密码将无法打开此文件；若无修改密码，可以使用打开密码以“只读”的方式打开。若需删除密码，则应先打开该工作簿，按加密的方法进入相关的界面，在其中删除密码即可

41、。,返回,上一页,第三节 本量利动态分析,本量利分析是指成本、业务量和利润三者依存关系分析的简称，它是在成本性态分析的基础上，通过对成本、业务量和利润三者的关系分析，建立数学化的分析模型和图式，进而揭示变动成本、固定成本、销售量、销售单价和利润等因素之间的内在规律性联系，以便为企业进行预测、决策、规划和控制提供有效的财务信息的一种定量分析方法。,返回,下一页,第三节 本量利动态分析,一、任意产品本量利分析 【技能储备】 1. Excel 中进行本量利分析时，主要使用以下基本公式： 保利点销量 = (固定成本总额+目标利润)(销售单价单位变动成本) 实际盈亏 = (销售单价单位变动成本)实际销售

42、量固定成本总额 保本点销量 = 固定成本总额(销售单价单位变动成本) 2. Excel中的滚动条、微调按钮、单选按钮、列表框、复选框、条件函数、绝对值函数等，能适应复杂多变的经济事项的分析、计算，所以本量利分析时必须掌握它们的运用技巧。,返回,上一页,下一页,第三节 本量利动态分析,【案例 66】某公司生产经营多种产品，这些产品售价在 80100 元之间，预计销量在2 万3 万台之间，单位变动成本在 4070 元之间，分配给各产品的固定成本在 20 万50万元之间，公司期望的每种产品的目标利润在 40 万60 万元之间。要求计算各种产品可能实现的利润、保本点销售、保利点销售、保本作业率、安全边

43、际率；并提示各种产品在任何可变状态下是盈利、保本还是亏损。 【操作提示】 （1）设计表格。录入文字，合并单元区域（D 列所有数值均不录入），如图 627 所示。 （2）设计滚动条与代码取值，方法如表 64 所示。,返回,上一页,下一页,第三节 本量利动态分析,（3）保本分析。在 D7 单元格中输入保本销量公式“=D5/(D2D4)”，在 D8 单元格中输入保本销售额公式“=D2*D7”。 （4）保利分析。在 D9 单元格中输入保利销量公式“=(D5+D6)/(D2D4)”，在 D10 单元格中输入保利销售额公式“=D2*D9”。 （5）信息提示。在任何可变状态下进行信息提示的设计方法如下：,返

44、回,上一页,下一页,第三节 本量利动态分析,a.用条件函数 IF 提示盈亏状况，在 B11 单元格中输入 IF 的嵌套函数“=IF(D3D7,“盈利“,IF(D3=D7,“保本“,“亏损“)”。 b.在 D12 单元格输入保本作业率公式“=D7/D3”。 c.在 D13 单元格中输入安全边际率公式“=(D3D7)/D3”。 d.在 D14 单元格中输入实现的盈亏额公式“=D3*(D2D4)D5”。,返回,上一页,下一页,第三节 本量利动态分析,二、本量利敏感分析模型 【技能储备】 1.敏感分析是在求得某个数学模型的最优解后，研究该模型中某个或若干个参数允许变化到何种范围，仍能使原最优解保持不变

45、；或当参数变化超出允许范围，原最优解已不能保持最优性时，提供一种简便的计算方法，重新求得最优解。在进行利润敏感分析时，为了简化计算，假设利润只受销售单价、单位变动成本、销售量和固定成本总额的影响，并且假设各因素均独立变动，即其中一个因素变动不会引起其他因素的变动。反映利润敏感性的指标是敏感系数，它说明各有关因素变动对利润的影响程度。敏感系数计算公式如下： 某参数敏感系数 =利润变动百分比该参数变动百分比,返回,上一页,下一页,第三节 本量利动态分析,2. Excel 中为了保证录入数据的准确性，可通过设置“数据有效性”的方法，在录入前、录入出错时进行提示，以便准确理解所要录入数据的经济含义，或

46、在出错时及时发现更正。 【案例 67】某产品销售单价 20 元，预计销量 3 万台，单位变动成本 12 元，固定成本总额 9 万元。要求计算这 4 个因素独立变动 1%时各自的敏感系数，确定它们对可实现利润、保本点的影响情况。,返回,上一页,下一页,第三节 本量利动态分析,【操作提示】 （1）设计表格。录入文字（暂时不用录入 C3:C6 单元区域的已知数据，以便进行单元格的有效性操作），合并单元格区域，如图 628 所示。 （2）设置数据有效性，以便在录入前、录入出错时提示，方法如下。 a.选定 C3 单元格，选择“数据/有效性”菜单命令进入“数据有效性”界面，该界面有设置、输入法模式等 4

47、个页签，如图 629 所示。 b.在“设置”页签中选择“整数”、“介于”，输入最小值“15”、最大值“25”。在“输入信息”页签的标题框中输入“销售单价”，输入信息框中输入“值为 1525 元整数”。在“出错警告”页签的样式中选择“停止”，在标题中输入“出错了！”，在错误信息框中输入“您录入的值在 1525 元之外，或不是整数！”，如图 630 所示。单击“确定”按钮回到工作表界面。,返回,上一页,下一页,第三节 本量利动态分析,（3）在 C3:C6 中录入已知数据。当选定 C3 单元格时将显示“输入信息”页签中的内容；如果输入 C3 单元格中的值有错，则弹出警告对话框，直到修改正确时为止，如

48、图 631 所示。 （4）插入单选按钮与代码取值，方法如下： a.选择“视图/工具栏/窗体”菜单命令调出窗体工具栏，单击其上的单选按钮，在 D3单元格中拖动一个适当大小的单选按钮控件；然后将其复制粘贴到 D4、D5、D6 单元格。 b.设置控件格式。右击 D3 单元格中的单选按钮进入“设置控件格式”界面，如图 632所示；在单元格链接中输入“$D$3”，并勾选“三维阴影”选项；再单击“确定”按钮。,返回,上一页,下一页,第三节 本量利动态分析,c.代码取值。以上单元格链接的设置对本工作表所有单选按钮生效，即所有单选按钮的代码值将显示于 D3 单元格中，并按其创建的先后分别显示为 1、2、3。由

49、于是单变量敏感分析（每一因素均独立变动），所以其变动率公式为：E3=IF(D3=1,1%,0)、E4 =IF(D3=2,1%,0)、 E5 =IF(D3=3,1%,0)、E6 =IF(D3=4,1%,0)。 d.隐藏设计代码。将 D3 单元格的字体颜色设置为白色而隐藏。 （5）计算自变量的变动后状况。F3=C3*(1+E3)、F4=C4*(1+E4)、F5=C5*(1+E5)、F6=C6*(1+E6)。,返回,上一页,下一页,第三节 本量利动态分析,（6）输入因变量相关公式。目标利润各单元格公式为：C7=(C3C5)*C4C6、E7=(F7C7)/C7、F7=(F3F5)*F4F6；保本销量各单元公式为：C8=C6/(C3C5)、E8=(F8C8)/C8、F8=F6/(F3F5)；保本点销售额各单元格公式为：C9=C3*C8、E9 =(F9C9)/C9、F9=F3*F8。 （7）输入敏感系数公式。在 G3 单元格中输入敏感系公式“=ABS(IF(E3=0,0,$E$7/E3)”。因为自变量的变动率为 0 时作分母将出现计算错误，所以用条件函数 IF 将错误值转换为 0；又因为成本上升时敏感系数为负数，所以用 ABS 函数取绝对值。,