Excel会计与财务管理-理论、方案暨模型（第二版） 桂良军课件第9章 Excel最优化问题应用

第9章Excel最优化问题应用本章教学要求：了解最优化问题的数学模型和解决方法掌握Excel规划求解工具掌握使用Excel规划求解工具解决运输问题、资金管理问题、生产管理和投资组合问题主要内容：9.1最优化问题概述9.2最优化问题求解工具——Excel规划求解9.3常见的最优化问题第9章Excel最优化问题应用9.1最优化问题概述最优化问题定义：最优化问题就是在给定条件下寻找最佳方案的问题。即在资源给定时寻找最好的目标，或在目标确定下使用最少的资源。最佳——成本最小、收益最大、利润最多、距离最短、时间最少、空间最小等等。最优化问题的分类：根据有无约束条件

无约束条件的最优化问题

有约束条件的最优化问题根据决策变量在目标函数与约束条件中出现的形式线性规划问题非线性规划问题二次规划问题根据决策变量是否要求取整数整数规划问题0-1规划问题任意规划问题最优化问题的数学模型……最优化问题的求解方法公式法用规划求解工具求解√

用查表法求解常见的最优化问题运输问题资金管理问题生产管理问题最优投资组合问题9.2最优决策分析工具——Excel规划求解作用：它是优化和资源配置工具，用来解决含有多变量和多限制的问题，帮助企管人员使用最好的方法、利用有效的资源、达到目标成本最小。工具的安装：开发工具/加载项简单举例：1.线性规划问题

某工厂生产A、B两种产品，每个A产品可赚50元，每个B产品可赚30元。制造一个A产品需要2小时机工和1小时手工；制造一个B产品需要1小时机工和1小时手工。由于机工和手工小时均有限，每周机工不超过150小时，每周手工不超过120小时，每周制造A产品不超过60件，试进行生产决策。设生产A产品X1件、B产品X2件，利润为Z规划求解选项2.求解方程组保存和装入规划求解模型1.保存模型2.装入模型目标单元格值可变单元格个数约束条件1约束条件2………选项对话框设置信息9.3常见的最优化问题1.运输问题【例9-3】某公司有3个工厂，生产的产品需要运往4个仓库，每个工厂的供应量、每个仓库的需求量以及单位运输成本数据下表所示。该公司如何安排运输才能使得总运费最小？目的地（仓库）单位运输成本（元）仓库1仓库2仓库3仓库4供应量出发地：工厂A46451365486775工厂B352416690791125工厂C995682388685100需求量806570852.资金管理问题【例9-4】现有10000元准备存入银行，可以选择一年期、二年期和三年期存款。三种存款的年利率分别为2.5%、2.7%和2.9%。第3年末和第5年末需要使用现金1000元和2000元，第4年末有5000元的现金收入可以存入银行。试在Excel中建立模型，计算每年年初的到期本金、到期利息和年末现金余额；用规划求解工具求解每年年初每种存款的最优存款额，使第6年末的现金余额最大；把规划求解参数保存在合适的单元格中。3.生产管理问题【例9-5】已知某公司生产的产品在不同月份的需求量、单位生产成本与生产能力不同，见下表：

每月的储存成本等于单位储存成本与月平均库存量（月初库存量与月末库存量的平均值）的乘积，而每月的单位储存成本等于当月单位生产成本的2.5%。公司要求每月的生产量既不超过当月生产能力又不低于当月生产能力的一半，另外，为防备急需，管理人员还要求每月月末库存量不少于1500件（安全库存量），仓库容量为6000件，当前库存量为2750件。假设每月生产量为1件，试在Excel中建立规划求解模型，并且按如下要求操作：计算每月的月初库存量、生产量下限、月末库存量、月平均库存量、单位储存成本和总成本；用规划求解工具求解每月的最优生产量和6个月总成本的最小值；把规划求解参数保存在单元格中。4.最优投资组合问题投资组合预期收益率：投资组合整体风险：最优投资组合：4.最优投资组合问题投资组合风险收益率RpD：风险厌恶度，R：预期收益率，：预期方差4.最优投资组合问题4.最优投资组合问题【例9-6】现有一笔资金，准备购买A、B和C三个公司的股票。各公司在过去12年的收益率见下表：问：在保证收益率不低于12%的前提下，怎样组合可以使风险最小？在方差不大于0.01的前提下，怎样组合可以使收益率最大？当风险厌恶度分别等于O、0.5和1时，求解最优证券组合，计算出收益率和方差。1．规划求解报告的生成运算结果报告敏感