巧用EXCEL建立合同模板管理台帐并动态管理合同模板.docx

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

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

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

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

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

6、支付信息及完成情况等ABCDEJKLMN1序号合同号合同签订 日期合同名称及标的合同相对方合同 已支付金番合同 余款 成况 完情付款方5£备注-J_30合计13,830,00089,170,00013.43%12010-0012009-12-31房屋租赁合同杭州市甲指挥部200,000.00200,000.0050.00%422010-0022010-1-8亦公家具购销合同浙江乙亦公家具制 造有限公司300,000.00300,000.0050.00%532010-0032010-2-1城东新城甲项目施工合同浙江省丙施工总承 宜革位13,000,000.0087,000,000.00

7、13.00%6042010-0042010-2-14城东新城甲项目勘察合辰0杭州市丁工程勘彳 设计公司330,()00.001,670,000.0016. 50%422010-0022010-1-8水公家具购销合同浙江乙亦公家具制 造有限公司亦公室张助理2010-3-15600,000.00300,000.00532010-0032010-2-1城东新城甲项目施工合同浙江省丙施工总承 包单位工程部杨主任2012-1-3100,000,000.0013,000,000.00642010-0042010-2-14城东新城甲项目勘察合同杭州市丁工程勘察 设计公司工程部吴主任2012-1-152,00

8、0,000.00330,000.00ABCDEN0PQR1序号合同号合同签订 日期合同名称及标的合同相对方备注S印花 税率花簇 印税印花税 所属 时期20合计31,580.00312010-0012009-12-31房屋租赁合同杭州市甲指挥部租赁0.001400.00201001422010-0022010-1-8亦公家具购销合同浙江乙亦公家具制 造有限公司购销0.0003180.00201001532010-0032010-2-1城东新城甲项目施工合曰浙江省丙施工总承 包单位建安0.000330,000.00201002642010-0042010-2-14城东新城甲顼目勘察合曰杭州市丁工程

9、勘察 设计公司勘察0.00051,000.00201002表13表13总账一右侧局部一印花税信息总账用到的公式均以每表中的第一行数据为例进展说明）合同已支付金额=SUMIF(明细账!C:C, =&总账!B3,明细账!G:G)合同余款=I3-J3完成情况=IF(AND(I3=J3, J3O0),/z完成,IF(J3<=0, ，IF(I3>0, J3/I3, )印花税率=VL00KUP(03,印花税率!A:AI, 2, FALSE)印花税额二 R0UND(I3*P3,2)除以上5列内容外，其余列均根据合同实际情况录入。2、建立“明细账表格，字段为“序号、日期、合同号、合同名称及

10、标的、合同相对方、合同总金额、本期付款金额、累计己付款占总金额比例、第几次付款，通过这张明表2明细账ABCDEFGHI1序号日期合同号合同名称及标的合同相对方合同总金额本期付款金额累计己付 款占总金 额比例第几次付款I20合计13,830,000.00312010-2-72010-004城东新城甲项目勘察合同杭州市丁工程勘察设计公司2, 000, 000. 0080,000.004.00%第1次422010-6-62010-003城东新城甲项目施工合同浙江省丙施工总承包单位100, 000, 000. 005,000,000.005.00%第1次532010-6-62010-004城东新城甲项

11、目勘察合同杭州市丁工程勘察设计公司2, 000, 000. 00100,000.009.00%第2次642010-6-62010-004城东新城甲项目勘察合同杭州市丁工程勘察设计公司2, 000, 000. 00150,000.0016.50%第3次752010-4-62010-002办公家具购销合同浙江乙办公家具制造有限公 司600, 000. 00300,000.0050.00%第1次862011-1-52010-001房屋租赁合同杭州市甲指挥部400, 000. 00100,000.0025.00%第1次912010-3-102010-001房屋租赁合同杭州市甲指挥部400, 000.0

12、0100,000.0050.00%第2次1082010-3-112010-003城东新城甲项目施工合同浙江省丙施工总承包单位100, 000, 000. 008,000,000.0013.00%第2次细账，可与总账以“合同号为关键字进展关联，将总账中的合同根本信息引入到明细账 中，又可将本明细账中每个合同的每笔支付金额自动统计到总账中，实现了合同进度的实 时把握，同时，又可通过“筛选功能筛选出明细账每个合同共有几笔支付记录，实现总 分结合，灵活查询的目的。具体见表2及表后说明。明细账用到的公式均以每表中的第一行数据为例进展说明）合同名称及标的=VL00KUP(C3,总账！B:AN, 3, FA

13、LSE)合同相对方二VL00KUP(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列内容外，其余列均根据合同实际情况录入。3、建立“印花税率表，定义印花税种和税率，为计提印花税准备。见表3印花税率ABC1合同类型税率说明2借款0. 00005借款合同（金融业按借贷金额）1

14、00%0.05%0（十万分之五）3购销0. 0003购销合同1。0.3%o（万分之三）4技术0. 0003技术合同10。0.3%o（万分之三）5建安0. 0003建筑安装工程承包合同10.3%o（万分之三）6勘察0. 0005建筑工程勘察设计合同100% 0.5%o（万分之五）7承揽0. 0005加工承揽合同100%0.5%o（万分之五）8表4Liu运输1心收0. 0005V. VX货物运输合同100%0.5%o（万分之五）_印花税计提盾”I LJ2。/u 1 1 人一 /ABCDEF1申报所属 时期申报日期印花税自动计算数2申报基数类型税率税额3合计103, 000, 000. 0031,

15、580.0042010012010-2-5400,000.00租赁0.00100400.0052010012010-5-10600,000.00购销0.00030180.0062010022010-5-10100,000,000.00建安0.0003030,000.0072010022010-5-102?000,000.00勘察0.000501,000.004、建立“印花税计提表，字段为“申报所属时期、申报日期、申报基数、类型、税 率、税额”，通过该表与总账的两个关键字即“申报所属时期“和“类型”进展关联，用 多条件求和函数自动将总账中合同金额汇总到本表的“申报基数中，根据相应税率自动 算出本

16、月需计提缴纳的各类合同印花税。详见表4。印花税计提用到的公式说明：申报基数为多条件求和函数，外部大括号并非手工录入，而需要在录入公式完成后按申报基数= SUM (总账! $R$3: $R$10000=$A4) * (总账! $0$3: $0$10000=$D4) *总账!$1$3:$1$10000)税率二VL00KUP(1)4,印花税率!A:AI, 2, FALSE)税额二ROUND(C4*E4, 2)ctrl+shift+enter组合键完成，该函数应用得当将在财务工作许多方面提高工作效率。三、合同管理台账中的几个重要函数介绍。1> vlookup()函数,具体用法及参数说明如下：语法

17、：VLOOKUP(关键字，被查找的数据范围需关键字所在列起头，返回数据范围匹 配值的列数，false或true准确匹配或近似匹配)。例如如下，在明细账的D3单元格填写:=VLOOKUP(C3,总账!B:AN, 3, FALSE) o式中:“ C3 代表以明细账中C3合同编号关键字，“总账!B:AN代表被搜索范围为总账中第B列到AN 列，其中B列填写为合同号，“ 3那么代表返回B至AN列中的第3列单元格即“合同名 称及标的的结果，false代表准确匹配。2、多条件求和函数住SUM(总账! $R$3: $R$10000=$A4) * (总账! $0$3: $0$10000=$D4) *总、 账!$1$3:$1$10000)这个函数在印花税计提表中申报基数单元格中，其中：总账!$R$3:$R$10000=$A4代表 “总账与“印花税计提的“印花税所属时期相匹配；总账!$0$3:$0$10000=$D4代表 “总账