Excel在會計與財務管理中的應用項目九本量利分析(1)理解成本性態分析和本量利分析的基本概念與理論知識；(2)掌握Excel中相關分析、回歸分析等數據統計分析方法的應用；(3)學會應用Excel解決實際案例中遇到的本量利分析問題。學習目標本量利分析(Cost-Volume-ProftAnalysis)就是對成本、業務量、利潤之間相互關系進行分析，簡稱CVP分析。該分析是指在變動成本計算模式的基礎上，以數量化的會計模型與圖像的方式揭示固定成本、變動成本、銷售量、銷售單價、銷售額和利潤等變量間的內在規律性聯系，為預測、決策和規劃提供必要的財務信息的一種定量分析方法。因此，本項目主要通過案例的形式來介紹如何通過Excel2010實現本量利分析。情景引入目錄CONTENTS成本性態分析1本量利分析2任務一成本性態分析知識準備成本是指企業在生產經營過程中，為達到一定目的而應當或可能發生的各種經濟資源的價值的犧牲或代價。在管理會計的范疇中，成本的時態可以是過去時、現在完成時或將來時。這與財務會計強調的歷史成本概念有較大的差異。一、成本定義及分類(一)成本定義知識準備(1)按經濟用途分為制造費用和非制造費用。一、成本定義及分類(二)成本分類(2)按實際發生時態分為歷史成本和未來成本。知識準備二、成本性態分析(一)成本性態的含義成本性態是指在一定條件下成本總額與特定業務量之間的依存關系。按照成本與產量的依存關系，成本可分為三大類:固定成本變動成本混合成本知識準備二、成本性態分析(二)成本性態分析定義成本性態分析是指在成本性態分類的基礎上，按照一定的程序和方法，將全部成本劃分為固定成本和變動成本兩大類，并建立成本函數模型的過程。成本函數模型通常用y=a+bx來模擬，其中y為成本總額，a為固定成本總額，b為單位變動成本，x為業務量，bx為變動成本總額。知識準備二、成本性態分析(三)成本性態分析方法賬戶分析法也稱會計分析法或直接分析法，是指根據各有關成本賬戶的具體內容直接判斷成本與業務量之間的依存關系，看其是更接近固定成本還是更接近變動成本，從而確定成本性態的一種成本分解方法。1.賬戶分析法知識準備二、成本性態分析(三)成本性態分析方法工程分析法是一種比較合理的混合成本的分解方法。其根據產品生產中的投入產出關系，即各種材料、人工、費用的消耗與產量之間的直接關系來合理區分哪些消耗是變動成本，哪些是固定成本。因為技術測定過程復雜且成本過高，所以工程分析法一般較少采用。2.工程分析法知識準備二、成本性態分析(三)成本性態分析方法歷史成本法也稱數學分解法，是指根據過去若干周期(月或年)數據所反映的實際成本與業務量之間的依存關系，對混合成本采用適當的數學方法加以分解，來確定成本性態的一種成本分解方法。歷史成本法是混合成本分解方法中最常用的一種方法。根據資料利用的具體形式不同，歷史成本法可分為高低點法、散布圖法和回歸分析法。本項目主要介紹回歸分析法。回歸分析法是指根據一定期間業務量與相應混合成本之間的歷史資料，利用微分極值原理計算出最能反映業務量與成本之間關系的回歸直線，從而確定成本性態的一種方法。3.歷史成本法知識準備二、成本性態分析(三)成本性態分析方法回歸分析法是計算結果最為精準的方法，其具體步如下:3.歷史成本法(1)計算相關系數r，判斷業務量x與成本y之間的線性關系。(2)通過微分極值原理(最小二乘法原理)計算出回歸直線方程中的固定成本a和單位變動成本b。(3)將求出的a、b代入直線方程y=a+bx中，得到成本性態分析模型。任務目標(1)了解成本性態分析方法；(2)掌握Excel中利用散點圖進行簡單相關分析；(3)掌握利用函數開展相關分析；(4)能利用數據分析工具確定相關系數。任務資料福源公司是一家生產制造企業，自2010年以來，公司主要經營的產品是A、B、C。福源公司2010—2021年A、B、C三種主營業務產品的產量和總成本如表9-1所示。任務資料要求:(1)分別利用散點圖、函數、數據分析方法分析福源公司2010-2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(相關系數)。(2)利用回歸分析方法分析福源公司2010-2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(a和b的值)。(3)預測福源公司2022年主營業務產品成本并計算主營業務產品成本的構成。任務操作一、輸入數據(1)打開Excel，單擊“保存”按鈕，將Excel工作簿另存為“本量利分析.xlsm”，如圖9-1所示。圖9-1新建“本量利分析”工作簿并保存任務操作一、輸入數據(2)將Sheet1工作表重命名為“成本分析與預測”，建立“成本分析與預測”工作表,如圖9-2所示。圖9-2建立“成本分析與預測”工作表任務操作一、輸入數據(3)在“成本分析與預測”工作表中輸入2010—2021年主營業務產品的產量和總成本的相關數據，如圖9-3所示。圖9-3輸入相關數據任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(1)選擇圖表類型。選中A3:C16單元格區域，選擇“插入”選項卡，執行“圖表”組中的“散點圖”命令，出現散點圖的幾種類型，如圖9-4所示。圖9-4選擇圖表類型(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系1.采用散點圖進行簡單相關分析任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系1.采用散點圖進行簡單相關分析(2)選擇“帶平滑線和數據標記的散點圖”，隨即打開帶平滑線和數據標記的散點圖。選擇“圖表工具-設計”選項卡，單擊“圖表布局”組右下方的下拉箭頭，選擇布局8,如圖9-5所示。(3)編輯散點圖。雙擊散點圖中的“圖表標題”,即可對其進行編輯,把標題修改為“A產品成本分析與預測”。雙擊散點圖中的橫軸，打開“設置坐標軸格式”對話框，在“坐標軸選項”的“最小值”中輸入“2010”，在“最大值”中輸入“2022”，在“主要刻度單位”中輸入“2.0”，在“次要刻度單位”中輸入“0.4”，如圖9-6所示。任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系圖9-5帶平滑線和數據標記的散點圖圖9-6編輯散點圖任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系1.采用散點圖進行簡單相關分析結論:通過散點圖可以比較直觀地看出，當A產品產量變化時，其總成本數據大多呈現同樣的分布走勢，因此得出福源公司2010-2021年A產品產量與總成本數據之間存在著一定程度的正相關關系。任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系2.采用函數進行簡單相關分析(1)計算相關系數。在B18單元格中輸入“相關系數”，在C18單元格中輸入公式“=CORREL(B4:B15,C4:C15)”，按Enter鍵，即得到福源公司2010-2021年A產品的產量與總成本之間的相關系數為0.94671687;在C19單元格中輸入“=PEARSON(B4:B15,C4:C15)”按Enter鍵，可以得到相同的結果，如圖9-7所示。任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系圖9-7計算相關系數任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系2.采用函數進行簡單相關分析(2)計算協方差。在B20單元格中輸入“協方差”，在C21單元格中輸入公式“=COVARIANCE.S(B4:B15,C4:C15)”，按Enter鍵，即得到福源公司2010-2021年A產品的產量與總成本之間的協方差為35937599.18，如圖9-8所示。任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系圖9-8計算協方差任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系2.采用函數進行簡單相關分析(3)除了上述直接輸入公式方式外,還可以通過“插入函數"命令進行計算。以“協方差的計算為例，選擇“公式”選項卡，執行“函數庫”組中的“插入函數”命令，打開“插入函數”對話框，在“或選擇類別”下拉列表中選擇“統計”，在“選擇函數”列表框中選擇“COVARIANCE.S”函數，如圖9-9所示。任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系圖9-9“插入函數”對話框任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系2.采用函數進行簡單相關分析(4)單擊“確定”按鈕，打開“函數參數”對話框，單擊“Arrayl”后的折疊按鈕，選中B4:B15單元格區域;單擊“Array2”后的折疊按鈕,選中C4:C15單元格區域,如圖9-10所示。單擊“確定”按鈕，會得到相同的結果(協方差為35937599.18)。圖9-10“函數參數”對話框任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系3.利用數據分析工具確定相關關系除了用函數功能計算兩個變量的相關系數和協方差外，Excel2010數據分析工具中還提供了專門進行相關分析的工具，用于計算兩個變量的相關系數和協方差。任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系3.利用數據分析工具確定相關關系(1)選擇“數據”選項卡，執行“分析”組中的“數據分析”命令，打開“數據分析”對話框，在“分析工具”中選擇“相關系數”，如圖9-11所示。圖9-11選擇“相關系數”任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系3.利用數據分析工具確定相關關系圖9-12“相關系數”對話框(2)單擊“確定”按鈕，打開“相關系數”對話框，進行圖9-12所示設置。任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系3.利用數據分析工具確定相關關系(3)同樣，在“分析工具”中選擇“協方差”并執行類似的步驟，則可以計算協方差，如圖9-13和圖9-14所示。圖9-13選擇“協方差”圖9-14“協方差”對話框任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系3.利用數據分析工具確定相關關系福源公司2010—2021年A產品產量與成本數據相關分析的計算結果如圖9-15所示。圖9-15A產品產量與成本數據相關分析的結果任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的相關關系3.利用數據分析工具確定相關關系結論:從圖9-15所示的結果不難看出，福源公司2010-2021年A產品產量與成本數據之間的相關系數很高，達到了0.94以上，說明兩者之間正相關的程度很強，也在一定程度上說明兩者之間有進行回歸的必要性。因為回歸分析研究的是自變量或者解釋變量對因變量的影響關系，如果參與分析的解釋變量和被解釋變量之間的相關系數很小，就沒有必要進行回歸分析。我們可以運用前面講解的三種方法對B、C兩種產品進行成本分析與預測。任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的相關關系1.采用散點圖進行簡單相關分析運用前面介紹的方法繪制B產品成本分析與預測散點圖，具體操作步驟及過程不再贅述，最終形成的散點圖如圖9-16所示。通過散點圖可以比較直觀地看出，與A產品的走勢相同，福源公司2010-2021年B產品產量與成本數據之間同樣存在著一定程度的正相關關系，理由是當B產品產量變化時，其總成本數據大多呈現同樣的分布走勢。圖9-16B產品成本分析與預測散點圖任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的相關關系2.計算相關系數和協方差圖9-17B產品產量與成本數據之間的相關系數可以運用前面插入函數和數據分析的方式進行簡單相關分析，計算得到福源公司2010-2021年B產品產量與成本數據之間的相關系數為0.912203707，協方差為23982570.75具體計算結果如圖9-17所示。任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的相關關系2.計算相關系數和協方差結論:從圖9-17所示的結果不難看出，福源公司2010-2021年B產品產量與成本數據之間的相關系數很高，達到了0.91以上，說明兩者之間正相關的程度很強，也在一定程度上說明兩者之間有進行回歸的必要性。任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的相關關系1.采用散點圖進行簡單相關分析運用前面介紹的方法繪制C產品成本分析與預測散點圖，具體操作步及過程不再贅述，最終形成的散點圖如圖9-18所示。通過散點圖可以比較直觀地看出，與A、B產品的走勢相同，福源公司2010-2021年C產品產量與成本數據之間同樣存在著一定程度的正相關關系，理由是當C產品產量變化時，其總成本數據大多呈現同樣的分布走勢。圖9-18C產品成本分析與預測散點圖任務操作二、通過相關分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的相關關系2.計算相關系數和協方差結論：從圖9-19所示的結果不難看出，福源公司2010—2021年C產品與成本數據之間的相關系數很高，達到了0.94以上，說明兩者之間正相關的程度很強，也在一定程度上說明兩者之間有進行回歸的必要性。圖9-19C產品產量與成本數據之間的相關系數任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系1.利用函數進行一元線性回歸分析(1)在B23單元格中輸入“截距”,在C23單元格輸入公式“=INTERCEPT(C4:C15,B4:B15)"，按Enter鍵，即可得到福源公司2010-2021年A產品產量與成本數據之間的回歸截距。(2)在B24單元格中輸入“斜率”，在C24單元格中輸入公式“=SLOPE(C4:C15.B4:B15)”，按Enter鍵，即可得到福源公司2010-2021年A產品產量與成本數據之間的回歸斜率。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系1.利用函數進行一元線性回歸分析(3)在B25單元格中輸入“判定系數”，在C25單元格中輸入公式“=RSQ(C4:C15.B4:B15)”，按Enter鍵，即可得到福源公司2010-2021年A產品產量與成本數據之間的回歸判定系數，計算結果如圖9-20所示。圖9-20利用函數分析工具分析的結果任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系1.利用函數進行一元線性回歸分析(4)除了直接輸入公式的方法外，也可以通過“插入函數”命令加以計算。例如，計算“截距”，選中B27單元格，選擇“公式”選項卡，執行“函數庫”中的“插入函數”命令，打開“插入函數”對話框，在“或選擇類別”下拉列表中選擇“統計”，在“選擇函數”列表框中選擇“INTERCEPT”函數，如圖9-21所示。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系1.利用函數進行一元線性回歸分析單擊“確定”按鈕，打開“函數參數”對話框，單擊“Knowny’s”后的折疊按鈕選中C4:C15單元格區域，單擊“Knownx’s”后的折疊按鈕，選中B4:B15單元格區域如圖9-22所示。單擊“確定”按鈕，會得到相同的結果。斜率和判定系數的計算同樣可以通過“插入函數”命令實現，步驟同上。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系1.利用函數進行一元線性回歸分析圖9-22“函數參數”對話框圖9-21“插入函數”對話框任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系1.利用函數進行一元線性回歸分析(5)利用函數的數組形式進行計算。選中B31:C35單元格區域,輸入公式“=LINEST(C4C15,B4:B15,1,1)”，并按Shift+Ctrl+Enter組合鍵執行數組運算，得到數組運算的結果,如圖9-23所示。圖9-23利用數組運算的結果任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系1.利用函數進行一元線性回歸分析需要特別解釋和說明的是，在Excel2010中并不會出現每個單元格的具體介紹，讀者需要按照本書的指導對分析結果進行解讀。在本例中，斜率為1.8693071，解釋變量標準誤差為0.2010976，判定系數為0.8962728，F值為86.406758，回歸平方和為738962487,截距為6137.435535，常數項標準誤差為1907.874992，回歸標準誤差為2924.403811，自由度為10，殘差平方和為85521376.5。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系1.利用函數進行一元線性回歸分析該結果也可以通過“插入函數”命令實現,首先選中一個5x2的單元格區域,選擇“公式”選項卡,執行“函數庫”組中的“插入函數”命令,打開“插入函數”對話框，在“選擇函數”列表框中選擇“LINEST”函數，單擊“確定”按鈕，在打開的“函數參數”對話框中設置相應參數，如圖9-24所示。因為截距不強制設為0且返回附加統計值，所以參數Const和Stats均選擇TURE。按Shift+Ctrl+Enter組合鍵執行數組運算，即可得到與輸入公式相同的數組運算結果。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系1.利用函數進行一元線性回歸分析圖9-24“函數參數”對話框任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系2.利用數據分析工具進行回歸分析(1)選擇回歸工具。選擇“數據”選項卡,執行“分析”組中的“數據分析”命令,打開“數據分析”對話框，在“分析工具”列表框中選擇“回歸”選項，如圖9-25所示，單擊“確定”按鈕，隨即打開“回歸”對話框。圖9-25“數據分析”對話框任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系2.利用數據分析工具進行回歸分析(2)設置“回歸”選項。在“回歸”對話框中首先設置“輸入”內容,單擊“Y值輸入區域后面的折疊按鈕，選中$C$3:$C$15單元格區域:單擊“X值輸入區域”后面的折疊按鈕選中$BS3:$BS15單元格區域。因為輸入區域包含標志項，所以選中“標志”復選框;選中“置信度”復選框，并默認為95%。然后設置“輸出選項”，在“新輸出區域”中選中SB$38單元格區域。如果選中“新工作表組”單選按鈕，則表示將輸出結果顯示在一個新的工作表上。選中“殘差”“正態分布”中的全部復選框,以觀察殘差、標準殘差、殘差圖、線性擬合圖以及正態概率圖等信息，如圖9-26所示。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系2.利用數據分析工具進行回歸分析圖9-26“回歸”對話框任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系2.利用數據分析工具進行回歸分析圖9-27回歸結果匯總輸出單擊“確定”按鈕，得到回歸結果，如圖9-27所示。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系2.利用數據分析工具進行回歸分析從圖9-27中可以得到以下結論:第一部分是回歸統計分析的結果，MultipleR為0.94671687,RSquare為0.896272832AdiustedRSquare為0.885900115，標準誤差為2924.403811，觀測值為12。其中，需要特別注意的是RSquare與AdjustedRSquare分別為回歸模型的判定系數以及修正的判定系數，在很大程度上代表著模型的解釋能力，相應的數值越大，說明模型的解釋能力越強。本例中回歸模型的判定系數以及修正的判定系數都接近0.9，說明模型解釋能力很好，或者選取的解釋變量能夠解釋被解釋變量的大部分信息。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系2.利用數據分析工具進行回歸分析第二部分是回歸模型的方差分析結果，df代表自由度，模型的F統計值為86.40675779，顯著性P值(SignifcanceF)為3.09197x10-6，遠小于通常具有顯著性意義的0.05，說明模型非常顯著。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系2.利用數據分析工具進行回歸分析第三部分是回歸模型的變量系數值、變量系數的標準誤差、T統計量、顯著性P值以及95%的置信區間等信息。本例中產量的變量系數值為1.86930706，變量系數的標準誤差為0.201097555，T統計量為9.295523535，顯著性P值為3.09197x10-6，95%的置信區間為[1.421233783,2.317380336];常數項的變量系數值為6137.435535，變量系數的標準誤差為1907.874992，T統計量為3.216896055，顯著性P值為0.009223555，95%的置信區間為[1886.425141.10388.44593]。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系2.利用數據分析工具進行回歸分析從結果中可以看出,自變量產量和常數項的系數都為正，而且非常顯著(遠小于通常具有顯著性意義的0.05)，這說明固定成本為正值而且非常顯著，同時產量會顯著作用于成本總額，或者說變動成本對于總成本的影響是構成重要性的。依據這一結果，我們也可以寫出福源公司2010—2021年A產品產量與成本數據之間的回歸方程：成本=6137.435535+產量×1.86930706任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(一)福源公司2010—2021年A產品的產量與總成本之間的回歸關系2.利用數據分析工具進行回歸分析圖9-28～圖9-32依次給出了本次回歸殘差結果輸出、正態概率輸出、殘差圖、線性擬合圖、正態概率圖等信息。圖9-28殘差結果輸出圖9-29正態概率輸出任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系圖9-30殘差圖圖9-31線性擬合圖圖9-32正態概率圖任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系我們可以直接采用上述講解的兩類方法進行分析，由于本項目篇幅的限制，本部分僅利用數據分析工具研究福源公司2010-2021年B產品的產量與總成本之間的回歸關系。對于利用函數進行一元線性回歸分析，讀者可以利用課余時間進行練習。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系(1)選擇回歸工具。選擇“數據”選項卡,執行“分析”組中的“數據分析”命令,打開“數據分析”對話框，在“分析工具”列表框中選擇“回歸”選項，如圖9-33所示，單擊“確定”按鈕，打開“回歸”對話框。圖9-33“數據分析”對話框任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系(2)設置“回歸”選項。在“回歸”對話框中首先設置“輸入”內容,單擊“Y值輸入區域’后面的折疊按鈕,并選中$E$3:$E$15單元格區域;單擊“X值輸入區域”后面的折疊按鈕并選中$DS3:$D$15單元格區域。因為輸入區域包含標志項，所以選中“標志”復選框;選中“置信度”復選框,并默認為95%。然后設置“輸出選項”,在“輸出區域”中選擇$BS89單元格區域。如果選中“新工作表組”單選按鈕，則表示將輸出結果顯示在一個新的工作表上。接著將“殘差”“正態分布”中的復選框全部選中,以觀察殘差、標準殘差、殘差圖、線性擬合圖以及正態概率圖等信息，如圖9-34所示。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系圖9-34“回歸”對話框任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系圖9-35回歸結果匯總輸出單擊“確定”按鈕，得到回歸結果，如圖9-35所示。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系從圖9-35中可以得到以下結論：第一部分是回歸統計分析的結果,MultipleR為0.912203707,RSquare為0.832115602AdjustedRSquare為0.815327162，標準誤差為2571.52361，觀測值為12。其中，需要特別注意的是RSquare與AdiustedRSquare分別為回歸模型的判定系數以及修正的判定系數，在很大程度上代表著模型的解釋能力，相應的數值越大，說明模型的解釋能力越強。本例中回歸模型的判定系數以及修正的判定系數都接近0.8，說明模型解釋能力很好，或者選取的解釋變量能夠解釋被解釋變量的大部分信息。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系第二部分是回歸模型的方差分析結果，df代表自由度，模型的F統計值為49.56479657顯著性P值(SignifcanceF)為3.53996x10-5，遠小于通常具有顯著性意義的0.05，說明模型非常顯著。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系第三部分是回歸模型的變量系數值、變量系數的標準誤差、T統計量、顯著性P值以及95%的置信區間等信息。本例中產量的變量系數值為1.242412868，變量系數的標準誤差為0.17647341，T統計量為7.040227026，顯著性P值為3.53996x10-5，95%的置信區間為[0.849205607,1.635620128];常數項的變量系數值為3588.365759，變量系數的標準誤差為1623.042855，T統計量為2.210887869，顯著性P值為0.051483202，95%的置信區間為[-27.99908519,7204.730603]。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系從結果中可以看出，自變量產量和常數項的系數都為正，而且非常顯著(遠小于通常具有顯著性意義的0.05)，這說明固定成本為正值而且非常顯著，同時產量會顯著作用于成本總額，或者說變動成本對于總成本的影響是構成重要性的。依據這一結果，我們也可以寫出福源公司2010—2021年B產品產量與成本數據之間的回歸方程：成本=3588.365759+產量×1.242412868任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系圖9-36～圖9-40依次給出了本次回歸殘差結果輸出、正態概率輸出、殘差圖、線性擬合圖、正態概率圖等信息。圖9-36殘差結果輸出圖9-37正態概率輸出任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(二)福源公司2010—2021年B產品的產量與總成本之間的回歸關系圖9-38殘差圖圖9-39線性擬合圖圖9-40正態概率圖任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系我們可以直接采用上述講解的兩類方法進行分析，由于本項目篇幅的限制，本部分僅利用數據分析工具研究福源公司2010-2021年C產品的產量與總成本之間的回歸關系。對于利用函數進行一元線性回歸分析，讀者可以利用課余時間進行練習。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系(1)選擇回歸工具。選擇“數據”選項卡，執行“分析”組中的“數據分析”命令打開“數據分析”對話框，在“分析工具”列表框中選擇“回歸”選項，如圖9-41所示，單擊“確定”按鈕，打開“回歸”對話框。圖9-41“數據分析”對話框任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系(2)設置“回歸”選項。在“回歸”對話框中首先設置“輸入”內容，單擊“Y值輸入區域”后面的折疊按鈕，并選中$G$3:$G$15單元格區域;單擊“X值輸入區域”后面的折疊按鈕，并選中$FS3:SGS15單元格區域。因為輸入區域包含標志項，所以選中“標志”復選框:選中“置信度”復選框，并默認為95%。然后設置“輸出選項”，在“輸出區域”中選擇$B$142單元格區域。如果選中“新工作表組”單選按鈕，則表示將輸出結果顯示在一個新的工作表上。接著將“殘差”“正態分布”中的復選框全部選中，以觀察殘差、標準殘差、殘差圖、線性擬合圖以及正態概率圖等信息，如圖9-42所示。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系圖9-42“回歸”對話框任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系圖9-43回歸結果匯總輸出單擊“確定”按鈕，得到回歸結果，如圖9-43所示。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系第一部分是回歸統計分析的結果,MultipleR為0.946696426,RSquare為0.896234123,AdjustedRSquare為0.8858575535，標準誤差為195.0459565，觀測值為12。其中，需要特別注意的是RSquare與AdiustedRSquare分別為回歸模型的判定系數以及修正的判定系數，在很大程度上代表著模型的解釋能力，相應的數值越大，說明模型的解釋能力越強。本例中回歸模型的判定系數以及修正的判定系數都接近0.9，說明模型解釋能力很好，或者選取的解釋變量能夠解釋被解釋變量的大部分信息。從圖9-43中可以得到以下結論：任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系第二部分是回歸模型的方差分析結果，df代表自由度，模型的F統計值為86.37079436顯著性P值(SignifcanceF)為3.0978x10-6，遠小于通常具有顯著性意義的0.05,說明模型非常顯著。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系第三部分是回歸模型的變量系數值、變量系數的標準誤差、T統計量、顯著性P值以及95%的置信區間等信息。本例中產量的變量系數值為0.124649304，變量系數的標準誤差為0.013412397，RT統計量為9.293588885，顯著性P值為3.0978x10-6，95%的置信區間為[0.094764621,0.154533987];常數項的變量系數值為398.2355439，變量系數的標準誤差為232.6468712,T統計量為1.711759723，顯著性P值為0.117723383，95%的置信區間為[-120.1339887.916.6050765]。任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系從結果中可以看出，自變量產量和常數項的系數都為正,而且非常顯著(遠小于通常具有顯著性意義的0.05)，這說明固定成本為正值而且非常顯著，同時產量會顯著作用于成本總額，或者說變動成本對于總成本的影響是構成重要性的。依據這一結果，我們也可以寫出福源公司2010—2021年C產品產量與成本數據之間的回歸方程：成本=398.2355439+產量×0.124649304任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系圖9-44～圖9-48依次給出了本次回歸殘差結果輸出、正態概率輸出、殘差圖、線性擬合圖、正態概率圖等信息。圖9-44殘差結果輸出圖9-45正態概率輸出任務操作三、通過回歸分析方法分析福源公司2010—2021年主營業務產品(A、B、C)的產量與總成本數據之間的關系(三)福源公司2010—2021年C產品的產量與總成本之間的回歸關系圖9-46殘差圖圖9-47線性擬合圖圖9-48正態概率圖任務操作四、對福源公司2022年主營業務產品成本進行預測在C16單元格中輸入公式“=C23+B16*C24”,在E16單元格中輸入公式“=E23+D16*E24”，在G16單元格中輸入公式“=G23+F16*G24”。分別假定福源公司2022年A產品產量為12000件、B產品產量為10000件、C產品產量為18000件的情況下，得到福源公司2022年主營業務產品成本預測結果，如圖9-49所示。任務操作四、對福源公司2022年主營業務產品成本進行預測圖9-49福源公司2022年主營業務產品成本預測結果任務操作四、對福源公司2022年主營業務產品成本進行預測說明：本例中對福源公司2022年主營業務產品成本預測基于前述確定的回歸分析方程。A產品：成本=6137.435535+產量×1.86930706B產品：成本=3588.365759+產量×1.242412868C產品：成本=398.2355439+產量×0.124649304任務操作五、計算福源公司2022年主營業務產品成本構成在C17單元格中輸入公式“=B16*C24/C16”，在E17單元格中輸入公式“=D16*E24/E16”，在G17單元格中輸入公式“=F16*G24/G16”。在分別假定福源公司2022年A產品產量為12000件、B產品產量為10000件、C產品產量為18000件的情況下，得到福源公司2022年主營業務產品成本構成預測結果(變動成本占比)，如圖9-50所示。圖9-50福源公司2022年主營業務產品成本構成預測結果任務操作五、計算福源公司2022年主營業務產品成本構成通過計算發現，福源公司2022年A產品變動成本占比為78.52%，B產品變動成本占比為77.59%，C產品變動成本占比為84.93%。福源公司2022年三種主營業務產品的變動成本占比都比較高。任務二本量利分析知識準備成本性態分析假設是假定成本性態分析工作已經完成，全部成本費用已經按成本性態劃分為固定成本和變動成本兩部分。成本性態分析假設是本量利分析的基礎，也是本量利分析的出發點。一、本量利分析的基本假設(一)成本性態分析假設知識準備不管是固定成本還是變動成本，固定性和變動性都體現在特定期間內，金額大小也是在特定期間內加以計量得到的。隨著時間的推移，固定成本及其內容會發生變化，單位變動成本及其內容也會發生變化。一、本量利分析的基本假設(二)相關范圍假設1.期間假設知識準備成本按成本性態劃分為固定成本和變動成本是在一定業務量范圍內分析和計量的結果，當業務量發生變化尤其是變化較大時，成本性態就有可能發生改變。一、本量利分析的基本假設(二)相關范圍假設2.業務量假設知識準備本量利分析中的模型線性假設首先是固定成本不變，用模型表示為y=a。也就是說，在企業經營能力的相關范圍內，固定成本保持不變，即在一定期間和業務量范圍內固定成本的曲線為一條水平線。一、本量利分析的基本假設(三)模型線性假設1.固定成本不變假設知識準備與固定成本不變假設近似,單位變動成本不變假設也是在一定的相關范圍內保持不變。單位變動成本與業務量之間的完全線性關系用模型表示為y=bx。基于完全線性假設，變動成本的曲線表現為一條從原點出發的直線，該直線的斜率就是單位變動成本。一、本量利分析的基本假設(三)模型線性假設2.單位變動成本不變假設知識準備假設銷售單價也在相關范圍內保持不變，這樣銷售收入與銷售量之間也呈線性關系，用模型表示為y=px(p為銷售單價)。一、本量利分析的基本假設(三)模型線性假設3.銷售單價不變假設知識準備假設當期產品的生產量和業務量相一致，不考慮存貨變動對利潤的影響，即假設每期生產的產品總量總是能在當期全部售出，產銷平衡。假設產銷平衡，主要是在盈虧平衡分析時不考慮存貨的影響。因為盈虧平衡是一種短期決策，僅僅考慮特定時期全部成本的收回，而存貨中包含了以前時期的成本，所以不在考慮范圍內。一、本量利分析的基本假設(四)產銷平衡假設知識準備本量利分析考慮的相關因素主要包括銷售量、銷售單價、銷售收入、單位變動成本、固定成本、利潤等。這些因素之間的關系可以用下列基本公式來反映:二、本量利分析的基本原理利潤=銷售收入-總成本

=銷售單價x銷售量-(變動成本+固定成本)

=銷售單價x銷售量-單位變動成本x銷售量-固定成本

=(銷售單價-單位變動成本)x銷售量-固定成本知識準備邊際貢獻也稱貢獻毛益或邊際利潤，是指產品的銷售收入減去變動成本之后的金額，是用來衡量企業經濟效益的一項重要指標。三、本量利分析的相關概念邊際貢獻通常有兩種表現形式:一是總額概念，稱邊際貢獻總額(Tcm)，通常簡稱“邊際貢獻”，是指產品的銷售收入總額減去變動成本總額后的余額;二是單位概念，稱單位邊際貢獻(cm)，是指產品的銷售單價減去單位變動成本后的余額，反映的是每一個單位產品的創利能力，即每增加一個單位產品銷售可提供的創利額。知識準備兩者用公式表示如下：三、本量利分析的相關概念邊際貢獻

=銷售收入-變動成本

=(銷售單價-單位變動成本)×銷售量

=單位邊際貢獻×銷售量

單位邊際貢獻=銷售單價-單位變動成本任務目標(1)了解本量利分析常用的公式；(2)學會使用Excel計算邊際貢獻、銷售利潤等相關數據；(3)使用本量利分析方法為企業做出正確的決策。任務資料本任務沿用任務一的案例，福源公司是一家生產制造企業，自2010年以來，公司主要經營的產品是A、B、C三種產品。福源公司2022年A、B、C三種主營業務產品的總銷售收入預測數據如表9-2所示。任務資料要求：(1)利用福源公司2022年主營業務產品銷售收入預測數據，結合任務一的相關結果,計算福源公司2022年的邊際貢獻、銷售利潤等，針對每種主營業務產品進行點評分析。任務資料(2)假定在第(1)步中計算得到的福源公司2022年主營業務利潤數據不夠理想，未達到股東預期，管理層提出了以下三種改進方案，在Excel2010中運用本量利分析方法幫助股東做出正確的決策。①不再生產預測產生虧損的A產品，其他產品(B、C)維持原生產量不變。②不再生產預測產生虧損的A產品，將釋放出的產能用于B和C的生產，其中合理預計B產品產量將增加40%，C產品產量將增加10%。③繼續生產預測產生虧損的A產品，但壓縮為原定計劃的70%，將釋放出的產能用于B產品的生產，合理預計B產品產量將增加36%。任務操作一、輸入數據(1)打開Excel“本量利分析.xlsm”文件，選擇一張空白工作表，重命名為“本量利分析”，建立“本量利分析”工作表。將A1:E1單元格區域合并并居中，在其中輸入“本量利分析”，字體設置為“宋體”，字號設置為“18”號，并進行加粗處理:在A2:E2單元格區域中分別輸入“產品”“A產品”“B產品”“C產品”“總計”。任務操作一、輸入數據(1)在A3:A8單元格區域中分別輸入“銷售收入”“銷售成本”“變動成本”“邊際貢獻”“固定成本”“銷售利潤”。在B3:D3單元格區域中直接輸入福源公司2022年主營業務銷售收入預測數據，在E3單元格中輸入公式“=SUM(B3:D3)”，如圖9-51所示。圖9-51“本量利分析”工作表任務操作一、輸入數據(2)在B4:E8單元格區域設置公式，如表9-3所示。任務操作一、輸入數據(3)輸入以上內容后，最終形成的福源公司2022年本量利分析預測結果如圖9-52所示。圖9-52福源公司2022年本量利分析預測結果任務操作一、輸入數據結論:從圖9-52中可以看出，福源公司2022年銷售利潤預測為虧損狀態，即-99.53770731萬元，預測結果不甚理想。其中，A產品銷售利潤預測為-2699.120253萬元，B產品銷售利潤預測為2241.505565萬元,C產品銷售利潤預測為358.0769808萬元。在資源和條件具備一定調整可能性的情況下，福源公司需要對當前的生產計劃進行必要調整，以獲得更加優異的經營業績，回報股東的信任。任務操作二、方案分析(1)在第一種備選方案下，利用福源公司2022