摘要：核算企業(yè)物料成本是企業(yè)財(cái)務(wù)管理中一項(xiàng)重要工作。該文試圖通過EXCEL函數(shù)來自動(dòng)完成企業(yè)物料發(fā)出成本和結(jié)存成本的核算，給出了加權(quán)平均法在EXCEL表格中計(jì)算物料發(fā)出和結(jié)存成本的設(shè)計(jì)思路和具體步驟。
中國(guó)論文網(wǎng) http://www.xzbu.com/8/view-1604676.htm
關(guān)鍵詞：加權(quán)平均法；物料成本；EXCEL函數(shù)
中圖分類號(hào)：TP393文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)：1009-3044(2012)03-0604-04
Using the EXCEL Function Weighted Average Method to Calculate Material CostSunshine
GENG Yong
(International Algeria Limited Company,Algiers 999103, Algeria)
Abstract: In a company calculating inventories’cost is the most important part of finance management. This essay aims at calculating the cost of inventories’outputting and inventories’in stock by EXCEL functions automatically, It explains the designing concept and concrete procedures of the method of weighted average in EXCEL sheet.
Key words: weighted average method; material cost; EXCEL function
庫(kù)存原材料是企業(yè)流動(dòng)性比較強(qiáng)的資產(chǎn)，為了正確核算材料成本，必須加強(qiáng)庫(kù)存材料進(jìn)銷（耗）存的管理，很多公司為了加強(qiáng)原材料的管理購(gòu)買了相關(guān)軟件來完成這一工作，但軟件的應(yīng)用都會(huì)產(chǎn)生較大的成本，相比利用專用軟件進(jìn)行物料管理而言，運(yùn)用EX? CEL表格中強(qiáng)大的函數(shù)功能來實(shí)現(xiàn)庫(kù)存材料進(jìn)銷（耗）存的管理，也不需要在EXCEL表中去編制一般使用者所不熟悉的比較復(fù)雜的EXCEL宏去實(shí)現(xiàn)計(jì)算，則要經(jīng)濟(jì)實(shí)惠得多，對(duì)諸多中小企業(yè)來說尤其如此。在這里我就加權(quán)平均法核算物料成本在EXCEL里的應(yīng)用作下詳細(xì)的介紹，希望對(duì)廣大數(shù)據(jù)處理人士有所裨益。
1加權(quán)平均法應(yīng)用的大體思路
1.1加權(quán)平均法與物料編碼的基本要求
在介紹利用EXCEL函數(shù)進(jìn)行加權(quán)平均法計(jì)算物料成本前我們首先來簡(jiǎn)略介紹下加權(quán)平均法和物料編碼的基本要求。
加權(quán)平均法也叫全月一次加權(quán)平均法，指以本月收入全部物料數(shù)量加月初物料數(shù)量作為權(quán)數(shù)，去除本月收入全部物料成本加月初物料成本的和，計(jì)算出物料的加權(quán)平均單位成本，從而確定物料的發(fā)出成本和庫(kù)存成本的方法。計(jì)算公式如下：
加權(quán)平均單價(jià)=（本月收入每種物料金額+月初每種物料金額）/（本月收入每種物料數(shù)量+月初每種物料數(shù)量）本月發(fā)出物料成本=本月發(fā)出物料數(shù)量*加權(quán)平均單價(jià)月末結(jié)存物料成本=月末庫(kù)存物料數(shù)量*加權(quán)平均單價(jià)
為了在EXCEL中利用函數(shù)實(shí)現(xiàn)自動(dòng)化計(jì)算物料成本，我必須將每一種具體的物料進(jìn)行編碼化，用編碼來對(duì)應(yīng)每一種物料，物料編碼的條件必須遵循以下三大原則：唯一性、分類別、長(zhǎng)度統(tǒng)一
1)唯一性：指在通常情況下如果物料的物理形態(tài)和化學(xué)形態(tài)不發(fā)生改變同一種物料自始至終使用統(tǒng)一代碼，如果發(fā)生了改變就必須編制不同的物料編碼。
2)分類別：在編碼時(shí)一般會(huì)按一定的方式對(duì)物料編碼進(jìn)行分類，物料編碼時(shí)通常按照大類―中類―小類―流水號(hào)的形式進(jìn)行編碼，這樣在日常統(tǒng)計(jì)分析時(shí)比較方便。
3)長(zhǎng)度統(tǒng)一：一般情況下編碼長(zhǎng)度統(tǒng)一可以使我們閱讀或者錄入時(shí)很容易發(fā)現(xiàn)物料編碼長(zhǎng)度不對(duì)（漏輸入或多輸入），也有利于物料進(jìn)行排序和分類匯總。
現(xiàn)以超市庫(kù)存為例進(jìn)行介紹物料代碼的編制，物料代碼中第一位為1，利用EXCEL的數(shù)據(jù)有效性將物料類別分成調(diào)料、干貨、酒、副食、煙、糧食等類別，其代碼分別對(duì)應(yīng)01、02、03、04……99，對(duì)于比較復(fù)雜的生產(chǎn)型公司來講物料的編碼除遵循上述基本規(guī)則外還必須考慮公司物料的分類等各種物料屬性，在此就不在贅述，為了簡(jiǎn)化我沒有進(jìn)行物料中小類別的分類，物料編碼中后五位為流水號(hào)，在EXCEL中物料代碼必須設(shè)置成文本格式。
1.2加權(quán)平均法實(shí)施的大體思路
計(jì)算本月發(fā)出物料成本、月末結(jié)存物料成本應(yīng)該具備以下幾張基礎(chǔ)的報(bào)表：期初庫(kù)存報(bào)表、本月入庫(kù)日?qǐng)?bào)報(bào)表、本月銷售（耗 用）發(fā)出日?qǐng)?bào)報(bào)表，其中倉(cāng)庫(kù)管理員物料入庫(kù)日?qǐng)?bào)和物料出庫(kù)日?qǐng)?bào)中的出入庫(kù)的每一筆記錄必須嚴(yán)格按照日期敘時(shí)逐條進(jìn)行登記，可以將入庫(kù)單和出庫(kù)單的數(shù)量、規(guī)格型號(hào)等物料信息定期傳遞到財(cái)務(wù)部門。我們知道：月末結(jié)存物料數(shù)量=月初物料結(jié)存數(shù)量+本月物料入庫(kù)數(shù)量-本月物料出庫(kù)數(shù)量，根據(jù)庫(kù)存物料數(shù)量這一個(gè)基礎(chǔ)公式我們可以計(jì)算每一種物料的月末結(jié)存數(shù)量，由于入庫(kù)是按日期敘時(shí)登記的，所以針對(duì)每一種物料的數(shù)量可以用SUMIF函數(shù)來匯總計(jì)算其入庫(kù)數(shù)量；財(cái)務(wù)部門根據(jù)發(fā)票的物料開票金額或者發(fā)票所附的物料清單的未含稅價(jià)格、倉(cāng)庫(kù)管理人員的入庫(kù)單數(shù)量就可以確定每一種物料的入庫(kù)單價(jià)，同樣地物料一個(gè)月內(nèi)多次入庫(kù)也可以利用SUMIF函數(shù)來匯總計(jì)算其入庫(kù)金額，這樣就確定了加權(quán)平均法公式中本月入庫(kù)的數(shù)量及金額，再結(jié)合已經(jīng)存在的期初庫(kù)存物料的數(shù)量和單價(jià)，每種物料的加權(quán)平均單價(jià)就可以確定了，所以為了計(jì)算物料成本就必須加上一張匯總期初庫(kù)存、本月入庫(kù)日?qǐng)?bào)、本月出庫(kù)日?qǐng)?bào)的報(bào)表用來自動(dòng)計(jì)算結(jié)存的報(bào)表即月末庫(kù)存物料匯總表。我將期初庫(kù)存報(bào)表、本月入庫(kù)日?qǐng)?bào)報(bào)表、本月銷售（耗用）出庫(kù)日?qǐng)?bào)報(bào)表、月末庫(kù)存物料明細(xì)表放在同一工作簿里，見下面的截圖（1），前三張表是基礎(chǔ)的表格，月末庫(kù)存物料明細(xì)表中是匯總本月入庫(kù)出庫(kù)結(jié)存的數(shù)據(jù)。
圖1
2具體實(shí)施步驟
2.1基礎(chǔ)表格的設(shè)計(jì)
期初庫(kù)存物料表中應(yīng)具備如下要素：物料編碼、序號(hào)、類別、物料名稱、品牌、規(guī)格型號(hào)、包裝率、最小單位、數(shù)量、單價(jià)、金額、備注等要素，下圖2為期初庫(kù)存物料表的表頭：
圖2
本月入庫(kù)報(bào)表中應(yīng)具備如下要素：物料編碼、序號(hào)、類別、物料名稱、品牌、規(guī)格型號(hào)、包裝率、最小單位、整裝數(shù)量、零散數(shù)量、單價(jià)、金額、入庫(kù)單號(hào)、備注等要素，見下圖3。
圖3
在做本月入庫(kù)日?qǐng)?bào)表時(shí)倉(cāng)庫(kù)管理人員去期庫(kù)存表中選擇物料代碼然后根據(jù)手工入庫(kù)單進(jìn)行填報(bào)入庫(kù)數(shù)量、入庫(kù)單號(hào)等信息，如果期初庫(kù)存物料表中沒有相應(yīng)的物料可以直接添加新的物料代碼，在備注后表明本月新增。財(cái)務(wù)人員可以根據(jù)倉(cāng)庫(kù)管理人員傳遞到財(cái)務(wù)部門的入庫(kù)單和采購(gòu)發(fā)票或者發(fā)票所附的采購(gòu)清單在入庫(kù)日?qǐng)?bào)表中填報(bào)其入庫(kù)單價(jià)，這樣就確定了入庫(kù)物料的成本。本月入庫(kù)日?qǐng)?bào)表中標(biāo)有黃色的部分是可以進(jìn)行手工輸入的，在第三行類別、物料名稱、品牌、規(guī)格型號(hào)、單位這些單元格中可以定義如下公式：=IF(A3="","",VLOOKUP(A3,期初庫(kù)存!A:H,3,FALSE))、=IF(A3="","",VLOOKUP(A3,期初庫(kù)存!A:H,4,FALSE))、=IF(A3="","", VLOOKUP(A3,期初庫(kù)存!A:H,5,FALSE))……就可以將這些所需的物料信息自動(dòng)從期初庫(kù)存表中引用過來。因?yàn)橛行┪锪鲜前凑b購(gòu)入、零星發(fā)出的，所以這些物料有其包裝率，因此最小單位數(shù)量單元格可以定義如下公式：=G3*I3+J3,即使沒有包裝率也可應(yīng)用此公式，本表的單價(jià)按照最小計(jì)量單位的價(jià)格進(jìn)行計(jì)量，本月入庫(kù)金額的單元格可以定義如下公式：=round（M3*K3,2），上述公式定義完畢可以將上述公式向下拖動(dòng)以便復(fù)制這些公式到該表格的合計(jì)行的上一行即可。本月入庫(kù)日?qǐng)?bào)中入庫(kù)單號(hào)可以錄入倉(cāng)管部門提供的入庫(kù)單編號(hào)，備注列中可以注上憑證號(hào)或者發(fā)票號(hào)碼，可以和財(cái)務(wù)系統(tǒng)金額核對(duì)一致，在這些有計(jì)算公式的區(qū)域中不允許手工收入（即表中表頭沒有填充黃色部分的所在列的區(qū)域），為了防止錯(cuò)誤操作破壞表中自動(dòng)取數(shù)的公式我們還可以將這些有計(jì)算公式的單元格區(qū)域保護(hù)起來。
本月發(fā)出日?qǐng)?bào)報(bào)表中應(yīng)具備如下要素：物料編碼、序號(hào)、類別、物料名稱、品牌、規(guī)格型號(hào)、包裝率、最小單位、整裝數(shù)量、零散數(shù)量、單價(jià)、金額、備注等要素，截圖如圖4。
圖4
上面標(biāo)黃色的部分是需要進(jìn)行手工輸入的，這張表中出庫(kù)類型根據(jù)公司的業(yè)務(wù)類型分零售、賒銷、配送、內(nèi)部領(lǐng)用；類別、物料名稱、品牌、規(guī)格型號(hào)、包裝率、最小單位數(shù)量等沒有填充黃色的表頭單元格所在列的區(qū)域的取數(shù)公式和上述本月入庫(kù)日?qǐng)?bào)表中取數(shù)公式一樣。
2.2月末結(jié)存報(bào)表的設(shè)計(jì)
月末結(jié)存明細(xì)匯總表這張表中物料的相關(guān)信息可以利用VLOOKUP函數(shù)、SUMIF函數(shù)將期初庫(kù)存、本月入庫(kù)、本月出庫(kù)報(bào)表的
相關(guān)信息從這些表格中引用過來，下面分別進(jìn)行介紹。
圖5
匯總表中類別、物料名稱、品牌、規(guī)格型號(hào)、包裝率、最小單位、月初結(jié)存數(shù)量、月初結(jié)存物料單價(jià)等信息（見圖5）運(yùn)用VLOOKUP函數(shù)都可以將這些信息自動(dòng)從期初庫(kù)存報(bào)表中引用過來，在這張表格沒有填充黃色表頭的下一行即第三行的單元格中（即單元格C3：K3這個(gè)區(qū)域）分別定義如下公式：=IF(A3="","",VLOOKUP(A3,期初庫(kù)存2011.1!A:I,3,FALSE))、=IF(A3="","",VLOOKUP(A3,期初庫(kù)存2011.1!A:I,4,FALSE))、=IF(A3="","",VLOOKUP(A3,期初庫(kù)存2011.1!A:I,5,FALSE)……。
因?yàn)楸驹氯霂?kù)報(bào)表中是以日?qǐng)?bào)的形式進(jìn)行按物料明細(xì)進(jìn)行錄入入庫(kù)信息的，所以同一種物料就有可能多次采購(gòu)入庫(kù)，在匯總這些物料入庫(kù)數(shù)量時(shí)可以考慮利用SUMIF函數(shù)從本月入庫(kù)日?qǐng)?bào)表中將其累計(jì)數(shù)量匯總過來，因此在匯總表中本月入庫(kù)欄中數(shù)量這一列的單元格中即L3單元格中定義如下公式=SUMIF(入庫(kù)日?qǐng)?bào)2011.1!$A$3:$A$20000,A3,入庫(kù)日?qǐng)?bào)2011.1!$K$3:$K$20000)，考慮到公司的業(yè)務(wù)量，也可以將匯總數(shù)量的行號(hào)最大值修改成大于20000或者小于20000的任何值，反正要將本月入庫(kù)日?qǐng)?bào)中全部入庫(kù)記錄給涵蓋進(jìn)去即可;同理在在匯總表中本月入庫(kù)欄中金額這一列的單元格中即N3單元格中定義如下公式：=SUMIF(入庫(kù)日?qǐng)?bào)2011.1!$A$3:$A$20000,A3,入庫(kù)日?qǐng)?bào)2011.1!$N$3:$N$20000),接下來定義入庫(kù)單價(jià)這一列，考慮到有些物料可能本月沒有購(gòu)入，因此可以利用IF函數(shù)進(jìn)行判斷本月入庫(kù)數(shù)量是否等于0，因此在匯總表中本月入庫(kù)欄中單價(jià)這一列的單元格中即單元格M3單元格中定義如下公式：=IF(L3<>0,round（N3/L3,2）,""),利用這三個(gè)公式就可以完整地將入庫(kù)日?qǐng)?bào)中數(shù)量、金額、單價(jià)匯總起來。相關(guān)截圖見圖6；如果上月有暫估物料的情況發(fā)生本月發(fā)票賬單已到能夠確定單價(jià)時(shí)就在本月入庫(kù)報(bào)表中做紅字入庫(kù)和按正確的單價(jià)做藍(lán)字入庫(kù)，這樣會(huì)產(chǎn)生入庫(kù)金額的差額，入庫(kù)金額的確認(rèn)仍然和前述公式一樣。
圖6
因?yàn)槌鰩?kù)報(bào)表中是以日?qǐng)?bào)的形式進(jìn)行按物料明細(xì)進(jìn)行錄入出庫(kù)信息記錄的，所以同一種物料就有可能發(fā)生多次出庫(kù)記錄，在匯總這些物料出庫(kù)數(shù)量時(shí)同樣可以考慮利用SUMIF函數(shù)來計(jì)算，在O3單元格中定義的公式如下：=SUMIF(出庫(kù)日?qǐng)?bào)2011.1!$A$3:$A$40000,A3,出庫(kù)日?qǐng)?bào)2011.1!$P$3:$P$40000)，P列即為銷售出庫(kù)日?qǐng)?bào)中最小單位數(shù)量這一列，考慮到公司出庫(kù)的業(yè)務(wù)量，也可以將匯總數(shù)量的行號(hào)最大值修改成大于40000或者小于40000的任何值，反正要將本月出庫(kù)日?qǐng)?bào)中全部入庫(kù)記錄給涵蓋進(jìn)去即可，接下來就需要在P3單元格定義出庫(kù)單價(jià)，定義單價(jià)可以采用月末一次加權(quán)平均法計(jì)算單價(jià)，具體計(jì)算公式為：=IF(O3<>0,ROUND((K3+ N3)/(I3+L3),2),0)，這個(gè)公式就是應(yīng)用前面所述的月末加權(quán)平均單價(jià)的文字公式的具體表達(dá)式，出庫(kù)金額定義公式為：= ROUND(O3*P3,2)，利用這三個(gè)公式這樣就可以計(jì)算出本月的出庫(kù)成本金額。
圖7
我們知道月末物料結(jié)存數(shù)量=期初庫(kù)存數(shù)量+本月入庫(kù)數(shù)量-本月出庫(kù)數(shù)量因此匯總表里面月末結(jié)存的數(shù)量的單元格即R3中可以定義公式：= I3+L3-O3，為了避免出現(xiàn)月末結(jié)存數(shù)量為0但月末結(jié)存余額不為0的情況，我們可以利用IF函數(shù)來排除金額出現(xiàn)尾差的情況，直接將尾差計(jì)入出庫(kù)成本中，尾差對(duì)出庫(kù)成本的影響極小，所以月末結(jié)存數(shù)量的單元格R3、結(jié)存金額的單元格T3的公式定義分別為：=IF(I3+L3-O3=0,0,I3+L3-O3)、=IF（I3+L3-O3<>0,K3+N3-Q3,0）,月末結(jié)存單價(jià)的單元格S3中公式定義：=IF(Q3<>0, round（S3/Q3，2）,"")截圖如下圖8。
圖8
為了正確結(jié)出每月出庫(kù)成本金額可以在這張表中增加一列：出庫(kù)金額尾差調(diào)整，在這列中T3單元格可定義如下公式：= IF(AND(R3=0,K3+N3-Q3<>0),K3+N3-Q3,0)這個(gè)公式就是判斷出現(xiàn)月末結(jié)存數(shù)量為0但月末結(jié)存余額不為0的情況，如果出現(xiàn)這種情況就一律將這個(gè)尾差計(jì)入出庫(kù)成本之中，所以還應(yīng)添加最后一列出庫(kù)成本合計(jì)即U列，U3單元格中定義公式如下：= U3+Q3，見下圖9。
圖9
上述公式定義完畢可以將上述公式向下拖動(dòng)以便復(fù)制這些公式到該表格的合計(jì)行的上一行即可，經(jīng)過以上步驟就可以將月末一次加權(quán)平均法計(jì)算物料成本在EXCEL中實(shí)現(xiàn)了。
對(duì)于物料的暫估入庫(kù)而言，我們?nèi)匀豢梢栽谶@個(gè)表格中進(jìn)行計(jì)算，對(duì)于貨物本月已經(jīng)入庫(kù)但供應(yīng)商發(fā)票還未到達(dá)的情況下（即貨到票未到），到下月該物料的發(fā)票已經(jīng)到達(dá)的情況下可以按照上月暫估入庫(kù)的數(shù)量在入庫(kù)日?qǐng)?bào)中做一筆紅字入庫(kù)記錄，據(jù)此沖回暫估入庫(kù)，然后按照正確的單價(jià)和數(shù)量做一筆入庫(kù)記錄。如果上月有物料的暫估出庫(kù)情況發(fā)生在計(jì)算當(dāng)月發(fā)出庫(kù)存商品成本時(shí)，上月仍然應(yīng)當(dāng)按照規(guī)定的方法正常計(jì)算確定發(fā)出成本，到下月時(shí)如果正式發(fā)票已到紅字沖回入庫(kù)，并且取得發(fā)票正式入賬，兩者相抵，余額為暫估成本與實(shí)際成本之間的差額，該差額就由確認(rèn)發(fā)票當(dāng)月發(fā)出和月末結(jié)存的庫(kù)存物料承擔(dān)。如果該物料上月恰好已經(jīng)全部發(fā)出，該物料本月再無(wú)購(gòu)進(jìn)與結(jié)存，本月發(fā)票已到實(shí)際成本與暫估成本之間的差異按照前面所述月末結(jié)存為零但結(jié)存金額不為零的情形進(jìn)行處理，例如香脆椒上月購(gòu)進(jìn)20袋已經(jīng)全部發(fā)出，本月發(fā)票已經(jīng)到達(dá)單價(jià)已經(jīng)確定，在月末結(jié)存物料明細(xì)表中本月入庫(kù)中就反映為只有金額而數(shù)量為零，在出庫(kù)成本尾差中就作了調(diào)整處理，該差額進(jìn)入沖回暫估的當(dāng)月成本中，不會(huì)還留在月末結(jié)存的金額中（見下圖10）。
圖10
2003版EXCEL最大行數(shù)只能是65536行，考慮到業(yè)務(wù)量的變化，對(duì)于有些中型企業(yè)不夠用，可以考慮安裝2007版EXCEL或者2010版EXCEL，其最大行數(shù)可達(dá)到1048576行，對(duì)于中小型企業(yè)來講，每個(gè)月的業(yè)務(wù)量這么多行是夠使用的了。從設(shè)計(jì)的角度來看，利用EXCEL函數(shù)省去了數(shù)據(jù)庫(kù)方面的大量工作，也不需要在EXCEL中去設(shè)計(jì)復(fù)雜的宏，提高了設(shè)計(jì)效率。而從中小型企事業(yè)用戶的角度來考慮，因其對(duì)電腦要求不高、對(duì)于一般EXCEL操作者來講簡(jiǎn)單易懂也便于操作，從而很受中小企業(yè)的歡迎。本設(shè)計(jì)方案已經(jīng)在陽(yáng)光國(guó)際阿爾及利亞超市應(yīng)用近2年，取得了明顯的成效。存在的不足之處是沒有將本月期末庫(kù)存自動(dòng)結(jié)轉(zhuǎn)到下月的功能，每月初需要將上月末結(jié)存的相關(guān)數(shù)據(jù)復(fù)制過來然后使用或者用VLOOKUP函數(shù)引用過來然后將有關(guān)函數(shù)修改下拖動(dòng)復(fù)制下來，顯得不太方便。
參考文獻(xiàn)：
[1]中電電氣集團(tuán)物料編碼規(guī)范[DB/OL].http://www.省略/p-118961650.html.
本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)
點(diǎn)擊舉報(bào)。
