EXCEL在會計與財務(wù)管理中的應(yīng)用-本量利分析

EXCEL在會計與財務(wù)管理中的應(yīng)用本量利分析任務(wù)一成本性態(tài)分析任務(wù)二本量利分析

本量利分析（Cost-Volume-ProfitAnalysis）就是對成本、業(yè)務(wù)量、利潤之間相互關(guān)系的分析，簡稱CPV分析。該分析是指在變動成本計算模式的基礎(chǔ)上，以數(shù)量化的會計模型與圖像的方式來揭示固定成本、變動成本、銷售量、銷售單價、銷售額和利潤等變量間的內(nèi)在規(guī)律性聯(lián)系，為預(yù)測、決策和規(guī)劃提供必要的財務(wù)信息的一種定量分析方法。因此本章主要是通過案例的形式來介紹如何通過Excel2010實現(xiàn)本量利分析。項目九本量利分析【知識準(zhǔn)備】

一、成本及分類（一）成本定義成本是指企業(yè)在生產(chǎn)經(jīng)營過程，對象化的資源以貨幣表現(xiàn)，為達到一定目的而應(yīng)當(dāng)或可能發(fā)生的各種經(jīng)濟資源的價值的犧牲或代價。在管理會計的范疇中，成本的時態(tài)可以是過去時、現(xiàn)在完成時或?qū)頃r。這與財務(wù)會計強調(diào)的歷史成本概念有較大的差異。（二）成本分類1.按經(jīng)濟用途分為制造費用和非制造費用2.按實際發(fā)生時態(tài)分為歷史成本、未來成本任務(wù)一成本性態(tài)分析【知識準(zhǔn)備】

二、成本性態(tài)分析（一）成本性態(tài)的含義成本性態(tài)是指在一定條件下成本總額與特定業(yè)務(wù)量之間的依存關(guān)系。按照成本與產(chǎn)量的依存關(guān)系，成本可分為固定成本、變動成本和混合成本三大類。（二）成本性態(tài)分析定義成本性態(tài)分析是指在成本性態(tài)分類的基礎(chǔ)上，按照一定的程序和方法，將全部成本劃分為固定成本和變動成本兩大類，并建立成本函數(shù)模型的過程。成本函數(shù)模型通常用y=a+bx來模擬，其中，y為成本總額，a為固定成本總額，b為單位變動成本，x為業(yè)務(wù)量，bx為變動成本總額。任務(wù)一成本性態(tài)分析【知識準(zhǔn)備】

1.賬戶分析法賬戶分析法，也稱會計分析法或直接分析法，是指根據(jù)各有關(guān)成本賬戶的具體內(nèi)容，直接判斷成本與業(yè)務(wù)量之間的依存關(guān)系，看其是更接近固定成本還是更接近變動成本，從而確定成本性態(tài)的一種成本分解方法。2.工程分析法工程分析法是一種比較合理的混合成本的分解方法。它是根據(jù)產(chǎn)品生產(chǎn)中的投入產(chǎn)出關(guān)系，即各種材料、人工、費用的消耗與產(chǎn)量之間的直接關(guān)系來合理區(qū)分那些消耗是變動成本，那些是固定成本。因為技術(shù)測定過程復(fù)雜且成本過高，所以一般較少采用。任務(wù)一成本性態(tài)分析【知識準(zhǔn)備】3.歷史成本法歷史成本法，也稱數(shù)學(xué)分解法，是指根據(jù)過去若干周期（月或年）數(shù)據(jù)所反映的實際成本與業(yè)務(wù)量之間的依存關(guān)系。歷史成本法是混合成本分解方法中最常用的一種方法。歷史成本法可分為高低點法、散布圖法和回歸分析法。本教材主要介紹回歸分析法，回歸分析法是指根據(jù)一定期間業(yè)務(wù)量與相應(yīng)混合成本之間的歷史資料，利用微分極值原理計算出最能反映業(yè)務(wù)量與成本之間關(guān)系的回歸直線，從而確定成本性態(tài)的一種方法。具體步驟如下：（1）計算相關(guān)系數(shù)r，判斷業(yè)務(wù)量x與成本y之間的線性關(guān)系；（2）通過微分極值原理（即最小二乘法原理），算出回歸直線方程中的固定成本a和單位變動成本b；（3）將求出的a、b代入直線方程y=a+bx中，得出成本性態(tài)分析模型。任務(wù)一成本性態(tài)分析【任務(wù)目標(biāo)】1.了解成本性態(tài)分析方法；2.掌握Excel中散點圖簡單相關(guān)分析；3.掌握利用函數(shù)開展的相關(guān)分析；4.能利用數(shù)據(jù)分析工具來確定相關(guān)系數(shù)。任務(wù)一成本性態(tài)分析【任務(wù)資料】福源公司是一家生產(chǎn)制造企業(yè)，自2010年以來，公司主要經(jīng)營的產(chǎn)品是A、B、C三種產(chǎn)品。福源公司2010年至2021年A、B、C三種主營業(yè)務(wù)產(chǎn)品的總產(chǎn)量和總成本如表9-1所示。A產(chǎn)品B產(chǎn)品C產(chǎn)品年份產(chǎn)量（件）總成本（萬元）產(chǎn)量（件）總成本（萬元）產(chǎn)量（件）總成本（萬元）20102262105051938667010584172720111716944513295963100381656201246870601443707879014972013105001784510176115631882222162014107102523810386164921903227092015903024523870616015173522662201611550261581122617105198722771201711130276451080618097194522870201813230325841290621389215523199201910710282101038618473190322907202011445302891112119859197673046202193452499690219664176672693202212000

10000

18000

任務(wù)一成本性態(tài)分析【任務(wù)資料】要求：1.分別利用散點圖、函數(shù)、數(shù)據(jù)分析分析福源公司2010年至2021年主營業(yè)務(wù)產(chǎn)品（A、B、C）的產(chǎn)量與總成本數(shù)據(jù)之間的關(guān)系（相關(guān)系數(shù)）。2.利用回歸分析方法分析福源公司2010年至2021年主營業(yè)務(wù)產(chǎn)品（A、B、C）的產(chǎn)量與總成本數(shù)據(jù)之間的關(guān)系（a和b的值）。3.預(yù)測福源公司2022年主營業(yè)務(wù)產(chǎn)品成本并計算主營業(yè)務(wù)產(chǎn)品成本的構(gòu)成。任務(wù)一成本性態(tài)分析【任務(wù)操作】

一、數(shù)據(jù)輸入設(shè)計1.打開Excel，點擊左上方保存按鈕，將Excel工作薄另存為“本量利分析.xlsm”,如圖9-1所示；【任務(wù)操作】

2.再將sheet1工作表重命名為“成本分析與預(yù)測”,完成建立“成本分析與預(yù)測”工作表,如圖9-2所示；【任務(wù)操作】

3.最后，在“成本分析與預(yù)測”工作表中錄入2010年至2021主營業(yè)務(wù)產(chǎn)品的產(chǎn)量和總成本的相關(guān)數(shù)據(jù)，錄入完后如圖9-3所示?！救蝿?wù)操作】

二、通過相關(guān)分析方法分析福源公司2010年至2021年主營業(yè)務(wù)產(chǎn)品（A、B、C）的產(chǎn)量與總成本數(shù)據(jù)之間的關(guān)系。（一）福源公司2010年至2021年A產(chǎn)品的產(chǎn)量與總成本之間的相關(guān)關(guān)系1.采用散點圖進行簡單相關(guān)分析（1）選擇圖表類型。單擊鼠標(biāo)左鍵選中單元格區(qū)域A3：C16，在“插入”選項卡的“圖表”組中，單擊“散點圖”下方箭頭，出現(xiàn)散點圖的幾種類型，如圖9-4所示。【任務(wù)操作】

（2）選擇“帶平滑線和數(shù)據(jù)標(biāo)記的散點圖”,隨即彈出帶平滑線和數(shù)據(jù)標(biāo)記的散點圖，再點擊圖表布局旁邊的下拉箭頭選中布局8，如圖9-5所示?！救蝿?wù)操作】

（3）編輯散點圖。雙擊散點圖中的“圖表標(biāo)題”即可進行編輯，把標(biāo)題修改為“A產(chǎn)品成本分析與預(yù)測”,然后雙擊散點圖中的橫軸進行編輯，彈出設(shè)置坐標(biāo)軸格式的窗口，在其中進行編輯。在坐標(biāo)軸選項的最小值中輸入“2010”,在坐標(biāo)軸最大值中輸入“2022”,在主要刻度單位輸入“2.0”,在次要刻度單位輸入“0.4”，即得圖9-6?！救蝿?wù)操作】

2.采用函數(shù)進行簡單相關(guān)分析（1）計算相關(guān)系數(shù)。在單元格B18輸入“相關(guān)系數(shù)”，在C18中輸入公式“=CORREL(B4:B15,C4:C15)”再按回車鍵，即得到福源公司2010年至2021年A產(chǎn)品的產(chǎn)量與總成本之間的相關(guān)系數(shù)為0.94671687；在C19中輸入“=PEARSON（B4:B15,C4:C15）”，再按回車鍵可以得到相同的結(jié)果，即得圖9-7?！救蝿?wù)操作】

（2）計算協(xié)方差。在單元格B20中輸入“協(xié)方差”，在C21中輸入公式“=COVARIANCE.S(B4:B15,C4:C15)”，按回車鍵，即得到福源公司2010年至2021年A產(chǎn)品的產(chǎn)量與總成本之間的協(xié)方差為35937599.18，如圖9-8所示。

【任務(wù)操作】

（3）除了上述直接輸入公式方式外，還可以通過“插入函數(shù)”命令進行計算，比如“協(xié)方差”的計算。選擇工具欄中的“公式”-“函數(shù)庫”-“插入函數(shù)”，彈出“插入函數(shù)”對話框，在“或選擇類別”后的下拉菜單中選擇“統(tǒng)計”，在選擇函數(shù)”后的下拉菜單中選擇“COVARIANCE.S”函數(shù)，如圖9-9所示?！救蝿?wù)操作】

（4）單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，單擊“Arrayl”后的折疊按鈕，然后選中單元格區(qū)域B4:B15；單擊“Array2”后的折疊按鈕，然后選中單元格區(qū)域C4:C15,如圖9-10所示。單擊“確定”按鈕，會得到相同的結(jié)果（協(xié)方差為35937599.18）?！救蝿?wù)操作】3.利用數(shù)據(jù)分析工具確定相關(guān)關(guān)系。除了用函數(shù)功能來計算兩個變量的相關(guān)系數(shù)和協(xié)方差外，Excel2010數(shù)據(jù)分析工具中提供了專門進行相關(guān)分析的工具，用于計算兩個變量的相關(guān)系數(shù)和協(xié)方差。（1）單擊“數(shù)據(jù)”選項卡，在“分析”欄中選擇“數(shù)據(jù)分析”命令，隨即彈出“數(shù)據(jù)分析”對話框，在“分析工具”中選擇“相關(guān)系數(shù)”選項，如圖9-11所示；【任務(wù)操作】

（2）單擊確定按鈕，隨即彈出“相關(guān)系數(shù)”對話框，如圖9-12所示?！救蝿?wù)操作】

（3）同樣，在“分析工具”中選擇“協(xié)方差”選項并執(zhí)行類似的步驟，則可以計算協(xié)方差，如圖9-13和圖9-14所示?！救蝿?wù)操作】

福源公司2010年至2021年A產(chǎn)品產(chǎn)量與成本數(shù)據(jù)相關(guān)分析的計算結(jié)果如圖9-15所示?！救蝿?wù)操作】

結(jié)論：從圖9-15中的結(jié)果不難看出，福源公司2010年至2021年A產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的相關(guān)系數(shù)很高，達到了0.95以上，說明兩者之間正相關(guān)的程度很強，也在一定程度上說明兩者之間有進行回歸的必要性。因為回歸分析研究的是自變量或者解釋變量對因變量的影響關(guān)系，如果參與分析的解釋變量和被解釋變量之間的相關(guān)系數(shù)很小，就沒有必要進行回歸分析。【任務(wù)操作】

（二）福源公司2010年至2021年B產(chǎn)品的產(chǎn)量與總成本之間的相關(guān)關(guān)系1.采用散點圖進行簡單相關(guān)分析我們首先運用前面介紹的方法繪制B產(chǎn)品成本分析與預(yù)測散點圖，具體操作步驟及過程不再贅述，最終形成的散點圖如圖9-16所示。通過散點圖可以比較直觀地看出，與A產(chǎn)品的走勢相同，福源公司2010年至2021年B產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間同樣存在著一定程度的正相關(guān)關(guān)系，理由是當(dāng)B產(chǎn)品產(chǎn)量變化時，其總成本數(shù)據(jù)大多呈現(xiàn)同樣的分布走勢?！救蝿?wù)操作】

2.計算相關(guān)系數(shù)和協(xié)方差我們可以運用前面插入函數(shù)和數(shù)據(jù)分析的方式進行簡單相關(guān)分析，計算得到福源公司2010年至2021年B產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的相關(guān)系數(shù)為0.912203707,協(xié)方差為23982570.75,具體計算結(jié)果如圖9-17所示?！救蝿?wù)操作】

結(jié)論：從圖9-17中的結(jié)果不難看出，福源公司2010年至2021年B產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的相關(guān)系數(shù)很高，達到了0.91以上，說明兩者之間正相關(guān)的程度很強，也在一定程度上說明兩者之間有進行回歸的必要性。因為回歸分析研究的是自變量或者解釋變量對因變量的影響關(guān)系，如果參與分析的解釋變量和被解釋變量之間的相關(guān)系數(shù)很小，就沒有必要進行回歸分析?！救蝿?wù)操作】

（三）福源公司2010年至2021年C產(chǎn)品的產(chǎn)量與總成本之間的相關(guān)關(guān)系1.采用散點圖進行簡單相關(guān)分析我們首先運用前面介紹的方法繪制C產(chǎn)品成本分析與預(yù)測散點圖，具體操作步驟及過程不再贅述，最終形成的散點圖如圖9-18所示。通過散點圖可以比較直觀地看出，與地板清潔設(shè)備、硬表面清洗機的走勢相同，福源公司2010年至2021年高壓清洗機產(chǎn)量與成本數(shù)據(jù)之間同樣存在著一定程度的正相關(guān)關(guān)系，理由是當(dāng)高壓清洗機產(chǎn)量變化時，其總成本數(shù)據(jù)大多呈現(xiàn)同樣的分布走勢。【任務(wù)操作】

2.計算相關(guān)系數(shù)和協(xié)方差然后我們運用函數(shù)開展簡單相關(guān)分析，計算得到福源公司2010年至2021年C產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的相關(guān)系數(shù)為0.946696426,協(xié)方差為2396394.273，具體計算結(jié)果如圖9-19所示?！救蝿?wù)操作】

結(jié)論：從圖9-19中的結(jié)果不難看出，福源公司2010年至2021年C產(chǎn)品與成本數(shù)據(jù)之間的相關(guān)系數(shù)很高，達到了0.94以上，說明兩者之間正相關(guān)的程度很強，也在一定程度上說明兩者之間有進行回歸的必要性。因為回歸分析研究的是自變量或者解釋變量對因變量的影響關(guān)系，如果參與分析的解釋變量和被解釋變量之間的相關(guān)系數(shù)很小，就沒有必要進行回歸分析。【任務(wù)操作】

三、通過回歸分析方法分析福源公司2010年至2021年主營業(yè)務(wù)產(chǎn)品（A、B、C）的產(chǎn)量與總成本數(shù)據(jù)之間的關(guān)系。（一）福源公司2010年至2021年A產(chǎn)品的產(chǎn)量與總成本之間的回歸關(guān)系1.利用函數(shù)進行一元線性回歸分析（1）在單元格B23中輸入“截距”,在單元格C23輸入公式“=INTERCEPT（C4:C15,B4:B15)”,按回車鍵，即可得到福源公司2010年至2021年A產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的回歸的截距。（2）在單元格B24中輸入“斜率”,在單元格C24中輸入公式“=SLOPE（C4:C15,B4:B15）”,按回車鍵，即可得到福源公司2010年至2021年A產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的回歸的斜率?！救蝿?wù)操作】

（3）在單元格B25中輸入“判定系數(shù)”,在單元格C25中輸入公式“=RSQ（C4:C15,B4:B15）”,按回車鍵，即可得到福源公司2010年至2021年A產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的回歸判定系數(shù)，計算結(jié)果如圖9-20所示?！救蝿?wù)操作】

（4）除了直接輸入公式的方法外，也可以通過“插入函數(shù)”命令加以計算。比如計算“截距”，單擊單元格B27，選擇工具欄中的“公式”-“插入函數(shù)”,彈出“插入函數(shù)”對話框，在“或選擇類別”后的下拉菜單中選擇“統(tǒng)計”，在“選擇函數(shù)”后的下拉菜單中選擇“INTERCEPT”函數(shù)，如圖9-21所示。【任務(wù)操作】

單擊“確定”按鈕，彈出“函數(shù)參數(shù)”對話框，單擊Known_y’s后的折疊按鈕，然后選中單元格區(qū)域C4:C15；單擊Known_x’s后的折疊按鈕，然后選中單元格區(qū)域B4:B15，如圖9-22所示。單擊“確定”按鈕，會得到相同的結(jié)果。斜率和判定系數(shù)的計算同樣可以通過“插入函數(shù)”命令實現(xiàn)，步驟同上。【任務(wù)操作】

（5）利用函數(shù)的數(shù)組形式。選中單元格區(qū)域B31：C35,然后輸入公式“=LINEST(C4:C15,B4:B15,1,1)”，并同時按Shift+Ctrl+Enter組合鍵執(zhí)行數(shù)組運算，得到數(shù)組運算的結(jié)果如圖9-23所示?！救蝿?wù)操作】

2.利用數(shù)據(jù)分析工具進行回歸分析（1）選擇回歸工具。在工具欄中選擇“數(shù)據(jù)”一“數(shù)據(jù)分析”命令，隨即彈出“數(shù)據(jù)分析”對話框，在“分析工具”菜單中選擇“回歸”選項，如圖9-25所示，完成后單擊“確定”按鈕，隨即彈出“回歸”對話框?！救蝿?wù)操作】

（2）設(shè)置“回歸”選項。在“回歸”對話框中，首先設(shè)置“輸入”內(nèi)容，單擊“Y值輸入?yún)^(qū)域”后面的折疊按鈕，并選取單元格區(qū)域$C$3:$C$15,同樣單擊“X值輸入?yún)^(qū)域”后面的折疊按鈕，并選取單元格區(qū)域$B$3:$B$15；因為“輸入?yún)^(qū)域”包含標(biāo)志項，所以選中“標(biāo)志”復(fù)選框；選中“置信度”復(fù)選框，并默認為95%。然后設(shè)置“輸出選項”,在“輸出區(qū)域”中選擇“$B$38”。如果選中“新工作表組”，則表示將輸出結(jié)果顯示在一個新的工作表上。接著將殘差、正態(tài)分布中的選項全部選中，以觀察殘差、標(biāo)準(zhǔn)殘差、殘差圖、線性擬合圖以及正態(tài)概率圖等信息，如圖9-26所示?！救蝿?wù)操作】

最后單擊“確定”按鈕，得到回歸結(jié)果，如圖9-27所示。【任務(wù)操作】

結(jié)論：第一部分是回歸統(tǒng)計分析的結(jié)果，MultipleR為0.94671687；RSquare為0.896272832；AdjustedRSquare為0.885900115；標(biāo)準(zhǔn)誤差為2924.403811；觀測值為12.其中，需要特別注意的是RSquare與AdjustedRSquare,分別為回歸模型的判定系數(shù)以及修正的判定系數(shù)，在很大程度上代表著模型的解釋能力，相應(yīng)的數(shù)值越大，說明模型的解釋能力越強。本例中回歸模型的判定系數(shù)以及修正的判定系數(shù)都接近0.9,說明模型解釋能力很好，或者選取的解釋變量能夠解釋被解釋變量的大部分信息。【任務(wù)操作】

結(jié)論：第二部分是回歸模型的方差分析結(jié)果，df代表自由度，模型的F統(tǒng)計值為86.40675779，模型的顯著性P值(SignificanceF)為3.09197E-06,遠小于通常具有顯著性意義的0.05,說明模型非常顯著?！救蝿?wù)操作】

結(jié)論：第三部分是回歸模型的變量系數(shù)值、變量系數(shù)的標(biāo)準(zhǔn)誤差、T統(tǒng)計量、顯著性P值以及95%的置信區(qū)間等信息。本例中產(chǎn)量的變量系數(shù)值為1.86930706、變量系數(shù)的標(biāo)準(zhǔn)誤差為0.201097555，RT統(tǒng)計量為9.295523535、顯著性P值為3.09197E-06、95%的置信區(qū)間為[1.421233783,2.317380336]；常數(shù)項的變量系數(shù)值為6137.435535、變量系數(shù)的標(biāo)準(zhǔn)誤差為1907.874992、T統(tǒng)計量為3.216896055、顯著性P值為0.009223555、95%的置信區(qū)間為[1886.425141,10388.44593]。從結(jié)果中可以看出,自變量產(chǎn)量和常數(shù)項的系數(shù)都為正,而且非常顯著（遠小于通常具有顯著性意義的0.05）,這說明固定成本為正值而且非常顯著，同時產(chǎn)量會顯著作用于成本總額，或者說變動成本對于總成本的影響是構(gòu)成重要性的。依據(jù)這一結(jié)果，我們也可以寫出福源公司2010年至2021年地板A產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的回歸方程：成本=6137.435535+產(chǎn)量*1.86930706【任務(wù)操作】

依次給出了本次回歸殘差結(jié)果輸出、正態(tài)概率輸出、殘差圖、線性擬合圖、正態(tài)概率圖等信息。殘差結(jié)果輸出圖【任務(wù)操作】

正態(tài)概率輸出圖殘差圖【任務(wù)操作】

線性擬合圖正態(tài)概率圖

【任務(wù)操作】

（二）福源公司2010年至2021年B產(chǎn)品的產(chǎn)量與總成本之間的回歸關(guān)系我們可以直接采用上述講解的兩類方法進行分析，由于本章篇幅的限制，本部分僅利用數(shù)據(jù)分析工具研究福源公司2010年至2021年B產(chǎn)品的產(chǎn)量與總成本之間的回歸關(guān)系。利用函數(shù)進行一元線性回歸分析同學(xué)們可以利用課余時間進行練習(xí)?！救蝿?wù)操作】

1.利用數(shù)據(jù)分析工具進行回歸分析（1）選擇回歸工具。在工具欄中選擇“數(shù)據(jù)”一“數(shù)據(jù)分析”命令，隨即彈出“數(shù)據(jù)分析”對話框，在“分析工具”菜單中選擇“回歸”選項，如圖9-33所示，完成后單擊“確定”按鈕，隨即彈出“回歸”對話框?！救蝿?wù)操作】

（2）設(shè)置“回歸”選項。在“回歸”對話框中，首先設(shè)置“輸入”內(nèi)容，單擊“Y值輸入?yún)^(qū)域”后面的折疊按鈕，并選取單元格區(qū)域$E$3:$E$15,同樣單擊“X值輸入?yún)^(qū)域”后面的折疊按鈕，并選取單元格區(qū)域$D$3:$D$15；因為“輸入?yún)^(qū)域”包含標(biāo)志項，所以選中“標(biāo)志”復(fù)選框；選中“置信度”復(fù)選框，并默認為95%。然后設(shè)置“輸出選項”,在“輸出區(qū)域”中選擇“$B$89”。如果選中“新工作表組”，則表示將輸出結(jié)果顯示在一個新的工作表上。接著將殘差、正態(tài)分布中的選項全部選中，以觀察殘差、標(biāo)準(zhǔn)殘差、殘差圖、線性擬合圖以及正態(tài)概率圖等信息，如圖9-34所示?！救蝿?wù)操作】

最后單擊“確定”按鈕，得到回歸結(jié)果，如圖9-35所示?！救蝿?wù)操作】

（三）福源公司2010年至2021年C產(chǎn)品的產(chǎn)量與總成本之間的回歸關(guān)系我們可以直接采用上述講解的兩類方法進行分析，由于本章篇幅的限制，本部分僅利用數(shù)據(jù)分析工具研究福源公司2010年至2021年C產(chǎn)品的產(chǎn)量與總成本之間的回歸關(guān)系。利用函數(shù)進行一元線性回歸分析同學(xué)們可以利用課余時間進行練習(xí)?！救蝿?wù)操作】

1.利用數(shù)據(jù)分析工具進行回歸分析（1）選擇回歸工具。在工具欄中選擇“數(shù)據(jù)”一“數(shù)據(jù)分析”命令，隨即彈出“數(shù)據(jù)分析”對話框，在“分析工具”菜單中選擇“回歸”選項，如圖9-41所示，完成后單擊“確定”按鈕，隨即彈出“回歸”對話框?！救蝿?wù)操作】

（2）設(shè)置“回歸”選項。在“回歸”對話框中，首先設(shè)置“輸入”內(nèi)容，單擊“Y值輸入?yún)^(qū)域”后面的折疊按鈕，并選取單元格區(qū)域$G$3:$G$15,同樣單擊“X值輸入?yún)^(qū)域”后面的折疊按鈕，并選取單元格區(qū)域$F$3:$G$15；因為“輸入?yún)^(qū)域”包含標(biāo)志項，所以選中“標(biāo)志”復(fù)選框；選中“置信度”復(fù)選框，并默認為95%。然后設(shè)置“輸出選項”,在“輸出區(qū)域”中選擇“$B$142”。如果選中“新工作表組”，則表示將輸出結(jié)果顯示在一個新的工作表上。接著將殘差、正態(tài)分布中的選項全部選中，以觀察殘差、標(biāo)準(zhǔn)殘差、殘差圖、線性擬合圖以及正態(tài)概率圖等信息，如圖9-42所示?！救蝿?wù)操作】

最后單擊“確定”按鈕，得到回歸結(jié)果，如圖9-43所示。【任務(wù)操作】

四、對福源公司2022年主營業(yè)務(wù)產(chǎn)品成本進行預(yù)測。在C16單元格中輸入公式“=C23+B16*C24”、在E16單元格中輸入公式“=E23+D16*E24”、在G16單元格中輸入公式“=G23+F16*G24”。分別假定福源公司2022年A產(chǎn)品產(chǎn)量為12000件、B產(chǎn)品產(chǎn)量為10000件、C產(chǎn)品產(chǎn)量為18000件的情況下，得到福源公司2022年主營業(yè)務(wù)產(chǎn)品成本預(yù)測結(jié)果，如圖9-49所示。【任務(wù)操作】

說明：本例中對福源公司2022年主營業(yè)務(wù)產(chǎn)品成本預(yù)測是基于前述確定的回歸分析方程的。

A產(chǎn)品：成本=6137.435535+產(chǎn)量*1.86930706B產(chǎn)品：成本=3588.365759+產(chǎn)量*1.242412868C產(chǎn)品：成本=398.2355439+產(chǎn)量*0.124649304【任務(wù)操作】

五、計算福源公司2022年主營業(yè)務(wù)產(chǎn)品成本構(gòu)成。在C17單元格中輸入公式“=B16*C24/C16”、在E17單元格中輸入公式“=D16*E24/E16”、在G17單元格中輸入公式“=F16*G24/G16”。在分別假定福源公司2022年A產(chǎn)品產(chǎn)量為12000件、B產(chǎn)品產(chǎn)量為10000件、C產(chǎn)品產(chǎn)量為18000件的情況下，得到福源公司2022年主營業(yè)務(wù)產(chǎn)品成本構(gòu)成預(yù)測結(jié)果（變動成本占比），如圖9-50所示?！局R準(zhǔn)備】一、本量利分析的基本假設(shè)（一）成本性態(tài)分析假設(shè)成本性態(tài)分析假設(shè)是假定成本性態(tài)分析工作已經(jīng)完成，全部成本費用已經(jīng)按成本性態(tài)劃分為固定成本和變動成本兩部分。成本性態(tài)分析假設(shè)是本量利分析的基礎(chǔ)，也是本量利分析的出發(fā)點。（二）相關(guān)范圍假設(shè)1.期間假設(shè)不管是固定成本還是變動成本，固定性和變動性都是體現(xiàn)在特定期間內(nèi)，金額大小也是在特定期間內(nèi)加以計量得到的。隨著時間的推移，固定成本及其內(nèi)容會發(fā)生變化，單位變動成本及其內(nèi)容也會發(fā)生變化。2.業(yè)務(wù)量假設(shè)成本按成本性態(tài)劃分為固定成本和變動成本是在一定業(yè)務(wù)量范圍內(nèi)分析和計量的結(jié)果，當(dāng)業(yè)務(wù)量發(fā)生變化尤其是變化較大時，成本性態(tài)就有可能發(fā)生改變。任務(wù)二本量利分析【知識準(zhǔn)備】（四）產(chǎn)銷平衡假設(shè)假設(shè)當(dāng)期產(chǎn)品的生產(chǎn)量和業(yè)務(wù)量相一致，不考慮存貨變動對利潤的影響。即假設(shè)每期生產(chǎn)的產(chǎn)品總量總是能在當(dāng)期全部售出，產(chǎn)銷平衡。假設(shè)產(chǎn)銷平衡，主要是在盈虧平衡分析時不考慮存貨的影響。因為盈虧平衡是一種短期決策，僅僅考慮特定時期全部成本的收回，而存貨中包含了以前時期的成本，所以不在考慮范圍內(nèi)。任務(wù)二本量利分析【知識準(zhǔn)備】二、本量利分析的基本原理本量利分析考慮的相關(guān)因素主要包括銷售量、銷售單價、銷售收入、單位變動成本、固定成本、利潤等。這些因素之間的關(guān)系可以用下列基本公式來反映：利潤=銷售收入?總成本=銷售單價×銷售量?（變動成本+固定成本）=銷售單價×銷售量?單位變動成本×銷售量?固定成本=（銷售單價?單位變動成本）×銷售量?固定成本任務(wù)二本量利分析【知識準(zhǔn)備】三、本量利分析的相關(guān)概念邊際貢獻，也稱貢獻毛益或邊際利潤，是指產(chǎn)品的銷售收入減去變動成本之后的金額，是用來衡量企業(yè)經(jīng)濟效益的一項重要指標(biāo)。邊際貢獻通常有兩種表現(xiàn)形式：一是總額概念，稱邊際貢獻總額（Tcm），通常簡稱“邊際貢獻”，是指產(chǎn)品的銷售收入總額減去變動成本總額后的余額；二是單位概念，稱單位邊際貢獻（cm），是指產(chǎn)品的銷售單價減去單位變動成本后的余額，反映的是每一個單位產(chǎn)品的創(chuàng)利能力，即每增加一個單位產(chǎn)品銷售可提供的創(chuàng)利額。兩者用公式表示如下：邊際貢獻=銷售收入?變動成本=（銷售單價?單位變動成本）×銷售量=單位邊際貢獻×銷售量單位邊際貢獻=銷售單價?單位變動成本 任務(wù)二本量利分析【任務(wù)目標(biāo)】1.了解本量利分析常用的公式；2.學(xué)會使用Excel計算邊際貢獻、銷售利潤等相關(guān)數(shù)據(jù)；3.使用本量利分析方法為企業(yè)作出正確的決策任務(wù)二本量利分析【任務(wù)資料】本任務(wù)沿用任務(wù)一的案例，福源公司是一家生產(chǎn)制造企業(yè)，自2010年以來，公司主要經(jīng)營的產(chǎn)品是A、B、C三種產(chǎn)品。福源公司2022年A、B、C3種主營業(yè)務(wù)產(chǎn)品的總銷售收入預(yù)測數(shù)據(jù)如表9-2所示。任務(wù)二本量利分析產(chǎn)品A產(chǎn)品B產(chǎn)品C產(chǎn)品合計銷售收入2587018254300047124要求：1.請利用福源公司2022年主營業(yè)務(wù)銷售收入預(yù)測數(shù)據(jù)，結(jié)合任務(wù)一的相關(guān)結(jié)果，計算福源公司2022年邊際貢獻、銷售利潤等情況，針對每種主營業(yè)務(wù)產(chǎn)品進行點評分析?！救蝿?wù)資料】要求：2.假定在上步中計算得到的福源公司2022年主營業(yè)務(wù)利潤數(shù)據(jù)不夠理想，未達到股東預(yù)期，管理層提出出了以下3種改進方案，請在Excel2010中運用本量利分析方法幫助股東做出正確的決策。（1）不再生產(chǎn)預(yù)測產(chǎn)生虧損的A產(chǎn)品，其他產(chǎn)品（B、C）維持原生產(chǎn)量不變。（2）不再生產(chǎn)預(yù)測產(chǎn)生虧損的A產(chǎn)品，將釋放出的產(chǎn)能用于B和C的生產(chǎn)，其中合理預(yù)計B產(chǎn)品產(chǎn)量將增加百分之四十，產(chǎn)品C產(chǎn)量將增加百分之十。（3）繼續(xù)生產(chǎn)預(yù)測產(chǎn)生虧損的A產(chǎn)品，但壓縮為原定計劃的70%，將釋放出的產(chǎn)能用于B產(chǎn)品的生產(chǎn)，合理預(yù)計B產(chǎn)品產(chǎn)量將增加百分之三十六。任務(wù)二本量利分析【任務(wù)操作】一、數(shù)據(jù)輸入1.打開Excel“本量利分析.xlsm”文件,選擇一張空白工作表，重命名為“本量利分析”，完成建立“本量利分析”工作表。將A1?E1單元格合并并居中，在其中輸入“本量利分析”，字體設(shè)置為“宋體”,字號設(shè)置為“18”號，并進行加粗處理；在A2?E2單元格中分別輸入產(chǎn)品、A產(chǎn)品、B產(chǎn)品、C產(chǎn)品、總計。在A3?A8單元格中分別輸入銷售收入、銷售成本、變動成本、邊際貢獻、固定成本、銷售利潤。在B3-D3單元格中直接輸入福源公司2022年主營業(yè)務(wù)銷售收入預(yù)測數(shù)據(jù)，在E3單元格中輸入公式"=SUM（B3:D3）”如圖9-51所示?！救蝿?wù)操作】

單元格公式B4=成本分析與預(yù)測!C16C4=成本分析與預(yù)測!E16D4=成本分析與預(yù)測!G16E4=SUM(B4:D4)B5=成本分析與預(yù)測!C1