巧用EXCEL建立合同管理台帐并动态管理合同文档格式.docx
《巧用EXCEL建立合同管理台帐并动态管理合同文档格式.docx》由会员分享，可在线阅读，更多相关《巧用EXCEL建立合同管理台帐并动态管理合同文档格式.docx（5页珍藏版）》请在冰豆网上搜索。
在此，笔者就编制的思路和具体台账作一介绍，以期与读者交流并相互提高。
一、合同管理台账的设计思路
（一）通过分别登记合同管理台账总账与明细账，并使之通过“合同号”这一关键字相互关联，再通过EXCEL的相关数据操作技巧，达到合同管理可合可分的灵活管理目的，并衍生出计提合同印花税的功能。
（二）合同管理台账应具备的几项功能：
1、合同总账管理，根据合同流水号记录合同签订的日期、标的或合同内容、相对方、经办人、合同记载的大致完成日期、合同总金额、已付款金额和占比、合同记载的付款方式、合同类型、印花税率等；
2、合同明细账管理，根据日常合同执行进度，流水记录每期支付的合同进度款，为简化录入并提高录入准确性，该台账的合同信息如合同名称、相对方、总金额等均可通过vlookup函数自动填充。
3、印花税计提，印花税是以经济活动中签立的各种合同、产权转移书据、营业账簿、权利许可证照等应税凭证文件为对象所征的税。
其中合同作为印花税计提的重点，可通过合同管理台账准确计提，以减少财务人员计提印花税的失误可能。
（二）建立相关表格
实现以上功能需建立四张表格：
总账、明细账、印花税计提、印花税率，具体可参见后附案例。
二、合同管理台账案例介绍
1、建立“总账”表格，字段为“序号、合同号、合同签订日期、合同名称及标的、合同相对方、部门、经办人、合同记载完成日期、合同总价款、合同已支付金额、合同余款、完成情况、付款方式、备注、合同类型、印花税率、印花税额、印花税所属时期”，具体内容及公式见表1-1、1-2、1-3及表后说明。
表1-3总账—右侧部分—印花税信息
总账用到的公式（均以每表中的第一行数据为例进行说明）
合同已支付金额
=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）
除以上5列内容外，其余列均根据合同实际情况录入。
2、建立“明细账”表格，字段为“序号、日期、合同号、合同名称及标的、合同相对方、合同总金额、本期付款金额、累计已付款占总金额比例、第几次付款”，通过这张明细账，可与总账以“合同号”为关键字进行关联，将总账中的合同基本信息引入到明细账中，又可将本明细账中每个合同的每笔支付金额自动统计到总账中，实现了合同进度的实时把握，同时，又可通过“筛选”功能筛选出明细账每个合同共有几笔支付记录，实现总分结合，灵活查询的目的。
具体见表2及表后说明。
明细账用到的公式（均以每表中的第一行数据为例进行说明）
合同名称及标的
=VLOOKUP（C3,总账!
B:
AN,3,FALSE）
合同相对方
AN,4,FALSE）
合同总金额
AN,8,FALSE）
累计已付款占总金额比例
=IF（F3<
SUMIF（$C$3:
C3,"
C3,$G$3:
G3）/F3,"
）
第几次付款
=IF（G3<
"
第"
COUNTIF（$C$1:
C3）&
次"
3、建立“印花税率”表，定义印花税种和税率，为计提印花税准备。
见表3
表4印花税计提
4、建立“印花税计提”表，字段为“申报所属时期、申报日期、申报基数、类型、税率、税额”，通过该表与总账的两个关键字即“申报所属时期”和“类型”进行关联，用多条件求和函数自动将总账中合同金额汇总到本表的“申报基数”中，根据相应税率自动算出本月需计提缴纳的各类合同印花税。
详见表4。
印花税计提用到的公式
申报基数
{=SUM（（总账!
$R$3:
$R$10000=$A4）*（总账!
$O$3:
$O$10000=$D4）*总账!
$I$3:
$I$10000）}
税率
=VLOOKUP（D4,印花税率!
税额
=ROUND（C4*E4,2）
说明：
申报基数为多条件求和函数，外部大括号并非手工录入，而需要在录入公式完成后按ctrl+shift+enter组合键完成，该函数应用得当将在财务工作许多方面提高工作效率。
三、合同管理台账中的几个重要函数介绍。
1、vlookup（）函数,具体用法及参数说明如下：
语法：
VLOOKUP（关键字，被查找的数据范围<
需关键字所在列起头>
，返回数据范围匹配值的列数，false或true<
精确匹配或近似匹配>
）。
示例如下，在明细账的D3单元格填写：
AN,3,FALSE）。
式中：
“C3”代表以明细账中C3合同编号关键字，“总账!
AN”代表被搜索范围为总账中第B列到AN列，其中B列填写为合同号，“3”则代表返回B至AN列中的第3列单元格即“合同名称及标的”的结果，false代表精确匹配。
2、多条件求和函数{=SUM（（总账!
$I$10000）}
这个函数在印花税计提表中申报基数单元格中，其中：
$R$10000=$A4代表“总账”与“印花税计提”的“印花税所属时期”相匹配；
$O$10000=$D4代表“总账”与“印花税计提”的“合同类型”相匹配；
$I$10000代表自动求和满足前两个匹配条件的S列，即“合同总金额”。
值得注意的是，在多条件求和函数公式录入完成后，直接回车将不会显示正确结果，必须在公式录入后按ctrl+shift+enter组合键，这时，该多条件求和函数将会自动被外部大括号括起来。
参考文献：
[1]恒盛杰资讯.Excel会计与财务管理经典108例.北京：
中国青年出版社，2007
[2]李俊杰.会计信息系统内部控制浅析.现代商业,2009第15期；
[3]李闻一，李世宗，张耀武.会计信息系统.武汉：
华中科技大学出版社，2006
升级会员 