赊销天数决策的Excel动态模型构建

      一、导言

      企业赊销产品是一种重要的促销手段，但也会因持有应收账款而付出一定的代价，故此，企业应制定合理的信用政策，加强应收账款管理。信用政策制定中很重要的一项是对赊销天数的决策。由于Excel具有强大的数据处理、图表图形创建等功能，所以，可充分利用Excel公式与函数、数据图表向导、绘图工具、艺术字工具、信息提示、滚动条交互选择功能等，进行赊销天数动态决策模型的设计。

      【案例】 成都X有限责任公司为了加强赊销管理，提出了甲、乙、丙三套方案。甲方案为N/30（即赊销期30天无现金折扣），估计年赊销3 600万元，坏账损失率为赊销额的2.1%，收账费用为18万元；乙方案N/60，估计年赊销3 960万元，坏账损失率为3.5%，收账费用为35万元；丙方案为N/120，估计年赊销4 200万元，坏账损失率为4.4%，收账费用为51万元。

      根据历史资料，该公司变动成本率为55% ～75%，贷款利率（机会成本）为9%，请用Excel进行方案择优。

      在Excel中构建该公司赊销天数动态决策模型如图1所示。单击C2单元格中滚动条的左右箭头时，E2单元格将增减1%，单击滚动条内的空白处时，E2单元格将增减2%；同时，表中的计算值、提示信息与图表也随之动态变化。图1为变动成本率70%的效果图。

      二、滚动条的设计与代码取值

      (一)设计表格

      根据已知资料，录入文字、数值；但E2、C9∶E16、B17、C18∶C20、B21∶B22的文字和数值不用录入。合并相关单元格，设置字体字号，调整行高列宽等。如图1所示。

      

      图1 赊销天数决策模型结果图

      (二)设计滚动条的方法如下

      1.选择“视图/工具栏/窗体”菜单命令，调出窗体工具栏，如图1顶部所示。

      2.单击窗体工具栏滚动条按钮，此时鼠标变“+”状，在C2单元格中拖动一个滚动条控件。若对控件的大小和位置不满意，右击该滚动条将出现带6个小圈的调节柄于四周，如图1的C2单元格所示；鼠标指向调节柄，对其长宽、位置等进行拖动调整。

      3.右击滚动条选择“设置控件格式”命令进入如图2所示界面。勾选“三维阴影”选项；在单元格链接中键入“＄C＄2”（绝对引用C2单元格）；在最小值、最大值、步长、页步长中分别键入55、75、1、2（它们的取值范围为0～30000、不能取小数和负数）。

      

      图2 设置滚动条控件格式

      单击“确定”按钮回到工作表界面，C2单元格中将显示“55”；这是滚动条产生的设计代码值，如图3的C2单元格所示。

      

      图3 赊销天数决策模型设计图

      (三)代码取值与隐藏

      在E2单元格键入“=C2/100”；单击格式工具栏百分比%按钮，将设计代码值转换为变动成本率。

      C2单元格中的设计代码是干扰数据，可单击格式工具栏的居中对齐按钮，将之隐藏于滚动条的后面。

      三、数据处理中的公式与函数

      赊销天数决策时，应根据不同赊销方案计算信用后收益，并以此进行方案择优。

      (一)计算信用前收益

      信用前收益=年赊销额×（1－变动成本率）。所以，键入C9单元格公式“=C6*(1-＄E＄2)”，如图3所示。

      然后选定C9单元格，将鼠标指向C9单元格右下角的自动填充柄，待变为“+”状时按下鼠标向右水平拖动到E9单元格，以自动填充D9、E9单元格的公式。

      (二)计算机会成本

      机会成本是指因资金投放在应收账款上而丧失的再投资收益。Excel中计算分析方法如下：

      1.平均收款天数。没有现金折扣时赊销天数即为平均收款天数，可用“取右部字符Right函数”取数，其函数公式为“=RIGHT(Text，Num_chars)”其中“Text”表示要提取字符的字符串；“Num_chars”表示要提取的字符个数，忽略则取1。

      甲方案的天数为C5单元格中的后三个字符，也即是说要提取的字符串为C5单元格，提取的是该单元格右部的3个字符数。所以，在C11单元格中键入“=RIGHT(C5，3)”。