巧用EXCEL建立合同管理臺帳并動態(tài)管理合同

1、巧用EXCEL建立合同管理臺賬并動態(tài)進行管理(杭州市鐵路投資有限公司魏強)【摘要】面對數量大、類型多、金額巨、涉及廣、收付數量頻繁的合同管理要求，要做好合同的日常登記和合同執(zhí)行進度的動態(tài)更新，建立一個方便易用的合同管理臺賬將會明顯提高合同管理的工作效率。筆者就單位實際情況用EXCEL編制了具備合同總賬、動態(tài)明細賬、印花稅計提等功能與一體合同管理臺賬，以期與讀者交流?！娟P鍵詞】合同管理臺賬印花稅Excel眾所周知，微軟0FFICE軟件中的EXCEL在數據管理方面的功能十分強大，隨著EXCEL應用的大規(guī)模普及，凡是小型數據管理或表格制作方面的工作，在應用了EXCEL之后，效率得到了大幅提高。然而，

2、有些用戶包括筆者在內在剛開始使用EXCEL的時候，面對滿是表格線的界面會顯的一籌莫展，甚至覺得畫表格填數據還不如WORD方便，這其實只是因為這些用戶對陌生的軟件界面產生的恐懼而造成的。然而，當有些用戶看到了EXCEL某些簡單的計算功能，如SUM()求和、SUMIF()條件求和及count()數字統(tǒng)計和countif()數字條件統(tǒng)計函數等簡單函數在日常數據管理工作中的方便應用，便對EXCEL產生了依賴，繼而對EXCEL發(fā)生了興趣。筆者所在一家大型國有建設單位，成立于2006年末，主要從事城東新城9.3平方公里范圍內的基礎設施的開發(fā)、建設和管理，并完成區(qū)域內農民及居民的拆遷安置和各類安置房的建管工

3、作，尤其是杭州東站東西廣場項目，是筆者單位重點建設管理的項目之一。在財務的日常管理中，合同作為一項重要的日常工作，其合同數量大、類型多、金額巨、涉及廣、收付數量頻繁，往往一個合同執(zhí)行期長達幾年，每月均涉及到合同款項的支付。面對如此繁雜的合同日常管理和合同執(zhí)行進度的動態(tài)更新要求，稍有不甚，就會出現(xiàn)合同管理偏差甚至超付的情形。為此，筆者就單位實際情況用EXCEL編制了具備合同總賬、執(zhí)行動態(tài)明細賬、印花稅計提等功能與一體合同管理臺賬。在此，筆者就編制的思路和具體臺賬作一介紹，以期與讀者交流并相互提高。一、合同管理臺賬的設計思路(一)通過分別登記合同管理臺賬總賬與明細賬，并使之通過“合同號”這一關鍵字

4、相互關聯(lián)，再通過EXCEL的相關數據操作技巧，達到合同管理可合可分的靈活管理目的，并衍生出計提合同印花稅的功能。（二）合同管理臺賬應具備的幾項功能：1、合同總賬管理，根據合同流水號記錄合同簽訂的日期、標的或合同內容、相對方、經辦人、合同記載的大致完成日期、合同總金額、已付款金額和占比、合同記載的付款方式、合同類型、印花稅率等；2、合同明細賬管理，根據日常合同執(zhí)行進度，流水記錄每期支付的合同進度款，為簡化錄入并提高錄入準確性，該臺賬的合同信息如合同名稱、相對方、總金額等均可通過vlookup函數自動填充。3、印花稅計提，印花稅是以經濟活動中簽立的各種合同、產權轉移書據、營業(yè)賬簿、權利許可證照等應

5、稅憑證文件為對象所征的稅。其中合同作為印花稅計提的重點，可通過合同管理臺賬準確計提，以減少財務人員計提印花稅的失誤可能。（二）建立相關表格實現(xiàn)以上功能需建立四張表格：總賬、明細賬、印花稅計提、印花稅率，具體可參見后附案例。二、合同管理臺賬案例介紹1、建立“總賬”表格，字段為“序號、合同號、合同簽訂日期、合同名稱及標的、合同相對方、部門、經辦人、合同記載完成日期、合同總價款、合同已支付金額、合同余款、完成情況、付款方式、備注、合同類型、印花稅率、印花稅額、印花稅所屬時期”，具體內容及公式見表1-1、1-2、1-3及表后說明。表1-1總賬一左側部分一合同基本信息ABCDEfaIJ音同號I煉”百網呂

6、沖?t斤的港-愛同id牯冗旺IIJS”介同總g”節(jié)同Ea合計1睥DDD4ODI13.A30rOiDOi312D1D-0012009-12-31冃工瑁二匚.譏|、:|妄-:I:2D1D-12O0jooaaaoa20D.CCO001?D1Q-0022010-1-B-_-n浙江乙力也家耳刖:汴肚=井北寶刪理2D10-3-15GODaOQO3Da8DOODO?D1M032010-2-1:柄親遊=匚冃施THJ心|:-tz-/r主：2012-1-3iDiooiaaaoa13,0000.11161?D1Q-OO42D1CFZ-H,：；i.-rZ-3L-Otie：on杭州市丁工程躺設汁靖_ri：吳王任2D12

7、-1-13zaQoosaao33Q8DOODO除以上5列內容外，其余列均根據合同實際情況錄入。1hCDETK匚n片沿同號丨圈僉同芒會及樸的音同和對方I我忖嘰脩兀答汁20is.moooBJ70.00013.4331201MO1200H2-315l：I-H-杭州市甲拾揮郁200.000GO200.00000SQ.m?.42Z01CU0OZ2010-1-G姥塞具購銷合同浙衽乙妞冢耳制造有帳咅司aoaooo.aogoaoaooo50.CCHt53201MU32010-2-1浙江蛍丙施工息孕13.000,000.00B78OuD,UDO0013.MSflq201MOi2D1Q-2-14市丁勘寫咬計-：3

8、3D.0DQ.Q01.670.000.0016.Mt-表1-2總賬一中間部分一合同已支付信息及完成情況等表1-3總賬一右側部分一印花稅信息hBcDENQPQKL序號合同號合同筈訂門期甘同巻稱及標的甘同相対巧備注卩化稅峑時期”2LI31.5B0.00312010012009-12-31杭州市羽指揮漲租監(jiān)0.001100.00201001422a10-0022D1D-I-B井公家貝購司斷I乙松家具制適有限拉司n;jirDDDD31BD.DD2D1DD1532aiD-ao32010-2-1城東新城甲項目施工頜-皿建安D.00033D.ODO.OO20100264201D-0D2D1D-2-14域東新

9、域甲項目勘粛制司刪市丁工程躺設計金司D.0D05LOOO.OO201002總賬用到的公式（均以每表中的第一行數據為例進行說明）合同已支付金額=SUMIF（明細賬!C:C,=&總賬!B3,明細賬!G:G）=I3-J3合同余款完成情況印花稅率=IF（AND（I3=J3,J30）,完成，IF（J3=0,，IF（I30,J3/I3,）=VL00KUP(03,印花稅率!A:AI,2,FALSE)印花稅額=R0UND(I3*P3,2)除以上5列內容外，其余列均根據合同實際情況錄入。2、建立“明細賬”表格，字段為“序號、日期、合同號、合同名稱及標的、合同相對方、合同總金額、本期付款金額、累計已付款占總金額比

10、例、第幾次付款”，通過這張明細表2明細賬ABCDEFGHI1序號日期合同號合同名稱及標的合同相對方合同總金額本期付款金額累計已付款占總金額比例第幾次付款20合計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城東新城甲項目勘察合同杭州市丁工程勘察設計公司2,000,000.00100,000.00

11、9.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.00第1次972010-3-102010-001房屋租賃合同杭州市甲指揮部400,000.00100,000.0050.00%第2次1082010-3-112010-003城東新城甲項目施工合同浙江省丙施工

12、總承包單位100,000,000.008,000,000.0013.00%第2次賬，可與總賬以“合同號”為關鍵字進行關聯(lián)，將總賬中的合同基本信息引入到明細賬中,又可將本明細賬中每個合同的每筆支付金額自動統(tǒng)計到總賬中，實現(xiàn)了合同進度的實時把握，同時，又可通過“篩選”功能篩選出明細賬每個合同共有幾筆支付記錄，實現(xiàn)總分結合，靈活查詢的目的。具體見表2及表后說明。明細賬用到的公式（均以每表中的第一行數據為例進行說明）合同名稱及標的二VL00KUP（C3,總賬！B:AN,3,FALSE）合同相對方二VL00KUP（C3,總賬!B:AN,4,FALSE）合同總金額=VL00KUP（C3,總賬！B:AN,&

13、FALSE）累計已付款占總金額比例=IF（F3O，Z,SUMIF（$C$3:C3,二&C3,$G$3:G3）/F3,）第幾次付款二IF（G3,第&COUNTIF（$C$1:C3,二&C3）&次，）3、建立“印花稅率”表，定義印花稅種和稅率，為計提印花稅準備。見表3表3印花稅率ABC1合同類型稅率說明2倍款0.OD0O5-昔蔥.合同笫融業(yè)換借安拿輸joraO.OSol十萬分藝五）3購銷0.OOD3鳩合同100%0.3%o（力片二I4技術0.OOD3妓術合冋1DD%U.冊萬廿/二I50.OOD3建埼安裝丄稈重赳臺冋1LU/oUJbc（.萬廿乞二J6勘票0.OOD5建萌工程勘察恢匸盲冋門昭.魂0（萬

14、分之五）7重攬U.LUJt加工承攬合同100%0.5%o（萬分芝五）8運輸0.COF境物運輸合同100%05cw（萬芳芝五）9帳簿0.0兀營業(yè)資金帳簿100Cl斑萬分藝五）10保險0.01財產保險臺司1001ViT分芝）11倉儲0.001倉儲保管合同100JC1%i千分芝一）12租賃0.001時產租賃合司100%1臉i千分之一）4、建立“印花稅計提”表，字段為“申報所屬時期、申報日期、申報基數、類型、稅率、稅額”，通過該表與總賬的兩個關鍵字即“申報所屬時期”和“類型”進行關聯(lián)，用多條件求和函數自動將總賬中合同金額匯總到本表的“申報基數”中，根據相應稅率自動算出本月需計提繳納的各類合同印花稅。詳

15、見表4。表4印花稅計提ABCDEF1申報所屬時期申報日期印花稅自動計算數2申報基叛類型稅率3合計23000,0M.0031,580.0042010012010-2-5400;000.00租賃0.00100400.0052010012010-5-10600,000.00購將0.00030180.0062010022010-S-10100,000,000.000.0003030,000.0072010022010-5-102,000,000.00勘察0.00050tooo.ao印花稅計提用到的公式=SUM(總賬!$R$3:$R$10000=$A4)*(總賬!$0$3:$0$10000=$D4)*總

16、申報基數稅率稅額=R0UND(C4*E4,2)=VL00KUP(D4,印花稅率!A:AI,2,FALSE)說明：申報基數為多條件求和函數，外部大括號并非手工錄入，而需要在錄入公式完成后按ctrl+shift+enter組合鍵完成，該函數應用得當將在財務工作許多方面提高工作效率。三、合同管理臺賬中的幾個重要函數介紹。1、vlookup()函數,具體用法及參數說明如下：語法：VLOOKUP(關鍵字，被查找的數據范圍需關鍵字所在列起頭，返回數據范圍匹配值的列數，false或true精確匹配或近似匹配)。示例如下，在明細賬的D3單元格填寫：=VL00KUP(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:$0$10000=$D4)*總賬!$1$3:$1$10000)這個函數在印花稅計提表中申報基數單元格中，其中：總賬!$R$3:$R$10000=$A4代表“總賬”與“印花稅計提”的“印花稅所屬時期”相匹配；總賬!$0$3:$0$10000=$D4代表“總