《Excel在會計與財務(wù)管理中的應(yīng)用》 教案 項目十 壞賬成因分析

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

二、課程授課教案項目十講課主題壞賬成因分析學(xué)時2學(xué)時教學(xué)目標(1)理解單因素方差分析方法的基本概念與分析的步驟；(2)掌握Excel數(shù)據(jù)分析工具中方差分析模塊的單因素分析方法的具體應(yīng)用；(3)學(xué)會應(yīng)用Excel數(shù)據(jù)分析工具中的方差分析方法對企業(yè)財務(wù)管理中的其他具體問題進行研究。任務(wù)目標(1)了解單因素方差分析；(2)學(xué)會使用Excel2010數(shù)據(jù)分析工具中的方差分析模塊單因素方差分析命令；(3)學(xué)會使用單因素方差分析方法對企業(yè)壞賬影響因素進行研究。(4)理解Excel2010中可重復(fù)雙因素方差分析的基本原理；(5)掌握可重復(fù)雙因素方差分析方法在Excel2010中的實現(xiàn)；(6)學(xué)會應(yīng)用這些統(tǒng)計分析方法解決實際中遇到的財務(wù)管理問題。教學(xué)設(shè)計授課提綱及重難點分析教學(xué)方法及課程思政設(shè)計教學(xué)時間任務(wù)一單因素影響分析知識準備1.單因素方差的基本概念試驗中要考察的指標稱為試驗指標，影響試驗指標的條件稱為因素，因素所處的狀態(tài)稱為水平，若試驗中只有一個因素改變則稱為單因素試驗，若有兩個因素改變則稱為雙因素試驗，若有多個因素改變則稱為多因素試驗。方差分析就是對試驗數(shù)據(jù)進行分析，檢驗方差相等的多個正態(tài)總體均值是否相等，進而判斷各因素對試驗指標的影響是否顯著。方差分析根據(jù)影響試驗指標條件的個數(shù)可以區(qū)分為單因素方差分析、雙因素方差分析和多因素方差分析。在方差分析中，我們將要考察的對象的某種特征稱為試驗指標，影響試驗指標的條件稱為因素。因素可分為兩類，一類是人們可以控制的(如原材料、設(shè)備、學(xué)歷、專業(yè)等因素)，另一類是人們無法控制的(如員工素質(zhì)與機遇等因素)。下面所討論的因素都是指可控制因素。每個因素又有若干個狀態(tài)可供選擇，因素可供選擇的每個狀態(tài)稱為該因素的水平。如果在一項試驗中只有一個因素在改變，則稱為單因素試驗;如果多于一個因素在改變，則稱為多因素試驗。因素常用大寫字母A，B，C，…表示，因素A的水平用A1、A2、A3表示。2.假設(shè)前提(1)各個觀察值是獨立的，即各組觀察數(shù)據(jù)是從相互獨立的總體中抽取的。(2)每個總體都應(yīng)服從正態(tài)分布且方差相等，即各組觀測數(shù)據(jù)是從具有相同方差的正態(tài)分布總體中抽取的簡單隨機樣本。3.單因素方差分析的數(shù)據(jù)結(jié)構(gòu)在單因素試驗中，假設(shè)因素A共有r個水平，r表示單因素的分類數(shù)目，每個水平的樣本容量為n，則共有nr個觀察值，單因素試驗的結(jié)果以r行n列表示，構(gòu)成單因素分析的數(shù)據(jù)結(jié)構(gòu)。4.單因素方差分析的步驟(1)提出假設(shè)。設(shè)因素有不同水平A1,A2,,Ar,各水平對應(yīng)的總體服從正態(tài)分布N，在水平Ar,進行Nr,次試驗。假定所有試驗都是獨立的，因為在水平的樣本觀測值與總體服從相同的分布，如果因素A對試驗結(jié)果影響不顯著，則所有樣本觀測值都可以看作來自同一總體，即各自變量取值分類組的均值相等。提出假設(shè):原假設(shè)H0:U1=U2=…=Ur,即因素A對觀測變量無顯著影響。備擇假設(shè)H1:U1=U2=…=Ur,不全相等，即因素A對觀測變量有顯著影響。若拒絕原假設(shè)，則表示自變量對因變量有顯著影響；若接受原假設(shè)，則表示自變量對因變量沒有顯著影響。(2)構(gòu)造檢驗統(tǒng)計量。(3)離差平方和。①總離差平方和也稱總平方和(SumofSquaresforTotal,SST)，反映全部試驗數(shù)據(jù)之間的離散狀況，是全部觀察值與總平均值的離差平方和。②組間離差平方和也稱因素A平方和(SumofSquaresforFactorA,SSA)，反映各個總體的樣本均值之間的差異程度，即每組數(shù)據(jù)均值和總平均值之間的離差平方和。③組內(nèi)離差平方和也稱誤差項離差平方和(SumofSquaresforError,SSE)，反映每個樣本各觀測值之間的離散狀況，即組內(nèi)數(shù)據(jù)和組內(nèi)平均值之間的隨機誤差。(4)判斷與結(jié)論。有兩種方法可以用來判定是否接受原假設(shè)H0。任務(wù)資料福源公司是一家生產(chǎn)制造企業(yè)，自2010年以來，公司主要經(jīng)營的產(chǎn)品是A、B、C。公司2010-2021年A、B、C三種主營業(yè)務(wù)產(chǎn)品的壞賬數(shù)據(jù)如表10-1所示。要求：利用圖表分析方法分析福源公司2010—2021年主營業(yè)務(wù)產(chǎn)品壞賬數(shù)據(jù)。任務(wù)操作一、輸入數(shù)據(jù)打開Excel，將工作簿保存為“壞賬成因.xlsm”。將Sheetl工作表重命名為“壞賬成因分析”，建立“壞賬成因分析”工作表，在其中輸入福源公司2010-2021年主營業(yè)務(wù)產(chǎn)品壞賬數(shù)據(jù)，如圖10-1所示。二、通過圖表分析方法分析福源公司2010—2021年主營業(yè)務(wù)產(chǎn)品壞賬數(shù)據(jù)這里使用“帶平滑線和數(shù)據(jù)標記的散點圖”和“雷達圖”比較適合。(一)帶平滑線和數(shù)據(jù)標記的散點圖(1)選中A2:D14單元格區(qū)域,選擇“插入”選項卡,執(zhí)行“圖表”組“散點圖”中的“帶平滑線和數(shù)據(jù)標記的散點圖”命令,如圖10-2所示。(2)打開基于福源公司2010—2021年主營業(yè)務(wù)產(chǎn)品壞賬數(shù)據(jù)的帶平滑線和數(shù)據(jù)標記的散點圖，如圖10-3所示。(3)將標題修改為“分產(chǎn)品壞賬成因分析”,單擊分產(chǎn)品壞賬成因分析圖中的橫坐標軸，打開如圖10-4所示的“設(shè)置坐標軸格式”對話框，在“坐標軸選項”的“最小值”中輸入2010.0，在“最大值”中輸入2021.0，在“主要刻度單位”中輸入2.0，在“次要刻度單位”中輸入1.0。(4)單擊“關(guān)閉”按鈕，最終形成的分產(chǎn)品壞賬成因分析圖如圖10-5所示。結(jié)論:從帶平滑線和數(shù)據(jù)標記的散點圖中可以較為直觀地看出，A、B、C三種產(chǎn)品之間還是存在一定差異的,尤其是自2010年以來,B產(chǎn)品壞賬金額要高于A產(chǎn)品壞賬金額,再高于C產(chǎn)品壞賬金額。(二)雷達圖(1)選中A2:D14單元格區(qū)域，選擇“插入”選項卡，執(zhí)行“圖表”組中“其他圖表下的“雷達圖”命令，如圖10-6所示。(2)打開如圖10-7所示的基于福源公司2010-2021年A、B、C三種主營業(yè)務(wù)產(chǎn)品壞賬數(shù)據(jù)的雷達圖。三、通過單因素方差分析方法分析福源公司2010—2021年A、B、C三種主營業(yè)務(wù)產(chǎn)品壞賬數(shù)據(jù)與主營業(yè)務(wù)產(chǎn)品品種之間的關(guān)系(1)選擇單因素方差分析工具。選擇“數(shù)據(jù)”選項卡,執(zhí)行“分析”組中的“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對話框，在“分析工具”列表框中選擇“方差分析:單因素方差分析”選項，如圖10-8所示。(2)單擊“確定”按鈕,打開“方差分析:單因素方差分析”對話框。首先設(shè)置“輸入’內(nèi)容，單擊“輸入?yún)^(qū)域”后面的折疊按鈕，并選中$B$2:$D$14單元格區(qū)域。因為本例中研究的是A、B、C三種產(chǎn)品之間的方差分析，而且三者數(shù)據(jù)都以“列”的形式在Excel2010中展示,所以在“分組方式”選項組中要選中“列”復(fù)選框。(3)單擊“確定”按鈕，得到針對A、B、C三種產(chǎn)品之間的單因素方差分析結(jié)果，如圖10-10所示。結(jié)論:“方差分析:單因素方差分析”結(jié)果圖包括兩部分，第一部分是參與方差分析的變量(包括A產(chǎn)品壞賬金額、B產(chǎn)品壞賬金額和C產(chǎn)品壞賬金額)的描述性分析統(tǒng)計結(jié)果，其中A產(chǎn)品壞賬金額觀測樣本數(shù)為12，總和為10579.92，平均值為881.66，方差為119924.9255;B產(chǎn)品壞賬金額觀測樣本數(shù)為12，總和為13199.76，平均值為1099.98，方差為229170.1156;C產(chǎn)品壞賬金額觀測樣本數(shù)為12，總和為7488.25，平均值為624.0208333，方差為20830.83475。37min任務(wù)二可重復(fù)雙因素方差分析知識準備一、雙因素方差分析的類型(1)無重復(fù)的雙因素方差分析:假定因素A和因素B的效應(yīng)之間是相互獨立的，不存在交互關(guān)系，也稱無交互作用的雙因素方差分析。(2)可重復(fù)的方差分析:假定A、B兩個因素不是獨立的，而是相互起作用的，并且兩個因素共同起作用的結(jié)果不是其各自作用的簡單相加，而是會產(chǎn)生一個新的效應(yīng)(如效果會成倍增加)，也稱有交互作用的雙因素方差分析。本任務(wù)集中介紹可重復(fù)雙因素方差分析的理論與操作實例?？芍貜?fù)的雙因素方差分析的數(shù)據(jù)結(jié)構(gòu)假設(shè)兩個因素分別是A和B，因素A共有n個水平，因素B共有k個水平，在水平組合(Ai,Bj)下的試驗結(jié)果Xij,服從N,i=1,…,n;B=1,…,k。假設(shè)這些試驗結(jié)果相互獨立,要對兩個因素的交互作用進行分析，每個水平組合下需要至少進行兩次試驗，若在每個水平組合(Ai,Bj)下重復(fù)t次試驗，則每次試驗的觀測值用Xijr(r=1,…,t)表示。三、可重復(fù)的雙因素方差分析的步驟(一)提出假設(shè)與無重復(fù)的雙因素方差分析的模型基本一樣，只是可重復(fù)的雙因素方差分析需要考慮兩個因素之間的交互作用，因此需要提出假設(shè)以檢驗兩個因素之間的交互效應(yīng)。在可重復(fù)的雙因素方差分析中,如果用U表示均值,則U1,U2,…,Un,分別表示因素A分類組的均值,U1,U2,…,Uk分別表示因素B分類組的均值;用Pij表示因素A的第i水平與因素B的第j水平的交互效應(yīng)。在可重復(fù)的雙因素方差分析中，要檢驗的假設(shè)有三個，即分別對因素A和因素B以及因素A、B之間的交互效應(yīng)提出假設(shè)。1.對因素A的假設(shè)原假設(shè)H01：U1=U2=…=Un，即因素A對觀測變量無顯著影響。備擇假設(shè)H11：U1,U2,…,Un不全相等，即因素A對觀測變量有顯著影響。2.對因素B的假設(shè)原假設(shè)H02：U1=U2=…=Uk，即因素B對觀測變量無顯著影響。備擇假設(shè)H12：U1,U2,…,Uk不全相等，即因素B對觀測變量有顯著影響。3.對因素A和因素B的交互效應(yīng)的假設(shè)原假設(shè)H03：對一切i和j，有Pij=0，即因素A與因素B之間不存在交互效應(yīng)。備擇假設(shè)H13：對一切i和j，Pij不全為零，即因素A與因素B之間存在交互效應(yīng)。(二)構(gòu)造檢驗統(tǒng)計量由于相互作用的存在，可重復(fù)的雙因素方差分析要比無重復(fù)的雙因素方差分析多一個交互作用項平方和，此時總離差平方和SST將被分解為四部分:SSA、SSB、SSAB和SSE，分別代表因素A的組間差異，因素B的組間差異，因素A、B的交互效應(yīng)和隨機誤差的離散狀況。(三)判斷與結(jié)論與無重復(fù)的雙因素方差分析一樣，在可重復(fù)的雙因素方差分析中，既可以將統(tǒng)計量的值F與臨界值Fa,進行比較，從而做出拒絕或接受原假設(shè)的決策；也可以利用F值計算出P值，再進行判斷。其具體判斷方法如下:利用F值進行判斷:若FA≥Fa，則拒絕原假設(shè)H01，表明因素A對觀察值有顯著影響,否則接受原假設(shè)H01；若FB≥Fa,則拒絕原假設(shè)H02,表明因素B對觀察值有顯著影響,否則接受原假設(shè)H02；若FAB≥Fa，則拒絕原假設(shè)H03，表明因素A、B的交互效應(yīng)對觀察值有顯著影響，否則接受原假設(shè)H:。同理，利用P值進行判斷:當(dāng)P<a時，拒絕H0;當(dāng)P>a時，接受原假設(shè)H0。任務(wù)資料福源公司是一家生產(chǎn)制造企業(yè)，自2010年以來，公司主要經(jīng)營的產(chǎn)品是A、B、C。2010-2021年A、B、C三種主營業(yè)務(wù)產(chǎn)品分客戶所在地區(qū)的壞賬數(shù)據(jù)如表10-2所示（完整表格查看教材297頁）。任務(wù)操作一、輸入數(shù)據(jù)使用Excel2010打開“壞賬成因.xlsm”工作簿，新建或者選擇其中一張空白工作表并重命名為“壞賬成因分析(雙因素)”，建立“壞賬成因分析(雙因素)”工作表，在其中輸入福源公司2010-2021年主營業(yè)務(wù)產(chǎn)品分客戶所在地區(qū)壞賬數(shù)據(jù)，如圖10-11所示。二、通過圖表分析方法分析福源公司2010—2021年主營業(yè)務(wù)產(chǎn)品分客戶所在地區(qū)壞賬數(shù)據(jù)這里主要介紹Excel2010中數(shù)據(jù)透視圖的用法。(1)在“壞賬成因分析(雙因素)”工作表中選中A2:E37單元格區(qū)域，選擇“插入選項卡執(zhí)行“表格”組中“數(shù)據(jù)透視表”下的“數(shù)據(jù)透視圖”命令，如圖10-12所示。(2)打開“創(chuàng)建數(shù)據(jù)透視表及數(shù)據(jù)透視圖”對話框，選中“新工作表”(命名為“數(shù)據(jù)透視圖”)單選按鈕，單擊“確定”按鈕，如圖10-13所示。(3)打開“數(shù)據(jù)透視表字段列表”對話框,把“客戶所在地區(qū)”移到“軸字段(分類)”列表框,把“A產(chǎn)品壞賬金額(萬元)”“B產(chǎn)品壞賬金額(萬元)”“C產(chǎn)品壞賬金額(萬元)”移到“數(shù)值”列表框，如圖10-14所示。(4)在“數(shù)值”列表框中選擇“求和項：A產(chǎn)品壞賬金額(萬元)”，選擇如圖10-15所示的“值字段設(shè)置”命令。(5)打開如圖10-16所示的“值字段設(shè)置”對話框,“計算類型”選擇“平均值”，即設(shè)置為“平均值項:A產(chǎn)品壞賬金額(萬元)”。按照同樣的操作方式，把“求和項:B產(chǎn)品壞賬金額(萬元)”設(shè)置為“平均值項:B產(chǎn)品壞賬金額(萬元)”，把“求和項:C產(chǎn)品壞賬金額(萬元)”設(shè)置為“平均值項:C產(chǎn)品壞賬金額(萬元)”。(6)設(shè)置完畢后，即顯示如圖10-17所示的基于福源公司2010-2021年A產(chǎn)品壞賬金額分客戶所在地區(qū)平均值項、B產(chǎn)品壞賬金額分客戶所在地區(qū)平均值項、C產(chǎn)品壞賬金額分客戶所在地區(qū)平均值項的數(shù)據(jù)透視圖。結(jié)論:從數(shù)據(jù)透視圖中可以非常明顯地看出，從主營業(yè)務(wù)產(chǎn)品品種來看，東部地區(qū)中B產(chǎn)品壞賬金額平均值項要高于A產(chǎn)品壞賬金額平均值項，再高于C產(chǎn)品壞賬金額平均值項;西部地區(qū)中B產(chǎn)品壞賬金額平均值項要高于A產(chǎn)品壞賬金額平均值項，再高于C產(chǎn)品壞賬金額平均值項。三、通過可重復(fù)雙因素方差分析方法分析福源公司2010—2021年A產(chǎn)品壞賬金額、B產(chǎn)品壞賬金額、C產(chǎn)品壞賬金額等主營業(yè)務(wù)產(chǎn)品壞賬數(shù)據(jù)與主營業(yè)務(wù)產(chǎn)品品種、客戶所在地區(qū)之間的關(guān)系(1)選擇可重復(fù)雙因素方差分析工具。選擇“數(shù)據(jù)”選項卡，執(zhí)行“分析”組中的“數(shù)據(jù)分析”命令，打開“數(shù)據(jù)分析”對話框，在“分析工具”列表框中選擇“方差分析:可重復(fù)雙因素分析”選項，如圖10-18所示。(2)單擊“確定”按鈕,打開“方差分析:可重復(fù)雙因素分析”對話框。首先設(shè)置“輸入”內(nèi)容，單擊“輸入?yún)^(qū)域”后面的折疊按鈕，并選中$B$2:$E$37單元格區(qū)域。在本任務(wù)中研究的是A產(chǎn)品壞賬金額、B產(chǎn)品壞賬金額、C產(chǎn)品壞賬金額以及分客戶所在地區(qū)之間的方差分析，其中三個主營業(yè)務(wù)品種都以不同的列進行展示，稱為“列數(shù)據(jù)”;客戶所在的不同地區(qū)都以不同的行展示，稱為“樣本數(shù)據(jù)”，每個樣本數(shù)據(jù)都有12行數(shù)據(jù)參與分析,所以在“每一樣本的行數(shù)”中輸入“12”。(3)單擊“確定”按鈕，得到針對分客戶所在地區(qū)的A產(chǎn)品壞賬金額、B產(chǎn)品壞賬金額、C產(chǎn)品壞賬金額之間的“方差分析:可重復(fù)雙因素分析”結(jié)果，如圖10-20和圖10-21所示。列差異反映的是不同主營業(yè)務(wù)產(chǎn)品品種對于壞賬金額的影響，可以發(fā)現(xiàn)列的F值為9.581913576，顯著性P值為0.000157021，遠遠小于設(shè)定的顯著性標準0.05，說明要顯著地拒絕原假設(shè)，即不同的主營業(yè)務(wù)產(chǎn)品品種對于壞賬金額的產(chǎn)生是有著顯著影響的。交互差異反映的是不同客戶所在地區(qū)、不同主營業(yè)務(wù)產(chǎn)品品種之間的交互作用(客戶所在地區(qū)x主營業(yè)務(wù)產(chǎn)品品種)對于壞賬金額的影響，可以發(fā)現(xiàn)交互差異的F值為3.063430043，顯著性P值為0.020001796，小于設(shè)定的顯著性標準0.05，說明要顯著地拒絕原假設(shè)，即不同客戶所在地區(qū)、不同主營業(yè)務(wù)產(chǎn)品品種之間的交互作用對于壞賬金額的產(chǎn)生是有著顯著影響的。38min項目小結(jié)本項目主要介紹了Excel2