5Excel在应收账款管理中的应用（课件）《EXCEL在财务中的应用》（航空工业出版社）

Excel在应收账款管理中的应用 项目五 1 知识目标： ①掌握运用Excel编制应收账款明细账的方法。 ②掌握IF函数、SUMIF函数、COUNTIF函数、LOOKUP函数、TEXT函数等函数的用法。 ③熟练掌握Excel中的各种图表。 能力目标： ①以职业岗位能力为导向，具有财务具有职业判断能力。 ②能运用知识分析、解决企业日常财务处理过程中的常见问题。 素养目标： ①具有严谨、诚信的职业品质和良好的职业道德，具有良好的心里素质。 ②具有遵纪守法的法治意识和真实客观、细心严谨的做人做事原则。 ③具有诚信为本的职业素养、仔细谨慎的职业态度。 学习目标 冀中职业学院 目录 创建应收账款明细账 任务5.1 冀中职业学院 应收账款对比分析 任务5.3 统计各客户应收账款 任务5.2 分析客户应收账款账龄 任务5.4 5.1 创建应收账款明细账 冀中职业学院 任务 5.1 创建应收账款明细账 要管理好应收账款，首先在 Excel 中创建“应收账款明细”，即根据企业实际发生的经济业务，将企业的应收账款信息登记到 Excel 表格中，具体操作步骤如下。 步骤一：创建一个新的 Excel工作簿，将“Sheet1”工作表重新命名为“应收账款明细”。 步骤二：根据企业需要，将应收账款管理所需字段信息输入到 Excel表格中，如下图所示。 5.1 创建应收账款明细账 冀中职业学院 任务 5.1 创建应收账款明细账 步骤三：在工作表中依次输入企业应收账款的明细记录，然后依次添加单元格边框、设置字体字号及颜色等，效果如下图所示。 5.1 创建应收账款明细账 冀中职业学院 任务 5.1 创建应收账款明细账 步骤四：用公式填写F列“未收金额”内容，在F4单元格输入公式“=D4-E4”，计算出F4的金额，双击F4单元格右下角的填充柄将公式向下复制，如下图所示。 5.1 创建应收账款明细账 冀中职业学院 任务 5.1 创建应收账款明细账 步骤五：用公式填写H列“是否到期”内容，在H2单元格选择时间函数“Today”，在H4单元格输入公式“=IF(((C4+G4)-$H$2)<0,"已到期","没有到期")”计算是否到期，双击 H4单元格右下角的填充柄将公式向下复制，如下图所示。 5.2.1 提取不重复客户名单 冀中职业学院 任务 5.2 统计各客户应收账款 企业会跟客户发生多项业务往来，每发生一笔业务产生应收账款就会记录一次，为了完整掌握对方欠款情况，能不能把重复的客户信息汇总成一笔记录呢？ 5.2.1 提取不重复客户名单 冀中职业学院 任务 5.2 统计各客户应收账款 步骤一：在“应收账款”工作簿中插入新工作表，取名为“应收账款汇总”。 步骤二：将“应收账款明细”工作表中“公司名称”列复制到“应收账款汇总”工作表中。 步骤三：单击“公司名称”列数据区域中的任意单元格，如A5单元格，在“数据” 选项卡下单击“删除重复项”命令按钮，打开“删除重复项”对话框。保留其中的默认选项，单击“确定”按钮，如图1所示。在弹出的Excel提示对话框中再次单击“确定”按钮，完成不重复客户的提取，如图2所示。 图1 图2 5.2.2 计算各客户未收账款总额和业务笔数 冀中职业学院 任务 5.2 统计各客户应收账款 使用公式完成各客户应收账款金额和业务笔数的汇总，操作步骤如下： 步骤一：在B1、C1单元格内依次输入列标题“未收金额”和“业务笔数”。 步骤二：在B2单元格输入公式“=SUMIF(应收账款明细!B:B,A2,应收账款明细!F:F)” 结果如下图所示。 5.2.2 计算各客户未收账款总额和业务笔数 冀中职业学院 任务 5.2 统计各客户应收账款 步骤三：在C2单元格输入公式“=COUNTIF(应收账款明细!B:B,A2)”计算各客户业务笔数，并将公式向下复制。如图1所示，最终结果如图2所示。 图1 图2 5.3.1 使用条件格式展示各客户的应收账款情况 冀中职业学院 任务 5.3 应收账款对比分析 步骤一：在“应收账款”工作簿中的“应收账款汇总”工作表中，选中B2:B14单元格区域，单击“开始”选项卡下的“条件格式”下拉按钮，在下拉菜单中依次选择“数据条”“紫色数据条”命令，如下图所示。 5.3.1 使用条件格式展示各客户的应收账款情况 冀中职业学院 任务 5.3 应收账款对比分析 步骤二：调整B列列宽，设置文本框为右对齐，效果如下图所示。 设置完成后，通过数据条的长短可以直观地显示各客户未收金额对比情况。 5.3.2 用图表展示各客户未收金额占比 冀中职业学院 任务 5.3 应收账款对比分析 图表是图形化的数据，由点、线、面与数据组合而成，具有直观、种类丰富、实时更新等特点。 使用图表能使数据的大小、差异以及变化趋势等更加直观形象，展示数据所包含的更有价值的信息，使用Excel图表，可以直观展示各客户未收账款的占比。操作步骤如下： 步骤一：在“应收账款”工作簿中的“应收账款汇总”工作表中，选中A1:B14单元格区域，在“插入”选项卡下单击“饼图”下拉按钮，在样式下拉菜单中选择“饼图”，如右图所示。 5.3.2 用图表展示各客户未收金额占比 冀中职业学院 任务 5.3 应收账款对比分析 根据以上操作，可得如下图表： 5.3.2 用图表展示各客户未收金额占比 冀中职业学院 任务 5.3 应收账款对比分析 步骤二：单击图表，然后在“设计”选项卡下单击饼图效果“图表样式”命令组右侧的下拉按钮，在图表样式库中选择一种样式，如下图所示。 5.3.2 用图表展示各客户未收金额占比 冀中职业学院 任务 5.3 应收账款对比分析 步骤三：单击选择图例项，按 delete 键删除，如下图所示。 5.3.2 用图表展示各客户未收金额占比 冀中职业学院 任务 5.3 应收账款对比分析 步骤四：单击图表，然后在“布局”选项卡下单击“数据标签”下拉按钮，在弹出的下拉菜单中选择“其他数据标签选项”，如下图所示。 5.3.2 用图表展示各客户未收金额占比 冀中职业学院 任务 5.3 应收账款对比分析 步骤五：在弹出的“设置数据标签格式”对话框中，勾选“标签包括”命令组下的“类别名称”“百分比”和“显示引导线”复选框，单击选中“标签位置”命令组下的“数据标签外”单选框，单击“分隔符”下拉按钮，然后在下拉菜单中选择“(空格)”，最后单击“关闭”按钮，如右图所示。 5.3.2 用图表展示各客户未收金额占比 冀中职业学院 任务 5.3 应收账款对比分析 步骤六：单击任意一个数据标签，在“开始”选项卡中设置字体和字号，如下图所示。 5.3.2 用图表展示各客户未收金额占比 冀中职业学院 任务 5.3 应收账款对比分析 步骤七：单击图表标题，按下鼠标左键不放，向左拖动，如下图所示。 5.3.2 用图表展示各客户未收金额占比 冀中职业学院 任务 5.3 应收账款对比分析 步骤八：图表标题用于说明图表要表达的主要内容，因此修改图表标题中的内容为“爱美丽公司未收账款占比最高”如下图所示。 5.3.2 用图表展示各客户未收金额占比 冀中职业学院 任务 5.3 应收账款对比分析 步骤九：单击图表区，在“格式”选项卡中单击“形状填充”下拉按钮，在“主题颜色”面板中选择一种颜色，如下图所示。 5.4.1 使用公式计算账龄 冀中职业学院 任务 5.4 分析客户应收账款账龄 假设账龄统计日为当前日期，在“应收账款明细工作表”中I3单元格输入标题“账龄”,在14单元格输入公式“=LOOKUP($HS2-(C4+G4),{-999,0,30,60,120},{”没有到期","30天以内","60天以内","60-120天","120天以上“})”，并向下复制公式，如下图所示。 5.4.1 使用公式计算账龄 冀中职业学院 任务 5.4 分析客户应收账款账龄 在上述公式“=LOOKUP($HS2-(C4+G4),{-999,0,30,60,120},{”没有到期","30天以内","60天以内","60-120天","120天以上“})”中 “$HS2-(C4+G4)”部分是用统计日期减去开票日期和付款期限之和，计算结果为“-25”，然后在LOOKUP函数第二个参数“{-999,0,30,60,120}”中查找账龄天数。 因为在“{-999,0,30,60,120}”中没有-25,所以会以小于-25的值-999进行匹配。 “-999”在“{-999,0,30,60,120}”中的位置是1，LOOKUP 函数最终返回第三个参数“{"没有到期","30天以内","60天以内”,”60-120天","120天以上"}”中相同的位置，计算结果为“没有到期”。 5.4.2 应收账款到期日提醒 冀中职业学院 任务 5.4 分析客户应收账款账龄 在“应收账款明细”工作表中设置公式，能够对应收账款到期和过期天数进行提醒，在J3单元格输入“到期日”,在J4单元格输入公式“=TEXT(C4+G4-$H$2,“0天后到期；已过期0天；今日到期“)”,并将公式向下复制，如下图所示。 $$