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