.. . .. . .
基于Excel的产品流向优化盈利分析
模型创新成果报告
销售部 2014年11月
基于Excel的产品流向优化盈利分析模型
创新成果报告
前言
S. . . . . ..
.. . .. . .
宝钢集团新疆八一钢铁，目前产能达到1000万吨，产品结构调整接近完成，企业实现了质的飞跃。八钢不但生产优质的建筑钢材、优特钢、金属制品，还生产高质量、高技术含量、高附加值的板带产品。钢铁产品种类的丰富，产品结构的优化，增强了企业的竞争活力，拓展了企业的发展空间。
2014年疆钢铁行业竞争激烈，钢铁“微利时代”已经形成，为确保公司目标的实现，开展产品流向优化盈利分析模型建设工作。现阶段，产品与服务差异越来越小、顾客需求日趋多样化、市场竞争越来越激烈，企业保持长久竞争力的重要手段是实施按照效益优先、利润最大化原则优化八钢产品流向结构。保持企业的资源优势；在为客户提供更大价值的同时提升企业的利润和价值。
本创新成果：通过产品流向优化盈利分析模型的创立，西北市场钢材需求每年以较高的速度递增。西北五省钢材市场主要是靠投资拉动，这期间的政府投资增长，必然使得钢材需求增长。然而，在区钢铁产量严重过剩的情况下，八钢公司考虑在西北地区销售部分钢材品种，销售品种结构、流向优化，并考虑重点战略用户、渠道维护，通过产品流向优化——DSP需求优化，测算产品效益最大化为原则。帮助企业完成产品盈利经营分析，进行销售计划管理，并跟踪市场产品销售状况。
一、成果产生背景
2014年疆钢铁行业竞争激烈，市场环境变化，钢铁“微利时代”已经形成，为确保公司目标的实现，促使销售部转变观念、创新营销、决战2014，销售部围绕着夯实营销基础工作开展产品流向优化分析模型建设工作。
集团审计部建议八钢公司建立产品流向优化盈利经营分析模型，通过开展产品盈利分析模型，收集和产品有关的销售量、制造成本、销售单价、运费等基础数据，上月盈利分析及次月产品流向规划，优化产品流向结构。
传统经营模式是以产品为竞争基础的，随着市场的变化和竞争的加剧，企业将关注的重点由产销率转向注重产品盈利流向分析。产品流向优化盈利分析模型两部分：数据归纳：在上个月实际发生的数据进行整理、归纳，在进行初步分析、归纳、整理。项目负责人收集和保存好原始记录和资料。规划求解法：整理各产品的（制造成本、销售单价、物流运费）数据，输入公式按照产品利润最大化原则，分配各产品资源流向工作。
二、成果的基本涵
S. . . . . ..
.. . .. . .
产品盈利预测模型是指：基于EXCEL将公司生产经营成本和产品结构的各种假定通过各种关系转化对公司的产品结构调整和销售流向报表预测的一种方法。基于产品盈利预测模型，通过计算公式下月的产品流向，并利用各种产品估值模型确定公司预算值。产品盈利预测模型中收集和产品有关的钢材销售、制造成本、销售单价、运费、等基本的信息，帮助企业完成产品盈利经营分析，确定主要市场，进行销售计划管理，并跟踪市场产品销售状况。 2.1产品流向优化盈利分析模型建立的目的
通过开展产品流向优化盈利分析模型，收集和产品有关的销售量、制造成本、销售单价、运费等基础数据，上月盈利分析及次月产品流向调整，优化产品流向结构。 （1）按照效益优先、利润最大化原则优化八钢产品流向结构； （2）制定产品盈利模型建立x要素，形成产品流向优化固化模型； （3） 预测下月产品流向，为八钢产品结构调整和销售流向提供有效支撑。 企业的任务不仅是向客户提供产品，还需要产品盈利分析经营模式，在实施产品盈利经营管理过程效果不仅能对公司下月计划有限制定，而且更能促进企业利润最大化原则体现。
三、成果的具体实践和主要做法
3.1产品流向优化盈利分析模型流程图
S. . . . . ..
.. . .. . .
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三列对应数据乘积的和——取最大值
S. . . . . ..
.. . .. . .
公式：=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 3250 2794 3462 0 3025 0 0 3405 3675 3376 3045 0 0 3280 0 3376 3248 0 3498 3094 3419 3291 3051 2）制造成本
品种 单位：元/吨
西北公司 宝钢公司 宝钢西部公司 宝钢华中公司 分公司 建材 优钢 热轧 冷轧 酸洗卷 中厚板 2801 3124 2786 3690 3083 2904 2801 3124 2786 3690 3083 2904 2801 3124 2786 3690 3083 2904 2801 3124 2786 3690 3083 2904 3）销售运费 单位：元/吨
品种 西北公司 宝钢公司 宝钢西部公司 宝钢华中公司 分公司 S. . . . . ..
基于Excel的产品流向优化盈利分析模型
