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

目錄

1貴州榮盛商貿有限公司簡介...................................................................................1

2貴州榮盛商貿有限公司EXCEL進行進銷存管理的動機....................................1

3貴州榮盛商貿有限公司進銷存管理流程設計.......................................................2

3.1采購業務設計.................................................................................................2

3.1.1建立供應商信息表...............................................................................2

3.1.2建立采購業務表...................................................................................3

3.1.3建立預付貨款匯總表...........................................................................5

3.2銷售業務設計.................................................................................................5

3.2.1建立客戶信息表...................................................................................5

3.2.2建立銷售業務表...................................................................................6

3.2.3建立應收賬匯總表...............................................................................8

3.3庫存管理設計.................................................................................................9

3.3.1建立商品調出調入表...........................................................................9

3.4商品成本計算表設計...................................................................................10

3.4.1建立商品成本計算表.........................................................................10

4總結..........................................................................................................................12

參考資料.....................................................................................................................14

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

貴州榮盛商貿有限公司基于EXCEL進行進銷存管理

的方案設計

1貴州榮盛商貿有限公司簡介

貴州榮盛商貿有限公司成立于2017年04月13日，注冊地位于貴州省銅仁

市萬山區義烏城市場A4棟1層25，注冊資本為1000萬元，屬于商品貿易類企

業，公司擁有員工較少，法定代表人為劉兵榮持股比例90%，財務負責人為韋俊，

貴州榮盛商貿有限公司屬于四川省古藺郎酒廠有限公司貴州銅仁地區總代理商，

其代理產品主要為青花郎、紅花郎、郎牌特曲、小郎酒，同時也是貴州茅臺茅臺

酒股份有限公司的茅臺迎賓酒（紫款）經銷商，主要銷售產品有茅臺迎賓酒、茅

臺王子酒，貴州大曲，仁酒等，公司負責對外銷售與推廣其旗下產品，屬于商品

貿易公司，經營范圍包括包裝食品、散裝食品、煙、酒、日用品、洗護用品、工

藝品、化妝品、等商品的零售與批發，貴州榮盛商貿有限公司在貴州省擁有三家

店面，因當地白酒市場較飽和，競爭非常激烈，公司需不斷擴大銷路，幫助供應

商不斷提高市場占有率，提高利潤，同時也要做好公司的進銷存管理，資金才能

及時回籠，才能做到持續經營，創造未來。

2貴州榮盛商貿公司采用EXCEL進行進銷存管理的動機

截至2021年年底，我國小微企業達到了4800萬戶，較十年前增長了2.7倍，

隨著中小企業行業競爭越來越激烈，且受到疫情影響，從3.5年的平均壽命下滑

到2.6年，且還在持續下降，行業的競爭加劇了商業競爭，在此期間貴州榮盛商

貿公司也深受打擊，商品銷售困難，銷售貨款無法收回，庫存積壓，且貨物堆積

無法盤點，導致資金得不到及時的回籠，支付供應商商品款顯得十分困難，企業

當前的進銷存管理方式沒有給企業帶來良好的效果，現如今，對于剛剛起步不久

的貴州榮盛商貿有限公司來說購買進銷存管理軟件無疑給公司造成了資金浪費，

增加了公司的資金壓力，但在信息化的時代，企業不僅要依賴于信息化的支持，

更要做到對采購到銷售這企業經營過程實施全方位，高標準的實時監控，才能使

企業更好的發展。

企業進銷存管理是指企業管理過程中由采購到入庫再到銷售的動態管理過

程，對企業經營中從采購到銷售進行全程跟蹤管理，其中涉及到商品采購，銷售，

回收貨款，支付商品款等業務都可以通過excel技術準確詳盡的記錄下來，對比

購買進銷存軟件，節省了人力，物力，財力。

1

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

3貴州榮盛商貿有限公司進銷存管理流程設計

系統分成幾個相對獨立的模塊，整個系統采用模塊化結構設計。作為應用序

有較強的可操作性和擴展性。合理的數據流設計，在應用系統設計中，相對獨立

的模塊間以數據流相連接，使各模塊間的耦合性較低，方便系統運行，提高系統

安全性。

3.1采購業務設計

3.1.1.建立供應商信息表

第一步:打開EXCEL，點擊新建工作簿，將工作簿命名為“貴州榮盛商貿公

司進銷存管理系統”將sheet1重命名為“供應商信息表”，設置“供貨商名稱”，

“供貨商電話”，“商品編碼”，“商品單位”，“采購結算方式”等字段，如圖3-1

所示。

圖3-1創建供應商信息表步驟1

第二步:單元格命名，點擊A列，單擊“公式”菜單欄下的“名稱管理器”

按鈕，點擊“定義名稱”，在彈出的對話框名稱欄輸入“供應商名稱”，點擊確認，

完成名稱定義。運用同樣的方法，將B列命名為“供應商電話”將C,D,E,列命

名為“商品編碼”“商品名稱”，“單位”，將G列命名為“采購結算方式”，如圖3-2

所示。

圖3-2創建供應商信息表步驟2

2

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

3.1.2建立采購業務表

采購業務表主要用來記錄企業從供應商采購貨物的時間，接下來我們在表格

內設置“商品編碼”，“商品名稱”，“單位”，“采購數量”，“采購單價”，以及“應

付貨款”，“采購結算方式”，“未付貨款”等字段。

第一步:新增一個工作表，將該工作表命名為“商品采購業務表”，通過“公

式”菜單欄下“名稱管理器”功能將單元格命名，方便后期數據制作。如圖3-3

所示。

圖3-3建立采購業務表步驟1

第二步:設置數據有效性，為了使采購業務表與供應商信息表數據關聯，并

且減少工作過程中由于人為輸入造成信息的錯誤，遺漏等問題，將“供應商名稱”

“商品編碼”“采購結算方式”設置數據有效性。單擊“數據”菜單欄下“數據

驗證”按鈕，如圖3-4所示，彈框中來源這一欄中的“供應商名稱”來源于“供

應商信息表”中的A列，改為同樣的方法設置商品編碼和采購計算方式，如圖3-4

所示。

圖3-4建立采購業務表步驟二

第三步:設置公式，完成日期、摘要、商品名稱等字段的填寫后，引用VLOOKUP

函數業務日期，摘要，供應商名稱，商品編碼，商品名稱，采購數量，采購單價，

已付款，結算方式等信息需要手動錄入，其他項目需要設置公式，更方便計算。

3

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

在D2單元格內輸入函數:=VLOOKUP(C2,供應商信息表!“商品編碼”，“商品

名稱”，“單位”，2，0)。

在E2單元格內輸入函數:=VLOOKUP(C2,供應商信息表!“商品編碼”，“商品

名稱”，“單位”，3，0),然后點擊單元格將鼠標拖到單元格右下角待鼠標變成十

字架狀向下拖動即可根據錄入的商品編碼自動生成商品名稱和單位。

金額列:在I2單元格內輸入公式:=G2*F2,向下拖動則自動計算該商品本次采

購金額。

未付款項列:在L2單元格中輸入公式:=I2-J2,向下拖動則自動計算本次采購

未付貨款數額。整表如圖3-5所示。

圖3-5建立采購業務表步驟3

3.1.3建立預付貨款匯總表

因公司性質，根據供應商規定，代理商采用先付款后提貨的方式采購商品，

所以我們需要先設置預付貨款匯總表。

第一步:新建工作表，將該工作表命名為“預付貨款匯總表”，設置“供應商

名稱”，“供應商電話“，“應付貨款”，“預付貨款”，“應付賬款余額”，等字段分

別填列在A1,B1,C1,D1，E1,單元格中，如圖3-6所示。

圖3-6建立預付貨款匯總表步驟一

第二步:設置數據有效性，對供應商名稱項目設置數據有效性，來源選擇“供

應商信息表”工作表中的A列。

第三步:設置公式，引用SUMIF函數

4

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

在B2單元格內輸入函數:=SUMIF(商品采購業務表!$H$2:$H$22,A2,商品采購

業務表!$I$2:$I$22),向下拖動填充，可以得到應付給該供應商的貨款。

在C2單元格內輸入函數:=SUMIF(商品采購業務表!$H$2:$H$22,A2,商品采購

業務表!$J$2:$J$22),向下填充。

在D2單元格中輸入公式:=B2-C2。

整表如圖3-7所示。

圖3-7建立預付貨款匯總表步驟2

3.2銷售業務設計

3.2.1建立客戶信息表

第一步:在“貴州榮盛商貿有限公司進銷存管理”工作簿內新建一張工作表，

重命名為“客戶信息表”，和“供應商信息表”一樣，填寫好客戶名稱、客戶電

話、商品編碼、商品名稱、單位、銷售結算方式，如圖3-8所示。

圖3-8建立客戶信息表步驟一

第二步:單元格命名，選擇A列，單擊“公式”菜單欄下“名稱管理器”按

鈕，點擊“定義名稱”，在彈出的對話框中輸入“客戶名稱”點擊確認，完成名

5

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

稱定義。運用同樣的方法，將B列命名為“客戶電話”，C,D,E命名為“商品信

息”，G列命名為“商品結算方式”，如圖3-9所示。

圖3-9建立客戶信息表步驟2

3.2.2建立銷售業務表

銷售業務表主要用于記錄企業零售，批發商品等經濟業務，包括銷售時間，

客戶名稱，商品編碼，商品名稱，單位，銷售數量，銷售單價，應收貨款，實收

貨款，銷售結算方式，應收賬款余額。

第一步:新建一個表格，命名為“商品銷售業務表”，填寫好項目名稱，如圖

3-10所示。

圖3-10建立銷售業務表步驟一

第二步:設置數據有效性，為了使銷售業務表與客戶信息表數據關聯，

并且減少工作過程中由于人為輸入造成信息的錯誤，遺漏等問題，將“客戶

名稱”，“商品編碼”，“銷售結算方式”設置數據有效性，單擊“數據”菜單欄下

“數據有效性”按鈕，如3-11所示，彈框中來源這一欄中的“客戶名稱”來源

于“客戶信息表”中的A列，某些客戶屬于零散客戶，需手動輸入，設置“商品

編碼”的數據有效性時，來源選擇“客戶信息表”工作表中C列，設置“銷售結

算方式”的數據有效性時，來源選擇“客戶信息表”中的G列，如圖3-11所示。

6

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

圖3-11建立銷售業務表一步驟二

第三步:設置公式，引用VLOOKUP函數

業務日期，摘要，客戶名稱，商品編碼，商品名稱，銷售數量，銷售單價，

實收貨款，銷售結算方式等信息需要手動錄入，其他項目需要設置公式，更方便

計算。

在D2單元格內輸入函數:=VLOOKUP(C2,客戶信息表!“商品編碼”，“商品名

稱”，“單位”，2，0)

在E2單元格內輸入函數:)=VLOOKUP(C2,客戶信息表!“商品編碼”，“商品名

稱”，“單位”，3，0)然后點擊單元格將鼠標拖到單元格右下角待鼠標變成十字架

狀向下拖動即可根據錄入的商品編碼自動生成商品名稱和單位。

應收貨款列:在I2單元格內輸入公式:=G2*F2,向下拖動則自動計算該商品本

次銷售應收金額。

應收賬款余額列:在L2單元格中輸入公式:=I2-J2,向下拖動則自動計算本次

銷售應收貨款余數額。整表如圖3-12所示。

圖3-12建立銷售業務表步驟3

3.2.3建立應收賬款匯總表

公司屬于總代理性質，有些批發給商戶的商品形成了應收賬款，有些商戶可

能拖欠時間過長，需要建立一個應收賬款匯總表以便核算和催收貨款。

7

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

第一步:在工作簿下新建一個工作表，命名為“應收賬款匯總表”，并在該表

中輸入客戶名稱，應收貨款，已收貨款，應收賬款余額項目，如圖3-13所示。

圖3-13建立應收款賬款匯總表步驟1

第二步:對客戶名稱項目設置數據有效性。

第三步:設置公式，引用SUMIF函數

在B2單元格內輸入函數:=SUMIF(商品銷售業務表!$H$2:$H$16,A2,商品銷售

業務表!$I$2:$I$16),向下拖動填充，可以得到客戶欠公司的貨款。在C2單元格

內輸入函數:=SUMIF(商品銷售業務表!$C$2:$C$16,A2,商品采購業務表!$J$2:$J$

16),向下填充。在D2單元格中輸入公式:=B2-C2，整表如圖3-13所示。

圖3-14建立應收賬款匯總表步驟2

3.3庫存管理設計

3.3.1建立商品調出調入表

商品調出調入表只要用于平時商品出入庫的記錄，出庫主要包含采購，因商

品短缺從公司其他店鋪調入商品，出庫只主要包括商品出售，因公司其他店鋪商

品短缺調出商品。

第一步:打開“貴州榮盛商貿有限公司”工作簿，新建一個工作表，命名為“商

品調出調入表”在表中輸入業務日期，摘要，類型，商品名稱，數量，摘要字段

可輸入上年結轉、采購、銷售、調入、調出，類型字段可輸入出庫、入庫，采購

的數據可以從“商品采購業務表”中取得，銷售的數據可以從“商品銷售業務表”

中取得，如圖3-15所示。

8

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

圖3-15建立商品調出調入表步驟1

第二步:插入數據透視表，將光標放在任意一個單元格，點擊插入——>數據

透視透視表——>點擊放在現有工作表中，點擊確認，將商品名稱拖到行標簽，

類型放在列標簽，數量字段放在值，我們點擊設計——總計——僅對行啟用，再

將光標放在“出庫”字段上，點擊數據透視表分析——字段、項目和集——計算

項——名稱改成“庫存”——公式:=入庫-出庫——點擊“確認”，當“商品調出

調入表”發生變動時，點擊數據透視表分析菜單欄下“刷新”按鈕，即可對數據

透視表的數據進行更新，得到最新庫存數。如圖3-16所示。

9

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

圖3-16建立商品調出調入表步驟二

3.4商品成本計算表設計

3.4.1建立商品成本計算表

貴州榮盛商貿有限公司存貨的發出計價方法采用的是月末一次加權平均法，

所以我們成本計算表也采用月末一次加權平均法計算。

第一步:建立成本計算表。

打開“貴州榮盛商貿有限公司進銷存管理”工作簿，新建一個工作表，命名

為“商品成本計算表”，其中包含的項目有:商品編碼、商品名稱、單位、期初結

存數量、期初結存金額、采購數量、本期增加金額、銷售數量、銷售成本、期末

結存數量、期末結存金額、單位成本，表中A,B,C,D,E列需手動輸入，如圖3-17

所示。

圖3-17建立成本計算表步驟1

第二步:設置公式

在F2單元格中輸入公式:=SUMIFS(商品調出調入表!$E$2:$E$45,商品調出調

入表!$D$2:$D$45,B2,商品調出調入表!$B$2:$B$45,"采購")

10

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

在G2單元格輸入公式:=SUMIF(商品采購業務表!$D$2:$G$22,B2,商品采購業

務表!$I$2:$I$22)

在H2單元格輸入公式=SUMIFS(商品調出調入表!$E$2:$E$45,商品調出調入

表!$D$2:$D$45,B2,商品調出調入表!$B$2:$B$45,"銷售")。后面的商品均可以復

制第2行的公式得到。

第三步:計算成本

單位成本=（等于期初結存金額+本期增加金額）/（期初結存數量+采購數

量），在L2單元格輸入公式:=（E2+G2)/(D2+F2)。

銷售成本=單位成本*銷售數量，在I2單元格輸入公式:=L2*H2。

期末結存數量=期初結存數量+采購數量-銷售數量，在J2單元格內輸入公式:

=D2+F2-H2。

期末結存金額=期末結存數量*單位成本，在K2單元格內輸入公式:=L2*J2。

結果如圖