基于Excel的产品流向优化盈利分析模型

PAGEPAGE9基于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.2.2基本Excel的SUMPROOUCT函数模型的规划求解（1）选取2014年4月数据模型分析求解1）为了预测下月区外不同品种的销售收益，首先将公司财务数据收集到Excel统计下来并计算各区域品种销售收益情况，通过作图发现各品种的销售收益呈现一定周期性关系，所以选用SUMPROOUCT函数，建立规划求解模型。SUMPROOUCT函数基本功能：函数SUMPRODUCT的功能返回相应的区域或数组乘积的和。基本格式SUMPRODUCT（数据1，数据2，……，数据30）示例数据表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的目标函数。同时依据区外各区域的要求，可以得出对目标函数的约束条件可分为：各产品的销售单价、制造成本、运费的约束各产品在各区域的最大需求量各产品的销售毛利在各区域需求的特定比例约束流向区外的总资源量的约束然后根据这4种约束条件可得出各个约束式，因此，已知目标函数与约束条件，再通过SUMPROOUCT函数规划求解求出最优解，最终得出区外产品销售的最大收益。（2）求解过程1)销售价格单位：元/吨品种西北公司宝钢公司西安分公司宝钢西部公司宝钢华中公司建材2737000优钢3250003498热轧2794340532803094冷轧3462367503419酸洗卷0337633763291中厚板30253045324830512）制造成本单位：元/吨品种西北公司宝钢公司西安分公司宝钢西部公司宝钢华中公司建材2801280128012801优钢3124312431243124热轧2786278627862786冷轧3690369036903690酸洗卷3083308330833083中厚板29042904290429043）销售运费单位：元/吨品种西北公司宝钢公司西安分公司宝钢西部公司宝钢华中公司建材285475545313优钢285475545313热轧285475545313冷轧285475545313酸洗卷285475545313中厚板2854755453134）“销售毛利”=销售单价-制造成本-运费单位：元/吨品种西北公司宝钢公司西安分公司宝钢西部公司宝钢华中公司建材-349优钢-15961热轧-277144-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区外资源通过约束条件规划求解分配资源流向，是区外品种销售获得最大收益。求解得出区外各区域流向量单位：万吨品种区外合计西北公司宝钢公司西安分公司宝钢西部公司宝钢华中公司合计15.616.873.820.84.12建材2200.000优钢3.991.0900.002.9热轧6.122.70.401冷轧0.040.0400.000酸洗卷1.501.120.350.03中厚板1.981.7400.050.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建材西北公司22约束$D$61建材宝钢西部公司00约束$E$61建材宝钢华中公司0.000.00约束$F$61建材西安分公司00约束$C$62优钢西北公司1.091.09约束$D$62优钢宝钢西部公司00约束$E$62优钢宝钢华中公司0.000.00约束$F$62优钢西安分公司2.92.9约束$C$63热轧西北公司22约束$D$63热轧宝钢西部公司2.72.7约束$E$63热轧宝钢华中公司0.400.40约束$F$63热轧西安分公司11约束$C$64冷轧西北公司0.040.04约束$D$64冷轧宝钢西部公司00约束$E$64冷轧宝钢华中公司0.000.00约束$F$64冷轧西安分公司00约束$C$65酸洗卷西北公司00约束$D$65酸洗卷宝钢西部公司1.121.12约束$E$65酸洗卷宝钢华中公司0.350.35约束$F$65酸洗卷西安分公司0.030.03约束$C$66中厚板西北公司1.741.74约束$D$66中厚板宝钢西部公司00约束$E$66中厚板宝钢华中公司0.050.05约束$F$66中厚板西安分公司0.190.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建材西北公司20-348.82051281E+30164.6410256$D$61建材宝钢西部公司0001E+301E+30$E$61建材宝钢华中公司0001E+301E+30$F$61建材西安分公司0001E+301E+30$C$62优钢西北公司1.090-159.01960321E+30354.4419352$D$62优钢宝钢西部公司0001E+301E+30$E$62优钢宝钢华中公司0001E+301E+30$F$62优钢西安分公司2.9061.407910171E+30227.1258589$C$63热轧西北公司20-276.81196581E+30236.6495726$D$63热轧宝钢西部公司2.70143.55840461E+30143.5584046$E$63热轧宝钢华中公司0.40-51.085470091E+30200.8461538$F$63热轧西安分公司10-4.9829059831E+30160.7350427$C$64冷轧西北公司0.040-513.4615385164.64102561E+30$D$64冷轧宝钢西部公司0-633.3447293-489.7863248633.34472931E+30$E$64冷轧宝钢华中公司0001E+301E+30$F$64冷轧西安分公司0-418.4786325-584.1965812418.47863251E+30$C$65酸洗卷西北公司0001E+301E+30$D$65酸洗卷宝钢西部公司1.120-181.9316239325.49002851E+30$E$65酸洗卷宝钢华中公司0.350-251.931623950.794871791E+30$F$65酸洗卷西安分公司0.030-105.40170941E+3060.31623932$C$66中厚板西北公司1.740-163.65369881E+30349.8078397$D$66中厚板宝钢西部公司0-477.6866097-334.1282051477.68660971E+30$E$66中厚板宝钢华中公司0.050-201.13675211E+3050.79487179$F$66中厚板西安分公司0.190-165.717948760.31623932418.4786325约束终阴影约束允许的允许的单元格名称值价格限制值增量减量$N$44西安公司中厚板销量西安分公司0.1900.51E+300.31$J$50西安公司热轧销量西北公司1160.735042710.190.31$J$51西安公司冷轧销量西北公司000.31E+300.3$J$52西安公司酸洗销量西北公司0.0360.316239320.030.190.03$B$49西北优钢销量区外合计1.09354.44193521.090.040$J$49西安公司优钢销量西北公司2.9227.12585892.90.190.31$B$51西北冷轧销量区外合计0.0400.041E+30-3.46945E-17$B$48西北建材销量区外合计2164.641025620.040$F$48宝钢西部冷轧销量西安分公司000.71E+300.7$F$50宝钢西部中厚板销量西安分公司000.21E+300.2$B$50西北热轧销量区外合计2236.649572620.040$J$48西安公司建材销量西北公司0165.717948700.190$J$46宝钢华中酸洗销量西北公司0.3500.351E+300$J$47宝钢华中中厚板销量西北公司0.0550.794871790.050.350$J$45宝钢华中冷轧销量西北公司0251.931623900.350$J$44宝钢华中热轧销量西北公司0.4200.84615380.40.350$F$51宝钢华中建材销量西安分公司0251.931623900.350$F$52宝钢华中优钢销量西安分公司0251.931623900.350$B$60合计区外合计15.61143.558404615.611E+300$B$52西北酸洗销量区外合计0513.461538500.040$F$44西北中厚板销量西安分公司1.74349.80783971.740.040$F$45宝钢西部建材销量西安分公司0-143.558404600.90$F$46宝钢西部优钢销量西安分公司0-143.558404600.90$F$47宝钢西部热轧销量西安分公司2.701.80.91E+30$B$44西北销量区外合计6.87-657.0199436.8700.04$B$45宝钢西部销量区外合计3.8203.82-2.05391E-151E+30$B$46宝钢华中销量区外合计0.8-395.49002850.800.35$B$47西安公司区外合计4.12-309.27635334.1200.19$F$49宝钢西部酸洗销量西安分公司1.12-325.49002851.120.91.12四、实施效果4.1典型案例分析建材结构优化——案例分析2014年钢材销售由产品销售向产品经营转变，钢材品种经营从销售单价、制造成本、单位运费3个方面为基础按照产品盈利模型优化产品结构。上半年建材结构调整通过2014年上半年度各主要产品盈利对比，建材产品中，线材盈利水平低于螺纹钢，加大螺纹钢产量及销量，特别是小螺纹、大螺纹价格高150元/吨，上半年大小规格螺纹钢生产比例由计划33%调整到41%。2014年计划大小规格螺纹钢生产比例较2013年42%调整到44%。名称2012年所占螺纹钢比例2013年所占螺纹钢比例2014年总量所占螺纹钢比例上半年实际螺纹钢224256322小螺纹6529%8132%10633%34.3%大螺纹198%2510%4011%10.8%大小螺纹合计8437%10742%14644%45.1%能够合理的降低八钢库存，减少存货的仓储及物流成本。改变以往区域库房布库销售的传统模式，提高资源的周转率，优化流向。淡季低库存运行，旺季结合产品结构、盈利水平控制适当的库存。在保证建材产品整体低库存的情况下，提高各区域点库库存周转率，减少调拨布库，已达到资源流向的均衡性。五、主要创新成效及体会5.1经济效益产品流向优化盈利模型应用于公司各销售部门的产品流向资源优化进一步提高销售额、增加产品盈利能力，进而提高销售收入。降低销售成本。对产品具体甄别和区域分类进行分析，使产品推销和销售计划的制定与执行避免了盲目性资源配置。效益：7193.6万元（依据见计算公式）销售毛利=“销售单价”-“销售成本”-“运费”产品盈利额=“销售毛利”×吨位公式：效益=∑产品销售毛利（使用该模型）×吨位（优化流向销售量）成果经济效益情况表单位：万元经济效益销售毛利=“销售单价”-“销售成本”-“运费”月产品盈利额=“销售毛利”×吨位公式：效益=∑产品销售毛利（使用该管理系统）×吨位（优化流向销售量）月份2月3月4月5月6月7月8月9月10月小计预计销售收益-2906.1-592.1-1648.7252.7-59.1-3618.8213.2-403.1-3923.5-12685.5流向优化实际销售收益-2401.8-152.6665.9423.3-46.9-1311.9941.1265.2-3874.2-5491.9流向优化差收益额（实际收益-预计收益）504.3439.52314.6170.612.22306.9727.9668.349.37193.620