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

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

2、率得到了大幅提高。然而，有些用户包括笔者在内在刚开始使用EXCEL的时候，面对满是表格线的界面会显的一筹莫展，甚至觉得画表格填数据还不如WOR方便，这其实只是因为这些用户对陌生的软件界面产生的恐惧而造成的。然而，当有些用户看到了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及表后说明。表

6、1-1总账一左侧部分一合同基本信息占aCDEFIJ1呼号合恫号會同生订V合同HI对右合同逗我 完VV合同2-101000.00013PUrU0U312010-0012009-12-31府JE希皆右同制I市甲捐挥耦李主任2D1D-12-30400.000.00200.IDOO.0042201D-OD22010-1-0浙衽乙办公家具制 违有限公司办仝室张an理2C1CKJ-1560D.Q0Q.Q0300.000.0053201D-OQ32010-2-1竦东新喩甲项目施工合同浙江罟丙施工思乎 包单住工矽畅主1?2012-1-310D.OOD.aOD. 0013.D0a.aDDi GO642010-0

7、042D 10-2-1 d城东断威甲更目曲酥合同抗州巾丁工程瞬 蹄込司工程至臭主任2012-1-15E.aoD.aoD.aosso.oao.oa表1-2总账一中间部分一合同已支付信息及完成情况等除以上5列内容外，其余列均根据合同实际情况录入。A AEcD DE EJ JK KL LJ J1 1合同駐订w同X 同碱 合余tfAtfA 完怡注2 2oo o 0 0 仇7 7 ajaj flflLJLJ1-4 4 IdId26 696 6L表1-3总账一右侧部分一印花税信息ABcDE0PQR1序号合同号T舍同釜订 日期合同茗称及标的合同相对舟备注”审印花 税爭r印佻T印花説 所展 旳期*20合计3L

8、50O 0012010-0012009-12-31房星玛萤合同杭州市甲指挥詔租長0 001400 002010013d22010-0022U10-1-0浙江乙办公冢具制 造有限公司0 00031B0 00201001532010-0032010-2-1城东新城甲项目施工合伺浙江省丙施工总淨 包药徒建安0.000330,000. DO2D1D02642010-0012010-2-14城东新城甲项目勘疥合伺杭州芾丁工程勒察 设计佥同0 DQD5bQOO DO201QQ2总账用到的公式（均以每表中的第一行数据为例进行说明）合同已支付金额=SUMIF（明细账！C:C,=& 总账!B3,明细账!G:G）

9、合同余款=I3-J3完成情况=IF(AND(l3=J3,J30),完成,IF(J3O,J3/I3,)印花税率=VLOOKUP(O印花税率！A:AI,2,FALSE)印花税额=ROUND(I3*P3,2)除以上5列内容外，其余列均根据合同实际情况录入。，通过这张明细2、建立“明细账”表格，字段为“序号、日期、合同号、合同名称及标的、合同相对方、合同总金额、本期付款金额、累计已付款占总金额比例、第几次付款”表2明细账*cDEFGHI1库号VIP台同号IP标的合同+剛方合FLS金瓯T本期付撚金韻T舉计己付 菽占急金 杭比釈T剳1次讨款2F0合汁1030,000.003120L0-2-72DL0-0M

10、城东新加甲项目赣音同时ifi 丁工程翊if浚计令司2. KO. 000. 008drODO.Oa4.00%无1出423010-003城祇和iiM甲血目是工合同浙江苦丙直工总哥怛单愎LOO/QOO.OOO QQEEM00B Q05 00%532DI0-6-62DL0-0O4就州帀丁口iEt橐役计書司2.|00|.000 00ioa.ODO.oa0.0064iOLO-6-62OLQ-0M城东新圾甲项目映書同帕+1市丁工召映浚计鉴司000.000. 001601000.0016.50%7520II0-4-62DL0-002办悵凉旦肉稍合同浙江乙办瓷冢拭碾 百限仑 祠观QQQ QQ30000.00$4

11、40%*1363DB Fl-52010-001层足构衰台间400,000. 00100,000.0025.00%Jgi；x9120I0-X02010-001杭押市甲揩挥刮400,000 00100.000.0050.00%102D II0-3H1I20L0-003區东新垢甲项目施工合H浙江省丙苗工总母包单愎0叫 DODjODO. 00aoaono.no19.00%账，可与总账以“合同号”为关键字进行关联，将总账中的合同基本信息引入到明细账中, 又可将本明细账中每个合同的每笔支付金额自动统计到总账中，实现了合同进度的实时把 握，同时，又可通过“筛选”功能筛选出明细账每个合同共有几笔支付记录，实现

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)&次,)3、建立“印花税率”表，定义印花税种和税率，为计提印花税准备。见表表3印花税率Ac1合同类型合同类型税率说明说明2借款借

13、款0. 00005借款合同（金融业按措贷金额）1 %0.応制応制十万十万分分之五）之五）3购销购销0. 0003购销合同100%0.3%0（万分之三）（万分之三）4技术技术10. 0003技术合同100%0,3%o（万分之三）（万分之三）5建安建安0, 0003建筑安装工程承包合|H|100% 0.3%O（万分之三）（万分之三）6勘察勘察0. 0005建筑工程勘察设计合同血 0.5%9（万分之五）（万分之五）7承揽承揽a 0005加工承揽合同100%0.5%a（万分之五）（万分之五）S运输运输0, 0005蛍物运输合同100%0.5%o（万分之五）（万分之五）9帐簿帐簿0. 0005营业资金帐

14、簿100%0.5%（万分（万分之五）之五）10保险保险CL 01财产保险合同100%陽陽（千分之一）（千分之一）11仓催仓催0, 001仓储保管合同100%1%0（千分之（千分之一）一）12租赁租赁0. 00.1财产租赁合同100%1%a刊刊之之）4、建立“印花税计提”表，字段为“申报所属时期、申报日期、申报基数、类型、税 率、税额”，通过该表与总账的两个关键字即“申报所属时期”和“类型”进行关联，用多 条件求和函数自动将总账中合同金额汇总到本表的“申报基数”中，根据相应税率自动算 出本月需计提缴纳的各类合同印花税。详见表4。表4印花税计提| AB CDEF1申报所属申报所属印花稅自动计算数印

15、花稅自动计算数2时期时期申抿日期申抿日期申抿却矣型矣型税率税率稅额稅额3合计合计103,000,000.0031580. 0042010012010-2-5400,000 00租赁租赁0.00100400 005201001201 0-5-1 0600,000 00购销购销0.00090180.00620W02201 3-5-1 010000,000.000.0008030,000.007201002201 0-5-1 02,000,000 000.000501,000.00印花税计提用到的公式申报基数=SUM(总账!$R$3:$R$10000=$A4)*(总账!$O$3:$O$10000=$

16、D4)* 总账!$1$3:$1$10000)税率=VLOOKUP(D4印花税率!A:AI,2,FALSE)税额=ROUND(C4*E4,2)说明：申报基数为多条件求和函数，外部大括号并非手工录入，而需要在录入公式完成后按ctrl+shift+e nter组合键完成，该函数应用得当将在财务工作许多方面提高工作效率。三、合同管理台账中的几个重要函数介绍。1、vlookup()函数，具体用法及参数说明如下：语法：VLOOKUP关键字，被查找的数据范围，返回数据范围匹配值的列数，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)*(总账!$0$3:$O$10000=$D4)* 总 账!$1$3:$1$10000)这个函数在印花税计提表中申报基数单元格中，其中：总账！$R$3:$R$10000=$A4代表“总账”与“印花税计提”的“印花税所属时期”相匹配；总账！$0$3:$O$10000=$D4代表“总账”与“印花税计提”