Excel在財(cái)務(wù)管理中的應(yīng)用

HYPERLINK管理學(xué)院實(shí)驗(yàn)報(bào)告課程名稱(chēng)：財(cái)務(wù)管理實(shí)驗(yàn)項(xiàng)目名稱(chēng)：Excel在財(cái)務(wù)管理中的運(yùn)用實(shí)驗(yàn)類(lèi)型：財(cái)務(wù)管理（市場(chǎng)營(yíng)銷(xiāo)）綜合姓名（學(xué)號(hào)）：實(shí)驗(yàn)指導(dǎo)教師：日期：2011.11–2011.12實(shí)驗(yàn)?zāi)康暮鸵竽康模罕菊n程是一門(mén)以工具，以財(cái)務(wù)管理基本理論和方法為主線的經(jīng)濟(jì)管理學(xué)科，是電算化會(huì)計(jì)專(zhuān)業(yè)的專(zhuān)業(yè)課程。通過(guò)本課程實(shí)驗(yàn)的教學(xué)，是學(xué)生既能掌握財(cái)務(wù)管理的理論知識(shí)，又能了解Excel的基本操作，同時(shí)再次基礎(chǔ)上把二者有機(jī)的結(jié)合起來(lái)，有效的提高學(xué)生運(yùn)用EXCEL的解決財(cái)務(wù)問(wèn)題的能力，為學(xué)習(xí)后續(xù)的專(zhuān)業(yè)課程以及今后從事的財(cái)務(wù)管理工作打下一定的基礎(chǔ)。要求：通過(guò)本實(shí)驗(yàn)課程，在對(duì)財(cái)務(wù)管理和Excel功能作一般性了解的基礎(chǔ)上，緊緊圍繞模擬案例資料進(jìn)行應(yīng)用性操作實(shí)踐，在操作中對(duì)軟件的各主要功能模塊的基本功能與界面特點(diǎn)進(jìn)行系統(tǒng)的體驗(yàn)，通過(guò)實(shí)際操作提高學(xué)生在財(cái)務(wù)管理電算化業(yè)務(wù)處理方面的綜合技能。。實(shí)驗(yàn)內(nèi)容和原理基礎(chǔ)函數(shù):1.Sum函數(shù)：返回某一單元格區(qū)域中數(shù)字、邏輯值及數(shù)字的文本表達(dá)式之和。2.Count函數(shù)：返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個(gè)數(shù)。利用函數(shù)COUNT可以計(jì)算單元格區(qū)域或數(shù)字?jǐn)?shù)組中數(shù)字字段的輸入項(xiàng)個(gè)數(shù)。3.Round函數(shù)：返回按指定位數(shù)進(jìn)行四舍五入的數(shù)值。Round函數(shù)的作用是返回某個(gè)數(shù)字按指定位數(shù)取整后的數(shù)字。4.截取函數(shù)：從一個(gè)文本字符串的第一個(gè)字符開(kāi)始，截取指定數(shù)目的字符。5.合并字符函數(shù)：將多個(gè)字符文本或單元格中的數(shù)據(jù)連接在一起，顯示在一個(gè)單元格中。6.Vlookup函數(shù)：在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回?cái)?shù)據(jù)表當(dāng)前行中指定列處的數(shù)值。7.FV函數(shù)：基于固定利率及等額分期付款方式，返回某項(xiàng)投資的未來(lái)值。8.PV函數(shù):返回投資的現(xiàn)值?，F(xiàn)值為一系列未來(lái)付款的當(dāng)前值的累積和。例如，借入方的借入款即為貸出方貸款的現(xiàn)值。9.NPV函數(shù)：返回一個(gè)Double，指定根據(jù)一系列定期的現(xiàn)金流（支付和收入）和貼現(xiàn)率而定的HYPERLINK投資凈現(xiàn)值，NPV函數(shù)使用數(shù)組中數(shù)值的順序來(lái)解釋支付和收入的順序。10．IRR函數(shù)：返回由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。這些現(xiàn)金流不必為均衡的，但作為年金，它們必須按固定的間隔產(chǎn)生主要儀器設(shè)備電腦,office2003操作方法與實(shí)驗(yàn)步驟實(shí)驗(yàn)結(jié)果與分析1．宏觀地認(rèn)識(shí)了用友ERP財(cái)務(wù)管理系統(tǒng)2．了解ERP財(cái)務(wù)管理系統(tǒng)在企業(yè)的實(shí)際應(yīng)用3．注意帳套備份4．清楚各個(gè)操作員的權(quán)限5．已沖銷(xiāo)憑證仍需審核、出納簽字后記賬，然后在將所有未記賬的憑證記賬后再進(jìn)行月末轉(zhuǎn)賬工作

項(xiàng)目經(jīng)濟(jì)效益和社會(huì)效益分析（參考）8.1經(jīng)濟(jì)效益分析8.1.1⑴國(guó)家發(fā)展改革委員會(huì)與建設(shè)部發(fā)布、中國(guó)計(jì)劃出版社出版的《建設(shè)項(xiàng)目經(jīng)濟(jì)評(píng)價(jià)方法與參數(shù)》（第三版）。⑵本項(xiàng)目投入、產(chǎn)出物為含稅價(jià)格，增值稅按有關(guān)規(guī)定計(jì)算，其附加稅費(fèi)包括城市維護(hù)建設(shè)稅、教育費(fèi)附加費(fèi)和地方教育費(fèi)附加，分別按應(yīng)繳增值稅額的5%、3%和1%計(jì)取；企業(yè)所得稅率為：2010年為22%、2011年為24%、2012及以后年為25%。⑶財(cái)務(wù)基準(zhǔn)收益率按10%考慮。⑷項(xiàng)目計(jì)算期和生產(chǎn)負(fù)荷本項(xiàng)目計(jì)算期按10年考慮（含建設(shè)期2年）。根據(jù)項(xiàng)目產(chǎn)品市場(chǎng)情況預(yù)計(jì)，投產(chǎn)后的生產(chǎn)負(fù)荷估計(jì)如下：第3年投產(chǎn)，達(dá)產(chǎn)率為70%，第4年達(dá)產(chǎn)率為85%，第5年及以后達(dá)產(chǎn)率為100%。8.1.2產(chǎn)品銷(xiāo)售收入與銷(xiāo)售稅金及附加估算⑴產(chǎn)品規(guī)模及產(chǎn)品銷(xiāo)售價(jià)格本項(xiàng)目產(chǎn)品為XXXXXXX，達(dá)產(chǎn)年的產(chǎn)品規(guī)模及預(yù)計(jì)產(chǎn)品銷(xiāo)售價(jià)格見(jiàn)表8-1。表8-1產(chǎn)品規(guī)模及銷(xiāo)售價(jià)格序號(hào)產(chǎn)品名稱(chēng)產(chǎn)品規(guī)模（萬(wàn)支）銷(xiāo)售價(jià)格（含稅）（元/支）1XXXXXXXX2XXXXXXX⑵產(chǎn)品銷(xiāo)售收入及稅金達(dá)產(chǎn)年可實(shí)現(xiàn)產(chǎn)品銷(xiāo)售收入XXXXX萬(wàn)元，年上繳產(chǎn)品銷(xiāo)售稅金及附加X(jué)XX萬(wàn)元，應(yīng)繳增值稅XXXXX萬(wàn)元。產(chǎn)品銷(xiāo)售收入與銷(xiāo)售稅金及附加估算見(jiàn)表8-2。8.1.3總成本費(fèi)用估算⑴外購(gòu)原輔材料估算本項(xiàng)目產(chǎn)品的主要原材料為注塑件、五金件、電子元器件、燈管等。輔料為焊錫、助焊劑及白膠等。預(yù)計(jì)達(dá)產(chǎn)年合計(jì)年外購(gòu)原輔材料費(fèi)為XXXXX萬(wàn)元。見(jiàn)表8-3。⑵外購(gòu)燃料及動(dòng)力費(fèi)估計(jì)預(yù)計(jì)達(dá)產(chǎn)年外購(gòu)燃料及動(dòng)力費(fèi)為114.80萬(wàn)元，見(jiàn)表8-3。⑶人員工資及福利費(fèi)估算本項(xiàng)目定員1500人，年工資福利費(fèi)支出為2,532.60萬(wàn)元。⑷修理費(fèi)本項(xiàng)目修理費(fèi)按固定資產(chǎn)原值的2%估計(jì)。⑸折舊費(fèi)和攤銷(xiāo)費(fèi)本項(xiàng)目折舊方法采用直線折舊法，設(shè)備折舊按10年計(jì)，殘值率分別為5%和3%。詳見(jiàn)表8-4。⑹技術(shù)研發(fā)費(fèi)技術(shù)研發(fā)費(fèi)用按銷(xiāo)售收入的2%估計(jì)。⑺其它費(fèi)用其它費(fèi)用包括其它制造費(fèi)用、其它管理費(fèi)用和其它銷(xiāo)售費(fèi)用。其它制造費(fèi)用按產(chǎn)品銷(xiāo)售收入的3%估計(jì)；其它管理費(fèi)用按管理技術(shù)員定額數(shù)3萬(wàn)元/人估計(jì)。其它銷(xiāo)售費(fèi)用按產(chǎn)品銷(xiāo)售收入的4%估計(jì)。⑻財(cái)務(wù)費(fèi)用本項(xiàng)目達(dá)產(chǎn)年總成本費(fèi)用為XXX萬(wàn)元，其中：可變成本XXX萬(wàn)元，固定成本XXX萬(wàn)元；年經(jīng)營(yíng)成本為XXX萬(wàn)元?？偝杀举M(fèi)用估算見(jiàn)表8-3。8.1.4利潤(rùn)估算該項(xiàng)目實(shí)施后，達(dá)產(chǎn)年可實(shí)現(xiàn)總產(chǎn)值20,000.00萬(wàn)元，年利潤(rùn)總額為2179.52萬(wàn)元，年上繳稅金2119.30萬(wàn)元，年稅后利潤(rùn)1634.64萬(wàn)元（詳見(jiàn)表8-5）。計(jì)算期內(nèi)年均利潤(rùn)總額1875.12萬(wàn)元?？偼顿Y收益率ROI為75%。由上述指標(biāo)可以看出，項(xiàng)目具有很好的投資收益。8.1.5項(xiàng)目盈利能力分析項(xiàng)目現(xiàn)金流量計(jì)算見(jiàn)表8-6。經(jīng)計(jì)算，本項(xiàng)目有關(guān)的財(cái)務(wù)盈利指標(biāo)如下：項(xiàng)目所得稅前財(cái)務(wù)內(nèi)部收益率FIRR為29.69%，財(cái)務(wù)凈現(xiàn)值FNPV（ic=10%）為6221.33萬(wàn)元，靜態(tài)投資回收期為4.30年（含建設(shè)期2年）。項(xiàng)目所得稅后財(cái)務(wù)內(nèi)部收益率為23.69%，財(cái)務(wù)凈現(xiàn)值（ic=10%）為4437.93萬(wàn)元，靜態(tài)投資回收期為4.62年（含建設(shè)期2年）。上述指標(biāo)表明，本項(xiàng)目能獲得較好的投資收益率，項(xiàng)目在財(cái)務(wù)上是可行的。8.1.6本項(xiàng)目還貸資金來(lái)源主要有可供分配利潤(rùn)、折舊與攤銷(xiāo)費(fèi)。按項(xiàng)目最大還款能力計(jì)算，本項(xiàng)目可在2.52年（含建設(shè)期）內(nèi)可償還全部貸款。項(xiàng)目還本付息情況詳見(jiàn)表8-7。8.1.7不確定性分析以設(shè)計(jì)生產(chǎn)能力利用率來(lái)表示項(xiàng)目的盈虧平衡點(diǎn)BEP，即=本項(xiàng)目只要生產(chǎn)能力利用率達(dá)到設(shè)計(jì)生產(chǎn)能力的56.28%就能夠保本經(jīng)營(yíng)而不發(fā)生虧損。表8-2銷(xiāo)售收入估算表單位：萬(wàn)元序號(hào)項(xiàng)目單位/稅率合計(jì)3456789101生產(chǎn)負(fù)荷70%85%100%100%100%100%100%100%產(chǎn)品銷(xiāo)售收入151,000.0014,000.0017,000.0020,000.0020,000.0020,000.0020,000.0020,000.0020,000.001.126W以上60,400.005,600.006,800.008,000.008,000.008,000.008,000.008,000.008,000.00產(chǎn)量萬(wàn)支350.00425.00500.00500.00500.00500.00500.00500.00售價(jià)元/支16.0016.0016.0016.0016.0016.0016.0016.001.226W以下90,600.008,400.0010,200.0012,000.0012,000.0012,000.0012,000.0012,000.0012,000.00產(chǎn)量萬(wàn)支1,050.001,275.001,500.001,500.001,500.001,500.001,500.001,500.00售價(jià)元/支8.008.008.008.008.008.008.008.001.3銷(xiāo)項(xiàng)稅17%25,670.002,380.002,890.003,400.003,400.003,400.003,400.003,400.003,400.002銷(xiāo)售稅金及附加981.4891.00110.50130.00130.00130.00130.00130.00130.002.1城市維護(hù)建設(shè)稅5%545.2750.5561.3972.2272.2272.2272.2272.2272.222.2教育費(fèi)附加3%327.1630.3336.8343.3343.3343.3343.3343.3343.332.3地方教育費(fèi)附加1%109.0510.1112.2814.4414.4414.4414.4414.4414.443增值稅10,905.341,011.091,227.751,444.421,444.421,444.421,444.421,444.421,444.423.1銷(xiāo)項(xiàng)稅25,670.002,380.002,890.003,400.003,400.003,400.003,400.003,400.003,400.003.2進(jìn)項(xiàng)稅14,764.661,368.911,662.251,955.581,955.581,955.581,955.581,955.581,955.58

表8-3總成本費(fèi)用估算表單位：萬(wàn)元序號(hào)項(xiàng)目單位/稅率合計(jì)345678910生產(chǎn)負(fù)荷70%85%100%100%100%100%100%100%1外購(gòu)原輔材料、包裝材料費(fèi)85,828.407,957.609,662.8011,368.0011,368.0011,368.0011,368.0011,368.0011,368.001.1原、輔材料85,088.507,889.009,579.5011,270.0011,270.0011,270.0011,270.0011,270.0011,270.001.2包裝物739.9068.6083.3098.0098.0098.0098.0098.0098.00進(jìn)項(xiàng)稅17%14,590.831,352.791,642.681,932.561,932.561,932.561,932.561,932.561,932.562外購(gòu)燃料及動(dòng)力費(fèi)866.7480.3697.58114.80114.80114.80114.80114.80114.80進(jìn)項(xiàng)稅小計(jì)143.1213.2716.1118.9618.9618.9618.9618.9618.962.1電761.0470.5685.68100.80100.80100.80100.80100.80100.80進(jìn)項(xiàng)稅17%129.3812.0014.5717.1417.1417.1417.1417.1417.142.2水105.709.8011.9014.0014.0014.0014.0014.0014.00進(jìn)項(xiàng)稅13%13.741.271.551.821.821.821.821.821.823外協(xié)加工費(fèi)211.4019.6023.8028.0028.0028.0028.0028.0028.00進(jìn)項(xiàng)稅17%30.722.853.464.074.074.074.074.074.074人工工資及福利費(fèi)20,260.802,532.602,532.602,532.602,532.602,532.602,532.602,532.602,532.605修理費(fèi)533.5366.6966.6966.6966.6966.6966.6966.6966.696折舊及攤銷(xiāo)費(fèi)1,474.40184.30184.30184.30184.30184.30184.30184.30184.307研發(fā)費(fèi)用3,020.00280.00340.00400.00400.00400.00400.00400.00400.008其它費(fèi)用12,250.001,190.001,400.001,610.001,610.001,610.001,610.001,610.001,610.008.1其它管理費(fèi)用1,680.00210.00210.00210.00210.00210.00210.00210.00210.008.2其它制造費(fèi)用4,530.00420.00510.00600.00600.00600.00600.00600.00600.008.3銷(xiāo)售費(fèi)用6,040.00560.00680.00800.00800.00800.00800.00800.00800.009財(cái)務(wù)費(fèi)用177.9920.1120.7822.8522.8522.8522.8522.8522.8510總成本費(fèi)用123,973.2212,331.2614,304.5516,246.0616,218.2716,218.2716,218.2716,218.2716,218.2710.1可變成本101,566.949,743.8611,560.4813,377.1013,377.1013,377.1013,377.1013,377.1013,377.1010.2固定成本22,406.282,587.402,744.072,868.962,841.172,841.172,841.172,841.172,841.1711經(jīng)營(yíng)成本122,759.4712,107.2514,099.6716,092.0916,092.0916,092.0916,092.0916,092.0916,092.09進(jìn)項(xiàng)稅合計(jì)14,764.661,368.911,662.251,955.581,955.581,955.581,955.581,955.581,955.58

表8-4折舊與攤銷(xiāo)估算表單位：萬(wàn)元序號(hào)項(xiàng)目原值折舊/攤銷(xiāo)年限345678910一固定資產(chǎn)1土建工程1.1折舊費(fèi)1.2凈值2設(shè)備1,900.002.1折舊費(fèi)10184.30184.30184.30184.30184.30184.30184.30184.302.2凈值1,715.701,531.401,347.101,162.80978.50794.20609.90425.603固定資產(chǎn)合計(jì)1,900.003.1折舊費(fèi)184.30184.30184.30184.30184.30184.30184.30184.303.2凈值1,715.701,531.401,347.101,162.80978.50794.20609.90425.60二無(wú)形及遞延資產(chǎn)1無(wú)形資產(chǎn)1.1攤銷(xiāo)費(fèi)1.2凈值-2遞延資產(chǎn)2.1攤銷(xiāo)費(fèi)2.2凈值-3無(wú)形及遞延資產(chǎn)合計(jì)3.1攤銷(xiāo)費(fèi)3.2凈值-

表8-5利潤(rùn)與利潤(rùn)分配表單位：萬(wàn)元序號(hào)項(xiàng)目合計(jì)3456789101產(chǎn)品銷(xiāo)售收入151,000.0014,000.0017,000.0020,000.0020,000.0020,000.0020,000.0020,000.0020,000.002銷(xiāo)售稅金及附加981.4891.00110.50130.00130.00130.00130.00130.00130.003增值稅10,905.341,011.091,227.751,444.421,444.421,444.421,444.421,444.421,444.424總成本費(fèi)用123,973.2212,331.2614,304.5516,246.0616,246.0616,246.0616,246.0616,246.0616,246.065利潤(rùn)總額15,000.97566.651,357.202,179.522,179.522,179.522,179.522,179.522,179.526彌補(bǔ)前年度虧損0.007應(yīng)納所得稅額15,000.97566.651,357.202,179.522,179.522,179.522,179.522,179.522,179.528所得稅3,719.67124.66325.73544.88544.88544.88544.88544.88544.889稅后利潤(rùn)11,281.30441.981,031.471,634.641,634.641,634.641,634.641,634.641,634.6410盈余公積金(10%)1,128.1344.20103.15163.46163.46163.46163.46163.46163.4611應(yīng)付利潤(rùn)12未分配利潤(rùn)10,153.17397.78928.321,471.181,471.181,471.181,471.181,471.181,471.18其中:借款償還300.0013累計(jì)未分配利潤(rùn)97.781,326.102,797.284,268.465,739.647,210.828,682.0010,153.1814EBIT15,178.96586.761,377.982,202.372,202.372,202.372,202.372,202.372,202.3715EBITDA16,653.36771.061,562.282,386.672,386.672,386.672,386.672,386.672,386.67

表8-6項(xiàng)目現(xiàn)金流量表單位：萬(wàn)元序號(hào)項(xiàng)目合計(jì)123456789101現(xiàn)金流入151,657.000.000.0014,000.0017,000.0020,000.0020,000.0020,000.0020,000.0020,000.0020,657.001.1產(chǎn)品銷(xiāo)售收入151,000.0014,000.0017,000.0020,000.0020,000.0020,000.0020,000.0020,000.0020,000.001.2回收固定資產(chǎn)余值57.0057.001.3回收流動(dòng)資金600.00600.002現(xiàn)金流出137,746.281,522.00978.0013,737.3415,455.4817,720.9617,666.5017,666.5017,666.5017,666.5017,666.502.1建設(shè)投資1,900.001,358.00542.000.002.2流動(dòng)資金600.00164.00436.00528.0017.5654.450.002.3經(jīng)營(yíng)成本122,759.4712,107.2514,099.6716,092.0916,092.0916,092.0916,092.0916,092.0916,092.092.4銷(xiāo)售稅金及附加981.4891.00110.50130.00130.00130.00130.00130.00130.002.5增值稅10,905.341,011.091,227.751,444.421,444.421,444.421,444.421,444.421,444.423所得稅前凈現(xiàn)金流量13,910.72-1,522.00-978.00262.661,544.522,279.042,333.502,333.502,333.502,333.502,990.504累計(jì)所得稅前凈現(xiàn)金流量-1,522.00-2,500.00-2,237.34-692.821,586.223,919.726,253.228,586.7210,920.2213,910.725所得稅前凈現(xiàn)值6,221.33-1,383.64-808.26197.341,054.941,415.111,317.201,197.451,088.59989.631,152.976累計(jì)所得稅前凈現(xiàn)值-1,383.64-2,191.90-1,994.56-939.62475.491,792.692,990.144,078.735,068.366,221.337調(diào)整所得稅105.91299.67503.96503.96503.96503.96503.96503.968所得稅后凈現(xiàn)金流量10,481.38-1,522.00-978.00156.751,244.851,775.081,829.541,829.541,829.541,829.542,486.549累計(jì)所得稅后凈現(xiàn)金流量-1,522.00-2,500.00-2,343.25-1,098.40676.682,506.224,335.766,165.307,994.8410,481.3810所得稅后凈現(xiàn)值4,437.93-1,383.