炎陵娜妹子農(nóng)業(yè)開發(fā)有限公司基于Excel進(jìn)行進(jìn)銷存管理的方案設(shè)計

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

目錄

1公司簡介............................................................1

2Excel在企業(yè)進(jìn)銷存管理的介紹........................................1

3Excel基于進(jìn)銷存管理的設(shè)計..........................................2

3.1基本資料表模塊設(shè)計............................................2

3.2采購業(yè)務(wù)表模塊設(shè)計............................................4

3.2.1原材料月初數(shù)據(jù)表設(shè)計....................................4

3.2.1原材料采購業(yè)務(wù)表設(shè)計.....................................4

3.2.2應(yīng)付賬款匯總表設(shè)計.......................................5

3.3銷售業(yè)務(wù)表模塊設(shè)計............................................5

3.3.1產(chǎn)品銷售業(yè)務(wù)表設(shè)計.......................................6

3.3.2應(yīng)收賬款匯總表...........................................6

3.4庫存業(yè)務(wù)表模塊設(shè)計............................................7

3.4.1領(lǐng)料清單設(shè)計............................................7

3.4.2完工產(chǎn)品入庫表設(shè)計......................................8

3.4.3材料成本核算表設(shè)計......................................9

3.4.4庫存材料盤點(diǎn)表設(shè)計.....................................10

4總結(jié).......................................................10

參考資料............................................................12

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

炎陵娜妹子農(nóng)業(yè)開發(fā)有限公司基于Excel進(jìn)行進(jìn)銷存

管理的方案設(shè)計

1公司簡介

炎陵娜妹子農(nóng)業(yè)開發(fā)有限公司成立于2013年10月29日，企業(yè)地址為湖南

省株洲市炎陵縣九龍經(jīng)濟(jì)開發(fā)區(qū)果園周路，其注冊資本為200萬人民幣。企業(yè)

的業(yè)務(wù)包括：飲料制造，食品制造，現(xiàn)制現(xiàn)售飲用水，生鮮乳道路運(yùn)輸，餐飲

服務(wù)等。

2Excel在企業(yè)進(jìn)銷存管理的介紹

企業(yè)的進(jìn)銷存管理是指企業(yè)從采購貨物，儲存貨物到銷售貨物的動態(tài)管理。

Excel有助于企業(yè)及時了解企業(yè)進(jìn)貨，存貨，銷貨的情況，保證有合理的存貨，

既不會因?yàn)榇尕洸蛔銓?dǎo)致無法供貨，也不會因?yàn)榇尕涍^多引起庫存空間被占用，

增加持有成本，存貨被積壓。同時可以促進(jìn)銷售。Excel在進(jìn)銷存管理中的應(yīng)用，

可以提高企業(yè)的工作效率，并使得人力、物力等成本降低，實(shí)現(xiàn)成本效益化。

炎陵娜妹子農(nóng)業(yè)開發(fā)有限公司屬于中小型企業(yè)，目前并沒有擁有完整的一

套屬于自己的進(jìn)銷存管理系統(tǒng)。如圖1所示是關(guān)于進(jìn)銷存管理的流程圖，公司

根據(jù)銷售訂單去計劃生產(chǎn)，合理規(guī)劃采購的數(shù)量并從倉庫領(lǐng)料進(jìn)行生產(chǎn)加工，

完工產(chǎn)品入庫之后，再通過倉庫出庫出去到達(dá)客戶手中。對于進(jìn)銷存管理系統(tǒng)

的設(shè)計，首先建立一套Excel工作簿，其中包含為基本信息表模塊，采購業(yè)務(wù)

表模塊，銷售業(yè)務(wù)表模塊，庫存業(yè)務(wù)表模塊這四個模板。

1

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

圖1進(jìn)銷存管理的流程圖

3Excel基于進(jìn)銷存管理的設(shè)計

3.1基本資料表模塊設(shè)計

基本信息表主要是介紹炎陵娜妹子農(nóng)業(yè)開發(fā)有限公司的客戶信息和產(chǎn)品信

息以及原材料基本信息。如圖2和圖3，4，5所示，進(jìn)入到Excel中，新建一

個工作簿，將sheet1命名為“基本資料表”，在該工作表中建立一個“客戶基

本資料”表和一個“產(chǎn)品基本資料”表以及一個“材料基本資料”表；并在“客

戶基本資料”表中設(shè)置“客戶名稱”，“客戶地址”，“客戶電話號碼”，在

“產(chǎn)品基本資料”表中設(shè)置“產(chǎn)品名稱”，“單位”，“銷售單價”；在“材

料基本資料”表中設(shè)置“材料名稱”，“材料編碼”，“單位”。這些資料由

信息部門負(fù)責(zé)從采購部門、銷售部門中獲取，并由信息部門經(jīng)理負(fù)責(zé)審核。

2

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

圖2客戶基本資料表

圖3產(chǎn)品基本資料

圖4材料基本資料

3

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

圖5工作表名稱

3.2采購業(yè)務(wù)表模塊設(shè)計

采購業(yè)務(wù)表模塊包括原材料采購業(yè)務(wù)表和應(yīng)付賬款匯總表，該模塊的設(shè)計

主要由采購部門負(fù)責(zé)，采購人員根據(jù)訂貨需求以及現(xiàn)有存貨來對材料進(jìn)行采購，

采購部經(jīng)理負(fù)責(zé)對于采購訂單進(jìn)行確認(rèn)和審核。而應(yīng)付賬款的管理由財務(wù)人員

進(jìn)行負(fù)責(zé)并由財務(wù)部經(jīng)理負(fù)責(zé)審核和確認(rèn)。

3.2.1原材料月初數(shù)據(jù)表設(shè)計

原材料月初數(shù)據(jù)表可以一目了然地展現(xiàn)出該公司期初原材料的情況。該表

中包括材料編碼，材料名稱，單位，月初結(jié)存數(shù)量，月初單位成本，月初結(jié)存

金額。操作人員可以根據(jù)上一期期末的原材料結(jié)存情況以及庫存材料盤點(diǎn)表進(jìn)

行填寫。

圖6原材料月初數(shù)據(jù)表

3.2.1原材料采購業(yè)務(wù)表設(shè)計

原材料采購業(yè)務(wù)表是反映公司日常采購材料的情況。炎陵娜妹子農(nóng)業(yè)開發(fā)

有限公司的水果原料主要是通過農(nóng)村合作社或者是農(nóng)民個人進(jìn)行采購，用Excel

對進(jìn)銷存管理進(jìn)行設(shè)計，為了更好地進(jìn)行歸納處理沒有具體到農(nóng)民個人。如圖7

所示，將工作表命名為“采購業(yè)務(wù)表”，在該工作表中建立一個“原材料采購

業(yè)務(wù)表”，包括業(yè)務(wù)日期，摘要，供應(yīng)商，材料編碼，材料名稱，單位，數(shù)量，

單價，金額，已付貨款，結(jié)算方式，未付貨款。

原材料采購業(yè)務(wù)表中的E,F，H這三列的單元格不需要銷售人員手動輸入，

可以運(yùn)用VLOOKUP函數(shù)自動得出相關(guān)信息，具體操作如下：當(dāng)材料編碼輸入之

后，系統(tǒng)會自動匹配出材料名稱和單位，

原材料采購業(yè)務(wù)表中的“金額”和“未付貨款”可利用以下公式計算得出。

4

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

在I3單元格中輸入公式“=G3*H3”,并把I3單元格向下填充，在L3單元格中

輸入公式“=I3-J3”,并把L3單元格向下填充。

圖7原材料采購業(yè)務(wù)表

3.2.2應(yīng)付賬款匯總表設(shè)計

應(yīng)付賬款匯總表中的“應(yīng)付貨款”和“已付貨款”是應(yīng)從原材料采購業(yè)務(wù)

表中匯總出來。，財務(wù)人員可以運(yùn)用SUMIF函數(shù),具體操作如下：將工作表命名

為“應(yīng)付賬款匯總表”，在B3單元格中輸入公式“=SUMIF(采購業(yè)務(wù)表

$C$3:$C$7,A3,采購業(yè)務(wù)表$I$3:$I$7)”,并把B3單元格向下填充，在C3單元

格中輸入公式“=SUMIF(采購業(yè)務(wù)表$C$3:$C$7,A3,采購業(yè)務(wù)表$J$3:$J$7）”，

并把C3單元格向下填充?！皯?yīng)付賬款余額”可以采用公式計算得出，在D3單

元格中輸入公式“=B3-C3”,并把D3單元格向下填充。當(dāng)“供應(yīng)商”由財務(wù)人

員手動填寫之后，B（應(yīng)付貨款）,C（已付貨款）,D（應(yīng)付賬款余額）三列的數(shù)

據(jù)會自動計算得出。

圖8應(yīng)付賬款匯總表

3.3銷售業(yè)務(wù)表模塊設(shè)計

銷售業(yè)務(wù)表模塊包括兩個部分，一個是產(chǎn)品銷售業(yè)務(wù)表，一個是應(yīng)收賬款

匯總表，該模塊的設(shè)計主要銷售部門負(fù)責(zé)，銷售人員負(fù)責(zé)銷售合同以及銷售訂

單的簽署，并由銷售經(jīng)理負(fù)責(zé)審核。若貨品從倉庫發(fā)出且已經(jīng)確認(rèn)銷售收入之

后仍未收到貨款，即形成應(yīng)收賬款，而應(yīng)收賬款的管理由財務(wù)部負(fù)責(zé)，并由財

5

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

務(wù)部經(jīng)理負(fù)責(zé)審核和確認(rèn)。

3.3.1產(chǎn)品銷售業(yè)務(wù)表設(shè)計

產(chǎn)品銷售業(yè)務(wù)表主要是反映公司在一定時期內(nèi)日常銷售產(chǎn)品的情況。如圖9

所示，在工作簿中新建一個工作表命名為“產(chǎn)品銷售業(yè)務(wù)表”，在該表中輸入

“業(yè)務(wù)日期”，“摘要”，“客戶”，“產(chǎn)品編碼”，“產(chǎn)品名稱”，“單位”，

“銷售數(shù)量”，“銷售單位”,“應(yīng)收貨款”，“實(shí)收貨款”，“應(yīng)收賬款余

額”。

其中A（業(yè)務(wù)日期）,B(摘要）,C（客戶）,D(商品編碼）,G（銷售數(shù)量）,J

（實(shí)收貨款）這六列的信息由操作人員手動輸入，按住鍵盤的快捷組合鍵

“Ctrl+A”將表格全部選中，在“開始”里面選擇“數(shù)值”形式，其他的數(shù)據(jù)

可通過公式、VLOOKUP函數(shù)自動得來。具體操作如下:

單元格E3中輸入公式“=VLOOKUP(D3,基本資料表!$F:$J,3,0)”,并把E3

單元格向下填充；

單元格F3中輸入公式“=VLOOKUP(D3,基本資料表！$F:$J,4,0)”,或者復(fù)

制已經(jīng)輸入公式的E3單元格，粘貼到F3單元格中，將F3單元格公式

“=VLOOKUP(E3,客戶產(chǎn)品基本資料表！$F:$J,4,0)”中的“E3”改為“D3”,

“4”改為“5”，并把F3單元格向下填充；

單元格H3中輸入公式“=VLOOKUP(D3,基本資料表！$F:$J,5,0)”,并把H3

單元格向下填充；

單元格I3中輸入公式“=G3*H3”，并把I3單元格向下填充；

單元格K3中輸入公式“=I3-J3”,并把K3單元格向下填充。

圖9產(chǎn)品銷售業(yè)務(wù)表

3.3.2應(yīng)收賬款匯總表

為了在表格填寫之后，方便財務(wù)人員可以更加清晰明了地找出還未全部結(jié)

清貨款的客戶，于是制作一張“應(yīng)收賬款匯總表”，在該表格中包括“客戶”，

“應(yīng)收貨款”，“已收賬款”，“應(yīng)收賬款余額”，其中A列的數(shù)據(jù)由財務(wù)人

員手動輸入，B列和C列以及D列的數(shù)據(jù)通過公式、SUMIF函數(shù)自動得來，具體

6

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

操作如下:

單元格B3中輸入公式“=SUMIF（產(chǎn)品銷售業(yè)務(wù)表!$C$3:$C$7,A3,產(chǎn)品銷售

業(yè)務(wù)表！$I$3:$I$7）”,并把B3單元格向下填充；

單元格C3中輸入公式“=SUMIF（產(chǎn)品銷售業(yè)務(wù)表!$C$3:$C$7,A3,產(chǎn)品銷售

業(yè)務(wù)表！$J$3:$J$7）”,并把C3單元格向下填充；

D列數(shù)據(jù)的取得有兩種方式，一種是在D3單元格中運(yùn)用SUMIF函數(shù)輸入公

式“=SUMIF（產(chǎn)品銷售業(yè)務(wù)表!$C$3:$C$7,A3,產(chǎn)品銷售業(yè)務(wù)表！$K$3:$K$7）”,

另一種是采用計算公式，單元格D3中輸入公式“=B3-C3”并把C3單元格向下

填充。若應(yīng)收賬款余額出現(xiàn)了負(fù)數(shù)，則代表有預(yù)收賬款的存在，提前收取到對

方的款項。

圖10應(yīng)收賬款匯總表

3.4庫存業(yè)務(wù)表模塊設(shè)計

庫存業(yè)務(wù)表模塊包括領(lǐng)料清單設(shè)計和完工產(chǎn)品入庫表設(shè)計，材料成本核算

表設(shè)計以及庫存材料盤點(diǎn)表設(shè)計。其中領(lǐng)料清單由倉庫人員進(jìn)行管理；完工的

產(chǎn)品由生產(chǎn)人員送到倉庫，并由倉庫人員對產(chǎn)成品負(fù)責(zé)入庫登記，倉庫經(jīng)理對

于產(chǎn)成品入庫情況負(fù)責(zé)審核和確認(rèn)；材料成本核算表由財務(wù)部人員負(fù)責(zé)核算，

由財務(wù)部經(jīng)理負(fù)責(zé)審核和確認(rèn)；庫存材料盤點(diǎn)由財務(wù)部和倉儲部門協(xié)同負(fù)責(zé)，

并由相關(guān)負(fù)責(zé)人進(jìn)行審核和確認(rèn)。

3.4.1領(lǐng)料清單設(shè)計

領(lǐng)料清單表是反映車間生產(chǎn)部門領(lǐng)用原料的情況。在完工產(chǎn)品入庫表后面

新建一個工作表，使其命名為“領(lǐng)料清單”，該工作表包括了業(yè)務(wù)日期，摘要，

材料編碼，材料名稱，單位，領(lǐng)用數(shù)量。其中“材料名稱”，“單位”可以通

過VLOOKUP函數(shù)自動得到數(shù)據(jù)具體操作如下：

單元格D3中輸入公式“=VLOOKUP(C3,基本資料表！$L:$M,2,0)”，并把D3

單元格向下填充；

單元格E3輸入公式“=VLOOKUP(C3,基本資料表！$L:$M,3,0)”，并把E3

7

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

單元格向下填充,

其他列的數(shù)據(jù)（業(yè)務(wù)日期，摘要，材料編碼，領(lǐng)用數(shù)量）由財務(wù)人員手動

輸入。領(lǐng)料清單輸入的數(shù)據(jù)如圖11所示。

圖11領(lǐng)料清單

3.4.2完工產(chǎn)品入庫表設(shè)計

完工產(chǎn)品入庫表反映的是原材料經(jīng)過加工，并完成制作之后入庫的情況。

在應(yīng)收賬款匯總表之后新建一個工作表，將其命名為“完工產(chǎn)品入庫表”，包

括“業(yè)務(wù)日期”，“摘要”，“產(chǎn)品編碼”，“產(chǎn)品名稱”，“單位”，“入

庫數(shù)量”，“入庫單價”，“入庫金額”，其中“業(yè)務(wù)日期”，“摘要”，“產(chǎn)

品編碼”，“入庫數(shù)量”由操作人員手動輸入，其他列的數(shù)據(jù)可以通過公式和

函數(shù)自動得來，具體操作如下：

單元格D3中輸入公式“=VLOOKUP(C3,基本資料表！$F:$J,3,0)”,并把D3

單元格向下填充；

單元格E3中輸入公式“=VLOOKUP(C3,基本資料表！$F:$J,4,0)”，并把E3

單元格向下填充；

單元格G3輸入公式“=VLOOKUP(C3,基本資料表！$F:$J,5,0)”，并把G3

單元格向下填充；

單元格H3中輸入公式“=F3*G3”,并把H3單元格向下填充。

完工產(chǎn)品入庫表輸入結(jié)果如圖12所示。

8

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

圖12完工產(chǎn)品入庫表

3.4.3材料成本核算表設(shè)計

材料成本核算表是反映原材料訂貨價格的情況。由于炎陵娜妹子農(nóng)業(yè)開發(fā)

有限公司采用月末加權(quán)平均法計算材料成本，所以在計算單位成本時，運(yùn)用月

末加權(quán)平均法的公式計算材料成本：

月初結(jié)存材料的金額+本月入庫材料的金額

月初結(jié)存材料的數(shù)量+本月入庫材料的數(shù)量

在原材料出入庫表中新建一個工作表，命名為“材料成本核算表”，該表

中包括材料編碼，材料名稱，月初結(jié)存數(shù)量，月初結(jié)存金額，本月入庫數(shù)量，

本月增加的金額，發(fā)出數(shù)量，發(fā)出成本，庫存數(shù)量，月末結(jié)存金額，材料單位

成本。具體操作如下：

當(dāng)輸入A列（材料編碼）的信息,B（材料名稱）列的信息會被系統(tǒng)自動匹

配出來。

C列（月初結(jié)存數(shù)量）和D列（月初結(jié)存金額）的信息操作人員可以手動錄

入，也可以通過表間取數(shù)得來。單元格中C3輸入“=原材料月初數(shù)據(jù)表！D3”,

在D3單元格輸入=“原材料月初數(shù)據(jù)表!E3”，然后把C3單元格和D3單元格向

下填充；

E列（本月入庫數(shù)量）和F列（本月增加的金額）以及G列（發(fā)出數(shù)量）可

以采用SUMIF函數(shù)自動得出相關(guān)數(shù)據(jù)。單元格E3中輸入公式“=SUMIF(原材料

采購業(yè)務(wù)表!$E$3:$E$7,B3,原材料采購業(yè)務(wù)表!$G$3:$G$7)”，在F3單元格輸

入公式“=SUMIF(原材料采購業(yè)務(wù)表!$E$3:$E$7,B3,原材料采購業(yè)務(wù)

表!$I$3:$I$7)”，單元格G3輸入公式“=SUMIF(領(lǐng)料清單!$D$3:$D$10,B3,領(lǐng)

料清單!$F$3:$F$10)”，并把E3,F3,G3單元格向下填充；

發(fā)出成本=材料單位成本*發(fā)出數(shù)量，在H3單元格輸入公式“=K3*G3”；

庫存數(shù)量=（月初結(jié)存數(shù)量+本月入庫數(shù)量）-發(fā)出數(shù)量，在I3單元格中輸

入公式“=(C3+E3)-G3”；

9

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計

月末結(jié)存金額=材料單位成本*庫存數(shù)量，在J3單元格輸入公式

“=K3*I3”;

材料單位成本根據(jù)月末加權(quán)平均法的公式，在K3單元格輸入公式

“=(D3+F3)/(E3+C3)”。

圖13材料成本核算表

3.4.4庫存材料盤點(diǎn)表設(shè)計

庫存材料盤點(diǎn)表可以反映原材料的賬目和實(shí)物是否一致的情況。在材料成

本核算表后面新建一個工作表，使其命名為“庫存材料盤點(diǎn)表”，該表中包括

材料編碼，材料名稱，單位，盤點(diǎn)數(shù)量以及備注。具體操作如下：

當(dāng)操作人員輸入A列（材料編碼）的信息，系統(tǒng)會自動匹配出B列（材料

名稱）和C列（單位）的相關(guān)信息。而D列（盤點(diǎn)數(shù)量）根據(jù)盤點(diǎn)結(jié)果輸入數(shù)

據(jù)，E列（備注）是為賬實(shí)不符時，填寫理由，可以在E3單元格輸入公式“=IF(D3=

材料成本核算表!I3,"","賬實(shí)不符")”，若賬實(shí)相符，則無需填寫，若賬實(shí)不

相符，就會出現(xiàn)“賬實(shí)不符”的這一標(biāo)志，并能夠在其后面解釋背后發(fā)生的原

因。

圖14庫存材料盤點(diǎn)表

4總結(jié)