用EXCEL模型進(jìn)行市場調(diào)查數(shù)據(jù)分析-以樂健體為例

湖南商務(wù)職業(yè)技術(shù)學(xué)院畢業(yè)設(shè)計目錄TOC\o"1-3"\h\u317901背景簡介 用EXCEL模型進(jìn)行市場調(diào)查數(shù)據(jù)分析——以樂健體為例1背景簡介樂健體育成立于2002年，在\t"/item/%E4%B9%90%E5%81%A5%E4%BD%93%E8%82%B2/_blank"上海、浙江、福建、河南、湖北等省、市、縣設(shè)有銷售服務(wù)網(wǎng)點(diǎn)。是國內(nèi)知名的健身器材、保健器材直銷商，樂健體育為眾多國際國內(nèi)知名品牌的區(qū)域總代理。為眾多專業(yè)健身中心及知名房產(chǎn)商提供配套健身器材及相關(guān)產(chǎn)品，同時為客戶提供健身會所方案設(shè)計、產(chǎn)品選型、管理培訓(xùn)、開業(yè)顧問等周到的售前、售后服務(wù)支持。2模型介紹EXCEL是微軟公司辦公軟件的其中一個組件，也是微軟公司辦公軟件的一個重要組成部分。它可以處理各式各樣的數(shù)據(jù)、起到一個統(tǒng)計分析的作用，它還能進(jìn)行輔助操作，清晰地看到數(shù)據(jù)的變化與不同。EXCEL普遍地應(yīng)用于財務(wù)管理數(shù)據(jù)統(tǒng)計、金融等許多范疇，也是日常辦公中不可或缺的工具。它有著應(yīng)用選擇大量的公式以及函數(shù)進(jìn)行計算，數(shù)據(jù)分析并將數(shù)據(jù)制作成圖表的功能，使用起來很方便，大大提高了辦公的效率。此次進(jìn)行數(shù)據(jù)分析我使用的是2017版的EXCEL，需用到的函數(shù)為VLOOKUP函數(shù)和SUM函數(shù)。其中VLOOKUP函數(shù)用于表中查找數(shù)據(jù)，SUM函數(shù)用于求和計算。2.1計算基本描述統(tǒng)計量性質(zhì)化統(tǒng)計,是一種運(yùn)用綜合計算性和概括性統(tǒng)計數(shù)據(jù)的粥方法來分析描述統(tǒng)計數(shù)據(jù)基本特征的統(tǒng)計活動,主要應(yīng)用包括統(tǒng)計數(shù)據(jù)的變化頻數(shù)程度分析、集中變化趨勢程度分析、離散變化程度趨勢分析以及數(shù)據(jù)分布程度分析。運(yùn)用EXCEL進(jìn)行描述性分析的具體操作步驟如下:首先，點(diǎn)擊菜單“文件”，依次選擇“選項”、“加載項”，選擇“分析工具庫”。接著依次點(diǎn)擊“數(shù)據(jù)”-“數(shù)據(jù)分析”，打開“數(shù)據(jù)分析”對話框，點(diǎn)擊“描述統(tǒng)計”，點(diǎn)擊“確定”按鈕。圖1：選擇描述統(tǒng)計打開"描述統(tǒng)計"對話框,點(diǎn)擊"輸入?yún)^(qū)域",在空白數(shù)據(jù)表中分別選中一個數(shù)據(jù)工作區(qū)域,勾選"標(biāo)志位于第一行",點(diǎn)擊"輸出區(qū)域",點(diǎn)擊其后的空白文本框,在空白工作區(qū)域表中分別點(diǎn)擊兩個空白工作區(qū)域,勾選"匯總統(tǒng)計"和"平均數(shù)置信度",點(diǎn)擊"確定"按鈕,返回工作表中，即可得到輸出數(shù)據(jù)。圖2：描述統(tǒng)計對話框2.2相關(guān)系數(shù)的計算相關(guān)系數(shù)取值是一種用來直接體現(xiàn)兩個不同變量之間的交互相關(guān)變化程度,取相關(guān)值值的范圍一般為-1到1之間。若相關(guān)系數(shù)通常大于0,則為正或負(fù)相關(guān);但是若相關(guān)系數(shù)通常小于0,則為正或負(fù)相關(guān)。相關(guān)系數(shù)越大或接近1,說明兩個數(shù)值變量之間的數(shù)值相關(guān)變化程度越大。若相關(guān)系數(shù)在0-0.3之間,則可能說明兩個相關(guān)變量之間可能存在弱具有相關(guān)性的關(guān)系;但是若相關(guān)系數(shù)在0.3-0.5之間,則可能說明兩個相關(guān)變量之間可能存在低度具有相關(guān)性的關(guān)系;但是若相關(guān)系數(shù)在0.5-0.8之間,則可能說明兩個相關(guān)變量之間可能存在顯著具有相關(guān)性的關(guān)系;但是若相關(guān)系數(shù)在0.8以上,則可能說明兩個相關(guān)變量之間可能存在高度具有相關(guān)性的關(guān)系。圖3：選擇相關(guān)系數(shù)在相關(guān)系數(shù)的輸入?yún)^(qū)域中輸入所要計量的變量，若輸入?yún)^(qū)域輸入了變量名稱，則勾選“標(biāo)志位于第一行”，然后在輸出選項中選擇想要輸出的位置，最后點(diǎn)擊確定。如圖8所示:圖4：相關(guān)系數(shù)對話框2.3構(gòu)建多元線性回歸模型回歸分析是指通過最小二乘法擬合進(jìn)行分析，主要考察變量之間的數(shù)量伴隨關(guān)系，并通過一定的數(shù)學(xué)表達(dá)式將這種關(guān)系表達(dá)出來，進(jìn)而確定一個變量的變化對另一個變量的影響程度。線性回歸分析是最基本的回歸分析方法，假設(shè)自變量和因變量之間存在線性關(guān)系，線性回歸的數(shù)學(xué)模型可以表示為:Y=aX+b?；貧w方程的確定系數(shù)在一定程度上反映了兩個變量之間關(guān)系的密切程度，但確定系數(shù)一般是在擬合回歸方程之后計算的，如果兩個變量之間的相關(guān)程度不高，擬合回歸方程便沒有意義，因此相關(guān)分析往往在回歸分析之前完成。運(yùn)用EXCEL進(jìn)行回歸分析的具體操作步驟如下:依次點(diǎn)擊“數(shù)據(jù)”–“數(shù)據(jù)分析”，打開“數(shù)據(jù)分析”對話框，點(diǎn)擊“回歸”，點(diǎn)擊“確定”按鈕。圖5：選擇回歸打開“回歸”對話框，點(diǎn)擊“Y值輸入?yún)^(qū)域”，在數(shù)據(jù)表中選中Y值數(shù)據(jù)區(qū)域，接著點(diǎn)擊“X值輸入?yún)^(qū)域”，在數(shù)據(jù)表中選中X值數(shù)據(jù)區(qū)域，點(diǎn)擊“輸出區(qū)域”，點(diǎn)擊后面的文本框，在工作表中點(diǎn)擊空白區(qū)域，點(diǎn)擊“確定”按鈕，返回工作表中，即可得到輸出數(shù)據(jù)。圖6：回歸對話框3數(shù)據(jù)來源在天眼查搜索“樂健體育”，進(jìn)入頁面，在“企業(yè)年報”中可得到2017-2019年的企業(yè)的銷售額與總資產(chǎn)以及費(fèi)用使用情況的數(shù)據(jù)，再通過集合得出最終所需要數(shù)據(jù)。圖7天眼查而行業(yè)內(nèi)平均水平可以登錄“前瞻數(shù)據(jù)庫”，進(jìn)行“行業(yè)搜索”得出體育行業(yè)的銷售利潤，總資產(chǎn)報酬率凈資產(chǎn)收益率，以及費(fèi)用利潤率的平均水平。圖8：前瞻數(shù)據(jù)庫4模型構(gòu)建4.1模型介紹圖9:數(shù)據(jù)分析結(jié)構(gòu)圖從圖9反映中看出數(shù)據(jù)分析法中各財務(wù)指標(biāo)的關(guān)系如下:凈資產(chǎn)收益率=總資產(chǎn)凈利率*權(quán)益乘數(shù)總資產(chǎn)凈利率=銷售凈利率*總資產(chǎn)周轉(zhuǎn)率銷售凈利率=凈利潤/銷售收入總資產(chǎn)周轉(zhuǎn)率=銷售收入/資產(chǎn)總額權(quán)益乘數(shù)=資產(chǎn)/權(quán)益4.2確定模型計算公式確定模型計算公式，如下圖所示。圖10：計算公式圖4.3構(gòu)建模型第一步，建立“樂健體育財務(wù)比率.xls”工作簿，在工作簿內(nèi)新建“資產(chǎn)負(fù)債表”工作表、“利潤表”工作表和“財務(wù)比率表”工作表。第二步，從桌面復(fù)制東莞市樂健體育有限公司的資產(chǎn)負(fù)債表，粘貼到“樂健體育財務(wù)比率.xls”工作簿中的“資產(chǎn)負(fù)債表”工作表中。然后繼續(xù)從桌面復(fù)制樂健體育的利潤表，粘貼到“樂健體育財務(wù)比率.xls”工作簿中的“利潤表”工作表。第三步，對“樂健體育財務(wù)比率.xls”工作簿中的“資產(chǎn)負(fù)債表”工作表和“利潤表”工作表進(jìn)行適當(dāng)?shù)母膭?，并省略了原有項目，?shí)際運(yùn)用時可根據(jù)需要進(jìn)行相應(yīng)的調(diào)整。改動后的表格如圖1、圖2所示。圖11：資產(chǎn)負(fù)債表圖12：利潤表第四步，在“財務(wù)比率表”工作表中設(shè)計財務(wù)比率表;制作表頭，選擇好適用的財務(wù)指標(biāo)并依次填列，如圖3所示。圖13：財務(wù)比率初表第五步，根據(jù)“資產(chǎn)負(fù)債表”工作表和“利潤表”工作表填列“財務(wù)比率表”工作表。5數(shù)據(jù)分析5.1數(shù)據(jù)引入首先填列償債能力指標(biāo),①選中C3單元格,點(diǎn)擊菜單欄中的公式,點(diǎn)擊插入公式。在彈出的顯示框中,找到Vlookup函數(shù),單擊確定。在彈出的函數(shù)參數(shù)顯示框中,在lookup_value欄輸入"流動資產(chǎn)合計"、table_array欄選中"資產(chǎn)負(fù)債表"工作表中的A4:C22單元格、col_index_num欄中輸入"2"、最后的range_lookup欄中輸入"false"表示精確查找,最后點(diǎn)擊確定,查找出流動資產(chǎn)合計的期末數(shù),點(diǎn)擊除號。再按照上一步程序操作的兩個步驟,輸入tavlookup_v函數(shù),在talookup_value欄中再輸入"流動負(fù)債合計"、table_array欄中再選中"資產(chǎn)負(fù)債表"即可工作后將表中的符號d4:f22單元格、最后兩個格欄不再改變,單擊即可確定。關(guān)于流動比率的計算就完成了。②計算速動比率,速動比率與流動比率的計算只多了存貨的存在,可以選中C3單元格,在編輯欄復(fù)制流動比率的計算公式,然后選中C4單元格粘貼公式。再在查找流動資產(chǎn)合計的公式后面減去查找存貨的公式,加上小括號,防止運(yùn)算順序出錯。這樣比一步一步操作要迅速一些。③計算現(xiàn)金比率,在C4單元格的編輯欄中復(fù)制流動負(fù)債合計的查找公式,然后根據(jù)Vlookup函數(shù)的操作步驟查找貨幣資金的期末數(shù)值,粘貼流動負(fù)債合計的查找公式,最后相除。④計算資產(chǎn)負(fù)債率,用同樣的方式查找出資產(chǎn)合計的期末數(shù)值,點(diǎn)擊除號,然后查找出負(fù)債合計的期末數(shù)值。⑤計算產(chǎn)權(quán)比率,可以復(fù)制C6單元格中負(fù)債合計的查找公式,然后除以所有者權(quán)益的查找公式。⑥計算有形凈值債務(wù)率,可以先復(fù)制C7單元格的公式,粘貼到C8單元格中,然后減去,無形資產(chǎn)的查找公式。在所有者權(quán)益合計和無形資產(chǎn)的查找公式前后,加上小括號,改變運(yùn)算順序。財務(wù)比率表中的第一大塊償債能力的所有財務(wù)指標(biāo)計算完成,接著計算營運(yùn)能力。其次計算營運(yùn)能力指標(biāo),①計算存貨周轉(zhuǎn)率,在C9單元格的編輯欄中輸入Vlookup函數(shù)、lookup_value欄輸入"營業(yè)成本"、table_array欄選中"利潤表"工作表中的A4:B24單元格,、col_index_num欄中輸入"2"、最后的range_lookup欄中輸入"false"、單擊確定,輸入除號和小括號、輸入存貨的期末數(shù)值查找公式減去存貨期初數(shù)之差的公式除以2,涉及到的存貨平均余額要加小括號,注意運(yùn)算順序。②計算流動資產(chǎn)周轉(zhuǎn)率,與上一操作步驟相似,只是將lookup_value欄該成輸入"營業(yè)收入",存貨的查找公式改成流動資產(chǎn)合計查找公式。③計算固定資產(chǎn)周轉(zhuǎn)率和總資產(chǎn)周轉(zhuǎn)率,與上一操作步驟一樣。只是將除數(shù)改成固定資產(chǎn)平均余額和總資產(chǎn)平均余額。第八步,然后計算盈利能力指標(biāo),①計算營業(yè)利潤率,利用Vlookup函數(shù)查找營業(yè)利潤和營業(yè)收入數(shù)值,然后相除。②計算總資產(chǎn)凈利率,利用Vlookup函數(shù)查找出凈利潤的本期數(shù)值,除以資產(chǎn)合計的平均余額。③計算營業(yè)毛利率,可以直接從上述的公式中復(fù)制營業(yè)收入和營業(yè)成本的查找公式,然后進(jìn)行相減,最后相除。④計算凈資產(chǎn)收益率和資本金利潤率,直接利用Vlookup函數(shù)查找出凈利潤的本期數(shù)值,然后除以所有者權(quán)益和實(shí)收資本查找的期末數(shù)值。計算發(fā)展能力指標(biāo),①計算營業(yè)收入增長率,首先利用Vlookup函數(shù)查找出營業(yè)收入的本期金額;然后,減去營業(yè)收入的上期金額,再除以營業(yè)收入的上期金額。②計算總資產(chǎn)增長率,可以用Vlookup函數(shù)查找出資產(chǎn)合計的期末數(shù)值和期初數(shù)值,兩個數(shù)值相減,得數(shù)再除以資產(chǎn)合計的期初數(shù)值。③計算營業(yè)利潤增長率,可以用Vlookup函數(shù)查找出營業(yè)利潤的本期金額和上期金額,然后相減,得數(shù)再除以營業(yè)利潤的上期金額。④計算凈利潤增長率,可以用Vlookup函數(shù)查找出凈利潤的本期金額和上期金額,然后相減,得數(shù)再除以凈利潤的上期金額。⑤計算凈資產(chǎn)增長率,首先利用Vlookup函數(shù)查找出所有者權(quán)益的期末金額;然后,減去所有者權(quán)益的的期初金額,再除以所有者權(quán)益的期初金額。圖14：財務(wù)比率計算公式截圖上述所有操作都基于Exce12016，而且要在“財務(wù)比率表”工作表下單擊菜單欄的公式，在公式審核欄中點(diǎn)擊“顯示公式”。在第五步至第十步的操作下完成“財務(wù)比率表”工作表，如上圖圖4所示。對上述模型進(jìn)行驗證，在“財務(wù)比率表”工作表下單擊菜單欄的公式，在公式審核欄中再次點(diǎn)擊“顯示公式”。所有EXCEL公式變成財務(wù)指標(biāo)的數(shù)值，然后再加上公式注釋一欄，能清晰的表示財務(wù)指標(biāo)的原始公式。驗證結(jié)果如圖5所示。由上述引述數(shù)據(jù)后，得到數(shù)據(jù)，可得出銷售利潤分析，總資產(chǎn)報酬率分析，凈資產(chǎn)收益率分析，費(fèi)用利潤率分析，具體數(shù)據(jù)分析如下。5.2銷售利潤分析從企業(yè)盈利實(shí)現(xiàn)能力這個指標(biāo)分析來看,樂健體育的整體主營銷售業(yè)務(wù)收入利潤增長不穩(wěn)定,3年間的利潤增長幅度差別并不大,2018年中期開始整個企業(yè)的整體主營銷售業(yè)務(wù)收入增長水平就在下降,而若從主營業(yè)務(wù)的凈利潤水平來看,則說明企業(yè)的利潤增長下滑情況與整體主營銷售業(yè)務(wù)收入一樣,說明整個企業(yè)的整體主營業(yè)務(wù)利潤帶來的經(jīng)濟(jì)收入正在不斷下降,而整個企業(yè)的整體凈利潤在近三年來一直都在下降,從這一點(diǎn)可以充分看出,企業(yè)的相關(guān)產(chǎn)品在中國市場上的實(shí)際銷售量正在不斷下降,給整個企業(yè)自身帶來的經(jīng)濟(jì)收益也一直在不斷下降。表1：2018年-2020年銷售利潤分析表表2-12017年-2019年銷售毛利率分析表201720182019主營業(yè)務(wù)收入264893028554302405920主營業(yè)務(wù)利潤10041501140770956240凈利潤407840527970423490表1是銷售利潤分析表，由表1可知，樂健體育2017-2019年的主營業(yè)務(wù)收入分別為264.89萬元、285.54萬元、240.59萬元，由此可以看出公司的營業(yè)收入呈現(xiàn)下降的趨勢。公司在該三年間的成本費(fèi)用總額呈現(xiàn)上升的趨勢。從表1還可以看出公司2017-2019年的凈利潤分別為100萬元、114萬元、95.62萬元，凈利潤分別為40.78萬、52.79萬、42.34萬元，也都在逐年遞減，所以該公司銷售凈利率的下降主要是主營業(yè)務(wù)收入下降和成本費(fèi)用總額的上升導(dǎo)致的。公司要想提高銷售凈利率，就必須控制公司的成本費(fèi)用總額和增加公司的主營業(yè)務(wù)收入。5.3總資產(chǎn)報酬率分析總資產(chǎn)報酬率分析是對一個企業(yè)的總利用資產(chǎn)的綜合利用實(shí)際情況的綜合分析,企業(yè)的對總資產(chǎn)的實(shí)際利用情況可以為企業(yè)帶來收益,而總資產(chǎn)報酬率則可以反映企業(yè)在利用資產(chǎn)帶來的收益情況。表2：2017-2019總資產(chǎn)報酬率分析表201720182019總資產(chǎn)報酬率23.5623.8619.46行業(yè)內(nèi)平均水平20.3123.1223.15表2是2017-2019總資產(chǎn)報酬率分析表。由表2可知，樂健有限公司2017-2019年的總資產(chǎn)報酬率分別為23.56%、23.86%、19.46%，行業(yè)內(nèi)平均水平分別為20.31%、23.12%、23.15%。從上表2-3中我們可以明顯看出樂健體育的總固定資產(chǎn)收益報酬率,在2019年中期下降最嚴(yán)重,表明這個企業(yè)對總固定資產(chǎn)收益帶來的長期收益并不高,并且收益在繼續(xù)下降,在2019年以嚴(yán)重的速度低于相同行業(yè)內(nèi)的國際平均水平了,樂健體育企業(yè)總資產(chǎn)報酬率的嚴(yán)重下降也直接導(dǎo)致表明企業(yè)的長期盈利實(shí)現(xiàn)能力有所下降。5.4凈資產(chǎn)收益率分析凈資產(chǎn)越低收益率越高指的也就是公司企業(yè)對固定投資者的所有資金的綜合利用分配能力,凈資產(chǎn)越高收益率最低越高,說明一個企業(yè)對固定投資者的長期資金管理利用的越充分,也就越代表了一個企業(yè)的長期資金風(fēng)險管理綜合能力,凈資產(chǎn)收益率越高,企業(yè)的盈利能務(wù)越強(qiáng)。表3：2017-2019凈資產(chǎn)收益率分析表201720182019凈資產(chǎn)收益率44.4440.9932.13行業(yè)內(nèi)平均水平40.1230.2931.27從上表3中我們可以明顯看出樂健體育的企業(yè)凈資產(chǎn)平均收益率仍然在不斷的出現(xiàn)下降,說明中小企業(yè)對固定投資者的流動資金的綜合利用管理能力與風(fēng)險管理制約能力越來越差,資產(chǎn)增值帶來的資金收益分配情況也不斷下降嚴(yán)重,在2019達(dá)到了下降后來到了32.13%,接近于整個行業(yè)內(nèi)的最低水平。5.5費(fèi)用利潤率分析表4：2017-2019年銷售費(fèi)用利潤率分析表201720182019費(fèi)用利潤率22.5427.9330.60行業(yè)內(nèi)平均水平23.2621.4532.12表4是2017-2019年銷售費(fèi)用利潤率分析表。由表2可知，樂健有限公司2017-2019年的費(fèi)用利潤率分別為22.54%、27.93%、30.60%，行業(yè)內(nèi)平均水平分別為23.26%、21.45%、32.12%。可以明顯看出樂健體育的行業(yè)費(fèi)用行業(yè)利潤率的平均水平從2017年開始增長起來到2018年的27.93%就已經(jīng)開始再次出現(xiàn)大幅下降的特殊情況,這主要可能是因為2018年以來國家社會發(fā)展經(jīng)濟(jì)速度開始下行,我國的裝備制造業(yè)以及未來人們的生活消費(fèi)都開始受到一定的因素影響,所以才會造成樂健體育的費(fèi)用沒有增長,但行業(yè)收益沒有下降的特殊情況,而通過對比這個行業(yè)內(nèi)的收益平均水平情況來看,樂健體育2019年收益低于這個行業(yè)內(nèi)的平均水平,說明這個行業(yè)在2019年的中期總體運(yùn)營成本的實(shí)際回報率比較高。6模型驗證在錄入公式后，根據(jù)樂健體育年財務(wù)報表數(shù)據(jù)，系統(tǒng)自動地得出了古丈縣全成林業(yè)有限責(zé)任公司財務(wù)比率分析的數(shù)據(jù)。圖15：驗證結(jié)果表利用EXCEL軟件，利用表格和框架圖更具體更清楚地體現(xiàn)出來各項指標(biāo)的運(yùn)算結(jié)果，簡單明了，便于做出分析。在企業(yè)實(shí)際應(yīng)用操作中，根據(jù)數(shù)據(jù)分析能更加清楚地了解整個企業(yè)具體的經(jīng)營狀況以及盈利能力。從計算出的數(shù)據(jù)分析綜合表可以看出以下幾點(diǎn):收益率:凈資產(chǎn)收益率指標(biāo)的高低反映了投資者的凈資產(chǎn)獲