Excel在财务中的应用课件：应用Excel建立会计凭证表

Excel

在财务中的应用

任务2应用Excel建立会计凭证表子任务2.1建立会计科目表子任务2.2建立会计凭证表应用Excel建立会计凭证表任务2重点和难点1.会计科目表的建立;2.会计凭证表的建立。学习目标【知识目标】1.了解会计凭证的相关概念;2.掌握会计科目表的建立过程;3.掌握会计凭证表的建立过程。【技能目标】1.能够建立会计科目表;2.能够建立会计凭证表;3.能够对会计凭证表中的借贷平衡关系进行设置;4.能够在会计凭证表中录入会计分录。应用Excel建立会计凭证表任务2任务概述会计科目表和会计凭证表是会计账务处理的重要依据,是会计核算的起点。通过本任务的学习,学生在熟练掌握Excel基本操作的基础上,掌握会计凭证的相关概念,能应用Excel制作会计科目表和会计凭证表,具备会计分录录入的能力。应用Excel建立会计凭证表任务2制作和修饰Excel工作表子任务2.1任务要求1.能够进行工作簿和工作表的基本操作;2.能够输入和编辑工作表单元格数据;3.能够修饰工作表;4.能够设置工作表页面。会计凭证的含义和作用2.1知识积累会计凭证是指记录经济业务、明确经济责任的书面证明。它是登记账簿的依据。填制和审核会计凭证,既是会计工作的开始,也是会计对经济业务进行监督的重要环节。会计凭证在会计核算中具有十分重要的意义,主要表现在以下几个方面:(1)填制和取得会计凭证,可以及时正确地反映各项经济业务的完成情况。(2)审核会计凭证,可以更有力地发挥会计的监督作用,使会计记录合理、合法、有效。(3)填制和审核会计凭证,可以加强经济管理人员的责任感。会计凭证的类型2.2知识积累会计凭证按填制的程序及在经济管理中的用途可分为原始凭证和记账凭证。1.原始凭证原始凭证是指在经济业务发生时取得或填制的,用以证明经济业务的发生或完成等情况,并作为原始依据的会计凭证。原始凭证必须真实、完整、规范、及时和正确,并必须有经办人的签字。此外,只有经过审核后的原始凭证才能作为记账依据。审核原始凭证是保证会计记录真实和正确、充分发挥会计监督作用的重要环节。2.记账凭证记账凭证是指会计人员根据审核后的原始凭证填制的,用来确定经济业务应借、应贷会计科目,作为记账依据的会计凭证。记账凭证在记账前必须经过审核。建立会计科目表子任务2.1任务要求1.能够建立会计科目表;2.能够修饰会计科目表;3.能够录入会计科目。建立会计科目表子任务2.1任务资料1.建立工作簿,工作簿名称为“大连百大公司会计核算”。2.在“大连百大公司会计核算”工作簿中,将Sheet1工作表重命名为“会计科目表”,并在工作表中建立如图3.对会计科目表进行适当的美化,使之美观实用。图2-1会计科目表建立会计科目表子任务2.1任务实施1.建立工作簿,工作簿名称为“大连百大公司会计核算”。请参考任务1建立工作簿的操作过程,此处略。2.在“大连百大公司会计核算”工作簿中,将Sheet1工作表重命名为“会计科目表”,并在工作表中建立如图2-1所示的会计科目表。步骤1

打开“大连百大公司会计核算”工作簿,在Sheet1工作表标签上右击鼠标,选择“重命名”命令,工作表标签反白显示,输入工作表标签名为“会计科目表”,按Enter键确定。步骤2在“会计科目表”工作表中,选择A1:B1单元格区域,选择“开始—对齐方式”命令组,单击“合并后居中”命令,将A1:B1单元格区域合并,并输入文本“大连百大公司会计科目表”。步骤3在“会计科目表”工作表中,依次在A2、B2单元格输入文本“科目编号”和“科目名称”。步骤4在“会计科目表”工作表中,分别在A3和B3单元格输入“1000”“资产类”,然后按照图2-1所示依次录入其他会计科目的编号和名称。3.对“会计科目表”进行适当的美化,使之美观实用。步骤1在“会计科目表”工作表中,选择A1单元格,设置为加粗、宋体、14磅。步骤2选择A2:B2,设置格式为加粗、水平居中、11磅。步骤3对工作表中数据按照图2-1所示设置背景填充颜色,调整行高和列宽,并设置表格边框。建立会计凭证表子任务2.2任务要求1.能够建立会计凭证表;2.能够在会计凭证表中自动生成会计科目名称;3.能够在会计凭证表中进行借贷平衡判断。建立会计凭证表子任务2.2任务资料1.在“大连百大公司会计核算”工作簿中,按如下要求完成“会计凭证表”工作表基本格式的设置:(1)选择Sheet2工作表,重命名为“会计凭证表”。(2)按如图2-2所示设计“会计凭证表”工作表,并录入相关数据。

(3)将A:D列单元格设置为文本格式。(4)将I:J列单元格数字分类格式设置为“会计专用”,小数位2位,货币符号为“无”。(5)对“会计凭证表”工作表进行格式修饰,调整行高、列宽,使之容纳所有数据,并设置字体、字号,结果如图2-3所示。图2-2“会计凭证表”结构图2-3“会计凭证表”修饰后结果建立会计凭证表子任务2.2任务资料2.在“会计凭证表”工作表中,设置“凭证编号”列公式,使其自动生成凭证编号。凭证编号生成规则为“年+月+日+序号”。3.在“会计凭证表”工作表中,设置“科目名称”列公式,使其自动显示会计科目名称。要求在“会计科目表”工作表中使用VLOOKUP函数查找科目编号,返回科目名称。4.在“会计凭证表”工作表K2单元格中设置借贷不平衡提示,错误时提示“借贷不平衡!”,并以红色字显示,正确时为空。5.根据大连百大公司的企业信息以及该公司2019年12月份发生的经济业务,要求在“会计凭证表”中编制相应的会计分录。企业信息如下:(1)公司名称:大连百大公司。(2)账号:123001。(3)核算要求:材料发出采用先进先出法。(4)固定资产月折旧率为0.4%,适用的增值税税率为13%、所得税税率为25%。12月份发生的经济业务如下:建立会计凭证表子任务2.2任务资料(1)1日,用现金400元购买办公用品。(2)2日,用支票偿还前欠新飞公司货款17000元。(3)2日,以银行存款购买转账支票,共计60元。(4)3日,车间领用甲材料10吨,每吨1600元,用于A产品生产。(5)3日,提取备用金2000元。(6)4日,从红星工厂购入甲材料10吨,每吨1600元,货款共计16000元,增值税2080元,用支票支付,材料已入库。(7)5日,缴纳上月增值税、城建税和教育费附加。(8)6日,采购员张平出差借差旅费2000元。(9)7日,销售给海文公司B产品一批,货款91000元,适用的增值税税率为13%,已将收到的支票存入银行。(10)7日,用支票支付第三季度养路费3000元。(11)8日,车间领用乙材料1吨,每吨2000元,用于一般耗费。(12)9日,用现金支付车间修理费500元。(13)10日,用现金预付明年上半年的报刊费600元。(14)11日,签发现金支票,提取本月工资46900元。(15)11日,发放本月工资46900元。建立会计凭证表子任务2.2任务资料(16)12日,厂部报销客户餐费,支付现金460元。(17)13日,职工王艳报销医药费240元。(18)14日,从新飞公司购入乙材料3吨,每吨2000元,款项尚未支付,材料已入库。(19)14日,由银行支付本月生产车间水费600元。(20)15日,车间领用乙材料15吨,每吨2000元,用于B产品的生产。(21)16日,销售给蓝天公司A产品一批,货款117600元,尚未收到。(22)17日,用支票支付广告费2000元。(23)18日,采购员张平出差回来报销差旅费2300元,不足部分用现金支付300元。(24)19日,用银行存款支付本月电费2700元,其中厂部用电负担800元,车间用电负担1900元。(25)20日,发生本月借款利息1000元。(26)21日,销售给万方公司材料10吨,每吨1900元,货款共计19000元,冲销预收账款8000元,其余款项以转账支票收取。(27)21日,以银行存款支付本月电话费1000元。(28)22日,分配本月工资,其中生产A产品的生产工人工资18000元,生产B产品的生产工人工资12000元,车间管理人员工资6600元,厂部人员工资10300元。(29)23日,按工资总额的14%计提福利费。(30)23日,本月以银行存款支付车间大修理费用1000元。建立会计凭证表子任务2.2任务资料(31)25日,年终盘点,盘盈生产用设备一台(全新),同类固定资产的市场价格为8000元。(32)25日,年终盘点,盘亏甲材料1吨,金额1600元(应负担的增值税为208元)。(33)26日,用银行存款支付第四季度借款利息3000元。(34)27日,计提本月折旧,其中车间10000元,厂部8000元。(35)27日,接受协作单位无偿捐赠电脑一台,市场价格12000元,用于管理。(36)27日,经领导审批后,将盘盈的设备按8000元计入营业外收入,盘亏的甲材料按1808元计入营业外支出。(37)28日,结转本月制造费用,按工人工资比例分配。(38)28日,结转本月已完工的8台A产品成本(包括上期尚未生产完工的A产品)。(39)29日,计提本月城建税、教育附加共计800元。(40)29日,公司已有的丙材料当前市场价为每吨1600元,已按规定提取存货跌价准备。(41)30日,用现金购买印花税票500元。(42)30日,厂部报销汽车加油费300元,经审核后以现金支付。(43)31日,按年末应收账款余额的5‰计提坏账准备。(44)31日,结转本月销售成本。其中,A产品12台,每台6400元,B产品10台,每台6000元。6.在“会计凭证表”工作表中筛选2019年12月14日发生的经济业务情况。7.使用快速分析功能筛选使用“银行存款”科目的经济业务情况。建立会计凭证表子任务2.2任务实施1.设置“会计凭证表”工作表的基本格式。步骤1打开“大连百大公司会计核算”工作簿,选择Sheet2工作表,重命名为“会计凭证表”。步骤2选定A1:J1单元格区域,选择“开始—对齐方式”命令组,单击“合并后居中”命令,并输入“大连百大公司会计凭证表”。步骤3在A2:J2单元格区域按图2-2所示依次输入“年”“月”“日”“序号”“凭证编号”“摘要”“科目编号”“科目名称”“借方金额”“贷方金额”。步骤4选定A:D列,右击鼠标,在快捷菜单中选择“设置单元格格式”,在打开的“设置单元格格式”对话框选择“数字”选项卡,在分类列表框中选择“文本”即可,如图2-4所示。步骤5选定I:J列,右击鼠标,在快捷菜单中选择“设置单元格格式”,在打开的“设置单元格格式”对话框,选择“数字”选项卡,在分类列表框中选择“会计专用”,在右侧选择小数位数为“2”,货币符号为“无”,如图2-5所示。图2-4设置单元格格式“文本”对话框图2-5设置单元格格式“会计专用”对话框建立会计凭证表子任务2.2任务实施步骤6对“会计凭证表”进行格式修饰和调整,结果如图2-6所示。2.在“会计凭证表”工作表中,设置“凭证编号”列公式,使其自动生成凭证编号。凭证编号生成规则为“年+月+日+序号”。步骤1打开“会计凭证表”工作表,在A3:D3单元格区域中分别输入“2019”“12”“01”“01”表示年、月、日和序号。步骤2选定“E3”单元格,单击编辑栏中的(插入函数)命令,打开插入函数对话框,选择文本函数CONCATENATE,如图2-7所示。单击“确定”按钮,弹出“函数参数”对话框,在Text1后面的文本框输入单元格名称“A3”或单击后面的按钮,选择A3单元格,然后依次在Text2、Text3、Text4文本框后输入“B3”“C3”“D3”单元格,如图2-8所示。单击“确定”按钮,得到所需要的公式,结果如图2-9所示。图2-6建立会计凭证表子任务2.2任务实施图2-7图2-8图2-9建立会计凭证表子任务2.2任务实施步骤3选定E3单元格,鼠标放在E3单元格的右下角,出现填充柄时,按住鼠标不放向下拖动到E4:E40单元格区域,完成快速填充。

【小提示】自动生成凭证编号,除了使用CONCATENATE函数外,还可以使用文本运算符“&”,用户可以在E3单元格直接输入公式“=A3&B3&C3&D3”即可。另外,在使用CONCATENATE函数时,不得在函数参数对话框或公式中使用单元格区域A3:D3,必须使用联合引用运算符,否则会显示“#VALUE”错误。图2-6建立会计凭证表子任务2.2任务实施3.在“会计凭证表”工作表中,设置“科目名称”列公式,使其自动显示会计科目名称。要求在“会计科目表”工作表中使用VLOOKUP函数查找科目编号,返回科目名称。步骤1在“会计凭证表”工作表,选定H3单元格,单击编辑栏中的(插入函数)命令,打开“插入函数”对话框,选择逻辑函数下的IF函数,单击“确定”按钮,弹出“函数参数”对话框,在Logical_text文本框后输入“G3=""”,在Value_if_true文本框后输入“""”,在Value_if_false文本框后输入“VLOOKUP()”,如图2-10所示。步骤2将光标定位于编辑栏上的“VLOOKUP”函数的“()”内,弹出VLOOKUP函数的“函数参数”对话框,如图2-11所示。图2-6图2-10图2-11建立会计凭证表子任务2.2任务实施步骤3在VLOOKUP函数的“函数参数”对话框中,在Lookup_value文本框后输入“G3”,或选择G3单元格名称,表示查找值为G3单元格的值。步骤4

将光标定位于Table_array后的文本框,单击“会计科目表”工作表标签,然后在“会计科目表”工作表中选择A1:B51单元格区域,在Table_array文本框中显示“会计科目表!A1:B51”,表示将在“会计科目表”的A1:B51单元格区域查找G3单元格的值。步骤5

在Col_index_num文本框后输入“2”,表示如果找到,则返回查找区域中第2列的值,即“科目名称”列的值。步骤6

在Range_lookup文本框后输入“1”,表示采用模糊查找方式,如图2-12所示,单击“确定”按钮完成函数设置。图2-10图2-12建立会计凭证表子任务2.2任务实施步骤7

选定H3单元格,鼠标指向H3单元格右下角的填充柄,按住鼠标不放向下拖动到H4:H40单元格区域,实现快速填充,完成自动生成科目名称操作。【小提示】如果用户对函数格式较为熟悉,则可以在选定H3单元格后,直接输入公式“=IF(G3="","",VLOOKUP(G3,会计科目表!A1:B51,2,1))”。公式中的IF函数用来判断要查找的G3单元格是否为空。如果为空,则返回空值;如果不为空,则调用函数VLOOKUP函数进行查找。在公式中如果没有IF函数,则G3为空时,系统会返回错误结果“#N/A”。建立会计凭证表子任务2.2任务实施4.在“会计凭证表”工作表K2单元格中设置借贷不平衡提示,错误时提示“借贷不平衡!”,并以红色字显示,正确时为空。步骤1在“会计凭证表”工作表,选定K2单元格,单击编辑栏中的(插入函数)命令,打开“插入函数”对话框,选择逻辑函数下的IF函数,单击“确定”按钮,弹出“函数参数”对话框。步骤2在Logical_text文本框后输入“SUM(I:I)=SUM(J:J)”,在Value_if_true文本框后输入“""”,在Value_if_false文本框后输入“"借贷不平衡!"”,如图2-13所示,单击“确定”按钮完成公式输入。步骤3

选定K2单元格,选择“开始—字体”命令组,单击“字体颜色”命令下拉列表,选择“红色”即可。【小提示】在录入会计分录时,如果借方金额合计与贷方金额合计不相等,则在K2单元格中会自动显示“借贷不平衡!”的提示信息;如果相等,则显示为空值。图2-13

建立会计凭证表子任务2.2任务实施步骤4

选定I3单元格,输入借方金额“400.00”。

【小提示】因尚未输入贷方金额,所以在K2单元格显示“借贷不平衡!”,这是正常现象。步骤5

选定A3:F3单元格区域,右击鼠标,在快捷菜中选择“复制”命令。然后选定A4单元格,再右击鼠标,在快捷菜单中选择“粘贴”命令。在G4单元格输入会计科目编号“1001”,系统自动在H4单元格显示会计科目名称“库存现金”。选定J4单元格,输入贷方金额“400.00”。结果如图2-15所示。

【小提示】

因输入了正确的贷方金额,所以在K2单元格显示的“借贷不平衡!”消失。步骤6

以上操作为录入一条完整的会计分录的全过程,后续的会计业务请参照此操作进行,再进行相应的格式设置即可完成会计凭证表分录的录入。其他会计业务的分录录入过程略。图2-14建立会计凭证表子任务2.2任务实施6.在“会计凭证表”工作表中筛选2019年12月14日发生的经济业务情况。步骤1

打开“会计凭证表”工作表,选择“数据—排序和筛选”命令组,单击“筛选”按钮。步骤2

单击“年”列右侧的下拉列表,选择“2019”。步骤3

单击“月”列右侧的下拉列表,取消“全选”,再选择“12”。步骤4

单击“日”列右侧的下拉列表,取消“全选”,再选择“14”,结果如图2-15所示。图2-13

建立会计凭证表子任务2.2任务实施7.使用快速分析功能筛选“银行存款”科目的经济业务情况。步骤1

打开“会计凭证表”工作表,选定H3:H140单元格区域。步骤2单击选定区域右下角的按钮(快速分析),或者右击鼠标,在快捷菜单中选择“快速分析”命令,弹出如图2-16所示的对话框。步骤3选择“格式—文本包含”命令组,打开“文本中包含”对话框,输入要筛选的文本,如图2-17所示。步骤4单击“确定”按钮,显示快捷分析筛选结果,如图2-18所示。图2-17

图2-18

图2-16

应用Excel建立会计凭证表任务2任务总结本任务介绍了如何应用Excel建立会计凭证表,具体包括会计凭证的有关概念;利用Excel建立会计科目表与美化会计科目表;利用Excel的CONCATENATE函数、IF函数和VLOOKUP函数建立具有自动生成凭证编号、自动获取会计科目名称、自动判断借贷方平衡等多项功能的会计凭证表。通过本任务的学习,学生可掌握会计科目表和会计凭证表的建立过程,为进一步会计核算积累了资料。任务实训一、实训的目的和要求1.掌握会计凭证的相关概念;2.能够建立会计科目表;3.能够建立会计凭证表;4.能够在会计凭证表中录入会计分录。二、实训内容1.建立“大连星河科技公司会计核算”工作簿。2.激活Sheet1工作表,重命名为“会计科目表”,并按大连百大公司的会计科目表结构建立大连星河科技公司的会计科目表。3.插入Sheet2工作表,重命名为“会计凭证表”,并按大连百大公司的会计凭证表结构建立会计凭证表。4.使用公式和函数实现自动生成会计凭证、自动获取科目名称、核对借贷方是否平衡等功能。应用Excel建立会计凭证表任务2任务实训5.已知大连星河科技公司的企业信息以及2020年1月份发生的经济业务,要求在“会计凭证表”工作表中录入相应的会计分录。企业信息:(1)企业名称:大连星河科技公司。(2)账号:123002。(3)核算要求:材料发出采用先进先出法。(4)固定资产月折旧率为0.4%,适用的增值税税率为13%、所得税税率为25%。发生的经济业务如下:(1)2日,收到阳光公司汇来的前欠货款80000元,存入银行。(2)3日,生产车间领用A材料10千克。其中:生产甲产品耗用4千克,生产乙产品耗用4千克,生产车间一般性消耗2千克。A材料单位成本100元。(3)4日,生产车间领用B材料1200千克,单位成本5元,全部用于生产甲产品。(4)4日,从大明公司购入A材料20千克,单价100元,价款合计2000元,增值税进项税额260元,款项已通过银行存款支付。材料已验收入库。(5)5日,开出银行支票800元,缴纳税金。应用Excel建立会计凭证表任务2任务实训(6)6日,开出银行支票45000元,归还短期借款。(7)6日,公司购入B材料600千克,单价5元,价款合计3000元,增值税进项税额390元,款项已通过银行存款支付。(8)7日,生产的乙产品已验收入库,结转其实际成本。(9)9日,用银行支票偿还前欠光明公司货款29000元。(10)10日,以现金700元购入车间用办公用品。(11)11日,生产车间领用A材料100千克。其中:生产甲产品耗用20千克,生产乙产品耗用70千克,生产车间一般性消耗10千克。A材料单位成本100元。(12)11日,公司出售甲产品100台,单价270元,价款总计27000元,增值税销项税额3510元,上述款项已收讫并存入银行。(13)12日,以银行支票2500元支付广告费。(14)12日,以现金500元支付广告费。(15)12日,公司出售乙产品100台,单价300元,价款总计30000元,增值税销项税额3900元,上述款项已收讫并存入银行。(16)12日,用银行存款30000元购买一台设备,已交付使用。(17)13日,从银行提取现金70000元,以备发放工资。应用Excel建立会计凭证表任务2任务实训(18)13日,以现金70000元发放工资。(19)13日,以现金200元支付生产车间机器修理费。(20)14日,出售甲产品30台,单价270元,价款总计8100元,增值税销项税额1053元,上述款项已收讫并存入银行。(21)14日,开出银行支票偿还前欠货款1500元。(22)15日,从银行借入为期3个月的贷款60000元,年利率为5%。(23)15日,行政管理部门的王明出差借差旅费500元,以现金支付。(24)16日,出售B材料1000千克,每千克售价10元,价款总计10000元,已存入银行。(25)16日,结转上述B材料的实际成本5000元。(26)17日,以银行支票支付税收滞纳金2500元。(27)18日,职工王明出差归来报销差旅费400元,余款以现金退回。(28)19日,职工李明报