作为表哥表姐的一员,柠檬妹也算是比较资深的那群了,今天用excel结合财务应用场景,给大家分享几个日常技巧。
一、合同金额计算
合同中有不含税金额,印花税的计算基础才是不含税金额,为了节税,合同中一般会计算出金额和税额。大小写金额要一致,金额、税额、税率与含税金额的要正确地计算。
用计算器计算很麻烦,大写输入更是费时间。而设置公式,操作简单准确性高,只要输入含税金额和税率,金额和税额就可以自动计算。
解析:
1、含税金额与金额、税额的保留两位小数计算:
G2=ROUND(F2/(1+H2),2),I2=ROUND(F2/(1+H2)*H2,2),ROUND函数是财务人员计算经常用到的公式,保留小数位数一致。
2、小写金额与大写金额的转换: D4=SUBSTITUTE(IF(-RMB(F3),IF(F3<0,"负",)&TEXT(INT(ABS(F3)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(F3),2),"[>9][dbnum2]0角0分;[>][dbnum2]0分;整"),""),"零分","整")
用到时直接复制粘贴,把F3全部替换为实际的行列号即可。
二、个税计算
工资表中的个税计算是一个令财务人麻烦的问题。经过我对公式的不断学习和研究,做出了一个个税计算的表格。社保在一年中的数据不固定,计税工资直接是以应付工资减去社保的数据。每月扣除的费用就是5000乘以入职月数,专项附加扣除利用公式是专项附加扣除表中的数据乘以入职月数。需要计算个税和个税金额不为零的颜色明显标记,清晰明了。
解析(该表格中的工资为扣除社保公积金后的工资):
1、累计扣除费用
以前年度入职:Q3=DATEDIF("2020-01-01",TODAY(),"m")*5000
本年度入职:Q3=DATEDIF(B3,TODAY(),"m")*5000
2、累计专项附加扣除
以前年度入职:P3=(DATEDIF("2020-01-01",TODAY(),"m"))*SUMIF(专项附加扣除!A:A,A3,专项附加扣除!I:I)
本年度入职:P3=(DATEDIF(B3,TODAY(),"m"))*SUMIF(专项附加扣除!A:A,A3,专项附加扣除!I:I)
3、R3=IF(O3-P3-Q3<=0,"","计算个税")
当结果为计算个税时自动颜色填充标记。
4、个税计算
S3=ROUND((MAX((O3-P3-Q3)*{0.03;0.1;0.2;0.25;0.3;0.35;0.45}-{0;2520;16920;31920;52920;85920;181920},0)),2)
计算出的数值是截止应发工资当月的个税累计金额,从当年第一个月计算的个税金额复制粘贴为数值,下一个月为本月计算值减当年以前月份计算的税额,依次上月粘贴为数值后再计算下个月。
5、个税合计金额列条件格式设置
当个税计算不为0时,自动颜色填充标记,能更清楚的看到需要交个税的人员。
三、社保计算
社保险种多,缴费比例不同,每个人的缴费基数也不同,每个人的每项社保的计算的工程量也比较大,只要设置一个公式,其他的复制公式即可全部计算,省时省力,计算准确。
解析:
1、D6=ROUND($C6*D$5,2)
只设置这一格的公式往右向下下拉公式即可,F4锁定C列和第五行,保证无论哪一格的公式都是该格相对应的C列和第五行的乘积保留两位小数。
2、J6=SUMIF($D$4:$I$4,$J$4,$D6:$I6)
利用条件求和函数和F4锁定对每行的单位部分求和。
3、K6=SUMIF($D$4:$I$4,$K$4,$D6:$I6)
利用条件求和函数和F4锁定对每行的个人部分求和。
四、个税系统人员信息表身份证提取年龄、性别、出生年月
我们建筑服务业的人员流动性很大,个税系统项目施工人员的信息采集表中填好身份证号还要手动去输入性别和出生年月,人数少了还好,人数多的话真的很麻烦,还不能保证数据的准确性。设置公式身份证提取年龄、性别、出生年月就能帮助我们解决这个问题。
解析:
1、C3=IF(MOD(MID(B3,17,1),2),"男","女")
字符串函数MID(字符串,开始位置,字符个数),取余函数MOD(数值,除数)。
MID(B3,17,1)=6,身份证第17位数字代表男女,偶数是女,单数是男,MOD(6,2)=0,有余数的是男,无余数的是女。
2、D3=TEXT(MID(B3,7,6),"00-00")
文本函数TEXT(值,数值格式)
MID(B3,7,6)=199010,TEXT("199010","00-00")=1990-10。
3、E3=YEAR(NOW())-MID(B3,7,4)
YEAR(NOW())=2020,MID(B3,7,4)=1990,2020-1990=30。
五、应收账款账龄分析
领导都很关注应收账款的回款情况,财务去查账龄,就要去查找合同、记账凭证和发票,如果按下图做成表格,设置公式,是不是很清晰地就能看到账龄和是否该催要账款了呢?
解析:
1、F4=TODAY()-C4
today函数插入当前的日期,截止目前天数等于当前日期和开票日期的天数差额。
2、G4=IF(TODAY()-C4>E4,"到期应收款","")
当前日期和开票日期的天数差额大于付款天数120时显示到期应收款,条件格式包含文本红色显示,可以及时地催要应收账款。
3、H4=IF(AND(F4>0,F4<=30),"1个月内",IF(AND(F4>30,F4<=180),"1个月-6个月",IF(AND(F4>180,F4<=360),"6个月-1年","")))
利用IF函数多条件进行账龄分析,0<当前日期和开票日期的天数差额≤30天时显示1个月内,30<当前日期和开票日期的天数差额≤180天时显示1个月-6个月,180<当前日期和开票日期的天数差额≤360天时显示6个月-1年。
六、工资表数据核对
工资表和银行批量转账上传文件的数据也是要核对的,人员多的时候只用肉眼看是不容易找出其中的不同的,就要用到条件函数去比对数据的一致性。
解析:
E3=IF(A3=C3,"","不一致"),F3=IF(B3=D3,"","不一致")
先将两表放在不同行不同列,都按姓名升序排列后移到同一行,然后利用条件函数比对数据,一致的不显示数据,不一致的显示不一致。
财务工作有很多的技巧让我们去发现,运用excel公式省时省力,可以快速地满足领导的需求,工作也可以事半功倍。每个人都会看到很多关于excel公式的文章,但只有爱钻研勤奋好学的人才会在工作之余悄悄地进步,把所学运用到工作中。
注:文章版权归原作者所有,内容仅供读者学习、交流之目的,如有侵权,请联系我们删除。
联系邮箱:zirongzhang@ningmengyun.com