赊销天数决策的Excel动态模型构建，本文主要内容关键词为：赊销论文,天数论文,模型论文,动态论文,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)”。
再自动填充D11、E11单元格公式，以计算乙、丙方案的平均收款天数。
2.应收账款平均余额 = 全年赊销额÷360×平均收账天数。所以，在C12单元格中键入“=C6/360*C11”；再自动填充D12、E12单元格公式。
3.应收账款占用资金额 = 应收账款平均余额×变动成本率。所以，在D13单元格中键入“=C12*＄E＄2”，E2单元格要绝对引用；再自动填充D13、E13单元格公式。
4.机会成本 = 应收账款占用资金额×机会成本率。所以，在C10单元格中键入“=C13*＄D＄3”，E3单元格要绝对引用；再自动填充D10、E10单元格公式。
(三)计算坏账损失与收账费用
因应收账款无法收回而产生的损失称为坏账损失，公式为“坏账损失额=全年赊销总额×坏账损失率”。所以，在C14单元格键入“=C6*C7”。
收账费用是企业对拖欠的应收款进行催收所支付的费用。在C15单元格键入“=C8”。
(四)计算信用后收益
信用后收益=信用前收益-机会成本-坏账损失-收账费用。所以，在C16单元格键入“=C9-C10-C14-C15”。
自动填充D14∶E16单元格公式。
四、编辑Excel数据图表
插入堆积柱形圆柱图，方法如下：
(一)选定C4∶E4单元格区域，按下键盘上的Ctrl键，再选定C16∶E16单元区域。
(二)单击常用工具栏的图表向导
按钮进入图表向导第1步界面，如图4所示。
图4 图表向导1-图表类型
选择“圆柱图”中的“堆积柱形圆柱图”，并单击“下一步”按钮。在图5所示的第3步“图表选项”的“坐标轴”页签中，取消“数值轴”选项；在“网格线”页签中取消“网格线”选项；在“图例”页签中取消“显示图例”选项；在“数据标志”页签中勾选“值”选项。其他按向导提示设置，完成图表的插入。
图5 图表向导3-图表选项
(三)修改修饰图表
1.右击圆柱选择“数据系列格式”命令进入图6所示的界面。在“图案”页签单击“填充效果”，选择窄竖线图案、粉红色前景色；在“选项”页签中修改分类间距为“20”。
图6 设置数据系列格式
2.右击背景墙选择“背景墙格式”命令，将边框和区域选项修改为“无”。
3.右击图表区选择“图表区格式”命令，再单击“填充效果”按钮，在“纹理”页签中选择“花束”图案。
4.调整图表大小并拖动其位置。
五、绘制Microsoft图形
图1的红色印章是由空心圆圈、五角星和2幅艺术字组合而成。
(一)红色圆圈用绘图工具栏设计
1.选择“视图/工具栏/绘图”菜单命令，调出绘图工具栏，如图7所示。
图7 绘图工具栏及功能
2.绘制圆形。单击绘图工具栏上的椭圆按钮，此时鼠标变“+”字状，在工作表上拖动，绘出一个有填充色的正圆图形，圆的四方将出现8个空心小圈的调节柄和一个绿色的旋转柄，如图8所示。通过这些调节柄，可对圆形的大小、方向、位置等进行拖动调整。
3.修改为红色圆圈。选定圆形图，选择绘图工具栏“填充色”按钮边的下拉箭头，在弹出的菜单中选择“无填充色”；选择“线条色”按钮边的下拉箭头，在弹出的菜单中选择“红色”；选择“线型”按钮，在弹出的菜单中选择“2.25磅”。如图8所示。
图8 红色圆圈设计过程
(二)绘制五角星
选择绘图工具栏“自选图形/星与旗帜/五角星”菜单命令，在工作表中拖动一个五角星，如图9所示。通过五角星上的调节柄，进行大小、位置等的调整。再将五角星的填充色、线条色设置为红色。
图9 绘制红色五角星
(三)插入上弯型艺术字
1.选择“视图/工具栏/艺术字”菜单命令，调出艺术字工具栏，如图10所示。
图10 艺术字工具栏及功能
2.单击“插入艺术字”按钮进入“艺术字库”界面，在该界面选择“上弯型”艺术字样式；单击“确定”按钮进入“编辑艺术字文字”界面，在该界面选择“华文中宋”字体、20字号，键入“成都×有限责任公司”，单击“确定”按钮；艺术字被作为图片飘浮于工作表中，并有调节柄，如图11上部所示。
图11 上弯型艺术字设计过程
3.通过艺术字下方中部的小白圈调节柄，向下拖动使艺术字变为向上弯曲的图形；拖动左部的绿色菱形调节柄，扩大或缩小艺术字的大小、弧度等。
4.选定艺术字，单击艺术字工具栏上的“高度相同”按钮；单击“字符间距”按钮，在下拉菜单中选择“稀疏”选项；单击“格式”按钮，设置填充与线条色为“红色”。
(四)插入直线型艺术字
直线型“财务专用章”艺术字，除在“字库”中选择“等高直线型”样式外，与上弯型的设计方法相似。
(五)组合为印章
1.组图。拖动五角星到圆圈中心，拖动上弯型艺术字、直线型艺术字到圆圈内；通过各自的调节柄，对其弧度、大小、高低、位置等进行调整。
2.组合。组图后的印章有4个对象，其中之一移动时，其他对象并不会移动，所以还应将其组合为一个图形。方法是：选定圆圈，按下键盘上的“Shift”键，再分别单击五角星、上弯艺术字、直线艺术字，使4个对象同时被选中；选择绘图工具栏“绘图菜单”选择“组合”命令，将4个对象组合为一个图形。
若组合不当需要重新加工，可右击该印章，选择“组合/取消组合”命令。
六、Excel提示与显示公式
(一)确定信用后收益最大值
最大值MAX函数公式为“=MAX(number1，number2，…)”；其中“Number1，number2，…”表示查找范围（最多30个参数）。所以，在C18单元格键入“=MAX(C16∶E16)”，如图3所示。
选定C18单元格，选择Excel“格式/单元格”菜单命令，在“数字”页签的右部（分类）中选择“自定义”，然后在右部的类型中键入“#，###"万元"”。即取整数并显示千位分隔符、“万元”的金额单位。
(二)确定最优方案
Excel的相对位置查找Match函数，可确定某单元格在选定区域中的相对行号、相对列号，函数公式为“=MATCH(Lookup_value，Lookup_array，Match_type)”。其中“Lookup_value”表示待查数； “Lookup_array”表示查找范围；“Match_type”为1或省略，则查找小于或等于待查数的最大值，为0则查找等于待查数的第一个值，为 -1则查找大于或等于待查数的最小值。
Excel的数值引用Index函数，可从一个指定的区域中查找某行与某列交叉的单元格，并取该单元格的数值。其函数公式为“= INDEX(Array，row_num，column_num)”。其中“Array”表示查找范围；“Row_num”表示在查找范围内要查找的相对行数；“Column_num”表示要查找的相对列数。
要动态显示收益最大值的方案名称，可用Match函数根据C18的值，去查找C16∶E16区域，确定该值所在单元格的相对列数；然后作为数组Index的嵌套函数，即根据最大值的相对列数，去查找并返回C4∶E4区域中的方案名称。所以，在C19单元格中建入的嵌套函数为“=Index(C4∶E4，Match(C18，C16∶E16，0))”。
(三)确定赊销天数
在C20单元格键入“=Index(C11∶E11，Match(C18，C16∶E16，0))”
(四)决策建议
在C21单元格键入“=″建议选择″&C19&″，即赊销天数为″&C20”。
(五)显示当前日期
在C22单元格键入当前日期函数“=TODAY()”。
(六)显示公式
默认情况下，Excel的单元格显示计算结果、编辑栏显示单元公式，如图1所示。选择“工具/选项”菜单命令，在“视图”页签中勾选“公式”选项，则可在单元格中显示公式，如图3所示。