財務人員實戰(zhàn)Excel之11--------成本分析 (未完，接下章)
第1節(jié)、采購成本分析表
第2節(jié)、采購成本變動分析
第3節(jié)、材料成本匯總表
第4節(jié)、企業(yè)成本|分析表|分解表
第11章 Excel 成本分析
第1節(jié)、采購成本分析表
材料的成本是生產成本的重要組成部分，而材料的成本除了價格因素外，還有一項很重要的因素，就是采購成本。采購成本通常由兩項組成，一是采購環(huán)節(jié)發(fā)生的費用；二是材料存儲時的發(fā)生的費用。這是兩個互相制約的因素，每批采購量大，采購次數(shù)少，可以減少年采購成本，但是存儲費用的增加；反之亦然。
因此確定采購量和存儲量之間的關系也是一項很重要的工作，通過“采購成本分析”可以幫助企業(yè)設置科學合理的采購量和采購次數(shù)，從而為降低企業(yè)采購成本提供可靠依據(jù)。
采購成本分析表效果圖
我們先創(chuàng)建采購成本和存儲成本在不同批次下的數(shù)據(jù)表，再利用公式計算最小成本、采購批次和采購量。然后添加年采購量、年采購成本和單位存儲成本滾動條，最后制作存儲成本和采購成本的散點圖。首先，我們還是要來看一下本節(jié)要使用的公式：
采購當量＝年采購量/年采購批次平均存量＝采購數(shù)量/2存儲成本＝平均存量×單位存儲成本采購成本＝年采購批次×采購成本總成本＝存儲成本＋采購成本
步驟01 新建工作表
新建一個工作簿，并在工作簿中重命名其中一個工作表，刪除其他的。輸入表格的字段標題、數(shù)據(jù)，并設置格式等。
步驟02 輸入公式
在B2到F2依次輸入下列公式：
=$B$19/A2 =B2/2 =C2*$I$19
=A2*$E$19 =D2+E2
步驟03 復制公式
選中B2:F2，移動光標到F2右下角，雙擊即可復制上述公式至B3:F13區(qū)域。
步驟04 最低采購成本公式
在B16單元格輸入公式：=MIN(F2:F13)。
步驟05 采購批次公式
在E16單元格輸入公式：=INDEX(A2:A13,MATCH(B16,F2:F13,0))。
步驟06 采購量公式
在I16單元格輸入公式：=INDEX(B2:B13,MATCH(B16,F2:F13,0))。關于INDEX函數(shù)
的使用我們在前面的相關章節(jié)已經介紹過。
知識點：
MIN函數(shù)返回一組值中的最小值。
函數(shù)語法MIN(number1,number2,...)Number1,number2,...是要從中查找最小值的1到255個數(shù)字。
函數(shù)說明參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計算在內。如果參數(shù)為數(shù)組或引用，則只使用該數(shù)組或引用中的數(shù)字。數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略。如果參數(shù)中不含數(shù)字，則函數(shù)MIN返回0。如果參數(shù)為錯誤值或為不能轉換為數(shù)字的文本，將會導致錯誤。
MATCH函數(shù)返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應位置。如果需要找出匹配元素的位置而不是匹配元素本身，則應該使用MATCH函數(shù)而不是LOOKUP函數(shù)。
函數(shù)語法MATCH(lookup_value,lookup_array,match_type)Lookup_value為需要在Lookup_array中查找的數(shù)值。例如，如果要在電話簿中查找某人的電話號碼，則應該將姓名作為查找值，但實際上需要的是電話號碼。Lookup_value可以為數(shù)值（數(shù)字、文本或邏輯值）或對數(shù)字、文本或邏輯值的單元格引用。
Lookup_array可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域。Lookup_array應為數(shù)組或數(shù)組引用?！ ?span lang="EN-US">Match_type為數(shù)字-1、0或1。Match_type指明Excel如何在lookup_array中查找lookup_value。如果match_type為1，函數(shù)MATCH查找小于或等于lookup_value的最大數(shù)值，Lookup_array必須按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；如果match_type為0，函數(shù)MATCH查找等于lookup_value的第一個數(shù)值，Lookup_array可以按任何順序排列；如果match_type為-1，函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值，Lookup_array必須按降序排列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。如果省略match_type，則假設為1。
函數(shù)說明函數(shù)MATCH返回lookup_array中目標值的位置，而不是數(shù)值本身。例如，MATCH("b",{"a","b","c"},0)返回2，即“b”在數(shù)組{"a","b","c"}中的相應位置。查找文本值時，函數(shù)MATCH不區(qū)分大小寫字母。如果函數(shù)MATCH查找不成功，則返回錯誤值#N/A。如果match_type為0且lookup_value為文本，可以在lookup_value中使用通配符、問號(?)和星號(*)。問號匹配任意單個字符；星號匹配任意一串字符。如果要查找實際的問號或星號，請在該字符前鍵入波形符(~)。
步驟07 添加年采購量滾動條
我們前面已經在自定義快速訪問工具欄上添加了滾動條按鈕，現(xiàn)在我們又要用到它。點擊這個按鈕，然后在A21單元格拖出一個滾動條。
步驟08 設置年采購量滾動條
在剛剛添加的滾動條上單擊鼠標右鍵，在彈出的對話框中對滾動條進行如圖中所示的設置。與這個滾動條相關聯(lián)的單元格是B19，設置方法是單擊文本框后面的編輯按鈕，再用鼠標選中B19單元格。單擊“確定”完成設置?！?span lang="EN-US">
步驟09 添加并設置采購成本滾動條
使用與上兩步相機的方法在D21單元格添加滾動條，并對其進行設置，設置內容如圖所示。
步驟10 添加并設置單位存儲成本滾動條
同理，在H21單元格添加滾動條，并對其進行設置，設置內容如圖所示。
步驟11 完成設置
通過以上幾個步驟的設置，我們已經得到一個具有數(shù)據(jù)調節(jié)功能的采購成本分析表。
第2節(jié)、采購成本變動分析
步驟01 采購量的變動影響分析
拖動滾動條，增大年采購量，存儲成本增大而采購成本未變，因此折線圖上的存儲成本線發(fā)生變動而采購成本線保持不變。
存儲成本增大，造成總成本增大，所以最低采購成本、采購批次、采購量也相應增大。
步驟02 采購成本的變動影響分析
拖動滾動條，增大采購成本，采購成本增大而存儲成本未變，因此折線圖上的采購成本線發(fā)生變動而存儲成本線保持不變。采購成本增大，造成總成本增大，所以最低采購成本、采購量也相應增大，采購批次則減少。
步驟03 單位存儲成本的變動影響分析
拖動滾動條，增大單位存儲成本，采購成本未變，因此折線圖上的存儲成本線發(fā)生變動而采購成本線保持不變。采購成本增大，造成總成本增大，所以最低采購成本、采購批次也相應增大，采購量則減少。
第3節(jié)、材料成本匯總表
知識點：
數(shù)據(jù)透視表的數(shù)據(jù)源使用數(shù)據(jù)透視表可以匯總、分析、瀏覽和提供摘要數(shù)］使用數(shù)據(jù)透視圖可以在數(shù)據(jù)透視表中可視化此摘要數(shù)據(jù)，并且可以方便地查看比較、模式和趨勢。數(shù)據(jù)透視表和數(shù)據(jù)透視圖都能使您做出有關企業(yè)中關鍵數(shù)據(jù)的決策。在創(chuàng)建數(shù)據(jù)透視表時，可使用多種源數(shù)據(jù)類型。
使用工作表數(shù)據(jù)可以將Excel工作表中的數(shù)據(jù)作為報表的數(shù)據(jù)來源。數(shù)據(jù)應為列表格式，第一行包含列標簽，其余行包含相同列中的類似項，并且數(shù)據(jù)區(qū)域中沒有空白的行或列。Excel將列標簽作為報表的字段名稱。
使用外部數(shù)據(jù)源要匯總和分析Excel的外部數(shù)據(jù)，則可從包括數(shù)據(jù)庫、OLAP多維數(shù)據(jù)集和文本文件的外部數(shù)據(jù)源上檢索數(shù)據(jù)。
使用其他數(shù)據(jù)透視表每次在新建數(shù)據(jù)透視表或數(shù)據(jù)透視圖時，Excel均將報表數(shù)據(jù)的副本存儲在內存中，并將其保存為工作簿文件的一部分。這樣每張新的報表均需要額外的內存和磁盤空間。
但是，如果將現(xiàn)有數(shù)據(jù)透視表作為同一個工作簿中的新報表的源數(shù)據(jù)，則兩張報表就可以共享同一個數(shù)據(jù)副本。因為可以重新使用存儲區(qū)，所以就會縮小工作簿文件，減少內存中的數(shù)據(jù)。
如果要將某個數(shù)據(jù)透視表用作其他報表的源數(shù)據(jù)，則兩個報表必須位于同一工作簿中。如果源數(shù)據(jù)透視表位于另一工作簿中，則需要將源報表復制到要新建報表的工作簿位置。不同工作簿中的數(shù)據(jù)透視表和數(shù)據(jù)透視圖是獨立的，它們在內存和工作簿文件中都有各自的數(shù)據(jù)副本。
在刷新新報表中的數(shù)據(jù)時，Excel也會更新源報表中的數(shù)據(jù)，反之亦然。如果對某個報表中的項進行分組或取消分組，那么也將同時影響兩個報表。如果在某個報表中創(chuàng)建了計算字段或計算項，則也將同時影響兩個報表。
更改現(xiàn)有報表的源數(shù)據(jù)更改源數(shù)據(jù)將導致用于分析的數(shù)據(jù)也發(fā)生變化。例如，您可能希望方便地從測試數(shù)據(jù)庫切換到生產數(shù)據(jù)庫?？梢酝ㄟ^刷新報表，使用與原始數(shù)據(jù)連接信息類似的新數(shù)據(jù)來更新數(shù)據(jù)透視表。
在材料消耗環(huán)節(jié)既要統(tǒng)計同一產品不同材料的消耗量，又要計算不同產品對同一材料的消耗量。如何利用材料消耗明細賬快速準確地制作出兩個統(tǒng)計口徑下的材料消耗匯總表是很重要的工作。
材料成本匯總表效果圖
步驟01 新建工作表
新建一個工作簿，并在工作簿中重命名其中一個工作表，刪除其他的。輸入表格的字段標題、數(shù)據(jù)，并設置格式等。
步驟02 生成材料金額
在D3輸入公式：=INT(RAND()*1000)，并復制公式到D4:D32區(qū)域。
知識點：RAND函數(shù)
D函數(shù)返回大于等于0及小于1的均勻分布隨機實數(shù)，每次計算工作表時都將返回一個新的隨機實數(shù)。
函數(shù)語法
RAND()
函數(shù)說明
如果要使用函數(shù)RAND生成一隨機數(shù)，并且使之不隨單元格計算而改變，可以在編輯欄中輸入“=RAND()”，保持編輯狀態(tài)，然后按F9，將公式永久性地改為隨機數(shù)。
在本例中，使用公式：=INT(RAND()*1000)，即是生成一個大于0及小于1000的整數(shù)。
如果要生成一個規(guī)定范圍內的隨機數(shù)，可以使用公式：=RAND()*(上限-下限)+下限
步驟03 開始生成數(shù)據(jù)透視表
選擇B2:D32中的任意單元格，然后在“插入”選項卡中選擇“數(shù)據(jù)透視表”里的“數(shù)據(jù)透視表”，開始生成數(shù)據(jù)透視表。
步驟04 使用向導進行設置
這時會彈出一個對話框，本例中不必改動任何選項。在本例中數(shù)據(jù)來源就是我們的工作表，而數(shù)據(jù)透視表生成于新的工作表中。
步驟05 設置布局
在這一步我們要在新生成的工作表中設置數(shù)據(jù)透視表的布局形式，比較簡單的方式就是直接在窗口中拖動字段名稱到相應的區(qū)域。
步驟06 生成數(shù)據(jù)透視表
通過上面的一系列設置，我們得到一張數(shù)據(jù)透視表，表中顯示了材料和產品的匯總數(shù)據(jù)。
步驟07 增加數(shù)據(jù)透視圖
有了數(shù)據(jù)透視表，但我們希望更加直觀地查看數(shù)據(jù)，所以我們可以增加一張數(shù)據(jù)透視表，方法跟之前生成柱形圖的方法是一樣的。
步驟08 生成數(shù)據(jù)透視圖
通過上面一個簡單的步驟，我們就得到了一張數(shù)據(jù)透視圖，不過我們還需要對圖形做一些細節(jié)的調整。
步驟09 設置數(shù)據(jù)標簽
現(xiàn)在我們要對柱形圖的數(shù)據(jù)標簽進行設置，以方便查看。方法前面已經講過，在“布局”選項卡中選擇“數(shù)據(jù)標簽”下的“其他數(shù)據(jù)標簽選項”。
步驟10 顯示系列名稱
在彈出的對話框中，我們選擇顯示系列名稱，這樣柱形圖上就會直觀地顯示材料的類型。
步驟11 完成設置
做完以上的步驟，我們就得到了最終的結果。實際上，本節(jié)的例子除了使用數(shù)據(jù)透視表，我們也可以使用公式來得到相同的結果，這里我們就不再贅述了，有興趣的朋友可以自己研究一下。
第4節(jié)、企業(yè)成本|分析表|分解表
成本分析中有很多常用的表，本章將會看到其中一些。上一章我們講了本量利分析，本量利分析要求將企業(yè)成本劃分為變動和固定成本，在成本項目中很多項目既有固定成分，又有變動成分，所以需要將其分解。
材料成本匯總表效果圖
采購成本分析表效果圖
如何準確將成本中的固定成分和翻去成分分離是做好本量利分析的關鍵，一些成本項目的性質比較明確，可以直接劃分為固定成本或變動成本，但是有的則比較模糊。比如電費，雖然電費與產量有關，但是產量為零時電費卻并不為零，這說明電費中既有變動成分，又有固定成分。
成本分解表效果圖
步驟01 新建工作表
新建一個工作簿，并在工作簿中重命名其中一個工作表，刪除其他的。輸入表格的標題、數(shù)據(jù)，并設置格式等。這個工作表中包括了產量和電費的數(shù)據(jù)。
步驟02 生成散點圖
選中C2:D14區(qū)域，使用上一章介紹的方法插入一個散點圖，這次我們要選擇的是一個沒有線的默認散點圖。
步驟03 對散點圖進行調整
對于生成的散點圖我們進行一些小的調整，以便最終效果更加便于查看。首先我們可以刪除圖例，因為現(xiàn)在只有一個系列。另外，我們還可以修改一下散點圖的標題
步驟04 設置軸標題
如圖所示，從“布局”選項卡中選擇“坐標軸標題”下的橫坐標及縱坐標項目可以添加軸標題，并設置相應的格式等。添加后可以直接對軸標題進行編輯。
步驟05 給散點添加趨勢線
右鍵單擊散點圖中的任意一個散點，從彈出的快捷菜單中選擇“添加趨勢線” 。
步驟06 趨勢線的設置
在彈出的對話框中，選擇“線性”，并勾選“顯示公式”。
步驟07 分解成本
確定后生成最終的散點圖。可以看到，在散點圖上出現(xiàn)了線性趨勢線和一個二元一次方程。方程中的截距代表固定成本，即505.4；斜率代表單位變動成本，即1.9942。
知識點：趨勢線
Excel的圖表中，散點圖、折線圖、面積圖、條形圖、柱形圖、股份圖和氣泡圖中都可以添加趨勢線，通常適合使用趨勢線的的圖表有兩類，一是成對的數(shù)字數(shù)據(jù)，即典型的散點圖中使用的數(shù)據(jù)；二是基于時間的數(shù)據(jù)，比如折線圖、散點圖、面積圖等使用的數(shù)據(jù)。另外，除了線性趨勢線，Excel還提供了非線性趨勢線：
* 對數(shù)：當數(shù)據(jù)增加或減少的變化速率非常大，然后很快變得平緩時使用。
* 多項式：數(shù)據(jù)規(guī)則波動時使用，可以根據(jù)數(shù)據(jù)的波動規(guī)律制定多項式的階數(shù)。
* 乘冪：數(shù)據(jù)按照固定的速率增加時使用，此時數(shù)據(jù)不能為零值或負數(shù)。
* 指數(shù)：數(shù)據(jù)以遞增或者遞減的趨勢變化時使用，數(shù)據(jù)同樣不能為零或負數(shù)。
* 移動平均：不是真正的趨勢線，它是原數(shù)據(jù)按照指定的項數(shù)不高平均值。使用移動平均時要設定移動平均的項數(shù)。
在勾選“顯示公式”后，圖形上會顯示線性或非線性的趨勢線對應的公式。
在勾選“顯示R平方值”后，圖形上會顯示模型的擬合系數(shù)。一般情況下，擬合系數(shù)越大，趨勢線和原數(shù)據(jù)的擬合程度越好；反之則越差。
聯(lián)系客服
