Excel在会计与财务中的应用课件：Excel在应收账款管理中的应用

7.1应收账款统计7.2逾期应收账款分析7.3应收账款账龄分析7.4信用政策下应收账款管理Excel在应收账款管理中的应用内容简介：本章主要讲解利用Excel工具进行应收账款管理，包括运用Excel建立应收账款统计表格以及对应收账款账龄分析，并对逾期应收账款重点关注计提坏账。结合实际，企业为加速回收应收账款会设置不同信用政策，通过Excel辅助进行信用决策选择。学习目标：通关本章的学习，学生应掌握运用Excel统计和分析应收账款，提高应收账款管理效率，制定合理的信用政策。内容简介和学习目标建立应收账款明细登记表根据企业自身管理需要，将企业现有应收账款信息登记到工作表中。以下仅提供一个参考：7.1应收账款统计各债务人的应收账款统计现有的各项应收账款登记完毕后,由于债务人众多,为了方便了解某一债务人所欠本公司款项的总额,利用Excel提供的数据命令,建立针对不同债权人所欠金额进行汇总的功能。分类汇总功能选择“开始”选项卡中的“排序和筛选”→“自定义排序”命令。在“主要关键字”选择“债务人姓名”,然后单击“添加条件”按钮,在出现的“次要关键字”选择“赊销日期”,其中“排列依据”默认为“单元格值”,“次序”默认为“升序”,单击“确定”按钮。7.1应收账款统计各债务人的应收账款统计执行命令后,原来按照应收账款发生先后顺序登记的数据,重排序为按照债务人姓名进行排序。如图所示。7.1应收账款统计各债务人的应收账款统计对各债务人的应收账款金额进行汇总,选中B1单元格即选中“债务人名称”单元格,选择“数据”→“分级显示”→“分类汇总”命令.7.1应收账款统计各债务人的应收账款统计在弹出的“分类汇总”对话框中,“分类字段”选择“债务人姓名”,“汇总方式”选择“求和”,“选定汇总项”选择“应收金额”,默认选择“替换当前分类汇总”和“汇总结果显示在数据下方”后,单击“确认”按钮。7.1应收账款统计各债务人的应收账款统计执行命令后,即可显示按照债务人姓名针对应收账款金额进行的汇总数据。7.1应收账款统计通过汇总数据可以看出客户丁和客户乙欠本公司款项金额较大，应对其进行重点管理。各债务人的应收账款统计执行命令后,原来按照应收账款发生先后顺序登记的数据,重排序为按照债务人姓名进行排序。如图所示。7.1应收账款统计完成应收账款明细登记表后，可以对该信息进行加工，使其自动判断应收账款随着时间的推移，是否到期，未到期金额发生的变动情况。7.2逾期应收账款分析提示：根据if函数以及if函数的嵌套，进行函数设置，自己可以尝试动手操作下~~为了下文进一步分析应收账款账龄,还需要利用Excel进一步计算各项应收账款的逾期天数,以便提供更加详细的管理数据。设计逾期天数分析表：在“逾期应收账款分析”工作表的数据右侧建立“逾期天数分析表”,对逾期天数进行分类筛选。实际工作中,通常对逾期天数划分不同等级,如“0~30”“30~60”“60~90”“90天以上”等。7.2逾期应收账款分析用IF函数分析逾期“0~30”天的应收账款：选中J4单元格,输入公式“=IF(AND($L$2-G4>0,$L$2-G4<=30),C4-E4,0)”,按“Enter”键确认。并将该公式复制到J5:J17单元格区域。7.2逾期应收账款分析该公式表示某债务人逾期应收账款的具体金额进行判断计算,如果该表中“L2”单元格(即当前日期为2020年7月29日)大于“G4”单元格(即到期日),即该项应收账款已经逾期,但逾期天数在30天以内(包括30天)。满足上述条件,返回“C4-E4”计算公式,计算该项已经逾期应收账款的剩余未收金额;如果不满足未到期条件,则返回“0”,表示未到期金额为0,即逾期应收账款不在0~30天期限内。同理，同学们可以自行完成30-60天，60-90天的逾期应收账款的函数设置。7.2逾期应收账款分析根据应收账款逾期天数分析表所提供的信息,可使企业了解各债务人收款、欠款情况,判断欠款的可收回程度和可能发生的损失。同时,企业还可酌情作出采取放宽或紧缩商业信用政策,并可作为衡量负责收款部门和资信部门工作效率的依据。建立应收账款账龄分析表7.3应收账款账龄分析利用“应收账款逾期天数分析表”建立“应收账款账龄分析表”如图所示7.3应收账款账龄分析（1）计算各账龄所涉及的应收账款金额编辑SUM公式引用未到期金额7.3应收账款账龄分析（2）计算各账龄所涉及的应收账款占应收账款总额的百分比

选中C4单元格,输入公式“=B4/$B$9”,按“Enter”键确认,即可计算出未到期应收账款占应收账款总额的百分比。复制公式到该列其他单元格中,可计算出其他账龄所涉及的应收账款占应收账款总额的百分比，结果如下图。7.3应收账款账龄分析（3）计算应收账款坏账准备的金额估计坏账准备比例账龄分析法下,计提坏账准备的比例简单得多,通常账龄越长,发生坏账的可能性越大,估计的坏账准备的比例就越高。假设A集团历史经验估计,未到期的应收账款发生坏账的可能性是0%,逾期0~30天的应收账款发生坏账的可能性约为1%,逾期30~60天的应收账款发生坏账的可能性约为3%,逾期60~90天的应收账款发生坏账的可能性约为6%,逾期90天以上的应收账款发生坏账的可能性约为10%,将估计的坏账准备比例分别输入D4:D9单元格。实际工作中,企业可以根据历史经验进行估计。7.3应收账款账龄分析（3）计算应收账款坏账准备的金额计算坏账准备金额①选中E3单元格,输入“坏账准备金额”。选中E4单元格,输入公式“=B4*D4”,按“Enter”键确认。

②将E4单元格公式复制到该列已经逾期的其他单元格中(即E5:E8),则可计算各账龄所涉及应收账款产生的坏账准备金额。选中E9单元格,输入公式“=SUM(E4:E8)”,按“Enter”键确认,计算坏账准备总额。7.3应收账款账龄分析7.4.1应收账款信用决策的基本原理在具体决策时，会涉及以下计算：计算方案的收益

边际贡献=销售收入-变动成本=销售量*单位边际贡献计算应收账款的机会成本

应收账款占用资金的机会成本=应收账款占用资金*资金成本其中：应收账款占用资金=应收账款平均余额*变动成本率=日赊销额*平均收现期*变动成本率

存货占用资金的机会成本=存货占用资金*资本成本

应付账款占用资金抵减的机会成本=应付账款占用资金*资本成本计算收账费用和坏账损失

坏账损失=预计销售率*坏账损失率计算折扣成本

折扣成本=赊销额*折扣率*享受折扣的客户比率各方案税前损益

税前损益=收益-成本费用7.4信用政策下应收账款管理7.4.2建立应收账款决策模型[案例]A集团面对竞争，为了稳定销售渠道，扩大商品销路，降低商品的管理费用，需要向客户采用信用政策，因此产生应收账款，然而赊销政策会给企业带来各种风险。该集团目前两种信用方案资料如图所示。要求：通过建立应收账款决策模型，选择合理的信用政策。

7.4信用政策下应收账款管理7.4.2建立应收账款决策模型根据已有信息，设计模型，如图。

7.4信用政策下应收账款管理7.4.2建立应收账款决策模型设置模型单元格公式步骤如下：在B10单元格中输入公式“=D4*($B$3-$B$4)”在B11单元格中输入公式“=(D4*$B$3/$B$6)*D3*($B$4/$B$3)*$B$5”在B12单元格中输入公式“=D6*$B$4*$B$5”在B13单元格中输入公式“=D4*D5*$B$3”在B14单元格中输入公式“=D7”在B15单元格中输入公式“=B10-SUM(B11:B14)”在B