郭少蓉，劉艷萍

摘要：Excel不僅具有多種函數(shù)庫，其VBA更是為用戶實(shí)現(xiàn)高級(jí)操作提供了便捷。本文以長期借款的還款模型為例給出運(yùn)用VBA編程技術(shù)建立財(cái)務(wù)管理教學(xué)中動(dòng)態(tài)模型的方法。

關(guān)鍵詞：財(cái)務(wù)管理教學(xué)；VBA；動(dòng)態(tài)模型

中圖分類號(hào)：G642.4 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1674-9324（2012）07-0206-02

一、引言

隨著計(jì)算機(jī)技術(shù)的廣泛應(yīng)用，在高校中如何利用計(jì)算機(jī)技術(shù)來提高財(cái)務(wù)管理課程的教學(xué)效果成為教師的關(guān)注點(diǎn)。Excel的計(jì)算功能與函數(shù)輔助工具，可以使一些復(fù)雜計(jì)算簡單地加以實(shí)現(xiàn)。教學(xué)過程中經(jīng)常需要臨時(shí)修改或輸入某些數(shù)據(jù)，運(yùn)用Excel可以隨時(shí)改變單元格的內(nèi)容而不影響其計(jì)算過程，這是其他教學(xué)常用軟件難以實(shí)現(xiàn)的，所以在財(cái)務(wù)管理教學(xué)課程上應(yīng)注重對(duì)excel的使用。如何構(gòu)造一個(gè)方便使用、節(jié)省不必要操作過程的動(dòng)態(tài)模型來提高教學(xué)效果是本文研究的重點(diǎn)。下面以長期借款的還款模型為例說明如何運(yùn)用VBA編程技術(shù)建立一個(gè)基于年限的動(dòng)態(tài)變化模型。

二、長期借款還款模型的設(shè)計(jì)實(shí)例

（一）模型建立

構(gòu)造一個(gè)長期借款還款模型來分析在不同年利率、貸款額、貸款期限及還款方式下每年應(yīng)付利息、欠款總額、支付金額、尚欠款額的變化。構(gòu)建模型如圖1所示。

圖1 動(dòng)態(tài)還款模型

還款方式為四種：每年只付利息，本金最后一年年末一次償還；每年年末等額還本金及當(dāng)年全部利息；每年均勻償還全部本利和；本息最后一年年末一次償還。

圖2 還款模型結(jié)果分析

該分析結(jié)果的表格會(huì)隨著貸款期限或是還款方式的不同而動(dòng)態(tài)的改變，克服了傳統(tǒng)模型中建立的表格行數(shù)、列數(shù)的限制，即假如模型建立時(shí)只考慮了期限為5年的貸款，則當(dāng)要分析其他期限的貸款時(shí)就須重新設(shè)計(jì)表格，而利用VBA編程可以實(shí)現(xiàn)只要改變還款方式則結(jié)果分析表格中的數(shù)據(jù)也會(huì)隨之改變。

（二）主要的程序代碼

（1）若單元格C6或C7的值改變則執(zhí)行以下代碼：

Private Sub Worksheet_Change（ByVal Target As Range）

On Error GoTo L

If Target = Range（“C6”）OrTarget=Range（“C7”） Then Call hkfs 調(diào)用不同還款方式的計(jì)算過程L：Call chsh重設(shè)表格：

End If

End Sub

（2）不同還款方式的計(jì)算

Sub hkfs（）

Range（“C12：Q15”）.FormulaR1C1=“”

Call c6“由于”每年只付利息，本金最后一年年末一次償還“和本息最后一年年末一次償還”兩種還款方式最后一年的支付金額與尚欠款額與之前年份的計(jì)算不同，需要單獨(dú)計(jì)算，所以要判斷C6單元格中的年限。

Select Case Range（“C7”）.FormulaR1C1

Case“每年只付利息，本金最后一年年末一次償還”

Range（“C12”）.FormulaR1C1=“=R5C3*（R4C3/100）”

Range（“C12”）.Select

Selection.AutoFillDestination：=Range（“C12：Q12”），Type：=xlFillDefault以上兩句為選中編輯完的單元格后橫向自動(dòng)填充Q列，后文多長用到，為了節(jié)省繁瑣的代碼之后用“自動(dòng)填充”

字樣表示：Range（“C13”）.FormulaR1C1=“=R5C3+R12C3”

自動(dòng)填充：Range（“C14”）.FormulaR1C1=“=R-[2]C”

自動(dòng)填充：Range（“C15”）.FormulaR1C1=“=R5C3”

自動(dòng)填充：Range（h14）.FormulaR1C1=“=R12C3+R5C3”；Range（h15）.FormulaR1C1=“0”

h14、h15為C6過程中的變量，代表最后一年的支付金額與尚欠款額所對(duì)應(yīng)的單元格。

Case“每年末等額還本金和當(dāng)年全部利息”

Range（“C12”）.FormulaR1C1=“=R5C3*（R4C3/100）”

Range（“D12”）.FormulaR1C1=“=R15C-[1]*（R4C3/100）”

Range（“D12”）.Select

自動(dòng)填充：Range（“C13”）.FormulaR1C1=“=R5C3+R12C3”；Range（“D13”）.FormulaR1C1=“=R[2]C-[1]+R-[1]C”

自動(dòng)填充：Range（“C14”）.FormulaR1C1=“=R5C3/R6C3+R12C”

自動(dòng)填充：Range（“C15”）.FormulaR1C1=“=R-[2]C-R-[1]C”

自動(dòng)填充：Case“每年均勻償還全部本利和”；Range（“C12”）.FormulaR1C1 =“=R5C3*（R4C3/100）”

Range（“D12”）.FormulaR1C1=“=R[3]C-[1]*（R4C3/100）”

自動(dòng)填充：Range（“C13”）.FormulaR1C1=“=R5C3+R12C3”；Range（“D13”）.FormulaR1C1=“=R[2]C-[1]+R-[1]C”

自動(dòng)填充：Range（“C14”）.FormulaR1C1=“=-PMT（R-[10]C/100，R-[8]C，R-[9]C）”

Range（“C14”）.Select；Range（“D14”）.FormulaR1C1=“=RC-[1]”

自動(dòng)填充：Range（“C15”）.FormulaR1C1=“=R-[2]C-R-[1]C”

自動(dòng)填充：Case“本息最后一年年末一次償還”；Range（“C12：Q12，C14：Q14”）.FormulaR1C1=“0”Range（“C13”）.FormulaR1C1=“=R5C3*（1+R4C3/100）”；Range（“D13”）.FormulaR1C1=“=RC-[1]*（1+R4C3/100）”

自動(dòng)填充：Range（“C15”）.FormulaR1C1=“=R-[2]C”

自動(dòng)填充：Range（h14）.FormulaR1C1=“=R-[1]C”；Range（h15）.FormulaR1C1=“0”；End Select；EndSub

（3）動(dòng)態(tài)表格制作：

Sub chsh（）重設(shè)表格

Call刷新

Dim a As String

Select Case Range（“C6”）.FormulaR1C1

Case“2”

a=“E11：Q15”

Call yincang（a）

Case“3”

a=“F11：Q15”

Call yincang（a）

……中間部分省略，節(jié)省篇幅。

Case“14”

a=“Q11：Q15”

Call yincang（a）

End Select

End Sub

Sub 刷新（）刷新表格

Range（“E11：Q15”）.Select

Selection.NumberFormatLocal=“G/通用格式”

Range（“C12：Q15”）.Select

對(duì)選中區(qū)域進(jìn)行表格繪制，這部分代碼可以用錄制宏的方式來實(shí)現(xiàn)，在此不展示其代碼。

End Sub

隱藏不在年限范圍內(nèi)的單元格

Sub yincang（x As String）

Range（x）.Select

Selection.NumberFormatLocal=“；；；”

將選中區(qū)域的內(nèi)外邊框設(shè)為空，這部分代碼可以用錄制宏的方式來實(shí)現(xiàn)，在此不展示其代碼。

End Sub

四、結(jié)束語

Excel強(qiáng)大的計(jì)算及圖表功能為財(cái)務(wù)管理教學(xué)帶來極大的方便。動(dòng)態(tài)模型的建立不僅可以節(jié)省教師備課時(shí)需要建立模型與多個(gè)表格的麻煩，也方便在課堂上演示，清晰地展示分析過程，有利于學(xué)生的學(xué)習(xí)吸收。

參考文獻(xiàn)：

[1]肖文鋒，肖莉.對(duì)財(cái)務(wù)管理教學(xué)方法的幾點(diǎn)思考[J].會(huì)計(jì)之友，2010（5）：118-119.

[2]蘇慶華.函數(shù)嵌套在計(jì)算機(jī)財(cái)務(wù)管理中的應(yīng)用[J].中國管理信息化，2007（11）：68-72.

基金項(xiàng)目：大連理工大學(xué)創(chuàng)新性實(shí)驗(yàn)計(jì)劃項(xiàng)目（2010692）；大連理工大學(xué)研究生教改基金資助項(xiàng)目（JG1030）。

作者簡介：郭少蓉，大連理工大學(xué)管理與經(jīng)濟(jì)學(xué)部，信息管理與信息系統(tǒng)專業(yè)；劉艷萍，大連理工大學(xué)管理與經(jīng)濟(jì)學(xué)部副教授，碩士生導(dǎo)師，管理科學(xué)與工程博士。主要研究方向：會(huì)計(jì)信息系統(tǒng)，資產(chǎn)負(fù)債組合優(yōu)化。