Sử dụng các công cụ tạo mô hình kinh doanh của Excel
Đôi khi, việc nhập vài dữ liệu cho một bảng tính, xây dựng một ít công thức, và định dạng để làm cho nó rõ ràng, thì chưa đủ. Trong kinh doanh, bạn thường được yêu cầu tiên đoán một giá trị nội tại nào đó từ một mớ hỗn độn các công thức và kết quả trong bảng tính. Nói cách khác, bạn cần phải phân tích dữ liệu để biết và nắm chắc được một chi tiết nào đó. Trong Excel, phân tích dữ liệu kinh doanh có nghĩa là sử dụng những công cụ tạo mô hình kinh doanh có sẵn. Chương này trình bày với bạn các công cụ đó và một số kỹ thuật phân tích với nhiều công dụng khác nhau của các công cụ này. Bạn sẽ học cách sử dụng một số phương pháp của Excel để phân tích What-if analysis (điều gì sẽ xảy ra), nắm vững công cụ Goal Seek, và cách tạo ra các Scenario.
1. Sử dụng What-If AnalysisWhat-If Analysis (phân tích điều gì sẽ xảy ra) có lẽ là phương pháp cơ bản nhất để truy vấn dữ liệu trong bảng tính. What-If Analysis đại khái có nghĩa là: Bạn tính một công thức D từ các biến A, B, C; sau đó bạn tự hỏi: Điều gì sẽ xảy ra nếu bạn thay đổi biến A, hoặc thay đổi B, hay là C? Điều gì sẽ xảy ra với kết quả?
Ví dụ, hình 1 minh họa một bảng tính tính giá trị tương lai của một khoản đầu tư (The Future Value of an Investment) dựa vào 5 biến: lãi suất hằng năm (interest rate), thời hạn (period), số tiền gửi hằng năm (annual deposit), số tiền gửi ban đầu (initital deposit), và cách tính lãi (deposit type). Đây chính là Hàm FV(), và ô C9 cho bạn thấy kết quả của nó.
Hình 1
Bây giờ, câu hỏi sẽ là: Điều gì xảy ra nếu như lãi suất là 7% một năm? Điều gì xảy ra nếu tôi gửi thêm vào ngân hàng $8,000 mỗi năm? Hoặc $12,000 mỗi năm? Điều gì xảy ra nếu tôi giảm số tiền gửi ban đầu?
Trả lời những câu hỏi này chỉ là đơn giản thay đổi các biến thích hợp và theo dõi hiệu ứng xảy ra trên kết quả.
1.1. Thiết lập bảng dữ liệu một đầu vào (One-Input Data Table)Trong ví dụ ở bài trên, việc chỉnh sửa các biến trong công thức chỉ cho bạn mỗi lần một kết quả. Nếu bạn thích nghiên cứu hiệu ứng xảy ra từ một dãy giá trị đối với kết quả của công thức, bạn cần thiết lập một bảng dữ liệu đầu vào (input data table) và sử dụng What-If Analyisis.
Ví dụ, trong bảng tính mà tôi đã trình bày ở hình 1, bạn muốn thấy giá trị tương lai của khoản đầu tư đó sẽ thay đổi thế nào khi số tiền gửi vào hằng năm chạy trong khoảng từ $7,000 đến $13,000. Bạn chỉ cần nhập các giá trị này trong một hàng hoặc một cột, và sau đó tạo những công thức thích hợp.
Việc thiết lập một bảng dữ liệu và sử dụng What-If Analyisis như thế nào rất là dễ dàng, bạn chỉ cần theo những bước sau đây:
- Thêm vào bảng tính các giá trị đầu vào mà bạn muốn thử trong công thức. Bạn có hai lựa chọn cho vị trí của những giá trị này:
- Nếu bạn muốn nhập các giá trị trong một hàng, bạn hãy bắt đầu tại ô cách ô chứa công thức một ô về phía bên phải và trong hàng phía trên. Ví dụ, ô chứa công thức là ô C9, bạn hãy bắt đầu nhập các giá trị tại ô D8.
- Nếu bạn muốn nhập các giá trị trong một cột, bạn hãy bắt đầu tại ô cách ô chứa công thức một ô về phía bên trái và trong hàng phía dưới. Ví dụ, ô chứa công thức là ô C9, bạn hãy bắt đầu nhập các giá trị tại ô B10, như minh họa ở hình 2.
Hình 2 - Chọn dãy ô bao gồm các ô chứa giá trị đầu vào và ô chứa công thức (trong hình 3, tôi chọn dãy B9:C16)
- Chọn Data, What-If Analysis, Data Table. Excel hiển thị hộp thoại Data Table.
- Bạn nhập các thông số trong hộp thoại này như thế nào tùy thuộc vào cách bạn đã thiết lập bảng dữ liệu đầu vào ở bước 1:
- Nếu bạn đã nhập các giá trị trong một hàng, bạn nhập địa chỉ của ô bạn sẽ thay đổi giá trị trong text box Row input cell.
- Nếu bạn đã nhập các giá trị trong một cột, bạn nhập địa chỉ của ô bạn sẽ thay đổi giá trị trong text box Column input cell. Trong minh họa ở hình 3, vì tôi sẽ thay đổi giá trị của Annual Deposit (số tiền gửi vào hằng năm), và đã thiết lập các giá trị đầu vào theo cột, nên tôi chọn ô C4 làm Column input cell.
Hình 3 - Nhấn OK. Excel sẽ đặt thay thế từng Input Value (giá trị đầu vào) vào trong Input Cell, và hiển thị kết quả ra bảng dữ liệu, như bạn thấy ở hình 4 sau đây:
Hình 4
Bạn thấy đó, ứng với mỗi số tiền gửi vào hằng năm, bạn sẽ biết trước được giá trị tương lai của khoản đầu tư này. Bạn chú ý ô B13, có giá trị là $10,000, kết quả tương ứng của nó ở C13 là $172,790, đó cũng chính là kết quả của công thức ban đầu (ở ô C9).
1.2. Bổ sung thêm công thức cho Input TableBạn không bị giới hạn với chỉ một công thức trong bảng dữ liệu. Nếu bạn muốn thấy của các giá trị đầu có hiệu ứng như thế nào với những công thức khác nhau, bạn chỉ việc thêm công thức vào trong dữ liệu.
Ví dụ, trong bảng tính Tính giá trị tương lai mà tôi đã dùng làm ví dụ trong những bài trước, tôi đưa thêm vấn đề lạm phát (inflation) vào các phép tính để biết con số thật sự của khoản đầu tư sẽ có được sau khi đã tính khoản lạm phát là bao nhiêu. Hình 5 minh họa bảng tính này đã được chỉnh sửa, với một biến mới ở C7 là Inflation (giả sử mức lạm phát hằng năm là 2%), và một công thức tính giá trị tương lai theo lạm phát ở ô Today's Dollars (D9).
Hình 5
Cú pháp cho công thức tính giá trị tương lai theo lạm phát là:
= Future Value / (1 + Inflation Rate) ^ Period
(= Giá trị tương lai / (1 + Mức lạm phát hằng năm) ^ Số năm đầu tư)
Cụ thể, công thức tại D9 là: = C9 / (1 + C7) ^ C3
Để tạo Data Table, bạn làm theo các bước đã trình bày ở bài trước. Bạn chỉ cần chắc chắn rằng, dãy mà bạn chọn ở bước 2 bao gồm các giá trị đầu vào (Input value) và cả hai công thức (tức là dãy B9:D16 trong hình 5).
Hình 6 minh họa kết quả bạn có được với công thức mới này:
Hình 6
1.3. Thiết lập bảng dữ liệu hai đầu vào (Two-Input Data Table)Bạn cũng có thể xác lập Data Table với hai biến đầu vào. Tiếp tục với bảng tính ví dụ từ những bài trước, tùy chọn này cho thấy hiệu ứng trên giá trị tương lai của một khoản đầu tư khi bạn thay đổi hai biến khác nhau, ví dụ, thay đổi số tiền gửi hằng năm và thay đổi lãi suất.
Các bước sau đây hướng dẫn bạn cách thiết lập một Data Table với hai đầu vào:
- Nhập một tập hợp các giá trị đầu vào trong cột chứa ô công thức, bắt đầu từ ô ngay dưới ô chứa công thức, và tập hợp các giá trị đầu vào thứ hai trên cùng một hàng với ô chứa công thức, bắt đầu từ ô kế tiếp ô chứa công thức. Hình 7 minh họa bảng tính Tính giá trị tương lai cho một khoản đầu tư, có một Data Table với hai đầu vào: Annual Deposit và Interest Rate:
Hình 7 - Chọn dãy ô bao gồm các ô chứa giá trị đầu vào và ô chứa công thức (trong hình 7, tôi chọn dãy B8:G15)
- Chọn Data, What-If Analysis, Data Table để hiển thị hộp thoại Data Table.
- Nhập địa chỉ của ô bạn sẽ thay đổi giá trị tương ứng với các giá trị đầu vào đã nhập trong môt hàng ở text box Row input cell (trong ví dụ, là biến Interest Rate, ô C2).
- Nhập địa chỉ của ô bạn sẽ thay đổi giá trị tương ứng với các giá trị đầu vào đã nhập trong một cột ở text box Column input cell (trong ví dụ, là biến Annual Deposit, ô C4).
- Nhấn OK. Excel sẽ đặt thay thế từng Input Value (giá trị đầu vào) vào trong Input Cell, và hiển thị kết quả ra bảng dữ liệu, như bạn thấy ở hình 8 sau đây:
Hình 8
1.4. Sửa một bảng dữ liệuKhi bạn muốn thực hiện những thay đổi trong bảng dữ liệu, bạn có thể sửa lại công thức (hay những công thức) và các dữ liệu đầu vào (Input value). Nhưng với các kết quả của bảng dữ liệu thì lại là chuyện khác. Khi bạn chạy lệnh Data Table (nghĩa là gọi hộp thoại Data Table ra và nhập các thông số), Excel sẽ nhập một công thức mảng vào bên trong của bảng dữ liệu. Công thức này là một hàm TABLE(), một hàm đặc biệt chỉ có khi dùng lệnh Data Table, hàm này có cú pháp như sau:
{=TABLE(row_input_ref, column_input_ref)}
Ở đây, row_input_ref và column_input_ref là các tham chiếu ô mà bạn đã nhập vào trong hộp thoại Data Table. Cặp dấu {} biểu thị rằng đây là một mảng, nghĩa là bạn không thể thay đổi hoặc xóa những phần tử riêng lẻ trong đó. Nếu bạn muốn thay đổi các kết quả, bạn cần chọn toàn bộ bảng dữ liệu và chạy lại lệnh Data Table. Nếu bạn muốn xóa các kết quả, bạn phải chọn toàn bộ toàn bộ mảng và sau đó xóa nó.
Nguồn: giaiphapexcel
Blog tổng hợp những kinh nghiệm được học, áp dụng và chia sẻ
