Excel规划求解

《Excel规划求解》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、□财会月刊·全国优秀经济期刊Excel规划求解：企业全球运营管理工具耿海利（江西财经大学会计学院南昌330013）【摘要】随着全球经济一体化的深入，企业运营管理方式发生了很大变化。本文通过一个实例，来探讨企业集团拥有多个生产子公司、多个产品市场并且各个产品市场价格不同的情况下，企业如何使用Excel规划求解工具进行产品生产、运输和分配决策，以实现集团利润最大化。【关键词】规划求解企业集团全球运营决策敏感性分析在传统财务运营管理中，营运决策包括确定最佳现最大化目标的年生产和运输预算的决策方案。金持有量、最优订货批量，或者只是考虑单个市场的生产二、建

2、立线性数学模型与销售决策。企业集团全球运营管理涉及生产、运输、销1.定义决策变量。下文中，（ii=1，2，3，4）表示工厂，j售等环节，需要在实现集团利润最大化的同时，解决生产表示市场（j=1，2，3，4）；决策问题可以用图2表示。所以定什么产品、在哪里生产、生产多少、运到哪个市场等诸多义决策变量为Xij：即在i工厂生产的产品投放到j市场。问题。显然，采用传统的运营管理方法会比较棘手。而Ex⁃cel提供的规划求解工具，不但能非常迅速地求出多种营运决策模型的最优解，还可以给出敏感性分析报告，满足财务全球化运营管理的需求，有效提高公司决策效率，同时也

3、能促进财务人员更多地参与到公司管理决策中。一、问题描述某跨国集团在中国和其他地区设立了四个工厂，分图2决策问题别为A、B、C、D厂，产品主要面向国际市场销售，分别销往北京、香港、纽约、东京四个城市。各个工厂的单位产品2.确定目标函数。最大利润=收入-产品变动成本-成本、固定成本、产能，各个市场的销售价格和需求量，以其他成本最大利润=55500（X11+X21+X31+X41）+61100及各个工厂到每个市场的运输成本见图1。（X12+X22+X32+X42）+57800（X13+X23+X33+X43）+62650（X14+X24+X34+X44

4、）-34900（X11+X12+X13+X14）-32200（X21+X22+X23+X24）-38350（X31+X32+X33+X34）-23400（X41+X42+X43+X44）-（500X11+12225X12+9075X13+21450X14+4500X21+……+15150X43+5925X44）。3.列出约束条件。（1）产能约束：X11+X12+X13+X14≤101；X21+X22+X23+X24≤201；X31+X32+X33+X34≤121；X41+X42+X43+X44≤250。（2）需求约束：X11+X21+X31+X4

5、1≤150；X12+X22+X32+X42≤75；X13+X23+X33+X43≤200；X14+X24+X34+X44≤100。（3）非负约束：Xij≥0。4.最优解：最大利润时的Xij。图1集团基本运营决策数据三、数据及公式准备在每个工厂产能允许同时最大限度满足市场需求的1.数据输入：把图1集团公司的决策数据输入新建的情况下，集团管理层希望财务部给出能够实现集团利润Excel表中，如图3所示。□·110·2014.8下全国中文核心期刊·财会月刊□PRODUCT（B2∶E2，B13∶E13）”；计算变动成本，选择B17单元格，输入公式“=SUM

6、PRODUCT（F4∶F7，F9∶F12）”；计算运输成本，在B18单元格输入公式“=SUMPRODUCT（B4∶E7，B9∶E12）”。SUMPRODUCT（数组1，数组2）函数，可以用来计算长度相等的行、列或者区域对应元素乘积的和，是模型中重要的函数，在计算多个数据乘法时会非常简便。5.计算总利润。选择B19单元格，输入公式“=图3数据准备相关公式B16-B17-B8”；在固定成本所在单元格输入公式“=SUM（G4∶G7）”；计算净利润，选中B21单元格，输入公式“=B19-B20”。四、规划求解使用Excel规划求解工具，要求安装Excel

7、时选择“完全安装”，“工具”菜单下才有“规划求解”子菜单。如果安装Excel时没有安装规划求解功能，需要加载“规划求解”功能，点击Excel左上方图标，选择“Excel选项”，出现Ex⁃cel选项对话框，选择左侧“加载项”→“规划求解加载项”→“确定”，然后默认安装即可使用规划求解功能。1.选中目标函数所在单元格B19，然后选择“工具”菜图4规划求解参数设置单栏下的“规划求解”，会出现规划求解参数对话框，如图4所示，默认即为求目标函数最大值。2.设置可变单元格：可变单元格一般是决策变量所在区域，在求解时会不断调整，直到满足目标函数最大化。把光标移

8、动到可变单元格下的选择框内，然后选中“B9∶E12”区域，松开鼠标即可完成设置。3.添加约束条件：选择“添加”按钮，出现添加约束条图5添