管理会计中Excel的高级应用_生产经营最优化决策模型设计与应用

《管理会计中Excel的高级应用_生产经营最优化决策模型设计与应用》由会员分享，可在线阅读，更多相关《管理会计中Excel的高级应用_生产经营最优化决策模型设计与应用（3页珍藏版）》请在装配图网上搜索。

1、管理会计中 Excel 的高级应用 生产经营最优化决策模型设计与应用 温素彬周 晔短期经营决策中经常遇到最优化决策问题，如多品种 条件下的最优生产组合问题、最优定价决策问题、多目标决 策问题、最优运输方案问题、最优选址问题、最优库存问题 等。对于最优化问题，通常运用运筹学方法进行计算分析和 决策。但该方法对于一般会计人员而言较为复杂且不容易 理解，影响了最优化决策的效率。Excel 为管理会计的最优 化决策提供了方便的工具。本文将通过实例来设计最优化 决策模型。因为前面文章已经对最优存货决策模型的设计 进行过介绍，所以本文不再重述。一、最优化决策的特征与建模要素最优化方法的主要研究对象是各种有

2、组织系统的管理 问题及其生产经营活动，其目的是通过对问题的系统分析， 进行人、财、物、时的优化配置，达到决策的最优目标。管 理会计中的最优化问题大多为组合决策问题，如多品种的 产品生产组合决策、多路径的运输路线决策、多地址的销售 网点决策和生产厂址决策等。这些决策具有的共同特征是 ：第一，多目标特征。最优化决策往往具有多种目标，且 目标之间存在一定程度的不相融性或矛盾性，如利润最大、 工时最少的决策。正是这种不相融和矛盾才使得最优化决 策显得尤为重要 ；第二，多资源特征。最优化决策通常面临多种资源的综 合利用与优化配置 ；第三，资源约束特征。无论是产品组合决策、定价决策， 还是运输决策，都面临

3、资源有限的约束，最优化决策正是要 在资源有限的条件下达到最优目标，这些资源包括物质资 源、货币资源、人力资源、时间资源等。构建最优化模型一般包括决策变量、约束条件、目标函 数三个要素。决策变量是指最优化决策中待确定的因素 ；约 束条件是指在进行最优决策时对变量的限制，即资源约束， 包括资金约束、技术约束、物质约束、时间约束等 ；目标函数指最优决策希望要达到的效果，通常用数学函数表达。二、生产计划的最优决策模型（一）模型概要 生产计划是经营预算的重要环节，企业进行年度预算的起点是目标利润，根据目标利润进行销售预算，根据销售预 算进行生产预算，即“以销定产”。因此，生产计划决策的重 点在于根据料、

4、工、费、时等资源约束，以满足需求为前提， 决定如何进行生产计划的安排，包括资金配置、产品组合、 时间进度等，从而达到利润最大化或成本最小化的目标。生产计划的决策变量包括生产量、库存量等，关键技术 主要是规划求解工具的应用和 SUMPRODUCT 函数的应用。（二）问题描述公司主要生产 A、B 两种机器零件。根据目标利润和市 场需求，2015 年第一季度的需求量及期初存货量、期末存 货量资料见表 1，其中，期末存货量为根据第二季度市场需 求预测，为了满足销售需求应保有的最低存货量。根据生产 技术与单耗的测算，产品生产成本和储存成本资料见表 2。 根据预测，企业 2015 年第一季度可用资源如表

5、3。决策问 题 ：在满足需求的情况下，如何安排生产与库存计划，使得 总成本最小？该决策的目标函数是成本最小化 ；约束条件 包括产品需求约束、库存约束、资源消耗约束 ；决策变量为 产品生产量和存货量。（三）建模的技巧与步骤本文的计算模型见 ： 之文件夹“财务与会计论文”之“生产经营最优化决策模 型 .xlsx”之“生产计划”工作表。第一步 ：在数据区输入相关变量及原始数据，见工作 表“数据区”表 1、表 2、表 3。第二步 ：设计计算分析区。计算分析区包括三个区域 ： 决策变量区、约束条件区（需求约束、库存约束、资源消耗财会信息化Finance & Accounting表1产品需求信息表单位 ：

6、件“可变单元格 ”处引 入“$J$5:$L$6,$N$5,$N$5:$P$6”， 依 次 添 加 约 束 条 件 ：$J$10:$L$11=$M$10:$O$11、 产品2014 年年末存货2015 年预期需求量2015 年第一季度末存货1月 2 月3 月A3002 0003 5005 000400B3001 5003 5005 500200$M$14:$O$16= $S$10:$S$11。选择“单纯线性规划”，点击“求解”（见下图）。表 2产品成本信息表产品生产资源消耗生产成本（元 / 件）储存资源消 耗（M2/ 件）储存成本（元 / 件）机器（时 / 件）劳动力（时 / 件）A0.20.1

7、400.40.5B0.150.2200.60.3表 3第一季度可用资源信息表月份资源1月 2 月3 月机器资源（时）1 0001 5002 000劳动力资源（时）1 5001 5001 500储存资源（M2）2 0002 0002 000约束）、目标函数区。首先在数据区域（I3 ：S6）输入决策变量相关信息，其 中生产量、期末存货量为决策变量，平均存货量为中间变 量。在数据区 域（I8 ：O11）输入产品需求约束信息 ：令 J10 = M5+J5-N5，复制粘贴 到（J10 ：L11），计算本期产 品供应量。在数据区域（Q8 ：S11）输入库存约束条件。在 数据区 域（I14 ：S16）输入资

8、源约束条件 ：令 M14=SUMP RODUCT(J$5:J$6,$C10:$C11)，M15=SUMPRODUCT (J$5:J$6,$D10:$D11)，M16=SUMPRODUCT(N$5:N$6,$F10:$F11)。然后复制粘贴到 N14 ：O16，计算出资源实 际资源消耗量，同时输入资源供给量。在目标函数区域输入 M18=SUM(J5:L5)*E10+SUM(J6:L6) *E11+SUM(Q5:S5)*G10+SUM(Q6:S6)*G11。特别需要说明的是，目标函数的表达式必须是决策变 量的函数，以便运用规划求解工具进行最优化决策。第三步 ：运用规划求解工具进行最优化决策。首先加

9、 载规划求解工具，依次点击“文件 - 选项 - 加载项”，在下方 “管 理（A）：”处，选 择“Excel 加载项 ”，点 击“转到 ”，勾 选相关选项，点击“确定”（见下图）。然后，选择“保存规划求解的解”，点击“确定”，得到 最优解（见下图）。因此本案例最优解为 ：产品生产量期末存货量1月 2 月3 月1月 2 月3 月A17003500540000400B1200440048000900200单位 ：件最优成本为 632 520 元。在 Excel2010 版 打 开“数据 ”菜单，点 击“规划求解” 工 具。在“设置目标 ”处引入目标单元 格“$M$18”，在三、货物配送问题的最优决策

10、模型（一）模型概要2014 12财务与会计 61货物配送问题是物流管理中的重要决策问题，通常需 要综合考虑生产能力、配送成本、需求量等多个因素，以合 理地安排生产商向销售商的货物运送。配送的决策变量包 括生产量、需求量、配送成本等，关键技术主要是规划求解 工具的应用。（二）问题描述公司有 4 个生产基地、5 个销售配送中心，前者的生产 能力分别是 1 000 件、1 500 件、2 000 件、800 件，后者的需求量分别为 1 500 件、1 000 件、800 件、1 100 件、900（一）模型概要运输量中心 1中心 2中心 3中心 4中心 5工厂A0001 0000工厂 B700080

11、000工厂 C01 0000100900工厂 D8000000单位 ：件件。从生产基地向销售配送中心的单位运费如下表。决策问 题 ：如何安排配送方案，以达到配送成本最低？单位 ：元运费中心 1中心 2中心 3中心 4中心 5工厂A152010815工厂 B8106810工厂 C1210866工厂 D51015820（三）建模的技巧与步骤本文的计算模型见 ： 之文件夹“财务与会计论文”之“生产经营最优化决策模 型 .xlsx”之“配送问题”工作表。第一步 ：在数据区输入相关变量及原始数据，见工作 表“数据区”（B2 ：G6）。第二步 ：设计计算分析区。计算分析区包括三个区域 ： 决策变量区、约束

12、条件区（配送量约束、需求量约束）、目标 函数区。首先在区域 C10：G13 输入决策变量相关信息，即配送量， 初始值设为 1 或其他值。然后，在（H10 ：I13）、（C14 ：G15） 输入约束条件，即配送量 = 生产能力，接收量 = 需求量。在目 标函数区域输入 C17=SUMPRODUCT(C3:G6,C10:G13)。第三步 ：运用规划求解工具进行最优化决策。在“数据” 菜单，点击规划求解工具。在“设置目标”处引入目标单元 格“$C$17”，在“可变单元格 ”处引 入“$C$10:$G$13”， 依次添加约束条件 ：$C$14:$G$14=$C$15:$G$15、$H$10:$H$13

13、=$I$10:$I$13。选 择“单纯线性规划 ”，点 击“求解”（图略）。得到最优解为 ：（见本页右上图） 最优总运输成本为 38 400 元。以上问题为一级分销配送问题，如果延伸为多级配送 问题，可用同样的方法进行优化。四、销售网点问题的最优决策模型选择销售网点是企业经营决策的重要内容，尤其是零 售业和超市，销售网点过少不利于产品销售，网点过多又会 增加销售网点的管理和运营成本。因此，如何合理地分布销 售网点是一个重要的决策问题。此类问题可以简化为“是否 设置某销售网点 ”，即“是 ”或“否 ”，所以可运用运筹学中 的 0-1 规划进行优化。（二）问题描述某超市经过市场调查与细分，将 NJ

14、 划分为 8 大消费区 域，计划设立 4 个分销门店，所覆盖的区域分别如下表所 示。决策问题 ：如何设置门店，使门店运营费用最低。门店费用（万元） 12345678A500111111B3001111C80011111D20011111（三）建模的技巧与步骤本文的计算模型见 ： 之文件夹“财务与会计论文”之“生产经营最优化决策模 型 .xlsx”之“销售网点问题”工作表。第一步 ：在数据区输入相关变量及原始数据，见工作 表“数据区”（B2 ：K6）。第二步 ：设计计算分析区。计算分析区包括三个区域 ： 决策变量区、约束条件区、目标函数区（公式略）。第三步 ：运用规划求解工具进行最优化决策。 经最优化决策可得 ：应设置 A、D 两个网点。最优成本为 700 万元。（本文受国家自然科学基金资助项目、教育部人文社 会科学研究一般项目、江苏省高校哲学社会科学研 究项目 的资助，本文作者温素彬系全国会计领军 人才学术类二期学员）（作者单位 ：南京理工大学经济管理学院）责任编辑 李 卓62 财务与会计2014 12