Chương 15 - Sử dụng các công cụ tạo mô hình kinh doanh của Excel

[BNTT]

Phỏng dịch từ cuốn Formulas and Functions with Microsoft Office Excel 2007 của Paul McFedries

Phần III: Xây dựng các mô hình kinh doanh

• Chương 13: Phân tích dữ liệu với các Table
  
  
• Chương 14: Phân tích dữ liệu với các PivotTable
  
  
• Chương 15: Sử dụng các công cụ tạo mô hình kinh doanh của Excel

---------------------------------------------------------------------------------------


PHẦN III - Xây dựng các mô hình kinh doanh

Chương 15 - 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.​

Bạn có thể tải về bảng tính với những ví dụ trong chương này tại đây: Example Files

 

[BNTT]

15.1. Sử dụng What-If Analysis

What-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 15.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 15.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ả.​

 

[BNTT]

15.1. Sử dụng What-If Analysis


15.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 15.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:

1. 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 15.2.
   
   
   

   
   Hình 15.2​
   
   
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 15.3, tôi chọn dãy B9:C16)
   
   
3. Chọn Data, What-If Analysis, Data Table. Excel hiển thị hộp thoại Data Table.
   
   
4. 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 15.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 15.3​
   
   
5. 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 15.4 sau đây:
   
   
   

   
   Hình 15.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).
​

 

[BNTT]

15.1. Sử dụng What-If Analysis


15.1.2. Bổ sung thêm công thức cho Input Table

Bạ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 15.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 15.5​

Ghi chú:

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 B916 trong hình 15.5).

Hình 15.6 minh họa kết quả bạn có được với công thức mới này:

Hình 15.6​

​

 

[BNTT]

15.1. Sử dụng What-If Analysis


15.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:

1. 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 15.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 15.7​
   
   
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 15.7, tôi chọn dãy B8:G15)
   
   
3. Chọn Data, What-If Analysis, Data Table để hiển thị hộp thoại Data Table.
   
   
4. 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).
   
   
5. 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).
   
   
6. 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 15.8 sau đây:
   
   
   

   
   Hình 15.8​

​

 

[BNTT]

15.1. Sử dụng What-If Analysis


15.1.4. Sửa một bảng dữ liệu

Khi 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ó.​

 

[BNTT]

15.2. Làm việc với Goal Seek

Thêm một câu hỏi What-If cho bạn:"Điều gì sẽ xảy ra" nếu bạn đã biết trước kết quả? Ví dụ, có thể bạn đã biết rằng bạn phải tiết kiệm được $50,000 thì mới đủ tiền để sắm thêm thiết bị trong vòng 5 năm sắp tới, vậy ngay từ bây giờ bạn phải tiết kiệm bao nhiêu mỗi tháng, mỗi năm? Hoặc là trong năm tài chính sắp tới, bạn phải đạt được 30% tổng lợi nhuận, vậy ngay từ bây giờ bạn phải làm gì? Hoặc bạn có phương trình x + 3 = 15, và bạn phải tính x bằng bao nhiêu...

Nếu bạn dùng Excel, và phải xử lý một biến nào đó để đạt được một kết quả đã cho trước, bạn có thể sử dụng tính năng Goal Seek. Bạn cho Goal Seek biết kết quả sau cùng, và biến nào phải thay đổi, nó sẽ tìm ra kết quả của việc thay đổi ở biến đó dùm cho bạn, nếu có thể.​

 

[BNTT]

15.2. Làm việc với Goal Seek


15.2.1. Goal Seek làm việc như thế nào?

Khi bạn thiết lập một bảng tính để sử dụng Goal Seek, bạn thường có một công thức trong một ô, và các biến cho công thức này (với một giá trị ban đầu) trong những ô khác. Công thức có thể có nhiều biến, nhưng Goal Seek chỉ cho phép bạn xử lý mỗi lần một biến mà thôi.

Goal Seek hoạt động bằng cách sử dụng phương pháp lặp đi lặp lại (iterative method) để tìm ra lời giải. Nghĩa là, Goal Seek sẽ thử giá trị ban đầu của biến để xem nó có tạo ra kết quả mà bạn muốn hay không. Nếu không, Goal Seek sẽ thử tiếp với những giá trị khác nhau, cho đến khi nó tìm ra lời giải, hay nói cách khác, cho đến khi nào kết quả mà nó tìm được gần giống với kết quả của bạn muốn nhất.​

Nếu muốn tìm hiểu về phương pháp lặp đi lặp lại, mời bạn xem lại bài này: Sử dụng sự lặp lại và các tham chiếu tuần hoàn​

 

[BNTT]

15.2. Làm việc với Goal Seek


15.2.2. Chạy Goal Seek

Trước khi chạy Goal Seek, bạn cần thiết lập bảng tính theo một mẫu nào đó, mà cụ thể là nên thực hiện 3 điều sau đây:

• Thiết lập một ô làm ô thay đổi (changing cell). Đây là giá trị mà Goal Seek sẽ xử lý lặp đi lặp lại để cố gắng đạt được kết quả gần đúng nhất. Rồi nhập một giá trị ban đầu trong ô này, cho nó bằng 0 chẳng hạn.
  
  
• Thiết lập các giá trị nhập liệu khác cho công thức và đặt cho chúng những giá trị thích hợp.
  
  
• Tạo một công thức để Gioal Seek sử dụng trong quá trình cố gắng đạt được mục tiêu.

Ví dụ, bạn đang ước mơ sắm được một thiết bị có trị giá $50,000, và muốn là trong vòng 5 năm tính từ hôm nay, ước mơ sẽ thành hiện thực. Giả sử rằng, tiền gửi ngân hàng có lãi suất là 5% một năm, vậy bạn cần phải gửi vào ngân hàng mỗi năm tối thiểu là bao nhiêu để đạt được ước mơ của mình? Hình 15.9 minh họa một bảng tính đã được thiết lập để sử dụng Goal Seek cho ước mơ này:

Hình 15.9​

Trong hình trên:

• Ô C6 là ô thay đổi (changing cell): số tiền tối thiểu phải gửi vào ngân hàng mỗi năm (với giá trị ban đầu là không có đồng nào cả).
  
  
• Các ô C4 và C5 được sử dụng làm các hằng cho hàm FV() ở ô C8.
  
  
• Ô C8 chứa hàm FV(), là một hàm chuyên dùng để tính một giá trị tương lai cho một khoản đầu tư. Và bạn muốn rằng kết quả ở đây sẽ là $50,000.

Bạn theo các bước sau đây để chạy Goal Seek:

1. Chọn Data, What-If Analysis, Goal Seek. Excel hiển thị hộp thoại Goal Seek.
   
   
2. Nhập tham chiếu đến ô chứa công thức trong hộp Set Cell. Trong trường hợp này, là $C$8.
   
   
3. Nhập giá trị mà bạn muốn sẽ là kết quả của công thức trong hộp To Value. Trong trường hợp này, bạn nhập vào 50000.
   
   
4. Nhập theo chiếu đến ô thay đổi, hay nói cách khác, để đạt được giá trị ở To Value cho công thức ở Set Cell, thì thay đổi giá trị ở đây: By changing cell (là ô C6 trong hình 15.9).
   
   Hình 15.10 minh họa bảng tính cùng với hộp thoại Goal Seek đã điền đầy đủ:
   
   
   

   
   Hình 15.10​
   
   
5. Sau khi nhấn OK của hộp thoại, Goal Seek sẽ "rùng mình" một tí, và hiển thị kết quả mà nó tìm được vào ô thay đổi (là giá trị $-9,059 ở ô C6) và hộp thoại Goal Seek Status cho bạn biết nó có tìm được lời giải hay không, cũng cho bạn so sánh kết quả áp dụng lời giải này (Current value) với kết quả mà bạn muốn có (Target value):
   
   
   

   
   Hình 15.11​
   
   Ở đây, Goal Seek tính ra rằng, nếu muốn có được $50,000 sau 5 năm, thì ngay từ bây giờ, mỗi năm bạn phải gửi vào ngân hàng ít nhất $9,059, với giả thiết lãi suất tiền gửi trong suốt 5 năm là 5% một năm.
   
   
6. Nếu bạn ưng ý với kết quả của Goal Seek tìm được, thì nhấn OK để chấp nhận kết quả ở ô thay đổi (là giá trị $-9,059 ở ô C6), còn nếu muốn bỏ qua kết quả này, bạn nhấn Cancel.

​

 

[BNTT]

15.2.3. Bài toán tối ưu hóa lợi nhuận sản phẩm

Có nhiều doanh nghiệp sử dụng lợi nhuận từ sản phẩm như là thước đo cho tình hình tài chính của mình. Một mức lợi nhuận mạnh, có nghĩa là các chi phí đang được kiểm soát tốt, và cho thấy thị trường hài lòng với giá cả của sản phẩm. Dĩ nhiên, lợi nhuận còn phụ thuộc vào nhiều yếu tố khác nữa, và bạn có thể sử dụng Goal Seek để tìm ra mức lợi nhuận tối ưu dựa vào một trong những yếu tố này.

Ví dụ, bạn muốn đưa ra một dòng sản phẩm mới, và bạn muốn thu được 30% lợi nhuận từ nó trong năm đầu tiên. Và giả sử rằng bạn có những giả định sau đây:

• Trong năm đầu tiên này sẽ bán được 100,000 sản phẩm (Unit Sold).
  
  
• Mức chiết khấu trung bình (Average Discount) cho các đại lý là 40%
  
  
• Tổng chi phí cố định (Fixed Costs) là $750,000, và chi phí riêng cho mỗi sản phẩm (Costs per Unit) là $12.63

Từ những thông tin trên, chúng ta sẽ tìm ra mức giá bán hợp lý nhất (Price per Unit) cho sản phẩm để kiếm được 30% lợi nhuận (Margin).

Hình 15.12 minh họa một bảng tính được thiết lập để giải bài toán này. Một giá trị ban đầu là $1.00 được nhập ở ô Price Per Unit (giá bán cho mỗi sản phẩm, ô C4). Với mức giá này, nếu bán hết 100,000 sản phẩm, và sau khi chiết khấu 40% cho đại lý, chúng ta sẽ có doanh thu là $60,000. Chi phí cho 100,000 sản phẩm này cộng với chi phí cố định sẽ là $2,013,000. Như vậy, nếu như bán với giá $1.00 một sản phẩm, chúng ta sẽ lỗ $1,953,000, hay tương đương với -3255% (!)

Để tìm ra giá bán cho sản phẩm (giá trị ở C4) mà kiếm được 30% lợi nhuận (ở C14), tôi thiết lập các tham số trong hộp thoại Goal Seek như sau:

1. Tham chiếu cho Set Cell là C14
   
   
2. Giá trị cho To Value là 0.3 (tức 30%)
   
   
3. Tham chiếu cho By Changing Cell là C4

Hình 15.12​

Sau khi chạy Goal Seek, nó đưa ra một giá bán đề nghị là $47.87 để đạt được mức lợi nhuận gần bằng mức lợi nhuận chúng ta muốn (29,92%), như minh họa ở hình 15.13:

Hình 15.13​

​

 

[BNTT]

15.2. Làm việc với Goal Seek


15.2.4. Ghi chú về những giá trị xấp xỉ của Goal Seek

Ở hình 15.13 của bài trên, bạn thấy rằng Goal Seek đưa ra một con số xấp xỉ: 29.92%, chứ không phải là 30% như chúng ta mong muốn. Giá trị đó khá gần (lệch chỉ 0.008) nhưng không chính xác. Tại sao Goal Seek không tìm ra lời giải chính xác?

Câu trả lời nằm ở một trong những tùy chọn mà Excel sử dụng để điều khiển các phép tính lặp đi lặp lại (iterative calculations). Đôi khi, việc lặp đi lặp lại có thể phải mất một thời gian cực kỳ dài để tìm ra được lời giải chính xác, do đó, Excel đã hòa hợp bằng việc xác lập những giới hạn nhất định trong quá trình lặp lại.

Để thấy những giới hạn này, bạn mở Excel Options ra rồi nhấn vào ngăn Formulas (xem hình 15.14). Trong đó có hai tùy chọn xác lập cho việc lặp lại:

• Maximum Iterations — Giá trị trong text box này quy định số lần lặp lại tối đa. Trong Goal Seek, chính là số giá trị tối đa mà Excel đưa vào ô thay đổi (changing cell) để thử.
  
  
• Maximum Change — Giá trị trong text box này là giới hạn mà Excel sử dụng để quyết định xem nó có hội tụ đến một lời giải hay không. Nếu hiệu số giữa lời giải hiện hành và mục tiêu muốn đạt được nhỏ hơn hoặc bằng giá trị này, Excel sẽ ngừng lại.

Hình 15.12​

Chính giá trị Maximum Change = 0.001 (mặc định) đã ngăn Excel đưa ra lời giải chính xác cho phép tính lợi nhuận ở bài trước. Trong một lần lặp lại nào đó, Goal Seek đã tìm ra đáp số là 0.2992 (tương đương 29.92%), nhưng bởi vì 0.008 (chênh lệch giữa 0.3 và 0.2992) thì nhỏ hơn giá trị mặc định 0.001 trong text box Maximum Change, nên Excel đã ngưng quá trịnh lặp lại và đưa ra lời giải.

Để đạt được lời giải chính xác = 30%, hay 0.3, bạn chỉ cần sửa lại con số trong Maximum Change là 0.0001.​

 

[BNTT]

15.2. Làm việc với Goal Seek


15.2.5. Bài toán phân tích điểm hòa vốn

Bạn được yêu cầu tìm số sản phẩm tối thiểu phải bán được để làm cho lợi nhuận bằng 0 (nghĩa là tổng chi phí cho sản phẩm bằng doanh thu từ sản phẩm), hay nói cách khác, phải bán được ít nhất là bao nhiêu sản phẩm thì mới hòa vốn? Thiết lập một phương trình lợi nhuận có kết quả là 0 bằng cách thay đổi số sản phẩm sẽ bán ra, là một trong những thế mạnh của Goal Seek.

Để thử làm điều đó, chúng ta quay lại với bài toán "Tối ưu lợi nhuận sản phẩm". Nhưng trong trường hợp này, tôi sẽ giả sử rằng giá bán của sản phẩm đã được xác định là $47.95 (lời giải của bài toán trên). Để tìm ra số sản phẩm tối thiểu phải bán được để có lợi nhuận bằng 0, tôi thiết lập các tham số cho Goal Seek như sau (xem hình 15.15):

1. Tham chiếu cho Set Cell là C13, là ô tính lợi nhuận theo số tiền.
   
   
2. Giá trị cho To Value là 0 (tức là mục tiêu của lợi nhuận, trong trường hợp này)
   
   
3. Tham chiếu cho By Changing Cell là C5 (số sản phẩm được bán)

Hình 15.15​

Và lời giải cho chúng ta được Goal Seek tìm ra như minh họa ở hình 15.16: Phải bán được ít nhất là 46,468 sản phẩm, thì chúng ta mới hòa vốn.

Hình 15.16​

​

 

[BNTT]

15.2. Làm việc với Goal Seek


15.2.6. Giải phương trình đại số

Các phương trình đại số thì thường không xuất hiện trong một mô hình kinh doanh, tuy nhiên, vì đây là một trong những khả năng của Goal Seek, nhất là đối với các phương trình có một biến, nên tôi đề cập đến vấn đề này tại đây.

Ví dụ, bạn cần phải giải một phương trình hơi khó chịu như sau:

Trước hết, bạn cần phải viết lại vế bên trái của phương trình theo kiểu mà Excel có thể hiểu được (với A2 là ô tượng trưng cho biến x):

=(((3 * A2 - 8) ^ 2) * (A2 - 1)) / (4 * A2 ^ 2 - 5)​

Tiếp theo, bạn dùng Goal Seek để xác lập mục tiêu cho công thức trên bằng 1 (vế phải của phương trình), bằng cách thay đổi giá trị của A2, như minh họa ở hình 15.17 sau đây:

Hình 15.17​

Hình 15.18 minh họa kết quả, với giá trị tại ô A2 là lời giải cho nghiệm x của phương trình. Bạn chú ý, kết quả của phương trình (ô B2) không chính xác bằng 1. Như đã nói ở bài 15.2.4, nếu bạn cần kết quả chính xác hơn, bạn phải thay đổi giới hạn hội tụ (Maximum Change) của Excel. Ví dụ, bạn có thể thiết lập cho Maximum Change là 0.000001.

Hình 15.18​

​

 

[BNTT]

15.3. Làm việc với các Scenario

Theo định nghĩa, phân tích What-If không phải là một môn khoa học chính xác. Tất cả những mô hình What-If chỉ đưa ra những tiên đoán và giả định cụ thể, các sự kiện mong muốn đạt được, hay bất cứ điều gì nảy ra cách bất chợt trong đầu... Và tập hợp những tiên đoán, giả định đó, khi bạn đưa vào trong một mô hình, nó sẽ được gọi là một Scenario.

Bởi vì hầu hết các bảng tính sử dụng What-If đòi hỏi phải có những dãy giá trị đầu vào tương đối rộng, nên bạn sẽ phải tạo nhiều Scenario để kiểm tra (chẳng hạn như với mỗi dự đoán, bạn tạo một Goal Seek, bởi vì Goal Seek chỉ cho phép bạn xử lý mỗi lần một biến). Tuy nhiên, để khỏi phải mất thời gian làm cái công việc tẻ nhạt là chèn những giá trị vào những ô thích hợp, Excel cung cấp cho bạn một công cụ là Scenario Manager, để giải quyết vấn đề này dùm bạn. Phần tiếp theo đây sẽ giúp bạn nắm vững công cụ hữu dụng này.​

 

[BNTT]

15.3. Làm việc với các Scenario


15.3.1. Tìm hiểu Sceriano

Như bạn đã thấy, Excel có nhiều tính năng mạnh mẽ giúp bạn xây dựng những mô hình tinh vi để có thể trả lời cho những câu hỏi phức tạp. Dù vậy, vấn để ở đây không phải là việc trả lời câu hỏi, mà là đặt ra những câu hỏi này. Ví dụ, hình 15.19 dưới đây minh họa một bảng tính mô phỏng việc phân tích một bài toán mua nhà trả góp. Bạn sẽ sử dụng mô hình này để quyết định sẽ phải trả góp bao nhiêu tiền hằng tháng, trong bao nhiêu năm, liệu việc tăng/giảm số tiền trả góp hằng tháng này có tiết kiệm được cho bạn không?

Hình 15.19​

Trong bảng tính trên, tôi đã chú thích cho các thuật ngữ tiếng Anh được dùng trong bảng tính. Tuy nhiên, tôi sẽ giải thích rõ thêm một chút.

Bảng tính này mô phỏng cho trường hợp bạn muốn mua trả góp một căn nhà trị giá $100,000 (100.000 USD). Người bán hàng nói rằng, bạn chỉ cần trả trước cho họ $20,000 (20% trị giá nhà), phần còn lại trả góp trong vòng 20 năm, với lãi suất không đổi 6% năm. Tuy nhiên họ còn nói rằng, bạn có thể giảm hoặc tăng số tiền trả trước, cũng như thời hạn vay, và nếu thích thì mỗi tháng bạn có thể trả thêm cho họ bao nhiêu đó (trừ vào tiền gốc) để giảm bớt số tiền lãi...

Bạn đem bảng dự tính của người bán về nhà, và sẽ ngồi suy nghĩ, tính toán... Trong bảng tính ở hình 15.19, tôi đã đưa ra một mô phỏng: giả sử rằng tôi sẽ trả thêm hằng tháng $100 nữa (giá trị tại ô B9, có tên là Paydown, dịch nôm na là trả bớt), thì sẽ như thế nào, có tiết kiệm được không? Và tôi làm thêm một phần nhỏ để tính thử.

Tôi trình bày trong bảng tính hai phần kết quả: môt là Regular Mortgage (B12:B15), phương án trả góp bình thường (theo các số liệu người bán gợi ý), và một là With Paydown (C12:C15), là phương án của tôi, dùng để tính thử nếu có cái khoản Paydown này vào thì sẽ ra sao.

Tại Regular Mortgage, tôi dùng hàm PMT() để tính ra số tiền tôi phải trả hằng tháng, gồm cả gốc và lãi, sau khi đã trừ đi số tiền trả trước lúc ban đầu, là bao nhiêu:

=PMT(Interest_Rate / 12, Term * 12, House_Price - Down_Payment) = $573.14​

(Ở đây tôi đã đặt tên cho các ô tham chiếu: Interest_Rate = B4, Term = B8, House_Price = B3 và Down_Payment = B7)

Sau đó tôi dùng công thức cho ô B13: =B12*Term*12 để tính ra tổng số tiền tôi phải trả cho căn nhà này theo phương án trả góp trên, nó là $137,554.76.

Bây giờ, nếu mỗi tháng tôi trả thêm $100 thì sao? Tại With Paydown, tôi dùng một phép tính bình thường cho ô C12: =B12 + Paydown (= 573.14 + 100 = 673.14), rồi dùng Goal Seek để làm thay đổi số năm trả góp, Goal Seek cho một kết quả là 15.1 (năm)

Nhập con số 15.1 này vào ô C15, dùng công thức = C12*C15*12 cho ô C13, tôi biết được tổng số tiền tôi phải trả góp cho căn nhà trong vòng 15.1 năm là $121,733.04, tiết kiệm được $15,821.72 so với tổng số tiền tôi phải trả theo phương án trả góp trong 25 năm mà người bán đề nghị ($137,544.76).

Nhưng chẳng lẽ cứ với mỗi phương án, lại phải chạy một Goal Seek? Chẳng hạn, tôi muốn biết nếu tăng thời hạn vay lên 25 năm, hoặc tăng số tiền trả tiền trả trước lên 25%, hoặc mỗi tháng chỉ đưa thêm $50 thôi... Và nhất là, sau mỗi lần đưa ra phương án, có cách nào lưu lại để sau này ngồi so sánh phương án nào là tối ưu nhất? Để giải quyết vấn đề này, tôi sẽ sử dụng Scenario.

Tiếp theo đây những ví dụ cụ thể về các Scenario. Scenario Manager của Excel 2007 sẽ cho phép định nghĩa từng scenario (có thể hiểu là phương án, là kịch bản) riêng biệt trong bảng tính. Bạn có thể lưu lại các scenario này, đạt tên cho nó, và sau này có thể gọi nó ra (cùng với tất cả các giá trị nhập liệu đã làm với nó) từ một danh sách.​

 

[BNTT]

15.3. Làm việc với các Scenario


15.3.2. Thiết lập bảng tính để sử dụng Scenario

Trước khi tạo một Scenario, bạn cần quyết định ô nào trong mô hình là ô nhập liệu. Đây sẽ là các biến, là các ô mà khi bạn thay đổi giá trị của chúng thì sẽ làm thay đổi kết quả của mô hình (Excel gọi các ô này là Changing Cell). Bạn có thể sử dụng đến 32 Changing Cell trong một Scenario.

Để đạt được kết quả tốt nhất, bạn hãy làm theo các hướng dẫn sau đây khi thiết lập một Scenario:

• Các Changing Cell nên chứa các hằng số. Vì các công thức có thể bị ảnh hưởng bởi những ô khác, và có thể loại bỏ hoàn toàn Scenario.
  
  
• Để dễ thiết lập một Scenario, và cũng để cho bảng tính được trình bày rõ ràng hơn, bạn hãy kết nhóm các ô và ghi nhãn cho chúng (xem lại hình 15.19).
  
  
• Để rõ ràng hơn nữa, hãy đặt tên cho các Changing Cell.

​

 

[BNTT]

15.3. Làm việc với các Scenario


15.3.3. Tạo một Sceriano

Để làm việc với các Scenario, bạn sử dụng công cụ Scenario Manager của Excel. Công cụ này cho phép bạn thêm, sửa, hiển thị và xóa các Scenario, cũng như tạo các báo cáo tổng kết Scenario.

Khi bảng tính của bạn đã được thiết lập theo ý bạn muốn, bạn có thể thêm một Scenario vào theo các bước sau đây:

1. Chọn Data, What-If Analysis, Scenario Manager. Excel hiển thị hộp thoại Scenario Manager như minh họa ở hình 15.20:
   
   
   

   
   Hình 15.20​
   
   
2. Nhấn Add. Hộp thoại Add Scenario xuất hiện. Hình 15.21 minh họa một hộp thoại Add Scenario đã được điền đầy đủ:
   
   
   

   
   Hình 15.21​
   
   
3. Nhập tên cho Scenario trong khung Scenario Name.
   
   
4. Nhập tham chiếu dẫn đến các ô thay đổi trong bảng tính ở khung Changing Cell. Bạn có thể nhập trực tiếp vào (nhớ tách biệt những ô không liền nhau bằng dấu phẩy), hoặc dùng chuột chọn trong bảng tính. Trong ví dụ này, tôi nhập dãy ô B7:B9.
   
   
5. Sử dụng khung Comment để nhập một vài mô tả cho Scenario này. Phần mô tả này sẽ xuất hiện trong Comment ngoài hộp thoại Scenario Manager, tương ứng với mỗi Scenario được chọn.
   
   
6. Nhấn OK. Excel sẽ hiển thị tiếp hộp thoại Scenario Value như hình ở hình 15.22. Trong hình này, bạn thấy Excel sử dụng tên cho các Changing cell, nhờ đó mà việc nhập liệu được chính xác hơn. Để có được như vậy, bạn phải đặt tên cho các Changing cell này từ trước. Nếu chưa có tên, Excel sẽ hiển thị địa chỉ của các ô đó.
   
   
   

   
   Hình 15.22​
   
   
7. Nhập các giá trị mà bạn muốn thay đổi vào các Changing cell tương ứng. Ví dụ như bạn muốn thay đổi số tiền trả trước (khi mua trả góp), nhập một giá trị mới cho ô Down_Payment; muốn tăng/giảm thời hạn trả góp, nhập giá trị mới vào ô Term; và nếu muốn sửa đổi số tiền sẽ trả thêm hằng tháng, ngoài số tiền gốc và lãi, bạn nhâp một giá trị mới cho ô Paydown.
   
   
8. Để bổ sung thêm một Scenario khác, bạn nhấn Add để quay về lại bước 3. Nếu không, nhấn OK để quay về hôp thoại Scenario Manager.
   
   
9. Nhấn Close để trở về bảng tính.

​

 

[BNTT]

15.3. Làm việc với các Scenario


15.3.4. Hiển thị một Sceriano

Sau khi bạn đã định nghĩa một Scenario, bạn có thể nhập những giá trị vào trong các ô thay đổi (changing cell) bằng cách hiển thị lại Scenario đó từ hộp thoại Scenario Manager. Các bước sau đây trình bày chi tiết cho bạn:

1. Chọn Data, What-If Analysis, Scenario Manager.
   
   
2. Trong danh sách Scenario, chọn Scenario mà bạn muốn hiển thị.
   
   
3. Nhấn Show. Excel sẽ điền các giá trị từ Scenario vào trong các Changing Cell. Hình 15.23 minh họa cho bạn một ví dụ:
   
   
   

   
   Hình 15.23​
   
   Bạn hãy so sánh hình này với hình 15.22, sẽ thấy sự khác biệt trong các Changing Cell, và đồng thời nó cũng làm thay đổi giá trị của kết quả. Đây là một Scenario mà tôi đã định nghĩa cho trường hợp trả trước $15,000, mua trả góp trong thời hạn 25 năm, và mỗi tháng tôi trả thêm cho người bán $50 nữa, trừ vào tiền gốc. Với "kịch bản" này, số tiền tiết kiệm được từ khoản mua trả góp không bằng trường hợp "Best Case" mà tôi đã ví dụ trong bài trước. Tuy nhiên, số tiền tôi trả góp hằng tháng thì ít hơn, và thời gian trả góp được lâu hơn...
   
   
4. Làm lại bước 2 và bước 3 để hiển thị (và sử dụng) một Scenario khác.
   
   
5. Nhấn Close để trở về bảng tính.

​

 

[BNTT]

15.3. Làm việc với các Scenario


15.3.5. Sửa lại một Sceriano

Khi bạn cần thực hiện những thay đổi của một Scenario (đổi tên, chọn lại Changing cell khác, hoặc nhập giá trị khác...), bạn theo các bước sau đây:

1. Chọn Data, What-If Analysis, Scenario Manager.
   
   
2. Trong danh sách Scenario, chọn Scenario mà bạn muốn sửa.
   
   
3. Nhấn Edit. Excel sẽ hiển thị hộp thoại Edit Scenario, hộp thoại này giống hệt hộp thoại Add Scenario mà bạn đã thấy ở hình 15.21.
   
   
4. Thực hiện những thay đổi bạn muốn, rồi nhấn OK. Hộp thoại Scenario Manager sẽ mở ra, và tại đây bạn có thể áp dụng những giá trị mới vào bảng tính, như đã nói ở bài trước.
   
   
5. Làm lại bước 2 đến bước 5 để sửa (và cho áp dụng) một Scenario khác.
   
   
6. Nhấn Close để trở về bảng tính.

​

 

[BNTT]

15.3. Làm việc với các Scenario


15.3.6. Trộn các Sceriano

Các Scenario mà bạn đã tạo ra được lưu trữ trong mỗi WorkSheet. Nếu như bạn có những mô hình tương tự trong các WorkSheet khác, bạn có thể ra các Scenario riêng biệt cho mỗi Sheet và sau đó trộn (merge) chúng lại. Đây là các bước để thực hiện:

1. Kích hoạt WorkSheet mà bạn muốn lưu trữ các Scenario đã được trộn.
   
   
2. Chọn Data, What-If Analysis, Scenario Manager.
   
   
3. Nhấn Merge, Excel sẽ hiển thị hộp thoại Merge Scenario như hình 15.24 sau đây:
   
   
   

   
   Hình 15.24​
   
   
4. Chọn bảng tính chứa Sheet có Scenario trong hộp Book.
   
   
5. Chọn Sheet có chứa Scenario mà bạn sẽ trộn với những Scenario đang có ở Sheet hiện hành trong hộp Sheet.
   
   
6. Nhấn OK để quay về Scenario Manager.
   
   
7. Nhấn Close để trở về bảng tính.

​