巧用EXCEL建立合同管理台账并动态进行管理

CO N T EM P O RARYECO N O M IC S 《当代经济》2010年6 月（下） 0 0 合计 1 2010-2-7 2010-004 城东新城甲城目勘察 合同 劳动合同范本免费下载装修合同范本免费下载租赁合同免费下载房屋买卖合同下载劳务合同范本下载 杭州市丁 工程 路基工程安全技术交底工程项目施工成本控制工程量增项单年度零星工程技术标正投影法基本原理 勘察设计公司 2，000，000.00 2 2010-6-6 2010-003 城东新城甲项目施工合同 浙江省丙施工总承包单位 100，000，000.00 3 2010-6-6 2010-004 城东新城甲城目勘察合同 杭州市丁工程勘察设计公司 13，830，000.00 80.000.00 5，000，000.00 100，000.00 4.00% 5.00% 9.00% 第 1次 第 1次 第 2次 序号 日期 合同号 合同名称 及标的 合同相对方 合同总金额 本期付款金额 累计已付款占 总金额比例 第几次 付款 表 4 明细账 4 2010-6-6 2010-004 城东新城甲城目勘察合同 杭州市丁工程勘察设计公司 150，000.00 16.50% 第 3次 5 2010-4-6 2010-002 办公家具购销合同 浙江乙办公室具制造有限公司 300，000.00 50.00% 第 1次 6 2010-1-5 2010-001 房屋 租赁合同 房屋租赁合同免费下载租赁合同范本下载上海市房屋租赁合同下载办公室租赁合同免费下载免费房屋租赁合同下载 杭州市甲指挥部 100，000.00 25.00% 第 1次 7 2010-3-10 2010-001 房屋租赁合同 杭州市甲指挥部 100，000.00 50.00% 第 2次 8 2010-3-11 2010-003 城东新城甲项目施工合同 浙江省丙施工总承包单位 8，000，000.00 13.00% 第 2次 2，000，000.00 2，000，000.00 600，000.00 400，000.00 400，000.00 100，000，000.00 0 合计 1 2010-001 2009-12-31 房屋租赁合同 杭州市甲指挥部 办公室 2 2010-002 2010-1-8 办公家具购销合同 浙江乙办公家具制造有限公司 办公室 3 2010-003 2010-2-1 城东新城甲项目施工合同 浙江省丙施工总承包单位 工程部 4 2010-004 2010-2-14 城东新城甲城目勘察合同 杭州市丁工程勘察设计公司 工程部 - 李主任 张助理 杨主任 吴主任 2010-12-30 2010-3-15 2012-1-3 2012-1-15 103，000，000 400，000.00 600，000.00 100，000，000.00 2，000，000.00 13，830.000 200，000.00 300，000.00 13，000，000.00 330，000.00 序号 合同号 合同签订 日期 合同名称 及标的 合同相对方 部门 经办人 合同记载 完成日期 合同 总价款 合同 已支付金额 表 1 总账—左侧部分—合同基本信息 0 合计 1 2010-001 2009-12-31 房屋租赁合同 杭州市甲指挥部 2 2010-002 2010-1-8 办公家具购销合同 浙江乙办公室具制造有限公司 3 2010-003 2010-2-1 城东新城甲项目施工合同 浙江省丙施工总承包单位 4 2010-004 2010-2-14 城东新城甲城目勘察合同 杭州市丁工程勘察设计公司 租赁 购销 建安 勘察 0.001 0.0003 0.0003 0.0005 31，580.00 400.00 180.00 30，000.00 1，000.00 201001 201001 201002 201002 序号 合同号 合同签订 日期 合同名称 及标的 合同相对方 备注 合同 类型 印花 税率 印花 税率 印花税 所属时期 表 3 总账—右侧部分—印花税信息 【摘要】面对数量大、类型多、金额巨、涉及广、收付数量频 繁的合同管理要求，要做好合同的日常登记和合同执行进度的 动态更新，建立一个方便易用的合同管理台账将会明显提高合 同管理的工作效率。 本文就单位实际情况用 EXCEL 编制了具 备合同总账、动态明细账、印花税计提等功能与一体合同管理 台账，以期与读者交流。 【关键词】合同管理 台账 印花税 Excel 众所周知，微软 OFFICE软件中的 EXCEL在数据管理方面 的功能十分强大，随着 EXCEL应用的大规模普及，凡是小型数 据管理或 表格 关于规范使用各类表格的通知入职表格免费下载关于主播时间做一个表格详细英语字母大小写表格下载简历表格模板下载 制作方面的工作，在应用了 EXCEL之后，效率得 到了大幅提高。然而，有些用户包括笔者在内在刚开始使用 EXCEL的时候，面对满是表格线的界面会显得一筹莫展，甚至觉 得画表格填数据还不如WORD方便，这其实只是因为这些用户 对陌生的软件界面产生的恐惧而造成的。然而，当有些用户看到 了 EXCEL某些简单的计算功能，如 SUM（）求和、SUMIF（）条件 求和及 count（）数字统计和 countif（）数字条件统计函数等简单 函数在日常数据管理工作中的方便应用，便对 EXCEL产生了依 赖，继而对 EXCEL发生了兴趣。在财务的日常管理中，合同作为 一项重要的日常工作，其合同数量大、类型多、金额巨、涉及广、 收付数量频繁，往往一个合同执行期长达几年，每月均涉及到合 同款项的支付。面对如此繁杂的合同日常管理和合同执行进度 的动态更新要求，稍有不慎，就会出现合同管理偏差甚至超付的 情形。为此，笔者就单位实际情况用 EXCEL编制了具备合同总 账、执行动态明细账、印花税计提等功能与一体合同管理台账。 巧用 EXCEL建立 合同管理台账并动态进行管理 ○魏 强 （杭州市铁路投资有限公司 浙江 杭州 310000） 管 理 世 界 0 合计 13，830，000 1 2010-001 2009-12-31 房屋租赁合同 杭州市甲指挥部 200，000.00 2 2010-002 2010-1-8 办公家具购销合同 浙江乙办公室具制造有限公司 300，000.00 3 2010-003 2010-2-1 城东新城甲项目施工合同 浙江省丙施工总承包单位 13，000，000.00 4 2010-004 2010-2-14 城东新城甲城目勘察合同 杭州市丁工程勘察设计公司 330，000.00 89，170，000 200，000.00 300，000.00 87，000，000.00 1，670，000.00 13.43% 50.00% 50.00% 13.00% 16.50% 序号 合同号 合同签订 日期 合同名称 及标的 合同相对方 合同已支付金额 合同 余款 完成 情况 付款 方式 备注 表 2 总账—中间部分—合同已支付信息及完成情况等 62 CO N T EM P O RARYECO N O M IC S 《当代经济》2010年6 月（下） 一、合同管理台账的设计思路 第一，通过分别登记合同管理台账总账与明细账，并使之 通过“合同号”这一关键字相互关联，再通过 EXCEL的相关数 据操作技巧，达到合同管理可合可分的灵活管理目的，并衍生 出计提合同印花税的功能。 第二，合同管理台账应具备的几项功能：其一，合同总账管 理，根据合同流水号记录合同签订的日期、标的或合同内容、相 对方、经办人、合同记载的大致完成日期、合同总金额、已付款金 额和占比、合同记载的付款方式、合同类型、印花税率等。其二， 合同明细账管理，根据日常合同执行进度，流水记录每期支付的 合同进度款，为简化录入并提高录入准确性，该台账的合同信息 如合同名称、相对方、总金额等均可通过 vlookup函数自动填 充。其三，印花税计提，印花税是以经济活动中签立的各种合同、 产权转移书据、营业账簿、权利许可证照等应税凭证文件为对象 所征的税。其中合同作为印花税计提的重点，可通过合同管理台 账准确计提，以减少财务人员计提印花税的失误可能。 第三，建立相关表格。实现以上功能需建立四张表格：总 账、明细账、印花税计提、印花税率，具体可参见后附 案例 全员育人导师制案例信息技术应用案例心得信息技术教学案例综合实践活动案例我余额宝案例 。 二、合同管理台账案例介绍 1、建立“总账”表格。 字段为“序号、合同号、合同签订日期、 合同名称及标的、合同相对方、部门、经办人、合同记载完成日 期、合同总价款、合同已支付金额、合同余款、完成情况、付款方 式、备注、合同类型、印花税率、印花税额、印花税所属时期”，具 体内容及公式见表 1、表 2、表 3及表后说明。 总账用到的公式（均以每表中的第一行数据为例进行说明）： 除以上 5列内容外，其余列均根据合同实际情况录入。 2、建立“明细账”表格。 字段为“序号、日期、合同号、合同名 称及标的、合同相对方、合同总金额、本期付款金额、累计已付 款占总金额比例、第几次付款”，通过这张明细账，可与总账以 “合同号”为关键字进行关联，将总账中的合同基本信息引入到 明细账中，又可将本明细账中每个合同的每笔支付金额自动统 计到总账中，实现了合同进度的实时把握，同时，又可通过“筛 选”功能筛选出明细账每个合同共有几笔支付记录，实现总分 结合，灵活查询的目的。具体见表 4及表后说明。 明细账用到的公式（均以每表中的第一行数据为例进行说明）： 除以上 5列内容外，其余列均根据合同实际情况录入。 3、建立“印花税率”表格。 定义印花税种和税率，为计提印 花税准备，见表 5。 4、建立“印花税计提”表。 字段为“申报所属时期、申报日 期、申报基数、类型、税率、税额”，通过该表与总账的两个关键 字即“申报所属时期”和“类型”进行关联，用多条件求和函数自 动将总账中合同金额汇总到本表的“申报基数”中，根据相应税 率自动算出本月需计提缴纳的各类合同印花税，详见表 6。 印花税计提用到的公式： 申报基数为多条件求和函数，外部大括号并非手工录入， 而需要在录入公式完成后按 ctrl+shift+enter组合键完成，该函 数应用得当将在财务工作许多方面提高工作效率。 三、合同管理台账中的几个重要函数介绍 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）* （总账! $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+enter组合键，这时，该多条 件求和函数将会自动被外部大括号括起来。 【参考文献】 [1] 恒盛杰资讯：Excel 会计与财务管理经典 108 例[M].中国青 年出版社，2007. [2] 李俊杰：会计信息系统内部控制浅析[J].现代商业，2009（15）. [3] 李闻一、李世宗、张耀武：会计信息系统[M].华中科技大学出 版社，2006. 申报基数 类型 税率 税额 合计 103，000，000.00 31，580.00 201001 2010-2-5 400，000.00 租赁 0.00100 400.00 201001 2010-5-10 600，000.00 购销 0.00030 180.00 201002 2010-5-10 100，000，000.00 建安 0.00030 30，000.00 201002 2010-5-10 2，000，000.00 勘察 0.00050 1，000.00 申报所 属时期 申报日期 印花税自动计算数 表 6 印花税计提 借款 0.00005 购销 0.0003 技术 0.0003 建安 0.0003 勘察 0.0005 承揽 0.0005 运输 0.0005 账薄 0.0005 保险 0.01 仓储 0.001 租赁 0.001 合同 类型 税率 借款合同（金融业按借金额） 购销合同 技术合同 建筑安装工程承包合同 建筑工程勘察设计合同 加工承揽合同 货物运输合同 营业资金账薄 财产保险合同 仓储保管合同 财产租赁合同 说 明 表 5 印花税率 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 0.05‰ 0.3‰ 0.3‰ 0.3‰ 0.5‰ 0.5‰ 0.5‰ 0.5‰ 1‰ 1‰ 1‰ （十万分之五） （万分之三） （万分之三） （万分之三） （万分之五） （万分之五） （万分之五） （万分之五） （千分之一） （千分之一） （千分之一） 合同已支付金额 =SUMIF（明细账! C:C，"="& 总账! B3，明细账! G:G） 合同余款 =I3-J3 完成情况 =IF （AND （I3 =J3，J3 <>0），"完成 "，IF （J3 <=0，""，IF （I3>0，J3/I3，""））） 印花税率 =VLOOKUP（O3，印花税率! A:AI，2，FALSE） 印花税额 =ROUND（I3*P3，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）&" 次"，""） 申报 基数 {=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) 管 理 世 界 63