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

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

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

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

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

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

6、t融日期.物柢借款T20id3.uiwinnnlrffi3a.naoiS200S-12-31盛宝祖鼻=1良丁上市当艳悻宗势乃宣季三任201M2-J0joaoooon200.000.00*1642DI0-002如占黑具H揖呻满工T力公京艮干1流就独司办公室如硬2010-3-15eoq口皿doSDfl.QOOGa52010-2-1跣帮蝌酝就X胴即卸同施工总承4ULfi工酶福主任期1IDEWOrnounnuouD64.JDU-OC2DTICh-14菊即妒项3旬音&同性沙力J_L里就与帙计同工程秫星三任2D12-1-15EXIL口口33O.aaDDD额、印花税所属时期”，具体内容及公式见表1-1、1-

7、2、1-3及表后说明。表1-2总账一中间部分一合同已支付信息及完成情况等表1-3总账一右侧部分一印花税信息ACIEJKLNX1.弁同号nw.合同化后股标的令伺相对方-13已变F分苫*1暴茶V完鹿第祝,忖S?”*.20令计13,.30.00003/170,00(113,5i20fD-D012DLJ-I2-9IM出性砥1州市出搭厚甑200,000Uu200.000Ud9Q,CO,4i二口1o-r：i22D1M-B鹏烹膜期二用新江丁力士定且制厘郁?金三nunco300.00CO53?aTD-QO82011题串弄口书工方F芥江出可沌工用些包里也1aDOD.CQloo幻心ODDagw20TMOi2010

8、11亘韦F值小耳目船谷司抗川产J。匡勒总评出朝：33D.ODO001.67D.ODO00设耻合同已支付金额合同余款完成情况印花税率印花税额8B1nEU0Pf.L.V合国格订U期合同名.睥及标的V合同fU对方W淅在.合同案型V印章税率VfiW.核覆V械惊所属附用.?IU合计llBD.OD9：1snin-aniiDOS-12-31其宝租发占同杭州市甲揩辉都栏费D.DUI40U02D1001422air-0D22010-1-fi用超家具瑚学艺百舐江映公安具制曲地100C31BO.OD201001S320i(ka.y2l1U-2-1兼烹布装中/及帽_古尼撕工售丙蔚工总事3Pti逑安U.D0C33D.L

9、Dl.LU30ICU2G42010-an2010-2-14源于、新城中方F勘三四OlilJ-1程勘察设计公司勘霹0O0C5iroron?OIC(K总账用到的公式（均以每表中的第一行数据为例进行说明）=SUMIF明细账!C:C,=&总账旧3,明细账!G:G)=I3-J3=IF(AND(I3=J3,J30),完成,IF(J30,J3/I3,)=VLOOKUP(O即花税率!A:AI,2,FALSE)=ROUND(I3*P3,2)除以上5列内容外，其余列均根据合同实际情况录入。2、建立“明细账”表格，字段为“序号、日期、合同号、合同名称及标的、合同相对方、表2明细账ACDEFHILLfJUlM自冏勺,

10、L向冬和鼻出的*宫取i后万.用默付蕊工辅嚼己忖津占段解世迎VVZ-Q合i+13.030ODDao32010-2-12D10014稣初典甲4E勘宜三杭州市H1融康俄#金司2.CDX而由.刖BD.ODD.UU，口u弛第邨t42MLO-fi-6女伴科M甲曰E苗二言*月他营第S工自净包单位网QX1,ODDQQr_-1rr-riS0%第依5K却LD-M3j0-Dfl4手勃司甲r自助糕含尚匕州而工出独裁退#公司S.CCO,DCO.OOIDO.OOO009.00%覆次&刈CMT与于端用甲力后孙言中.一苴卅玉丁丁稽斯至砧叶上司2.m.mr1500Q0QQ1S,60M第做7S和gT2010-012J；寺班号三屈

11、杆江二由金奈RXittTJ强工司em.DDD.ODaDO.ODDDOISODO%第/Bg2UL1-1-S2KJlbD0!后回相*台司纪甘IF甲噌国那401OQOaIDQ.OOOOQS50(1%革成gT20105-10201D-DDL用巨柑Hu的力川L甲兆长邹3CO.DOIDOlOO.uu5O.M*察致103010-013哧fBfW甲引目加工合同法工审,工总学包学皮WD,IXO.jDIXi.O013W%第球合同总金额、本期付款金额、累计已付款占总金额比例、第几次付款”，通过这张明细账，可与总账以“合同号”为关键字进行关联，将总账中的合同基本信息引入到明细账中，又可将本明细账中每个合同的每笔支付金

12、额自动统计到总账中，实现了合同进度的实时把握，同时，又可通过“筛选”功能筛选出明细账每个合同共有几笔支付记录，实现总分结合，灵活查询的目的。具体见表2及表后说明。明细账用到的公式（均以每表中的第一行数据为例进行说明）合同名称及标的合同相对方合同总金额累计已付款占总金额比例第几次付款=VLOOKUP(C3、账!B:AN,3,FALSE)=VLOOKUP(C3、账!B:AN,4,FALSE)=VLOOKUP(C3、账!B:AN,8,FALSE)=IF(F3,SUMIF($C$3:C3,=&C3,$G$3:G3)/F3,)=IF(G3,第&COUNTIF($C$1:C3,=&C3)&次,)除以上5列

13、内容外，其余列均根据合同实际情况录入。印花税率3、建立“印花税率”表，定义印花税种和A3C1合同类型税率说明2借款0.00005喈款合冏诠融业按借贷全却）1皿%口D5%口（十万分在）3购箱0.C003陶特合同mi%n3怖历分之三Iq技未0,0003技术合同1OD%U.3%。，3分之三5建安0.0003建筑安装工程承包合同1口口口?虬（万分之三）6勘痕0.0005建箭H程勘察漫计合同1口石融（万分之五）7越0,0005加工承揽合同100%。5%1万分之五）B运输0.0005货物运输合同1000.5%0（万分之五）9幄簿0,0006营业资金帐簿1口口用。5%万分之五）10保险3.01唠产保险合同1

14、00%（千分之一）11仓储0.001仓储保管合同1口口%出（千分之一）12租赁0.001财产租赁合同1001%0,千分之一）表3税率，为计提印花税准备。见表34、建立“印花税计提”表，字段为“申报所属时期、申报日期、申报基数、类型、税率、表4印花税计提A二BCDEF1申报所属时期申报日期印花税自动计算数2申报基数类型税率税截3合计103,00000.Ou31,580.0042010012010-2-5400,00000租赁0.00100400.0052010012010-6-10600,00000购销0.00030190.0062010022010-6-101100,000,000.00建安0

15、0003030,000.0072010022010-5-102.000,00000勘察0.000501,000.00税额通过该表与总账的两个关键字即“申报所属时期”和“类型”进行关联，用多条件求和函数自动将总账中合同金额汇总到本表的“申报基数”中，根据相应税率自动算出本月需计提缴纳的各类合同印花税。详见表4。印花税计提用到的公式=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)说明：申报基数为多条件求和函数，

16、外部大括号并非手工录入，而需要在录入公式完成后按ctrl+shift+enter组合键完成，该函数应用得当将在财务工作许多方面提高工作效率。三、合同管理台账中的几个重要函数介绍。1、vlookup（）函数，具体用法及参数说明如下：语法：VLOOKU咪键字，被查找的数据范围需关键字所在列起头，返回数据范围匹配值的列数，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代表“总账”与“印花税