運用Ｅｘｃｅｌ解決管理會計問題

［摘 要］ 管理會計是服務于企業內部經營管理的會計信息系統，專為企業管理部門正確地進行最優管理決策提供有用的資料。管理會計方法靈活多樣，并大量應用現代數學方法，管理會計方法若能與Excel工具結合起來，不僅生成的信息準確，而且結果的獲得也更加便捷。本文就管理會計所經常面臨的幾個問題，探討如何運用Excel工具加以解決。

［關鍵詞］ Excel ；混合成本分解；資源配置；最優售價

［中圖分類號］F232；F234.3［文獻標識碼］A［文章編號］1673-0194（2007）12-0023-05

管理會計是服務于企業內部經營管理的會計信息系統，專為企業管理部門正確地進行最優管理決策提供有用的資料。管理會計方法靈活多樣，并大量應用現代數學方法，管理會計方法若能與Excel工具結合起來，不僅生成的信息準確，而且結果的獲得也更加便捷。以下就管理會計經常面臨的幾個問題，探討如何運用Excel工具加以解決。

一、運用Excel解決混合成本的分解問題

管理會計為向管理當局提供其所需的成本資料，就有必要將全部成本按成本特性劃分為固定成本和變動成本兩大類。但在實際工作中，有些成本卻很難被辨認為固定成本或變動成本，這些成本可以稱為“混合成本”。為適應管理的需要，混合成本必須借助一定的方法，將其中的固定成本部分及變動成本部分分解開來。

分解混合成本的方法有很多，如合同檢查法、技術估算法、高低點法、散布圖法、回歸分析法等。從分解結果的科學性、準確性而言，回歸分析法是最為理想的一種方法。傳統的回歸分析法雖然思維嚴密，但操作方法甚為煩瑣，而如果運用Excel工具，則可以很快得出結果。以下借助一個實例，就如何使用Excel分解混合成本進行探討。

【例】某廠的歷史成本數據表明，維修費在相關范圍內的變動情況如表1所示。

按照回歸分析法原理，根據歷史成本數據，以y代表一定期間混合成本（維修費）的總額，x代表業務量（機器工作小時），a代表混合成本中的固定部分，b代表混合成本中依一定比率隨業務量的變動而變動的部分，并假定經過測算，x與y之間的相關系數值接近1，說明它們之間基本上存在線性聯系，則它們之間的關系可用y = a + bx表示，然后利用下列兩式來確定a與b的數值，即：

b = （n∑xy - ∑x∑y） / [ n∑x2 -（∑x）2]

a = (∑y - b∑x) / n

運用Excel工具確定a和b的數值，需要進行以下步驟的操作：

第一步：在A2單元格中輸入“x”，在B2單元格中輸入“y”，在C2單元格中輸入“xy”，在D2單元格中輸入“ x2 ”；

第二步：在A3至A12及B3至B12單元格中輸入數據；

第三步：選定A13單元格，并單擊“自動求和”按鈕，則A3至A12單元格中的數字的總和就會自動插入到選定的A13單元格內；

第四步：選定B13單元格，并單擊“自動求和”按鈕，則B3至B12單元格中的數字的總和就會自動插入到選定的B13單元格內；

第五步：選定C3單元格，并輸入公式“=A3*B3”，然后按回車鍵；

第六步：單擊公式所在的單元格C3，然后將鼠標放置在單元格邊框的右下角（即填充柄），指針會變成“+”，然后拖動“+”到要復制的區域（C4 ∶ C12）；

第七步：單擊公式所在的單元格D3，然后將鼠標放置在單元格邊框的右下角，指針會變成“+”，然后拖動“+”到要復制的區域（D4 ∶ D12）；

第八步：在A15單元格中輸入n，在B15單元格中輸入10，在A17單元格中輸入a，在A18單元格中輸入b；

第九步：選定B18單元格，并輸入公式“=($B$15*C13-A13*B13)/($B$15*D13-A13*A13)”，然后按回車鍵，即得出b的數值為28.444 4；

第十步：選定B17單元格，并輸入公式“=(B13-$B$18*A13) / $B$15”，然后按回車鍵，即得出a的數值為2.044 4（如圖1所示）。

上述計算結果表明：混合成本（維修費）中包含28.444 4元的固定成本以及按業務量x和單位變動成本2.044 4元計算的變動成本。

二、運用Excel解決最優資源配置的問題

企業所擁有的人力、財力及物力資源往往是有限的，如何利用有限的經濟資源獲得最佳的經濟效益是任何企業都關注的問題。這個問題可以利用運籌學中的線性規劃法加以解決，舉例說明如下：

【例】某企業計劃年度準備生產甲、乙兩種產品，其有關資料如表2所示。

采用線性規劃法確定資源的有效利用，需要先確定目標函數和約束條件。

設：Z為兩種產品的邊際貢獻總額，x為甲產品的產量，y為乙產品的產量。

則目標函數為：Z=15x+20y

約束條件為：

10x+4y≤300

5x+4y≤200

4x+9y≤360

x≥0

y≥0

目標函數和約束條件中的x和y的數值，如果利用傳統的圖解法人工計算，程序非常煩瑣，特別是當變量超過兩個時。而運用Excel工具加以解決，此項工作就變得簡單易行。

第一步：將自變量、x、y、常數分別輸入A1至D1單元格內；將目標函數系數、600、1 000、0，約束條件分別輸入A2至E2單元格內；將約束條件系數、10、4、300分別輸入A3至D3單元格內；將5、4、200分別輸入B4至D4單元格內；將4、9、360分別輸入B5至D5單元格內；將1、0、0分別輸入B6至D6單元格內；將0、1、0分別輸入B7至D7單元格內；將x、y、目標函數分別輸入A8至A10單元格內。

第二步：在E3單元格中輸入公式“=B3*$B$8+C3*$B$9”，并用填充柄拉至E7（見圖2）。

第三步：在B10單元格中輸入目標函數公式“=B3*B8+C2*B9”。

第四步：選中B10，然后進入菜單欄上的“工具—規劃求解”，在對話框中輸入如下內容：將“設置目標單元格”

設置成“$B$10”，并設置成最大值；將“可變單元格”設置成“$B$8 : $B$9”；添加約束條件“$E$3≤$D$3，$E$4≤$D$4，$E$5≤$D$5，$E$6≤$D$6，$E$7≤$D$7”（見圖3），單擊求解，即得出如下內容：$B$10單元格的值為41 931，$B$8單元格的值為12.414，$B$9單元格的值為34.483，B10單元格的值為41 931，所以當x=12.414，y=34.483時，Zmax=41 931（見圖4）。

如果上例中的x和y要求是整數，則只需要在上面的求解中添加約束條件：“$B$8=整數；$B$9=整數”即可，從而可以得到$B$8單元格的值為11，$B$9單元格的值為35，$B$10單元格的值為41 600，所以當x=11，y=35時，Zmax=41 600。

以上計算結果表明：當甲產品生產11件，乙產品生產35件時，既能使企業的經濟資源得到充分利用，同時企業還能獲取最佳的經濟效益，即最多的邊際貢獻總額為41 600元。

三、運用Excel解決最優售價的問題

售價并非越高越好，因為價格與銷售量是一種反向變動的關系。最優售價是指能夠使企業獲取最大利潤時的產品售價。這里所說的最大利潤就是銷售總收入與銷售總成本的最大差額，此差額可以通過邊際收入與邊際成本求得。銷售總收入與銷售總成本發展趨勢如圖5所示。

所謂“邊際收入”(marginal revenue)，就是指在一定的銷售量的基礎上每增加一個單位銷售量所增加的銷售總收入。所謂“邊際成本”(marginal cost)，是指在一定的產量的基礎上，每增加一個單位產量所增加的總成本。當“邊際收入”等于“邊際成本”（或“邊際利潤”=0）時，能夠為企業提供最大的銷售利潤，此時的銷售單價、銷售量就是產品的最佳售價和最佳銷售量。

【例】某企業生產的丙產品單價20元，單位變動成本5元，固定成本1 000元，現在每月銷售量為200件，如果單價逐步下降，預計銷售量將逐步上升。具體資料如表3所示。

運用Excel工具確定最優售價的步驟如下：

第一步：分別將單價、銷售量、單位變動成本和固定成本總額的數據輸入各單元格內，如圖6所示。

第二步：在C4至J4單元格內分別輸入“銷售收入”、“邊際收入”、“變動成本”、“固定成本”、“銷售成本”、“邊際成本”、“邊際利潤”、“利潤”。

第三步：在C5單元格中輸入公式“=B5*C5”，并用填充柄拉至C12。

第四步：在D6單元格中輸入公式“=C6-C5”，并用填充柄拉至D12。

第五步：在E5單元格中輸入公式“=$B$14*B5”，并用填充柄拉至E12。

第六步：在F5單元格中輸入公式“=$B$15”，并用填充柄拉至F12。

第七步：在G5單元格中輸入公式“=E5+F5”，并用填充柄拉至G12。

第八步：在H6單元格中輸入公式“=G6-G5”，并用填充柄拉至H12。

第九步：在I6單元格中輸入公式“=D6-H6”，并用填充柄拉至I12。

第十步：在J5單元格中輸入公式“=C5-G5”，并用填充柄拉至J12。

輸出結果如圖7所示。圖7中的邊際收入是指銷售量每增加一個單位(50件)所增加的收入，邊際成本是指銷售量每增加一個單位(50件)所增加的成本。增加的邊際利潤是邊際收入與邊際成本的差額，若差額為正數，表示降價后增加銷售量所增加的利潤額，說明降價有利；若差額為負數，則表示降價后減少的利潤額，說明降價是不利的。

計算結果表明，單價從20元降到19元，從19元降到18元，一直降到15元，增加的邊際利潤都是正數，說明降價是有利的。單價從15元降到14元，增加的邊際利潤為零，利潤并沒有增加，說明降價沒有任何意義，該點也是降價的極限。單價從14元降到13元，邊際利潤為負數，說明降價后減少了利潤，降價是不利的。

根據以上的分析可得出這樣的結論：單價下降的極限就是邊際收入等于邊際成本處，最優價格應該是邊際利潤最接近于零的一點。本例中，最優價格應該是在14元與15元之間，這時的利潤達到最大值。

主要參考文獻

［1］ 潘學模. 管理會計學［M］. 第2版. 成都：西南財經大學出版社，2006.

［2］ ［美］Glenn Owen. 用Excel和Access學習會計學［M］. 北京：機械工業出版社，2004.