运用EXCEL规划求解投资项目管理.pdf

441研究与探讨 运用EXCEL规划求解投资项目管理 Using EXCEL for Planning and Determining Investment Project Management 在生产管理， 经营决策及项目 投资等商业活动过程中， 经常会遇 到一些规划问题。 例如生产的组织 安排， 产品的运输调度， 作物的合 理布局， 原料的恰当搭配， 项目的 投资方案等， 其共同点就是要解决 如何合理地利用有限的人力、物 力、 财力等资源， 得到最佳的经济 效益，即达到产蜇最高、 利润最 大、 成本最小、 资源消耗最少等目 标。 这些问题中通常要涉及到众多 的关联因素， 复杂的数量关系，只 凭经验进行简单估算显然是不行 的。 而线性规划、 非线性规划和动 态规划等方法正是研究和求解该类 问题的有效数学方法。 但是这些方 法的求解过程大多十分繁琐复杂， 常令人望而却步。Office中的组件 之-Excel提供了一种规划求解工 具， 用于解决复杂的方程求值及各 类线性或非线形的 有约束优化问 题， 可以方便快捷地帮助人们得到 各种规划问题的最佳解。 一、 规划模型的含义 规划问题可以涉及到众多的生 产、 经营和投资等领域的常见问 题。 例如生产的组织安排问题如 果要生产若干种不同的产品，每种 产品需要在不同的设备上加工，每 种产品在不同设备上需要加工的时 周明红 间不同，每种产品所获得的利润也 不同。 要求在各种设备生产能力的 限制下， 如何安排生产可获得最大 利润。 又如运输的调度问题 如果 某种产品的产地和销地有若干个， 从各产地到各销地的运费不同。 要 求在满足各销地的需要量的情况 下， 如何调度可使运费最小。 再如 作物的合理布局问题不同的作物 在不同性质的土壤上单位面积的产 量是不同的。 要求在现有种植面积 和完成种植计划的前提下， 如何因 地制宜使得总产值最高。 还有原料 的恰当搭配问题 在食品、 化工、 冶金等企业， 经常需要使用多种原 料配置包含一定成份的产品，不同 原料的价格不同，所含成份也不 同。 要求在满足产品成份要求的情 况下，如何 配方可使产品成本最 小。以 及在项目的投资决策问题 上有多个项目可以进行投资， 各 个项目的回报方式 、 回报时间、 回 报金额各不 相同，将 一定数量的资 金如何投资到各个项目中，才能使 最终的回报金额最大。 等等。 虽然规划问题种类繁多，但是 其所要解决的问题可以分成两类 一类是确定了某个任务， 决定如何 使用最少的人力、物力和财力去完 成它；另 一类是巳经有了一定数量 的人力、 物力或财力， 决策如何 使 它们获得最大的收益。 如果从数学 的角度来看， 这类规划问题都有下 述共同特征 决策变量每个规划问题都有 一组需要求解的未知数x1,X2, 心， 称作决策变量。 这组决策变量 的一组确定值就代表一个具体的规 划方案。 在Excel规划求解工具中， 可变单元格代表的是决策变董， 求 解时其中的数值不断调整， 直到满 足约束条件 ，并且使 “目标单元 格”中的值达到目标值（最大，最 小，某值）， 可变单元格必须直接 或间接与目标单元格相联系。 约束条件对于规划问题的决策 变量通常都有一定的限制条件， 称作 约束条件。 约束条件可以用与决策变 量有关的不等式或等式来表示。 目标每个问题都有一个明确 的目标，如利润最大，成本最小， 路程最近。 目标通常可用与决策变 量 有 关的函数表示。 在Excel规划 求解工具中， 包含公式的目标单元 格代表的是目标函数。 规划求解问题首先要解决的是 将实际问题数学化、 模型化，即将 实际问题通过一组决策变量、 一组 用不等式或等式表示的约束条件以 及目 标 函数 来 表示。然后应用 Excel的规划求解工具求解出 一组 具体方案值。 求解使目标单元格最 优化 、约 束条 件 得 到满 足 的 最佳 决 策变 量值 。 二 、 问题 的提 出 以某 公 司对几 个 项 目的投 资 为 例 ．说 明 利 用 E x c e l规 划 法 进 行 决 策 的分 析 和计算 过程 。 例 如 ，某公 司要 进行 项 目投资 ， 目前有下 面 四个可 投资 的项 目 项 目 A从 第 一 年 到第 四 年 每 年 年初 需 投 资 ， 于 次 年 末 回收 本 利 1 1 5 ％ 项 目 B从 第 三 年 年 初 投 资 。 到 第 五 年 年末 回 收 本 利 1 2 5 ％ ，规 定 最大投 资 额不得 超 过 4万元 项 目 C第 二 年 年 初 投 资 。到 第 五 年 年 末 回 收 本 利 1 4 0 ％ 。最 大 投 资额不 超过 3万 元 ； 项 目 D五年内每年年初 可买公 债 ，于当年末归还 。并加利息6 ％。 该 部 门 现 有 资 金 1 0万 元 。应 如 何 确 定 这 些 项 目每 年 的 投 资 额 ， 使得 到第 五 年末 拥有 的资 本 的本 利 总 额 最 大 。 这是 个 线性 问题 。首 先 将 其 模 型化 。即根据 实 际 问题 确 定决 策 变 量 ，设 置 约束条 件 和 目标 函数 。 1 、决策变量。 该 问题 的决策变量显然 是各 年在 各项 目上的投 资金额。假设 X ，X ， X ，X 。 。 i l ，2 ，3 ，4，5 分 别表 示第 i 年年初在 项 目 A，B ，C，D上 的投资额。 2 、 问题 建模 。 ① 约 束 函数 条 件 为 获得 最 大 利 益 。每年 的投 资 额 应等 于 当 年拥 有 的全部 资金 。项 目 D每年 年 初投 资 ，年 末 回收 本利 ，可作 为 次 年各 项 目投 资 的本 金 ．故第 一 年 可 将所 有 的资金 全部 投 出 第 一 年 将 部 门拥 有 的 1 0万 元 资 金 全 部 投 入 项 目 A 和 项 目 D 中 。故有 约束 条件 函数 式 X1 A X1 D 1 0 第 二 年 由 于第 一 年 投 入 项 目 A的资 金需 到 第 二 年末 才 能收 回本 利 ，所 以该部 门第 二 年 年初 拥 有 的 资金 仅为 项 目 D在第 一 年 末收 回 的 本 息 ， 即 1 ． 0 6 X 。 ， 这 些 金 额 可 用 于第 二 年项 目 A，C，D 的投 资额 。 故 有 约束条 件 函数 式 X2 A X2 C X2 D 1． 0 6 Xm 第 三 年 第 三 年 初 拥 有 的资 金 包 括第 一 年项 目 A投 资 的本 息 回收 额 及第 二 年项 目 D投 资 的本息 回收 额 总 和 ，即 1 ． 1 5 Xl 1 ． 0 6 X 2 D ，而 第 三 年 可 将 这 些 资 金 投 入 到 项 目 A． B，D 中。故有 约束条件 函数式 X3 A X3 B X3 D 1 ． 1 5 Xl A 1 ． 0 6 X2 o 第 四 年 第 四年 初 拥 有 的 资 金 包 括项 目 A第 二年 的投 资 回报 及 项 目 D第 三 年 的 投 资 回报 总 和 ，即 ． 1 ． 1 5 X 2 A 1 ． 0 6 X 3 。这 些 投 资 可 用 于项 目 A 和 D 的 投 资 中 。故 有 约 束 条 件 函数式 X“X4 I 】 1 ． 1 5 X1 ． 0 6 X3 D 第 五 年 只有 项 目 D可 以在 第 五 年年初 进 行 投 资 ，其投 资 的资 金 可来 自项 目 A第 三年 的 投资 回报 和 项 目 D第 四年 的投 资 回报 总和 。即 1 ． 1 5 X 3 A 1 ． 0 6 【 4 。 ，故 有 约 束 条 件 函 数 式 X5 o 1 ． 1 5 X3 A 1 ． 0 6 X4 0 另外 。项 目 B和项 目C的投资额 有最大限度 。故还应有两个约束式 X2 c≤ 3 X3 B ≤ 4 ② 目标 函数 该 问 题 要 求 投 资 后 在第 五 年末 拥 有 的资 本本 利 总 额 最 大 ．故 目标 函数 式 为 F x 1 ． 1 5 X 4 A 1 ． 4 X 2 c 1 ． 2 5 X 3 B 1 ． 0 6 X 5 0 ③最优化 问题 要如何投 资才 能 使得 F x 】取得最 大值 ，可建 立 下列 数 学 模 型 ．用 线 性 规 划 描 述 为 醢 g 臻蛹譬誊 毪鲁 靛 强甏瀣蕊毯 g | 蛰 链强 醢 强 | ； l 鼙强 曹 二 董 茸薰 四 _正l 庳 翥 I } ol 0j l O B l 0l 0I O c f Oj Ol 0 D l { 0l Ol 0 戛F J l I 卜 争 嘴 研究与探讨 I 4 5 Ma x 1 ． 1 5 X4 A 1 ． 4 X2 c 1 ． 2 5 X3 B 1 ． 0 6 X5 0 S． T． Xl A Xm一1 00 X2 A X2 C X2 D 一1 ． 0 6 XIn 0 X3 A X3 B X3 D 一1 ． 1 5 Xl A 一1 ． 0 6 X2 D 0 X4 A X4 I 】 一1 ． 1 5 X2 A --1 ． 0 6 X3 D 0 Xs o 一1 ． 1 5 X3 A 一1 ． 0 6 X4 I 】 0 X， r ≤ 3 X3 B ≤ 4 X ， X i B ， X ， X iD ≥ 0 i l ， 2 ， 3 ， 4， 5 三 、 运 用 E x c e I进 行 规 划 模 型 的 求解 建 立 好规 划 模 型 后 。就可 以使 用 E x c e l的规 划 求 解 工 具 求 解 。 先 加 载 规 划 求 解 工 具 从 E x c e l的 “ 工 具 ”菜 单 中执 行“ 加 载 宏 ” 命 令 ，在 出现 的对话 框 中 。选定 规 划 求解 的复 选框 并确 定 。 1 ． 根据模 型建 立工 作表 。 该 问题 涉 及 到 每个 项 目每 年 的 投 资额 和利 息 额 。假设 每个 项 目每 年 的投 资额 初 值 均 为 0。则 将 决 策 变量和利率 建立成为 图 1所示表格 。 其 中 的约束 条 件 表 达 式 分别 为 表 1所 示 目标 单 元 格 中 的 表 达 式 为 G1 6G7E9 E1 8 F 1 7F 8H1 9xH1 0 2 ． 运用 规 划求解 工 具 。 执 行“ 工具 ”菜 单 下 的“ 规 划 求 解 ”命 令 。在 弹 出的对 话 框 中做 如 图 2的设 置 。 指 定 目标 单 元 格 为 D 2 3，可 变 单 元 格 为 D 7 H 1 0．将 约 束 条 件 逐 一 添 加 进 去 。 再 单 击“ 选 项 ”按 钮 ．在 其对 话 框 中选 定 “ 采 用 线 性 模 型 ” 和“ 假 定 非 负 ” 两 项 。要求 用 线性 模 型 解决 非线 性 问 图 1 决策变量和利率关 系图 图 2 规 划求解参数 图 维普资讯 研究与探讨 表 1 约 束 条件 表达 式 表 单元格引用位置 约束值 说明 D 7 Dl O 1 0 o o 0 0 第一年投到项 目 A和 D上 的资金为 l O万元 E 7 E 9 E l O H1 9 x D1 0 O 第一年末项 目 D收 回的本息等于第二年项 目 A．C．D的投资额 第二年 末项 目 A及项 目D收 回的本 息总和等 于第 三年 项 目A，B． F 7 F 8 F 1 0 一 Gl 6 D7 一 H1 9 x E1 0 O D的投 资额 第三年末 项 目 A及 项 目 D收回的本 息总 和等于第 四年项 目 A和 D G1 0 一 G1 6 x E 7 一 H1 9 F 1 0 O 的投资额 Hl O G1 6 x F／ 一 H1 9 x G1 0 O 第四年末项 目A及项 目 D收回的本息总和等于第五年项 目 D的投资额 F 8 4 0 0 0 o 项 目B的最大投 资额不得大于 4万元 E 9 3 0 0 0 0 项 目C的最大投资额不得大于 3万元 表 2 计 算 结 果表 第一年 第 二年 第 三年 第 四年 第五年 A 7 1 6 9 8 ． 1 1 3 2 1 0 4 2 4 5 2 ． 8 3 O 0 B O O 4 0 0 0 o O 0 C O 3 0 0 0 o O O 0 D 2 8 3 0 1 ． 8 8 6 7 9 O O O 4 8 8 2 0 ．7 5 题 ，并假 定投 资 额不 能 有 负值 。其 它采 用 默认设 置 。 确定后 单 击求 解按 钮 。E x c e l即 开始 进行 计算 ，出现 规划 求 解结 果 对话 框 ，进 行 求解 。最 后 的计 算 结 果 为 表 2所 示 。 从 计 算 结果 可 以看 出 ．最 佳投 资方 案是 第一年 第二 年 第三 年 第 五 年 Xl A 71 6 9 8 ． 1 1 3 2 1元 X l D 2 8 3 0 1 ． 8 8 6 7 9元 X2 c 3 0 0 0 0元 XS A 4 2 4 5 2． 8 3元 X3 B 4 0 0 0 0元 X 4 8 8 2 0 ． 7 5元 四 、对 最 佳 解 决 方 案 的 直 观 解 释 第 一年 ，将 1 0 0 0 0 0元 资金 分别 投 在 项 目 A 和 项 目 D 上 A项 目上投资 7 1 6 9 8 ． 1 1 3 2 1元 ．第 二 年 年末 回收 本 息 7 1 6 9 8 ． 1 1 3 2 1 1 1 5 ％ 8 2 4 5 2 ． 8 3元 D 项 目 上 投 资 2 8 3 0 1 ． 8 8 6 7 9元 ． 第 一年年末 回收本 息 2 8 3 0 1 ． 8 8 6 7 9 x 1 0 6％3 0 0 0 0兀 。 第 二 年 ，只有 项 目 D 收 回 的 本息可用 于投 资 ，只能投 在项 目 C 上 C项 目上 投资 3 0 0 0 0元 ，第 五 年 年 末 回 收 本 息 3 0 0 0 01 4 0 ％ 4 2 0 0 0元 第 三 年 ．只有 项 目 A第 二 年末 收 回的本 息可 用 于投 资 ，分 别投 在 项 目 A 和 项 目 B上 A项 目上 投 资 4 2 4 5 2 ． 8 3元 ．第 四年 年 末 回收 本 息 4 2 4 5 2 ． 8 3 l 1 5 ％ 48 8 2 0． 7 5元 B项 目上 投 资 4 0 0 0 0元 ．第 五 年 年 末 回 收 本 息 4 0 0 0 01 2 5 ％ 5 0 o 0 0元 第 四年 没有 投资 。 第 五 年 ，只有 项 目 A第 三 年末 收 回的本 息 可用 于投 资 ．全 部投 在 项 目 D 上 D项 目上投 资 4 8 8 2 0． 7 5元 ，第 五 年 年 末 回收 本 息 4 8 8 2 0 ． 7 5 l 0 6 ％ 5 1 7 5 0元 这样 到 第 五年 末 共 收 回本 息 总 额为 4 2 0 0 0 元 5 0 0 0 0 元 5 1 7 5 0 元 1 4 3 7 5 0元 五 、分 析 规 划 求 解 结 果 E x c e l的规 划 求解 工 具可 以根据 需 要 生 成 多 个 报 告 运 算 结 果 报 告 ，敏 感 性 报 告 ，极 限 值 报 告 。 在 “ 规 划 求 解 结 果 ” 对 话 框 中 的报 告 栏 中可 以选 择 要生 成 的报 告 ．见 图 3。 通过 查 看 规 划求 解 工 具 生成 的 各种 报告 ，可 以进 一 步 分析 规划 求 解 结 果 ，并 根 据需 要 修 改或 重新 设 置 规 划求解 参 数 。当规 划求 解失 败 时 ，还 可 以适 当调 整 规 划 求 解 选 项 。 在 运 算结 果 报 告 中 列 出 目标 单 元格 和可 变 单元 格 以及 它们 的初 始值 、最 终 结果 、约束 条 件 和有 关 约束 条 件 的信 息 。本例 中有关 决策 变 量 的 约 束 条 件 中 ， 约 束“ D 3 0 F 3 0” 和“ D 3 1 F 3 1 ” ，即项 目 B和项 目 C的投 资额 不 能超 过 4万 元 和 3万 元 的约束 条 件 已达到 限 制值 。这 一点 通 过敏 感 性 报告 可 以更清 楚地 反映 出来 。 敏 感 性报 告 中 ，列 出 了 在 “目 标 单 元 格 ” 中所 指 定 的 公 式 的 微 小 变 化 ，以及 约束 条 件 的微 小变 化 对 求 解结果 的影 响 。此报 告 提供 关 于 求解 结果 对 这些 微小 变 化 的敏 感性 的信 息 。当可 变 单元 格 中 的决 策变 量 的值 在“ 允 许 的增 减 量 ”范 围 内 变化 时 ． 目标 单元 格 中的值 不会 受 到 影 响 极 限 值 报 告 中 列 出 了 目标 单 元 格 和 可 变 单 元 格 以 及 它 们 的 数 值 、上 下 限 和 目标 值 。 下 限 是 在 满 足 约 束 条 件 和 保 持 其 它 可 变 单 元 格 数 值 不 变 的 情 况 下 ，某 个 可 变 单 元 格 可 以 取 到 的 最 小 值 。上 限 是 在 这 种 情 况 下 可 以 取 到 的 最 大 值 。 含 有 整 数 约 束 条 件 的模 型 不 能 生 成 本 报 告 。 从 上 面 的 计 算 和 分 析 可 以 看 出 ．运 用 E XC E L 进 行 规 划求 解 可 以帮助 决策 者进 行 投 资结果 的预 算 和分 析 ，以便正 确进 行 投 资 ，避免 不必 要 的损失 。 图 3 规划求解 结果选择对话框 作者单位山西大学商务学院