《Excel在會(huì)計(jì)與財(cái)務(wù)管理中的應(yīng)用》 教案 項(xiàng)目九 本量利分析

Excel在會(huì)計(jì)與財(cái)務(wù)管理中的應(yīng)用課程教案課程名稱：Excel在會(huì)計(jì)與財(cái)務(wù)管理中的應(yīng)用課程號(hào)：課程學(xué)分：2參考學(xué)時(shí)：32課程性質(zhì)：專業(yè)必修課適用專業(yè)：經(jīng)管類專業(yè)上課時(shí)間：授課教師：負(fù)責(zé)人：審核單位：年月日-1-一、課程基本信息課程名稱Excel在會(huì)計(jì)與財(cái)務(wù)管理中的應(yīng)用課程號(hào)課程性質(zhì)專業(yè)必修課開課部門課程負(fù)責(zé)人課程團(tuán)隊(duì)授課學(xué)期學(xué)分/學(xué)時(shí)2/32授課語言漢語課程簡(jiǎn)介Excel是使用極為廣泛的電子表格軟件，其以操作簡(jiǎn)便、功能強(qiáng)大而著稱，非常適合缺乏計(jì)算機(jī)編程語言知識(shí)和對(duì)數(shù)據(jù)處理要求不高的財(cái)務(wù)管理人員使用。本書以Excel2010為基礎(chǔ)，通過實(shí)例詳細(xì)介紹了Excel在會(huì)計(jì)電算化中的應(yīng)用，并應(yīng)用Excel對(duì)企業(yè)運(yùn)營(yíng)中的會(huì)計(jì)流程和財(cái)務(wù)管理問題進(jìn)行成因分析。本書應(yīng)用實(shí)例典型，內(nèi)容豐富，充分利用宏、MicrosofQuery組件、數(shù)據(jù)透視表組件等完成會(huì)計(jì)電算化的設(shè)計(jì)。書中各項(xiàng)目詳細(xì)介紹了實(shí)例的操作步驟，學(xué)習(xí)者只需按照書中介紹的步驟一步步地實(shí)際操作，就能完全掌握本書的內(nèi)容。授課班級(jí)

二、課程授課教案項(xiàng)目九講課主題本量利分析學(xué)時(shí)2學(xué)時(shí)教學(xué)目標(biāo)(1)理解成本性態(tài)分析和本量利分析的基本概念與理論知識(shí)；(2)掌握Excel中相關(guān)分析、回歸分析等數(shù)據(jù)統(tǒng)計(jì)分析方法的應(yīng)用；(3)學(xué)會(huì)應(yīng)用Excel解決實(shí)際案例中遇到的本量利分析問題。任務(wù)目標(biāo)(1)了解成本性態(tài)分析方法；(2)掌握Excel中利用散點(diǎn)圖進(jìn)行簡(jiǎn)單相關(guān)分析；(3)掌握利用函數(shù)開展相關(guān)分析；(4)能利用數(shù)據(jù)分析工具確定相關(guān)系數(shù)。(5)了解本量利分析常用的公式；(6)學(xué)會(huì)使用Excel計(jì)算邊際貢獻(xiàn)、銷售利潤(rùn)等相關(guān)數(shù)據(jù)；(7)使用本量利分析方法為企業(yè)做出正確的決策。教學(xué)設(shè)計(jì)授課提綱及重難點(diǎn)分析教學(xué)方法及課程思政設(shè)計(jì)教學(xué)時(shí)間任務(wù)一成本性態(tài)分析知識(shí)準(zhǔn)備一、成本定義及分類(一)成本定義成本是指企業(yè)在生產(chǎn)經(jīng)營(yíng)過程中，為達(dá)到一定目的而應(yīng)當(dāng)或可能發(fā)生的各種經(jīng)濟(jì)資源的價(jià)值的犧牲或代價(jià)。在管理會(huì)計(jì)的范疇中，成本的時(shí)態(tài)可以是過去時(shí)、現(xiàn)在完成時(shí)或?qū)頃r(shí)。這與財(cái)務(wù)會(huì)計(jì)強(qiáng)調(diào)的歷史成本概念有較大的差異。(二)成本分類(1)按經(jīng)濟(jì)用途分為制造費(fèi)用和非制造費(fèi)用。(2)按實(shí)際發(fā)生時(shí)態(tài)分為歷史成本和未來成本。二、成本性態(tài)分析(一)成本性態(tài)的含義成本性態(tài)是指在一定條件下成本總額與特定業(yè)務(wù)量之間的依存關(guān)系。按照成本與產(chǎn)量的依存關(guān)系，成本可分為三大類:固定成本、變動(dòng)成本、混合成本(二)成本性態(tài)分析定義成本性態(tài)分析是指在成本性態(tài)分類的基礎(chǔ)上，按照一定的程序和方法，將全部成本劃分為固定成本和變動(dòng)成本兩大類，并建立成本函數(shù)模型的過程。成本函數(shù)模型通常用y=a+bx來模擬，其中y為成本總額，a為固定成本總額，b為單位變動(dòng)成本，x為業(yè)務(wù)量，bx為變動(dòng)成本總額。(三)成本性態(tài)分析方法1.賬戶分析法賬戶分析法也稱會(huì)計(jì)分析法或直接分析法，是指根據(jù)各有關(guān)成本賬戶的具體內(nèi)容直接判斷成本與業(yè)務(wù)量之間的依存關(guān)系，看其是更接近固定成本還是更接近變動(dòng)成本，從而確定成本性態(tài)的一種成本分解方法。2.工程分析法工程分析法是一種比較合理的混合成本的分解方法。其根據(jù)產(chǎn)品生產(chǎn)中的投入產(chǎn)出關(guān)系，即各種材料、人工、費(fèi)用的消耗與產(chǎn)量之間的直接關(guān)系來合理區(qū)分哪些消耗是變動(dòng)成本，哪些是固定成本。因?yàn)榧夹g(shù)測(cè)定過程復(fù)雜且成本過高，所以工程分析法一般較少采用。3.歷史成本法歷史成本法也稱數(shù)學(xué)分解法，是指根據(jù)過去若干周期(月或年)數(shù)據(jù)所反映的實(shí)際成本與業(yè)務(wù)量之間的依存關(guān)系，對(duì)混合成本采用適當(dāng)?shù)臄?shù)學(xué)方法加以分解，來確定成本性態(tài)的一種成本分解方法。歷史成本法是混合成本分解方法中最常用的一種方法。根據(jù)資料利用的具體形式不同，歷史成本法可分為高低點(diǎn)法、散布圖法和回歸分析法。本項(xiàng)目主要介紹回歸分析法?；貧w分析法是指根據(jù)一定期間業(yè)務(wù)量與相應(yīng)混合成本之間的歷史資料，利用微分極值原理計(jì)算出最能反映業(yè)務(wù)量與成本之間關(guān)系的回歸直線，從而確定成本性態(tài)的一種方法?；貧w分析法是計(jì)算結(jié)果最為精準(zhǔn)的方法，其具體步如下:(1)計(jì)算相關(guān)系數(shù)r，判斷業(yè)務(wù)量x與成本y之間的線性關(guān)系。(2)通過微分極值原理(最小二乘法原理)計(jì)算出回歸直線方程中的固定成本a和單位變動(dòng)成本b。(3)將求出的a、b代入直線方程y=a+bx中，得到成本性態(tài)分析模型。任務(wù)資料福源公司是一家生產(chǎn)制造企業(yè)，自2010年以來，公司主要經(jīng)營(yíng)的產(chǎn)品是A、B、C。福源公司2010—2021年A、B、C三種主營(yíng)業(yè)務(wù)產(chǎn)品的產(chǎn)量和總成本如表9-1所示。要求:(1)分別利用散點(diǎn)圖、函數(shù)、數(shù)據(jù)分析方法分析福源公司2010-2021年主營(yíng)業(yè)務(wù)產(chǎn)品(A、B、C)的產(chǎn)量與總成本數(shù)據(jù)之間的關(guān)系(相關(guān)系數(shù))。(2)利用回歸分析方法分析福源公司2010-2021年主營(yíng)業(yè)務(wù)產(chǎn)品(A、B、C)的產(chǎn)量與總成本數(shù)據(jù)之間的關(guān)系(a和b的值)。(3)預(yù)測(cè)福源公司2022年主營(yíng)業(yè)務(wù)產(chǎn)品成本并計(jì)算主營(yíng)業(yè)務(wù)產(chǎn)品成本的構(gòu)成。任務(wù)操作一、輸入數(shù)據(jù)(1)打開Excel，單擊“保存”按鈕，將Excel工作簿另存為“本量利分析.xlsm”，如圖9-1所示。(2)將Sheet1工作表重命名為“成本分析與預(yù)測(cè)”，建立“成本分析與預(yù)測(cè)”工作表,如圖9-2所示。(3)在“成本分析與預(yù)測(cè)”工作表中輸入2010—2021年主營(yíng)業(yè)務(wù)產(chǎn)品的產(chǎn)量和總成本的相關(guān)數(shù)據(jù)，如圖9-3所示。二、通過相關(guān)分析方法分析福源公司2010—2021年主營(yíng)業(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.采用散點(diǎn)圖進(jìn)行簡(jiǎn)單相關(guān)分析(1)選擇圖表類型。選中A3:C16單元格區(qū)域，選擇“插入”選項(xiàng)卡，執(zhí)行“圖表”組中的“散點(diǎn)圖”命令，出現(xiàn)散點(diǎn)圖的幾種類型，如圖9-4所示。(2)選擇“帶平滑線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖”，隨即打開帶平滑線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖。選擇“圖表工具-設(shè)計(jì)”選項(xiàng)卡，單擊“圖表布局”組右下方的下拉箭頭，選擇布局8,如圖9-5所示。(3)編輯散點(diǎn)圖。雙擊散點(diǎn)圖中的“圖表標(biāo)題”,即可對(duì)其進(jìn)行編輯,把標(biāo)題修改為“A產(chǎn)品成本分析與預(yù)測(cè)”。雙擊散點(diǎn)圖中的橫軸，打開“設(shè)置坐標(biāo)軸格式”對(duì)話框，在“坐標(biāo)軸選項(xiàng)”的“最小值”中輸入“2010”，在“最大值”中輸入“2022”，在“主要刻度單位”中輸入“2.0”，在“次要刻度單位”中輸入“0.4”，如圖9-6所示。結(jié)論:通過散點(diǎn)圖可以比較直觀地看出，當(dāng)A產(chǎn)品產(chǎn)量變化時(shí)，其總成本數(shù)據(jù)大多呈現(xiàn)同樣的分布走勢(shì)，因此得出福源公司2010-2021年A產(chǎn)品產(chǎn)量與總成本數(shù)據(jù)之間存在著一定程度的正相關(guān)關(guān)系。2.采用函數(shù)進(jìn)行簡(jiǎn)單相關(guān)分析(1)計(jì)算相關(guān)系數(shù)。在B18單元格中輸入“相關(guān)系數(shù)”，在C18單元格中輸入公式“=CORREL(B4:B15,C4:C15)”，按Enter鍵，即得到福源公司2010-2021年A產(chǎn)品的產(chǎn)量與總成本之間的相關(guān)系數(shù)為0.94671687;在C19單元格中輸入“=PEARSON(B4:B15,C4:C15)”按Enter鍵，可以得到相同的結(jié)果，如圖9-7所示。(2)計(jì)算協(xié)方差。在B20單元格中輸入“協(xié)方差”，在C21單元格中輸入公式“=COVARIANCE.S(B4:B15,C4:C15)”，按Enter鍵，即得到福源公司2010-2021年A產(chǎn)品的產(chǎn)量與總成本之間的協(xié)方差為35937599.18，如圖9-8所示。(3)除了上述直接輸入公式方式外,還可以通過“插入函數(shù)"命令進(jìn)行計(jì)算。以“協(xié)方差的計(jì)算為例，選擇“公式”選項(xiàng)卡，執(zhí)行“函數(shù)庫”組中的“插入函數(shù)”命令，打開“插入函數(shù)”對(duì)話框，在“或選擇類別”下拉列表中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”列表框中選擇“COVARIANCE.S”函數(shù)，如圖9-9所示。(4)單擊“確定”按鈕，打開“函數(shù)參數(shù)”對(duì)話框，單擊“Arrayl”后的折疊按鈕，選中B4:B15單元格區(qū)域;單擊“Array2”后的折疊按鈕,選中C4:C15單元格區(qū)域,如圖9-10所示。單擊“確定”按鈕，會(huì)得到相同的結(jié)果(協(xié)方差為35937599.18)。3.利用數(shù)據(jù)分析工具確定相關(guān)關(guān)系除了用函數(shù)功能計(jì)算兩個(gè)變量的相關(guān)系數(shù)和協(xié)方差外，Excel2010數(shù)據(jù)分析工具中還提供了專門進(jìn)行相關(guān)分析的工具，用于計(jì)算兩個(gè)變量的相關(guān)系數(shù)和協(xié)方差。(1)選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“分析”組中的“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對(duì)話框，在“分析工具”中選擇“相關(guān)系數(shù)”，如圖9-11所示。(2)單擊“確定”按鈕，打開“相關(guān)系數(shù)”對(duì)話框，進(jìn)行圖9-12所示設(shè)置。(3)同樣，在“分析工具”中選擇“協(xié)方差”并執(zhí)行類似的步驟，則可以計(jì)算協(xié)方差，如圖9-13和圖9-14所示。結(jié)論:從圖9-15所示的結(jié)果不難看出，福源公司2010-2021年A產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的相關(guān)系數(shù)很高，達(dá)到了0.94以上，說明兩者之間正相關(guān)的程度很強(qiáng)，也在一定程度上說明兩者之間有進(jìn)行回歸的必要性。因?yàn)榛貧w分析研究的是自變量或者解釋變量對(duì)因變量的影響關(guān)系，如果參與分析的解釋變量和被解釋變量之間的相關(guān)系數(shù)很小，就沒有必要進(jìn)行回歸分析。我們可以運(yùn)用前面講解的三種方法對(duì)B、C兩種產(chǎn)品進(jìn)行成本分析與預(yù)測(cè)。(二)福源公司2010—2021年B產(chǎn)品的產(chǎn)量與總成本之間的相關(guān)關(guān)系1.采用散點(diǎn)圖進(jìn)行簡(jiǎn)單相關(guān)分析運(yùn)用前面介紹的方法繪制B產(chǎn)品成本分析與預(yù)測(cè)散點(diǎn)圖，具體操作步驟及過程不再贅述，最終形成的散點(diǎn)圖如圖9-16所示。通過散點(diǎn)圖可以比較直觀地看出，與A產(chǎn)品的走勢(shì)相同，福源公司2010-2021年B產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間同樣存在著一定程度的正相關(guān)關(guān)系，理由是當(dāng)B產(chǎn)品產(chǎn)量變化時(shí)，其總成本數(shù)據(jù)大多呈現(xiàn)同樣的分布走勢(shì)。2.計(jì)算相關(guān)系數(shù)和協(xié)方差可以運(yùn)用前面插入函數(shù)和數(shù)據(jù)分析的方式進(jìn)行簡(jiǎn)單相關(guān)分析，計(jì)算得到福源公司2010-2021年B產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的相關(guān)系數(shù)為0.912203707，協(xié)方差為23982570.75具體計(jì)算結(jié)果如圖9-17所示。結(jié)論:從圖9-17所示的結(jié)果不難看出，福源公司2010-2021年B產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的相關(guān)系數(shù)很高，達(dá)到了0.91以上，說明兩者之間正相關(guān)的程度很強(qiáng)，也在一定程度上說明兩者之間有進(jìn)行回歸的必要性。(三)福源公司2010—2021年C產(chǎn)品的產(chǎn)量與總成本之間的相關(guān)關(guān)系1.采用散點(diǎn)圖進(jìn)行簡(jiǎn)單相關(guān)分析運(yùn)用前面介紹的方法繪制C產(chǎn)品成本分析與預(yù)測(cè)散點(diǎn)圖，具體操作步及過程不再贅述，最終形成的散點(diǎn)圖如圖9-18所示。通過散點(diǎn)圖可以比較直觀地看出，與A、B產(chǎn)品的走勢(shì)相同，福源公司2010-2021年C產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間同樣存在著一定程度的正相關(guān)關(guān)系，理由是當(dāng)C產(chǎn)品產(chǎn)量變化時(shí)，其總成本數(shù)據(jù)大多呈現(xiàn)同樣的分布走勢(shì)。2.計(jì)算相關(guān)系數(shù)和協(xié)方差結(jié)論：從圖9-19所示的結(jié)果不難看出，福源公司2010—2021年C產(chǎn)品與成本數(shù)據(jù)之間的相關(guān)系數(shù)很高，達(dá)到了0.94以上，說明兩者之間正相關(guān)的程度很強(qiáng)，也在一定程度上說明兩者之間有進(jìn)行回歸的必要性。三、通過回歸分析方法分析福源公司2010—2021年主營(yíng)業(yè)務(wù)產(chǎn)品(A、B、C)的產(chǎn)量與總成本數(shù)據(jù)之間的關(guān)系(一)福源公司2010—2021年A產(chǎn)品的產(chǎn)量與總成本之間的回歸關(guān)系1.利用函數(shù)進(jìn)行一元線性回歸分析(1)在B23單元格中輸入“截距”,在C23單元格輸入公式“=INTERCEPT(C4:C15,B4:B15)"，按Enter鍵，即可得到福源公司2010-2021年A產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的回歸截距。(2)在B24單元格中輸入“斜率”，在C24單元格中輸入公式“=SLOPE(C4:C15.B4:B15)”，按Enter鍵，即可得到福源公司2010-2021年A產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的回歸斜率。(3)在B25單元格中輸入“判定系數(shù)”，在C25單元格中輸入公式“=RSQ(C4:C15.B4:B15)”，按Enter鍵，即可得到福源公司2010-2021年A產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的回歸判定系數(shù)，計(jì)算結(jié)果如圖9-20所示。(4)除了直接輸入公式的方法外，也可以通過“插入函數(shù)”命令加以計(jì)算。例如，計(jì)算“截距”，選中B27單元格，選擇“公式”選項(xiàng)卡，執(zhí)行“函數(shù)庫”中的“插入函數(shù)”命令，打開“插入函數(shù)”對(duì)話框，在“或選擇類別”下拉列表中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”列表框中選擇“INTERCEPT”函數(shù)，如圖9-21所示。(5)利用函數(shù)的數(shù)組形式進(jìn)行計(jì)算。選中B31:C35單元格區(qū)域,輸入公式“=LINEST(C4C15,B4:B15,1,1)”，并按Shift+Ctrl+Enter組合鍵執(zhí)行數(shù)組運(yùn)算，得到數(shù)組運(yùn)算的結(jié)果,如圖9-23所示。2.利用數(shù)據(jù)分析工具進(jìn)行回歸分析(1)選擇回歸工具。選擇“數(shù)據(jù)”選項(xiàng)卡,執(zhí)行“分析”組中的“數(shù)據(jù)分析”命令,打開“數(shù)據(jù)分析”對(duì)話框，在“分析工具”列表框中選擇“回歸”選項(xiàng)，如圖9-25所示，單擊“確定”按鈕，隨即打開“回歸”對(duì)話框。(2)設(shè)置“回歸”選項(xiàng)。在“回歸”對(duì)話框中首先設(shè)置“輸入”內(nèi)容,單擊“Y值輸入?yún)^(qū)域后面的折疊按鈕，選中$C$3:$C$15單元格區(qū)域:單擊“X值輸入?yún)^(qū)域”后面的折疊按鈕選中$BS3:$BS15單元格區(qū)域。因?yàn)檩斎雲(yún)^(qū)域包含標(biāo)志項(xiàng)，所以選中“標(biāo)志”復(fù)選框;選中“置信度”復(fù)選框，并默認(rèn)為95%。然后設(shè)置“輸出選項(xiàng)”，在“新輸出區(qū)域”中選中SB$38單元格區(qū)域。如果選中“新工作表組”單選按鈕，則表示將輸出結(jié)果顯示在一個(gè)新的工作表上。選中“殘差”“正態(tài)分布”中的全部復(fù)選框,以觀察殘差、標(biāo)準(zhǔn)殘差、殘差圖、線性擬合圖以及正態(tài)概率圖等信息，如圖9-26所示。(二)福源公司2010—2021年B產(chǎn)品的產(chǎn)量與總成本之間的回歸關(guān)系我們可以直接采用上述講解的兩類方法進(jìn)行分析，由于本項(xiàng)目篇幅的限制，本部分僅利用數(shù)據(jù)分析工具研究福源公司2010-2021年B產(chǎn)品的產(chǎn)量與總成本之間的回歸關(guān)系。對(duì)于利用函數(shù)進(jìn)行一元線性回歸分析，讀者可以利用課余時(shí)間進(jìn)行練習(xí)。從圖9-35中可以得到以下結(jié)論：第一部分是回歸統(tǒng)計(jì)分析的結(jié)果,MultipleR為0.912203707,RSquare為0.832115602AdjustedRSquare為0.815327162，標(biāo)準(zhǔn)誤差為2571.52361，觀測(cè)值為12。其中，需要特別注意的是RSquare與AdiustedRSquare分別為回歸模型的判定系數(shù)以及修正的判定系數(shù)，在很大程度上代表著模型的解釋能力，相應(yīng)的數(shù)值越大，說明模型的解釋能力越強(qiáng)。本例中回歸模型的判定系數(shù)以及修正的判定系數(shù)都接近0.8，說明模型解釋能力很好，或者選取的解釋變量能夠解釋被解釋變量的大部分信息。第二部分是回歸模型的方差分析結(jié)果，df代表自由度，模型的F統(tǒng)計(jì)值為49.56479657顯著性P值(SignifcanceF)為3.53996x10-5，遠(yuǎn)小于通常具有顯著性意義的0.05，說明模型非常顯著。第三部分是回歸模型的變量系數(shù)值、變量系數(shù)的標(biāo)準(zhǔn)誤差、T統(tǒng)計(jì)量、顯著性P值以及95%的置信區(qū)間等信息。本例中產(chǎn)量的變量系數(shù)值為1.242412868，變量系數(shù)的標(biāo)準(zhǔn)誤差為0.17647341，T統(tǒng)計(jì)量為7.040227026，顯著性P值為3.53996x10-5，95%的置信區(qū)間為[0.849205607,1.635620128];常數(shù)項(xiàng)的變量系數(shù)值為3588.365759，變量系數(shù)的標(biāo)準(zhǔn)誤差為1623.042855，T統(tǒng)計(jì)量為2.210887869，顯著性P值為0.051483202，95%的置信區(qū)間為[-27.99908519,7204.730603]。從結(jié)果中可以看出，自變量產(chǎn)量和常數(shù)項(xiàng)的系數(shù)都為正，而且非常顯著(遠(yuǎn)小于通常具有顯著性意義的0.05)，這說明固定成本為正值而且非常顯著，同時(shí)產(chǎn)量會(huì)顯著作用于成本總額，或者說變動(dòng)成本對(duì)于總成本的影響是構(gòu)成重要性的。依據(jù)這一結(jié)果，我們也可以寫出福源公司2010—2021年B產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的回歸方程：成本=3588.365759+產(chǎn)量×1.242412868(三)福源公司2010—2021年C產(chǎn)品的產(chǎn)量與總成本之間的回歸關(guān)系我們可以直接采用上述講解的兩類方法進(jìn)行分析，由于本項(xiàng)目篇幅的限制，本部分僅利用數(shù)據(jù)分析工具研究福源公司2010-2021年C產(chǎn)品的產(chǎn)量與總成本之間的回歸關(guān)系。對(duì)于利用函數(shù)進(jìn)行一元線性回歸分析，讀者可以利用課余時(shí)間進(jìn)行練習(xí)。(1)選擇回歸工具。選擇“數(shù)據(jù)”選項(xiàng)卡，執(zhí)行“分析”組中的“數(shù)據(jù)分析”命令打開“數(shù)據(jù)分析”對(duì)話框，在“分析工具”列表框中選擇“回歸”選項(xiàng)，如圖9-41所示，單擊“確定”按鈕，打開“回歸”對(duì)話框。(2)設(shè)置“回歸”選項(xiàng)。在“回歸”對(duì)話框中首先設(shè)置“輸入”內(nèi)容，單擊“Y值輸入?yún)^(qū)域”后面的折疊按鈕，并選中$G$3:$G$15單元格區(qū)域;單擊“X值輸入?yún)^(qū)域”后面的折疊按鈕，并選中$FS3:SGS15單元格區(qū)域。結(jié)論：第一部分是回歸統(tǒng)計(jì)分析的結(jié)果,MultipleR為0.946696426,RSquare為0.896234123,AdjustedRSquare為0.8858575535，標(biāo)準(zhǔn)誤差為195.0459565，觀測(cè)值為12。其中，需要特別注意的是RSquare與AdiustedRSquare分別為回歸模型的判定系數(shù)以及修正的判定系數(shù)，在很大程度上代表著模型的解釋能力，相應(yīng)的數(shù)值越大，說明模型的解釋能力越強(qiáng)。本例中回歸模型的判定系數(shù)以及修正的判定系數(shù)都接近0.9，說明模型解釋能力很好，或者選取的解釋變量能夠解釋被解釋變量的大部分信息。第二部分是回歸模型的方差分析結(jié)果，df代表自由度，模型的F統(tǒng)計(jì)值為86.37079436顯著性P值(SignifcanceF)為3.0978x10-6，遠(yuǎn)小于通常具有顯著性意義的0.05,說明模型非常顯著。第三部分是回歸模型的變量系數(shù)值、變量系數(shù)的標(biāo)準(zhǔn)誤差、T統(tǒng)計(jì)量、顯著性P值以及95%的置信區(qū)間等信息。本例中產(chǎn)量的變量系數(shù)值為0.124649304，變量系數(shù)的標(biāo)準(zhǔn)誤差為0.013412397，RT統(tǒng)計(jì)量為9.293588885，顯著性P值為3.0978x10-6，95%的置信區(qū)間為[0.094764621,0.154533987];常數(shù)項(xiàng)的變量系數(shù)值為398.2355439，變量系數(shù)的標(biāo)準(zhǔn)誤差為232.6468712,T統(tǒng)計(jì)量為1.711759723，顯著性P值為0.117723383，95%的置信區(qū)間為[-120.1339887.916.6050765]。從結(jié)果中可以看出，自變量產(chǎn)量和常數(shù)項(xiàng)的系數(shù)都為正,而且非常顯著(遠(yuǎn)小于通常具有顯著性意義的0.05)，這說明固定成本為正值而且非常顯著，同時(shí)產(chǎn)量會(huì)顯著作用于成本總額，或者說變動(dòng)成本對(duì)于總成本的影響是構(gòu)成重要性的。依據(jù)這一結(jié)果，我們也可以寫出福源公司2010—2021年C產(chǎn)品產(chǎn)量與成本數(shù)據(jù)之間的回歸方程：成本=398.2355439+產(chǎn)量×0.124649304四、對(duì)福源公司2022年主營(yíng)業(yè)務(wù)產(chǎn)品成本進(jìn)行預(yù)測(cè)在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íng)業(yè)務(wù)產(chǎn)品成本預(yù)測(cè)結(jié)果，如圖9-49所示。說明：本例中對(duì)福源公司2022年主營(yíng)業(yè)務(wù)產(chǎn)品成本預(yù)測(cè)基于前述確定的回歸分析方程。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在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íng)業(yè)務(wù)產(chǎn)品成本構(gòu)成預(yù)測(cè)結(jié)果(變動(dòng)成本占比)。五、計(jì)算福源公司2022年主營(yíng)業(yè)務(wù)產(chǎn)品成本構(gòu)成通過計(jì)算發(fā)現(xiàn)，福源公司2022年A產(chǎn)品變動(dòng)成本占比為78.52%，B產(chǎn)品變動(dòng)成本占比為77.59%，C產(chǎn)品變動(dòng)成本占比為84.93%。福源公司2022年三種主營(yíng)業(yè)務(wù)產(chǎn)品的變動(dòng)成本占比都比較高。37min任務(wù)二本量利分析知識(shí)準(zhǔn)備一、本量利分析的基本假設(shè)(一)成本性態(tài)分析假設(shè)成本性態(tài)分析假設(shè)是假定成本性態(tài)分析工作已經(jīng)完成，全部成本費(fèi)用已經(jīng)按成本性態(tài)劃分為固定成本和變動(dòng)成本兩部分。成本性態(tài)分析假設(shè)是本量利分析的基礎(chǔ)，也是本量利分析的出發(fā)點(diǎn)。(二)相關(guān)范圍假設(shè)1.期間假設(shè)不管是固定成本還是變動(dòng)成本，固定性和變動(dòng)性都體現(xiàn)在特定期間內(nèi)，金額大小也是在特定期間內(nèi)加以計(jì)量得到的。隨著時(shí)間的推移，固定成本及其內(nèi)容會(huì)發(fā)生變化，單位變動(dòng)成本及其內(nèi)容也會(huì)發(fā)生變化。2.業(yè)務(wù)量假設(shè)成本按成本性態(tài)劃分為固定成本和變動(dòng)成本是在一定業(yè)務(wù)量范圍內(nèi)分析和計(jì)量的結(jié)果，當(dāng)業(yè)務(wù)量發(fā)生變化尤其是變化較大時(shí)，成本性態(tài)就有可能發(fā)生改變。(三)模型線性假設(shè)1.固定成本不變假設(shè)本量利分析中的模型線性假設(shè)首先是固定成本不變，用模型表示為y=a。也就是說，在企業(yè)經(jīng)營(yíng)能力的相關(guān)范圍內(nèi)，固定成本保持不變，即在一定期間和業(yè)務(wù)量范圍內(nèi)固定成本的曲線為一條水平線。2.單位變動(dòng)成本不變假設(shè)與固定成本不變假設(shè)近似,單位變動(dòng)成本不變假設(shè)也是在一定的相關(guān)范圍內(nèi)保持不變。單位變動(dòng)成本與業(yè)務(wù)量之間的完全線性關(guān)系用模型表示為y=bx。基于完全線性假設(shè)，變動(dòng)成本的曲線表現(xiàn)為一條從原點(diǎn)出發(fā)的直線，該直線的斜率就是單位變動(dòng)成本。3.銷售單價(jià)不變假設(shè)假設(shè)銷售單價(jià)也在相關(guān)范圍內(nèi)保持不變，這樣銷售收入與銷售量之間也呈線性關(guān)系，用模型表示為y=px(p為銷售單價(jià))。(四)產(chǎn)銷平衡假設(shè)假設(shè)當(dāng)期產(chǎn)品的生產(chǎn)量和業(yè)務(wù)量相一致，不考慮存貨變動(dòng)對(duì)利潤(rùn)的影響，即假設(shè)每期生產(chǎn)的產(chǎn)品總量總是能在當(dāng)期全部售出，產(chǎn)銷平衡。假設(shè)產(chǎn)銷平衡，主要是在盈虧平衡分析時(shí)不考慮存貨的影響。因?yàn)橛澠胶馐且环N短期決策，僅僅考慮特定時(shí)期全部成本的收回，而存貨中包含了以前時(shí)期的成本，所以不在考慮范圍內(nèi)。二、本量利分析的基本原理本量利分析考慮的相關(guān)因素主要包括銷售量、銷售單價(jià)、銷售收入、單位變動(dòng)成本、固定成本、利潤(rùn)等。這些因素之間的關(guān)系可以用下列基本公式來反映:利潤(rùn)=銷售收入-總成本=銷售單價(jià)x銷售量-(變動(dòng)成本+固定成本)=銷售單價(jià)x銷售量-單位變動(dòng)成本x銷售量-固定成本=(銷售單價(jià)-單位變動(dòng)成本)x銷售量-固定成本三、本量利分析的相關(guān)概念邊際貢獻(xiàn)也稱貢獻(xiàn)毛益或邊際利潤(rùn)，是指產(chǎn)品的銷售收入減去變動(dòng)成本之后的金額，是用來衡量企業(yè)經(jīng)濟(jì)效益的一項(xiàng)重要指標(biāo)。邊際貢獻(xiàn)通常有兩種表現(xiàn)形式:一是總額概念，稱邊際貢獻(xiàn)總額(Tcm)，通常簡(jiǎn)稱“邊際貢獻(xiàn)”，是指產(chǎn)品的銷售收入總額減去變動(dòng)成本總額后的余額;二是單位概念，稱單位邊際貢獻(xiàn)(cm)，是指產(chǎn)品的銷售單價(jià)減去單位變動(dòng)成本后的余額，反映的是每一個(gè)單位產(chǎn)品的創(chuàng)利能力，即每增加一個(gè)單位產(chǎn)品銷售可提供的創(chuàng)利額。兩者用公式表示如下：邊際貢獻(xiàn)=銷售收入-變動(dòng)成本=(銷售單價(jià)-單位變動(dòng)成本)×銷售量=單位邊際貢獻(xiàn)×銷售量單位邊際貢獻(xiàn)=銷售單價(jià)-單位變動(dòng)成本任務(wù)資料本任務(wù)沿用任務(wù)一的案例，福源公司是一家生產(chǎn)制造企業(yè)，自2010年以來，公司主要經(jīng)營(yíng)的產(chǎn)品是A、B、C三種產(chǎn)品。福源公司2022年A、B、C三種主營(yíng)業(yè)務(wù)產(chǎn)品的總銷售收入預(yù)測(cè)數(shù)據(jù)如表9-2所示。要求：(1)利用福源公司2022年主營(yíng)業(yè)務(wù)產(chǎn)品銷售收入預(yù)測(cè)數(shù)據(jù)，結(jié)合任務(wù)一的相關(guān)結(jié)果,計(jì)算福源公司2022年的邊際貢獻(xiàn)、銷售利潤(rùn)等，針對(duì)每種主營(yíng)業(yè)務(wù)產(chǎn)品進(jìn)行點(diǎn)評(píng)分析。(2)假定在第(1)步中計(jì)算得到的福源公司2022年主營(yíng)業(yè)務(wù)利潤(rùn)數(shù)據(jù)不夠理想，未達(dá)到股東預(yù)期，管理層提出了以下三種改進(jìn)方案，在Excel2010中運(yùn)用本量利分析方法幫助股東做出正確的決策。①不再生產(chǎn)預(yù)測(cè)產(chǎn)生虧損的A產(chǎn)品，其他產(chǎn)品(B、C)維持原生產(chǎn)量不變。②不再生產(chǎn)預(yù)測(cè)產(chǎn)生虧損的A產(chǎn)品，將釋放出的產(chǎn)能用于B和C的生產(chǎn)，其中合理預(yù)計(jì)B產(chǎn)品產(chǎn)量將增加40%，C產(chǎn)品產(chǎn)量將增加10%。③繼續(xù)生產(chǎn)預(yù)測(cè)產(chǎn)生虧損的A產(chǎn)品，但壓縮為原定計(jì)劃的70%，將釋放出的產(chǎn)能用于B產(chǎn)品的生產(chǎn)，合理預(yù)計(jì)B產(chǎn)品產(chǎn)量將增加36%。任務(wù)操作一、輸入數(shù)據(jù)(1)打開Excel“本量利分析.xlsm”文件，選擇一張空白工作表，重命名為“本量利分析”，建立“本量利分析”工作表。將A1:E1單元格區(qū)域合并并居中，在其中輸入“本量利分析”，字體設(shè)置為“宋體”，字號(hào)設(shè)置為“18”號(hào)，并進(jìn)行加粗處理:在A2:E2單元格區(qū)域中分別輸入“產(chǎn)品”“A產(chǎn)品”“B產(chǎn)品”“C產(chǎn)品”“總計(jì)”。(2)在B4:E8單元格區(qū)域設(shè)置公式，如表9-3所示。(3)輸入以上內(nèi)容后，最終形成的福源公司2022年本量利分析預(yù)測(cè)結(jié)果如圖9-52所示。結(jié)論:從圖9-52中可以看出，福源公司2022年銷售利潤(rùn)預(yù)測(cè)為虧損狀態(tài)，即-99.53770731萬元，預(yù)測(cè)結(jié)果不甚理想。其中，A產(chǎn)品銷售利潤(rùn)預(yù)測(cè)為-2699.120253萬元，B產(chǎn)品銷售利潤(rùn)預(yù)測(cè)為2241.505565萬元,C產(chǎn)品銷售利潤(rùn)預(yù)測(cè)為358.0769808萬元。在資源和條件具備一定調(diào)整可能性的情況下，福源公司需要對(duì)當(dāng)前的生產(chǎn)計(jì)劃進(jìn)行必要調(diào)整，以獲得更加優(yōu)異的經(jīng)營(yíng)業(yè)績(jī)，回報(bào)股東的信任。二、方案分析(1)在第一種備選方案下，利用福源公司2022年主營(yíng)業(yè)務(wù)銷售收入預(yù)測(cè)數(shù)據(jù)，結(jié)合任務(wù)一的相關(guān)結(jié)果，計(jì)算福源公司2022年的邊際貢獻(xiàn)、銷售利潤(rùn)等。第一種備選方案為不再生產(chǎn)預(yù)測(cè)產(chǎn)生虧損的A產(chǎn)品，其他產(chǎn)品(B、C)維持原生產(chǎn)量不變。復(fù)制“本量利分析”工作表，并將其重命名為“本量利分析(方案一)”工作表，將B7:D8單元格區(qū)域中的內(nèi)容清除后合并并居中，在E7單元格復(fù)制并粘貼總固定成本數(shù)據(jù)(僅粘貼數(shù)值)，將B3:B6單元格區(qū)域內(nèi)的數(shù)值直接刪除，在E8單元格輸入“=E3-E4-本量利分析!B7”,(雖然不再生產(chǎn)A產(chǎn)品，但A產(chǎn)品產(chǎn)生的固定成本是一直存在的，所以銷售利潤(rùn)需要減去A產(chǎn)品產(chǎn)生的固定成本)，得到的最終結(jié)果如圖9-53所示。結(jié)論:從圖9-53中可以看出，福源公司2022年銷售利潤(rùn)預(yù)測(cè)仍為虧損狀態(tài)，而且號(hào)損的更多，達(dá)到了-3537.852989萬元，預(yù)測(cè)結(jié)果更加不理想。這是因?yàn)锳產(chǎn)品為虧損主營(yíng)業(yè)務(wù)