Excel在本量利分析中的应用规划求解与最优生产安排院校遵.pptxVIP

Excel在本量利分析中的应用-规划求解与最优生产安排

课程名称：《Excel在财务中的应用》

院校：遵义职业技术学院

主讲老师：王楠

项目四:Excel在成本管控中的应用

Excel2016规划求解功能应用

功能概述

Excel2016的规划求解功能十分实用，它能根据已知的约束条件求出最优结果，也可以理解为通过更改单元格变量来确定目标单元格的最大值、最小值或者目标值，在生产安排、资源配置等工作中应用广泛。

案例应用

今天，我们就承接引导案例资料，以重庆柏思酒店有限责任公司海鲜自助餐厅的订单安排为例，学习如何利用规划求解功能进行最优生产安排。

01

重庆柏思酒店有限责任公司下设一个海鲜自助餐厅，接受个人点餐与团建用餐，共80名餐厅人员负责餐厅的整体运作。

酒店运营概述

每个厨师每天可以服务5单个人点餐，80名厨师，每人每年工作天数为300天。

02

个人点餐服务

团建用餐需要2天的准备时间，公司规定为个人订单服务的总天数不得少于为团建服务天数。

03

团建用餐服务

销售服务成本数据如表4-24所示。

04

销售服务成本

案例概况

操作步骤

为最大化利用资源，达到边际贡献最大化的目标，分析应当如何安排订单服务。

操作步骤

设计最优生产表。我们新建Excel2016表格，在表格内录入表单数据，作为分析的基础，样表设计如图4-41所示。

操作步骤

此时订单安排计算表中数据均为0，因为还没有设置规划求解，规划求解的添加方法如图4-42所示。

规划求解准备

在订单安排计算表部分，单元格K3、K5都录入“0”，单元格K3录入公式“=K3*C7”，单元格K6录入公式“=K5*B7”，单元格K7录入“=K4+K6”，单元格K8录入“=K4C8+K6B8”。

录入订单安排计算表

输入：A1:C8录入表单，B4录入“=G4”，B5录入“=SUM(G5:G7)”，B6录入“=B4-B5”，选B4:B6后填至C4:C6，B7:C7录入“0.2、2”，B8录入“=B6/B7”，选B8填至C8，完成边际贡献表计算。

录入表单结构

操作步骤

添加规划求解功能

在菜单栏的【开发工具】选项卡【加载项】中单击【Excel加载项】，在弹出的【加载项】对话框中勾选【规划求解加载项】，点击确定后，菜单栏【数据】选项卡【分析】中会出现【规划求解】命令，单击该命令可设置规划求解条件，规划求解功能添加方法如图4-42所示。

操作步骤

订单安排计算表的设置。仔细阅读基础资料，根据资料要求，编辑订单安排计算表，为规划求解建立基本工作模型，设置方法如图4-43所示。

01

操作步骤

设置目标与约束条件。根据资料内容，选择单元格K8，设置目标为“最大值”，通过改变单元格K3、K5，也就是个人订单、团建订单数量，来完成贡献最大化的目标。

设置约束条件：订单应该是整数，所以单元格K3、K5应为整数；设置工作总天数不超过24000个工作日，公式设置为“K7=24000”；设置企业用于居民订单安装的工作天数不得少于商业订单安装的工作天数，公式设置为“K6=K4”；勾选“使无约束变量为非负数”，选择求解方法为“单纯线性规划”，点击求解，处理结果如图4-44所示。

设计最优生产表

从设计最优生产表开始，这是规划求解的基础步骤，确保数据的准确性和完整性。

计算边际贡献

通过计算边际贡献，分析不同生产安排的经济效益，为后续的优化提供依据。

添加规划求解功能

在Excel中添加规划求解功能，这是实现最优生产安排关键工具。

设置订单安排计算表

设置订单安排计算表，确保订单与生产计划匹配，优化资源分配。

设置目标与约束条件并求解

最后，设置目标与约束条件并求解，找到最优的生产或订单安排方案，实现边际贡献最大化，为企业的生产经营决策提供支持。

感谢观看

课程名称：《Excel在财务中的应用》

院校：遵义职业技术学院

主讲老师：王楠

Excel在本量利分析中的应用-规划求解与最优生产安排

项目四:Excel在成本管控中的应用