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

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

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

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

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

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

6、沖?t斤的港-愛(ài)同id牯冗旺IIJS”介同總g”節(jié)同Ea合計(jì)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杭州市丁工程躺設(shè)汁靖_ri：吳王任2D12

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

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

9、域甲項(xiàng)目勘粛制司刪市丁工程躺設(shè)計(jì)金司D.0D05LOOO.OO201002總賬用到的公式（均以每表中的第一行數(shù)據(jù)為例進(jìn)行說(shuō)明）合同已支付金額=SUMIF（明細(xì)賬!C:C,=&總賬!B3,明細(xì)賬!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列內(nèi)容外，其余列均根據(jù)合同實(shí)際情況錄入。2、建立“明細(xì)賬”表格，字段為“序號(hào)、日期、合同號(hào)、合同名稱及標(biāo)的、合同相對(duì)方、合同總金額、本期付款金額、累計(jì)已付款占總金額比

10、例、第幾次付款”，通過(guò)這張明細(xì)表2明細(xì)賬ABCDEFGHI1序號(hào)日期合同號(hào)合同名稱及標(biāo)的合同相對(duì)方合同總金額本期付款金額累計(jì)已付款占總金額比例第幾次付款20合計(jì)13,830,000.00312010-2-72010-004城東新城甲項(xiàng)目勘察合同杭州市丁工程勘察設(shè)計(jì)公司2,000,000.0080,000.004.00%第1次422010-6-62010-003城東新城甲項(xiàng)目施工合同浙江省丙施工總承包單位100,000,000.005,000,000.005.00%第1次532010-6-62010-004城東新城甲項(xiàng)目勘察合同杭州市丁工程勘察設(shè)計(jì)公司2,000,000.00100,000.00

11、9.00%第2次642010-6-62010-004城東新城甲項(xiàng)目勒察合同杭州市丁工程勘察設(shè)計(jì)公司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城東新城甲項(xiàng)目施工合同浙江省丙施工

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

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

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

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

16、申報(bào)基數(shù)稅率稅額=R0UND(C4*E4,2)=VL00KUP(D4,印花稅率!A:AI,2,FALSE)說(shuō)明：申報(bào)基數(shù)為多條件求和函數(shù)，外部大括號(hào)并非手工錄入，而需要在錄入公式完成后按ctrl+shift+enter組合鍵完成，該函數(shù)應(yīng)用得當(dāng)將在財(cái)務(wù)工作許多方面提高工作效率。三、合同管理臺(tái)賬中的幾個(gè)重要函數(shù)介紹。1、vlookup()函數(shù),具體用法及參數(shù)說(shuō)明如下：語(yǔ)法：VLOOKUP(關(guān)鍵字，被查找的數(shù)據(jù)范圍需關(guān)鍵字所在列起頭，返回?cái)?shù)據(jù)范圍匹配值的列數(shù)，false或true精確匹配或近似匹配)。示例如下，在明細(xì)賬的D3單元格填寫：=VL00KUP(C3,總賬!B:AN,3,FALSE)。式中：“C3”代表以明細(xì)賬中C3合同編號(hào)關(guān)鍵字，“總賬!B:AN”代表被搜索范圍為總賬中第B列到AN列，其中B列填寫為合同號(hào)，“3”則代表返回B至AN列中的第3列單元格即“合同名稱及標(biāo)的”的結(jié)果，false代表精確匹配。2、多條件求和函數(shù)=SUM(總賬!$R$3:$R$10000=$A4)*(總賬!$0$3:$0$10000=$D4)*總賬!$1$3:$1$10000)這個(gè)函數(shù)在印花稅計(jì)提表中申報(bào)基數(shù)單元格中，其中：總賬!$R$3:$R$10000=$A4代表“總賬”與“印花稅計(jì)提”的“印花稅所屬時(shí)期”相匹配；總賬!$0$3:$0$10000=$D4代表“總