Chương 2 Excel với hoạch định tài chính và dự báo kinh doanh

ThS. Nguyễn Thị Khiêm Hòa Khoa Công nghệ thông tin Trƣờng ĐH Ngân hàng TP HCM 22/03/20121 EXCEL VỚI HOẠCH ĐỊNH TÀI CHÍNH VÀ DỰ BÁO KINH DOANH CHƯƠNG 2 Mục tiêu 22/03/20122 1. Nắm đƣợc các phƣơng pháp, kỹ thuật cơ bản đƣợc sử dụng trong công tác hoạch định tài chính và dự báo kinh doanh trong doanh nghiệp. 2. Sử dụng đƣợc phần mềm MS Excel để thực hiện các công việc kể trên. I. Hoạch định và kiểm soát tài chính II. Dự báo kinh doanh Nội dung 22/03/20123  [1] Đinh Thế Hiển, Excel ứng dụng phân tích hoạt động kinh doanh và tài chính kế toán, Nhà xuất bản Lao động Xã hội, 2009.  [2] Nguyễn Tấn Bình, Phân tích quản trị tài chính, Nhà xuất bản Thống kê, 2009. 22/03/20124 Tài liệu tham khảo I. Hoạch định và kiểm soát tài chính 22/03/20125 1. Lập báo cáo tài chính dự toán 2. Phân tích rủi ro 3. Tính lại chi phí lãi vay 4. Bài toán điểm hòa vốn 1. Lập báo cáo tài chính dự toán 22/03/20126 Khái niệm “báo cáo tài chính dự toán” Dự báo theo tỷ lệ phần trăm doanh thu Khái niệm “báo cáo tài chính dự toán” 22/03/20127 Công cụ dự báo tài chính, dự báo những gì mà các báo cáo tài chính thực sẽ thể hiện vào cuối kỳ. Mục đích: Ƣớc tính lợi nhuận và nhu cầu vốn cần huy động từ bên ngoài.  Phƣơng pháp: Dự báo theo tỷ lệ phần trăm doanh thu. Dự báo theo tỷ lệ phần trăm doanh thu 22/03/20128 Xem xét mối quan hệ giữa các khoản mục trong Báo cáo thu nhập và Bảng cân đối kế toán với giá trị doanh thu và doanh thu dự kiến. Ưu điểm: đơn giản, cho phép dự báo hầu hết các biến cố tài chính quan trọng nhất. Trình tự thực hiện 22/03/20129 1. Xem xét các dữ liệu lịch sử để xác định các khoản mục nào của báo cáo tài chính thay đổi tỷ lệ với doanh thu. 2. Dự báo doanh thu. 3. Dự báo cho từng khoản mục trong các báo cáo bằng phương pháp ngoại suy (áp dụng các tỷ lệ của kỳ trước với doanh thu dự kiến). Ví dụ minh họa 22/03/201210  Lập báo cáo tài chính dự toán và ƣớc tính nhu cầu vốn huy động bên ngoài năm 2009 của công ty XYZ. Dữ liệu lịch sử: các báo cáo tài chính từ 2005 – 2008. Dự kiến 2009: doanh thu tăng 45%, không chia cổ tức, mua mới TSCĐ 270 triệu đồng, vốn CSH không đổi, nợ dài hạn không phát sinh, chi phí khấu hao nhƣ năm 2008. Tính tỷ lệ phần trăm với lệnh chép – dán đặc biệt 22/03/201211 Copy ô chứa doanh thu, chọn vùng cần tính tỷ lệ, vào Edit  Paste Special.  Trong hộp thoại Paste Special, chọn Values trong mục Paste và Divide trong mục Operation. Định dạng lại vùng kết quả theo dạng tỷ lệ phần trăm. Xem 2. Phân tích rủi ro 22/03/201212  Phân tích độ nhạy.  Phân tích tình huống.  Phân tích mô phỏng.  Phân tích rủi ro với các hàm xác suất – thống kê. Phân tích độ nhạy 22/03/201213 Cho phép đánh giá mức độ ảnh hƣởng của các yếu tố đầu vào đối với kết quả bài toán. Ý nghĩa: Cung cấp các thông tin về mức độ biến thiên có thể có của các thông số cần biết. Giúp các nhà QL xác định đƣợc các yếu tố có ảnh hƣởng mạnh nhất tới kết quả dự báo để có các quyết sách phù hợp. Ví dụ: Nhu cầu huy động vốn 22/03/201214  Theo dự báo, nhu cầu huy động vốn từ bên ngoài trong năm 2009 của công ty XYZ (Ví dụ trƣớc) là 583 triệu đồng. Phân tích một số yếu tố đầu vào nhạy đối với kết quả dự báo trên. Cho phép lần lƣợt đánh giá mức độ tác động của từng yếu tố đầu vào tới kết quả bài toán. VD: đánh giá tác động của tỷ lệ tăng trƣởng doanh thu và tỷ lệ giá vốn hàng bán tới nhu cầu huy động thêm vốn. 22/03/201215 Phân tích độ nhạy một chiều: Phân tích độ nhạy một chiều: (tt) 22/03/201216 Phân tích độ nhạy hai chiều: 22/03/201217 Cho phép lần lƣợt đánh giá tác động đồng thời của hai yếu tố đầu vào tới kết quả bài toán. VD: đánh giá tác động của tỷ lệ tăng trƣởng doanh thu và tỷ lệ giá vốn hàng bán tới nhu cầu huy động thêm vốn. Phân tích độ nhạy hai chiều: 22/03/201218 Thực hiện trên Excel 22/03/201219  Sử dụng chức năng Data Table  Excel 2003: Data  Data Table.  Excel 2010: Data | What – If analysis | Data Table. Xem Ghi chú: Bảng phân tích độ nhạy phải đƣợc đặt trên cùng một sheet với Báo cáo tài chính dự toán. Phân tích tình huống 22/03/201220 Cho phép khảo sát sự ảnh hƣởng của một nhóm các yếu tố đầu vào đối với kết quả của bài toán trong một số trƣờng hợp nhất định. Ví dụ 22/03/201221 Với sự tăng trƣởng doanh thu của công ty ABCD trong năm 2009 là 45%, nhu cầu huy động vốn từ bên ngoài phụ thuộc vào tỷ lệ giá vốn hàng bán, tỷ lệ các khoản phải thu, tỷ lệ hàng tồn kho và tỷ lệ các khoản phải trả. Các tình huống tốt nhất, xấu nhất và kỳ vọng đƣợc xác định (determinant): Các tình huống khảo sát Yếu tố Tốt Kỳ vọng Xấu TL giá vốn hàng bán 55.0% 61.5% 70.0% TL khoản phải thu 19.0% 21.2% 23.0% TL hàng tồn kho 22.0% 23.3% 24.0% TL khoản phải trả 14.0% 12.6% 10.0% Vốn huy động ? 583 ? 22/03/201222 Thực hiện phân tích trên Excel 22/03/201223  Sử dụng công cụ Scenario Manager Excel 2003: Tools  Scenarios. Excel 2010: Data | What – If Analysis | Scenario Manager.  Thực hiện: Khởi động Scenario Manager Mô tả các tình huống (tên, các yếu tố đầu vào, giá trị). Chỉ định biến kết quả và kiểu báo cáo. Hộp thoại Scenario Manager 22/03/201224 Mô tả tình huống 22/03/201225 Nhập giá trị tập yếu tố đầu vào 22/03/201226 Chỉ định biến kết quả và kiểu báo cáo 22/03/201227 Kết quả thực hiện 22/03/201228 Xem Phân tích mô phỏng 22/03/201229  Là sự mở rộng của phân tích độ nhạy và phân tích tình huống sự trợ giúp của máy tính. Kết quả: đồ thị phân bố xác suất của biến kết quả, cho phép tính xác suất của các kết quả khác nhau.  Thực hiện: Sử dụng chƣơng trình Crystal Ball (cài đặt vào Excel Add-Ins trƣớc khi sử dụng).  Phân tích hiệu quả tài chính trong kỳ kế hoạch => xem xét kết quả dựa trên các các giá trị có khả năng xảy ra cao nhất (kỳ vọng) và các giá trị khác có khả năng xảy ra. Kỳ vọng: bình quân của các giá trị có khả năng xảy ra nhƣ nhau (hoặc bình quân có trọng số của các giá trị có khả năng xảy ra khác nhau). 22/03/201230 Phân tích rủi ro với các hàm XS-TK Độ lệch chuẩn: Biên độ dao động của KV: độ lệch chuẩn. Các giá trị tin cậy trong phạm vi kỳ vọng +/- độ lệch chuẩn. Hệ số phương sai (= độ lệch chuẩn/kỳ vọng): Độ tin cậy của kỳ vọng. Nếu hệ số PS kỳ vọng có độ tin cậy cao, ngƣợc lại, kỳ vọng có khả năng biến động lớn.. 22/03/201231 Phân tích rủi ro với các hàm XS-TK (tt)  Tập đoàn X gồm 5 công ty thành viên (Công ty 1, Công ty 2, Công ty 3, Công ty 4, Công ty 5). Cho báo cáo kết quả lợi nhuận năm 2010 và dự báo kết quả lợi nhuận năm 2011 của tập đoàn. Xem 22/03/201232 Ví dụ Năm 2010: Tính kỳ vọng, độ lệch chuẩn tỷ suất lợi nhận hàng tháng của từng công ty của toàn tập đoàn, nhận xét về hiệu quả đầu tƣ của từng công ty và toàn tập đoàn. Năm 2011: Tính kỳ vọng, độ lệch chuẩn tỷ suất lợi nhận cả năm của từng công ty của toàn tập đoàn, nhận xét về hiệu quả đầu tƣ của từng công ty và toàn tập đoàn. 22/03/201233 Yêu cầu  Tính kỳ vọng: Hàm AVERAGE  Tính phương sai: Hàm VAR.  Tính độ lệch chuẩn: Hàm STDEV. 22/03/201234 Hướng dẫn Tính kỳ vọng, phương sai và độ lệch chuẩn năm 2010  Tính kỳ vọng: Sử dụng hàm = SUMPRODUCT(khối GT XS * khối GT tính toán) Hoặc sử dụng công thức mảng {=SUM(khối GT XS * khối GT tính toán)} 22/03/201235 Tính kỳ vọng, phương sai và độ lệch chuẩn năm 2011  Tính phương sai: hoặc Sử dụng công thức mảng {=SUM(khối GT XS * (khối GT tính toán - kỳ vọng) ^2)}  Tính độ lệch chuẩn: sử dụng công thức =SQRT(phương sai) Xem 22/03/201236 Tính kỳ vọng, phương sai và độ lệch chuẩn năm 2011 (tt) 3. Tính lại chi phí lãi vay 22/03/201237  Mối quan hệ qua lại giữa chi phí lãi vay và số tiền vay  Không thể xác định chính xác chi phí lãi vay khi chƣa biết số tiền vay.  Số tiền vay phụ thuộc một phần vào chi phí lãi vay.  Giải pháp:  Bỏ qua .  Tính toán trên Excel. Tính toán trên Excel 22/03/201238  Lập bảng tính nhu cầu vay mới năm tới với chi phí lãi vay tạm tính bằng chi phí lãi vay năm trƣớc. Đặt lại chế độ tính toán thủ công cho Excel. Nhập công thức tích chi phí lãi vay. Nhấn để thực hiện tính toán.  Excel 2003: Vào Tools/Option chọn thẻ Calculation. Chọn Calculation Manual. Đánh dấu hộp kiểm Iteration. Nhập giá trị Maximum iteration. Nhấp OK. 22/03/201239 Đặt lại chế độ tính toán thủ công cho Excel Hộp thoại Calculation 22/03/201240 Ví dụ: Tính lại nhu cầu vay mới 22/03/201241  Tính lại nhu cầu vay mới năm 2009 của công ty ABCD. Excel 2003 Excel 2010 4. Bài toán điểm hòa vốn 22/03/201242 Mục tiêu: Xác định số sản phẩm cần sản xuất/kinh doanh để đạt cân bằng giữa doanh thu và chi phí (điểm hòa vốn). Dữ liệu F: Định phí. v: Biến phí đơn vị. r: giá bán đơn vị. Bài toán điểm hòa vốn (tt) 22/03/201243 Biến Q: Số lƣợng sản phẩm. Biến trung gian TC: Tổng chi phí. DT: Doanh thu. Hàm mục tiêu LN: Lợi nhuận (bằng 0 tại ĐHV). Bài toán điểm hòa vốn (tt) 22/03/201244 Các phương trình quan hệ LN = DT – TC DT = r * Q TC = F + v * Q Bài toán điểm hòa vốn (tt) 22/03/201245 Công thức tính điểm hòa vốn  LN = DT – CP = r * Q – (F + v * Q) = 0 Điểm hòa vốn QBE = F/(r – v) Ví dụ minh họa 22/03/201246  Một xí nghiệp lập kế hoạch sản xuất loại hàng A với định phí là 15 triệu đồng, giá bán 20,000 đồngvà chi phí sản xuất là 10,000 đồng trên một đơn vị sản phẩm.  Yêu cầu: Xác định điểm hòa vốn cho bài toán trên. Xem II. Dự báo kinh tế 22/03/201247 1. Dữ liệu cho dự báo 2. Các dự báo Bình quân diễn biến 3. Dự báo bằng các hàm hồi qui 4. Dự báo bằng các hàm liên tiến 5. Phân tích dự báo bằng phương trình hồi qui tuyến tính đa biến 1. Dữ liệu cho dự báo 22/03/201248  Dự báo đƣợc thực hiện trên việc nghiên cứu, phân tích chuỗi dữ liệu vào => dòng cơ sở (baseline).  Dòng cơ sở: chuỗi số liệu quan sát đƣợc qua thời gian (chuỗi thời gian – Time series) về một sự kiện, hiện tƣợng nào đó xảy ra trong thế giới thực. Yêu cầu 22/03/201249  Các số liệu quan sát phải đƣợc sắp xếp theo thời gian.  Các kỳ quan sát có độ dài bằng nhau.  Số liệu quan sát phải xuất phát từ cùng một điểm trong mỗi kỳ.  Không đƣợc phép để thiếu dữ liệu.  Một số công cụ dự báo của Excel yêu cầu số liệu quan sát phải đƣợc sắp xếp theo chiều dọc (theo cột). 2. Các dự báo bình quân diễn tiến (Moving Average) 22/03/201250  Phƣơng pháp đơn giản, tính số dự báo của một kỳ nhất định bằng bình quân số một số kỳ trƣớc đó.  Ƣu điểm :  Dễ tính toán.  Đáp ứng đƣợc các thay đổi mới nhất trong chuỗi thời gian. Ví dụ: Dịch vụ kỹ thuật 22/03/201251  Công ty dịch vụ - tƣ vấn kỹ thuật X nhận hợp đồng lắp đặt, bảo trì và sửa chữa thiết bị cho một doanh nghiệp. Nhằm phục vụ tốt cho công tác lập kế hoạch, công ty lập bảng theo dõi số sự cố kỹ thuật xảy ra mỗi tuần và dự báo số sự cố có khả năng xảy ra trong tuần kế tiếp. Hãy sử dụng Excel để giúp công ty thực hiện công việc trên. Thống kê sự cố kỹ thuật Tuần Số sự cố 1 17 2 12 3 114 4 8 5 11 6 10 7 6 22/03/201252 Thực hiện 22/03/201253  Lập bảng giá trị bình quân diễn tiến với hàm AVERAGE.  Sử dụng biểu đồ với đƣờng xu hƣớng.  Sử dụng công cụ Moving Average. Lưu ý: Công cụ Moving Average cho kết quả dự báo sớm hơn 1 kỳ so với thực tế. xem 3. Dự báo bằng các hàm hồi qui 22/03/201254  Phƣơng pháp bình quân: chỉ cho phép lập dự trù cho các kỳ không vƣợt quá điểm cuối cùng của chuỗi thời gian.  Phƣơng pháp hồi qui: ƣớc định mối tƣơng quan giữa các số quan sát với một biến khác, có thể là vị trí của số quan sát trong chuỗi thời gian hoặc thời điểm quan sát.  Inrar : Hồi qui tuyến tính y = n.x + b Logarithmic: Hồi qui logarit y = n.ln(x) + b  Polynomial (n): Hồi qui đa thức (bậc n) y = bnx n + bn-1x n-1 + …+ b1x + b0 22/03/201255 Các kiểu đường hồi qui trong Excel  Power: Hồi qui lũy thừa y = b.xn Exponential: Hồi qui số mũ y = b.nx 22/03/201256 Các kiểu đường hồi qui trong Excel Tạo các dự báo hồi qui tuyến tính 22/03/201257  Phƣơng cách đơn giản để tạo một dự báo với hàm hồi quy tuyến n tính y = nx + b  Thực hiện trong Excel: sử dụng hàm FRECAST hoặc hàm mảng TREND. Cú pháp FORECAST(x, Known_Y’s, Known_X’s) Công dụng: Hàm hồi qui truyến tính, dựa trên tập giá trị quan sát Known_Y’s tại chuỗi thời điểm Known_X’s, cho biết giá trị dự báo tại thời điểm tƣơng lai x. 22/03/201258 Hàm FORECAST Hàm TREND 22/03/201259  Cú pháp TREND(Known_Y’s, Known_X’s, New_X’s, Const) Công dụng: Hàm mảng, dựa trên tập giá trị quan sát Known_Y’s tại chuỗi thời điểm Known_X’s, tính các giá trị dự báo tại chuỗi thời điểm tƣơng lai New_X’s. Giá trị b = 0 nếu const = FALSE, đƣợc tính bình thƣờng nếu const = TRUE hoặc bỏ qua. Ví dụ: Dịch vụ kỹ thuật 22/03/201260 Xem Tạo các dự báo hồi qui phi tuyến 22/03/201261  Sử dụng trong trƣờng hợp các số quan sát biến động lên hoặc xuống theo một đƣờng cong tƣơng đối lớn.  Thực hiện trong Excel: sử dụng hàm GROWTH. Hàm GROWTH 22/03/201262  Cú pháp GROWTH(Known_Y’s, Known_X’s, New_X’s, Const) Công dụng: Tƣơng tự nhƣ hàm TREND song các giá trị đƣợc dự báo với quan hệ y = b*n^x. Giá trị b đƣợc tính bình thƣờng nếu const = TRUE hoặc bỏ qua, b = 1 nếu const = FALSE. Ví dụ: Doanh thu sản phẩm mới 22/03/201263  Một công ty kinh doanh mới tung ra thị trƣờng một loại sản phẩm mới. Do là loại sản phẩm độc đáo, chất lƣợng tốt, giá cả phải chăng nên công ty đã bán đƣợc hàng với doanh số ngày càng cao (xem bảng thống kê). Hãy sử dụng Excel để dự báo doanh thu của công ty trong những tháng sắp tới. Thống kê doanh thu sản phẩm Tháng Doanh thu Tháng Doanh thu 1 10 5 20 2 11 6 30 3 12.5 7 45 4 15 8 70 22/03/201264 Thực hiện 22/03/201265 Xem Tạo các biểu đồ hồi qui trên Excel 22/03/201266 Nhấp phải chuột vào chuỗi dữ liệu trong biểu đồ, thực hiện chức năng Add Trendline. Xem 4. Dự báo bằng các hàm liên tiến 22/03/201267 Áp dụng phƣơng trình cơ bản F(t+1) = F(t) + a.e(t) Trong đó: t, t + 1: các kỳ (thời điểm) liên tiếp F(t), F(t+1): số dự báo trong các kỳ t và t+1. a: hằng liên tiến. e(t): sai số giữa số dự báo và số quan sát tại thời điểm t. Công cụ Exponential Smoothing 22/03/201268 Công cụ dự báo liên tiến, thành phần trong bộ công cụ Data Analysis, Các tùy biến trong hộp thoại Exponential Smoothings:  Input range: Vùng nhập. Damping factor: Thừa số cản (= 1 – a). Output range: Vùng xuất. Chart Output: Xuất biểu đồ. Hộp thoại Exponential Smoothing 22/03/201269 Ví dụ: Cơ sở cho thuê xe 22/03/201270  Một cơ sở cho thuê xe du lịch tiến hành thống kê số xe cho thuê mỗi ngày (xem bảng) và dự trù số xe cần chuẩn bị để sẵn sàng cho thuê vào ngày kế tiếp. Hãy sử dụng Excel để thực hiện công việc trên. Xem  Phƣơng pháp đƣợc sử dụng phổ biến, cho phép nghiên cứu tác động của nhiều nhân tố đối với kết quả bài toán.  Đặc biệt thích hợp với các doanh nghiệp có sản phẩm đặc trƣng, thị phần lớn. 22/03/201271 5. Phân tích dự báo bằng phƣơng trình hồi qui đa biến  Phƣơng trình mẫu: Y = b0 + b1x1 + b2x2 + … + bnxn +  Trong đó Y: biến kết quả. b0: hằng số, gốc của phƣơng trình. x1, x2, … xn: biến độc lập. b1, b2, … bn: hệ số biến độc lập : mức sai số của các tác động khác. 22/03/201272 Phƣơng trình hồi qui tuyến tính đa biến Công ty ABC thống kê tình hình kinh doanh mặt hàng X trong 3 năm 2008, 2009 và 2010 (Xem ). Yêu cầu: Xây dựng phƣơng trình hồi qui tuyến tính đa biến, qua đó dự báo doanh thu trong các tháng tới và mối quan hệ giữa các chỉ số. 22/03/201273 Ví dụ  Sử dụng công cụ Regression (bộ công cụ Data Analysis ). Các tùy biến chính trong hộp thoại công cụ: Khởi động tiện ích.  Input Y range: khối giá trị biến Y .  Input X range.: Khối giá trị các biến X Labels : dòng đầu trong các vùng là nhãn. Output options: Tùy biến vùng xuất. 22/03/201274 Xây dựng phƣơng trình hồi qui tuyến tính đa biến trên Excel 22/03/201275 Xem 22/03/201276 Kết quả xuất Hệ số R = 0,99 => giữa các biến có mối tƣơng quan cao. Hệ số xác định R2 = 0.97 => mức độ cao về khả năng giải thích của các biến số.  Thông số độ dốc của biến Giá cả (X1) = - 338.02 biến động tỷ lệ nghịch với Sản lượng (biến Y ).  Thông số độ dốc của biến Chi phí QC (X2) = 13.03 >0 => tỷ lệ thuận với Sản lượng. 22/03/201277 Nhận định kết quả Xem xét giá trị t-stat (kiểm định thống kê) của các biến độc lập:  |t-stat| > 2.58 : độ tin cậy 99%  |t-stat| > 1.96 : độ tin cậy 95%  |t-stat| > 1.64 : độ tin cậy 90% Kết quả kiểm định: Biến X1 có |t-stat||= 11.24, biến X2 có |t-stat||= 24.98 => mô hình có độ tin cậy rất cao, phƣơng trình hồi qui có ý nghĩa thực tiễn. 22/03/201278 Kiểm định mô hình hồi qui  Phƣơng trình hồi qui: Y = -199.93 - 338.02X1 + 13.03 X2 Ứng dụng: Phân tích, dự báo, đánh giá mức độ tác động của việc thay đổi giá bán và chi phí QC tới sản lƣợng, phục vụ cho việc xây dựng các chiến lƣợc kinh doanh của doanh nghiệp. 22/03/201279 Xây dựng mô hình dự báo hồi qui  Lập bảng phân tích độ nhạy hai chiều, xem xét tác động của việc thay đổi giá bán và chi phí quảng cáo tới sản lƣợng  Phạm vi biến động: Giá bán: từ 3.5 tới 4.75 triệu đồng/tấn. Chi phí QC: từ 300 tới 400 triệu đồng. Xem 22/03/201280 Ví dụ 22/03/201281 HẾT CHƢƠNG 2