Chương 1 - Tổng quan về mô hình hóa (Financial Modeling In Excel) PDF

Ứng dụng bảng tính trong lập mô hình tài chính Th.S Trương Thị Hoài Thơ TỔNG QUAN VỀ HỌC PHẦN WHAT HOW WHY PHƯƠNG PHÁP ĐÁNH GIÁ Đánh giá thường xuyên: 20% Lý thuyết Kiểm tra gi...

Ứng dụng bảng tính trong lập mô hình tài chính Th.S Trương Thị Hoài Thơ TỔNG QUAN VỀ HỌC PHẦN WHAT HOW WHY PHƯƠNG PHÁP ĐÁNH GIÁ Đánh giá thường xuyên: 20% Lý thuyết Kiểm tra giữa kỳ: 30% Kiểm tra cuối kỳ: 50% PP đánh giá Chuẩn bị bài: 20% Thực hành Báo cáo thực hành: 80% NỘI DUNG HỌC PHẦN Chương 1: Tổng quan về mô hình hóa và những thông lệ tốt nhất Chương 2: Thiết kế và lập kế hoạch cho mô hình Chương 3: Xây dựng, thử nghiệm và kiểm tra mô hình Chương 4: Phân tích độ nhạy và ngữ cảnh, mô phỏng và tối ưu hóa  Tài liệu học tập Sách, giáo trình chính: Rees, Michael. Principles of financial modelling: model design and best practices using Excel and VBA. UK: John Wiley & Sons, 2018. Tài liệu tham khảo Benninga, Simon. Financial modeling. 5th ed. US: MIT press, 2021. Häcker, Joachim, and Dietmar Ernst. Financial Modeling: An Introductory Guide to Excel and VBA Applications in Finance. Springer, 2017. Fairhurst, Danielle Stein. Using Excel for business analysis: a guide to financial modelling fundamentals. John Wiley & Sons, 2015. Chương 1: Tổng quan về mô hình hóa NỘI DUNG CHƯƠNG 1 Mô hình là gì? 1 Các bước trong quy trình mô hình hóa 2 Các thuật ngữ trong mô hình hóa 3 Các hàm toán học chính 4  Mục tiêu chương 1 Tiếp xúc với ngôn ngữ mô hình hóa Nhận diện đa dạng các mô hình kinh doanh định lượng và ứng dụng của chúng Tìm hiểu quy trình mô hình hóa và phân tích chi tiết các mô hình Liên kết các đặc điểm của quy trình kinh doanh với mô hình phù hợp Hiểu được giá trị và hạn chế của các mô hình định lượng  Mô hình là gì? Là mô tả chính thức về một quy trình kinh doanh Thường bao gồm các phương trình toán học và/hoặc các biến ngẫu nhiên Là sự đơn giản hóa của một cấu trúc phức tạp. Thường dựa trên tập hợp các giả định Thường được triển khai trong một chương trình trên máy tính hoặc sử dụng bảng tính (vd: Excel)  Mô hình là gì? – Ví dụ về mô hình Giá của viên kim cương theo trọng lượng của nó Sự lây lan của một dịch bệnh theo thời gian Mối quan hệ giữa nhu cầu và giá của một sản phẩm Sự tiếp nhận một sản phẩm mới trên thị trường  Các mô hình được ứng dụng trong thực tế như thế nào? Dự đoán: tính toán một đầu ra duy nhất Giá dự kiến của một viên kim cương nặng 0,2 carat là bao nhiêu? Dự báo: Bao nhiêu người có nguy cơ bị nhiễm bệnh trong 6 tuần? Lập lịch trình– Bác sĩ nào có khả năng đến khám cho các bệnh nhân ngoại trú Tối ưu hóa: Mức giá nào sẽ tối đa hóa lợi nhuận?  Các mô hình được ứng dụng trong thực tế như thế nào? Xếp hạng và xác định mục tiêu: Với nguồn lực hạn chế, loại kim cương nào nên được đặt mục tiêu đầu tiên cho khách hàng tiềm năng? Kiểm tra các kịch bản giả định (What-if scenarios): Nếu tốc độ tăng trưởng theo tuần của dịch bệnh lên 20%, dự tính có bao nhiêu ca nhiễm trong 10 tuần tới?  Các mô hình được ứng dụng trong thực tế như thế nào? Giải thích ý nghĩa các hệ số trong mô hình: Chúng ta biết được điều gì từ hệ số -2.5 trong mô hình giá/nhu cầu? Đánh giá mức độ nhạy cảm của mô hình với các giả định chính  Lợi ích của mô hình hóa Tìm ra được những khoảng trống trong hiểu biết hiện tại Làm rõ các giả định Có mô tả rõ ràng về quy trình kinh doanh Tạo ra bộ nhớ của tổ chức Được sử dụng như một công cụ hỗ trợ ra quyết định Tạo ra sự hiểu biết về quy trình kinh doanh một cách bất ngờ Các bước trong quy trình mô hình hóa Xác định và định nghĩa Phân tích độ các đầu vào nhạy và đầu ra Phù hợp Triển khai mô Công thức với mục YES hóa mô hình hình đích Xác thực các Xác định rõ dự báo của phạm vi mô hình NO  Mô hình không phải lúc nào cũng phù hợp? Khi kết quả quan sát được khác biệt rất nhiều so với dự đoán của mô hình, có thể đúc rút được thông tin từ sự khác biệt này nếu nắm được nguyên nhân của nó. Mô hình hóa là một quá trình thay đổi và cải tiến liên tục. Xác định các điểm yếu và hạn chế, sau đó lặp lại các bước trong quy trình mô hình hóa để khắc phục chúng. Các khái niệm trong mô hình hóa Mô hình ngẫu nhiên Mô hình liên tục  Định hướng dữ liệu và định hướng lý thuyết Định hướng lý thuyết: đưa ra một tập hợp các giả định và mối quan hệ, hệ quả logic ở đây là gì? VD: Giả định rằng thị trường hiệu quả, giá của một quyền chọn cổ phiếu sẽ là bao nhiêu? Định hướng dữ liệu: cho trước một tập hợp các quan sát, làm thế nào để ước tính được quy trình cơ bản đã tạo ra chúng? VD: Dựa trên tệp dữ liệu khách hàng mang lại lợi nhuận và dữ liệu khách hàng không mang lại lợi nhuận, những đặc điểm nào có thể phân biệt 2 loại khách hàng này?  Mô hình tất định và mô hình ngẫu nhiên Mô hình tất định: với tập hợp đầu vào cố định, mô hình luôn cho cùng một kết quả đầu ra. VD: Đầu tư 100tr với lãi suất kép hàng năm là 5% trong 2 năm. Sau 2 năm, 100tr ban đầu sẽ luôn có giá trị là 110,25tr Mô hình ngẫu nhiên: thậm chí với tập hợp đầu vào cố định, đầu ra của mô hình có thể khác nhau tùy trường hợp. VD: Một người bỏ ra 100tr để mua vé số. Sau khi xổ số được rút, giá trị thu về được bao nhiêu tùy thuộc vào biến ngẫu nhiên liệu họ có trúng số hay không?  Mô hình liên tục và mô hình gián đoạn Đồng hồ có thể là đồng hồ kỹ thuật số hoặc đồng hồ kim Cũng giống như mô hình có thể bao gồm các biến liên tục và rời rạc Mô hình gián đoạn: đặc trưng bởi các giá trị riêng biệt Mô hình liên tục: là một quá trình liên tục với vô số các giá trị tiềm năng trong bất kỳ khoảng thời gian cố định nào  Mô hình động và mô hình tĩnh Mô hình tĩnh: ghi lại một ảnh chụp nhanh của quy trình kinh doanh VD: Dựa vào phần mềm đã được cài đặt, khả năng xâm hại của một trang web trong ngày hôm nay là bao nhiêu? Mô hình động: mô tả sự chuyển động từ trạng thái này sang trạng thái khác. Sự phát triển của chính quá trình này là điều đáng quan tâm. VD: Học viên sau khi tốt nghiệp khóa học nghề sẽ mất bao lâu để tìm được việc, nếu tìm được họ sẽ giữ công việc này được trong bao lâu?  Các hàm toán học chính Toán học là ngôn ngữ chính của mô hình Bốn hàm toán học chính cung cấp nền tảng cho mô hình định lượng: 1. Hàm tuyến tính (linear function) 2. Hàm số lũy thừa (power function) 3. Hàm mũ (expotional function) 4. Hàm log (log function)  Hàm tuyến tính 𝑦 = 𝑚𝑥 + 𝑏 Trong đó: x là đầu vào, y là đầu ra b là hệ số góc, m là độ dốc Đặc điểm của hàm tuyến tính là độ dốc không thay đổi, x thay đổi 1 đơn vị thì y thay đổi m đơn vị Hàm tuyến tính  Hàm số lũy thừa 𝑦 = 𝑥𝑚 Trong đó: x là cơ số, m là số mũ Đặc điểm của hàm số lũy thừa là khi x thay đổi một phần trăm (tỷ lệ) thì y thay đổi m phần trăm (tỷ lệ). Một số đặc điểm của hàm số lũy thừa: 1. 𝑥 𝑚 𝑥 𝑛 = 𝑥 𝑚+𝑛 −𝑚 1 2. 𝑥 = 𝑥𝑚 Hàm số lũy thừa  Hàm số mũ (tự nhiên) 𝑦 = 𝑒 𝑚𝑥 Trong đó: e là hằng số toán học: 2.71828… Lưu ý rằng so với hàm lũy thừa, x nằm ở số mũ, không phải cơ số. Đặc điểm của hàm mũ: phần trăm thay đổi của y tỷ lệ với chính y. Đối với các giá trị nhỏ của m (-0.2 ≤ m ≤ 0.2): x thay đổi một đơn vị thì y thay đổi xấp xỉ 100m%. VD: Nếu m = 0.05, thì khi x tăng 1 đơn vị thì y tăng 5% Hàm số mũ (tự nhiên)  Hàm log Hàm log thường được sử dụng để mô hình hóa các quy trình thể hiện lợi tức giảm dần theo quy mô Những quy trình này tăng nhưng với tốc độ giảm dần. Đặc điểm của hàm log: khi x thay đổi với một tỷ lệ không đổi thì y thay đổi với giá trị tuyệt đối. Hàm log  Hàm log 𝑦 = 𝑙𝑜𝑔𝑏 (𝑥) Trong đó: b là cơ số của hàm logarit Cơ số được thường được sử dụng nhiều nhất là số “e” và logarit được gọi là “logarit tự nhiên”. Phép đảo ngược của hàm số mũ (tự nhiên): 1. 𝑙𝑜𝑔𝑒 𝑒 𝑥 = 𝑥 2. 𝑒 𝑙𝑜𝑔𝑒 𝑥 = 𝑥 log(xy) = log(x) + log(y) Trong phạm vi học phần này, chúng ta sẽ thường xuyên sử dụng logarit tự nhiên và viết tắt là log(x). Hàm log Tỷ lệ thay đổi không đổi trong x tương ứng với giá trị thay đổi tuyệt đối trong y 4 hàm toán học chính Phụ lục: Tham chiếu, Bảng và Biểu đồ Trước khi chèn biểu đồ Nên dành thời gian để sắp xếp tập dữ liệu của thành một bảng. Việc tổ chức bảng đúng cách giúp Excel nhận ra cách nó nên được biểu diễn trên đồ thị. Biểu đồ trong Excel dựa trên chuỗi dữ liệu, và dữ liệu từ bảng thường được sắp xếp theo dạng cột Nếu xây dựng một bảng làm việc cập nhật khi dữ liệu thay đổi, thì biểu đồ cũng sẽ cập nhật theo. Để làm điều này, cần phải cẩn thận khi thêm và sao chép công thức trong bảng. Tham chiếu và sao chép công thức Công thức có tham chiếu ô là một phần quan trong khi xây dựng mô hình tài chính. Thay vì tạo một công thức cho mỗi ô trong bảng, bạn có thể tiết kiệm thời gian bằng cách sao chép công thức và dán vào một ô mới. Excel định nghĩa hai loại tham chiếu gồm: ▪ Tham chiếu tương đối: Liên kết đến một ô dựa trên vị trí của nó so với ô hiện tại. Nếu bạn sao chép loại tham chiếu này vào một ô khác, tham chiếu sẽ cập nhật. Theo mặc định, tất cả các tham chiếu ô đều là tương đối ▪ Tham chiếu tuyệt đối: Liên kết đến một ô cụ thể trong bảng tính. Nếu bạn sao chép loại tham chiếu này vào một ô khác, tham chiếu sẽ vẫn tham chiếu đến cùng một ô đó. Để thêm tham chiếu ô tuyệt đối, bạn viết dấu đô la ($) trước hàng và cột. ▪ Ngoài ra, cũng có thể có các tham chiếu hỗn hợp: trong đó chỉ có hàng hoặc cột được cố định Tham chiếu và sao chép công thức Ví dụ Tham chiếu tương đối Tham chiếu tuyệt đối Tham chiếu và sao chép công thức Kiểm tra tham chiếu ô: Khi sao chép một công thức, nên kiểm tra xem các tham chiếu của đã thay đổi hoặc giữ nguyên khi cần thiết hay chưa. Có hai cách để kiểm tra: Thanh công thức (Formula bar): Chọn ô và nhấp vào thanh công thức. Các tham chiếu trong công thức đã được mã hóa màu và các ô mà chúng tham chiếu được tô sáng bằng màu thích hợp. Trace Precedents and Dependents (Theo dõi các tiền lệ và các phụ thuộc): Đây là một tính năng trong nhóm Kiểm toán công thức của tab Công thức. Nếu bạn chọn một ô và nhấp vào nút Trace Precedents, các mũi tên sẽ xuất hiện dẫn từ tất cả các ô được tham chiếu trong công thức. Nếu bạn nhấp vào nút Trace Dependents, các mũi tên sẽ xuất hiện dẫn đến tất cả các ô tham chiếu đến ô đã chọn. Các mũi tên này sẽ ở trên màn hình của bạn cho đến khi bạn nhấp vào Remove Arrows (Xóa Mũi tên). Định dạng bảng Sau khi chèn bảng, có thể điều chỉnh định dạng để giúp dữ liệu nổi bật nhằm giúp người dùng dễ đọc hơn. Một số định dạng cơ bản: Thêm đường viền xung quanh các ô trong bảng Thêm tiêu đề bảng theo kiểu tiêu đề, hợp nhất và căn giữa Sử dụng định dạng văn bản khác nhau trên các tiêu đề hàng và cột Các tùy chọn này có thể tìm thấy trong tab Home. Biểu đồ Sau khi đã thiết lập bảng, có thêm biểu đồ để trực quan hóa dữ liệu. Các bước thêm biểu đồ: 1. Chọn bảng cần thêm biểu đồ. Lưu ý rằng nếu nhấp vào bất kỳ ô nào của bảng và sử dụng phím tắt ctrl+A (command+A), Excel sẽ tự động chọn toàn bộ bảng. 2. Điều hướng đến tab Insert (Chèn), sau đó chọn một tùy chọn từ nhóm Charts (Biểu đồ). Nếu không chắc nên sử dụng biểu đồ nào, hãy chọn Recommended Charts (Biểu đồ được đề xuất). 3. Chọn biểu đồ từ màn hình Biểu đồ được đề xuất để xem trước biểu đồ. 4. Chọn OK khi đã quyết định biểu đồ. Các loại biểu đồ Các loại biểu đồ thường được sử dụng trong Excel: Biểu đồ cột và biểu đồ thanh (Column & bar charts) được sử dụng để minh họa sự so sánh giữa các danh mục khác nhau. Trong biểu đồ thanh, các danh mục được hiển thị trên trục dọc và các giá trị xuất hiện dọc theo trục ngang. Trong biểu đồ cột, các danh mục nằm trên trục ngang và các giá trị nằm trên trục dọc. Biểu đồ đường thẳng (Line charts) được sử dụng để minh họa những thay đổi theo thời gian. Các giá trị theo trục dọc được hiển thị ở các khoảng cách đều nhau dọc theo trục ngang. Biểu đồ hình tròn (Pie charts) được sử dụng để so sánh các phần của một tổng thể, minh họa kích thước của các mục trong một chuỗi dữ liệu tỷ lệ với tổng của các mục. Biểu đồ phân tán (còn gọi là biểu đồ XY) hữu ích để kiểm tra mối quan hệ của hai biến. Mỗi biến tương ứng với một trục và các cặp giá trị được biểu diễn dưới dạng điểm. Các loại biểu đồ Định dạng biểu đồ Một số thông tin cần lưu ý khi thêm biểu đồ trong Excel: ▪ Tiêu đề biểu đồ: Khi bạn chèn biểu đồ, Excel thêm tiêu đề giữ chỗ "Chart Title" (Tiêu đề Biểu đồ). Hãy chắc chắn chỉnh sửa thành một tiêu đề ngắn gọn mô tả nội dung và mục đích của biểu đồ. ▪ Nhãn trục (Axis labels): Thêm nhãn mô tả cho các trục ngang và dọc trong biểu đồ, bao gồm cả đơn vị nếu thích hợp. ▪ Nhãn chuỗi (Series labels): Các chuỗi dữ liệu trong biểu đồ được mã hóa màu và được liệt kê trong một chú giải. Excel có thể đặt tên cho chuỗi dựa trên các tiêu đề trong bảng dữ liệu, nhưng nên kiểm tra lại xem tên chính xác có được hiển thị hay không. Định dạng biểu đồ Tùy thuộc vào dữ liệu được cung cấp và loại biểu đồ, người dung có thể thêm nhiều thông tin hơn vào biểu đồ của mình. Để thêm thông tin, nhấp vào biểu đồ, sau đó điều hướng đến tab "Chart Design" (Thiết kế Biểu đồ) xuất hiện ở đầu cửa sổ Excel. "Add Chart Element" (Thêm Yếu tố Biểu đồ) là tùy chọn đầu tiên trên tab này. Hoặc có thể nhấp vào biểu đồ và nhấp vào dấu cộng (+) xuất hiện bên cạnh để nhanh chóng thêm hoặc xóa các yếu tố biểu đồ. Định dạng biểu đồ Định dạng biểu đồ Để kiểm soát chuỗi dữ liệu nào được hiển thị trên biểu đồ, trước tiên hãy nhấp vào biểu đồ, sau đó sử dụng một trong các tùy chọn sau: Nhấp vào "Select Data" (Chọn Dữ liệu) từ nhóm Dữ liệu trên tab "Chart Format" (Định dạng Biểu đồ). Một cửa sổ bật lên sẽ xuất hiện nơi người dùng có thể chọn hoặc bỏ chọn chuỗi dữ liệu. Nhấp vào nút "Chart Filters" (Bộ lọc Biểu đồ) xuất hiện bên cạnh biểu đồ để xem danh sách kiểm tra các chuỗi dữ liệu để hiển thị hoặc ẩn. Ngoài ra, có thể nhanh chóng xóa một chuỗi dữ liệu khỏi biểu đồ bằng cách nhấp vào nó và nhấn phím Delete. Định dạng biểu đồ