Nội dung
Các quy trình trong lĩnh vực tài chính luôn được kết nối với nhau - yếu tố này phụ thuộc vào yếu tố khác và thay đổi theo nó. Theo dõi những thay đổi này và hiểu những gì sẽ xảy ra trong tương lai, có lẽ bằng cách sử dụng các hàm và phương pháp bảng tính Excel.
Nhận nhiều kết quả với một bảng dữ liệu
Khả năng của biểu dữ liệu là các yếu tố của phân tích điều gì xảy ra - thường được thực hiện thông qua Microsoft Excel. Đây là tên thứ hai để phân tích độ nhạy.
Giới thiệu chung
Bảng dữ liệu là một loại phạm vi ô có thể được sử dụng để giải quyết vấn đề bằng cách thay đổi giá trị trong một số ô. Nó được tạo ra khi cần theo dõi những thay đổi trong các thành phần của công thức và nhận cập nhật kết quả, theo những thay đổi này. Hãy cùng tìm hiểu cách sử dụng bảng dữ liệu trong nghiên cứu và chúng thuộc loại nào.
Kiến thức cơ bản về bảng dữ liệu
Có hai loại bảng dữ liệu, chúng khác nhau về số lượng thành phần. Bạn cần phải biên dịch một bảng với trọng tâm là số lượng giá trị uXNUMXbuXNUMX mà bạn cần kiểm tra với nó.
Các nhà thống kê sử dụng một bảng biến đơn khi chỉ có một biến trong một hoặc nhiều biểu thức có thể thay đổi kết quả của chúng. Ví dụ, nó thường được sử dụng cùng với hàm PMT. Công thức được thiết kế để tính toán số tiền thanh toán thông thường và có tính đến lãi suất quy định trong thỏa thuận. Trong các phép tính như vậy, các biến được viết trong một cột và kết quả của các phép tính trong một cột khác. Ví dụ về một tấm dữ liệu có 1 biến:
Tiếp theo, hãy xem xét các tấm có 2 biến. Chúng được sử dụng trong trường hợp có hai yếu tố ảnh hưởng đến sự thay đổi của bất kỳ chỉ tiêu nào. Hai biến có thể kết thúc trong một bảng khác liên quan đến khoản vay, bảng này có thể được sử dụng để xác định thời hạn trả nợ tối ưu và số tiền phải trả hàng tháng. Trong phép tính này, bạn cũng cần sử dụng hàm PMT. Ví dụ về bảng có 2 biến:
Tạo bảng dữ liệu với một biến
Hãy xem xét phương pháp phân tích bằng cách sử dụng ví dụ về một hiệu sách nhỏ chỉ có 100 cuốn sách trong kho. Một số trong số chúng có thể được bán đắt hơn ($ 50), phần còn lại sẽ khiến người mua phải trả ít hơn ($ 20). Tổng thu nhập từ việc bán tất cả hàng hóa được tính toán - chủ sở hữu quyết định rằng anh ta sẽ bán 60% số sách với giá cao. Bạn cần tìm hiểu xem doanh thu sẽ tăng như thế nào nếu bạn tăng giá một lượng hàng hóa lớn hơn - 70%, v.v.
Chú ý! Tổng doanh thu phải được tính bằng công thức, nếu không sẽ không thể tạo bảng dữ liệu.
- Chọn một ô trống cách xa mép của trang tính và viết công thức vào đó: = Ô của tổng doanh thu. Ví dụ: nếu thu nhập được viết trong ô C14 (chỉ định ngẫu nhiên được chỉ định), bạn cần viết như sau: = S14.
- Chúng tôi viết tỷ lệ phần trăm khối lượng hàng hóa trong cột bên trái của ô này - không phải bên dưới nó, điều này rất quan trọng.
- Chúng tôi chọn phạm vi ô có cột tỷ lệ phần trăm và liên kết đến tổng thu nhập.
- Chúng tôi tìm thấy trên tab “Dữ liệu”, mục “Nếu phân tích” và nhấp vào mục đó - trong menu mở ra, hãy chọn tùy chọn “Bảng dữ liệu”.
- Một cửa sổ nhỏ sẽ mở ra nơi bạn cần chỉ định ô có phần trăm sách được bán với giá cao ban đầu trong cột “Giá trị thay thế theo hàng trong…”. Bước này được thực hiện để tính toán lại tổng doanh thu có tính đến tỷ lệ phần trăm ngày càng tăng.
Sau khi nhấp vào nút “OK” trong cửa sổ nơi dữ liệu được nhập để biên dịch bảng, kết quả của các phép tính sẽ xuất hiện trên các dòng.
Thêm một công thức vào một bảng dữ liệu biến đơn
Từ một bảng đã giúp tính toán một hành động chỉ với một biến, bạn có thể tạo một công cụ phân tích phức tạp bằng cách thêm một công thức bổ sung. Nó phải được nhập bên cạnh một công thức đã có - ví dụ: nếu bảng được định hướng theo hàng, chúng tôi nhập biểu thức vào ô bên phải của biểu thức hiện có. Khi định hướng cột được thiết lập, chúng tôi viết công thức mới dưới công thức cũ. Tiếp theo, hãy làm theo thuật toán:
- Chọn lại phạm vi ô, nhưng bây giờ nó sẽ bao gồm công thức mới.
- Mở menu phân tích “điều gì xảy ra nếu” và chọn “Biểu dữ liệu”.
- Chúng tôi thêm một công thức mới vào trường tương ứng theo hàng hoặc cột, tùy thuộc vào hướng của tấm.
Tạo bảng dữ liệu có hai biến
Đầu bảng như vậy hơi khác một chút - bạn cần đặt liên kết đến tổng doanh thu cao hơn giá trị phần trăm. Tiếp theo, chúng tôi thực hiện các bước sau:
- Viết các tùy chọn giá trong một dòng có liên kết đến thu nhập - một ô cho mỗi giá.
- Chọn một loạt các ô.
- Mở cửa sổ bảng dữ liệu, như khi biên dịch bảng với một biến - thông qua tab "Dữ liệu" trên thanh công cụ.
- Thay thế trong cột “Thay thế các giá trị bằng các cột trong…” một ô có giá cao ban đầu.
- Thêm một ô có phần trăm doanh thu ban đầu của những cuốn sách đắt tiền vào cột “Thay thế giá trị theo hàng trong…” và nhấp vào “OK”.
Do đó, toàn bộ bảng được điền đầy đủ các khoản thu nhập có thể có với các điều kiện bán hàng hóa khác nhau.
Tăng tốc độ tính toán cho trang tính có chứa bảng dữ liệu
Nếu bạn cần tính toán nhanh trong bảng dữ liệu không kích hoạt tính toán lại của toàn bộ sổ làm việc, bạn có thể thực hiện một số điều để tăng tốc quá trình.
- Mở cửa sổ tùy chọn, chọn mục “Công thức” trong menu bên phải.
- Chọn mục “Tự động, ngoại trừ bảng dữ liệu” trong phần “Tính toán trong sổ làm việc”.
- Hãy tính toán lại kết quả trong bảng theo cách thủ công. Để thực hiện việc này, hãy chọn công thức và nhấn phím F.
Các công cụ khác để thực hiện phân tích độ nhạy
Có các công cụ khác trong chương trình để giúp bạn thực hiện phân tích độ nhạy. Chúng tự động hóa một số hành động mà nếu không sẽ phải thực hiện thủ công.
- Chức năng “Lựa chọn tham số” phù hợp nếu biết kết quả mong muốn và bạn cần biết giá trị đầu vào của biến để có được kết quả như vậy.
- “Tìm kiếm giải pháp” là một tiện ích bổ sung để giải quyết vấn đề. Cần thiết lập các giới hạn và chỉ ra chúng, sau đó hệ thống sẽ tìm ra câu trả lời. Giải pháp được xác định bằng cách thay đổi các giá trị.
- Phân tích độ nhạy có thể được thực hiện bằng Trình quản lý tình huống. Công cụ này được tìm thấy trong trình đơn phân tích điều gì xảy ra bên dưới tab Dữ liệu. Nó thay thế các giá trị trong một số ô - con số có thể lên tới 32. Người điều phối so sánh các giá trị này để người dùng không phải thay đổi chúng theo cách thủ công. Ví dụ về việc sử dụng trình quản lý tập lệnh:
Phân tích độ nhạy của một dự án đầu tư trong Excel
Phân tích điều gì sẽ xảy ra đặc biệt hữu ích trong các tình huống cần dự báo, chẳng hạn như đầu tư. Các nhà phân tích sử dụng phương pháp này để tìm hiểu giá trị cổ phiếu của một công ty sẽ thay đổi như thế nào do sự thay đổi của một số yếu tố.
Phương pháp phân tích độ nhạy đầu tư
Khi phân tích "điều gì xảy ra nếu" sử dụng phương pháp liệt kê - thủ công hoặc tự động. Phạm vi giá trị đã biết và chúng được thay thế lần lượt vào công thức. Kết quả là một tập hợp các giá trị. Chọn số thích hợp từ chúng. Hãy xem xét bốn chỉ số phân tích độ nhạy được thực hiện trong lĩnh vực tài chính:
- Giá trị hiện tại ròng - Được tính bằng cách lấy thu nhập trừ đi số tiền đầu tư.
- Tỷ suất hoàn vốn / lợi nhuận nội bộ - cho biết mức lợi nhuận cần thiết để nhận được từ một khoản đầu tư trong một năm.
- Tỷ lệ hoàn vốn là tỷ lệ của tất cả lợi nhuận so với khoản đầu tư ban đầu.
- Chỉ số lợi nhuận chiết khấu - cho biết hiệu quả của khoản đầu tư.
Công thức
Độ nhạy nhúng có thể được tính bằng công thức sau: Thay đổi tham số đầu ra tính bằng% / Thay đổi tham số đầu vào tính bằng%.
Các tham số đầu ra và đầu vào có thể là các giá trị được mô tả trước đó.
- Bạn cần biết kết quả trong điều kiện tiêu chuẩn.
- Chúng tôi thay thế một trong các biến và theo dõi những thay đổi trong kết quả.
- Chúng tôi tính toán phần trăm thay đổi của cả hai tham số liên quan đến các điều kiện đã thiết lập.
- Chúng tôi chèn phần trăm thu được vào công thức và xác định độ nhạy.
Một ví dụ về phân tích độ nhạy của một dự án đầu tư trong Excel
Để hiểu rõ hơn về phương pháp phân tích, cần có một ví dụ. Hãy phân tích dự án với các dữ liệu đã biết sau:
- Điền vào bảng để phân tích dự án trên đó.
- Chúng tôi tính toán dòng tiền bằng cách sử dụng hàm OFFSET. Ở giai đoạn đầu, dòng chảy tương đương với các khoản đầu tư. Tiếp theo, chúng tôi áp dụng công thức: = IF (OFFSET (Số, 1;) = 2; SUM (Dòng vào 1: Dòng ra 1); SUM (Dòng vào 1: Dòng ra 1) + $B$ 5)Các ký hiệu ô trong công thức có thể khác nhau, tùy thuộc vào bố cục của bảng. Cuối cùng, giá trị từ dữ liệu ban đầu được thêm vào - giá trị cứu hộ.
- Chúng tôi xác định khoảng thời gian mà dự án sẽ hoàn vốn. Đối với giai đoạn đầu, chúng tôi sử dụng công thức này: = SUMMESLY (G7:G17;»<0″). Phạm vi ô là cột dòng tiền. Đối với các giai đoạn tiếp theo, chúng tôi áp dụng công thức này: = Khoảng thời gian ban đầu + IF (Dòng đầu tiên> 0; Dòng đầu tiên; 0). Dự án đang ở mức hòa vốn sau 4 năm.
- Chúng tôi tạo một cột cho số của các giai đoạn đó khi dự án thành công.
- Chúng tôi tính toán lợi tức đầu tư. Cần phải lập biểu thức trong đó lợi nhuận trong một khoảng thời gian cụ thể được chia cho khoản đầu tư ban đầu.
- Chúng tôi xác định hệ số chiết khấu bằng công thức sau: = 1 / (1 + Đĩa.%) ^ Số.
- Chúng tôi tính toán giá trị hiện tại bằng cách sử dụng phép nhân - dòng tiền được nhân với hệ số chiết khấu.
- Hãy tính PI (chỉ số sinh lời). Giá trị hiện tại theo thời gian được chia cho khoản đầu tư khi bắt đầu dự án.
- Hãy xác định tỷ lệ hoàn vốn nội bộ bằng cách sử dụng hàm IRR: = IRR (Phạm vi của dòng tiền).
Phân tích độ nhạy đầu tư sử dụng biểu dữ liệu
Đối với việc phân tích các dự án trong lĩnh vực đầu tư, các phương pháp khác phù hợp hơn so với bảng số liệu. Nhiều người dùng gặp phải sự nhầm lẫn khi soạn công thức. Để tìm ra sự phụ thuộc của một yếu tố vào những thay đổi của những yếu tố khác, bạn cần chọn các ô chính xác để nhập các phép tính và để đọc dữ liệu.
Phân tích nhân tố và phân tán trong Excel với tính năng tự động hóa
Một kiểu phân tích độ nhạy khác là phân tích nhân tố và phân tích phương sai. Loại đầu tiên xác định mối quan hệ giữa các con số, loại thứ hai tiết lộ sự phụ thuộc của một biến vào các biến khác.
ANOVA trong Excel
Mục đích của phân tích như vậy là chia sự biến thiên của giá trị thành ba thành phần:
- Sự thay đổi do ảnh hưởng của các giá trị khác.
- Những thay đổi do mối quan hệ của các giá trị ảnh hưởng đến nó.
- Thay đổi ngẫu nhiên.
Hãy thực hiện phân tích phương sai thông qua bổ trợ Excel “Phân tích dữ liệu”. Nếu nó chưa được bật, nó có thể được bật trong phần cài đặt.
Bảng ban đầu phải tuân theo hai quy tắc: có một cột cho mỗi giá trị và dữ liệu trong đó được sắp xếp theo thứ tự tăng dần hoặc giảm dần. Cần phải kiểm tra ảnh hưởng của trình độ học vấn đến hành vi trong xung đột.
- Tìm công cụ Phân tích Dữ liệu trong tab Dữ liệu và mở cửa sổ của nó. Trong danh sách, bạn cần chọn phân tích phương sai một chiều.
- Điền vào các dòng của hộp thoại. Khoảng đầu vào là tất cả các ô, không bao gồm tiêu đề và số. Nhóm theo cột. Chúng tôi hiển thị kết quả trên một trang tính mới.
Vì giá trị trong ô màu vàng lớn hơn một, nên giả định có thể được coi là không chính xác - không có mối quan hệ nào giữa giáo dục và hành vi trong xung đột.
Phân tích nhân tố trong Excel: một ví dụ
Hãy phân tích mối quan hệ của dữ liệu trong lĩnh vực bán hàng - cần thiết để xác định sản phẩm phổ biến và không phổ biến. Thông tin ban đầu:
- Chúng ta cần tìm hiểu xem nhu cầu hàng hóa nào tăng nhiều nhất trong tháng thứ hai. Chúng tôi đang biên soạn một bảng mới để xác định sự tăng trưởng và suy giảm của nhu cầu. Tăng trưởng được tính bằng công thức sau: = IF ((Nhu cầu 2-Nhu cầu 1)> 0; Nhu cầu 2- Nhu cầu 1; 0). Giảm công thức: = IF (Tăng trưởng = 0; Nhu cầu 1- Nhu cầu 2; 0).
- Tính tốc độ tăng cầu hàng hóa theo phần trăm: = IF (Tăng trưởng / Kết quả 2 = 0; Giảm / Kết quả 2; Tăng trưởng / Kết quả 2).
- Hãy tạo biểu đồ cho rõ ràng - chọn một dải ô và tạo biểu đồ thông qua tab “Chèn”. Trong cài đặt, bạn cần loại bỏ phần điền, điều này có thể được thực hiện thông qua công cụ Định dạng chuỗi dữ liệu.
Phân tích phương sai hai chiều trong Excel
Phân tích phương sai được thực hiện với một số biến. Hãy xem xét điều này với một ví dụ: bạn cần tìm hiểu xem phản ứng với âm thanh có âm lượng khác nhau thể hiện ở nam và nữ nhanh như thế nào.
- Chúng tôi mở "Phân tích dữ liệu", trong danh sách, bạn cần tìm phân tích phương sai hai chiều mà không lặp lại.
- Khoảng thời gian đầu vào - các ô chứa dữ liệu (không có tiêu đề). Chúng tôi hiển thị kết quả trên một trang tính mới và nhấp vào “OK”.
Giá trị F lớn hơn F-tới hạn, có nghĩa là sàn ảnh hưởng đến tốc độ phản ứng với âm thanh.
Kết luận
Trong bài viết này, phân tích độ nhạy trong bảng tính Excel đã được thảo luận chi tiết để mỗi người dùng có thể hiểu các phương pháp ứng dụng của nó.