貴州榮盛商貿(mào)有限公司基于EXCEL進行進銷存管理的方案設(shè)計

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

目錄

1貴州榮盛商貿(mào)有限公司簡介...................................................................................1

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

3貴州榮盛商貿(mào)有限公司進銷存管理流程設(shè)計.......................................................2

3.1采購業(yè)務(wù)設(shè)計.................................................................................................2

3.1.1建立供應(yīng)商信息表...............................................................................2

3.1.2建立采購業(yè)務(wù)表...................................................................................3

3.1.3建立預(yù)付貨款匯總表...........................................................................5

3.2銷售業(yè)務(wù)設(shè)計.................................................................................................5

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

3.2.2建立銷售業(yè)務(wù)表...................................................................................6

3.2.3建立應(yīng)收賬匯總表...............................................................................8

3.3庫存管理設(shè)計.................................................................................................9

3.3.1建立商品調(diào)出調(diào)入表...........................................................................9

3.4商品成本計算表設(shè)計...................................................................................10

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

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

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

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

貴州榮盛商貿(mào)有限公司基于EXCEL進行進銷存管理

的方案設(shè)計

1貴州榮盛商貿(mào)有限公司簡介

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

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

業(yè)，公司擁有員工較少，法定代表人為劉兵榮持股比例90%，財務(wù)負責(zé)人為韋俊，

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

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

酒股份有限公司的茅臺迎賓酒（紫款）經(jīng)銷商，主要銷售產(chǎn)品有茅臺迎賓酒、茅

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

貿(mào)易公司，經(jīng)營范圍包括包裝食品、散裝食品、煙、酒、日用品、洗護用品、工

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

店面，因當?shù)匕拙剖袌鲚^飽和，競爭非常激烈，公司需不斷擴大銷路，幫助供應(yīng)

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

及時回籠，才能做到持續(xù)經(jīng)營，創(chuàng)造未來。

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

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

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

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

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

無法盤點，導(dǎo)致資金得不到及時的回籠，支付供應(yīng)商商品款顯得十分困難，企業(yè)

當前的進銷存管理方式?jīng)]有給企業(yè)帶來良好的效果，現(xiàn)如今，對于剛剛起步不久

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

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

更要做到對采購到銷售這企業(yè)經(jīng)營過程實施全方位，高標準的實時監(jiān)控，才能使

企業(yè)更好的發(fā)展。

企業(yè)進銷存管理是指企業(yè)管理過程中由采購到入庫再到銷售的動態(tài)管理過

程，對企業(yè)經(jīng)營中從采購到銷售進行全程跟蹤管理，其中涉及到商品采購，銷售，

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

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

1

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

3貴州榮盛商貿(mào)有限公司進銷存管理流程設(shè)計

系統(tǒng)分成幾個相對獨立的模塊，整個系統(tǒng)采用模塊化結(jié)構(gòu)設(shè)計。作為應(yīng)用序

有較強的可操作性和擴展性。合理的數(shù)據(jù)流設(shè)計，在應(yīng)用系統(tǒng)設(shè)計中，相對獨立

的模塊間以數(shù)據(jù)流相連接，使各模塊間的耦合性較低，方便系統(tǒng)運行，提高系統(tǒng)

安全性。

3.1采購業(yè)務(wù)設(shè)計

3.1.1.建立供應(yīng)商信息表

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

司進銷存管理系統(tǒng)”將sheet1重命名為“供應(yīng)商信息表”，設(shè)置“供貨商名稱”，

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

所示。

圖3-1創(chuàng)建供應(yīng)商信息表步驟1

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

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

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

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

所示。

圖3-2創(chuàng)建供應(yīng)商信息表步驟2

2

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

3.1.2建立采購業(yè)務(wù)表

采購業(yè)務(wù)表主要用來記錄企業(yè)從供應(yīng)商采購貨物的時間，接下來我們在表格

內(nèi)設(shè)置“商品編碼”，“商品名稱”，“單位”，“采購數(shù)量”，“采購單價”，以及“應(yīng)

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

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

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

所示。

圖3-3建立采購業(yè)務(wù)表步驟1

第二步:設(shè)置數(shù)據(jù)有效性，為了使采購業(yè)務(wù)表與供應(yīng)商信息表數(shù)據(jù)關(guān)聯(lián)，并

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

“商品編碼”“采購結(jié)算方式”設(shè)置數(shù)據(jù)有效性。單擊“數(shù)據(jù)”菜單欄下“數(shù)據(jù)

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

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

所示。

圖3-4建立采購業(yè)務(wù)表步驟二

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

函數(shù)業(yè)務(wù)日期，摘要，供應(yīng)商名稱，商品編碼，商品名稱，采購數(shù)量，采購單價，

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

3

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

在D2單元格內(nèi)輸入函數(shù):=VLOOKUP(C2,供應(yīng)商信息表!“商品編碼”，“商品

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

在E2單元格內(nèi)輸入函數(shù):=VLOOKUP(C2,供應(yīng)商信息表!“商品編碼”，“商品

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

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

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

購金額。

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

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

圖3-5建立采購業(yè)務(wù)表步驟3

3.1.3建立預(yù)付貨款匯總表

因公司性質(zhì)，根據(jù)供應(yīng)商規(guī)定，代理商采用先付款后提貨的方式采購商品，

所以我們需要先設(shè)置預(yù)付貨款匯總表。

第一步:新建工作表，將該工作表命名為“預(yù)付貨款匯總表”，設(shè)置“供應(yīng)商

名稱”，“供應(yīng)商電話“，“應(yīng)付貨款”，“預(yù)付貨款”，“應(yīng)付賬款余額”，等字段分

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

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

第二步:設(shè)置數(shù)據(jù)有效性，對供應(yīng)商名稱項目設(shè)置數(shù)據(jù)有效性，來源選擇“供

應(yīng)商信息表”工作表中的A列。

第三步:設(shè)置公式，引用SUMIF函數(shù)

4

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

在B2單元格內(nèi)輸入函數(shù):=SUMIF(商品采購業(yè)務(wù)表!$H$2:$H$22,A2,商品采購

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

在C2單元格內(nèi)輸入函數(shù):=SUMIF(商品采購業(yè)務(wù)表!$H$2:$H$22,A2,商品采購

業(yè)務(wù)表!$J$2:$J$22),向下填充。

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

整表如圖3-7所示。

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

3.2銷售業(yè)務(wù)設(shè)計

3.2.1建立客戶信息表

第一步:在“貴州榮盛商貿(mào)有限公司進銷存管理”工作簿內(nèi)新建一張工作表，

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

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

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

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

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

5

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

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

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

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

3.2.2建立銷售業(yè)務(wù)表

銷售業(yè)務(wù)表主要用于記錄企業(yè)零售，批發(fā)商品等經(jīng)濟業(yè)務(wù)，包括銷售時間，

客戶名稱，商品編碼，商品名稱，單位，銷售數(shù)量，銷售單價，應(yīng)收貨款，實收

貨款，銷售結(jié)算方式，應(yīng)收賬款余額。

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

3-10所示。

圖3-10建立銷售業(yè)務(wù)表步驟一

第二步:設(shè)置數(shù)據(jù)有效性，為了使銷售業(yè)務(wù)表與客戶信息表數(shù)據(jù)關(guān)聯(lián)，

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

名稱”，“商品編碼”，“銷售結(jié)算方式”設(shè)置數(shù)據(jù)有效性，單擊“數(shù)據(jù)”菜單欄下

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

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

編碼”的數(shù)據(jù)有效性時，來源選擇“客戶信息表”工作表中C列，設(shè)置“銷售結(jié)

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

6

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

圖3-11建立銷售業(yè)務(wù)表一步驟二

第三步:設(shè)置公式，引用VLOOKUP函數(shù)

業(yè)務(wù)日期，摘要，客戶名稱，商品編碼，商品名稱，銷售數(shù)量，銷售單價，

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

計算。

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

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

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

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

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

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

次銷售應(yīng)收金額。

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

銷售應(yīng)收貨款余數(shù)額。整表如圖3-12所示。

圖3-12建立銷售業(yè)務(wù)表步驟3

3.2.3建立應(yīng)收賬款匯總表

公司屬于總代理性質(zhì)，有些批發(fā)給商戶的商品形成了應(yīng)收賬款，有些商戶可

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

7

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

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

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

圖3-13建立應(yīng)收款賬款匯總表步驟1

第二步:對客戶名稱項目設(shè)置數(shù)據(jù)有效性。

第三步:設(shè)置公式，引用SUMIF函數(shù)

在B2單元格內(nèi)輸入函數(shù):=SUMIF(商品銷售業(yè)務(wù)表!$H$2:$H$16,A2,商品銷售

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

內(nèi)輸入函數(shù):=SUMIF(商品銷售業(yè)務(wù)表!$C$2:$C$16,A2,商品采購業(yè)務(wù)表!$J$2:$J$

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

圖3-14建立應(yīng)收賬款匯總表步驟2

3.3庫存管理設(shè)計

3.3.1建立商品調(diào)出調(diào)入表

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

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

品短缺調(diào)出商品。

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

品調(diào)出調(diào)入表”在表中輸入業(yè)務(wù)日期，摘要，類型，商品名稱，數(shù)量，摘要字段

可輸入上年結(jié)轉(zhuǎn)、采購、銷售、調(diào)入、調(diào)出，類型字段可輸入出庫、入庫，采購

的數(shù)據(jù)可以從“商品采購業(yè)務(wù)表”中取得，銷售的數(shù)據(jù)可以從“商品銷售業(yè)務(wù)表”

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

8

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

圖3-15建立商品調(diào)出調(diào)入表步驟1

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

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

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

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

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

調(diào)入表”發(fā)生變動時，點擊數(shù)據(jù)透視表分析菜單欄下“刷新”按鈕，即可對數(shù)據(jù)

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

9

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

圖3-16建立商品調(diào)出調(diào)入表步驟二

3.4商品成本計算表設(shè)計

3.4.1建立商品成本計算表

貴州榮盛商貿(mào)有限公司存貨的發(fā)出計價方法采用的是月末一次加權(quán)平均法，

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

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

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

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

存數(shù)量、期初結(jié)存金額、采購數(shù)量、本期增加金額、銷售數(shù)量、銷售成本、期末

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

所示。

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

第二步:設(shè)置公式

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

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

10

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

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

務(wù)表!$I$2:$I$22)

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

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

制第2行的公式得到。

第三步:計算成本

單位成本=（等于期初結(jié)存金額+本期增加金額）/（期初結(jié)存數(shù)量+采購數(shù)

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

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

期末結(jié)存數(shù)量=期初結(jié)存數(shù)量+采購數(shù)量-銷售數(shù)量，在J2單元格內(nèi)輸入公式:

=D2+F2-H2。

期末結(jié)存金額=期末結(jié)存數(shù)量*單位成本，在K2單元格內(nèi)輸入公式:=L2*J2。

結(jié)果如圖