基于Excel的产品流向优化盈利分析模型.docx
《基于Excel的产品流向优化盈利分析模型.docx》由会员分享，可在线阅读，更多相关《基于Excel的产品流向优化盈利分析模型.docx（28页珍藏版）》请在冰豆网上搜索。
基于Excel的产品流向优化盈利分析模型
基于Excel的产品流向优化盈利分析
模型创新成果报告
销售部
2014年11月
基于Excel的产品流向优化盈利分析模型
创新成果报告
前言
宝钢集团新疆八一钢铁有限公司，目前产能达到1000万吨，产品结构调整接近完成，企业实现了质的飞跃。
八钢不但生产优质的建筑钢材、优特钢、金属制品，还生产高质量、高技术含量、高附加值的板带产品。
钢铁产品种类的丰富，产品结构的优化，增强了企业的竞争活力，拓展了企业的发展空间。
2014年疆内钢铁行业竞争激烈，钢铁“微利时代”已经形成，为确保公司目标的实现，开展产品流向优化盈利分析模型建设工作。
现阶段，产品与服务差异越来越小、顾客需求日趋多样化、市场竞争越来越激烈，企业保持长久竞争力的重要手段是实施按照效益优先、利润最大化原则优化八钢产品流向结构。
保持企业的资源优势；在为客户提供更大价值的同时提升企业的利润和价值。
本创新成果：
通过产品流向优化盈利分析模型的创立，西北市场钢材需求每年以较高的速度递增。
西北五省钢材市场主要是靠投资拉动，这期间的政府投资增长，必然使得钢材需求增长。
然而，在区内钢铁产量严重过剩的情况下，八钢公司考虑在西北地区销售部分钢材品种，销售品种结构、流向优化，并考虑重点战略用户、渠道维护，通过产品流向优化——DSP需求优化，测算产品效益最大化为原则。
帮助企业完成产品盈利经营分析，进行销售计划管理，并跟踪市场产品销售状况。
一、成果产生背景
2014年疆内钢铁行业竞争激烈，市场环境变化，钢铁“微利时代”已经形成，为确保公司目标的实现，促使销售部转变观念、创新营销、决战2014，销售部围绕着夯实营销基础工作开展产品流向优化分析模型建设工作。
集团审计部建议八钢公司建立产品流向优化盈利经营分析模型，通过开展产品盈利分析模型，收集和产品有关的销售量、制造成本、销售单价、运费等基础数据，上月盈利分析及次月产品流向规划，优化产品流向结构。
传统经营模式是以产品为竞争基础的，随着市场的变化和竞争的加剧，企业将关注的重点由产销率转向注重产品盈利流向分析。
产品流向优化盈利分析模型两部分：
数据归纳：
在上个月实际发生的数据进行整理、归纳，在进行初步分析、归纳、整理。
项目负责人收集和保存好原始记录和资料。
规划求解法：
整理各产品的（制造成本、销售单价、物流运费）数据，输入公式按照产品利润最大化原则，分配各产品资源流向工作。
二、成果的基本内涵
产品盈利预测模型是指：
基于EXCEL将公司生产经营成本和产品结构的各种假定通过各种关系转化对公司的产品结构调整和销售流向报表预测的一种方法。
基于产品盈利预测模型，通过计算公式下月的产品流向，并利用各种产品估值模型确定公司预算值。
产品盈利预测模型中收集和产品有关的钢材销售、制造成本、销售单价、运费、等基本的信息，帮助企业完成产品盈利经营分析，确定主要市场，进行销售计划管理，并跟踪市场产品销售状况。
2.1产品流向优化盈利分析模型建立的目的
通过开展产品流向优化盈利分析模型，收集和产品有关的销售量、制造成本、销售单价、运费等基础数据，上月盈利分析及次月产品流向调整，优化产品流向结构。
（1）按照效益优先、利润最大化原则优化八钢产品流向结构；
（2）制定产品盈利模型建立x要素，形成产品流向优化固化模型；
（3）预测下月产品流向，为八钢产品结构调整和销售流向提供有效支撑。
企业的任务不仅是向客户提供产品，还需要产品盈利分析经营模式，在实施产品盈利经营管理过程效果不仅能对公司下月计划有限制定，而且更能促进企业利润最大化原则体现。
三、成果的具体实践和主要做法
3.1产品流向优化盈利分析模型流程图
3.2模型的具体实践及主要做法
精细销售管理先从销售计划的制定与管理开始，如果计划中的销售目标与实际差异巨大，将直接影响到企业的生产和库存。
因此必须尽量准确地确定每个月的销售目标。
计划制定只是管理的开始，市场往往会以企业意想不到的变化来考验企业的管理能力。
如何调整资源流向计划的速度会成为企业成功的核心竞争能力。
要把这种能力转化成企业的知识管理，把营销人员的经验积累下来，降低企业运营的成本和风险。
3.2.1模型建立条件
为解决区外资源如何流向，最大满足疆内销售要求，疆外销售品种结构、流向必须要优化；并考虑重点战略用户、渠道维护，以产品效益最大化为原则。
通过建立规划求解模型求出区外产品流向效益最大值。
规划求解模型建立条件：
（1）区外产品各品种需求量
（2）制造成本、销售单价、运费等下月预测数据
（3）所有数据来源均以财务数据为准（全部是不含税计算）
假设：
（1）公司下月制造成本由财务预测提供，不受其他因素影响；
（2）区外运费为财务提供理论值，不受其他因素影响；
3.2.2基本Excel的SUMPROOUCT函数模型的规划求解
（1）选取2014年4月数据模型分析求解
1）为了预测下月区外不同品种的销售收益，首先将公司财务数据收集到Excel统计下来并计算各区域品种销售收益情况，通过作图发现各品种的销售收益呈现一定周期性关系，所以选用SUMPROOUCT函数，建立规划求解模型。
SUMPROOUCT函数基本功能：
a）函数SUMPRODUCT的功能返回相应的区域或数组乘积的和。
b）基本格式SUMPRODUCT（数据1，数据2，……，数据30）
c）示例数据表A列B列C列D列E列数据1数据2数据3数据4数据
区域计算要求：
计算A、B、C三列对应数据乘积的和——取最大值
公式：
=SUMPRODUCT（A2:
A6,B2:
B6,C2:
C6）计算方式：
=A2*B2*C2+A3*B3*C3+A4*B4*C4+A5*B5*C5+A6*B6*C6即三个区域A2:
A6,B2:
B6,C2:
C6同行数据积的和。
返回值可取（最大值、最小值、目标值）。
2）销售收益=销售毛利×品种吨位，据此建立了SUMPROOUCT的目标函数。
同时依据区外各区域的要求，可以得出对目标函数的约束条件可分为：
a）各产品的销售单价、制造成本、运费的约束
b）各产品在各区域的最大需求量
c）各产品的销售毛利在各区域需求的特定比例约束
d）流向区外的总资源量的约束
然后根据这4种约束条件可得出各个约束式，因此，已知目标函数与约束条件，再通过SUMPROOUCT函数规划求解求出最优解，最终得出区外产品销售的最大收益。
（2）求解过程
1）销售价格单位：
元/吨
品种
西北公司
宝钢公司
西安分公司
宝钢西部公司
宝钢华中公司
建材
2737
0
0
0
优钢
3250
0
0
3498
热轧
2794
3405
3280
3094
冷轧
3462
3675
0
3419
酸洗卷
0
3376
3376
3291
中厚板
3025
3045
3248
3051
2）制造成本单位：
元/吨
品种
西北公司
宝钢公司
西安分公司
宝钢西部公司
宝钢华中公司
建材
2801
2801
2801
2801
优钢
3124
3124
3124
3124
热轧
2786
2786
2786
2786
冷轧
3690
3690
3690
3690
酸洗卷
3083
3083
3083
3083
中厚板
2904
2904
2904
2904
3）销售运费单位：
元/吨
品种
西北公司
宝钢公司
西安分公司
宝钢西部公司
宝钢华中公司
建材
285
475
545
313
优钢
285
475
545
313
热轧
285
475
545
313
冷轧
285
475
545
313
酸洗卷
285
475
545
313
中厚板
285
475
545
313
4）“销售毛利”=销售单价-制造成本-运费单位：
元/吨
品种
西北公司
宝钢公司
西安分公司
宝钢西部公司
宝钢华中公司
建材
-349
优钢
-159
61
热轧
-277
144
-51
-5
冷轧
-513
-490
-584
酸洗卷
-182
-252
-105
中厚板
-164
-334
-201
-166
（3）区外各钢种约束条件
西北销量
6.87
＞=
6.87
西北中厚板销量
1.74
＜=
1.74
宝钢华中热轧销量
0.40
＜=
0.4
西安公司中厚板销量
0.19
＜=
0.5
宝钢西部销量
3.82
＞=
3.82
宝钢西部建材销量
0.00
=
0
宝钢华中冷轧销量
0.00
=
0
宝钢华中销量
0.80
＞=
0.8
宝钢西部优钢销量
0.00
=
0
宝钢华中酸洗销量
0.35
＜=
0.35
西安公司
4.12
＞=
4.12
宝钢西部热轧销量
2.70
＞=
1.8
宝钢华中中厚板销量
0.05
＜=
0.05
西北建材销量
2.00
＜=
2
宝钢西部冷轧销量
0.00
＜=
0.7
西安公司建材销量
0.00
=
0
西北优钢销量
1.09
＜=
1.09
宝钢西部酸洗销量
1.12
＞=
1.12
西安公司优钢销量
2.90
＜=
2.9
西北热轧销量
2.00
＜=
2
宝钢西部中厚板销量
0.00
＜=
0.2
西安公司热轧销量
1.00
＜=
1
西北冷轧销量
0.04
＜=
0.04
宝钢华中建材销量
0.00
=
0
西安公司冷轧销量
0.00
＜=
0.3
西北酸洗销量
0.00
=
0
宝钢华中优钢销量
0.00
=
0
西安公司酸洗销量
0.03
＜=
0.03
（4）区外资源通过约束条件规划求解分配资源流向，是区外品种销售获得最大收益。
求解得出区外各区域流向量单位：
万吨
品种
区外合计
西北公司
宝钢公司
西安分公司
宝钢西部公司
宝钢华中公司
合计
15.61
6.87
3.82
0.8
4.12
建材
2
2
0
0.00
0
优钢
3.99
1.09
0
0.00
2.9
热轧
6.1
2
2.7
0.40
1
冷轧
0.04
0.04
0
0.00
0
酸洗卷
1.5
0
1.12
0.35
0.03
中厚板
1.98
1.74
0
0.05
0.19
（5）规划求解演算过程
◆运算结果报告
MicrosoftExcel14.0运算结果报告
工作表:
[2014年销售分析排产、流向建议模型4.10.xls]4月区外分大品种
报告的建立:
2014-11-0518:
47:
18
结果:
规划求解找到一解，可满足所有的约束及最优状况。
规划求解引擎
引擎:
单纯线性规划
求解时间:
.062秒。
迭代次数:
27子问题:
0
规划求解选项
最大时间100秒,迭代100,Precision0.000001
最大子问题数目无限制,最大整数解数目无限制,整数允许误差5%,无整数约束的求解,假设为非负数
目标单元格（最大值）
单元格
名称
初值
终值
$A$55
西北酸洗销量
-1526
-1526
可变单元格
单元格
名称
初值
终值
整数
$C$61
建材西北公司
2
2
约束
$D$61
建材宝钢西部公司
0
0
约束
$E$61
建材宝钢华中公司
0.00
0.00
约束
$F$61
建材西安分公司
0
0
约束
$C$62
优钢西北公司
1.09
1.09
约束
$D$62
优钢宝钢西部公司
0
0
约束
$E$62
优钢宝钢华中公司
0.00
0.00
约束
$F$62
优钢西安分公司
2.9
2.9
约束
$C$63
热轧西北公司
2
2
约束
$D$63
热轧宝钢西部公司
2.7
2.7
约束
$E$63
热轧宝钢华中公司
0.40
0.40
约束
$F$63
热轧西安分公司
1
1
约束
$C$64
冷轧西北公司
0.04
0.04
约束
$D$64
冷轧宝钢西部公司
0
0
约束
$E$64
冷轧宝钢华中公司
0.00
0.00
约束
$F$64
冷轧西安分公司
0
0
约束
$C$65
酸洗卷西北公司
0
0
约束
$D$65
酸洗卷宝钢西部公司
1.12
1.12
约束
$E$65
酸洗卷宝钢华中公司
0.35
0.35
约束
$F$65
酸洗卷西安分公司
0.03
0.03
约束
$C$66
中厚板西北公司
1.74
1.74
约束
$D$66
中厚板宝钢西部公司
0
0
约束
$E$66
中厚板宝钢华中公司
0.05
0.05
约束
$F$66
中厚板西安分公司
0.19
0.19
约束
约束
单元格
名称
单元格值
公式
状态
型数值
$N$44
西安公司中厚板销量西安分公司
0.19
$N$44<=$P$44
未到限制值
0.31
$J$50
西安公司热轧销量西北公司
1.00
$J$50<=$L$50
到达限制值
0
$J$51
西安公司冷轧销量西北公司
0.00
$J$51<=$L$51
未到限制值
0.3
$J$52
西安公司酸洗销量西北公司
0.03
$J$52<=$L$52
到达限制值
0
$B$49
西北优钢销量区外合计
1.09
$B$49<=$D$49
到达限制值
0
$J$49
西安公司优钢销量西北公司
2.90
$J$49<=$L$49
到达限制值
0
$B$51
西北冷轧销量区外合计
0.04
$B$51<=$D$51
到达限制值
0
$B$48
西北建材销量区外合计
2.00
$B$48<=$D$48
到达限制值
0
$F$48
宝钢西部冷轧销量西安分公司
0.00
$F$48<=$H$48
未到限制值
0.7
$F$50
宝钢西部中厚板销量西安分公司
0.00
$F$50<=$H$50
未到限制值
0.2
$B$50
西北热轧销量区外合计
2.00
$B$50<=$D$50
到达限制值
0
$J$48
西安公司建材销量西北公司
0.00
$J$48=$L$48
到达限制值
0
$J$46
宝钢华中酸洗销量西北公司
0.35
$J$46<=$L$46
到达限制值
0
$J$47
宝钢华中中厚板销量西北公司
0.05
$J$47<=$L$47
到达限制值
0
$J$45
宝钢华中冷轧销量西北公司
0.00
$J$45=$L$45
到达限制值
0
$J$44
宝钢华中热轧销量西北公司
0.40
$J$44<=$L$44
到达限制值
0
$F$51
宝钢华中建材销量西安分公司
0.00
$F$51=$H$51
到达限制值
0
$F$52
宝钢华中优钢销量西安分公司
0.00
$F$52=$H$52
到达限制值
0
$B$60
合计区外合计
15.61
$B$60<=15.61
到达限制值
0
$B$52
西北酸洗销量区外合计
0.00
$B$52=$D$52
到达限制值
0
$F$44
西北中厚板销量西安分公司
1.74
$F$44<=$H$44
到达限制值
0
$F$45
宝钢西部建材销量西安分公司
0.00
$F$45=$H$45
到达限制值
0
$F$46
宝钢西部优钢销量西安分公司
0.00
$F$46=$H$46
到达限制值
0
$F$47
宝钢西部热轧销量西安分公司
2.70
$F$47>=$H$47
未到限制值
0.90
$B$44
西北销量区外合计
6.87
$B$44>=$D$44
到达限制值
0.00
$B$45
宝钢西部销量区外合计
3.82
$B$45>=$D$45
到达限制值
0.00
$B$46
宝钢华中销量区外合计
0.80
$B$46>=$D$46
到达限制值
0.00
$B$47
西安公司区外合计
4.12
$B$47>=$D$47
到达限制值
0.00
$F$49
宝钢西部酸洗销量西安分公司
1.12
$F$49>=$H$49
到达限制值
0.00
◆敏感性报告
MicrosoftExcel14.0敏感性报告
工作表:
[2014年销售分析排产、流向建议模型4.10.xls]4月区外分大品种
报告的建立:
2014-11-0518:
47:
19
可变单元格
终
递减
目标式
允许的
允许的
单元格
名称
值
成本
系数
增量
减量
$C$61
建材西北公司
2
0
-348.8205128
1E+30
164.6410256
$D$61
建材宝钢西部公司
0
0
0
1E+30
1E+30
$E$61
建材宝钢华中公司
0
0
0
1E+30
1E+30
$F$61
建材西安分公司
0
0
0
1E+30
1E+30
$C$62
优钢西北公司
1.09
0
-159.0196032
1E+30
354.4419352
$D$62
优钢宝钢西部公司
0
0
0
1E+30
1E+30
$E$62
优钢宝钢华中公司
0
0
0
1E+30
1E+30
$F$62
优钢西安分公司
2.9
0
61.40791017
1E+30
227.1258589
$C$63
热轧西北公司
2
0
-276.8119658
1E+30
236.6495726
$D$63
热轧宝钢西部公司
2.7
0
143.5584046
1E+30
143.5584046
$E$63
热轧宝钢华中公司
0.4
0
-51.08547009
1E+30
200.8461538
$F$63
热轧西安分公司
1
0
-4.982905983
1E+30
160.7350427
$C$64
冷轧西北公司
0.04
0
-513.4615385
164.6410256
1E+30
$D$64
冷轧宝钢西部公司
0
-633.3447293
-489.7863248
633.3447293
1E+30
$E$64
冷轧宝钢华中公司
0
0
0
1E+30
1E+30
$F$64
冷轧西安分公司
0
-418.4786325
-584.1965812
418.4786325
1E+30
$C$65
酸洗卷西北公司
0
0
0
1E+30
1E+30
$D$65
酸洗卷宝钢西部公司
1.12
0
-181.9316239
325.4900285
1E+30
$E$65
酸洗卷宝钢华中公司
0.35
0
-251.9316239
50.79487179
1E+30
$F$65
酸洗卷西安分公司
0.03
0
-105.4017094
1E+30
60.31623932
$C$66
中厚板西北公司
1.74
0
-163.6536988
1E+30
349.8078397
$D$66
中厚板宝钢西部公司
0
-477.6866097
-334.1282051
477.6866097
1E+30
$E$66
中厚板宝钢华中公司
0.05
0
-201.1367521
1E+30
50.79487179
$F$66
中厚板西安分公司
0.19
0
-165.7179487
60.31623932
418.4786325
约束
终
阴影
约束
允许的
允许的
单元格
名称
值
价格
限制值
增量
减量
$N$44
西安公司中厚板销量西安分公司
0.19
0
0.5
1E+30
0.31
$J$50
西安公司热轧销量西北公司
1
160.7350427
1
0.19
0.31
$J$51
西安公司冷轧销量西北公司
0
0
0.3
1E+30
0.3
$J$52
西安公司酸洗销量西北公司
0.03
60.31623932
0.03
0.19
0.03
$B$49
西北优钢销量区外合计
1.09
354.4419352
1.09
0.04
0
$J$49
西安公司优钢销量西北公司
2.9
227.1258589
2.9
0.19
升级会员 