商務數(shù)據(jù)分析與應用 課件 項目八 商品庫存分析

商務數(shù)據(jù)分析與應用重慶大學出版社尹愛飛，杜轉萍主編本書目錄項目一商務數(shù)據(jù)分析基礎項目二認識數(shù)據(jù)表項目三

商務數(shù)據(jù)錄入與整理項目四

商務數(shù)據(jù)分析工具項目五

商務數(shù)據(jù)可視化項目六商品銷售情況統(tǒng)計與分析項目七商品銷售費用分析項目八

商品庫存分析項目九

銷售市場預測分析項目八

商品庫存分析賣家對于商品庫存的分析有助于正確判斷銷售商品的供給能力，同時也可以判斷商品銷售的暢銷與滯銷狀況，及時調整商品的進貨種類，處理積壓的庫存商品，補給缺貨的商品，保證線上線下商品銷售的順利進行。請根據(jù)店鋪“商品庫存表”相關數(shù)據(jù)計算庫存周轉率、存銷比、動銷率等指標，判斷庫存商品的結構與供給狀況，同時制定庫存商品查詢表，以便快速查詢庫存商品的基本信息。目錄1庫存指標分析商品庫存數(shù)據(jù)分析28.1.1單一庫存商品信息查詢8.1.2商品庫存數(shù)量統(tǒng)計分析STEP1：打開素材文件/項目8/單一商品查詢表，在表格空白處編制單一商品庫存信息查詢表的框架，包含“產品名稱”、“采購金額”、“廠商”、“保質期”、“成本單價”等需要查詢和顯示的庫存商品信息，如圖8-1-1所示。8.1.1單一庫存商品信息查詢STEP2：進行數(shù)據(jù)有效性的設置。選中“產品名稱”下面空白單元格A24，點擊“數(shù)據(jù)”選項卡，選擇“數(shù)據(jù)驗證”，如圖8-1-2所示。在彈出的對話框里面設置驗證條件。點擊“允許”下拉菜單，選擇“序列”，點擊“來源”處箭頭，選中“A6：A20”區(qū)域范圍，即選中產品名稱一列，如圖8-1-3所示。點擊A24單元格，產品名稱全部已經列在下拉菜單中。8.1.1單一庫存商品信息查詢STEP3：進行相關信息的匹配。在A24單元格中任選一種產品名稱如“蜜桃汁飲料”，在B24單元格中輸入“=VLOOKUP”，依次輸入函數(shù)的各個部分，如圖8-1-4所示。函數(shù)語法第一部分中“A24”為查找的值；函數(shù)第二部分語法為查找的區(qū)域范圍“A2：J20”，查找的區(qū)域范圍首列應為包含“A24”產品名稱所在的列數(shù)；函數(shù)第三部分為匹配的列數(shù)。由于B24單元格對應的商品信息為“成本單價”，而“成本單價”位于選中區(qū)域的第7列，因此函數(shù)語法第三部分為“7”；函數(shù)第四部分為精確匹配，即“FALSE”。按“Enter”鍵，則單元格“B24”顯示結果為“40.5”元。8.1.1單一庫存商品信息查詢STEP4：進行公式復制。選中B24單元格，將公式中的前兩部分語法鎖定。然后按“Enter”回車鍵，如圖8-1-5所示。選中B24單元格向右拖動鼠標，進行公式的初始復制，并依次修改復制的函數(shù)中匹配的列數(shù)，結果如圖8-1-6所示。8.1.1單一庫存商品信息查詢STEP5：進行信息查詢。點擊A24單元格，任選一類產品，當產品名稱變化時，表中的信息也進行了相應的變化，單一商品查詢表就制作完成了，結果如圖8-1-7所示。8.1.1單一庫存商品信息查詢STEP1：打開素材文件/項目8/“庫存數(shù)據(jù)分析”.xlsx/庫存數(shù)據(jù),點擊“數(shù)據(jù)”選項卡，選擇“分類匯總”功能。在彈出的對話框中分類字段選擇“產品名稱”，匯總方式選擇“求和”，匯總項選擇“期末庫存數(shù)量”，如圖8-1-8所示。分類匯總的結果如圖8-1-9所示。8.1.2商品庫存數(shù)量統(tǒng)計分析STEP2：打開素材文件/項目8/“庫存數(shù)據(jù)分析”.xlsx/庫存狀態(tài)，根據(jù)分類匯總的結果統(tǒng)計各個產品系列的期末庫存數(shù)量，與標準庫存量進行比較判斷各產品系列的庫存狀態(tài)。在H8單元格中輸入IF公式：

=IF(G8>F8,"庫存充足","庫存不足"),按Enter鍵結束。選中H8單元格，按住鼠標左鍵向下拖動，進行公式的復制。每種產品系列的庫存狀態(tài)則顯示在單元格中，如圖8-1-10所示。8.1.2商品庫存數(shù)量統(tǒng)計分析STEP3：選中E7：G11表格區(qū)域，插入柱形圖，如圖8-1-11所示。從圖中可知，飲料產品系列的庫存嚴重不足，要馬上進行補貨；自熱火鍋產品系列庫存相對充足；火鍋調料和腌制品產品系列的庫存量接近標準庫存量，要注意及時補貨。8.1.2商品庫存數(shù)量統(tǒng)計分析目錄1庫存指標分析商品庫存數(shù)據(jù)分析2庫存周轉率是反映企業(yè)庫存周轉快慢程度的一個指標，指的是庫存在一定周期內周轉的次數(shù)。庫存周轉率越高，說明存貨的周轉速度越快，從成本投入到商品銷售再到資金回流的周期越短，銷售情況越好。庫存周轉率是分析庫存非常重要的一個指標，庫存周轉率提高，庫存周轉加快，庫存占用資金減少，企業(yè)現(xiàn)金流提高，資金流通速度加快；同時庫存持有成本下降，企業(yè)利潤得到提升，因此投入資金一定的情況下，庫存周轉率越高，企業(yè)的投資回報率越高。同時庫存周轉率是一個相對系數(shù)，可以和外部企業(yè)進行橫向對比，從而了解自己企業(yè)庫存周轉的合理性。

庫存周轉率=銷貨成本/平均存貨余額8.2.1庫存周轉率與周轉天數(shù)庫存周轉天數(shù)是企業(yè)從取得存貨開始，直到消耗、銷售為止所經歷的天數(shù)。庫存周轉天數(shù)=周期天數(shù)/庫存周轉率=（周期天數(shù)*平均存貨余額）/銷貨成本8.2.1庫存周轉率與周轉天數(shù)STEP1：打開素材文件/項目8/庫存周轉率與周轉天數(shù).xlsx，計算期末庫存數(shù)量，期末庫存數(shù)量=期初庫存數(shù)量+入庫數(shù)量-出庫數(shù)量。在H3單元格輸入公式“=E3+F3-G3”,并按回車鍵。選中H3單元格，當光標變?yōu)楹谑謺r向下拖動鼠標進行公式復制，計算出不同商品的期末庫存數(shù)量，結果如圖8-2-1所示。8.2.1庫存周轉率與周轉天數(shù)STEP2：計算平均庫存余額，平均庫存余額=（期初庫存余額+期末庫存余額）/2。期初庫存余額=期初庫存數(shù)量*成本單價，期末庫存余額=期末庫存數(shù)量*成本單價，因此，在I3單元格中輸入公式“=(E3*D3+H3*D3)/2”，并按回車鍵。選中I3單元格，當光標變?yōu)楹谑謺r向下拖動鼠標進行公式復制，計算出不同商品的平均庫存余額，結果如圖8-2-2所示。8.2.1庫存周轉率與周轉天數(shù)STEP3：計算銷貨成本，銷貨成本=銷貨數(shù)量*成本單價。這里的銷貨數(shù)量等于出庫數(shù)量。在J3單元格中輸入公式“=G3*D3”，并按回車鍵。選中J3單元格，當光標變?yōu)楹谑謺r向下拖動鼠標進行公式復制，計算出不同商品的銷貨成本，結果如圖8-2-3所示。8.2.1庫存周轉率與周轉天數(shù)STEP4：計算庫存周轉率，庫存周轉率=銷貨成本/平均庫存余額。在K3單元格中輸入公式“=J3/I3”，并按回車鍵。選中K3單元格，當光標變?yōu)楹谑謺r向下拖動鼠標進行公式復制，計算出不同商品的庫存周轉率，結果如圖8-2-4所示。8.2.1庫存周轉率與周轉天數(shù)STEP5：計算庫存周轉天數(shù)，庫存周轉天數(shù)=周期天數(shù)/庫存周轉率。在L3單元格中輸入公式“=30/K3”，并按回車鍵。選中L3單元格，當光標變?yōu)楹谑謺r向下拖動鼠標進行公式復制，計算出不同商品的庫存周轉天數(shù)，結果如圖8-2-5所示。8.2.1庫存周轉率與周轉天數(shù)存銷比（庫銷比）是指在一個周期內，商品平均庫存或本周期期末庫存與周期內總銷售的比值，是用來反映商品即時庫存狀況的相對數(shù)。而更為精確的法則是使用日均庫存和日均銷售的數(shù)據(jù)來計算，從而反映當前的庫存銷售比例。存銷比=期末庫存金額（or數(shù)量）/周期內商品銷售金額（or數(shù)量）公式中的周期可以是周、月、季度等等。存銷比過高，庫存資金占用過大，存銷比過低，則容易備貨不足，損失銷售機會。8.2.2存銷比與動銷率動銷率是指銷售的商品種數(shù)與倉庫所有商品種數(shù)的比值。動銷率=（動銷品種數(shù)/倉庫總品種數(shù)）*100%一般情況下，動銷率越高，企業(yè)商品周轉就越快；動銷率越低則代表周轉速度越慢。動銷率能夠作為商品經營深度與寬度的參考，動銷率低的商品企業(yè)則不需要花費大量時間和精力去經營。8.2.2存銷比與動銷率STEP1：第一種計算方式：存銷比=期末庫存數(shù)量/周期內商品銷售數(shù)量打開“素材文件/項目8/存銷比與動銷率.xlsx，在L3單元格中輸入公式“=K3/J3”，然后按Enter鍵，結果顯示為1.8，如圖8-2-6所示。選中L3單元格，按下鼠標左鍵拖動公式至L4:L20區(qū)域，通過公式的復制計算每種產品的存銷比，結果如圖8-2-7所示。8.2.2存銷比與動銷率STEP2：第二種計算方式：存銷比=期末庫存金額/周期內商品銷售金額打開“素材文件/項目8/庫存數(shù)據(jù)分析.xlsx，在M3單元格中輸入公式“=(K3*G3)/(J3*E3)”，然后按Enter鍵，如圖8-2-8所示。選中M3單元格，按下鼠標左鍵拖動公式至M4:M20區(qū)域，通過公式的復制計算每種產品的存銷比，結果如圖8-2-9所示。其中，K3*G3為期末庫存金額的計算，等于期末庫存數(shù)量與產品成本單價的乘積；J3*E3為周期內商品銷售金額的計算，等于出庫數(shù)量與銷售單價的乘積。在此，進行了出庫數(shù)量等于銷售數(shù)量的假設。8.2.2存銷比與動銷率STEP1：首先，點擊“開始”選項卡下面的“條件格式”，在下拉菜單中選擇“突出顯示單元格規(guī)則”中的“等于”選項。設置值為0，顏色可選擇默認顏色或者自行進行顏色的設置，如圖8-2-10所示。點擊“確定”，庫存數(shù)據(jù)分析表中出庫數(shù)量為0的對應單元格則被標記出來，結果如圖8-2-11所示。