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