excel在财务中的应用模块一制作“应收账款账龄分析表”任务4.1应收账款账龄分析表编制指南.docxVIP

项目四 往来账款管理 任务4.1 编制应收账款账龄分析表 4.1.1任务情境 账龄是指负债人欠款的时间。一般来说，账龄越长，发生坏账损失的可能性就越大。账龄分析法是指根据应收账款的时间长短来估计坏账损失的一种方法，又称“应收账款账龄分析法 ”。采用账龄分析法时，将不同账龄的应收账款进行分组，可以将账龄段划分为0～30天、31～60天、61～90天、91～120天、121～180天、180天以上等。 今天，小肖接到的任务是编制应收账款账龄分析表。他首先从系统中导出逾期应收账款清单，然后从信用部门得到各个客户的信用期清单，之后运用VLOOKUP函数、IF函数、数据透视表等功能创建了鹏程公司应收账款账龄分析表。 4.1.3任务实施 1.创建应收账款账龄分析表 【要求】下载“应收账款账龄分析表素材”工作簿，复制“发票信息”工作表到“信用期限”工作表后，重命名Sheet为“账龄分析表”。在表上方插入两行，添加报表名称“鹏程公司应收账款账龄分析表”，并跨列居中。A2输入“当前日期：”，B2输入“2017-12-31”，J2输入“单位：”，K2输入“元”。添加D列至K列的表头。美化表格，按照自己的审美添加边框。效果如图。 【提示】右击复制工作表，移动或复制工作表-建立副本 【要求】打开“账龄分析表”，冻结前三行和A列，设置B55的合计公式。效果如图： 【提示】选中B3，冻结窗格 B55=SUM(B4:B54) 【要求】打开“信用期限”工作表，把该表格通过复制粘贴，从横排变成纵排，显示在F2：F5。并在G3：G25填充对应的天数。效果如图： 【要求】打开“账龄分析表”工作表，设置D4通用公式，利用Vlookup函数自动填充信用期限。 【提示】D4=VLOOKUP(C4,信用期限!F:G,2,0)或D4=VLOOKUP(C4,信用期限!$F$3:$G$26,2,0)，向下自动填充。 【要求】打开“账龄分析表”工作表，理解“到期日期=开票日期+信用期限”，设置E4通用公式，自动显示到期日期。设置E列的日期格式，使其对齐。效果如图： 【提示】E4=A4+D4，向下自动填充。 选中E4：E54，自定义格式yyyy-mm-dd 【要求】打开“账龄分析表”工作表，理解“逾期天数=当前日期-到期日期”，设置F4通用公式，自动显示逾期天数。效果如图： 【提示】F4=$B$2-E4，向下自动填充。 【要求】打开“账龄分析表”工作表，理解“未到期即逾期天数小于0”，设置G4通用公式，当逾期时，显示发票的应收金额；当没逾期时，显示0。效果如图： 【提示】G4=IF(F40,B4,0) 【要求】打开“账龄分析表”工作表，理解0-30天指“逾期天数0天，=30天”，30-60天指“逾期天数30天，=60天”。同理类推，180天以上指“逾期天数=180天”。分别设置H4到k4通用公式，显示账龄。效果如图： 【提示】H4=IF(AND($F40,$F4=30),$B4,0)，向右复制，修改I4、J4、K4的公式如下： I4=IF(AND($F430,$F4=60),$B4,0) J4=IF(AND($F460,$F4=180),$B4,0) K4=IF($F4180,$B4,0) 分别向下填充 【要求】打开“账龄分析表”工作表，设置G55：K55的合计公式 【提示】选中G55，快捷键Alt+，向右复制 2.利用数据透视表功能创建汇总分析表 【要求】打开“账龄分析表”工作表，利用数据透视表，建一张新表，汇总分析不同客户的应收账款账龄统计，重命名工作表名称为“汇总分析”，效果如图： 【提示】选中A3：K54，插入数据透视表 3.重命名数据透视表的字段 【要求】打开“汇总分析”工作表，重命名数据透视表的表头，去掉“求和项：”几个字。效果如图： 【提示】方法一：双击B3，修改自定义名称，删除“求和项：”，添加一个空格，其他同理 方法二：Ctrl+H替换“求和项：”为空格 4.使用表样式格式化数据透视表 【要求】打开“汇总分析”工作表，利用表格样式，根据自己的审美，美化表格。删除行标签，设置货币格式，保留整数，取消网格线。效果如图： 【要求】设计-透视表样式 5.创建数据透视图 【要求】打开“汇总分析”工作表，使用数据透视图功能，在本工作表建立一个簇状柱形图，其中，180天以上的修改为折线图，效果如下： 【提示】组合图 6.设置数据透视图样式 【要求】打开“汇总分析”工作表，利用数据透视图的图表样式，根据自己的审美美化表格。并尝试分析哪些客户的应收账款应该重点关注。效果如图： 【提示】设计-图表样式