Excel在会计中的应用实训指导书：往来账款管理实务.doc

项目五 固定资产管理实务往来账款管理实务工作目标往来账款是企业在经济业务活动中发生的应收、应付、暂收、暂付款，往来账款是企业资产、负债的一个组成部分。往来账款在单位会计核算中涉及的内容多、项目杂、往来客户繁多而且变化大。要快速了解往来账款的情况比较困难。运用Excel就可以系统地、有序地、快速地开展工作，而且具有易于保存、工作效率高的优点。通过完成本项且的六个工作任务，使学习者能够利用Excel进行往来账款管理系统工作簿的制作，并从中学习掌握有关函数的应用、不同类型图表的制作和编辑，回顾数据名称的定义等基本操作方法。工作思路本项目的工作思路是建立往来业务管理系统工作簿，创建完成各种基本表页，如“往来客户表”、“供应商往来表”等；编制完成应收款项表和应付款项表；对应收、应付账款进行账龄分析；建立有关图表、图形进行分析。其中，应收账款管理流程图如下：建立基本表格应收账款分析应收账款管理逾期应收账款的分析应收账款账龄分析IF函数图表背景资料某企业为生产型中小企业，现欲构建一个企业的往来账款管理系统，该企业迄今有关往来款的基本资料如下：【资料1】 客户及应收款信息客户代码客户名称发票号码期初余额开票日期收款期限(天)k01新飞科技A301220,000.002003-11-1190k02康域实业D64985,000.002001-10-1380k03河南实业B50195,000.002002-1-230k04天美服饰P55984,000.002002-12-2430k05宝家商场L2354200,000.002003-6-2320k06新型大厦N634610,000.002003-6-330k07宜家房产H88791,000.002003-6-290k08快乐超市T8967500.002003-8-190【资料2】 供应商及应付款信息供应商代码供应商名称发票号码应付款金额已付款金额开票日期付款期限(天)G01诺亚电脑B332150,000.003,000.002003-7-11120G02历程电脑H35543,000.000.002003-10-1390G03正大机电L234512,000.006,000.002003-1-250G04民生百货N25428,000.002,000.002003-6-470G05天天实业T9834100,000.0030,000.002003-12-230G06立新百货A943640,000.0010,000.002003-8-350G07南方集团D658311,000.000.002003-5-4120G08利民实业T27734,300.003,000.002003-2-1090工作准备首先建立一个名为“班级姓名项目五.xls”的工作簿，在此工作簿中建立“往来客户表”、“应收款项表”、“供应商往来表”、“应付款项表”等工作表，各工作表分别完成相应的工作任务。根据这些表格再对应收款项和应付款先进性账龄分析。下面介绍具体的做法和步骤。工作任务一 应收款项表的创建项目活动一 应收款项表的建立应收账款是指企业因销售商品、产品或提供劳务等原因，应向购货客户或接受劳务的客户收取的款项或代垫的运杂费等。在会计上，应收账款应于收入实现时予以确认。在市场经济发达的今天，对于企业应收账款的管理是至关重要的，它关系到企业能否及时收回资金，能否有效地利用资金。因此应收账款管理是企业管理的一个重要方面。应收款项表的具体建立步骤如下；(1)在工作簿中的“往来客户表”里面，根据【资料1】中的内容输入相应的标题内容。完成后效果如图(2)根据【资料1】中的内容输入相应的客户代码、客户名称和期初余额。(3)单击C3单元格，在公式输入栏中输入“=IF(D3=0,借)”，显示相应的计算结果后用Excel的自动填充功能自动填充C列。(4)单击“往来客户表”工作表，选择单元格区域A3：D10。(5)单击菜单拦中的【插入】-【名称】-【定义】，弹出【定义名称】对话框，在“在当前工作簿中的名称”中输入“往来客户表”，单击【添加】按钮，再单击【确定】。(6)在“应收款项表”里面，输入【资料l】中的相应内容。(7)根据【资料l】在A列输入客户代码。(8)单击B2单元格，应用Vlookup函数，提取“往来客户表”中的客户名称，函数参数的“Lookup_value”设置为“A3”，“Table_array”设置为“往来客户表”，“Col_index_num”设置为“2”。(9)利用自动填充功能将该列其他单元格的数据引入。(10)根据【资料1】中所给的数据，输入“应收款项表”的其他内容。如果要修改日期格式，则单击E3单元格，选择菜单栏中的【格式】-【单元格】，在弹出的【单元格格式】对话框中选择【数字】选项卡，在“分类”中选择“日期”，则在右侧的“类型”中可以选择任意想要的日期格式。(11)原始数据输入完成后，分别在G2、H2、I2、J2、K2、L2、M2单元格中输入“到期日”、“是否到期”、“未到期”、“060天”、“61120天”、“121365天”、“366720天”。单击G3单元格，在公式编辑栏中输入公式“=E3+F3”（到期日=开票日期+收款期限），按回车键，则G3单元格便显示到期日期。(12)使用自动填充功能输入G列所有客户应收账款的到期日。(13)选中A1:M1，单击工具栏的【合并及居中按钮】，输入“应付款项表”，设置其格式。(14)选中A11:H11，单击工具栏的【合并及居中】按钮，输入“应收款合计：”。(15)单击G12单元格，输入“今天日期：”。假设当前日期为2003-12-11，输入到I12单元格中。项目活动二 判断应收款项是否到期利用IF函数，判定各个客户的应收款是否到期。如果到期日小于当前日期，则说明应收账款已经到期；如果到期日大于当前日期，则说明该应收款还未到期。具体步骤如下：(1)单击H3单元格，在公式编辑栏中输人“=IF(G3I12,是,否)”，按回车键后显示应收款项是否到期。(2)使用自动填充功能填充H列，判断各个客户的应收款项是否到期。工作任务二 应收账款账龄分析表项目活动一 应收账款账龄设置应收账款账龄分析是依据企业每一笔应收账款的账龄划分账龄组来进行的，那么，当不同顾客拖欠的多笔应收账款中存在多种账龄时，所拖欠的应收账款就可能会被划分为不同的账龄组。应收账款账龄的具体设置步骤如下：(1)单击I3单元格，在公式编辑栏内输入公式“=IF(I12-G30,I12-G361,I12-G3121,I12-G3366,I12-G3=720),D3,-)。IF函数的含义在于，若满足账龄判断条件，则返回值为该笔应收款的金额，若不满足条件，则返回值为“-”(6)利用IF函数将其他应收账款分别进行账龄的分类，设置显示格式。(7)单击I11单元格，在公式编辑栏输入公式“=SUM(I3:I10)”后按回车键，在I11单元格即显示为未到期的应收款相加。(8)利用自动填充功能将公式复制到J11、K11、L11、M11，让各个不同账龄区间的应收款相加填入到这几个单元格。项目活动二 应收账款账龄分析表的创建应收账款账龄分析是在应收账款的日常管理中，企业通过掌握的每个客户的应收账款的账龄，并依据账龄的长短来合理制定应收账款的催收制度和进行坏账准备提取。具体分析步骤如下：(1)在工作簿中，新建名为“应收款账龄分析表”的工作表。选择A1:C1，点击工具栏的【合并及居中】按钮，输入应收款账龄分析表，设置格式。分别在A3、B3、C3单元格中输入“账龄”、“”、“”(2)选择“应收款项表”工作表中的I2：M2区域，单击右键进行复制。(3)单击“应收款账龄分析表”工作表中A4单元格，单击右键选择【选择性粘贴】，在弹出的对话框中选择“粘贴”-“数值”、“运算”-“无”，并在“转置”前的方框内打钩，这样就将账龄区域垂直复制到A4：A8区域。(4)输入应收款金额，方法同账龄输入方法。单击A9单元格，输入“合计”，在B9单元格公式编辑栏内输入“=SUM(B4:B8)”。最后的结果如图所示。(5)单击C4单元格，在公式编辑栏内输人公式“=B4/B9”，按回车键后即显示结果。应收款账龄分析主要是分析不同账龄的应收戟在总应收款中所占的比例，因此，应收款账龄分析表内的“百分比”一栏为不同账龄区内的应收款占应收款合计的比例。(6)单击菜单栏中的【格式】-【单元格】，在弹出的【单元格格式】对话框中选择【数字】选项卡，“分类”选择“百分比”，“小数位数”选择“2”。(7)将整个C列各个账龄按此方法计算相应的百分比。最终结果如图所示。项目活动三 坏账准备计提坏账准备的核算方法一般有两种：直接转销法和备抵法。我国企业会计制度规定，企业只能采用备抵法核算坏账准备。备抵法是按期估计坏账损失，形成坏账准备。当某一应收账款全部或部分被确认为坏账时，应根据坏账金额冲减坏账准备，同时转销相应的应收账款金额。使用备抵法对坏账损失进行估计的方法有余额百分比法、账龄分析法、赊销百分比法和个别认定法四种。假设该企业2003年12月11日“坏账准备”科目余额为贷方金额4023元，使用账龄分析法对坏账损失进行估计，具体步骤如下：(1)在工作簿中插人新的工作表，重命名为“坏账准备计提表”，在A1、B1、C1、D1单元格分别填入“账龄”、“应收款金额”、“估计损失比率”、“估计损失金额”。并将应收款的账龄及应收款金额录入，如图所示。(2)在C2:C6中分别录入估计损失比率“0.5%”、“1%”、“2%”、“3%”、“5%”如图所示。(3)单击D2单元格，在公式编辑栏内输人公式“=B2*C2”(估计损失金额一应收款金额估计损失比率)，按回车键后即显示结果。(4)利用自动填充功能计算不同账龄应收款的估计损失金额。选择B7单元格，利用自动填充功能将公式复制到C7单元格和D7单元格，如图所示。(5)如上图所示，企业计算出的2003年12月11日“坏账准备”的账面金额应为7045面，企业要根据前期“坏账准备”科目的账面余额来计算本期应入账金额。原有坏账准备贷方余额为4023元，所以本期调整分录的金额为7035-4023=3012(元)。企业应做如下调整分录：借：管理费用 3012 贷：坏账准备 3012工作任务三 建立应收票据账期金额分析图票据是由出票人无条件地承诺由自己或委托他人支付一定金额的有价证券。按照票据法的规定，票据包括汇票、本票和支票三种。随着市场经济的迅速发展和社会竞争的加剧，越来越多的企业已经采取通过票据实现应收、应付款的商业往来。因此，对票据实行管理就越发显得重要。应收票据是指企业在采用商业汇票结算方式时，因销售商品、产品或提供劳务而收到的商业汇票。对应收票据实施管理的第一步就是建立应收票据账期数据。假设工作任务三中的应收款都由商业汇票支付，对应收票据金额进行具体分析的步骤如下：(1)选择“应收款项表”中需要分析的单元格区域I2：M2和I11：M11（可按住Ctrl选择不连续区域），单击菜单栏中的【插入】-【图表】命令。(2)在弹出的对话框【图表向导】中选择“图表类型”-“柱形图”，在右边的子图表类型里面选择三位堆积柱形图，图所示。(3)单击【下一步】，显示“数据区域”,如图所示，由于数据区域已经选好，不用再做修改。(4)单击【下一步】在弹出的【图表向导】中选择【标题】选项卡，在“图表标题”输入“应收票据账期分析图”，在“分类(x)轴”输入“到期天数”，在“数值(y)轴”输入“应收金额”，如图所示。(5)单击【下一步】，勾选“作为新的工作表插入”，如图所示。(6)单击【完成】，将新工作表chartl调整到合适的位置，并将该工作表改名为“应收票据账期分析图”，如图所示。如果数轴标题显示不正确，可以先单击标题文字，再双击文字边框，进行设置。(7)单击图表外围任意白色区域，单击鼠标右键，在弹出的菜单中选择【图表选项】。(8)在弹出的【图表选项】对话框中选择【数据标志】选项卡，勾选在“值”前面的复选框，单击【确定】按钮。(9)在数据区域单击鼠标右键，选择【绘图区格式】，在弹出的【绘图区格式】对话框中选择合适的颜色，同时可单击【填充效果】对话框中选择自己需要的效果。(11)两次不连续单击图表中的任意柱形，单击鼠标右键，在弹出菜单中选择【数据系列格式】，可以在弹出的【数据系列格式】对话框中进行图形颜色、边框等设置。(12)设置完毕后单击【确定】，可继续对其他柱形图进行设置。工作任务四 应付款项表的创建项目活动一 应付款项表的建立应付账款是指企业因购买材料、商品或接受劳务等应当支付给货物提供者或劳务提供者的款项，是在商品交易中买方先取贷后付款时发生的一种信用形式。企业要避免财务危机、维护企业信誉，就一定要加强应付账款的管理。建立应付款项表的具体步骤如下：(1)在“应商往来表”工作表中，根据【资料2】中应付账款情况表输入相应的内容。(2)根据【资料2】的信息输人相应的供应商代码、供应商名称和应付金额。(3)单击“供应商往来表”工作表，选择单元格区域A3：C10。(4)单击菜单栏中的【插入】-【名称】-【定义】，在【定义名称】对话框中，在“在当前工作簿中的名称”中输入“供应商往来表”，单击【添加】按钮，再单击【确定】。(5)在“应付款项表”工作表中，根据【资料2】和下图输入相应的内容。(6)单击B3单元格，应用“Vlookup”函数，按照前面的方法，提取“供应商往来表”中供应商名称和应付款金额。参数“Lookup_value”填入“A3”，“Table_array”填入“供应商往来表”，“Col_index_num”填入“2”。(7)根据【资料2】所给的数据，输入“应付款项表”的其他内容。(8)单击F3单元格，在公式编辑栏输入“=D3-E3”（未付款金额=应付款金额-已付款金额），单击回车键，则在F3单元格计算出未付款金额。(9)利用自动填充功能将该列其他供应商的未付款金额计算机出来。(10)单击I3单元格，在公式编辑栏输入公式“=G3+H3”（到期日期=开票日期+付款期限），按回车键，则I3单元格便显示到期日期。(11)使用自动填充功能将该列的其他供应商应付款项到期日计算出来。项目活动二 判断应付款项是否到期假设当前日期为2003年12月11日(J12单元格)，利用IF函数，判断各个客户的应付款项是否到期；如果I列的日期小于当前日期，则该应付账款来到期；如果J列的日期大于当前日期，则该应付账数到期。判断应付款项是否到期的具体步骤如下：(1)单击I12单元格，输入“今天日期：”，单击J12单元格，输入“2003-12-11”。单击J3单元格，在公式编辑栏中输入“=IF(I3J12,是,否)”，按叫车键后显示是否到期。(2)运用同样方法判断各个供应商的应付款项是否到期。工作任务五 应付账款账龄分析表项目活动一 应付账款账龄设置应付账款账龄分析是依据企业每一笔应付账款的账龄划分账龄组来进行的，那么，当企业拖欠不同供应商多笔应付账款中存在多种账龄时，所拖欠的应付而未付的账款就可能会被划分为不同的账龄组。具体设置步骤如下：(1)在K2：O2等单元格输入企业应付账款的账龄区间，分为“未到期金额”、“030天”、“3160天”、“6190天”、“90天以上”等几个时问区间，如图所示。(2)单击K3单元格，在公式编辑栏内输人公式“=IF(I3-J120,J12-I330,J12-I360,J12-I390,F3,-)”。IF函数的含义在于，若满足账龄判断条件，则返回值为该笔应付款的未付款金额，若不满足条件则返回值为“-”。(8)合并A11:J11单元格，填入“应付账款合计：”。单击K11单元格，在公式编辑栏输入公式“=SUM(K3:K10)”后按回车键在K11单元格即显示为未到期的应付款金额相加。(9)利用自动填充功能将公式复制到L11、M11、N11、O11，将各个不同账龄区间的应付款相加填入这几个单元格，如图所示。项目活动二 应付款账龄分析表的创建应付款账龄分析主要是分析不同账龄的应付款未付金额在总应付款未付金额中所占的比例。因此，应付款账龄分析表内的百分比一栏为不同账龄区内的应付款未付金额占应付款未付金额合计的比例。创建应付款账龄分析表的具体操作步骤如下：(1)单击菜单栏的【插入】-【工作表】，插入一张新的工作表，将其调整到合适的位置并重命名为“应付款账龄分析表”，并输入账龄分析表有关内容，如图所示。其中A4:A8，B4:B8的内容可以用前面介绍过的“选择性粘贴”方法来填写。(2)单击B9单元格，在公式编辑栏输入公式“=SUM(B4:B8)”，按回车键。(3)单击C4单元格，在公式编辑栏输入公式“=B4/B9”，按回车键后显示结果。此时显示结果为小数，设置单元格格式让其显示为百分比数。利用自动填充功能计算C列其他的百分比。工作任务六 建立应付款账期金额分析图在对应付款进行了详细的分析之后，为了能够更直观、清楚地了解应付款账期金额的分析情况，还需要创建一个应付款账期金额分析图，以图表的形式展现账期金额情况。具体建立步骤如下：(1)选择“应付款项表”中需要分析的单元格区域K2；02和K11：O11（可按住Ctrl键进行不连续区域的选择），单击菜单栏中的【插入】-【图表】命令，在弹出的【图表向导】对话框中选择“图表类型”-“柱形图”。(2)单击【下一步】，显示“数据区域”。由于数据区域已经选好，不用再做修改。(3)单击【下一步】，选择【标题】选项卡，在“图表标题”输入“应付款账龄分析图”，在“分类(X)轴”输入“应付款未付金额”，在“数值(y)轴”输入“账龄期间”，如图所示。(4)单击【下一步】，将该图表作为新工作表插入。(5)单击【完成】，将新工作表chartl调整到合适的位置，并将该工作表改名为“应付款账龄分析图”，如图所示。(6)单击图标外围任意白色区域，单击鼠标右键，在弹出的菜单中选择【图表选项】。(7)在弹出的【图表选项】对话框中选择【数据标志】选项卡，勾选“值”前面的复选框，单击【确定】按钮。(8)单击【确定】按钮后，图表的柱形上显示出数据。(9)用上述同样的方法弹出【图表选项】对话框，选择【数据表】选项卡，勾选“显示数据表”前方的复选框。(10)单击【确定】按钮后的结果如图。实操练习【资料1】和天利公司有关的业务单位的基本信息如下所示：（1） 客户名称华茂公司，地址为华山街1526号，联系电话为法人代表是张深。（2） 客户名称嘉惠超市，地址为南平街2616号，联系电话为法人代表是张嘉惠。（3） 客户名称利群商场，地址为新兴路315号，联系电话为法人代表是林渡。（4） 客户名称红星商场，地址为红星广场，联系电话为法人代表是赵梓。在2010年1到3月间，发生了具体业务数据如下所示：（1） 与华茂公司在1月2日发生一笔业务，业务内容为出售电脑，金额为35,000元，信用期为90天，该笔业务的对冲号为2010-1-2-2。（2） 与嘉惠超市在1月3日发生一笔业务，业务内容为出售主机，金额为70,000元，信用期为60天，该笔业务的对冲号为2010-1-10-1。（3） 与嘉惠超市在1月13日发生一笔业务，业务内容为出售主机，金额为68,000元，信用期为60天，该笔业务的对冲号为2010-1-31-1。（4） 与嘉惠超市在2月20日发生一笔业务，业务内容为出售键盘，金额为65,000元，信用期为60天，该笔业务的对冲号为2010-2-14-1。（5） 与嘉惠超市在2月20日发生一笔业务，业务内容为出售显示器，金额为43,000元，信用期为60天，该笔业务的对冲号为2010-2-16-1。（6） 与利群商场在2月25日发生一笔业务，业务内容为出售电脑，金额为22,000元，信用期为90天，该笔业务的对冲号为2010-2-20-1。（7） 与利群商场在3月15日发生一笔业务，业务内容为出售电脑，金额为99,000元，信用期为90天，该笔业务的对冲号为2010-3-8-1。（8）