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眾所周知，微軟OFFICE軟件中的EXCEL在數(shù)據(jù)管理方面的功能十分強(qiáng)大，隨著EXCEL 應(yīng)用的大規(guī)模普及，但凡小型數(shù)據(jù)管理或表格制作方面的工作，在應(yīng)用了 EXCEL之后，效率 得到了大幅提高。然而

2、，有些用戶包括筆者在內(nèi)在剛開場(chǎng)使用EXCEL的時(shí)候，面對(duì)滿是表格線的界面會(huì) 顯的一籌莫展，甚至覺得畫表格填數(shù)據(jù)還不如WORD方便，這其實(shí)只是因?yàn)檫@些用戶對(duì)陌生 的軟件界面產(chǎn)生的恐懼而造成的。然而，當(dāng)有些用戶看到了 EXCEL某些簡(jiǎn)單的計(jì)算功能， 如SUM）求和、SUM IF）條件求和及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ó)有建立單位，成立于2006年末，主要從事城東新城9. 3平方公里 范圍內(nèi)的根底設(shè)施的開發(fā)、建立和管理，并完成區(qū)域內(nèi)農(nóng)民及居民的拆遷安置和各

3、類安置 房的建管工作，尤其是杭州東站東西廣場(chǎng)工程，是筆者單位重點(diǎn)建立管理的工程之一。在 財(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ì)思路一）通過分別登記合同管理臺(tái)賬總賬與明細(xì)賬，并使

4、之通過“合同號(hào)這一關(guān)鍵字 相互關(guān)聯(lián)，再通過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ì)方、總金額等均可通過 vlookup函數(shù)自動(dòng)填充。3、印花稅計(jì)提，印花稅是以經(jīng)濟(jì)活動(dòng)中簽立的各種合同、產(chǎn)權(quán)轉(zhuǎn)

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

6、支付信息及完成情況等ABCDEJKLMN1序號(hào)合同號(hào)合同簽訂 日期合同名稱及標(biāo)的合同相對(duì)方合同 已支付金番合同 余款 成況 完情付款方5£備注-J_30合計(jì)13,830,00089,170,00013.43%12010-0012009-12-31房屋租賃合同杭州市甲指揮部200,000.00200,000.0050.00%422010-0022010-1-8亦公家具購(gòu)銷合同浙江乙亦公家具制 造有限公司300,000.00300,000.0050.00%532010-0032010-2-1城東新城甲項(xiàng)目施工合同浙江省丙施工總承 宜革位13,000,000.0087,000,000.00

7、13.00%6042010-0042010-2-14城東新城甲項(xiàng)目勘察合辰0杭州市丁工程勘彳 設(shè)計(jì)公司330,()00.001,670,000.0016. 50%422010-0022010-1-8水公家具購(gòu)銷合同浙江乙亦公家具制 造有限公司亦公室張助理2010-3-15600,000.00300,000.00532010-0032010-2-1城東新城甲項(xiàng)目施工合同浙江省丙施工總承 包單位工程部楊主任2012-1-3100,000,000.0013,000,000.00642010-0042010-2-14城東新城甲項(xiàng)目勘察合同杭州市丁工程勘察 設(shè)計(jì)公司工程部吳主任2012-1-152,00

8、0,000.00330,000.00ABCDEN0PQR1序號(hào)合同號(hào)合同簽訂 日期合同名稱及標(biāo)的合同相對(duì)方備注S印花 稅率花簇 印稅印花稅 所屬 時(shí)期20合計(jì)31,580.00312010-0012009-12-31房屋租賃合同杭州市甲指揮部租賃0.001400.00201001422010-0022010-1-8亦公家具購(gòu)銷合同浙江乙亦公家具制 造有限公司購(gòu)銷0.0003180.00201001532010-0032010-2-1城東新城甲項(xiàng)目施工合曰浙江省丙施工總承 包單位建安0.000330,000.00201002642010-0042010-2-14城東新城甲頊目勘察合曰杭州市丁工程

9、勘察 設(shè)計(jì)公司勘察0.00051,000.00201002表13表13總賬一右側(cè)局部一印花稅信息總賬用到的公式均以每表中的第一行數(shù)據(jù)為例進(jìn)展說明）合同已支付金額=SUMIF(明細(xì)賬!C:C, =&總賬!B3,明細(xì)賬!G:G)合同余款=I3-J3完成情況=IF(AND(I3=J3, J3O0),/z完成,IF(J3<=0, ，IF(I3>0, J3/I3, )印花稅率=VL00KUP(03,印花稅率!A:AI, 2, FALSE)印花稅額二 R0UND(I3*P3,2)除以上5列內(nèi)容外，其余列均根據(jù)合同實(shí)際情況錄入。2、建立“明細(xì)賬表格，字段為“序號(hào)、日期、合同號(hào)、合同名稱及

10、標(biāo)的、合同相對(duì)方、合同總金額、本期付款金額、累計(jì)己付款占總金額比例、第幾次付款，通過這張明表2明細(xì)賬ABCDEFGHI1序號(hào)日期合同號(hào)合同名稱及標(biāo)的合同相對(duì)方合同總金額本期付款金額累計(jì)己付 款占總金 額比例第幾次付款I(lǐng)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)

11、目勘察合同杭州市丁工程勘察設(shè)計(jì)公司2, 000, 000. 00100,000.009.00%第2次642010-6-62010-004城東新城甲項(xiàng)目勘察合同杭州市丁工程勘察設(shè)計(jì)公司2, 000, 000. 00150,000.0016.50%第3次752010-4-62010-002辦公家具購(gòu)銷合同浙江乙辦公家具制造有限公 司600, 000. 00300,000.0050.00%第1次862011-1-52010-001房屋租賃合同杭州市甲指揮部400, 000. 00100,000.0025.00%第1次912010-3-102010-001房屋租賃合同杭州市甲指揮部400, 000.0

12、0100,000.0050.00%第2次1082010-3-112010-003城東新城甲項(xiàng)目施工合同浙江省丙施工總承包單位100, 000, 000. 008,000,000.0013.00%第2次細(xì)賬，可與總賬以“合同號(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í)，又可通過“篩選功能篩選出明細(xì)賬每個(gè)合同共有幾筆支付記錄，實(shí)現(xiàn)總 分結(jié)合，靈活查詢的目的。具體見表2及表后說明。明細(xì)賬用到的公式均以每表中的第一行數(shù)據(jù)為例進(jìn)展說明）合同名稱及標(biāo)的=VL00KUP(C3,總賬！B:AN, 3, FA

13、LSE)合同相對(duì)方二VL00KUP(C3,總賬!B:AN, 4, FALSE)合同總金額二VLOOKUP(C3,總賬!B:AN, 8, FALSE)累計(jì)已付款占總金額比例二IF(F3<>，SUMIF($C$3:C3, 二&C3, $G$3:G3)/F3, )第幾次付款二IF(G3>，第&COUNTIF($C$1:C3, =&C3)&"次，)除以上5列內(nèi)容外，其余列均根據(jù)合同實(shí)際情況錄入。3、建立“印花稅率表，定義印花稅種和稅率，為計(jì)提印花稅準(zhǔn)備。見表3印花稅率ABC1合同類型稅率說明2借款0. 00005借款合同（金融業(yè)按借貸金額）1

14、00%0.05%0（十萬分之五）3購(gòu)銷0. 0003購(gòu)銷合同1。0.3%o（萬分之三）4技術(shù)0. 0003技術(shù)合同10。0.3%o（萬分之三）5建安0. 0003建筑安裝工程承包合同10.3%o（萬分之三）6勘察0. 0005建筑工程勘察設(shè)計(jì)合同100% 0.5%o（萬分之五）7承攬0. 0005加工承攬合同100%0.5%o（萬分之五）8表4Liu運(yùn)輸1心收0. 0005V. VX貨物運(yùn)輸合同100%0.5%o（萬分之五）_印花稅計(jì)提盾”I LJ2。/u 1 1 人一 /ABCDEF1申報(bào)所屬 時(shí)期申報(bào)日期印花稅自動(dòng)計(jì)算數(shù)2申報(bào)基數(shù)類型稅率稅額3合計(jì)103, 000, 000. 0031,

15、580.0042010012010-2-5400,000.00租賃0.00100400.0052010012010-5-10600,000.00購(gòu)銷0.00030180.0062010022010-5-10100,000,000.00建安0.0003030,000.0072010022010-5-102?000,000.00勘察0.000501,000.004、建立“印花稅計(jì)提表，字段為“申報(bào)所屬時(shí)期、申報(bào)日期、申報(bào)基數(shù)、類型、稅 率、稅額”，通過該表與總賬的兩個(gè)關(guān)鍵字即“申報(bào)所屬時(shí)期“和“類型”進(jìn)展關(guān)聯(lián)，用 多條件求和函數(shù)自動(dòng)將總賬中合同金額匯總到本表的“申報(bào)基數(shù)中，根據(jù)相應(yīng)稅率自動(dòng) 算出本

16、月需計(jì)提繳納的各類合同印花稅。詳見表4。印花稅計(jì)提用到的公式說明：申報(bào)基數(shù)為多條件求和函數(shù)，外部大括號(hào)并非手工錄入，而需要在錄入公式完成后按申報(bào)基數(shù)= SUM (總賬! $R$3: $R$10000=$A4) * (總賬! $0$3: $0$10000=$D4) *總賬!$1$3:$1$10000)稅率二VL00KUP(1)4,印花稅率!A:AI, 2, FALSE)稅額二ROUND(C4*E4, 2)ctrl+shift+enter組合鍵完成，該函數(shù)應(yīng)用得當(dāng)將在財(cái)務(wù)工作許多方面提高工作效率。三、合同管理臺(tái)賬中的幾個(gè)重要函數(shù)介紹。1> vlookup()函數(shù),具體用法及參數(shù)說明如下：語法

17、：VLOOKUP(關(guān)鍵字，被查找的數(shù)據(jù)范圍需關(guān)鍵字所在列起頭，返回?cái)?shù)據(jù)范圍匹 配值的列數(shù)，false或true準(zhǔn)確匹配或近似匹配)。例如如下，在明細(xì)賬的D3單元格填寫:=VLOOKUP(C3,總賬!B:AN, 3, FALSE) o式中:“ C3 代表以明細(xì)賬中C3合同編號(hào)關(guān)鍵字，“總賬!B:AN代表被搜索范圍為總賬中第B列到AN 列，其中B列填寫為合同號(hào)，“ 3那么代表返回B至AN列中的第3列單元格即“合同名 稱及標(biāo)的的結(jié)果，false代表準(zhǔn)確匹配。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代表 “總賬