利用Excel建立利潤敏感分析模型

1、江西j ian gx i科sc ience學(xué)第 28卷第 1期2010年 2 月vo l. 28 no. 1feb. 2010文章編號 : 1001 - 3679 ( 2010 ) 01 - 0011 - 04利用exce l建立利潤敏感分析模型楊樺(東北財經(jīng)大學(xué)職業(yè)技術(shù)學(xué)院 ,遼寧 大連 116023)摘要 :利潤是企業(yè)的綜合性經(jīng)濟指標(biāo) ,它反映企業(yè)一個時期的經(jīng)營成果 。在利潤的形成過程中 ,會存在許多不確定因素對利潤產(chǎn)生影響 ,各因素對利潤的影響程度和敏感程度不同 。所謂利潤敏感性分析是衡量單一變量 如產(chǎn)品售價或組合變量 (售價 、銷量都變動 ) 變動對利潤影響的一種分析方法 。利用 ex

2、cel 的分析工具 ,分 析了各因素變動對利潤的影響 ,并在此基礎(chǔ)上建立了利潤敏感分析模型與動態(tài)圖表 。關(guān)鍵詞 :利潤 ;敏感分析 ;模型中圖分類號 : f275. 4文獻(xiàn)標(biāo)識碼 : ato e sta b l ish the prof it sen s it iv ity ana ly s is m ode l by exce lyan g h ua( co llege of voca tiona l and techn ica l, dongbe i u n ive rsity of f inance and econom ic s, l iaon ing d a lian 116023

3、prc)a b stra c t: p rofit is a comp rehen sive econom ic ind ica to r of an en te rp rise, and it reflec ts the en te rp rises op e ra ting re su lts fo r a p e riod. d u ring the fo rm a tion of p rofit, the re a re m any unce rta in fac to rs tha t w ill have imp ac ts on p rofit. the effec t degr

4、ee and the sen sitivity of p rofit of each fac to r a re d iffe ren t. the sen sitivity ana lysis is a m e thod tha t d iscu sse s the influence of a single va riab le, such a s p rice, o r com b ined va riab le ( p rice, sa le s a re bo th sub jec t to change) on the p rofit.in th is p ap e r, the

5、influ2 ence of each va riab le fac to r on p rofit is d iscu ssed w ith excel ana lysis too ls. then the p rofit sen si2 tivity ana lysis mode l and dynam ic cha rts a re e stab lished on th is ba sis.key word s: p rofit, sen sitivity ana lysis, mode l小 。那些對利潤影響大的因素稱之為敏感因素 ,反之 ,則稱為非敏感因素 。0 前言利潤敏感性分析

6、法是指從定量分析的角度研 究有關(guān)因素發(fā)生某種變化對某一個或一組關(guān)鍵指 標(biāo)影響程度的一種不確定分析技術(shù) 。其實質(zhì)是通 過逐一改變相關(guān)變量數(shù)值的方法來解釋關(guān)鍵指標(biāo) 受這些因素變動影響大小的規(guī)律 。有些因素雖然 變化幅度較大 ,卻只對利潤產(chǎn)生微小的影響 。所 以對一個企業(yè)的管理者來說 ,不僅需要了解哪些 因素對利潤增減有影響 ,而且需要了解影響利潤 的若干因素中 , 哪些因素影響大 , 哪些因素影響1 利潤敏感性分析指標(biāo)的確定在運用利潤敏感性對實際業(yè)務(wù)進(jìn)行定量分析 時 ,常運用的方法可以包括質(zhì)性的分析和量性的 分析兩方面 。1. 1 質(zhì)性的分析即與利潤變化的方向分析 。如果因素 i對利 潤的敏感度

7、s i > 0, 則說明此因素的變化方向與利潤的變化方向相同 , 或說增大此因素會使利潤增收稿日期 : 2009 - 12 - 16;修訂日期 : 2010 - 01 - 25作者簡介 :楊 樺 ( 1963 - ) ,女 ,遼寧大連人 ,經(jīng)濟學(xué)碩士 ,從事信息管理方面的研究工作 。大 ; 反之 , 若 s i < 0, 則說明此因素的變化方向與利潤的變化方向相反 ,或說削減此因素會使利潤增大 。1. 2 量性的分析即因素的變化與利潤變化的幅度大小對比 。 因素對利潤的敏感度 s 的絕對值越大 , 說明因素的變化對利潤的變化影響大 。當(dāng)影響利潤的任一 因素以任意幅度和任意方向單獨變

8、動時 , 則可以 利用事先測算出來的某因素 利 潤靈 敏度 指 標(biāo) s i 很方便地預(yù)測出這種變動將對利潤產(chǎn)生的影響幅 度 。企業(yè)管理人員在決定生產(chǎn)和銷售時 , 非常想 知道它們對企業(yè)利潤的影響 , 但中間隔著收入和 成本 。對于收入 , 可以通過數(shù)量和單價來估計 , 而 成本則不然 , 無論是總成本還是單位成本都很難 把握 , 因為數(shù)量變化之后 , 單位成本也會變化 , 這就需要一個模型 , 并且利用這個模型 , 在銷量變動 時可以估計其對利潤的影響 , 或者在目標(biāo)利潤變 動時計算出完成目標(biāo)所需要的銷量水平 。為此 , 對成本和銷量之間的關(guān)系進(jìn)行研究 , 并將成本按 性態(tài)進(jìn)行分類 , 然后在

9、此基礎(chǔ)上明確成本數(shù)量和利潤之間的關(guān)系 。 把成本按其性態(tài)分類分解成固定成本和變動成本之后 , 再把收入和利潤加進(jìn)來 , 成本銷量和利 潤的關(guān)系就可以統(tǒng)一于一個數(shù)學(xué)模型 :反映敏感程度的指標(biāo)是敏感系數(shù) :某因素的敏感系數(shù) =利潤變化 ( % ) /該因素變化 ( % ) 。 其判別標(biāo)準(zhǔn)是 : ( 1 ) 敏感系數(shù)的絕對值 > 1,即當(dāng)某影響因素發(fā)生變化時 , 利潤發(fā)生更大程度 的變化 , 該影響因素為敏感因素 ; ( 2 ) 敏感系數(shù)的 絕對值 < 1, 即利潤變化的幅度小于影響因素變化 的幅度 , 該因素為非敏感因素 ; ( 3 ) 敏感系數(shù)的絕對值 = 1, 即影響因素變化合導(dǎo)致

10、利潤相同程度的 變化 , 該因素亦為非敏感因素 。2 利用實例建立模型在企業(yè)只生產(chǎn)單品種產(chǎn)品 , 且產(chǎn)銷平衡的條 件下 , 本量利之間的基本關(guān)系為 :利潤 =銷售量 ×(單價 - 單位變動成本 ) -固定成本 。在進(jìn)行利潤敏感性分析時 , 假定條件如下 :利 潤只受銷售量 、產(chǎn)品單價 、單位變動成本和固定成本的影響 ; 上述各因素的變動均不會影響其他因 素的變動 。2. 1 多因素綜合變動對利潤的影響2. 1. 1 新建工作簿與工作表 打開 exce l,新建一工作簿 ,命名為 "敏感分析. xls" ,再新建一工作 表 ,取名為 "敏感分析模型 &qu

11、ot; ,建立 "敏感分析模型 表 " ,如圖 1所示 。圖 1 敏感分析模型2. 1. 2 指標(biāo)計算打開【窗體 】控件 單擊【滾動條 】按鈕 在 e3: e6單元格分別添加【滾動條 】控件 右擊 e3 單元格【滾動條 】選擇【設(shè)置控件格式 】在彈出的【設(shè)置控件格式 】對話框中作如圖 2 的設(shè)置 。其它控件同理 , 分別鏈接到 f3、f4、f5、f6單元格 。圖 2 參數(shù)設(shè)置本例 中 各 因 素 的 變 動 范 圍 為 - 25 % +25 % ,而滾動條控制按鈕的值的變化范圍為 0 50 ,為了使?jié)L動條控制按鈕的變化表示為百分?jǐn)?shù) 的變化 ,這里將控制按鈕的值除以 100

12、后再減去25 % ,則每次單擊滾動條兩端的箭頭 ,單元格中的變動百分比就變化 1 % ,而當(dāng)滾動條在中間位置 ,百分比恰好為零 ; 當(dāng)單擊滾動框與滾動條兩端之間時 ,每單擊一次 ,變動百分比就增 (或減 ) 10 % 。選取 c3: c6 單元格區(qū)域 在編輯欄中以數(shù)組方式輸入 " = b 3: b 6 3 ( 1 + d 3: d6 ) " ,即可計算 出變動后的數(shù)值 ; 單擊 d 3 單元格 在編輯欄中輸入 " = f3 /100 - 25 % " 復(fù) 制 公 式 至 d 6 單 元格 ,即可設(shè)置出變動的幅度 ,如圖 3所示 。2. 1. 3 因素分析

13、單擊 b 11 單元格 在編輯欄 中輸入 " = b 6 3 (b 3 - b 4 ) - b 5 " 復(fù) 制公 式至c11 單 元 格 ; 單 擊 d11 單 元 格 在 編 輯 欄 中 輸 入 " = c11 - b 11 " ;單擊 e11單元格 在編輯欄中時 ,利潤增加 3 % 。因此 ,產(chǎn)品單價對利潤的影響最大 , 敏感性最強 , 其次是單位變動成本和銷售 量 ,而固定成本對利潤的影響最小 。2. 2. 2 建立單因素動態(tài)圖 單擊 b 19 單元格 在編輯欄中輸入公式 " = if (a 18 = 1 , f14 , if (a 18

14、= 2 , f15 , if (a 18 = 3 , f16 , f17 ) ) ) " ,即當(dāng) a18 = 1 時 ,讓其值等于單價變動的幅度 ; 當(dāng) a 18 = 2 時 , 讓其值等于單位變動成本變動的幅度 ;當(dāng) a18 = 3 時 ,讓其值等于固定成本變動的幅度 ; 當(dāng) a 18 不 等于 1 或 2 或 3 時 ,讓其值等于銷售量變動的幅度 。選中 b 18: b 19 單元格區(qū)域 利用圖表向?qū)?建立變動幅度的直方圖 ;利用【窗體 】控件添加一 個【組合框 】右擊【組合框 】選擇【設(shè)置控件格 式 】在彈出的【設(shè)置控件格式 】對話框中作如圖6的設(shè)置 。建立的動態(tài)圖表如圖 7所

15、示 。圖 3 變動后的數(shù)值計算輸入 " = d 11 /b 11 " ,這樣就計算出了多因素綜合 變動對利潤的綜合影響 ,如圖 4所示 。圖 4 多因素分析當(dāng)上述 4個因素同時朝著使利潤增加的方向 變動 5 %時 ,則利潤的增加幅度為 44. 60 % 。2. 2 單因素變動對利潤的影響2. 2. 1 編輯指標(biāo)計算公式 選取 b 14: b 17 單元 格區(qū)域 在編輯欄中以數(shù)組公式輸入 " = b 11 " ; 選取 c14: c17 單元格區(qū)域 在編輯欄中以數(shù)組公式輸入 " = d3: d 6 " ,復(fù)制原始數(shù)據(jù) 。單擊 d 14單

16、元格 在編輯欄中輸入 " = b 6 3( c3 - b 4 ) - b 5 " ; 單擊 d15 單元格 在編輯欄 中輸入 " = b 6 3 ( b 3 2c4 ) 2b 5 " ; 單擊 d16 單元 格在編輯 欄中 輸 入 " = b 6 3 (b 3 2b 4 ) 2c5 " ; 單 擊d17單元格 在編輯欄中輸入 " = c6 3 (b 3 2b 4 ) 2b 5 " ,這樣就分別計算出了銷售量 、產(chǎn)品單價 、單 位變動成本和固 定成 本 單獨 變動 時 對利 潤的 影 響 ;單擊 e14 單元格 在編

17、輯欄中輸入 " = d14 2b 14 " 復(fù) 制 公式 至 e17 單 元格 ; 選 取 f14: f17單元 格 區(qū) 域 在 編 輯 欄 中 以 數(shù) 組 公 式 輸 入 " = e14: e17 /b 14: b 17 " , 將 全 部 因 素 的 變 動 數(shù) 值 計算完成 ,如圖 5所示 。2. 2. 3 建立銷售收入變動與利潤變動動態(tài)圖( 1 )建立銷售收入變動與利潤變動表 , 如圖 8 所 示 。圖 5 單因素分析這樣利潤敏感性分析模型就建立起來了 。單 擊各個影響因素滾動條的箭頭 ,改變其變動幅度 , 就可以很方便地了解各個因素變動對利潤的

18、單獨 影響程度以及綜合影響程度 。例如 ,當(dāng)產(chǎn)品銷量 單獨增加 5 %時 ,利潤增加 8 % ; 當(dāng)產(chǎn)品單價單獨 增加 5 %時 ,利潤增加 20 % ;當(dāng)單位變動成本減少5 %時 ,利潤增加 12 % ; 當(dāng)固定成本單獨減少 5 %圖 8 銷售收入與利潤變動表其中 d 21 單元格的計算公式為“ = c21 2b 6 3b 4 2b 5 ”; d 22 單元格的計算公式為“ = c22 2c6 3c4 2c5 ”。( 2 )建立模擬運算表 。復(fù)制 c24: d 24 單元格區(qū)域的數(shù)值至 g20: h20 ,在 f21: f26 單元格區(qū)域 填入必要的銷售收入數(shù)值 ,選取 f20: h26 單

19、元格 區(qū)域 單擊【數(shù)據(jù) 】菜單 選擇【模擬運算表 】 在彈出的【模擬運算表 】對話框“輸入引用列的單元格 ”中輸入“c21 ”,這樣就建立起了不同銷售收 入所對應(yīng)的銷售收入的變動幅度與利潤的變動幅 度 。如圖 9所示 。綜合利用各有關(guān)因素之間的相互聯(lián)系 ,采取綜合措施 ,是成功的關(guān)鉸 。3. 1 慎重實施價格決策單價為絕對敏感因素 ,單價的變化會引起利 潤以幾倍于單價變化的速度發(fā)生變化 。所以在經(jīng)濟決策中 ,對降價必須給予格外的關(guān)注 。價格下 調(diào)帶來的利潤損失 ,若不能通過擴大銷售量或降 低單位成本予以更大程度的補償 ,則企業(yè)的整體 利潤肯定會下降 ,目標(biāo)利潤難以實現(xiàn) 。同時 ,價格 上漲的同時 ,應(yīng)盡可能地抑制銷量的大幅縮減和成本的大幅上升 。否則目標(biāo)利潤同樣難以實現(xiàn) 。3. 2 降低單位成本單位變動成本同樣屬于利潤的敏感因素 ,所 以降低單位成本對實現(xiàn)企業(yè)目標(biāo)利潤具有重要意 義 。特別是從長期看 ,成本的高低將是企業(yè)能否生存和發(fā)展的關(guān)鍵 。企業(yè)降低單位成本的主要措 施有 : ( 1 )源頭控制 , 降低材料的采購成本 , 其主 要方式有招標(biāo)采購或網(wǎng)上采購等 ; ( 2 ) 生產(chǎn)過程圖 9 模擬運算表( 3 )建立動態(tài)圖 。根據(jù)模擬運算表 ,利用【圖