三年前在网上看到的资料，某牛人用excel做的MRP运算逻辑，不得不佩服一下。

附件在25楼

[ 本帖最后由 urinator 于 2007-11-23 16:28 编辑 ]

继续

电子表格应用于MRPII实例的注解

注：本工具据原书所注是摘自《信息管理教育研讨会论文集》。原来接触K/3系统的MRPII部分，仅能从表面来了解MRP的原理和系统的运行结果。好多资料上都有关于MPS/MRP运算的逻辑表，但没有很强的理论基础，是很难搞清楚表内及表间运算逻辑，一直困惑。通过该工具的学习，对MPS/MRP的运算逻辑又了一个更深层次的理解，对学习K/3系统的MRPII部分很有帮助，但还是不能融会贯通，得心应手，希望和大家一块交流学习。学习时，先通过后面得专业术语 了解各简写得含义，再通过Execl 公式研究 运算逻辑。

基本资料
参见 ： 表1、表2、表3

    设有两个产品A和B，A由C和D构成，B由E和C构成，如图1所示。表1显示这两个产品的材料表内容。与MRP计算有关的材料主文件数据项包括前置时间、安全存量、安全时间、最低阶码（LLC）等，如表2所示。
本工具以Excel写成，全部皆为电子表格，并有很清晰的逻辑结构。表中的数字是任意决定的。BOM中的单位用量（Q-P），材料主文件中的前置时间、安全时间、和安全存量都可以修改，库存文件中的所有数字也可以修改。这些资料一经修改。本文所有的MPS及MRP表格立即跟着改变。


以电子表格设计MPS和MRP
   
   独立需求是与其他项目无关的需求，依赖需求可以从其他项目的需求算出来。在本文的例子中，A和B是独立需求项目，其他材料则是依赖需求项目。主生产排程是一个分期间订购点发（TPOP）的程序，用来计算独立需求项目的未来需求；材料需求规划是一个类似的程序，用来计算依赖需求项目的未来需求。MRP的总需求（GR）来自上阶材料的展开；TPOP的总需求则取自独立需求来源如客户定单及销售预测。

分期间订购点

TPOP程序涉及的资料包括GR、SR、POH、PAB、NR、PORC、POR。产品A的TPOP表如表4所示。总需求由CO和FCS构成，如表5所示。

在需求时栅（DTF）前的总需求量为实际的客户定单，在需求时栅和计划时栅（PIF）间的总需求为客户定单和销售预测的较大者，在计划时栅之后的总需求量则只包括销售预测。

▲GR总需求量公式如下：
GR（t）=CO（t），for  0≦t≦DTF；
GR（t）=MAX（CO（t），FCS（t））， for  DTF≦t≦PTF；
GR（t）=FCS（t），for  PTF≦t≦T.     （1）
以上公式中T 是计划期间，GR（t）、CO（t）和FCS（t）分别是第t期的总需求、客户定单和销售预测。目前的时间是第1期， 出现在第0期的数量为逾期未完成的累计数量。因此，GO（0）是逾期定单、SR（0）是逾期在途量。

▲POH预计在库量的公式如下：
POH（1）=OH+Max（SR（0），0）+SR（1）-AL-Max（GR（0），0）-GR（1），for t=1；
POH（t）=PAB（t-1）+SR（t）-GR（t），for 2≦t≦T.    （2）
以上公式中，POH（t）是第t期末的预计在库量，SR（t）是第t期的在途量，亦即预收料量。

▲        NR净需求公式如下：
  当POH（t）小于安全库存量SS时，就会有净需求。
     NR（t）=IF（POH（t）≧SS，0，SS-POH（t）），for 1≦t≦T     （3）
     
▲        PORC计划定单收料
  若批量法采用逐批法（LFL），则PORC等于NR，如公式所示：
     PORC（t）=NR（t）             （4）
▲        POR计划定单发出
计划定单发出（FOR）是经过前置时间（LT）及安全时间（ST）调整后的PORC。安全时间使定单提早发出前置时间不变，亦即，定单交期比实际所需早。所有的逾期计划定单发出都累加在POR（0）中。假设最长的前置时间为3，最长的安全时间为1，计划定单发出的公式如下：
POR（O）=OFFSET（PORC（0），0，LS+ST）
        +OFFSET（PORC（0），0，MAX（LT+ST-1，0））
        +OFFSET（PORC（0），0，MAX（LT+ST-2，0））
        +OFFSET（PORC（0），0，MAX（LT+ST-3，0））
POR（t）=OFFSET（PORC（t），0，LT+ST），for 1≦t≦T     （5）

▲        PAB预计可用量
预计可用量是PORC（t）都如期达成下的预计库存量，是预计在库量（不考虑PORC）加上前一期的预计可用量，公式如下：
PAB（t）=POH（t）+PORC（t），for 1≦t≦T     （6）

●        讨论 批量法则LSR
     假设有三种批量法则： 逐批法（LFL）、固定批量法（FOQ）、定期批量法（POQ）。
  固定批量法：FOQ使批量为一个固定的数量，或当净需求超过该数量时，为其倍数。设Q为固定批量，PORC（t）的公式如下：
PORC（t）=IF（NR（t）>0,Q*(INT((NR(t)-1)/Q)+1,0）,for  1≦t≦T  (7)

  定期批量法:POQ使批量等于一个固定期数净需求的和.设P为固定期数,PORC(t)的公式如下:
PORC(t)=IF(NR(t)>0,IF(P>1,NR(t)+SUM(GR(t+1):OFFSET(GR(t+1,0,MAX(P-2,0))),NR(t),0) for  1≦t≦T         (8)

从公式(7)和(8)知,当Q=1时,FOQ就变成了LFL;当P=1,POQ也成了LFL.这就是表2中的批量法则写成FOQ/LFL及POQ/LFL的原因.



主生产排程
在主生产排程功能中有两个重要的信息:MPS和ATP.MPS和ATP是生产计划员和销售人员经常用到的 资料.MPS是产成品的计划定单收料和在途量的总和.计划定单收料是系统建议补存但尚未发单的料.在途量则是已发出定单的料,公式如下:
MPS(t)=SR(t)+PORC(t), for 1≦t≦T.                   (9)

ATP是库存量和计划量之中尚未承诺客户的量,销售员可以根据ATP决定新的客户定单交期和数量.在表6中,MPS_period为1表示当期MPS大于零.acc_co是累计客户定单,这两个字段和insufficient(不足)是用来计算ATP的中间资料.ATP的算法如下:

MPS_period(t)=IF(MPS(t)=0,0,1).  for 1≦t≦T;     (10)
acc_co(T)=CO(T);                                  (11)
acc_co(t)=CO(t)+acc_co(t+1)*ABS(mps_period(t+1)
          -1)+ insufficient(t+1), for 1≦t≦T;    (12)
acc_co(1)=CO(0)+CO(1)+acc_co(2)*ABS(mps_period(2)
          -1)+ insufficient(2);                   (13)
insufficient(1)=IF(mps_period(1)=0,0,max(acc_co(1)-MPS(0)-MPS(1)-OH,0))(14)
insufficient(t)=IF(mps_period(t)=0,0,max(acc_co(t)-MPS(t),0))for1≦t≦T(15)
ATP(1)=OH+MPS(0)+MPS(1)-acc_co(1);                 (16)
ATP(t)=IF(mps_period=0,“”,IF(insufficient(t) ≦0,MPS(t)-acc_co(t),0)),
       For   2≦t≦T                                    (17)

表7为产品A的MPS报表.用相同的方法,从表5的资料,导出产品B的TPOP表        、MPS/ATP计算、及MPS报表如表8、９、10所示。


物料需求规划
依赖需求项目的总需求（GR）来自其父件的计划定单发出（POR）。完成品的需求计划由MPS处理，完成品以下的项目则由MRP处理。MRP程序与TPOP类似，其不同处在MRP的总需求来自其他材料。材料主文件中的最低阶码（LLC）决定MRP计算的顺序，LLC较小的项目先处理。计划定单的计算程序与前述TPOP相同，BOM根据上阶零件的计划定单发出算出下阶零件的需求并加入总需求之中。MRP从LCC为1的项目开始计算。MRP程序的算法如下：
PROCEDURE MRP ；
BOM explosion for POR（t）  of  MPS items and add to GR（t） of components；
LLC ← 1；
WHILE there exist any item not processed  DO
   WHILE there exist any item with current LLC not processed DO
       PAB（0）← OH－AL；{SR（0）＞0 has been re_balanced}
       FOR  t=1 TO T  DO
          IF t=1 THEN  POH（1）←PAB（0）+SR（1）-GR（1）- max（GR（0），0）
               ELSE POH（t）←PAB（t-1）+SR（t）-GR（t）；
          IF POH（t）＜SS
               THEN  NR（t）←SS-POH（t）；
                     PORC（t）← Lotsizing（POH（t），NR（t），GR（t））；
               ELSE NR（t）← 0；
                    PORC（t）←0；
          ENDIF；
          PAB（t）←POH（t）+PORC（t）；
          POR（t-LT）← PORC（t）；
          BOM explosion for POR（t-LT）and add to GR（t） of components；
      ENDFOR；
      Print MRP report for current item；
   ENDWHILE；
   LLC←LLC+1；
ENDWHILE；
ENDPROCENDURE.

在本例子中，LLC最小的项目是D，MRP从D开始计算，如表11所示，接着依序是C、D、F如表12、13、14所示。

相关专用术语
MRP   Material Requirement Planning （材料需求规划（计划））：
材料需求规划利用材料表（BOM）将主生产排程中的完成品需求转换为半成品及原材料需求。它利用库存状态如在库及在途量等，以及材料主文件中的材料基本资料，如前置时间及安全存量等，以及厂历，计算出何时需要多少何种材料。

MRPII  Manufacturing Resource Planning （制造资源计划）：
把生产、财务、销售、工程技术、采购等各子系统结合成一个一体化的系统。英文缩写还是MRP，为了区别于基本MRP而记为MRPII。

MPS    Master Production Scheduling （主生产排程）：
主生产排程时完成品的计划表，描述一个特定的完成品的生产时间和生产数量。主生产排程是一个决定完成品生产排程及可应答量（ATP）的程序。依据主生产排程，材料需求规划得以计算在该完成品的需求之下，所有组件、零件以至原材料的补存计划。主生产排程不是销售预测，不代表需求。主生产排程须考虑生产规划、预测、待交定单、关键材料、关键产量、及管理目标和政策。除了材料外，MPS也时其他制造资源的规划基础。

TPOP    Time Phased Order Point     （分期间订购点法）：
适用用独立需求的计划逻辑，类似MRP。其总需求（GR）来自预测而不是BOM的展开。TPOP可用来计划配销中心的库存或服务零件的需求计划。它与再定购点法（ROP）不同的地方时考虑到未来变动需求。

BOM    Bill Of Material     （材料表）：
材料表记录一个项目所用到的所有下阶材料及相关属性。亦即，父件及其所有子件的从属关系、单位用量及其他属性。

QP     Quantity-per  （单位用量）：
某材料用来生产一单位父件时所需要的数量。单位用量时BOM的一个属性，MRP利用它来计算下阶材料的总需求（GR）。

LT      Lead Time    （前置时间）：
     进行一个作业所需要的时间。对ERP而言，从确认订购需求到取得材料或产品的时间，包括准备定单、签核、通知、制作、运输、收货、检验等时间。

ST    Safety Time     （安全时间）：
安全时间是将MRP逻辑中的计划定单收料（PORC）及计划定单发出（POR）同时提前一个时间值，用来保障在实际前置时间发生变动时，仍能在需要日期前完成定单。对供货商而言，前置时间一样，单定单交期则较实际需要早些。安全时间的目的时为了减少供货商迟交对生产所造成的影响。

SS    Safety Time     （安全存量）：
为了应突发的需求或供给的波动，在库存中保持一个额外的库存量时必要的。这种为因应不确定因素而建立的库存称为“安全库存”。

LLC    Low-Level Code （最低阶码）：
产品结构中，最上阶的项目的阶次码定位0，其下依序为1、2，…阶。一个材料项目可能出现在二个以上的阶次中，以该材料在产品结构中出现的最低阶次码定位其最低阶码。

LSR    Lot Sizing Rule     （批量法则）
决定批量的程序及规则。

LS    Lot Size      （批量）：
某项目的量，向工厂或供应商订购，或发料到现场，都时根据这个量。

FOQ    Fixed Order Quantity （定量批量法）：
若某期间出现净需求，则此种批量法会将计划定单的定单量定为某一依经验事先决定的量或其倍数。

POQ    Periodic Order Quantity    （定期批量法）：
此种批量法则由PRS演绎而来，批量为数个时段中的净需求，但时段的数目由EOQ决定。换言之，这是一个由EOQ决定订购周期的定期评估法。

LFL    Lot For Lot     （逐批批量法）：
此批量方法所产生的计划定单量与每一期的净需求相等。在MRP的逻辑中，若采用LFL批量法则其计划定单的发出量会与净需求相等。

USC

CC
OH    On-Hand Inventory    （在库量）：
查询库存或执行MRP的那个时刻，正在仓库中的库存量。

AL    Allocated Inventory     （保留量）：
保留量是用来表示已被指定用于某张已发出的制令单、外包单或调拨单，预定从仓库领出单实际尚未领出的数量。当生产计划员发出制令单及领料单时，MRP会将该制令单的组件数量，排在预定完工的时段内，变成该组件的在途量（SR），同时，将每一个被该组件用到的零件数量记录为“保留量”。当零件被零用时，会同时减少其在库量和保留量。保留量不是可用库存，不能用于其他用途上。

GR    Gross Requirement    （总需求）：
在MRP中，材料的独立需求及依赖需求的总合称为总需求。

SR（OO）    Scheduled Receipts ，On-Order Inventory  （在途量）：
排定在未来某一时间将会取得的量，又称为已开定单或已订未交量，是一种未来的库存，在交期的那一期，视为可用量。
   
POH    Projected On-Hand     （预计在库量）
     某期（时段）若无计划定单收料（PORC），则期末预计的在库量称为预计在库量。MRP利用POH来决定在某期是否有净需求（NR）。

PAB    Projected Available Balance     （预计可用量）：
预计可用量时考虑计划定单收料（PORC）的各时段之期末库存量，亦即，预计在库量（POH）加上计划定单收料量（PORC）等于预计可用量（PAB）。

NR    Net Requirement    （净需求）：
将一时段的总需求（GR）扣掉在库量（OH）、在途量（SR）及安全存量（SS），便得
到净需求（NR）。更精确地说，在MRP逻辑中，若预计在库量（POH）小于SS，其差额即为NR。
PORC    Planned Order Receipts     （计划定单收料）：
计划定单收料（PORC）是指在未来各时段（期）中预计会收到的材料数量。这个量在定单发出前时计划定单收料（PORC）；定单发出后是在途量（SR）。净需求（NR）经批量法则及安全时间（ST）调整后就成为计划定单收料（PORC）。

POR    Planned Order Releases     （计划定单发出）：
计划定单收料（PORC）向前推移一个前置时间即得计划定单发出（POR）。父件的计划定单发出（POR）会被材料表展开，算出其所有子件的总需求（GR）。当计划定单发出时，该计划定单收料（PORC）变成了在途量（SR），计划定单发出（POR）不再存在，也不再对其子件产生总需求（GR）。

CO    Customer Order    （客户定单）：
来自客户的定单，告知对某些产品的需求量及时间。客户定单又称实际需求以有别于预测需求。


FCS          Forecast Sales    （销售预测）：
对未来需求的估计。预测可以根据经验主观估计，也可以用数学方法由历史资料求出，或两种方法并用。

DTF    Demand Time Fence  （需求时栅）：
需求时栅（DTF）是在MPS计划期间中的一个时间点，设定于目前日期与计划时栅（PTF）之间。在目前日期到DTF之间，包含确认了的客户定单。在此期间内，除了经过仔细分析和上级核准修改外，MPS时不能改变的。

TTF    Planning Time Fence    （计划时栅）：
计划时栅介于DTF和计划期间的最后日期之间。在DTF和PTF间包含了实际以及预测的定货，而在PTF之后则只有预测之客户定单。在DTF与PTF之间，实际客户定单逐渐取代预测数量。

ATP  Available-To-Promise    （可应答量）：
可应答量（ATP）是公司的库存及计划生产量中未被承诺的部分。ATP通常显示在MPS报表中，以支持业务员让他们能合理地向客户承诺定单交期。可应答量在MPS报表中出现在第一期以及所有有MPS的期间。在第一期中，ATP等于在库量加上MPS（如果是正数的）减已到期和已逾期之客户定单量。在第一期之后的任何有MPS数量的期间，ATP等于MPS减该期到下一个有MPS计划数量期间之前一期的所有客户定单总和。某期间算出的ATP若为负数，则业务员已超量承诺定单，此时，应从前一个ATP减去不足之数而使该期的ATP调为0。

相关函数学习
IF
执行真假值判断，根据逻辑测试的真假值，返回不同的结果。
可以使用函数 IF 对数值和公式进行条件检测。

语法 1
IF(logical_test,value_if_true,value_if_false)
Logical_test   计算结果为 TRUE 或 FALSE 的任何数值或表达式。
Value_if_true   Logical_test 为 TRUE 时函数的返回值。如果 logical_test 为 TRUE 并且省略 value_if_true，则返回 TRUE。
Value_if_true 可以为某一个公式。
Value_if_false   Logical_test 为 FALSE 时函数的返回值。如果 logical_test 为 FALSE 并且省略 value_if_false，则返回 FALSE。Value_if_false 可以为某一个公式。

说明
函数 IF 可以嵌套七层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。请参阅下面最后一个示例。
在计算参数 value_if_true 和 value_if_false 后，函数 IF 返回相应语句执行后的返回值。
如果函数 IF 的参数包含数组，则在执行 IF 语句时，数组中的每一个元素都将计算。如果某些 value_if_true 和 value_if_false 参数为操作提取函数，则执行所有的操作。

示例
在下面的示例中，如果单元格 A10 中的数值为 100，则 logical_test 为 TRUE，且区域 B5:B15 中的所有数值将被计算。反之，logical_test 为 FALSE，且包含函数 IF 的单元格显示为空白。
IF(A10=100,SUM(B5:B15),""
假设有一张费用开支工作表，B2:B4 中有一月、二月和三月的“实际费用”，其数值分别为 1,500、500 和 500。C2:C4 是相同期间内的“预算经费”，数值分别为 900、900和925。
可以通过公式来检测某一月份是否出现预算超支，下列的公式将产生有关的信息文字串：
IF(B2>C2,"超过预算","OK" 等于 "超过预算"
IF(B3>C3,"超过预算","OK" 等于 "OK"
如果要给以 AverageScore 为名称所引用的数字设置字母级别，请参阅下表：
如果 AverageScore 为        则返回
大于 89        A
80 到 89        B
70 到 79        C
60 到 69        D
小于 60        F
可以使用下列嵌套 IF 函数：
IF(AverageScore>89,"A",IF(AverageScore>79,"B",
IF(AverageScore>69,"C",IF(AverageScore>59,"D","F")))

在上例中，第二个 IF 语句同时也是第一个 IF 语句的参数 value_if_false。同样，第三个 IF 语句是第二个 IF 语句的参数 value_if_false。例如，如果第一个 logical_test（Average > 89）为 TRUE，则返回“A”；如果第一个 logical_test 为 FALSE，则计算第二个 IF 语句，以此类推。

INT

返回实数舍入后的整数值。

语法
INT(number)
Number   需要进行取整处理的实数。

示例
INT(8.9) 等于 8
INT(-8.9) 等于 -9
下面公式将返回 A1 单元格中的一个正实数的小数部分:
A1-INT(A1)


OFFSET

以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。

语法
OFFSET(reference,rows, cols,height,width)

Reference   作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE！。
Rows   相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。
Cols   相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。
如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值 #REF！。
Height   高度，即所要返回的引用区域的行数。Height 必须为正数。
Width   宽度，即所要返回的引用区域的列数。Width 必须为正数。
如果省略 height 或 width，则假设其高度或宽度与 reference 相同。

说明
函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数 OFFSET 可用于任何需要将引用作为参数的函数。例如，公式 SUM(OFFSET(C2,1,2,3,1)) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。


示例
OFFSET(C3,2,3,1,1) 等于 F5，如果在工作表中输入此公式，Microsoft Excel 显示单元格 F5 中的数值。
OFFSET(C3:E5,-1,0,3,3) 等于 C2:E4
OFFSET(C3:E5,0,-3,3,3) 等于 #REF!


ABS
返回参数的绝对值，参数绝对值是参数去掉正负号后的数值。

语法
ABS(number)
Number   需要计算其绝对值的实数。

示例
ABS(2) 等于 2
ABS(-2) 等于 2
如果 A1 中的数值为 -16，那么：
SQRT(ABS(A1)) 等于 4

MAX
返回数据集中的最大数值。

语法
MAX(number1,number2,...)
Number1,number2,...   为需要找出最大数值的 1 到 30 个数值。
?可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。
?如果参数为数组或引用，则只有数组或引用中的数字将被计算。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果逻辑值和文本不能忽略，请使用函数 MAXA 来代替。
?如果参数不包含数字，函数 MAX 返回 0。

示例
如果 A1:A5 包含数字 10、7、9、27 和 2，则：
MAX(A1:A5) 等于 27
MAX(A1:A5,30) 等于 30

牛，直观地从EXCEL表中学习运算逻辑

────────
我是老罗
我现在就付诸行动
E-mail：max656798@21cn.com

如果哪位兄弟需要相关资料，请加好评之后再与我联系,谢谢支持

那确实牛，对MRP理论有深刻理解

不知道这种算法有没有考虑一个时间因素

很厉害，我也准备用Excel做套进销存给朋友，值得参考