Excel電商數(shù)據(jù)分析與應(yīng)用 課件 10.2.1庫(kù)存績(jī)效分析 10.2.2補(bǔ)貨模型

產(chǎn)品運(yùn)營(yíng)分析10.2.1庫(kù)存績(jī)效分析&10.2.2補(bǔ)貨模型1庫(kù)存績(jī)效分析2CONTENTS補(bǔ)貨模型庫(kù)存績(jī)效分析PARTONE庫(kù)存績(jī)效分析庫(kù)存分析是對(duì)企業(yè)的庫(kù)存績(jī)效進(jìn)行分析的過(guò)程，包括庫(kù)存預(yù)警和補(bǔ)貨數(shù)量的分析，幫助企業(yè)提高倉(cāng)庫(kù)管理能力，提高庫(kù)存績(jī)效，降低不良庫(kù)存。庫(kù)存績(jī)效分析主要是監(jiān)控庫(kù)存量、庫(kù)存周轉(zhuǎn)率、售罄率和動(dòng)銷率，是庫(kù)存分析的基本項(xiàng)。存貨周轉(zhuǎn)速度越快，存貨占用水平越低，流動(dòng)性越強(qiáng)，存貨轉(zhuǎn)化為現(xiàn)金或應(yīng)收賬款的速度就越快，這樣會(huì)增強(qiáng)企業(yè)的短期償債能力及獲利能力。1．庫(kù)存量2．庫(kù)存周轉(zhuǎn)率庫(kù)存量是指?jìng)}庫(kù)中存放貨物的數(shù)量，一般庫(kù)存量要根據(jù)SKU（最小庫(kù)存單位）進(jìn)行統(tǒng)計(jì)，商品在正常銷售的過(guò)程的庫(kù)存量太大或?yàn)榱愣疾皇呛芎玫默F(xiàn)象。庫(kù)存周轉(zhuǎn)率是在某一時(shí)間段內(nèi)庫(kù)存貨物周轉(zhuǎn)的次數(shù)，是反映庫(kù)存周轉(zhuǎn)快慢程度的指標(biāo)。周轉(zhuǎn)率越大表明銷售情況越好。在商品保質(zhì)期及資金允許的條件下，可以適當(dāng)增加其庫(kù)存控制目標(biāo)天數(shù)，以保證合理的庫(kù)存。反之，則可以適當(dāng)減少其庫(kù)存控制目標(biāo)天數(shù)。庫(kù)存周轉(zhuǎn)率計(jì)算公式為：庫(kù)存績(jī)效分析減少存貨周轉(zhuǎn)天數(shù)可以提高存貨周轉(zhuǎn)率，從實(shí)際運(yùn)營(yíng)來(lái)分析，需要減少存貨在庫(kù)時(shí)間。例10-8：某企業(yè)在2019年Q1的期初庫(kù)存量為32149，期末庫(kù)存量為23910，期間銷售量為8374，請(qǐng)計(jì)算該企業(yè)在2019年Q1的庫(kù)存周轉(zhuǎn)天數(shù)和庫(kù)存周轉(zhuǎn)率。解：2019年Q1可以認(rèn)為是90天，代入公式：售罄率=2.4萬(wàn)÷3萬(wàn)×100%=80%庫(kù)存績(jī)效分析3．售罄率售罄率是指一定時(shí)間段某種商品的銷售占總進(jìn)貨量的比例，售罄率是衡量商品收回銷售成本和費(fèi)用的一個(gè)考核指標(biāo)，便于確定商品銷售到何種程度可以進(jìn)行折扣銷售清倉(cāng)處理的一個(gè)合理的標(biāo)準(zhǔn)。計(jì)算公式為：售罄率=實(shí)際銷售成本÷總進(jìn)貨成本×100%售罄率=實(shí)際累積銷售數(shù)量÷總進(jìn)貨數(shù)量×100%或者售謦率計(jì)算期間通常為一周、一個(gè)月或一個(gè)季度。例10-9：某企業(yè)的某商品在2019年Q1的總進(jìn)貨量是3萬(wàn)，實(shí)際銷售量是2.4萬(wàn)，計(jì)算售罄率。解：售罄率大于80%小于等于100%的為備貨命中。庫(kù)存績(jī)效分析4．動(dòng)銷率動(dòng)銷率是反映階段期間企業(yè)銷售商品的占比，反映了企業(yè)的商品運(yùn)營(yíng)能力，是考驗(yàn)采銷部門和運(yùn)營(yíng)部門的核心指標(biāo)之一。商品動(dòng)銷率計(jì)算公式為：商品動(dòng)銷率有別于電商的網(wǎng)店動(dòng)銷率，電商的網(wǎng)店動(dòng)銷率只考慮在線銷售的品種數(shù)，不考慮倉(cāng)庫(kù)的總品種數(shù)，往往倉(cāng)庫(kù)的總品種數(shù)要大于在線銷售的品種數(shù)。電商的網(wǎng)店動(dòng)銷率為：例10-10：某企業(yè)在2019年1月的倉(cāng)庫(kù)總品種數(shù)為131個(gè)，其中有98個(gè)品種在期間產(chǎn)生了銷量，計(jì)算動(dòng)銷率。解：補(bǔ)貨模型PARTTWO補(bǔ)貨模型哪個(gè)SKU需要補(bǔ)貨？需要補(bǔ)多少貨？這是運(yùn)營(yíng)或者采購(gòu)經(jīng)常遇到的場(chǎng)景。本小節(jié)介紹該場(chǎng)景的業(yè)務(wù)建模，提高采購(gòu)的工作效率和補(bǔ)貨數(shù)量命中率（實(shí)際售罄誤差在15%內(nèi)為命中）。1．補(bǔ)貨業(yè)務(wù)邏輯補(bǔ)貨要考慮現(xiàn)有庫(kù)存量、未來(lái)可能產(chǎn)生的銷量、安全庫(kù)存量和供應(yīng)鏈的補(bǔ)貨周期，在買家下單時(shí)現(xiàn)有庫(kù)存量能夠支撐等待補(bǔ)貨的時(shí)間。比如補(bǔ)貨周期時(shí)14天，要提前預(yù)留超過(guò)14天的庫(kù)存，在剩余14天庫(kù)存時(shí)就要給工廠下單。補(bǔ)貨模型2．補(bǔ)貨數(shù)量測(cè)算例10-11：創(chuàng)建模型計(jì)算需要補(bǔ)貨的商品和數(shù)量。數(shù)據(jù)采集自商家后臺(tái)的訂單報(bào)表和寶貝報(bào)表。解：在后臺(tái)生成訂單報(bào)表和寶貝報(bào)表，如圖10-81所示。生成報(bào)表后，分別下載訂單報(bào)表和寶貝報(bào)表，報(bào)表下載界面如圖10-82所示。補(bǔ)貨模型另外建立一張庫(kù)存統(tǒng)計(jì)報(bào)表，記錄現(xiàn)時(shí)庫(kù)存量和補(bǔ)貨周期如圖10-83所示，庫(kù)存統(tǒng)計(jì)表的“商家編碼”對(duì)應(yīng)寶貝報(bào)表的“商家編碼”，補(bǔ)貨周期是指補(bǔ)貨所需的天數(shù)。補(bǔ)貨模型如圖10-84所示，打開Excel，在【PowerPivot】選項(xiàng)卡中單擊【管理】選項(xiàng)，轉(zhuǎn)到PowerPivot窗口，進(jìn)入PowerPivot管理界面。如圖10-85所示，在PowerPivot窗口的【主頁(yè)】選項(xiàng)卡中單擊【從其他源】選項(xiàng)，進(jìn)入“表導(dǎo)入向?qū)А睂?duì)話框，選擇【Excel文件】，然后單擊【下一步】按鈕。圖10-84圖10-85補(bǔ)貨模型在圖10-86所示的對(duì)話框中，先單擊【瀏覽】按鈕選擇好文件路徑，再勾選復(fù)選框【使用第一行作為列標(biāo)題】，單擊【下一步】按鈕。注：文中已經(jīng)把原來(lái)下載好的表格從CSV格式改為EXCEL文本格式。如圖10-87所示，在寶貝報(bào)表中新建匯總的度量值，這個(gè)度量值要用于后面的運(yùn)算。補(bǔ)貨模型如圖10-88所示，在PowerPivot的【主頁(yè)】選項(xiàng)卡中單擊【關(guān)系圖視圖】建模，將“訂單報(bào)表”的“訂單編號(hào)”字段連接到“寶貝報(bào)表”的“訂單編號(hào)”，“寶貝報(bào)表”的“商家編碼”連接到“庫(kù)存統(tǒng)計(jì)表”的“商家編碼”。

回到庫(kù)存報(bào)表，鍵入公式“=SUMX(FILTER('寶貝報(bào)表','寶貝報(bào)表'[商家編碼]='庫(kù)存統(tǒng)計(jì)表'[商家編碼]&&DATEDIFF(RELATED('訂單報(bào)表'[訂單付款時(shí)間]),Date(2022,1,30),day)<='庫(kù)存統(tǒng)計(jì)表'[補(bǔ)貨周期]),[購(gòu)買數(shù)量的總和])”，求出的近N天銷量是基于補(bǔ)貨周期的天數(shù)計(jì)算的銷量，結(jié)果如圖10-89所示。圖10-88圖10-89SUMX函數(shù)說(shuō)明如下。函數(shù)功能：返回為表中每一行計(jì)算的表達(dá)式之和。函數(shù)語(yǔ)法：SUMX（<表名>,<表達(dá)式>）DATEDIFF函數(shù)說(shuō)明如下。函數(shù)功能：返回兩個(gè)日期的單位間隔（可指定單位）。函數(shù)語(yǔ)法：DATEDIFF（起始時(shí)間，結(jié)束時(shí)間，時(shí)間單位）。RELATED函數(shù)說(shuō)明如下。RELATED函數(shù)說(shuō)明如下。函數(shù)功能：返回與當(dāng)前表相關(guān)的列的值，應(yīng)用在表間已經(jīng)創(chuàng)建的關(guān)系，從關(guān)系表中查找相關(guān)數(shù)據(jù)。補(bǔ)貨模型圖10-90圖10-91鍵入公式“=IF(CEILING(DIVIDE([庫(kù)存],[近N天銷量]),1)-1<0,0,CEILING(DIVIDE([庫(kù)存],[近N天銷量]),1)-1)*[補(bǔ)貨周期]”，計(jì)算出現(xiàn)有的庫(kù)存可以銷售幾天，在多少天后需要補(bǔ)貨，結(jié)果如圖10-90所示。鍵入公式“=IF([多少天后要補(bǔ)貨](méi)=0,[近N天銷量]-[庫(kù)存],0)”，計(jì)算出最小補(bǔ)貨量，最小補(bǔ)貨量是指可以靈活調(diào)配的補(bǔ)貨量，可能并不需要完整的補(bǔ)貨周期，結(jié)果如圖10-91所示。CEILING函數(shù)說(shuō)明如下。函數(shù)功能：將數(shù)字向上舍入到最接近的整數(shù)，或者基數(shù)的最接近倍數(shù)。函數(shù)語(yǔ)法：CEILING（數(shù)值，基數(shù)）。DIVIDE函數(shù)說(shuō)明如下。函數(shù)功能：處理除數(shù)為零或者為空的情況。函數(shù)語(yǔ)法：DIVIDE（分子，分母）。補(bǔ)貨模型圖10-92圖10-93鍵入公式“=IF('庫(kù)存統(tǒng)計(jì)表'[多少天后要補(bǔ)貨](méi)=0,'庫(kù)存報(bào)表'[最小補(bǔ)貨量]+'庫(kù)存統(tǒng)計(jì)'[近N天銷量],0)”，計(jì)算庫(kù)存一周期的備貨量，至少保留一個(gè)備貨周期的庫(kù)存，結(jié)果如圖10-92所示。如圖10-93所示，在PowerPivot【主頁(yè)】選項(xiàng)卡中單擊【數(shù)據(jù)透視表】選項(xiàng)，在“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框中勾選【新工作表】，單擊【確定】按鈕，在數(shù)據(jù)透視表上單擊鼠標(biāo)右鍵，在菜單中選擇【顯示字段列表】。補(bǔ)貨模型圖10-94圖10-95如圖10-94所示，將“庫(kù)存統(tǒng)計(jì)表”中的字段設(shè)置在相應(yīng)的行和列。如圖10-95所示，一張完整的庫(kù)存監(jiān)