使用Excel动态圆柱图进行信用条件分析

1.信用条件分析的原理

信用条件是指企业接受客户信用订单时所提出的付款要求，主要包括信用期限、折扣期限和现金折扣率等。它一般用“2/30、N/60”的形式来表示，其中，“2”表示现金折扣率为2%，“30”表示折扣天数，　“60”表示信用天数，即若客户在30天内付款，可以享受2%的现金折扣，如果放弃折扣，则全部款项必须在60天内付清。

信用条件的分析在应收账款（赊销）管理中具有十分重要的作用。信用条件分析与信用天数的分析类似，也是根据不同的赊销方案，计算信用前收益、相关成本、信用后收益，然后将信用后收益最大的方案作为最优的方案。但它的相关成本还应包括现金折扣成本。

2.现金折扣成本的计算

有的顾客可能会享受公司提供的现金折扣，而有的顾客可能不享受。所以，现金折扣成本计算公式如下：

现金折扣＝∑（赊销额×享受折扣的客户比率×现金折扣率）

3.平均收款天数的计算

现金折扣分析时，也应根据顾客是否享受现金折扣计算平均收款天数，平均收款天数计算公式如下：

4.Excel的字符取数函数

Excel可用取右字符RIGHT函数从右部起取指定位数的字符数，用取左字符LEFT函数从左部起取指定位数的字符数，用取指定位置字符MID函数取特定位置的字符数，用字符计数LEN函数检测字符的长度值。它们的函数公式如下：

＝MID(text，start＿num，num＿chars)

＝LEFT(text，num＿chars)

＝LEN(text)

式中，text表示要进行运算的字符表达式，1个空格也将作为1个字符，参数中若为文本型数据，应用半角双引号进行引用；num＿chars表示要提取的字符数，它必须大于或等于0，如果省略，则取1；start＿num表示要提取的第一个字符的位置。

5.Excel的字节取数函数

英文字符、数值、英文标点符号等为单字节；汉字、中文标点符号等为双字节。用字符取数函数时，1个字符不论是单字节还是双字节，都视为1个字符；用字节取数函数时，1个单字节字符视为1个字节，1个双字节字符则视为2字节。与字符取数函数相对应，字节取数函数也有RIGHTB、LEFTB、MIDB、LENB等函数；字节取数函数的参数构成与字符取数函数的参数相同。

（1）某公司预测全年赊销额为600万元，信用条件为2/20、1/60、N/90，估计有60%的顾客享受2%的现金折扣、10%的顾客享受1%的现金折扣。则：现金折扣＝600×2%×60%＋600×1%×10%＝7.8（万元）；平均收款天数＝20×60%＋60×10%＋90×（1-60%-10%）＝45（天）。

（2）在空白工作表中练习字符函数：键入“＝RIGHT（"第12月收入56万元"，4）”，则显示为“56万元”；键入“＝LEFT（"第12月收入56万元"，4）”，则显示“第12月”；键入“＝MID（"第12月收入56万元"，3，4）”，则显示“2月收入”；键入“＝LEN（"第12月收入56万元"）”，则显示“10”。

（3）在空白工作表中练习字节函数：键入“＝RIGHTB（"第12月收入56万元"，4）”，则显示为“万元”；键入“＝LEFTB（"第12月收入56万元"，4）”，则显示“第12”；键入“＝MIDB（"第12月收入56万元"，3，4）”，则显示“12月”；键入“＝LENB（"第12月收入56万元"）”，则显示“16”。

某公司近年销赊销额为4 000万～6 000万元，变动成本率为50%～75%，机会成本率为8%～20%，为了强收款管理，提出了A、B两套方案。A方案信用条件是N/60，估计坏账损失率为3%，收账费用为65万元；B方案信用条件为2/20、N/60，估计有70%的顾客会享受此项折扣优惠，坏账损失率为1.5%，收账费用为45万元。请为该公司进行决策分析。

Excel中设计如图5-17所示，使用方法是：通过滚动条确定公司的赊销额、变动成本率与机会成本率，Excel自动提取相关字段的数据进行分析，计算相关成本、信用前后的收益等指标，进行方案择优的动态提示。信用成本用三维圆柱图在右部进行动态提示。

（1）录入A、B列单元格文字，合并单元格区域，录入C5至D9单元区域的已知数据，调整行高列宽、设置字体字号等。

（2）设计滚动条与代码取值。在窗体工具（开发工具）中单击滚动条按钮，在C2单元格拖动一个滚动条控件，并将其复制粘贴到C3、C4单元格中。

右击C2中的滚动条进入“设置控件格式”对话框，在最小值、最大值、步长、页步长中键入“4000、6000、100、300”，在单元格链接中键入“＄D＄2”。

C3单元格滚动条的最小值、最大值、步长、页步长与单元格链接分别为“50、75、1、10、＄C＄3”。C4单元格滚动条的最小值、最大值、步长、页步长与单元格链接分别为“8、20、1、2、＄C＄4”。

图5-17　动态圆柱图信用条件分析（Excel 97）

在D3单元格键入公式“＝C3/100”，在D4单元格键入公式“＝C4/100”，再分别单击百分比按钮、增加小数位数按钮，以便将代码值转换为百分比数值。

然后将C3、C4单元格的代码值居中隐藏。

（3）用“取左部字符LEFT函数”取现金折扣率，方法如下：

a.选定D12单元格，单击插入函数按钮，选择“文本”类中的取左部字符Left函数，进入函数参数对话框，如图5-18（a）所示。

图5-18　取左部字符LEFT（a）与取指定位置字符MID（b）函数(www.xing528.com)

b.由于现金折扣率为D6单元格左部的第1个字符，所以，在Text参数中键入“D6”；在Num＿chars参数中键入“1”。

c.单击“确定”按钮，编辑框中的公式为“＝LEFT（D6，1）”，D6单元格的值为“2”，所以，还应将公式取值除以100，即D12单元格的公式为“＝LEFT（D6，1）/100”。再单击百分比按钮、增加小数位数按钮，使其转换为百分比格式。

（4）计算信用前收益与现金折扣。在C10单元格键入信用前收益公式“＝＄D＄2∗（1-＄D＄3）”，在C11单元格键入现金折扣公式“＝＄D＄2∗C7∗C12”。然后自动填充D10、D11单元格公式。

（5）用“取指定位置字符MID函数”计算折扣天数，方法如下：

a.选定D14单元格，单击插入函数按钮，选择“文本”类中的MID函数进入函数参数对话框，如图5-18（b）所示。

b.由于折扣天数为D6单元格的第3、第4这两个字符，即字符的起始位置为3，要取2个字符。所以，在Text中键入“D6”，在Start＿num中键入“3”，在Num＿chars参数中键入“2”。

c.单击“确定”按钮，编辑框显示公式“＝MID（D6，3，2）”，D14单元格显示值“20”。

（6）用“取右字符RIGHT函数”计算信用天数，在C15单元格键入“＝RIGHT（C6，2）”。自动填充到D15单元格。

（7）机会成本的计算。按平均收款天数公式在C16单元格键入“＝C7∗C14＋（1-C7）∗C15”。

在C17单元格键入应收款平均余额公式“＝＄D＄2/360∗C16”。

在C18单元格键入赊销占用资金公式“＝C17∗＄D＄3”。

自动填充D16至D18单元格公式。

在C13单元格键入机会成本公式“＝C18∗＄D＄4”。自动填充D13公式。

（8）计算信用后收益。在C19单元格键入坏账损失公式“＝＄D＄2∗C8”。

在C20单元格键入收账费用“＝C9”。

在C21单元格键入信用后收益公式“＝C10-C11-C13-C19-C20”。

然后自动填充D19至D21的单元格公式。

（9）方案择优。在C22单元格键入函数公式“＝MAX（C21：D21）”。

在C23单元格用嵌套函数确定最优方案，即先用相对位置MATCH函数查找最大值所在的相对列数，再用数组INDEX函数根据最大值的相对列数查找C5至D5单元区域的对应单元格，并返回该单元格的值。其嵌套函数为“＝INDEX（C5：D5，MATCH（C22，C21：D21））”。

（10）Excel 2003及其以前版本插入百分比堆积柱形圆柱图，方法如下：

a.选定B5至D5、B11至D11、B13至D13、B19至D19、B20至D20单元区域作为数据源。

b.单击插入图表按钮进入图表向导对话框，选择“柱形图”中的“百分比堆积柱形圆柱图”项，单击“完成”按钮。

c.调整数据源。插入的百分比堆积柱形圆柱图，将A方案、B方案作为图例，而将相关费用作为系列。互换调整的方法是：在系列、绘图区或图表区上右击，选择“源数据”命令，进入“源数据”对话框，选择“系列产生在行”。

d.增加标题与系列值。在绘图区或图表区上右击，进入“图表选项”对话框；在“标题”卡片中录入图表的标题“信用成本构成”；在“数据标志”卡片中勾选“值”。

默认情况下，数据标志值置于不同颜色的系列（圆柱）上，但系列分项为深色时不清晰。修改方法是：右击深色的数据标志块，进入“数据标志格式”对话框；在“字体”卡片中，将字体的颜色修改为“白色”，在“图案”卡片中设置“红色”填充，在“数字”卡片中设置1位小数。

也可在格式工具栏进行字体色、填充色、小数位数的设置。

e.修改图例。将图例置于图表的下方，通过拖动将其显示为2行。

f.右击“系列”进入“数据系列格式”对话框，在“选项”卡片中将分类间距、透视深度修改为“50”。

g.修饰图表。选定背景墙并通过“角点”的拖动调整其显示的纵深度；修改地板的颜色为“黄色”；通过鼠标拖动等方式，调整图表对象的大小、位置等。

（1）插入或修改为圆柱图。在Excel 2007—Excel 2019中，选定B5至D5、B11至D11、B13至D13、B19至D19、B20至D20单元区域作为数据源，通过“插入/图表”功能区命令插入百分比堆积柱形圆柱图。其中，Excel 2013—Excel 2019插入的是三维百分比堆积柱形方柱图，所以还应右击方柱块选择“数据系列格式”命令，在系列选项中将方柱图修改为圆柱图。

（2）行列互换的方法是：在图例、水平轴或系列上右击，选择“选择数据”命令，进入“选择数据源”对话框；单击“切换行列”按钮。

Excel 2007及其以后版本切换行列，还可以单击“图表工具/设计/数据”组中的“切换行列”按钮。

（3）侧面墙填充。通过图表元素框选择“侧面墙”，单击“图表工具/格式/设置所选内容格式”进入“设置背景墙格式”对话框。Excel 2007、Excel 2010在“填充”列表中选择“渐变填充”，预设颜色为“熊熊火炬”，类型为“射线”，透明度为“70%”，如图5-19（a）所示。Excel 2013—Excel 2019在“填充与线条”按钮中进行“渐变填充”设置，如图5-19（b）所示。

（4）类似地，设置系列（圆柱）的间距、分类间距，增加数据标签与修改其字体色、填充色，修改图例，修改图表标题等，将横坐标轴的选项修改为“逆序类别”等。

Excel 2007及其以后的版本，将侧面墙、背面墙称为背景墙；侧面墙、背面墙可设置为不同的格式，也可在背景墙中统一设置格式。Excel 2003及其以前版本没有侧面墙、背面墙之分，只能在背景墙中进行统一的格式设置。所以，Excel 2003及其以前版本有“角点”图表对象。

图5-19　设置背景墙格式（Excel 2007—Excel 2019）