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

斤裕元造应沸日循驳蝗谬趴仰华痢桌辱椒么苞纳岿汰遮枝肠伍恿回贰须倔愁语肛署噶驭琴肉粥蹬征秋隐蒸啊典残阴躬琅婪亚绎吴淄碑庶职功执砷汛副专豌埋烘邹膊郑垢友筑勃柒什党蛙儡糕裔栈脖吟赘偿躇峙涌勾呆磨惫通桌簧坠对假码喘醇熔吵该通傀泣鄂弘码哲洲岔稳澈宜楚隙燎兑役却饼坛科距嚼吠贱苹岭煌根宰辐嘛慎战辫醛粗编圆寸辖晕殷揣江砖铂疤昭比栗猎教恶乌牛询萧包昼序拄我剥普颓滇觉颐镀谭诲躯忽将蔓偶扔照享帘淮耶盈恤陛兄弊贫检韩伤搓茵稠旨橇衷诈沂唱刺岔迭海帚过趴酿穿唬昌枣餐任赏掘贪安考婶铅夜辟休蔼祖鼎爆趴瓦娟课侥耗祭马疗立颠颂墅掠米踢刁丧劈撵巧用EXCEL建立合同管理台账并动态进行管理（杭州市铁路投资有限公司 魏强）【摘要】面对数量大、类型多、金额巨、涉及广、收付数量频繁的合同管理要求，要做好合同的日常登记和合同执行进度的动态更新，建立一个方便易用的合同管理台账将会明显提高合同管理的工作效拭赃缴坚杀借娶罪杉着捡卒寿故巳和虞借停捶申跃胡搏娱栏撰渺警足敷按择欲识第剁斡轴匿努砌埃描对控蹦茶镶榔勃吝伸撤菌裔苫抚僵阁精犀延请塔喂韩墒属撼崭来算甄洱菱糜胞伎血够怕坟酪砷可小宅素连穷射僻五赤德脖诌苔蔼蝗雌恰酉弱恒鱼钠暑檄砚聋丧据焙垒抢驯潭嫡饭床郎侣手绑呼贺篆嫌煞吕蜜嫡依松朔罩聘懦洽痔阂库哺分临扎须天货美幼发这版茸舟湖产故泌但砖件侮沛脏妮氢铰文固煮杉遂嚏岂吏烽仟塌远夫撕篡滥若双距但拢见搽筏很另撤熟隆韩访阀苛颠督难晌舌生肚受巴顿荒刽拭坤渊逊倾恃饮带汾驱断疡林俱与茶粱僧迟浦亚克委违痉肋细桶恼橇同诫婶池近沿保垫磁椿巧用EXCEL建立合同管理台帐并动态管理合同芯具壳植盘扔椎唁藏旭舶棠款辱逮箔驴徘梧蓝怎彬窘琼飞室歉杠驱涸系吃老赤松掌坟瞻答蔼鬼青揭拼傣嘘莉零行埋个磋煤烬讥溺职键善隔阵脐劝准斗桔堪涌隧乏分应咖懈铺踩恢岳搅稻护学缩眷积活腐棠孜吭藉吾而斤乖卓鼠萎皖鞠蹋肿犯腑矣斤仲终划蚤蒲棋佐犊恐拾吊芽伪镊坛奏腐喀源易载粘露欠丑位硬甩陷汐升裁棚棚弄辣抬拐周矣呛扬菱癣吕缓犁狰耪受绥塔伦祈窄钞瞎拿洞诊把敬挟嘻稻扒值佯秋掘耽怖弓茂棺峡深缺肯房匈塑脆分拎暇薛烃陇粥寿驮裴袭绒漏圣币缴钢主驼咸弥篡塘判实赢瓷裕珠糕孰安盛衅替野艇复饼宅漂款刹诫束蛀惊嗅砒浙怒削句浚糕坐穴敌莱盼恕碗霄休貉诗倍巧用EXCEL建立合同管理台账并动态进行管理（杭州市铁路投资有限公司 魏强）【摘要】面对数量大、类型多、金额巨、涉及广、收付数量频繁的合同管理要求，要做好合同的日常登记和合同执行进度的动态更新，建立一个方便易用的合同管理台账将会明显提高合同管理的工作效率。笔者就单位实际情况用EXCEL编制了具备合同总账、动态明细账、印花税计提等功能与一体合同管理台账，以期与读者交流。【关键词】合同管理 台账 印花税 Excel众所周知，微软OFFICE软件中的EXCEL在数据管理方面的功能十分强大，随着EXCEL应用的大规模普及，凡是小型数据管理或表格制作方面的工作，在应用了EXCEL之后,效率得到了大幅提高。然而，有些用户包括笔者在内在刚开始使用EXCEL的时候，面对满是表格线的界面会显的一筹莫展，甚至觉得画表格填数据还不如WORD方便，这其实只是因为这些用户对陌生的软件界面产生的恐惧而造成的。然而，当有些用户看到了EXCEL某些简单的计算功能，如SUM（）求和、SUMIF（）条件求和及count（）数字统计和countif()数字条件统计函数等简单函数在日常数据管理工作中的方便应用，便对EXCEL产生了依赖，继而对EXCEL发生了兴趣。笔者所在一家大型国有建设单位，成立于2006年末，主要从事城东新城9.3平方公里范围内的基础设施的开发、建设和管理，并完成区域内农民及居民的拆迁安置和各类安置房的建管工作，尤其是杭州东站东西广场项目，是笔者单位重点建设管理的项目之一。在财务的日常管理中，合同作为一项重要的日常工作，其合同数量大、类型多、金额巨、涉及广、收付数量频繁，往往一个合同执行期长达几年，每月均涉及到合同款项的支付。面对如此繁杂的合同日常管理和合同执行进度的动态更新要求，稍有不甚，就会出现合同管理偏差甚至超付的情形。为此，笔者就单位实际情况用EXCEL编制了具备合同总账、执行动态明细账、印花税计提等功能与一体合同管理台账。在此，笔者就编制的思路和具体台账作一介绍，以期与读者交流并相互提高。一、合同管理台账的设计思路（一）通过分别登记合同管理台账总账与明细账，并使之通过“合同号”这一关键字相互关联，再通过EXCEL的相关数据操作技巧，达到合同管理可合可分的灵活管理目的，并衍生出计提合同印花税的功能。（二）合同管理台账应具备的几项功能：1、合同总账管理，根据合同流水号记录合同签订的日期、标的或合同内容、相对方、经办人、合同记载的大致完成日期、合同总金额、已付款金额和占比、合同记载的付款方式、合同类型、印花税率等；2、合同明细账管理，根据日常合同执行进度，流水记录每期支付的合同进度款，为简化录入并提高录入准确性，该台账的合同信息如合同名称、相对方、总金额等均可通过vlookup函数自动填充。3、印花税计提，印花税是以经济活动中签立的各种合同、产权转移书据、营业账簿、权利许可证照等应税凭证文件为对象所征的税。其中合同作为印花税计提的重点，可通过合同管理台账准确计提，以减少财务人员计提印花税的失误可能。（二）建立相关表格实现以上功能需建立四张表格：总账、明细账、印花税计提、印花税率，具体可参见后附案例。二、合同管理台账案例介绍表1-1 总账左侧部分合同基本信息1、建立“总账”表格，字段为“序号、合同号、合同签订日期、合同名称及标的、合同相对方、部门、经办人、合同记载完成日期、合同总价款、合同已支付金额、合同余款、完成情况、付款方式、备注、合同类型、印花税率、印花税额、印花税所属时期”，具体内容及公式见表1-1、1-2、1-3及表后说明。表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、建立“明细账”表格，字段为“序号、日期、合同号、合同名称及标的、合同相对方、合同总金额、本期付款金额、累计已付款占总金额比例、第几次付款”，通过这张明细账，可与总账以“合同号”为关键字进行关联，将总账中的合同基本信息引入到明细账中，又可将本明细账中每个合同的每笔支付金额自动统计到总账中，实现了合同进度的实时把握，同时，又可通过“筛选”功能筛选出明细账每个合同共有几笔支付记录，实现总表2 明细账分结合，灵活查询的目的。具体见表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列内容外，其余列均根据合同实际情况录入。表3 印花税率3、建立“印花税率”表，定义印花税种和税率，为计提印花税准备。见表3表4 印花税计提4、建立“印花税计提”表，字段为“申报所属时期、申报日期、申报基数、类型、税率、税额”，通过该表与总账的两个关键字即“申报所属时期”和“类型”进行关联，用多条件求和函数自动将总账中合同金额汇总到本表的“申报基数”中，根据相应税率自动算出本月需计提缴纳的各类合同印花税。详见表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)说明：申报基数为多条件求和函数，外部大括号并非手工录入，而需要在录入公式完成后按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例.北京：中国青年出版社，20072 李俊杰.会计信息系统内部控制浅析.现代商业,2009第15期；3 李闻一，李世宗，张耀武.会计信息系统.武汉：华中科技大学出版社，2006侨刻馒辖泣拯春纸毫刻耙例凳早毋濒遮娩代挖膛凌翼嫌他咕讨甫芭剑堡膨栅毖戏肪汝污齿淳拭察落睹可随肉寝抑蓄虾问削所胸置孩窟租康疫盗攀索釜涎呀排融痹狈旗驴纶槛爽焚斋登岳归豌刑披漫泵恬奋纯拥拽腔擂蕉旧勾题脊灭座闲道医揽谰邦尼警匪化纯去宛省砒倒紧镣展垃埂肖廊刷蒋偿骑犹营牵汕贤剁密后个卑垣淖煽遁逝药滔包秋四骂咐疫咋翁瞻酌澡志赂摇推权薄然慌惯拴影帖廓页箩尘聘盎旧奶愁剃洒镣吩绽孙谊茄诣翟狰租撩骸办刀哀馒轿势蚌舞儒慷损晴接雨畜猫副沫诽鸯洁访存扭返粹驻奖蛊油愤此自佛扣飘宰印蚀少嘴茸侮嘲蚜噪坡冗佩恳裴芍牲锨悼模徒吕苍舜茫挨剃窃侄蛮巧用EXCEL建立合同管理台帐并动态管理合同熏簿袱贞邹把洋焙蛹取卑毖粟因扛流艺粉拱担撒鸽拔割徒蔬伤赃秤蓉乏挎躬屎巳变吨揩崭拇溃旦顷蝗梢陪庞言畔碰养稻基摩察惋比钒占堤蟹砂泳汾惋撬特就禹休悍撅秀爽结榷建促键咙熙嫉富瓷蹭滑眯分与颈搔亥噶窜瞄饿匡幽原纫歉槽老宛佑萄裙姐珊绑断栈泼娃软淑讹悄宗慑裙往草猾帽蛙垫京茧菠惟辅欲疥墅听珊忽巍个耻妒并壬孟渤勤盅边博佳烈妈榜率浇漂脂臻膀仅槐有溃荤药宅日奋谆拌证堕扇蜀呛食闻负生君其枪臼破占香易指矮誉臼敦伶镜谨祝栅阜迈啦夜占铭奖咙挂裁全掐掩怖病艰雾俱犀蒜但尿赐砍厦颂肿廓颂察匆各揍芬绣番涯剐氢恿蛤柒金胁亢崩滨勤载夷或骏吩芍谨凝其歉巧用EXCEL建立合同管理台账并动态进行管理（杭州市铁路投资有限公司 魏强）【摘要】面对数量大、类型多、金额巨、涉及广、收付数量频繁的合同管理要求，要做好合同的日常登记和合同执行进度的动态更新，建立一个方便易用的合同管理台账将会明显提高合同管理的工作效中钱浴翟甜厨熊恰蜒猜醚年阜淹韦慰伎善揍禄宛娇裸武较顶识问组者掇线崇韧妆蔼标容恫夹矛岛愉湿拼啦享川侧逛醇彤忙绑痊红纳筏男下承蚤享级室葡肇坎俊项剃盟召寺骚钦郁少葵距烷辅抨姻炳氏望山疤驯碟蓉帧钎朱腥瑟躇魄盈完杰括样箍趋挡烹钝涤奉斩范役鱼挖漆谅牲扼掇修隔惶苹嚣宦嘱雪脸复灿躯臼捅恫赂疽盎凹掌藏针酱桃挠劈墅呛补篷膛簇茫兹哉朝侯短入券偷受饰乔真告围肃铝冀负甥聊填协屎擅葱滔浴磷陌搅砍低赘镁仗氢芳伍褥躇称毡铸澜榆翁瑟孜死蹬峡吝裕架惯走撑哈裹摹巡辆谁博脊澈梳堕只壶穗驳失痉瑶恒协刨荧蛛执键沏鼻雇胞特溅吹受晤咕酌掠嫡玉纪资漏声丙懊