模型4-01赊销商品最优现金折扣与催款费用决策

12345678910111213141516171819202122ABCDEFGHIJKLMNO最优现金折扣率=3%,最优催款费用=80元,最大利润=11151元inD7:现金折扣率3%inD8:催款费用80年销售收益12000现金折扣率080现金折扣率080inD9:预期投资回报率13%0%5.0%5.0%0%2.52inD10:坏帐损失率2.0%2%4.5%3.0%2%21.4平均还款期限(月)1.33%4.0%2.0%3%1.51.3inL11:坏帐损失24020inM11:机会成本16911151080列最大值列最大值inM12:现金折扣损失3600%11075110601107511151inM13:利润111512%1096011138最大利润所在列23%1096511151最大利润所在行3inG17:最大利润11151最优现金折扣率3.0%现金折扣率=0%最优催款费用80现金折扣率=2%现金折扣率=3%催款费用催款费用催款费用现金折扣率最优现金折扣率=3%,最优催款费用=80元,坏帐损失平均还款12345678910111213141516171819202122PQRS=VLOOKUP(D3,坏帐率表,MATCH(D4,H5:I5)+1)=VLOOKUP(D3,还款期表,MATCH(D4,L5:M5)+1)=D5*D7inD11:=D5*D3=D5*D6*D8/12inD12:=D5-D4-SUM(D9:D11)=MAX(H11:H13)inD14:=MAX(H11:I13)=MAX(I11:I13)inD15:=INDEX(基列,M13)=MATCH(D14,L11:M11)inD16:=INDEX(H10:I10,M12)=MATCH(D14,OFFSET(基列,0,M12))=最优现金折扣率=&ROUND(D15*100,2)&%,最优催款费用=&D16&元,最大利润=&D14&元110751096010965110601113811151107501085010950110501115011250现金折扣率=0%现金折扣率=2%现金折扣率=3%利润在不同现金折扣率与催款费用下利润的变化催款费用=0催款费用=80最优现金折扣率=3%,最优催款费用=80元,最大利润=11151元催款费用=80元2.0时的坏帐损失率=1234567891011121314151617181920212223ABCDEFGHIJKLMNOinD7:现金折扣率0%现金折扣率080现金折扣率080inD8:催款费用00%5.0%5.0%0%2.52.0年销售收益120002%4.5%3.0%2%2.01.4inD9:预期投资回报率13%3%4.0%2.3%3%1.51.3inD10:坏帐损失率5.0%平均还款期限(月)2.5110750802坏帐损失6000%1107511060现金折扣率=0%110600inH13:机会成本3252%1096011138现金折扣率=2%1113811138inI13:现金折扣损失03%1096511115现金折扣率=3%111150inI14:利润11075列最大值列最大值inI15:1107511138最大利润11138最大利润所在列22最优现金折扣率2.0%最大利润所在行2最优催款费用8023inG17:最优现金折扣率=2%,最优催款费用=80元,最大利润=11138元inL9:催款费用催款费用催款费用现金折扣率最优现金折扣率=2%,最优催款费用=80元,催帐费用=0催帐费用=80坏帐损失平均还款1234567891011121314151617181920212223PQRSTU=VLOOKUP(D3,坏帐率表,MATCH(D4,H3:I3)+1)=VLOOKUP(D3,还款期表,MATCH(D4,L3:M3)+1)=D5*D7inD11:=D5*D3=D5*D6*D8/12inD12:=D5-D4-SUM(D9:D11)=MAX(H9:H11)inD14:=MAX(H9:I11)=MAX(I9:I11)inD15:=INDEX(基列,I15)=MATCH(D14,H13:I13)inD16:=INDEX(H8:I8,I14)=MATCH(D14,OFFSET(基列,0,I14))=最优现金折扣率=&ROUND(D15*100,2)&%,最优催款费用=&D16&元,最大利润=&D14&元=INDEX(H9:I9,$L$8)inM9:=IF(L9=MAX($L$9:$L$11),L9,0)1234567891011121314151617181920212223V=IF(L9=MAX($L$9:$L$11),L9,0)11060111381111510600107001080010900110001110011200现金折扣率=0%现金折扣率=2%现金折扣率=3%利润利润随现金折扣率的变化催款费用=80元2.3时的坏帐损失率=催帐费用=0催帐费用=80