湖南商務職業技術學院畢業設計

目錄

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

2Excel在企業進銷存管理的介紹........................................1

3Excel基于進銷存管理的設計..........................................2

3.1基本資料表模塊設計............................................2

3.2采購業務表模塊設計............................................4

3.2.1原材料月初數據表設計....................................4

3.2.1原材料采購業務表設計.....................................4

3.2.2應付賬款匯總表設計.......................................5

3.3銷售業務表模塊設計............................................5

3.3.1產品銷售業務表設計.......................................6

3.3.2應收賬款匯總表...........................................6

3.4庫存業務表模塊設計............................................7

3.4.1領料清單設計............................................7

3.4.2完工產品入庫表設計......................................8

3.4.3材料成本核算表設計......................................9

3.4.4庫存材料盤點表設計.....................................10

4總結.......................................................10

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

湖南商務職業技術學院畢業設計

炎陵娜妹子農業開發有限公司基于Excel進行進銷存

管理的方案設計

1公司簡介

炎陵娜妹子農業開發有限公司成立于2013年10月29日，企業地址為湖南

省株洲市炎陵縣九龍經濟開發區果園周路，其注冊資本為200萬人民幣。企業

的業務包括：飲料制造，食品制造，現制現售飲用水，生鮮乳道路運輸，餐飲

服務等。

2Excel在企業進銷存管理的介紹

企業的進銷存管理是指企業從采購貨物，儲存貨物到銷售貨物的動態管理。

Excel有助于企業及時了解企業進貨，存貨，銷貨的情況，保證有合理的存貨，

既不會因為存貨不足導致無法供貨，也不會因為存貨過多引起庫存空間被占用，

增加持有成本，存貨被積壓。同時可以促進銷售。Excel在進銷存管理中的應用，

可以提高企業的工作效率，并使得人力、物力等成本降低，實現成本效益化。

炎陵娜妹子農業開發有限公司屬于中小型企業，目前并沒有擁有完整的一

套屬于自己的進銷存管理系統。如圖1所示是關于進銷存管理的流程圖，公司

根據銷售訂單去計劃生產，合理規劃采購的數量并從倉庫領料進行生產加工，

完工產品入庫之后，再通過倉庫出庫出去到達客戶手中。對于進銷存管理系統

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

表模塊，銷售業務表模塊，庫存業務表模塊這四個模板。

1

湖南商務職業技術學院畢業設計

圖1進銷存管理的流程圖

3Excel基于進銷存管理的設計

3.1基本資料表模塊設計

基本信息表主要是介紹炎陵娜妹子農業開發有限公司的客戶信息和產品信

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

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

本資料”表和一個“產品基本資料”表以及一個“材料基本資料”表；并在“客

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

“產品基本資料”表中設置“產品名稱”，“單位”，“銷售單價”；在“材

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

信息部門負責從采購部門、銷售部門中獲取，并由信息部門經理負責審核。

2

湖南商務職業技術學院畢業設計

圖2客戶基本資料表

圖3產品基本資料

圖4材料基本資料

3

湖南商務職業技術學院畢業設計

圖5工作表名稱

3.2采購業務表模塊設計

采購業務表模塊包括原材料采購業務表和應付賬款匯總表，該模塊的設計

主要由采購部門負責，采購人員根據訂貨需求以及現有存貨來對材料進行采購，

采購部經理負責對于采購訂單進行確認和審核。而應付賬款的管理由財務人員

進行負責并由財務部經理負責審核和確認。

3.2.1原材料月初數據表設計

原材料月初數據表可以一目了然地展現出該公司期初原材料的情況。該表

中包括材料編碼，材料名稱，單位，月初結存數量，月初單位成本，月初結存

金額。操作人員可以根據上一期期末的原材料結存情況以及庫存材料盤點表進

行填寫。

圖6原材料月初數據表

3.2.1原材料采購業務表設計

原材料采購業務表是反映公司日常采購材料的情況。炎陵娜妹子農業開發

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

對進銷存管理進行設計，為了更好地進行歸納處理沒有具體到農民個人。如圖7

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

業務表”，包括業務日期，摘要，供應商，材料編碼，材料名稱，單位，數量，

單價，金額，已付貨款，結算方式，未付貨款。

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

可以運用VLOOKUP函數自動得出相關信息，具體操作如下：當材料編碼輸入之

后，系統會自動匹配出材料名稱和單位，

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

4

湖南商務職業技術學院畢業設計

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

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

圖7原材料采購業務表

3.2.2應付賬款匯總表設計

應付賬款匯總表中的“應付貨款”和“已付貨款”是應從原材料采購業務

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

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

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

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

并把C3單元格向下填充。“應付賬款余額”可以采用公式計算得出，在D3單

元格中輸入公式“=B3-C3”,并把D3單元格向下填充。當“供應商”由財務人

員手動填寫之后，B（應付貨款）,C（已付貨款）,D（應付賬款余額）三列的數

據會自動計算得出。

圖8應付賬款匯總表

3.3銷售業務表模塊設計

銷售業務表模塊包括兩個部分，一個是產品銷售業務表，一個是應收賬款

匯總表，該模塊的設計主要銷售部門負責，銷售人員負責銷售合同以及銷售訂

單的簽署，并由銷售經理負責審核。若貨品從倉庫發出且已經確認銷售收入之

后仍未收到貨款，即形成應收賬款，而應收賬款的管理由財務部負責，并由財

5

湖南商務職業技術學院畢業設計

務部經理負責審核和確認。

3.3.1產品銷售業務表設計

產品銷售業務表主要是反映公司在一定時期內日常銷售產品的情況。如圖9

所示，在工作簿中新建一個工作表命名為“產品銷售業務表”，在該表中輸入

“業務日期”，“摘要”，“客戶”，“產品編碼”，“產品名稱”，“單位”，

“銷售數量”，“銷售單位”,“應收貨款”，“實收貨款”，“應收賬款余

額”。

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

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

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

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

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

單元格向下填充；

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

制已經輸入公式的E3單元格，粘貼到F3單元格中，將F3單元格公式

“=VLOOKUP(E3,客戶產品基本資料表！$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產品銷售業務表

3.3.2應收賬款匯總表

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

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

“應收貨款”，“已收賬款”，“應收賬款余額”，其中A列的數據由財務人

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

6

湖南商務職業技術學院畢業設計

操作如下:

單元格B3中輸入公式“=SUMIF（產品銷售業務表!$C$3:$C$7,A3,產品銷售

業務表！$I$3:$I$7）”,并把B3單元格向下填充；

單元格C3中輸入公式“=SUMIF（產品銷售業務表!$C$3:$C$7,A3,產品銷售

業務表！$J$3:$J$7）”,并把C3單元格向下填充；

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

式“=SUMIF（產品銷售業務表!$C$3:$C$7,A3,產品銷售業務表！$K$3:$K$7）”,

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

填充。若應收賬款余額出現了負數，則代表有預收賬款的存在，提前收取到對

方的款項。

圖10應收賬款匯總表

3.4庫存業務表模塊設計

庫存業務表模塊包括領料清單設計和完工產品入庫表設計，材料成本核算

表設計以及庫存材料盤點表設計。其中領料清單由倉庫人員進行管理；完工的

產品由生產人員送到倉庫，并由倉庫人員對產成品負責入庫登記，倉庫經理對

于產成品入庫情況負責審核和確認；材料成本核算表由財務部人員負責核算，

由財務部經理負責審核和確認；庫存材料盤點由財務部和倉儲部門協同負責，

并由相關負責人進行審核和確認。

3.4.1領料清單設計

領料清單表是反映車間生產部門領用原料的情況。在完工產品入庫表后面

新建一個工作表，使其命名為“領料清單”，該工作表包括了業務日期，摘要，

材料編碼，材料名稱，單位，領用數量。其中“材料名稱”，“單位”可以通

過VLOOKUP函數自動得到數據具體操作如下：

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

單元格向下填充；

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

7

湖南商務職業技術學院畢業設計

單元格向下填充,

其他列的數據（業務日期，摘要，材料編碼，領用數量）由財務人員手動

輸入。領料清單輸入的數據如圖11所示。

圖11領料清單

3.4.2完工產品入庫表設計

完工產品入庫表反映的是原材料經過加工，并完成制作之后入庫的情況。

在應收賬款匯總表之后新建一個工作表，將其命名為“完工產品入庫表”，包

括“業務日期”，“摘要”，“產品編碼”，“產品名稱”，“單位”，“入

庫數量”，“入庫單價”，“入庫金額”，其中“業務日期”，“摘要”，“產

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

函數自動得來，具體操作如下：

單元格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單元格向下填充。

完工產品入庫表輸入結果如圖12所示。

8

湖南商務職業技術學院畢業設計

圖12完工產品入庫表

3.4.3材料成本核算表設計

材料成本核算表是反映原材料訂貨價格的情況。由于炎陵娜妹子農業開發

有限公司采用月末加權平均法計算材料成本，所以在計算單位成本時，運用月

末加權平均法的公式計算材料成本：

月初結存材料的金額+本月入庫材料的金額

月初結存材料的數量+本月入庫材料的數量

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

中包括材料編碼，材料名稱，月初結存數量，月初結存金額，本月入庫數量，

本月增加的金額，發出數量，發出成本，庫存數量，月末結存金額，材料單位

成本。具體操作如下：

當輸入A列（材料編碼）的信息,B（材料名稱）列的信息會被系統自動匹

配出來。

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

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

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

下填充；

E列（本月入庫數量）和F列（本月增加的金額）以及G列（發出數量）可

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

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

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

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

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

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

庫存數量=（月初結存數量+本月入庫數量）-發出數量，在I3單元格中輸

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

9

湖南商務職業技術學院畢業設計

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

“=K3*I3”;

材料單位成本根據月末加權平均法的公式，在K3單元格輸入公式

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

圖13材料成本核算表

3.4.4庫存材料盤點表設計

庫存材料盤點表可以反映原材料的賬目和實物是否一致的情況。在材料成

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

材料編碼，材料名稱，單位，盤點數量以及備注。具體操作如下：

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

名稱）和C列（單位）的相關信息。而D列（盤點數量）根據盤點結果輸入數

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

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

相符，就會出現“賬實不符”的這一標志，并能夠在其后面解釋背后發生的原

因。

圖14庫存材料盤點表

4總結