Excel在会计与财务管理中的应用 课件 项目六 往来款项管理

Excel在会计与财务管理中的应用项目六往来款项管理(1)理解应收账款、坏账准备和应付账款的基本概念与理论知识；(2)掌握Excel中管理与分析应收账款、应付账款的操作，建立分析表格和分析图；(3)掌握Excel中提取和分析坏账的操作。学习目标往来款项(accountcurent)是指企业在生产经营过程中发生的各种应收、应付款项及预收、预付款项。本项目主要介绍应收账款以及相应的坏账准备、应付账款在Excel中的应用和操作。应收账款是伴随企业的销售行为发生而形成的一项债权，通常要经过一段时间之后才能收回。企业在应收账款收回之前，该笔款项不仅不会增值，而且还可能存在一定的风险,这种风险指的是因为各种情况导致资金收不回来，如购货方意外死亡、破产无力支付等。发生应收账款时就会存在收不回来的情况，这种情况就是坏账。坏账准备是指企业无法收回或收回的可能性极小的应收账款，是应收账款的备抵账户。企业对坏账损失的核算采用备抵法。在备抵法下，企业每期末要估计坏账损失，设置“坏账准备”账户。情景引入备抵法是指采用一定的方法按期(至少每年末)估计坏账损失，提取坏账准备并转作当期费用；实际发生坏账时，直接冲减已计提坏账准备，同时转销相应的应收账款余额的一种处理方法。应付账款是伴随企业的购货行为发生而形成的一项债务，用以核算企业因购买材料、商品和接受劳务供应等经营活动经过一段时间之后应支付尚未支付的款项。这是买卖双方在购销活动中由于取得物资与支付货款在时间上不一致而产生的负债。下面以福源公司在某段时间内发生的应收账款、应付账款明细数据为例，介绍如何按账龄分别统计应收账款和应付账款、如何使用图表分析应收账款和应付账款、如何根据账龄提取坏账准备并进行坏账分析等。情景引入目录CONTENTS应收账款的管理与分析1坏账提取与分析2应付账款的统计3任务一应收账款的管理与分析知识准备应收账款是指企业在正常的经营过程中因销售商品、产品、提供劳务等业务而应向购买单位收取的款项，包括应由购买单位或接受劳务单位负担的税金、代购买方垫付的各种运杂费等。加强对应收账款的管理已经成为企业财务管理的一项重要工作，在进行应收账款管理之前，首先需要建立应收账款明细表;然后建立应收账款账龄分析表，加强应收账款的管理;最后，为了方便数据对比，可以建立账龄统计分析图。任务目标(1)建立应收账款明细表；(2)按账龄统计应收账款；(3)建立应收账款账龄统计分析图。任务资料福源公司是一家生产制造企业,本公司日常合作的企业如表6-1所示。自2020年以来，公司的应收账款数据如表6-2所示。任务资料任务操作(1)启动Excel2010，创建一个新的工作簿，命名为“应收账款”。将Sheetl工作表命名为“客户代码”，将公司的客户信息输入该表中，如图6-1所示。1.建立应收账款明细表

图6-1新建工作表任务操作(2)将Sheet2工作表重命名为“应收账款明细表”，根据实际业务发生情况在其中输入应收账款明细数据，并输入列标题，即交易日期、客户代码、客户名称、应收金额、本期已收金额、未收款金额、付款期限。将A列设置为“长日期”格式，将D、E、F三列设置为“货币”格式，将G列设置为自定义“0”天””格式，如图6-2所示。根据实际业务发生情况将数据输入表格中，如图6-3所示。1.建立应收账款明细表任务操作

图6-2设置数据格式1.建立应收账款明细表

图6-3输入数据任务操作(3)引用“客户代码”工作表中的“公司名称”列数据。在“应收账款明细表”工作表C2单元格中输入公式“=VLOOKUP(B2,客户代码!$A$2:$B$16.2.FALSE)”,按Enter键。将鼠标指针放在C2单元格右下角,向下拖动填充柄填充单元格,完成数据的录入,如图6-4所示(这种方法适用于公司重复的业务较多的情况，如果公司业务很少是重复的，则不用建立客户代码，直接录入即可)。1.建立应收账款明细表任务操作

图6-4填充“客户名称”列1.建立应收账款明细表任务操作(4)假设本期已收金额都是应收金额的10%，在E2单元格中输入“=D2*10%”，按Enter键，将鼠标指针放在E2单元格右下角，向下拖动填充柄填充单元格，完成数据的录入，如图6-5所示。1.建立应收账款明细表

图6-5填充“本期已收金额”列任务操作

图6-6填充“未收款金额”列(5)计算未收款金额。在F2单元格中输入“=D2-E2”，按Enter键，将鼠标指针放在F2单元格右下角，向下拖动填充柄填充单元格，完成数据的录入，此时整张应收账款明细表即完成，如图6-6所示。1.建立应收账款明细表任务操作随着经济的发展，赊销经济行为也逐渐兴起，企业应收账款总量呈持续上升趋势，并逐渐成为制约企业健康、稳定发展的主要“瓶颈”，所以企业应当加强对应收账款的管理。为了方便管理，很多企业会选择建立应收账款账龄分析表。账龄分析表是以账龄为序对应收账款分类列示的表格，可较全面地显示应收账款账龄的分布情况，以此来判断企业应收账款的总体质量以及不同客户的信用状况，为企业后续赊销政策和赊销判断提供依据。通过账龄分析表可以了解不同客户应收账款发生时间的长短，反映不同客户的信用状况、拖欠情况等。拖欠账款时间越长，发生坏账的可能性越大。2.按账龄统计应收账款任务操作建立应收账款账龄分析表的方式主要有两种，一种是根据企业需求设置表格，运用函数公式进行计算;另外一种是制作数据透视图。这里主要介绍采用数据透视图的形式制作应收账款账龄分析表，感兴趣的学习者也可以自行研究运用函数公式来制作应收账款账龄分析表。2.按账龄统计应收账款任务操作(1)计算账龄。在“应收账款明细表”工作表中新增一列“账龄”，在H2单元格中输入公式“=TODAYO)-A2”，按Enter键，得出数据。将鼠标指针放在H2单元格右下角，向下拖动填充柄填充数据，如图6-7所示(如果计算得出的结果是日期或者其他形式，则右击，在弹出的快捷菜单中选择“设置单元格格式”命令，把单元格格式改为常规或者文本即可)。2.按账龄统计应收账款(2)创建数据透视表。选中“客户名称”列，选择“插入”选项卡，执行“表格”组中的“数据透视表”命令,在打开的“创建数据透视表”对话框中设置数据，单击“确定按钮，如图6-8所示。任务操作2.按账龄统计应收账款

图6-7计算账龄

图6-8创建数据透视表任务操作2.按账龄统计应收账款(3)设置数据透视表字段。设置行字段为“客户名称”，列字段为“账龄”，数值字段为“未收款金额”，如图6-9所示。

图

6-9

设置数据透视表字段任务操作2.按账龄统计应收账款(4)设置完成就可以得到数据透视图，从图中可以清晰地看出应收账款的欠账情况(还可以进行数据设置，选中数据透视表中账期为“371”的单元格，右击，在弹出的快捷菜单中选择“创建组”命令，设置账期区间，如图6-10所示)，如图6-11所示。

图

6-10选择“创建组”命令任务操作2.按账龄统计应收账款

图6-11数据透视图效果任务操作3.建立应收账款账龄统计分析图为了使应收账款的信息更为直观，还可以建立各种图形。采用图形不仅使数据更直观，同时可以使数据更具有对比性。这里制作两个图形，用条形图反映未收款金额，用饼图反映账龄。(1)选中C列和H列,选择“插入”选项卡,单击“图表”组右下角的启动器,打开“插入图表”对话框，选择“饼图”，单击“确定”按钮，如图6-12所示。任务操作3.建立应收账款账龄统计分析图

图6-12选择“饼图”任务操作3.建立应收账款账龄统计分析图(2)单击形成的饼图(形成的饼图可以随意移动)，会激活“图表工具”选项卡，在其下有三个子选项卡,即“设计”“布局”“格式”。通过“设计”子选项卡,可以进行图表颜色、图表布局等(在图上是否显示百分比)的选择;通过“布局”子选项卡,可以进行图表标题、数据标签、图例等的选择;通过“格式”子选项卡，可以进行字体、颜色、形状等的选择,如图6-13所示。任务操作3.建立应收账款账龄统计分析图

图6-13美化饼图任务操作3.建立应收账款账龄统计分析图(3)选中C列和F列,选择“插入”选项卡,单击“图表”组右下角的启动器,打开“插入图表”对话框，选择“条形图”，单击“确定”按钮，如图6-14所示。

图6-14选择“条形图”任务操作3.建立应收账款账龄统计分析图(4)单击形成的条形图，会激活“图表工具”选项卡，在其下有三个子选项卡，即“设计”“布局”“格式”。通过“设计”子选项卡，可进行图表颜色、图表布局等(在图上是否显示百分比)的选择;通过“布局”子选项卡，可进行图表标题、数据标签图例、横纵坐标、坐标轴、网格线、背景等的选择;通过“格式”子选项卡，可进行字体、颜色、形状等的选择，如图6-15所示(这一步主要是美化工作，形成的条形图可以随意移动)。任务操作3.建立应收账款账龄统计分析图

图6-15美化条形图任务二坏账提取与分析知识准备当应收账款收不回时，坏账就产生了。坏账损失是指企业未收回的应收账款、经批准列入损失的部分。提取坏账准备的企业也在本账户反映，发生坏账时直接抵减坏账准备。不提取坏账准备的企业发生坏账时，直接在本账户核算。企业的坏账损失应根据《财政部关于建立健全企业应收款项管理制度的通知》(财企(2002)513号)的规定确认。坏账损失及其核算是应收账款核算的一个重要方面。知识准备一笔应收账款在什么时候才能被确认为坏账，其条件通常是由会计准则或制度给出的。不论会计准则或制度如何变化，在会计实务中，坏账的确认都要遵循财务报告的基本目标和会计核算的一般原则，尽量做到真实、准确,切合本单位的实际。一般来说,应收账款符合下列条件之一的,就应将其确认为坏账:(1)债务人死亡，以其遗产清偿后仍然无法收回的账款。(2)债务人破产，以其破产财产清偿后仍然无法收回的账款。(3)债务人较长时期内未履行其偿债义务，并有足够的证据表明无法收回或收回可能性极小的账款。知识准备上述三个条件中的每一个条件都是充分条件，其中第三个条件需要会计人员做出职业判断。我国现行制度规定，上市公司坏账损失的决定权在公司董事会或股东大会。坏账损失的核算方法有两种:一是直接转销法，二是备抵法。直接转销法是指在坏账损失实际发生时,直接借记“资产减值损失-坏账损失"科目，贷记“应收账款”科目。这种方法核算简单，不需要设置“坏账准备”科目。关于直接转销法，我们还应掌握以下两个要点:第一,该法不符合权责发生制和配比原则;第二，在该法下,如果已冲销的应收账款以后又收回,应做两笔会计分录,即先借记“应收账款科目，贷记“资产减值损失-坏账损失”科目;再借记“银行存款”科目，贷记“应收账款”科目。知识准备采用直接转销法的缺点:①只有实际发生的坏账才确认为当期费用，导致资产不实，各期损益不实，不能真实反映企业的财务状况;②在期末资产负债表上，应收账款是按照账面余额而不是账面价值反映，采用直接转销法在一定程度上扭曲了财务状况。结合以上原因，我国会计准则规定确定应收款项减值(信用减值损失)只能采用备抵法，不能采用直接转销法。知识准备备抵法是指在坏账损失实际发生前，就依据权责发生制原则估计损失，并同时形成坏账准备，待坏账损失实际发生时再冲减坏账准备。估计坏账损失时，借记“信用减值损失”科目，贷记“坏账准备”科目;坏账损失实际发生时(符合前述的三个条件之一)，借记“坏账准备”科目，贷记“应收账款”科目;已确认并转销的应收款项以后又收回时，做相反分录即可,即借记“应收账款”,贷记“坏账准备”科目,同时借记“银行存款”科目，贷记“应收账款”科目。知识准备至于如何估计坏账损失，则有三种方法可供选择，即年末余额百分比法、账龄分析法和销货百分比法。应用年末余额百分比法时，坏账准备的计提(坏账损失的估计)分首次计提和以后年度计提两种情况。首次计提时，坏账准备提取数=应收账款年末余额x计提比例。(1)设置公式计算坏账提取比例和应提取坏账金额；(2)创建坏账统计表格并统计坏账金额所占比例；(3)创建应提取坏账分析图表。任务目标任务资料福源公司根据应收账款的账龄进行坏账的提取，即在信用期内的不提取坏账，超过信用期1~30天的按2%提取，超过信用期31~90天的按6%提取，超过信用期91~180天的按9%提取，超过信用期181~360天的按14%提取，超过信用期360天以上的按25%提取坏账。截至2022年2月23日，福源公司应收账款余额情况如表6-3所示。任务资料续表任务操作(1)打开新的Excel工作簿,命名为“坏账”,将Sheetl工作表命名为“坏账提取金额”,将上述应收账款余额输入表格中,并增加两列,即“坏账提取比例”“坏账金额”。选择“开始”选项卡,执行“字体”组中的“所有边框”命令,给表格加上边框;选择“开始”选项卡，执行“对齐方式”组中的“合并并居中”命令,对标题栏进行设置,如图6-16所示(“日期”列的内容可以通过输入公式“=TODAYO)”得到)。1.设置公式计算坏账提取比例和应提取坏账金额任务操作1.设置公式计算坏账提取比例和应提取坏账金额

图6-16输入基础数据并设置格式任务操作1.设置公式计算坏账提取比例和应提取坏账金额(2)选中F列，选择“开始”选项卡，在“数字”组中将“坏账提取比例”列的数字格式设置为“百分比”，如图6-17所示;选中G列，将“坏账金额”列的数字格式设置为“货币”。

图6-17设置数字格式任务操作(3)计算坏账提取比例。按照任务资料中设定的提取比例，在F4单元格中输入公式“=IF(E4<0,0,IF(E4<=30,2%,IF(E4<=90,6%,IF(E4<=180,9%,IF(E4<=360,14%,25%)))))”,按Enter键，得出数据。将鼠标指针放在F4单元格右下角，向下拖动填充柄填充整列数据,如图6-18所示。(4)计算坏账金额。在G4单元格中输入公式“=D4*F4”，按Enter键，得出数据。将鼠标指针放在F4单元格右下角，向下拖动填充柄填充整列数据，如图6-19所示。1.设置公式计算坏账提取比例和应提取坏账金额任务操作1.设置公式计算坏账提取比例和应提取坏账金额

图6-18计算坏账提取比例

图6-19计算坏账金额任务操作2.创建坏账统计表格并统计坏账金额所占比例(1)打开“坏账”工作簿，将Sheet2命名为“坏账分析表”，将“客户名称”列从“应收账款”工作簿“客户代码”工作表中粘贴复制过来，增加两列“坏账金额”和“比例”最后进行美化工作(标题栏合并单元格，调大字体,给表格加上边框)，如图6-20所示。

图6-20新建工作表任务操作2.创建坏账统计表格并统计坏账金额所占比例(2)在C3单元格中输入公式“=SUMIF(坏账提取金额!$C$4:$C$28,坏账分析表!$A3坏账提取金额!$G$4:$G$28)”，按Enter键，得出数据。将鼠标指针放在C3单元格右下角，向下拖动填充柄填充数据，如图6-21所示。

图6-21计算坏账金额任务操作2.创建坏账统计表格并统计坏账金额所占比例(3)在A18单元格增加一行“合计”，添加上边框。选中B18单元格，选择“开始选项卡，执行“编辑”组中的“自动求和”命令，按Enter键，得到坏账金额的合计，如图6-22所示(如果自动求和的单元格区域错误，可以手动进行调整)。

图6-22计算坏账金额的合计任务操作2.创建坏账统计表格并统计坏账金额所占比例

图6-23计算比例(4)计算比例。在C3单元格中输入公式“=B3/$B$18”，按Enter键，得到数据。将鼠标指针放在C3单元格右下角，向下拖动填充柄填充数据，如图6-23所示。任务操作2.创建坏账统计表格并统计坏账金额所占比例

图6-24设置数据一(5)调整“比例”列数据。选中C列,选择“开始”选项卡,执行“数字”组中的“%命令，得到调整为百分比的数据，如图6-24和图6-25所示。

图6-25设置数据二任务操作3.创建应提取坏账分析图表下面为提取的坏账金额创建折线图和柱形图并进行比较。1)折线图(1)选中要创建图表的A2:B17单元格区域，选择“插入”选项卡，执行“图表”组中的“折线图”命令，选择一个合适的样式，如图6-26所示。

图6-26选择折线图样式任务操作3.创建应提取坏账分析图表1)折线图

图6-27美化折线图(2)选中折线图，激活“图表工具”选项卡，在其下有三个子选项卡，即“设计”“布局”“格式”。通过“设计”子选项卡,可进行图表颜色、图表布局(在图上是否显示百分比)等的选择;通过“布局”子选项卡,可进行图表标题、数据标签、图例等的选择;通过“格式”子选项卡，可进行字体、颜色、形状等的选择，如图6-27所示。任务操作3.创建应提取坏账分析图表2)柱形图

图6-28选择柱形图样式(1)选中要创建图表的A2:B17单元格区域，选择“插入”选项卡，执行“图表”组中的“柱形图”命令，选择一个合适的样式，如图6-28所示。任务操作(2)选中折线图，激活“图表工具”选项卡，在其下有三个子选项卡，即“设计”“布局”“格式”。通过“设计”子选项卡,可进行图表颜色、图表布局(在图上是否显示百分比)等的选择;通过“布局”子选项卡,可进行图表标题、数据标签、图例等的选择;通过“格式”子选项卡，可进行字体、颜色、形状等的选择。双击表格的纵轴数据，打开“设置坐标轴格式”对话框，可以在“坐标轴选项”选项卡中更改坐标比例，使图形更美观，如图6-29所示;在“数字”选项卡中选择数字的格式。3.创建应提取坏账分析图表2)柱形图任务操作图6-29美化柱形图任务三应付账款的统计知识准备应付账款用以核算企业因购买材料、商品和接受劳务供应等经营活动应支付的款项是指因购买材料、商品或接受劳务供应等而发生的债务，这是买卖双方在购销活动中由于取得物资与支付货款在时间上不一致而产生的负债。应付账款主要包括购买货物的欠款应交未交的税款以及为销售方代垫的相关费用。本科目应当按照不同的债权人进行明细核算。企业应当加强对应付账款的管理，以避免发生财务危机，维护企业信誉。任务目标(1)创建应付账款分析表；(2)创建应付账款分析图。任务资料福源公司的应付账款信息如下:2022年2月10日，与虹光有限责任公司发生交易，金额总计49000元，约定的付款期限为10天;2022年2月18日，与缘来有限责任公司发生交易，金额总计157950元，约定的付款期限为30天;2022年2月25日，与天奇有限公司发生交易，金额总计119340元，约定的付款期限为30天。任务操作应付账款分析表主要包括对应付金额的分析、应付账款的账期的分析以及应付账款金额比例的计算等内容。(1)新建一个工作簿，命名为“应付账款”，将Sheet1工作表重命名为“应付账款分析表”,在工作表中添加标题和列标题(客户名称、交易日期、付款期限、应付金额、未到期等)。选择“开始”选项卡，执行“对齐方式”组中的“合并并居中”命令，在“字体”组中调整标题的字体并给表格加上边框，如图6-30所示。1.创建应付账款分析表图6-30输入基础数据并设置格式任务操作(2)设置日期。选中H2单元格,输入公式“=TODAYO)”或者“=NOWO)”,按Enter键，即可返回当前的日期和时间，如图6-31所示。通过公式设置的日期是随着系统的时间变化而变化的。1.创建应付账款分析表

图6-31设置日期任务操作1.查询员工工资数据【提示】NOW函数:返回当前日期和时间所对应的序列号。函数语法:=NOWO)TODAY函数:返回系统当前日期的序列号。函数语法:=TODAY()两个函数的区别在于TODAY函数返回的是当前日期，NOW函数返回的是当前日期与时间。任务操作(3)根据背景资料将企业尚未支付的应付账款的数据信息输入相应的表格中,如图6-32所示。2.科目汇总表数据图6-32输入数据任务操作(4)设置格式。选中D5:18单元格区域，选择“开始”选项卡，单击“数字”组中的下三角按钮，在下拉列表中选择“会计专用”格式，将“交易日期”和“付款期限”设置为“长日期”格式，如图6-33所示。(5)计算未到期金额。在E5单元格中输入公式“=IF(C5>$H$2.D5.0)”(如果C5>SHS2则返回D5单元格;如果不是，则返回0)，按Enter键，得出数据。将鼠标指针放在E5单元格右下角，向下拖动填充柄填充数据，如图6-34所示。1.查询员工工资数据任务操作1.查询员工工资数据

图6-34计算未到期金额图6-33设置格式任务操作1.查询员工工资数据(6)计算企业逾期尚未支付的应付账款金额。在F5单元格中输入公式“=IF(AND(SHS2-$C5>0.$H$2-$C5<=20),$D5,””)”，按Enter键，得出数据，如图6-35所示。在G5单元格中输入公式“=IF(AND($H$2-$C5>20,$H$2-$C5<=40),$D5,”")”,按Enter键，得出数据。在H5单元格中输入公式“=IF(AND(SH$2-$C5>40,$H$2-$C5<=60),$D5,”")”，按Enter键，得出数据。在I5单元格中输入公式“=IF(SHS2-$C5>60,$D5,”")”,按Enter键,得出数据(公式中的“”””表示错误，返回空格)。任务操作1.查询员工工资数据

图6-35计算企业逾期未支付的应付账款金额任务操作1.查询员工工资数据(7)填充账龄，分析其余的单元格。选中F5:I5单元格区域，将鼠标指针放在15单元格右下角，向下拖动填充柄填充数据，如图6-36所示。

图6-36填充数据任务操作1.查询员工工资数据(8)计算应付账款合计。选中D8单元格，输入公式“=SUM(D5:D7)”，按Enter键得出数据。将鼠标指针放在D8单元格右下角，向右拖动填充柄填充数据，如图6-37所示。

图6-37计算应付账款合计任务操作1.查询员工工资数据(9)计算应付账款比例。选中D9单元格，输入公式“=D8/$D$8”，按Enter键，得出数据，如图6-38所示。

图6-38计算应付账款比例任务操作1.查询员工工资数据(10)设置数字格式并填充E9:I9单元格区域。选中D9单元格，选择“开始”选项卡,执行“数字”组中的“%”命令，将鼠标指针放在D9单元格右下角，向右拖动填充柄填充数据，如图6-39所示。

图6-39设置数字格式并填充E9:I9单元格区域任务操作2.创建应付账款分析图

图6-40新建工作表创建完应付账款分析表之后，为了反映数据之间的关系，接下来根据计算完成的应付账款账期分析结果和表格建立应付账款分析图。(1)将Sheet2重命名为“应付账款分析图”，如图6-40所示。任务操作2.创建应付账款分析图

图6-41选择柱形图(2)选择“插入”选项卡，执行“图表”组中的“柱形图”命令，在下拉列表中根据需求选择一个图形，创建一个空白图表，如图6-41所示。任务操作2.创建应付账款分析图

图6-42执行“数据选择”命令(3)选择数据。选中生成的空白柱形图,选择“图表工具-设计”选项卡,执行“数据组中的“选择数据”命令，如图6-42所示。任务操作2.创建应付账款分析图

图6-43选择数据(4)打开“选择数据源”对话框，单击“图表数据区域”文本框右侧的“折叠”按钮,如图6-43所示。任务操作2.创建应付账款分析图

图6-44选中单元格区域(5)随即“选择数据源”对话框处于折叠状态，切换到“应付账款分析表”工作表,按住Ctl键,选中单元格区域“=应付账款分析表!SES4:$IS4.应付账款分析表!SES8:SIS8”如图6-44所示。任务操作2.创建应付账款分析图图6-45图表数据区域选择完成(6)单击文本框右侧的“展开”按钮,“选择数据源”对话框即处于展开状态,如图6-45所示。任务操作2