商品赊销决策模型.doc

商品赊销决策模型1、赊销商品最优信用期的确定分析：【参考模型4-1】赊销商品最优信用期的确定（注册会计专业学生梁宏斌提供）某贸易公司所出售的一种商品的固定成本为50000元，单位可变成本为4元，单价为5元。该公司目前采用30天信用期的赊销政策，年销售数量为100000件，收帐费用率为1%，坏帐损失率为2%。为了提高利润公司准备将该商品的赊销信用期进一步延长至60天（但仍将收帐费用率维持在原有水平上），估计在采用这种新的信用期后年销售数量将增至120000件而坏帐损失率将增至3%。公司的预期投资回报率为15%。试探讨该公司将信用期延长至60天这一措施是否是可以达到提高利润的目的。【解】首先对商品赊销的有关问题做一简单说明。公司采用赊销方式（即信用销售方式）销售商品时允许客户在不付现金的情况下先将商品取回然后在一段称为“信用期”的时间内支付货款（或者说归还欠款）。赊销方式的采用可以起到提高商品销售数量（因而提高销售收益）的作用，这显然是有利于提高公司利润的一种正面效果。但是，赊销方式同时还会引起四种负面的效果。第一，公司为了对不及时缴纳欠款的客户进行催帐必须支付一定数量的收帐费用；第二，一定数量的客户会不归还欠款从而造成坏帐。第三，一定数量的资金将会被积压从而使公司失去将这些资金用于其他方面的投资而原本可以获得的收益因而造成一种机会成本；第四，公司为了鼓励客户在信用期内归还欠款公司通常会规定在信用期内归还欠款的客户可以获得某种“折扣”优惠，这样就会给公司造成一种“折扣”成本。由此可见，为了判断一种赊销策略是否能够确实起到提高利润的作用，必须将正反两种效果综合起来考虑。图 1 在本例中我们作以下四点假定：第一，对于准时在信用期内归还欠款的客户只作精神上的鼓励而不给“折扣”优惠因此折扣成本等于零；第二，不存在过期归还欠款的情况，即，每个客户或者（经过催款后）在信用期到期时准时归还欠款或者永远不归还欠款而导致坏帐的发生，因此，由于欠款延期归还而积压的资金等于在信用期内发生的商品销售数量与其单位可变成本的乘积（在已知商品年销售数量的情况下，信用期内的商品销售数量等于其年销售数量除以一年内的天数再乘以信用期的天数） ，由这些被积压的资金所造成的机会成本等于商店的预期投资回报率与所积压的资金额的乘积；第三，坏帐损失与销售金额成比例而作为比例系数的坏帐损失率随信用期的加长而增大；第四，收帐费用与销售金额成比例而作为比例系数的收帐费用率是一个不随信用期长度而变化的常数。 在一个Excel工作表中的数据区内首先键入问题中的已知参数，然后建立一个对两种信用期的利润的计算模型（图 1）。在此模型各个单元格中输入的Excel公式是： （除了D17以外，D列中各个单元格中的数据均需拷贝到并列的E列单元格中去）。 图 1所示的模型在将延长信用期这一策略的正反效果综合加以考虑后求得的结果表明：将信用期从30天延长到60天可使公司利润从30068.49（元）提高到34164.68（元）， 因此这是一项值得采纳的积极措施。 但是，上述计算结果是在预期投资回报率与坏帐损失率、收帐费用率等参数取特定值的假设之下得到的，如果这些参数发生变化，结论完全可能改变。图 2显示了预期投资回报率的变化对60天与30天这两种信用期利润大小关系的影响，从中可以看出：在问题给定的坏帐损失率与收帐费用率数据不变的情况下，当预期投资回报率小于23.9%时将信用期从30天提高到60天将使公司利润增加，而当预期投资回报率大于23.9%时将信用期从30天提高到60天将使公司利润减少。图 3显示了60天信用期坏帐损失率的变化对60天与30天这两种信用期利润大小关系的影响，从中可以看出：在问题给定的预期投资回报率与收帐费用率数据不变的情况下，当60天坏帐损失率小于3.7%时将信用期从30天提高到60天将使公司利润增加，而当预期投资回报率大于3.7%时将信用期从30天提高到60天将使公司利润减少。图 2与图 3二图中两条曲线的交点可以分别将它们称为投资回报率盈亏平衡点与60天坏帐损失率盈亏平衡点。这两个盈亏平衡点的计算可参见附注1。 图 2 图 3 认真思考会发现一个问题：在适当的参数值下将信用期从30天提高到60天可以使公司利润增加，既然如此那么在这些参数值下不断加大信用期的长度是否会使公司利润不断提高？反之，在另一些参数值下将信用期从30天提高到60天会使公司利润减少，既然如此那么在这些参数值下将信用期从30天不断减小（一直到零）是否会使公司利润不断提高？ 经验告诉我们公司利润随信用期长度的变化不是线性的，因此上面所说的两种情况都不会发生。比较切合实际的情况是在问题中的其他参数保持不变的情况下，当信用期长度在一个范围内增大时，公司利润先增大后减小，在某个最优信用期长度下达到极大值。出现这一规律的关键是年销售数量与信用期长度之间的依赖关系的非线性性质和（或）坏帐损失率与信用期长度之间的依赖关系的非线性性质。 图 4 图 5 下面我们除了保持前面所作的四条假设之外再作两项补充：第一，在前面第三个假设的基础上进一步假定坏帐损失率 随信用期长度 t 增大服从一个线性规律 ： 第二，假定年销售数量Qt与信用期长度t之间存在着一种非线性的依赖关系： 其中Q30表示当信用期长度为30天时的销售数量，即Q30 = 100000。除了Q30之外( 2 )式中还包含着两个未知参数：A与k。它们可以利用两个条件来加以确定：1）当t = 60时Q = 1200000。2）我们补充假定无论怎样增大信用期的长度销售数量都不会超过某个值，例如200000，即 。容易证明，利用Q30 与 便可按照下式确定A与k这两个参数： 将这两个参数代入( 2 )式后得到的函数 的图形如图 4所示。图 6 在一个新工作表中按照图 5的形式构造一个模型，其中在E3:E12各个单元格中键入有关的已知参数，然后在D18:D20与E13:E21中各个单元格中键入下面的公式： 这样，在最后一个单元格E21中便可求得在信用期长度等于E12中的数值（60天）时公司的利润，即34164.38元。 图 7 图 8 在图 5所示的模型的基础上可以绘制一个如图 6所示的可调图形，从这个图形中可以清楚地看出：第一，在问题中各个参数固定不变的情况下，公司利润随信用期的变化曲线呈现为一个类似于抛物线的形状：随着信用期长度的增加公司利润首先增大然后减小并在中间某处达到极大。第二，当预期投资回报率变化时，公司利润随信用期变化的类似于抛物线形的曲线的位置将会改变。图中表示出了当预期投资回报率取不同数值时的一族曲线，当预期投资回报率逐步增大时曲线逐步向右上方移动（这可以从一条受微调器控制的红色当前曲线的移动看出），（被一条红色垂直参考直线突出显示的）利润曲线顶点描绘出一条向右上方延伸的直线（图中的绿色直线） 。当操作者利用图中的第二个微调器对信用期长度进行调节时，代表当前信用期长度的棕色垂直参考线将左右移动，指示出与当前信用期长度对应的公司利润点在红色曲线上的位置及其实际高度。 图 9 图 10 从图 6所示的图形可以看出，60天的信用期十分接近使公司利润达到极大的信用期长度（66）。这就是说，如果我们所假定的销售数量随信用期长度变化的（负指数）函数符合公司实际情况的话，那么将信用期从原来的30天提高到60天基本上实现了利润的极大化。如果将信用期长度进一步提高（例如增加到70天或者更长）将不但不能进一步提高公司利润反而会使公司利润下降（图 7）。但这是公司预期投资回报率等于15%时的情况。如果公司预期投资回报率等于10%的话，将信用期提高到60天虽能提高公司利润（图 8），但是如果希望实现最大利润的话则应将信用期提高到82天（图 9）。在公司预期投资回报率等于20%的情况下为了获得最大利润应该将信用期提高到52天而不是60天（图 10）。 如要了解图 6?图 10所示可调图形的绘制方法可参见附注2。 附注1：投资回报率盈亏平衡点与60天坏帐损失率盈亏平衡点的计算图 11 信用期为60天时的利润等于 其中 、 与 分别是信用期等于60天时的毛利、坏帐损失与收帐费用，r是期望投资报酬率，v是单位变动成本， 是信用期等于60天时的年销售数量。同样地，信用期为60天时的利润等于 其中各个符号具有类似的意义。因此，使60天信用期利润与30天信用期利润相等的投资回报率（投资回报率盈亏平衡点）就可以表示为 类似地，我们可以将信用期等于60天与30天时的利润重新表示为与 其中 、 与 分别是信用期等于60天时的坏帐损失率、销售收益与机会成本， 、 与 则分别是信用期等于30天时的坏帐损失率、销售收益与机会成本（其他符号与前面意义相同）。因此，在实现利润 与 条件时应有 所以使60天信用期利润与30天信用期利润相等的60天信用期坏帐损失率（即60天坏帐损失率盈亏平衡点）就等于 根据( 6 )与( 10 )两式，我们可以在图 1所示工作表模型的基础上在它们下方的两个范围E24:E26与E28:E30中实际完成对于两个盈亏平衡值的计算，利用所求出的盈亏平衡值在图 2与图 3中便可绘制出经过两条曲线交点的垂直参考直线。图 11显示了相应的工作表模型的外观，其中将图 1所示的模型部分重新显示出来（并且将在图 1中被隐藏着的两个单元格D18、E18与D21、E21也显示出来）。在各个单元格中键入的Excel公式是：附注2：图 6?图 10所示可调图形的绘制 在图 5所示的模型的基础上，在同一工作表的空白区域（例如N3:U84）中做一个E21中的利润相对于E6中的预期回报率与E12中的信用期长度的二维灵敏度分析（图 12），其中在水平方向让预期回报率从P3中的5%变到U3中的30%（步长等于5%），在O3中设置一个也让预期回报率取值的、受微调器控制的数值（将微调器设置成可使该值以5%的步长从5%变到25%）；在垂直方向让信用期长度从N4中的30增至N84中的110（步长等于1）。这样，利用所得到的位于P列至U列中的数据就可以绘制出可调图形中的灰色背景曲线，而利用位于O列中的数据就可以制成可调图形中受微调器控制的红色可动当前曲线。 图 12 在二维灵敏度分析数据区的下方范围O87:U88中生成两行数据，其中在O88:U88中的数据是二维灵敏度分析数据中各对应列中数据的最大值，O87:U87中的数据则是该最大值所对应的信用期长度值。利用这两行数据就可以生成可调图形中灰色背景曲线族中各条曲线最高点的（绿色）轨迹。在O87与O88中键入的Excel公式是（这些公式在键入后要拷贝到整个范围O87:U88中去）： 在制成了上述曲线图形后再做以下几项操作：第一，添加一条与O87中的数据（当前曲线最高点所对应的信用期长度）对应的参考直线；第二，添加一条与E12中的（当前信用期长度）对应的参考直线；第三，制作一个对O3中的数值（期望回报率）进行调节的微调器与一个对E12中的信用期长度值进行调节的微调器就构造好了所需要的可调图形。下载2、商品赊销中最优现金折扣与催款费用决策分析：【参考模型4-2】商品赊销中最优现金折扣与催款费用决策某公司采用赊销的方式销售一种产品。在不采取任何辅助措施的情况下，根据去年销售状况推测今年坏帐损失率将达到5%而平均还款期限则估计等于2.5月，在这一条件下销售收益可以达到12000元，而税前利润则估计等于11075元。公司考虑是否可以用催款（即向顾客发送催款信函）与向顾客提供现金折扣（即对以现金方式当场支付货款的顾客给予适当减价鼓励）这两种方法来提高利润。根据过去公司在其他类似商品销售中的经验，针对0%、2%与3%这三种现金折扣率和0与80这两种催款费用的坏帐损失率与平均还款期限（以月为单位）数据（在以“坏帐损失率表”与“平均还款期限表”的形式输入到一个Excel工作表中去后）如图 13所示（但是，由于存在着多种不确定的因素，所以虽然表中注明在现金折扣率等于3%而催款费用等于80元的情况下坏帐损失率等于2.3%，但它可能会在2.0%?2.5%范围内变化）。该公司的预期投资回报率等于13%。现在要确定：应采用怎样的现金折扣率和催款费用才能使公司的利润达到极大 。图 13 本问题参考了以下文献中的内容：马海清，朱光林编著，决策会计学，经济管理出版社，1997年1月，第365-367页。【解】在键入了上述坏帐损失率表与平均还款期限表的工作表中构造一个如图 14所示公司利润计算模型，其中上部是一些参数而下部是一个有关该产品销售状况的损益表，其中在单元格J6与J7中键入的Excel公式是：而在J10:J14各个单元格中键入的公式则是： 图 14对于J6与J7中的公式需要作一些解释。我们事先已将坏帐损失率表中的范围C5:E7与平均还款期限表中的范围C12:E14（见图 13）分别命名为“坏帐率表”与“还款期表”。这样，当我们在J3中输入0%、2%与3%这三个现金折扣率数值中的任何一个和在J4中输入0与80这两个催款期限值中的任何一个时，MATCH(J4,D4:E4)就会确定J4中的催款费用值在D4:E4中出现的序号（如果J4中输入的是0则序号为1，如果输入的是80则序号为2）；这样，在J6中键入的VLOOKUP()函数就会用J3中的现金折扣数值到“坏帐率表”中去找到相应列（第二列或第三列）中的适当的坏帐率值，同样地，在J7中的VLOOKUP()函数就会到“还款期表”中去找到适当的还款期值。 在确定了给定的现金折扣率与催帐费用数据后，在J10、J11、J12与J13中所键入的公式就计算出将从销售收益中扣除的催帐费用、坏帐损失（等于坏帐损失率与销售收益的乘积）、机会成本（等于还款期限中积压的销售收益与预期投资回报率的乘积）和由于为顾客提供现金折扣优惠而带来的损失（等于现金折扣率与销售收益的乘积），最后在J14中求得公司利润（税前利润留成）。在此基础上在一个空白范围（例如M2:O5）中作一个利润单元格J14相对于J3与J4的二维灵敏度分析（见图 14），便求得了在六种可能的现金折扣率与催款费用搭配下的利润。在此基础上经过在N7:O8范围中四个单元格中的中间计算最后在范围J16:J18中求得利润的极大值（11138元）与实现此极大利润的现金折扣率（2%）与催款费用（80元）。这里在有关单元格中键入的Excel公式是： 图 15 图 16 对于上述各个公式需要作一些必要的解释。第一，在N7、O7与O10中应用MAX()函数求得N3:O5中的利润值矩阵第一列的极大值、第二列的极大值和整个矩阵的极大值；第二，在N8与O8中应用MATCH()函数找到利润矩阵极大值在矩阵中所在的列序号与行序号；第三，在O11与O12中应用INDEX()函数（利用前一步骤找到的序号）找到实现利润极大值的现金折扣率值与催款费用值。这里要说明的是：为方便起见我们事先已经将范围M3:M5命名为“基列”而OFFSET(基列,0,N8)的意义是从“基列”向右偏移N8中找到的列序号，因此也就是利润极大值所在的列。最后在一个单元格（例如M21）中利用如下的公式得到一个本问题的自动答案： 图 17 在范围N3:O5中的二维灵敏度分析数据的基础上可以制作一个如图 15所示的图形，其中清楚地绘出了现金折扣率与催帐费用的六种搭配状况下的利润值，并且在一个文字筐中明确显示出关于最优现金折扣率、最优催款费用和最大利润的结论（这个文字筐与前面所介绍的、模型工作表中的单元格H17链接着，所以它会动态地给出自动的正确结论）。也可以绘制成如图 16与图 17所示的可调图形，操作者可以通过对于控制面板上的催帐费用的选择将六种搭配状况分成两组分别加以显示。 值得指出的是，在图 16与图 17所示的可调图形中最高的一个柱形会自动显示出与另外两个柱形不同的颜色（最高者为红色而其他两个为浅蓝色），实现这个目的的方法是准备两组数据，一组用来并列地显示设置为浅蓝色的三个柱形，另一组用来只显示高度最大的一个红色柱形，再在图中将二组柱形的重叠程度改为100%。图 14中的R3:R5与S3:S5就是所准备的两组数据。在R3:R5中键入的公式利用单选钮所控制的单元格R2从灵敏度分析数据N3:O5中选出适当的一列，在S3:S5中键入的公式则从R3:R5中选出一个最大值，再将另外两个值设置为零。在R3与S3中键入的Excel公式分别是： （这两个公式要分别拷贝到R3:R5与S3:S5中去）。 最后，考虑到问题中所指出的、在现金折扣率等于3%而催款费用等于80元的情况下坏帐损失率可能会在2.0%?2.5%范围内变化的事实，为了便于操作者对这一不确定性进行研究，我们可以将图 15中的图形改造成一个带有对图 13所示的工作表中单元格E7进行调节的微调器的可调图形，图 18与图 19显示了这个可调图形在将该坏帐损失率调到2.5%与2.0%这两个极端条件下时的情形，我们看到在这两个情况下虽然最优催帐费用都是80元但最优折扣率却分别是2%与3%，所实现的最大利润分别是11138元与11151元。 最后还有一点需要说明，从原则上说，对于以现金方式立即付款的顾客给予现金折扣虽然会带来一定的“现金折扣损失”，但是同时也会引起销售收益的增长，我们在前面图 14所示的模型中没有考虑销售收益的这种增长而认为单元格J9中的年销售收益是一个与J3中的现金折扣率无关的常数，是为简化模型而作的一个近似处理。如果将模型稍加改进使它能将现金折扣率对销售收益的影响考虑进去，则它将能够更好地代表实际情况。 图 18 图 19 下载3、 居民购房贷款最优还款计划分析：【参考模型4-3】居民购房贷款最优还款计划（注册会计专业学生葛茜提供）对该最优还款计划问题构造一个如图 20所示的模型框架，其中在D3:D4、D8:D9与D15中分别键入A、B两种贷款的贷款总额与年利率与银行存款年利率的数值，在D5与D10中分别键入代表A、B两种贷款还款年限的两个不超过8的正整数。最后在D6、D7中求得A方案贷款每月与每年的还款额，在D11、D12中求得B方案贷款每月与每年的还款额，在D13中求得居民的最大月还款金额（它等于D6与D11中求得的两种方案贷款月还款额之和），在D14中键入居民的可承受月还款额数字，在D16:D18中求得A、B两种贷款各年还款额的净现值与两种贷款年还款额的总净现值。有关的公式是：【解】首先对商品赊销的有关问题做一简单说明。公司采用赊销方式（即信用销售方式）销售商品时允许客户在不付现金的情况下先将商品取回然后在一段称为“信用期”的时间内支付货款（或者说归还欠款）。赊销方式的采用可以起到提高商品销售数量（因而提高销售收益）的作用，这显然是有利于提高公司利润的一种正面效果。但是，赊销方式同时还会引起四种负面的效果。第一，公司为了对不及时缴纳欠款的客户进行催帐必须支付一定数量的收帐费用；第二，一定数量的客户会不归还欠款从而造成坏帐。第三，一定数量的资金将会被积压从而使公司失去将这些资金用于其他方面的投资而原本可以获得的收益因而造成一种机会成本；第四，公司为了鼓励客户在信用期内归还欠款公司通常会规定在信用期内归还欠款的客户可以获得某种“折扣”优惠，这样就会给公司造成一种“折扣”成本。由此可见，为了判断一种赊销策略是否能够确实起到提高利润的作用，必须将正反两种效果综合起来考虑。图 20 图 21在完成了图 20所示的模型框架后，可以看出，表示两种贷款还款年限的两个单元格D5与D10应是问题中的可变单元格（决策变量），这两个单元格与表示月最大还款额的D13应是问题中的约束单元格（前两个单元格必须是介于1与8之间的正整数，后一单元格必须不超过D14中的居民可承受月还款额），而表示年还款额总净现值的单元格D18应是极小化的目标单元格（目标变量）。选择菜单命令“工具”?“规划求解”， 按照这个框架在“规划求解参数”对话框中完成如图 21所示的Solver模型设置 ， 图 20所显示的就是在Solver运行后获得的结果，它表明该居民的最优还款方案是：将A、B方案的还款年数分别定为6年与5年，这时每月最大还款额为3803元（未超过4000元），所实现的年还款额总净现值为226365元。很明显，这里所得到的结果与我们在模型2-11中求得的结果相同。 下载4、最优产品混合问题决策分析：【参考模型4-4】最优产品混合问题决策（注册会计专业学生高毅提供）某公司在生产A、B与C三种产品的过程中需要耗用分别由四个车间所提供的配件1、配件2、配件3与配件4等四种配件。这三种产品对于四种配件的耗用情况与四个车间对它们的生产能力分别如下表所示：由于公司生产安排与市场需求方面的原因，产品A的产量不得小于5000件而产品B的产量则不的大于5500。另外，需要考虑两种情况：一种情况是三种产品的单价都是固定的：它们分别10元/件、25元/件与18元/件；另一种情况是除产品A的单价仍然等于固定的10元/件之外，产品B与C的单价分别是它们各自产量（QB与QC的函数：pB=(3500-QB)/100；pC=(10000-QC)/500。试在以上条件下确定公司的最优生产安排。【解】在一个Excel工作表中按照图 22的形式构造一个模型，其中在范围C4:F8中的部分是一个财务模型，在G8中求得当三种产品的产量取D4:F4中的数值时的公司总利润。模型中的有关公式是：这里E7中的公式要拷贝到F7中，D8中的公式要拷贝到E8与F8中，G10中的公式要拷贝到整个范围G10:G13中。需要说明的是，在E7与F7中键入的公式是为了利用一个列表框让操作者在单价不变与单价可变这两种情况之间进行选择使用的，该列表框的输入范围与单元格链接被分别设置为K8:K9与M10。 本问题的可变单元格（决策变量）应是D4:F4中三种产品的产量，约束单元格应是这三个单元格G10:G13中的配件需要量，有待极大化的目标单元格则应是G8中的总利润。在“规划求解参数”对话框中完成的Solver模型设置显示在图 23中（注意，由于当我们选择单价可变的情况时，问题将会变成一个非线性规划问题，所以如果不准备在利用列表框对“单价固定”与“单价可变”进行选择时临时修改Solver设置的话，在上述模型设置最后一行中第五个参数必须规定不采用线性规划算法求解）。 图 22图 23在列表框中将需要研究的单价固定或单价可变选项（产品B与C的单价是否按问题的规定依赖于产品生产数量）选中后利用Solver求解一次便可求得对应情况下的最优生产安排。在两种情况下求得的三种产品的最优生产数量已记录在图 22所示工作表的范围E16:H17中。在单价固定时的最优生产安排是：产品A生产7800件，产品B生产5500件，产品C生产600件，这时实现的总利润是145400元；在单价按照问题的要求变化的请客下的最优生产安排是：产品A生产10183件，产品B生产1266.6件，产品C生产1916.5件，这时实现的总利润是81058.3元。图 22的模型框架中正显示出了在单价固定情况下的最优解。 下载5、最优季度生产供货方案确定分析：【参考模型4-5】最优季度生产供货方案确定（注册会计专业学生王春珍提供）某柴油机厂各季度按合同规定必须提供的柴油机台数、各季度生产能力与各季度柴油机的单台生产成本数据如下表所示。由于每个月的合同任务与生产成本都是不相等的，因此工厂完全可以恰当地安排各个月的实际生产数量（例如在成本较低的季度多生产一些以便用来满足成本较高的季度的合同需求。但是每台柴油机在仓库中保存一个季度需要支付储存费用0.15万元。试在保证完成合同需求的前提下确定一种使工厂全年生产与储存总费用最小的柴油机生产安排方案。【解】将问题中有关成本的数据在一个Excel工作表范围中输入（图 24），然后在同一工作表中按照图 25的形式建立一个线性规划模型的框架。其中在范围I4:L7中未加阴影的各个单元格用来输入为满足每个季度的销售需求而在各个季度安排的柴油机生产台数（目前暂时输入了数字1），其中在第i行第j列的单元格中的数字表示在第i个季度生产而保存到第j个季度销售的柴油机台数（显然只有在 时这样的生产数量才有实际意义，那些打了阴影的单元格正是因为不符合这一条件而不得使用）。在M4:M7中求出每个季度所生产的（满足该季度与该季度以后各个季度的合同需求的）全部柴油机数量，在I8:L8中求出每个季度所销售的（在该季度以前和该季度所生产的）全部柴油机数量。在N4:N8与I9:L9中分别输入了各个季度的生产能力和合同需求量。在下部的I12:L15中显示了在第i个季度生产而保存到第j个季度销售的每台柴油机的生产与储存成本，这些成本数据是根据图 24所示的范围D3:D7中的数据计算出来的。最后，在单元格N9中利用一个Excel数组公式求出了四个季度的总成本。在各个单元格中键入的公式是：（其中I8中的公式要拷贝到整个范围I8:L8中去，M4中的公式要拷贝到整个范围M4:M7中去）。 图 24很明显，范围I4:L7中未加阴影的各个单元格应是问题中的可变单元格（changing cell），这些单元格与范围M4:M7与I8:L8中的单元格应是问题中的约束（constraints，即约束条件左端表示式），而N9则应是问题的有待极小化的目标单元格（target cell）。在图 25所示模型框架的基础上在“规划求解参数”对话框中按照图 26所示的形式完成Solver模型的设置。 应用Solver找到的最优生产安排如图 27所示，即：第一季度利用其全部生产能力生产25台柴油机，其中满足本季度合同销售需求10台，留到第二季度销售10台，留到第三季度销售5台；第二季度（由于生产成本较高）只生产5台（虽然生产能力为35台），全部用来与第一季度留下来的10台一起满足本季度的销售需求；第三季度利用其全部生产能力生产30台，其中20台与第一季度留下来的5台一起用来满足本季度的销售需求，10台留到第四季度；第四季度按照其全部生产能力生产10台，用它们与第三季度留下来的10台一起满足该季度的销售需求。这一安排所实现的最小年度成本为773万元。图 25图 26图 27下载6、考虑到学习曲线效应的最优生产安排决策分析：【参考模型4-6】考虑到学习曲线效应的最优生产安排决策（注册会计专业学生王琰提供）在制造行业企业在生产新产品的过程中存在着明显的学习曲线效应。亦即，随着产品生产数量的增加生产全部产品的平均工时（与平均成本）将会按照一个负指数曲线的规律减少。某些产品或其零部件可以由不同生产部门来承担，而这些部门之间在对于特定产品生产过程的学习特性上会出现差别，如果这些部门本来就可能因为使用不同的（专用或通用）设备进行加工而存在成本上的差别，那么，学习特性上的差别将会再增添一个引起成本差别的因素，在必须生产一定数量的产品以满足市场需求的情况下这种成本差别与各个部门在生产能力与资源方面的限制就引起了如何在不同部门之间分配生产数量以便使总成本达到极小的问题，本问题所要研究的就是一个这样的最优生产数量安排问题。本问题在第一部分中先不考虑学习曲线效应而假定每个部门生产每种产品中的每一个所需要的工时是固定不变的，在第二部分中再假定每个部门生产每种产品中的每一个所需要的工时随着生产数量的增加依照一定的学习曲线效应而不断减少。 某公司的两个车间所能提供的最高工时（以小时为单位）数据与某月三种产品的需求量（以件为单位）数据分别为： 在问题的第一部分中假定两车间在三种产品的生产中的单件产品成本（以元为单位）与单件生产工时数据（以小时为单位）均是固定不变的，它们是：在问题的第二部分中假定公司对于三种产品都是第一次生产因此在生产工时上呈现出一种学习曲线效应，即：在刚开始生产时每个产品的生产工时比较高而随着生产数量的增长每个产品的生产工时将不断下降。公司每个人工小时成本为5元，三种产品生产中（两车间相同的）首件产品所需工时与两个车间对于这些产品的学习曲线特性参数以及（两车间相同的）三种产品单件材料成本数据为：试在两种情况下分别为公司确定该月的最优生产安排以便在完成当月所需生产数量的前提下使总成本达到极小。【解】第一部分。在一个Excel工作表中输入各个已知参数并建立一个如图 28所示的线性规划模型框架，其中D9:F10中各个单元格表示两个车间对于三种产品的生产数量，在D11:F11中求出三种产品的总生产数量，在D12:F12中则输入了三种产品的需求量。在G9:G10中求出两车间所需要的总工时，在H9:H10中则输入了两车间所能提供的最高工时。最后，在单元格H4中利用D9:F10中的生产数量数据与D4:F5中的成本数据计算出公司的总成本。在其中各个单元格中键入的公式是：（G9中的公式需要拷贝到G10中去，D11中的公式需要拷贝到E11与F11中去）。 图 28图 29在图 28所示的模型框架中，可变单元格或决策变量应是D9:F10中的六个单元格，这六个单元格与G9:G10和D11:F11中的各个单元格应是问题的约束单元格（即约束条件左端，对后两个范围中的单元格来说，相应的约束条件右端是H9:H10与D12:F12中的单元格），而问题中有待于极小化的目标单元格或目标变量则是H4。在此框架的基础上应通过“规划求解参数”对话框完成如图 29所示的Solver模型设置。 在以上设置下Solver帮助我们找到的最优解如图 30所示，它表明：车间A应利用所有可供使用的工时生产产品1与产品3的全部需要量（100件与300件）和产品2需要量中的20件，车间B则应利用1100工时生产产品2剩下的需要量220件（其余200工时可供完成其他生产任务使用），这一安排所实现的最小成本为59320元。 图 30 第二部分。首先需要说明的是：在本问题后面的附注中给出了对学习曲线效应的简单介绍。附注中的( 11 )式给出了在具有学习曲线效应的产品制造过程中生产前x个产品的总工时Tx与x之间的关系为 （其中t1为生产第一个产品的工时，k是一个介于0与1之间的常数）。除了参数k之外还可使用一个称为学习百分比的参数p来描述学习曲线效应，它表明当产品的生产数量翻番时每个产品的平均生产工时缩短的百分比。附注中的( 17 )式给出了学习百分比p与常数k之间的转换关系： 或 。 根据本问题给定的已知条件可知：车间B对于产品1的生产技术学习效果较好：在该产品生产数量翻番时生产该产品的平均工时下降率可达40%，同样地，车间A对于产品3的学习效果也比较好。反之，车间A对于产品2和车间B对于产品3的学习效果都很差，在生产数量翻了一番时平均生产工时只能缩短1%。 在一个新工作表中输入问题的各个已知参数根据，其中在范围D6:F7中输入两个部门对于三种产品的学习百分比数据，然后在范围D8:F9的各个单元格中求出相应的k系数。这里使用的Excel公式是（这一公式要拷贝到整个范围D8:F9中去）： 在上述学习曲线特性参数与其他参数的基础上我们可以在同一个Excel工作表中构造一个如图 32所示的模型，其中K4:M5中各个单元格表示两个车间对于三种产品的生产数量，在K6:M6中求得三种产品的总生产数量，在K7:M7中则输入了三种产品的需求量。在K9:M9中键入了三种产品中每种的第一个（所谓“首产品”）所需要的生产工时，在K10:M10中应用学习曲线效应公式求出了在K4:M4中的生产数量下车间A对于三种产品所需要的总工时，同样地，在K11:M11中求出了在K5:M5中的生产数量下车间B对于三种产品所需要的总工时。在N10与N11中则求出了两个车间分别需要的总工时，在O10与O11中则输入了两个车间所能提供的最大工时。最后，在单元格O4中利用N4:N5中的总工时数据、D3中的单位工时成本数据、K6:M6中的总生产数量数据与D12:F12中的单件材料成本数据计算出公司的总成本。这些单元格中的Excel公式是： （其中K6中的公式要拷贝到K6:M6中去，K10中的公式要拷贝到K10:M11中去，N10中的公式要拷贝到N10:N11中去）。 图 31 图 32 在如图 32所示的模型框架中， K4:M5中的六个单元格应是本问题中的可变单元格或决策变量，这六个单元格与K6:M6和N10:N11中的各个单元格应是本问题的约束单元格（即约束条件左端，对后两个范围中的单元格来说，相应的约束条件右端是K7:M7与O10:O11中的单元格），而O4则应是问题的目标单元格或目标变量。需要注意的是，在考虑了学习曲线效应的情况下目标变量显然成了决策变量的非线性函数，因此现在的问题是一个非线性规划模型。 图 33 图 34 图 35 在上述模型框架的基础上通过“规划求解参数”对话框所要完成的Solver设置如图 33所示。需要特别注意的是：由于目前所求解的问题是一个非线性规划模型问题（注意图 33所示设置最后一行中的第五个参数），因此在Solver求解的过程中会发生一些特别的情况。具体地说，如果从如图 32所示的状态（各个决策变量的初始值都等于1）开始运行Solver的话，那么Solver在运行结束时将会显示一个如图 34所示的“规划求解结果（Solver Result）”对话框，其中告诉我们“规划求解遇到目标或约束单元格中的错误值”，在单击该对话框的“确定”按钮而使它退去后可以看到模型工作表中所显示的内容的确出了毛病：在一个可变单元格（L4）中出现了负值而且在其他几个有关的单元格（L10、N10）与目标单元格（O4）中都显示了出错信息。对此不要过分担心，这时应该手工地将L4中出现的负值改为零，然后再第二次运行Solver，在这次Solver运行结束时会再一次显示如图 34所示的出错信息，但这时工作表模型已变成了如图 36所示的形式。再将其中的负值可变单元格（K4）改为零并第三次运行Solver，这次Solver运行结束时便会显示一个正常的“规划求解结果”对话框（图 37）并将得到最后的最优解（图 38）。 图 36图 37图 38显示的最终计算结果表明：对于三种产品车间A分别生产 (0, 0, 300)，车间B分别生产 (100, 240, 0)是一个最优解。但是这个最优解是从车间A生产(1, 1, 1)而车间B也生产(1, 1, 1)这一初始条件出发找到的。由于在非线性规划问题中有可能发生局部最优解，所以我们需要从不同的初始条件出发多做几次Solver求解然后才能确定真正的最优解。 经过对于多个局部最优解的比较，我们最后可以断言：图 38所示的解的确是一个全局最优解。公司应该避开各个车间在学习曲线效应上的短处（由于车间A在产品1与产品2的生产中学习能力不强，所以不要安排该车间生产这两种产品；另外，车间B在产品3的生产中进步不快，所以也不要安排它生产产品3）。由于学习曲线效应使生产时间大大缩短，两个车间在最高工时指标远没有用完的情况下就完成了生产任务。总成本为45741.37元。将图 38与图 36、图 35这两个显示出错信息的中间结果加以对比可以发现，两个中间结果其实还是有意义的：第一个中间结果已经求得了产品3的正确安排，第二个中间结果在此基础上进一步求得了对于产品2的正确安排。 图 38 图 39 特别有意思的是，我们在从不同初始条件出发计算最优解的过程中假如将初始条件设置成 那么这时将会求得一个看来很古怪的“最优解”：所有的产品都让车间A生产（这个解所实现的总成本为50738.97元，见图 39上部）。由于前面已经说过车间A其实并不适宜于生产产品1与产品2，而这个解居然会被Solver认定为一个最优解这的确有一些令人费解（何况它所实现的总成本又比前面求得的45741.37元大得多）。我们现在来分析这个解的确是一个局部最优解的理由。考虑车间A生产数量为(100, 239, 300)而车间B生产数量为(0, 0, 0)这样一个初始条件，从这一条件（其中已经安排车间A生产239个产品2）出发有两种方法来安排产品2所差的最后一个产品：由车间B生产这个产品或者继续由车间A来生产它。虽然车间A对于产品2的学习能力远不及车间B（两个车间的学习百分比分别是99%与80%），但是车间A在已经生产了239个的基础上（愚者已经学习了足够长的时间）生产第240个产品所需要的时间还是比车间B（智者刚开始学习）生产第一个产品所需要的时间短，因此全部240个产品都由车间A生产就这样成了一个“局部最优解”（可对图 39中上下两部分在O4中所显示的总成本进行比较）。 图 40图 41 为了将上面所作的分析进一步具体化，我们在两个空白单元格中计算出车间A生产第240个产品所需要的时间与车间B生产第一个产品所需要的时间，结果显示前者等于4.55小时而后者等于5小时（图 40），既然每个工时的成本相同，那么当然最后一个产品让车间A生产成本较小）。在这两个单元格中键入的Excel公式是：图 41显示了在K4=100、K5=0、M4=300与M5=0情况下在L4接近240同时又保证L5与L4之和等于240（在L5中键入公式”=240-L4”）的情况下O4中的总成本随L4变化的规律，从中可以清楚地看出L4=240的“局部最优”性质。附注：学习曲线效应 人们从经验中发现，工人在不断制造相同的产品（或反复从事相同性质的工作）时，随着所制造的产品数量的增加，所花费的时间会逐步减少。如果用t1表示某个工人制造第一个产品所花的时间，经验证明：做完前x个产品所需要的时间Tx可以表示为 其中k是一个介于0与1之间的常数 在( 11 )式的基础上可以进一步得出以下结论： 1对于前x个产品平均制造每个产品所需要的时间ax等于 2第k个产品的制造时间ux等于 在x很大（例如x10）时，此式可以近似地简化为 3从第a个产品到第b个产品的制造时间等于 一个特定的学习曲线效应除了可以用系数k来加以刻画之外，还可以采用一个称为学习百分比（learning percentage）的参数p来刻画。很容易证明，前2x个产品的平均制造时间与前x个产品的平均制造时间的比值是与x的具体数值无关的，即 这个（无论原来制造个数等于多少将制造个数加大一倍时每个产品平均制造时间的减小百分比称为学习百分比，即 容易验证，p与k之间存在着简单的对应关系 在已知一个学习曲线效应中的学习百分比p的情况下，在将产品个数增大一倍时的每个产品平均制造时间与原有产品个数时的每个产品平均制造时间之间存在这以下的关系：容易证明，在x10时在第2x个产品的制造时间u2x与第x个产品的制造时间ux之间也近似地存在着相同的关系： 下载7、一个产品混合问题分析：【参考模型4-7】一个产品混合问题（注册会计专业学生林岚君提供）某公司生产和销售A与B两种产品，它们的生产与销售数据为：此外，该公司每月生产能力在机器与人工两种资源上的限制分别为68250机器小时与47250人工小时。假定所有产品只要生产出来都能按预定的单价销售出去。试确定在满足生产能力限制条件下为了使公司利润达到极大两种产品各应生产多少数量。【解】这是一个典型的产品混合问题。我们用?表示公司销售两种产品所获得的总利润，用Qa与Qb表示两种产品的销售数量，pa与pb表示两种产品的单价（pa = 11.3，pb = 16.4），va与vb表示它们的单位可变成本（va = 6.3，vb = 10.0），Fa与Fb表示它们的固定成本（Fa = 300，Fb = 700）；再用a1与b1表示各生产一件产品A与产品B分别需要的机器小时数（a1 = 76，b1 = 120），用a2与b2表示各生产一件产品A与产品B分别需要的人工小时数（a2 = 85，b2 = 60），又用C1与C2表示公司每月可供使用的全部机器小时与人工小时（C1 = 68250，C2 = 47250）。这样，目前的问题便可表示成如下的数学形式：为了在Excel中应用Solver求解此问题，我们首先在工作表的适当单元格中输入各个已知参数并将这些单元格布置成如图 42所示的格局（在D4与E4中目前输入的是两个任意的数值）并在有关单元格中键入如下的Excel公式(D10:D12中的公式要拷贝到E10:E12中去，F5中的公式要拷贝到F6中去)： 经过上述布置后，目前键入了任意数值的、代表两种产品销售数量的单元格D4与E4就是预定的决策变量（changing cell），这两个单元格与F5:F6中表示所需使用的资源总量的两个单元格就是预定的约束条件左端（constraint），代表总利润的单元格F12则是预定的、有待极大化的目标变量（target cell）。开始时我们假定两种产品的销售量不要求一定是整数。在“规划求解参数”对话框中作如图 43所示的Solver设置（其中第三行规定了决策变量的非负性，第四行反映了的机器小时与人工小时这两种生产能力限制，第五行第五个参数”TRUE”正确地指明了这是一个线性模型），这样就完成了对于这个产品混合问题线性规划模型的构建。 图 42 图 43 图 44Solver帮助我们求得的最优解（图 44）表明，在目前条件下的最优生产安排是 与 ，所获得的最大利润为2904.36元，这时机器小时与人工小时两种资源全部用完。 如果要求两种产品的销售量都必须是整数的话则需要在图 43所示的Solver模型设置中增加一个要求D4与E4取整数值的条件。改造后的Solver设置如图 45所示，所求得的最优解则如图 46所示，即： 与 ，在整数销售量条件下所实现的最大利润为2903.80元，此时人工小时与机器小时两种资源均略有剩余。 图 45图 46可以制作一个如图 47所示的可调图形来表示图 44中的线性规划模型。图中水平轴与垂直轴分别代表两种产品的销量Qb与Qa，绿色与蓝色直线分别代表机器小时与人工小时的限制（约束条件），这两条约束直线与两个坐标轴所围成的凸四边形就是问题的可行区，此可行区边界共有四个顶点，即：机器小时约束直线与人工小时约束直线的相互交点、它们各自与垂直坐标轴或水平坐标轴的交点以及坐标原点。根据线性规划理论可知本问题的最优解（即可行区中使目标函数达到其最优值的点）必定是这些顶点中的一个。 本问题中的目标函数是公司总利润，图中灰色直线是该总利润等值线（等利润线）