Phân tích độ nhạy trong Excel

Phân tích độ nhạy trong excel giúp chúng ta nghiên cứu sự không chắc chắn trong đầu ra của mô hình với sự thay đổi của các biến đầu vào. Nó chủ yếu thực hiện kiểm tra căng thẳng về các giả định được mô hình hóa của chúng tôi và dẫn đến những hiểu biết sâu sắc về giá trị gia tăng.

Trong bối cảnh định giá DCF, Phân tích độ nhạy trong excel đặc biệt hữu ích trong tài chính để lập mô hình giá cổ phiếu hoặc độ nhạy định giá đối với các giả định như tốc độ tăng trưởng hoặc chi phí vốn.

Trong bài viết này, chúng tôi xem xét Phân tích độ nhạy sau đây trong Excel để lập mô hình DCF một cách chuyên nghiệp.

Quan trọng nhất - Tải xuống Phân tích độ nhạy trong Mẫu Excel

Tìm hiểu các kiểu BẢNG DỮ LIỆU một biến và hai biến trong excel

# 1 - Phân tích độ nhạy của bảng dữ liệu một biến trong Excel

Chúng ta hãy lấy ví dụ về Tài chính (Mô hình chiết khấu cổ tức) dưới đây để hiểu chi tiết vấn đề này.

DDM tăng trưởng liên tục cho chúng ta Giá trị hợp lý của cổ phiếu là giá trị hiện tại của một dòng cổ tức vô hạn đang tăng với tốc độ không đổi.

Công thức Gordon Growth như sau:

Ở đâu:

D1 = Giá trị cổ tức nhận được trong năm tới
D0 = Giá trị cổ tức nhận được trong năm nay
g = Tỷ lệ tăng trưởng của cổ tức
Ke = Tỷ lệ chiết khấu

Bây giờ, giả sử rằng chúng ta muốn hiểu mức độ nhạy cảm của giá cổ phiếu đối với Tỷ suất sinh lợi kỳ vọng (ke). Có hai cách để làm điều này -

Donkey way :-)
Những gì nếu phân tích

# 1 - Donkey Way

Phân tích độ nhạy trong Excel bằng cách sử dụng Donkey rất đơn giản, nhưng khó thực hiện khi có nhiều biến liên quan.

Bạn có muốn tiếp tục làm điều này với 1000 giả định không? Rõ ràng là không!

Tìm hiểu phân tích độ nhạy sau đây trong kỹ thuật excel để cứu chính mình khỏi rắc rối.

# 2 - Sử dụng một bảng dữ liệu biến

Cách tốt nhất để thực hiện phân tích độ nhạy trong excel là sử dụng Bảng dữ liệu. Bảng dữ liệu cung cấp một lối tắt để tính toán nhiều phiên bản trong một thao tác và một cách để xem và so sánh kết quả của tất cả các biến thể khác nhau với nhau trên trang tính của bạn. Dưới đây là các bước mà bạn có thể làm theo để triển khai phân tích độ nhạy một chiều trong excel.

Bước 1 - Tạo bảng ở định dạng chuẩn

Trong cột đầu tiên, bạn có các giả định đầu vào. Trong ví dụ của chúng tôi, đầu vào là tỷ suất sinh lợi kỳ vọng (ke). Ngoài ra, xin lưu ý rằng có một hàng trống (được tô màu xanh lam trong bài tập này) bên dưới tiêu đề bảng. Hàng trống này phục vụ một mục đích quan trọng cho bảng dữ liệu một chiều này mà bạn sẽ thấy trong Bước 2.

Bước 2 - Liên kết Đầu vào và Đầu ra tham chiếu như được cung cấp trong ảnh chụp nhanh bên dưới.

Khoảng trống được cung cấp bởi hàng trống hiện được sử dụng để cung cấp đầu vào (Ke trả về dự kiến) và công thức đầu ra. Tại sao nó được thực hiện như thế này?

Chúng ta sẽ sử dụng “What if Analysis”, đây là một cách để hướng dẫn excel rằng đối với Đầu vào (ke), công thức tương ứng được cung cấp ở phía bên phải sẽ được sử dụng để tính toán lại tất cả các đầu vào khác.

Bước 3 - Chọn công cụ Phân tích điều gì sẽ xảy ra để thực hiện Phân tích độ nhạy trong Excel

Điều quan trọng cần lưu ý là điều này được chia thành hai bước

Chọn phạm vi bảng bắt đầu từ phía bên trái bắt đầu từ 10% cho đến góc dưới bên phải của bảng.
Nhấp vào Dữ liệu -> Điều gì sẽ xảy ra nếu Phân tích -> Bảng Dữ liệu

Bước 4 - Hộp thoại Bảng Dữ liệu Mở ra.

Hộp thoại tìm kiếm hai đầu vào - Đầu vào Hàng và Đầu vào Cột. Vì chỉ có một đầu vào Ke đang được xem xét, chúng tôi sẽ cung cấp một đầu vào cột duy nhất.

Bước 5 - Liên kết đầu vào cột

Trong trường hợp của chúng tôi, tất cả đầu vào được cung cấp trong một cột và do đó, chúng tôi sẽ liên kết với đầu vào cột. Đầu vào cột được liên kết với lợi nhuận Dự kiến (Ke). Xin lưu ý rằng đầu vào phải được liên kết từ nguồn gốc và Không phải từ nguồn bên trong bảng

Bước 6 - Tận hưởng đầu ra

# 2 - Phân tích độ nhạy của bảng dữ liệu hai biến trong Excel

Bảng dữ liệu rất hữu ích cho việc phân tích Độ nhạy trong excel, đặc biệt là trong trường hợp DCF. Khi một trường hợp cơ sở được thiết lập, phân tích DCF phải luôn được kiểm tra trong các tình huống nhạy cảm khác nhau. Thử nghiệm bao gồm việc kiểm tra ảnh hưởng gia tăng của các thay đổi khác nhau trong các giả định (chi phí vốn, tốc độ tăng trưởng cuối kỳ, tăng trưởng doanh thu thấp hơn, yêu cầu vốn cao hơn, v.v.) đến giá trị hợp lý của cổ phiếu.

Hãy để chúng tôi phân tích độ nhạy trong excel với một ví dụ tài chính về Phân tích dòng tiền chiết khấu của Alibaba.

Với các giả định cơ bản về Chi phí vốn là 9% và tốc độ tăng trưởng không đổi ở mức 3% , chúng tôi đã đạt được mức định giá hợp lý là 191,45 tỷ đô la.

Bây giờ chúng tôi giả định rằng bạn không hoàn toàn đồng ý với các giả định về Chi phí vốn hoặc các giả định về tốc độ tăng trưởng mà tôi đã đưa ra trong Định giá IPO của Alibaba. Bạn có thể muốn thay đổi các giả định và tiếp cận tác động đến việc định giá.

Một cách là thay đổi các giả định theo cách thủ công và kiểm tra kết quả của mỗi thay đổi. (codeword - Donkey method!)

Tuy nhiên, chúng tôi ở đây để thảo luận về một cách tốt hơn và hiệu quả hơn để tính toán định giá bằng cách sử dụng phân tích độ nhạy trong excel, không chỉ tiết kiệm thời gian mà còn cung cấp cho chúng tôi một cách để hình dung tất cả các chi tiết đầu ra ở một định dạng hiệu quả.

Nếu chúng ta thực hiện phân tích What-if trong excel một cách chuyên nghiệp trên dữ liệu trên, thì chúng ta sẽ nhận được kết quả sau.

Ở đây, đầu vào hàng bao gồm những thay đổi về Chi phí vốn hoặc WACC (7% đến 11%)
Đầu vào cột bao gồm những thay đổi về tỷ lệ tăng trưởng (1% đến 6%)
Giao điểm là Định giá Alibaba. Ví dụ. sử dụng trường hợp cơ sở của chúng tôi là 9% WACC và tốc độ tăng trưởng 3%, chúng tôi nhận được mức định giá là 191,45 tỷ đô la.

Với nền tảng này, bây giờ chúng ta hãy xem cách chúng ta có thể chuẩn bị một phân tích độ nhạy như vậy trong excel bằng cách sử dụng bảng dữ liệu hai chiều.

Bước 1 - Tạo cấu trúc bảng như dưới đây

Vì chúng tôi có hai bộ giả định - Chi phí vốn (WACC) và Tỷ lệ tăng trưởng (g), bạn cần chuẩn bị một bảng dưới đây.
Bạn có thể tự do chuyển đổi đầu vào hàng và cột. Thay vì WACC, bạn có thể có tỷ lệ tăng trưởng và ngược lại.

Bước 2 - Liên kết điểm giao với ô đầu ra.

Điểm giao nhau của hai đầu vào nên được sử dụng để liên kết đầu ra mong muốn. Trong trường hợp này, chúng ta muốn xem ảnh hưởng của hai biến này (WACC và tốc độ tăng trưởng) đến giá trị Vốn chủ sở hữu. Do đó, chúng tôi đã liên kết ô giao nhau với đầu ra.

Bước 3 - Mở bảng dữ liệu hai chiều

Chọn bảng mà bạn đã tạo
Sau đó nhấp vào Dữ liệu -> Điều gì sẽ xảy ra nếu Phân tích -> Bảng Dữ liệu

Bước 4 - Cung cấp đầu vào hàng và đầu vào cột.

Đầu vào hàng là Giá vốn hoặc Kế.
Đầu vào cột là tốc độ tăng trưởng.
Hãy nhớ liên kết các đầu vào này từ nguồn giả định ban đầu chứ không phải từ bất kỳ đâu bên trong bảng

Bước 5 - Tận hưởng kết quả đầu ra.

Hầu hết các giá trị sản lượng bi quan nằm ở góc trên cùng bên phải, nơi Chi phí sử dụng vốn là 11% và tốc độ tăng trưởng chỉ là 1%
Giá trị IPO của Alibaba lạc quan nhất là khi Ke là 7% và g là 6%
Trường hợp cơ sở mà chúng tôi tính toán cho ke 9% và tỷ lệ tăng trưởng 3% nằm ở giữa.
Phân tích độ nhạy hai chiều trong bảng excel này cung cấp cho khách hàng khả năng phân tích kịch bản dễ dàng, tiết kiệm rất nhiều thời gian.

# 3 - Tìm kiếm Mục tiêu cho Phân tích Độ nhạy trong Excel

Lệnh Tìm Mục tiêu được sử dụng để đưa một công thức đến một giá trị cụ thể
Nó thực hiện điều này bằng cách thay đổi một trong các ô được tham chiếu bởi công thức
Goal Seek yêu cầu tham chiếu ô có chứa công thức (ô Đặt). Nó cũng yêu cầu một giá trị, là con số mà bạn muốn ô bằng
Cuối cùng, Goal Seek yêu cầu một ô thay đổi để đưa ô Đặt thành giá trị cần thiết

Hãy để chúng tôi xem xét DCF của Định giá IPO Alibaba.

Như chúng tôi biết từ DCF rằng tốc độ tăng trưởng và định giá có liên quan trực tiếp với nhau. Tốc độ tăng trưởng ngày càng tăng làm tăng giá cổ phiếu của cổ phiếu.

Giả sử rằng chúng ta muốn kiểm tra xem giá cổ phiếu sẽ chạm mức 80 đô la ở tốc độ tăng nào?

Như mọi khi, chúng ta có thể thực hiện việc này theo cách thủ công bằng cách thay đổi tốc độ tăng trưởng để tiếp tục xem tác động đến giá cổ phiếu. Đây sẽ lại là một quá trình tẻ nhạt, chúng tôi có thể phải nhập tỷ lệ tăng trưởng nhiều lần để đảm bảo rằng giá cổ phiếu khớp với mức 80 đô la trong trường hợp của chúng tôi.

Tuy nhiên, chúng ta có thể sử dụng một hàm như Goal Seek trong excel để giải quyết vấn đề này theo các bước đơn giản.

Bước 1 - Nhấp vào ô có giá trị bạn muốn đặt. (Ô Đặt phải chứa công thức)

Bước 2 - Chọn Công cụ, Tìm kiếm Mục tiêu từ menu và hộp thoại sau sẽ xuất hiện:

Lệnh Tìm Mục tiêu tự động đề xuất ô hiện hoạt làm ô Đặt.
Điều này có thể được nhập quá nhiều với tham chiếu ô mới hoặc bạn có thể nhấp vào ô thích hợp trên bảng tính.
Bây giờ hãy nhập giá trị mong muốn mà công thức này sẽ đạt được.
Nhấp vào bên trong hộp "Đến giá trị" và nhập giá trị bạn muốn công thức đã chọn của mình bằng
Cuối cùng, nhấp vào bên trong hộp “Bằng cách thay đổi ô” và nhập hoặc nhấp vào ô có giá trị có thể thay đổi để đạt được kết quả mong muốn
Nhấp vào nút OK và bảng tính sẽ thay đổi ô thành một giá trị đủ để công thức đạt được mục tiêu của bạn.

Bước 3 - Tận hưởng kết quả đầu ra.

Goal Seek cũng thông báo cho bạn rằng mục tiêu đã đạt được

Phần kết luận

Phân tích độ nhạy trong excel làm tăng hiểu biết của bạn về tình hình tài chính và hoạt động của doanh nghiệp. Như chúng ta đã học được từ ba phương pháp tiếp cận - Một Bảng Dữ liệu Thứ nguyên, Hai Bảng Dữ liệu Thứ nguyên và Tìm kiếm Mục tiêu rằng phân tích độ nhạy cực kỳ hữu ích trong lĩnh vực tài chính, đặc biệt là trong bối cảnh định giá - DCF hoặc DDM.

Tuy nhiên, bạn cũng có thể hiểu được tầm vĩ mô về công ty và ngành nói chung. Bạn có thể phát triển các trường hợp để phản ánh độ nhạy của định giá đối với những thay đổi về lãi suất, suy thoái, lạm phát, GDP, v.v. trên định giá. Suy nghĩ và nhận thức thông thường nên được sử dụng để phát triển các trường hợp nhạy cảm hợp lý và hữu ích.

Tiếp theo là gì?

Nếu bạn đã học được điều gì đó về Phân tích độ nhạy trong Excel, vui lòng để lại bình luận bên dưới. Cho tôi biết bạn nghĩ gì. Cảm ơn nhiều và chăm sóc. Chúc bạn học vui vẻ!

Bạn cũng có thể xem các bài viết này bên dưới để tìm hiểu thêm về Định giá và Tài chính Doanh nghiệp -

Công thức tính nhạy cảm về giá
Phân tích rủi ro - Phương pháp
Phân tích hòa vốn trong Excel
Phân tích Pareto trong Excel

seychellesartprojects.org