巧用EXCEL建立合同管理台帐并动态管理合同

1、巧用 excel建立合同管理台账并动态进行管理（杭州市铁路投资有限公司魏强）【摘要】面对数量大、类型多、金额巨、涉及广、收付数量频繁的合同管理要求，要做好合同的日常登记和合同执行进度的动态更新，建立一个方便易用的合同管理台账将会明显提高合同管理的工作效率。笔者就单位实际情况用excel编制了具备合同总账、动态明细账、 印花税计提等功能与一体合同管理台账，以期与读者交流。【关键词】合同管理台账 印花税 excel众所周知，微软office软件中的 excel在数据管理方面的功能十分强大，随着excel应用的大规模普及，凡是小型数据管理或表格制作方面的工作，在应用了excel之后 , 效率得到了大

2、幅提高。然而，有些用户包括笔者在内在刚开始使用excel的时候，面对满是表格线的界面会显的一筹莫展，甚至觉得画表格填数据还不如word方便，这其实只是因为这些用户对陌生的软件界面产生的恐惧而造成的。然而，当有些用户看到了excel某些简单的计算功能，如 sum（）求和、 sumif（）条件求和及count （）数字统计和 countif() 数字条件统计函数等简单函数在日常数据管理工作中的方便应用，便对excel产生了依赖，继而对excel发生了兴趣。笔者所在一家大型国有建设单位，成立于2006 年末，主要从事城东新城 9.3平方公里范围内的基础设施的开发、建设和管理，并完成区域内农民及居民的

3、拆迁安置和各类安置房的建管工作，尤其是杭州东站东西广场项目，是笔者单位重点建设管理的项目之一。在财务的日常管理中，合同作为一项重要的日常工作，其合同数量大、类型多、金额巨、涉及广、收付数量频繁，往往一个合同执行期长达几年，每月均涉及到合同款项的支付。面对如此繁杂的合同日常管理和合同执行进度的动态更新要求，稍有不甚，就会出现合同管理偏差甚至超付的情形。为此，笔者就单位实际情况用excel 编制了具备合同总账、执行动态明细账、印花税计提等功能与一体合同管理台账。在此，笔者就编制的思路和具体台账作一介绍，以期与读者交流并相互提高。一、合同管理台账的设计思路（一）通过分别登记合同管理台账总账与明细账，

4、并使之通过“合同号”这一关键字相互关联，再通过excel的相关数据操作技巧，达到合同管理可合可分的灵活管理目的，并衍生出计提合同印花税的功能。（二）合同管理台账应具备的几项功能：1、合同总账管理，根据合同流水号记录合同签订的日期、标的或合同内容、相对方、经办人、合同记载的大致完成日期、合同总金额、已付款金额和占比、合同记载的付款方式、合同类型、印花税率等；2、合同明细账管理，根据日常合同执行进度，流水记录每期支付的合同进度款，为简化录入并提高录入准确性，该台账的合同信息如合同名称、相对方、总金额等均可通过vlookup函数自动填充。3、印花税计提，印花税是以经济活动中签立的各种合同、产权转移书

5、据、营业账簿、权利许可证照等应税凭证文件为对象所征的税。其中合同作为印花税计提的重点，可通过合同管理台账准确计提，以减少财务人员计提印花税的失误可能。（二）建立相关表格实现以上功能需建立四张表格：总账、明细账、印花税计提、印花税率，具体可参见后附案例。二、合同管理台账案例介绍1、建立“总账”表格，字段为“序号、合同号、合同签订日期、合同名称及标的、合同相对方、部门、经办人、合同记载完成日期、合同总价款、合同已支付金额、合同余款、完成情况、付款方式、备注、合同类型、印花税率、印花税额、印花税所属时期”，具体内容及公式见表1-1 、1-2 、 1-3 及表后说明。表 1-1总账左侧部分合同基本信息

6、表 1-2总账中间部分合同已支付信息及完成情况等表 1-3总账右侧部分印花税信息总账用到的公式（均以每表中的第一行数据为例进行说明）合同已支付金额=sumif(明细账 !c:c,=&总账 !b3, 明细账 !g:g)合同余款=i3-j3完成情况=if(and(i3=j3,j30),完成 ,if(j30,j3/i3,)印花税率=vlookup(o3,印花税率 !a:ai,2,false)印花税额=round(i3*p3,2)除以上 5 列内容外，其余列均根据合同实际情况录入。2、建立“明细账”表格，字段为“序号、日期、合同号、合同名称及标的、合同相对方、合同总金额、本期付款金额、累计已付款占总金

7、额比例、第几次付款”，通过这张明细表 2明细账账，可与总账以“合同号”为关键字进行关联，将总账中的合同基本信息引入到明细账中，又可将本明细账中每个合同的每笔支付金额自动统计到总账中，实现了合同进度的实时把握，同时，又可通过“筛选”功能筛选出明细账每个合同共有几笔支付记录，实现总分结合，灵活查询的目的。具体见表2 及表后说明。明细账用到的公式（均以每表中的第一行数据为例进行说明）合同名称及标的=vlookup(c3,总账 !b:an,3,false)合同相对方=vlookup(c3,总账 !b:an,4,false)合同总金额=vlookup(c3,总账 !b:an,8,false)累计已付款占

8、总金额比例=if(f3,sumif($c$3:c3,=&c3,$g$3:g3)/f3,)第几次付款=if(g3,第 &countif($c$1:c3,=&c3)&次 ,)除以上 5 列内容外，其余列均根据合同实际情况录入。3、建立“印花税率”表，定义印花税种和税率，为计提印花税准备。见表3表 3印花税率4、建立“印花税计提”表，字段为“申报所属时期、申报日期、申报基数、类型、税率、税额”，通过该表与总账的两个关键字即“申报所属时期”和“类型”进行关联，用多条件求和函数自动将总账中合同金额汇总到本表的“申报基数”中，根据相应税率自动算出本月需计提缴纳的各类合同印花税。详见表4。表 4印花税计提印

9、花税计提用到的公式=sum( 总账 !$r$3:$r$10000=$a4)*(总账 !$o$3:$o$10000=$d4)* 总申报基数账 !$i$3:$i$10000)税率=vlookup(d4,印花税率 !a:ai,2,false)税额=round(c4*e4,2)说明： 申报基数为多条件求和函数，外部大括号并非手工录入，而需要在录入公式完成后按ctrl+shift+enter组合键完成，该函数应用得当将在财务工作许多方面提高工作效率。三、合同管理台账中的几个重要函数介绍。1、 vlookup()函数 , 具体用法及参数说明如下：语法： vlookup(关键字，被查找的数据范围，返回数据范

10、围匹配值的列数， false或 true) 。示例如下，在明细账的d3 单元格填写： =vlookup(c3,总账 !b:an,3,false) 。式中：“ c3 ”代表以明细账中c3 合同编号关键字， “总账 !b:an ”代表被搜索范围为总账中第b 列到 an列，其中b 列填写为合同号， “ 3 ”则代表返回b 至 an列中的第3 列单元格即“合同名称及标的”的结果，false代表精确匹配。2、多条件求和函数=sum( 总账 !$r$3:$r$10000=$a4)*(总账 !$o$3:$o$10000=$d4)* 总账!$i$3:$i$10000)这个函数在印花税计提表中申报基数单元格中，其中：总账!$r$3:$r$10000=$a4代表“总账”与“印花税计提”的“印花税所属时期”相匹配；总账!$o$3:$o$10000=$d4代表“总账”与“印花税计提”的“合同类型”相匹配；总账!$i$3:$i$10000代表自动求和满足前两个匹配条件的s 列，即“合同总金额” 。值得注意的是，在多条件求和函数公式录入完成后，直接回车将不会显示正确结果，必须在公式录入后按ctrl + shift