《基于某Excel的产品流向优化盈利分析报告模型.doc》由会员分享，可在线阅读，更多相关《基于某Excel的产品流向优化盈利分析报告模型.doc（17页珍藏版）》请在课桌文档上搜索。
1、word基于Excel的产品流向优化盈利分析模型创新成果报告基于Excel的产品流向优化盈利分析模型创新成果报告前言宝钢集团某某八一钢铁，目前产能达到1000万吨，产品结构调整接近完成，企业实现了质的飞跃。八钢不但生产优质的建筑钢材、优特钢、金属制品，还生产高质量、高技术含量、高附加值的板带产品。钢铁产品种类的丰富，产品结构的优化，增强了企业的竞争活力，拓展了企业的开展空间。2014年疆钢铁行业竞争激烈，钢铁“微利时代已经形成，为确保公司目标的实现，开展产品流向优化盈利分析模型建设工作。现阶段，产品与服务差异越来越小、顾客需求日趋多样化、市场竞争越来越激烈，企业保持长久竞争力的重要手段是实施按
2、照效益优先、利润最大化原如此优化八钢产品流向结构。保持企业的资源优势；在为客户提供更大价值的同时提升企业的利润和价值。 本创新成果：通过产品流向优化盈利分析模型的创立，西北市场钢材需求每年以较高的速度递增。西北五省钢材市场主要是靠投资拉动，这期间的政府投资增长，必然使得钢材需求增长。然而，在区钢铁产量严重过剩的情况下，八钢公司考虑在西北地区销售局部钢材品种，销售品种结构、流向优化，并考虑重点战略用户、渠道维护，通过产品流向优化DSP需求优化，测算产品效益最大化为原如此。帮助企业完成产品盈利经营分析，进展销售计划管理，并跟踪市场产品销售状况。一、成果产生背景2014年疆钢铁行业竞争激烈，市场环境
3、变化，钢铁“微利时代已经形成，为确保公司目标的实现，促使销售部转变观念、创新营销、决战2014，销售部围绕着夯实营销根底工作开展产品流向优化分析模型建设工作。集团审计部建议八钢公司建立产品流向优化盈利经营分析模型，通过开展产品盈利分析模型，收集和产品有关的销售量、制造本钱、销售单价、运费等根底数据，上月盈利分析与次月产品流向规划，优化产品流向结构。传统经营模式是以产品为竞争根底的，随着市场的变化和竞争的加剧，企业将关注的重点由产销率转向注重产品盈利流向分析。产品流向优化盈利分析模型两局部：数据归纳：在上个月实际发生的数据进展整理、归纳，在进展初步分析、归纳、整理。项目负责人收集和保存好原始记录
4、和资料。规划求解法：整理各产品的制造本钱、销售单价、物流运费数据，输入公式按照产品利润最大化原如此，分配各产品资源流向工作。二、成果的根本涵产品盈利预测模型是指：基于EXCEL将公司生产经营本钱和产品结构的各种假定通过各种关系转化对公司的产品结构调整和销售流向报表预测的一种方法。基于产品盈利预测模型，通过计算公式下月的产品流向，并利用各种产品估值模型确定公司预算值。产品盈利预测模型中收集和产品有关的钢材销售、制造本钱、销售单价、运费、等根本的信息，帮助企业完成产品盈利经营分析，确定主要市场，进展销售计划管理，并跟踪市场产品销售状况。产品流向优化盈利分析模型建立的目的通过开展产品流向优化盈利分析
5、模型，收集和产品有关的销售量、制造本钱、销售单价、运费等根底数据，上月盈利分析与次月产品流向调整，优化产品流向结构。1按照效益优先、利润最大化原如此优化八钢产品流向结构； 2制定产品盈利模型建立x要素，形成产品流向优化固化模型；3 预测下月产品流向，为八钢产品结构调整和销售流向提供有效支撑。企业的任务不仅是向客户提供产品，还需要产品盈利分析经营模式，在实施产品盈利经营管理过程效果不仅能对公司下月计划有限制定，而且更能促进企业利润最大化原如此表现。三、成果的具体实践和主要做法3.1产品流向优化盈利分析模型流程图3.2模型的具体实践与主要做法精细销售管理先从销售计划的制定与管理开始，如果计划中的销
6、售目标与实际差异巨大，将直接影响到企业的生产和库存。因此必须尽量准确地确定每个月的销售目标。计划制定只是管理的开始，市场往往会以企业意想不到的变化来考验企业的管理能力。如何调整资源流向计划的速度会成为企业成功的核心竞争能力。要把这种能力转化成企业的知识管理，把营销人员的经验积累下来，降低企业运营的本钱和风险。 为解决区外资源如何流向，最大满足疆销售要求，疆外销售品种结构、流向必须要优化；并考虑重点战略用户、渠道维护，以产品效益最大化为原如此。通过建立规划求解模型求出区外产品流向效益最大值。 规划求解模型建立条件：（1） 区外产品各品种需求量（2） 制造本钱、销售单价、运费等下月预测数据（3）
7、所有数据来源均以财务数据为准全部是不含税计算假设：1公司下月制造本钱由财务预测提供，不受其他因素影响；2区外运费为财务提供理论值，不受其他因素影响；的SUMPROOUCT 函数模型的规划求解1选取2014年4月数据模型分析求解1为了预测下月区外不同品种的销售收益，首先将公司财务数据收集到Excel统计下来并计算各区域品种销售收益情况，通过作图发现各品种的销售收益呈现一定周期性关系，所以选用SUMPROOUCT 函数，建立规划求解模型。SUMPROOUCT 函数根本功能：a) 函数SUMPRODUCT的功能返回相应的区域或数组乘积的和。b) 根本格式SUMPRODUCT数据1，数据2，数据30c
8、) 示例数据表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)
9、 各产品的销售毛利在各区域需求的特定比例约束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制造本钱单位：元/吨品种西北公司宝钢公司分公司宝钢西部公司宝
10、钢华中公司建材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“销售毛利=销售单价-制造本
11、钱-运费 单位：元/吨品种西北公司宝钢公司分公司宝钢西部公司宝钢华中公司建材-349 优钢-159 61 热轧-277 144 -51 -5 冷轧-513 -490 -584 酸洗卷-182 -252 -105 中厚板-164 -334 -201 -166 9 / 173区外各钢种约束条件西北销量6.87 =西北中厚板销量1.74 =宝钢华中热轧销量0.40 =公司中厚板销量0.19 =宝钢西部销量3.82 =宝钢西部建材销量0.00 =0宝钢华中冷轧销量0.00 =0宝钢华中销量0.80 =宝钢西部优钢销量0.00 =0宝钢华中酸洗销量0.35 =公司4.12 =宝钢西部热轧销量2.70 =
12、宝钢华中中厚板销量0.05 =西北建材销量2.00 =2宝钢西部冷轧销量0.00 =公司建材销量0.00 =0西北优钢销量1.09 =宝钢西部酸洗销量1.12 =公司优钢销量2.90 =西北热轧销量2.00 =2宝钢西部中厚板销量0.00 =公司热轧销量1.00 =1西北冷轧销量0.04 =宝钢华中建材销量0.00 =0公司冷轧销量0.00 =西北酸洗销量0.00 =0宝钢华中优钢销量0.00 =0公司酸洗销量0.03 =（4） 区外资源通过约束条件规划求解分配资源流向，是区外品种销售获得最大收益。求解得出区外各区域流向量 单位：万吨品种区外合计西北公司宝钢公司分公司宝钢西部公司宝钢华中公司合
13、计建材2200.00 0优钢00.00 热轧20.40 1冷轧00.00 0酸洗卷00.35 中厚板00.05 5规划求解演算过程u 运算结果报告Microsoft Excel 14.0 运算结果报告工作表: 2014年销售分析排产、流向建议模型4.10.xls4月区外分大品种报告的建立: 2014-11-05 18:47:18结果: 规划求解找到一解，可满足所有的约束与最优状况。规划求解引擎引擎: 单纯线性规划求解时间: .062 秒。迭代次数: 27 子问题: 0规划求解选项最大子问题数目 无限制, 最大整数解数目 无限制, 整数允许误差 5%, 无整数约束的求解, 假设为非负数目标单元格
14、 (最大值)单元格名称初值终值$A$55西北酸洗销量-1526 -1526 可变单元格单元格名称初值终值整数$C$61建材 西北公司22约束$D$61建材 宝钢西部公司00约束$E$61建材 宝钢华中公司0.00 0.00 约束$F$61建材 分公司00约束$C$62优钢 西北公司约束$D$62优钢 宝钢西部公司00约束$E$62优钢 宝钢华中公司0.00 0.00 约束$F$62优钢 分公司约束$C$63热轧 西北公司22约束$D$63热轧 宝钢西部公司约束$E$63热轧 宝钢华中公司0.40 0.40 约束$F$63热轧 分公司11约束$C$64冷轧 西北公司约束$D$64冷轧 宝钢西部公
15、司00约束$E$64冷轧 宝钢华中公司0.00 0.00 约束$F$64冷轧 分公司00约束$C$65酸洗卷 西北公司00约束$D$65酸洗卷 宝钢西部公司约束$E$65酸洗卷 宝钢华中公司0.35 0.35 约束$F$65酸洗卷 分公司约束$C$66中厚板 西北公司约束$D$66中厚板 宝钢西部公司00约束$E$66中厚板 宝钢华中公司0.05 0.05 约束$F$66中厚板 分公司约束约束单元格名称单元格值公式状态型数值$N$44公司中厚板销量 分公司0.19 $N$44=$P$44未到限制值$J$50公司热轧销量 西北公司1.00 $J$50=$L$50到达限制值0$J$51公司冷轧销量
16、 西北公司0.00 $J$51=$L$51未到限制值$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未到限制值$F$50宝钢西部中厚板销量 分公司0.00 $F$50=$H$50未到限制值$B$50西北
17、热轧销量 区外合计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$
18、52到达限制值0$B$60合计 区外合计到达限制值0$B$52西北酸洗销量 区外合计0.00 $B$52=$D$52到达限制值0$F$44西北中厚板销量 分公司1.74 $F$44=$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$4
19、9到达限制值0.00 u 敏感性报告Microsoft Excel 14.0 敏感性报告工作表: 2014年销售分析排产、流向建议模型4.10.xls4月区外分大品种报告的建立: 2014-11-05 18:47:19可变单元格终递减目标式允许的允许的单元格名称值本钱系数增量减量$C$61建材 西北公司201E+30$D$61建材 宝钢西部公司0001E+301E+30$E$61建材 宝钢华中公司0001E+301E+30$F$61建材 分公司0001E+301E+30$C$62优钢 西北公司01E+30$D$62优钢 宝钢西部公司0001E+301E+30$E$62优钢 宝钢华中公司0001
20、E+301E+30$F$62优钢 分公司01E+30$C$63热轧 西北公司201E+30$D$63热轧 宝钢西部公司01E+30$E$63热轧 宝钢华中公司01E+30$F$63热轧 分公司101E+30$C$64冷轧 西北公司01E+30$D$64冷轧 宝钢西部公司01E+30$E$64冷轧 宝钢华中公司0001E+301E+30$F$64冷轧 分公司01E+30$C$65酸洗卷 西北公司0001E+301E+30$D$65酸洗卷 宝钢西部公司01E+30$E$65酸洗卷 宝钢华中公司01E+30$F$65酸洗卷 分公司01E+30$C$66中厚板 西北公司01E+30$D$66中厚板 宝
21、钢西部公司01E+30$E$66中厚板 宝钢华中公司01E+30$F$66中厚板 分公司0约束终阴影约束允许的允许的单元格名称值价格限制值增量减量$N$44公司中厚板销量 分公司01E+30$J$50公司热轧销量 西北公司11$J$51公司冷轧销量 西北公司001E+30$J$52公司酸洗销量 西北公司$B$49西北优钢销量 区外合计0$J$49公司优钢销量 西北公司$B$51西北冷轧销量 区外合计01E+30$B$48西北建材销量 区外合计220$F$48宝钢西部冷轧销量 分公司001E+30$F$50宝钢西部中厚板销量 分公司001E+30$B$50西北热轧销量 区外合计220$J$48公
22、司建材销量 西北公司000$J$46宝钢华中酸洗销量 西北公司01E+300$J$47宝钢华中中厚板销量 西北公司0$J$45宝钢华中冷轧销量 西北公司000$J$44宝钢华中热轧销量 西北公司0$F$51宝钢华中建材销量 分公司000$F$52宝钢华中优钢销量 分公司000$B$60合计 区外合计1E+300$B$52西北酸洗销量 区外合计000$F$44西北中厚板销量 分公司0$F$45宝钢西部建材销量 分公司000$F$46宝钢西部优钢销量 分公司000$F$47宝钢西部热轧销量 分公司01E+30$B$44西北销量 区外合计0$B$45宝钢西部销量 区外合计01E+30$B$46宝钢华
23、中销量 区外合计0$B$47公司 区外合计0$F$49宝钢西部酸洗销量 分公司四、实施效果4.1典型案例分析 建材结构优化案例分析2014年钢材销售由产品销售向产品经营转变，钢材品种经营从销售单价、制造本钱、单位运费3个方面为根底按照产品盈利模型优化产品结构。 上半年建材结构调整通过2014年上半年度各主要产品盈利比照，建材产品中，线材盈利水平低于螺纹钢，加大螺纹钢产量与销量，特别是小螺纹、大螺纹价格高150元/吨，上半年大小规格螺纹钢生产比例由计划33%调整到41%。2014年计划大小规格螺纹钢生产比例较2013年42%调整到44%。名称2012年所占螺纹钢比例2013年所占螺纹钢比例201
24、4年总量所占螺纹钢比例上半年实际螺纹钢224 256 322小螺纹65 29%81 32%10633%34.3%大螺纹19 8%25 10%4011%10.8%大小螺纹合计84 37%107 42%14644%45.1%能够合理的降低八钢库存，减少存货的仓储与物流本钱。改变以往区域库房布库销售的传统模式，提高资源的周转率，优化流向。淡季低库存运行，旺季结合产品结构、盈利水平控制适当的库存。在保证建材产品整体低库存的情况下，提高各区域点库库存周转率，减少调拨布库，已达到资源流向的均衡性。五、主要创新成效与体会5.1经济效益产品流向优化盈利模型应用于公司各销售部门的产品流向资源优化（1） 进一步提
25、高销售额、增加产品盈利能力，进而提高销售收入。（2） 降低销售本钱。对产品具体甄别和区域分类进展分析，使产品推销和销售计划的制定与执行防止了盲目性资源配置。效益：7万元依据见计算公式销售毛利=“销售单价-“销售本钱-“运费产品盈利额=“销售毛利吨位公式：效益=产品销售毛利使用该模型吨位优化流向销售量 成果经济效益情况表 单位：万元经济效益销售毛利=“销售单价-“销售本钱-“运费月产品盈利额=“销售毛利吨位公式：效益=产品销售毛利使用该管理系统吨位优化流向销售量月份2月3月4月5月6月7月8月9月10月小计预计销售收益-2906.1 -592.1 -1648.7 252.7 -59.1 -361
26、8.8 213.2 -403.1 -3923.5 -12685.5 流向优化实际销售收益-2401.8 -152.6 665.9 423.3 -46.9 -1311.9 941.1 265.2 -3874.2 -5491.9 流向优化差收益额实际收益-预计收益504.3 439.5 2314.6 170.6 12.2 2306.9 727.9 668.3 49.3 7193.6 2014年2-10月通过该测算模型量化工具最大满足疆销售要求，疆外销售品种结构、流向优化，以产品效益最大化为原如此，并考虑重点战略用户、渠道维护，累计销售收益7万元5.2 为产品销售提供营销改良措施实现由产品销售到产品
27、盈利经营分析的转变，深度挖掘利润空间。a) 建材：通过2014年各主要产品盈利比照，建材产品中，适当增加西北区域资源量；制度创新，逐步实现建材产品低库存运行，管理创新，逐步建立建材产品低库存运行模式，盘活现有库存产品，降低物流本钱，进一步降低钢厂库存；b) 优钢：1、线材类品种钢与优钢发往各个区域市场比拟效益中焊丝、焊条、硬线相比HPB300线材销往乌鲁木齐、南疆、北疆较低；焊丝、硬线相比HPB300线材销往略低；目前线材类优钢产品比拟效益较差，关注疆、西北区域价风格整，择机考虑增加线材类产品的销售量；2、棒材类品种钢与优钢发往各个区域市场比拟效益中，合结钢齿轮钢相比HRB400E棒材销往、乌
28、鲁木齐、北疆、南疆较低，其它棒材类品种钢与优钢好于当期HRB400E棒材在南、北疆、乌鲁木齐、区域市场销售；C) 热轧卷板：充分发挥产销研小组优势，从掌握用户需求、用途到用户加工制造工艺，量身定制生产工艺方案，提升热卷产品质量。 通过电商平台拓展用户，积极寻找新的客户群体，与时了解用户需求信息，加快库存周转。 D)冷区产品：通过产销研团队，进一步提高冷区产品的板形质量和厚度、精度，缩小与竞争对手差距，提高产品市场占有率；e)中厚板：按照用户合同套数，下放资金和合同跟踪、管理工作；11月份销售计划5.3万吨生产量5.5万吨，降低库存产品；后期加大容器钢市场调研，完成容器钢的认证和试制工作，适应疆容器钢市场增长需求，将容器钢打造为中厚板产品新的增长点；5.3 充分发挥模型应用的价值产品与服务差异越来越小、顾客需求日趋多样化、市场竞争越来越激烈，企业保持长久竞争力的重要手段是实施按照效益优先、利润最大化原如此优化八钢产品流向结构。保持企业的资源优势；在为客户提供更大价值的同时提升企业的利润和价值。加产品盈利分析和相关人员培训，后期开发和应用DSP系统，对用户需求进展归纳分析、处理与综合管理能力，对产品需求进展准确诊断，实现产品价值最大化，使之成为销售风险评估和决策的依据。
