EXCEL在财务中的应用(PPT42页)

1,Excel在财务中的应用,汪升华,Telmail：foxshwang,2,目录,预备知识、Excel操作小结第一章、财务处理系统第二章、会计报表的编制第三章、工资管理第四章、固定资产管理第五章、进销存管理第六章、货币时间价值的计算第七章、财务预测第八章、全面预算化编制,3,预备知识、Excel操作小结,Excel是微软推出的一个功能强大的电子表格软件，具有强大的数据计算与分析处理功能，可以把数据用表格及各种统计图、透视图的形式表现出来，使得制作出来的报表图文并茂，信息表达更清晰。,4,Excel的窗口界面,一般情况下，Excel窗口界面可通过“视图”菜单或“工具/选项”菜单命令下的“视图”选项卡来调整。问题：Excel的编辑栏用于输入或修改工作表单元格数据,你能打开/关闭编辑栏吗？,5,基本概念,1工作簿2、工作表问题：你知道工作表的名称显示在什么地方吗？（工作表标签）3单元格4单元格区域表示法：左上角单元格地址：右下角单元格地址,工作薄由工作表组成，工作表由单元格组成。,6,数据输入,数值型：直接录入字符型：一般也是直接录入（由数码0-9构成的字符量，录入时用单引号开头）日期/时间型：（录入格式）“年/月/日”或“年-月-日”使用“填充柄”可提高录入速度录入数据时先不要考虑数据的格式，录入完毕后再行修改其显示格式；在计算机中，日期型的量是以整数形式存放的；修改数据可通过“编辑栏”来实现；,7,公式与函数,公式输入方法1、使用键盘直接输入2、使用鼠标选择输入公式以等号开头公式尽可能使用鼠标选择输入,8,相对地址与绝对地址,1、相对地址；例如：A3、B5等公式中如果使用相对地址，则公式复制要相应调整（变化）：调整规律如下：新行（列）号=原行（列）号+行（列）号的变化值2、绝对地址：例如：$A$3、$B$5等公式中如果使用绝对地址，则公式复制后不会改变3、混合地址：例如：$A3、B$5等提示：进行地址换时可使用F4键。,9,第一章、财务处理系统,一、会计科目代码与科目名称在计算机系统中一般使用会计科目代码作为代表会计科目的惟一标识。财政部对一级科目编码进行统一规定。,10,二、凭证的输入,1、LOOKUP函数简介P5格式：lookup(lookup_value,lookup_vector,result_vector)功能：首先在lookup_vector区域中寻找lookup_value，找到相匹配的单元后，将此单元在result_vector区域对应单元的值返回。注意：lookup_vector和result_vector只能为一行或一列的值,查什么,在哪查,返回对应的值,11,2、科目信息的建立输入时注意数据类型输入余额时，借方余额为正数，贷方余额为负数。,12,3、凭证信息的输入对于应收或预收账款、应付或预付账款科目将采用电算化系统中的“客户或供应商”辅助核算方法进行；对于管理费用科目将采用电算化系统中的“部门和项目”辅助核算方法进行。为了保证科目代码输入的有效性，使用“数据/有效性”功能进行相应设置。为了在输入凭证时，输入科目代码后使得相对应的科目名称自动显示，可使用LOCKUP函数。为了能使计算机给予输入提示防止输入出错，可以对“项目”和“部门”列加以有效性控制。为了方便浏览数据，可以对工作进行“拆分”与“冻结窗格”处理。,13,三、凭证查询,凭证查询主要利用EXCEL的筛选功能实现1、按凭证号进行查询2、按日期进行查询3、按科目进行查询4、按金额进行查询5、按部门或项目进行查询问题：如何设置“科目性质”与“总账科目”栏的公式。科目性质：1-资产2负债3权益4-成本5-损益提示：使用字符（文本）函数LEFT实现。,14,四、明细账账与总账的形成,1、SUMIF函数格式：SUMIF（range，criteria，sum_range）功能：在range数据区找到与criteria匹配的单元，对sum_range区域中与此单元对应的单元进行求和,在此区域中找与criteria匹配的单元,对sum_range区域中与找到的对应单元求和,15,四、明细账账与总账的形成（续）,2、利用SUMIF函数计算明细账科目的发生额与余额提示：发生额栏公式设置利用SUMIF函数range-填写凭证时科目代码所在区域criteria-余额表中的科目单元sum_range-填写凭证时发生额或余额所在区域余额栏公式设置余额=期初余额+借方发生额-贷方发生额累计发生额栏公式设置1月份的累计发生额与1月份的发生额相等，以后各月份的累计发生额等于当月发生额加上上月的累计发生额。,16,四、明细账账与总账的形成（续）,3、利用SUMIF函数计算按部门或项目明细核算科目的发生额提示：创建一个新的工作表“管理费用明细”用于计算按部门或项目明细核算科目的发生额利用CONCATENATE函数将科目名称、项目与部门合并为一新栏目，以便通过此列利用SUMIF函数，对不同部门、不同项目管理费用的借方或贷方发生额进行汇总。计算按部门或项目明细核算科目的借贷方发生额提示：利用SUMIF函数设置“预算-实际”栏目公式，当进行凭证输入时，可随时监控费用是否超出预算。设置“预算-实际”栏目数据显示格式，当超过预算时（负数）用红色表示。,17,四、明细账账与总账的形成（续）,4、利用SUMIF函数计算总账科目的发生额与余额提示：期初额栏公式设置利用SUMIF函数range-填写总账科目所在区域criteria-填写总账科目代码所在单元sum_range-填写对应期初余额所在的区域借贷方发生额栏目公式设置期末余额栏公式设置余额=期初余额+借方发生额-贷方发生额累计发生额栏公式设置1月份的累计发生额与1月份的发生额相等，以后各月份的累计发生额等于当月发生额加上上月的累计发生额。,18,四、明细账账与总账的形成（续）,5、利用分类汇总功能计算明细科目的余额与发生额6、利用分类汇总功能计算总账科目的余额与发生额7、利用分类汇总功能计算资产、负债、所有者权益及末结利润,19,四、明细账账与总账的形成（续）,8、利用数据透视表与分析管理费用某部门或某项目的发生额、余额提示：输入凭证，并在“管理费用明细”表中设置公式，按部门或项目明细核算科目的计算出发生额后，再利用数据透视表与分析管理费用某部门或某项目的发生额。,20,四、明细账账与总账的形成（续）,9、期末损益结转10、下月账薄的建立提示：1）、将上月的工作表复制一份作为2月“基础”表2）、编写公式计算2月份期初余额（等于上月的期末余额）3）、将上月填写的凭证删除后，再编辑本月业务凭证4）、编辑公式计算所累计借（贷）方发生额（等于上月累计发生额+本月发生额）,21,五、会计数据的保护,1、对工作薄进行保护与撤消保护2、对工作表进行保护与撤消保护3、工作表（公式）区域的保护提示：1）、选择不被保护区域2）、使用“快捷菜单/设置单元格格式”，选择“保护”选项，取消“锁定”3）、保护工作表,22,第二章、会计报表的编制,一、资产负债表的编制通过数据引用方法直接从第一章相关月份账薄中取数即可-引用账薄中的期初与期末余额。知识点：1、数据引用的三种方式：A、同一工作表中不同单元格的数据引用；B、同一工作薄中的不同工作表的数据引用；C、不同工作薄间的数据引用。2、名称的定义与引用,23,二、损益表的编制通过数据引用方法直接从第一章相关月份账薄中取数即可-引用账薄中的借方或货方发生额。,会计报表的编制,24,三、现金流量表的编制通过数据引用方法直接从第一章相关月份账薄中取数即可-引用账薄中的借方或货方发生额。1、准备工作1）、建立现金流量项目代码与名称间的对应关系；2）、在凭证项目中，加入“现金流量代码”和“现金流量名称”两个项目；3）、对“现金流量代码”列设置有效性控制；4）、通过lookup函数，使得“现金流量名称”自动产生；5）、输入凭证时，“现金”与“银行存款”科目要输入“现金流量项目代码”（现金流量项目名称自动对应生成）,会计报表的编制,25,现金流量表的编制2、现金流量表的编制使用SUMIF函数直接从第一章相关月份账薄中按条件求和即可-引用账薄中的借方或货方发生额。,会计报表的编制,26,四、合并报表的编制1、合并报表的前题条件合并报表的源数据表的结构应相同，否则，合并报表结果无意义。2、操作提示：1）、将母公司与子公司报表进行汇总处理；利用EXCEL中的合并计算功能实现合并计算完成后，源区域数据中的更改不会对合并计算表（结果）影响，如果希望更改源区域数据后，合并计算表的数据也随之自动更改，可将合并计算表链接到源区域上。2）、建立调整分录输入的项目，并输入调整会计分录；3）、建立合并报表格式；4）、设置合并报表单元取数公式，并自动完成取数。,会计报表的编制,27,五、财务报表分析1、操作分析：使用EXCEL的图表功能实现。2、分析类型：趋势分析柱型图或折线图结构变化分析-堆积柱型图结构分析-饼图,会计报表的编制,28,第三章、工资管理,一、工资基本数据的输入操作提示：为了输入方便并防止出错，可对“部门”、“性别”、“职工类型”等栏目添加有效性规则设置。,29,工资管理,二、基本工资项目设置1、IF函数介绍格式：if(logical_test,value_if_true,value_if_false)功能：根据对指定条件逻辑判断的真假而返回不同的值。2、主要内容设置：1）利用IF函数对岗位工资、福利费、副食补贴、奖金、事假扣款、病假扣款、所得税等项目进行设置、2）利用SUM函数等对应发合计、扣款合计、应发工资、实发工资等项目进行设置。,30,工资管理,三、工资数据的查询1、利用筛选功能进行工资数据的查询自动筛选：适用于多个筛选条件间为逻辑与关系（多个条件同时满足）的情况。高级筛选：一般用于多个筛选条件间为逻辑或关系（多个条件中只要一个满足）的情况。,31,工资管理,三、工资数据的查询2、利用VLOOKUP函数进行工资数据的查询知识点-VLOOKUP介绍格式：vlookup(lookup_value,table_array,col_index_num,rang_lookup)其中：lookup_value：指需要在数据表首列搜索的值。table_array：指需要在其中搜索数据的数据表。col_index_num：指在数据表的首列搜索到满足条件的单元后，此函数返回数据表中与此单元格同行，列号为col_index_num单元格的值。ang_lookup：指定在搜索时是精确匹配还是大致匹配，如果是TRUE，为大致匹配，要求数据表首列按升序排序，如果为FALSE，为精确匹配，不要求数据表首列按升序排序。如果此值省略，表示选择TRUE。功能：在table_array数据表首列，寻找与lookup_value匹配的单元，如果找到，返回数据表中与此单元同行的第col_index_num列的值。,32,工资管理,四、工资数据的汇总分析通过数据透视表（图）方法实现。1、依据部门和职工类别的统计分析2、依据性别的统计分析3、依据年龄段或基本工资段的统计分析4、以月份为依据的统计分析,33,第四章、固定资产,一、原始卡片录入1、为了输入数据的方便性，应对“增加方式”、“减少方式”、“使用状态”、“部门”等项进行数据有效性设置。2、折旧方法项目的输入约定：1-平均年限法、2-工作量法、3-双倍余额递减法、4、年数总和法3、输入原始卡片时可以使用记录单来完成4、计算固定资产净值5、为了方便浏览，可使用窗口拆分与冻结方法。,34,固定资产,二、固定资产新增的业务处理与原始卡片录入相似：1、可以直接以数据行方式录入2、也可以使用记录单来完成,35,固定资产,三、固定资产部门调拨的业务处理1、当月的处理：使用筛选功能找待处理的资产项目将此资产项目的“减少方式”栏修改为“部门调拨”2、下月的处理复制上月的相应固定资产项目数据行。将此固定资产项目的“增加方式”栏修改为“部门调拨”将此固定资产项目的“部门”栏修改为新的部门值将此资产项目的“减少方式”栏修改为“空”,36,固定资产,四、固定资产减少的业务处理1、使用筛选功能找到减少的资产项目。2、在“减少方式”一栏输入相应的减少方式内容。,37,固定资产,五、固定资产折旧的计提业务处理一）预备知识：1、DDB（）函数-双倍余额递减法计提折旧DDB（cost,salvage,life,period,factor）Cost：原值Salvage：残值Life：预计使用年或月值Period：折旧计提年或月数Factor：默认表示2，即双倍余额递减，可以为其他整数，为如3，表示3倍余额递减,38,固定资产,五、固定资产折旧的计提业务处理预备知识：2、模拟运算表和使用3、SYD（）函数-年数总和法计提折旧SYD（cost,salvage,life,per）Cost：原值Salvage：残值Life：预计使用年或月值Period：折旧计提年或月数,39,固定资产,五、固定资产折旧的计提业务处理二）、固定资产折旧计提前的准备1、在”部门”栏后面插入“当前日期”栏并输入当前值2、在”开始使用日期”栏后面插入“已计提折旧月份”栏并使用函数DAYS360（）计算该栏目值。3、在”已计提折旧月份”栏后面插入“已计提折旧年份”栏并使用函数计算该栏目值。4、对按工作量法计提折旧的固定资产输入当月工作量。,40,固定资产,五、固定资产折旧的计提业务处理三）、固定资产折旧计提1、计提注意事项：不同的固定资产采用不同的折旧方法计提。