Làm thế nào một công ty có thể sử dụng Bộ giải để xác định nên đảm nhận những dự án nào?
Mỗi năm, một công ty như Eli Lilly cần xác định loại thuốc nào cần phát triển; một công ty như Microsoft, chương trình phần mềm nào để phát triển; một công ty như Proctor & Gamble, là sản phẩm tiêu dùng mới để phát triển. Tính năng Bộ giải trong Excel có thể giúp một công ty đưa ra những quyết định này.
Làm thế nào một công ty có thể sử dụng Bộ giải để xác định nên đảm nhận những dự án nào?
Hầu hết các tập đoàn muốn thực hiện các dự án đóng góp giá trị hiện tại ròng (NPV) lớn nhất, phụ thuộc vào các nguồn lực hạn chế (thường là vốn và lao động). Giả sử rằng một công ty phát triển phần mềm đang cố gắng xác định dự án nào trong số 20 dự án phần mềm mà công ty đó nên đảm nhận. NPV (tính bằng hàng triệu đô la) được đóng góp bởi mỗi dự án cũng như vốn (theo hàng triệu đô la) và số lượng lập trình viên cần thiết trong mỗi năm trong ba năm tiếp theo được đưa ra trên trang tính Mô hình Cơ sở trong Capbudget.xlsx tệp, được hiển thị trong Hình 30-1 ở trang tiếp theo. Ví dụ: Dự án 2 thu về 908 triệu USD. Nó yêu cầu 151 triệu USD trong Năm 1, 269 triệu USD trong Năm 2 và 248 triệu USD trong Năm 3. Dự án 2 cần có 139 lập trình viên trong Lớp 1, 86 lập trình viên trong Lớp 2 và 83 lập trình viên trong Lớp 3. Các ô E4:G4 hiển thị số vốn (theo đơn vị hàng triệu đô la) sẵn có trong mỗi năm trong số ba năm và các ô H4:J4 cho biết có bao nhiêu lập trình viên đang sẵn sàng. Ví dụ: trong năm thứ nhất, có tới 2,5 tỷ USD vốn và 900 lập trình viên.
Công ty phải quyết định xem có nên đảm nhận từng dự án hay không. Hãy giả sử rằng chúng ta không thể thực hiện một phần nhỏ của một dự án phần mềm; Ví dụ, nếu chúng ta phân bổ 0,5 số tài nguyên cần thiết, chúng ta sẽ có một chương trình không hoạt động mang lại doanh thu 0 đô la!
Mẹo trong lập mô hình các tình huống mà bạn làm hoặc không làm điều gì đó là sử dụng các ô thay đổi nhị phân. Ô thay đổi nhị phân luôn bằng 0 hoặc 1. Khi một ô thay đổi nhị phân tương ứng với một dự án bằng 1, chúng ta thực hiện dự án đó. Nếu ô thay đổi nhị phân tương ứng với một dự án bằng 0, chúng ta sẽ không thực hiện dự án đó. Bạn thiết lập Bộ giải để sử dụng một phạm vi các ô thay đổi nhị phân bằng cách thêm một ràng buộc—chọn các ô thay đổi bạn muốn dùng rồi chọn Bin từ danh sách trong hộp thoại Thêm Ràng buộc.
Với nền tảng này, chúng tôi đã sẵn sàng giải quyết vấn đề lựa chọn dự án phần mềm. Như thường lệ với mô hình Bộ giải, chúng ta bắt đầu bằng cách xác định ô mục tiêu, các ô thay đổi và các ràng buộc.
- Ô đích. Chúng tôi tối đa hóa NPV được tạo ra bởi các dự án được chọn.
- Thay đổi ô. Chúng tôi tìm kiếm một ô thay đổi nhị phân 0 hoặc 1 cho mỗi dự án. Tôi đã định vị các ô này trong phạm vi A6:A25 (và đặt tên cho phạm vi là doit). Ví dụ, số 1 trong ô A6 cho biết rằng chúng tôi thực hiện Dự án 1; 0 trong ô C6 cho biết chúng tôi không thực hiện Dự án 1.
- Ràng buộc. Chúng ta cần đảm bảo rằng đối với mỗi năm t (t = 1, 2, 3), vốn năm t sử dụng nhỏ hơn hoặc bằng năm t vốn có sẵn và năm t lao động sử dụng nhỏ hơn hoặc bằng năm t lao động có sẵn.
Như bạn có thể thấy, trang tính của chúng tôi phải tính NPV, vốn sử dụng hàng năm và các lập trình viên sử dụng mỗi năm cho bất kỳ lựa chọn dự án nào. Trong ô B2, tôi dùng công thức SUMPRODUCT(doit,NPV) để tính tổng NPV được tạo ra bởi các dự án đã chọn. (Tên phạm vi NPV tham chiếu đến phạm vi C6:C25.) Đối với mọi dự án có số 1 trong cột A, công thức này chọn NPV của dự án và với mọi dự án có số 0 trong cột A, công thức này sẽ không chọn NPV của dự án. Do đó, chúng ta có thể tính NPV của tất cả các dự án và ô mục tiêu của chúng ta là tuyến tính vì nó được tính toán bằng cách tính tổng các số hạng theo dạng (ô thay đổi)*(hằng số). Tương tự, tôi tính toán số vốn sử dụng mỗi năm và nhân công sử dụng mỗi năm bằng cách sao chép công thức SUMPRODUCT(doit,E6:E25) từ E2 sang F2:J2.
Bây giờ tôi điền vào hộp thoại Tham số Bộ giải như trong Hình 30-2.
Mục tiêu của chúng tôi là tối đa hóa NPV của các dự án đã chọn (ô B2). Các ô thay đổi của chúng ta (phạm vi có tên là doit) là các ô thay đổi nhị phân cho mỗi dự án. Giới hạn E2:J2<=E4:J4 đảm bảo rằng trong mỗi năm vốn và lao động được sử dụng nhỏ hơn hoặc bằng vốn và lao động sẵn có. Để thêm ràng buộc làm cho các ô thay đổi nhị phân, tôi bấm Thêm trong hộp thoại Tham số Bộ giải rồi chọn Bin từ danh sách ở giữa hộp thoại. Hộp thoại Thêm Ràng buộc sẽ xuất hiện như trong Hình 30-3.
Mô hình của chúng tôi là tuyến tính vì ô đích được tính toán dưới dạng tổng của các thuật ngữ có dạng (ô thay đổi)*(hằng số) và vì các ràng buộc sử dụng tài nguyên được tính toán bằng cách so sánh tổng (ô thay đổi)*(hằng số) với một hằng số.
Với hộp thoại Tham số Bộ giải được điền vào, hãy bấm Giải quyết và chúng ta sẽ có kết quả được hiển thị trước đó trong Hình 30-1. Công ty có thể thu được NPV tối đa là 9.293 triệu đô la (9,293 tỷ USD) bằng cách chọn các Dự án 2, 3, 6–10, 14–16, 19 và 20.
Xử lý các ràng buộc khác
Đôi khi các mô hình lựa chọn dự án có những ràng buộc khác. Ví dụ: giả sử rằng nếu chúng ta chọn Dự án 3, chúng ta cũng phải chọn Dự án 4. Vì giải pháp tối ưu hiện tại của chúng tôi chọn Dự án 3 chứ không phải Dự án 4, chúng tôi biết rằng giải pháp hiện tại của chúng tôi không thể duy trì tối ưu. Để giải quyết vấn đề này, chỉ cần thêm ràng buộc ô thay đổi nhị phân cho Dự án 3 nhỏ hơn hoặc bằng ô thay đổi nhị phân cho Dự án 4.
Bạn có thể tìm thấy ví dụ này trên trang tính Nếu 3 rồi 4 trong Capbudget.xlsx tệp được hiển thị trong Hình 30-4. Ô L9 tham chiếu đến giá trị nhị phân liên quan đến Dự án 3 và ô L12 tham chiếu đến giá trị nhị phân liên quan đến Dự án 4. Bằng cách thêm ràng buộc L9<=L12, nếu chúng ta chọn Dự án 3, L9 bằng 1 và ràng buộc của chúng ta buộc L12 (nhị phân Dự án 4) bằng 1. Ràng buộc của chúng ta cũng phải để giá trị nhị phân trong ô thay đổi của Dự án 4 không bị hạn chế nếu chúng ta không chọn Dự án 3. Nếu chúng ta không chọn Dự án 3, L9 bằng 0 và ràng buộc của chúng ta cho phép nhị phân Dự án 4 bằng 0 hoặc 1, đó là điều chúng ta muốn. Giải pháp tối ưu mới được thể hiện trong Hình 30-4.
Giải pháp tối ưu mới được tính toán nếu chọn Dự án 3 đồng nghĩa với việc chúng ta cũng phải chọn Dự án 4. Bây giờ, giả sử rằng chúng ta chỉ có thể thực hiện bốn dự án trong số các Dự án từ 1 đến 10. (Xem trang tính At Most 4 Of P1–P10, hiển thị trong Hình 30-5.) Tại ô L8, chúng ta tính tổng các giá trị nhị phân liên kết với Dự án từ 1 đến 10 bằng công thức SUM(A6:A15). Sau đó, chúng ta thêm ràng buộc L8<=L10, để đảm bảo rằng, nhiều nhất, 4 trong số 10 dự án đầu tiên được chọn. Giải pháp tối ưu mới được thể hiện trong Hình 30-5. NPV đã giảm xuống còn 9,014 tỷ USD.
Giải quyết các bài toán về lập trình nhị phân và số nguyên
Các mô hình Bộ giải tuyến tính, trong đó một số hoặc tất cả các ô thay đổi được yêu cầu phải là nhị phân hoặc số nguyên thường khó giải hơn các mô hình tuyến tính, trong đó tất cả các ô thay đổi được phép là phân số. Vì lý do này, chúng ta thường hài lòng với một giải pháp gần tối ưu cho bài toán lập trình nhị phân hoặc số nguyên. Nếu mô hình Bộ giải của bạn chạy trong một thời gian dài, bạn có thể muốn xem xét điều chỉnh thiết đặt Dung sai trong hộp thoại Tùy chọn Bộ giải. (Xem Hình 30-6.) Ví dụ, cài đặt Dung sai 0,5% có nghĩa là Bộ giải sẽ dừng ngay lần đầu tiên nó tìm thấy giải pháp khả thi nằm trong phạm vi 0,5 phần trăm so với giá trị ô đích tối ưu trên lý thuyết (giá trị ô mục tiêu tối ưu trên lý thuyết là giá trị mục tiêu tối ưu được tìm thấy khi các ràng buộc nhị phân và số nguyên được bỏ qua). Thông thường, chúng ta phải đối mặt với sự lựa chọn giữa việc tìm một câu trả lời trong vòng 10 phần trăm của tối ưu trong 10 phút hoặc tìm ra một giải pháp tối ưu trong hai tuần sử dụng máy tính! Giá trị Dung sai mặc định là 0,05%, có nghĩa là Bộ giải dừng lại khi nó tìm thấy một giá trị ô Đích nằm trong phạm vi 0,05 phần trăm giá trị ô đích tối ưu trên lý thuyết.
Sự cố
- Một công ty có chín dự án đang được xem xét. NPV được thêm vào bởi mỗi dự án và số vốn cần thiết cho mỗi dự án trong hai năm tới được thể hiện trong bảng sau đây. (Tất cả các con số đều tính bằng hàng triệu.) Ví dụ, Dự án 1 sẽ bổ sung thêm 14 triệu USD NPV và yêu cầu chi tiêu 12 triệu USD trong Năm 1 và 3 triệu USD trong Năm 2. Trong Năm 1, 50 triệu USD vốn dành cho các dự án và 20 triệu USD trong Năm 2.
| NPV | Chi phí năm 1 | Chi tiêu năm thứ 2 | |
|---|---|---|---|
| Dự án 1 | 14 | 12 | 3 |
| Dự án 2 | 17 | 54 | 7 |
| Dự án 3 | 17 | 6 | 6 |
| Dự án 4 | 15 | 6 | 2 |
| Dự án 5 | 40 | 30 | 35 |
| Dự án 6 | 12 | 6 | 6 |
| Dự án 7 | 14 | 48 | 4 |
| Dự án 8 | 10 | 36 | 3 |
| Dự án 9 | 12 | 18 | 3 |
- Nếu chúng ta không thể thực hiện một phần nhỏ của một dự án nhưng phải thực hiện tất cả hoặc không thực hiện dự án nào, làm thế nào chúng ta có thể tối đa hóa NPV?
- Giả sử nếu Dự án 4 được thực hiện, Dự án 5 phải được thực hiện. Làm thế nào chúng ta có thể tối đa hóa NPV?
Một công ty xuất bản đang cố gắng xác định cuốn sách nào trong số 36 cuốn sách mà họ sẽ xuất bản trong năm nay. Pressdata.xlsx tệp cung cấp thông tin sau về mỗi cuốn sách:
- Doanh thu dự kiến và chi phí phát triển (tính theo hàng nghìn đô la)
- Các trang trong mỗi cuốn sách
- Liệu cuốn sách có hướng đến đối tượng các nhà phát triển phần mềm hay không (được thể hiện bằng số 1 trong cột E)
Một công ty xuất bản có thể xuất bản sách tổng cộng lên đến 8500 trang trong năm nay và phải xuất bản ít nhất bốn cuốn sách hướng đến các nhà phát triển phần mềm. Làm thế nào công ty có thể tối đa hóa lợi nhuận của mình?
Giới thiệu về bài viết
Bài viết này được chuyển thể từ Microsoft Office Excel 2007, Phân tích Dữ liệu và Lập mô hình Kinh doanh .
Cuốn sách kiểu lớp học này được phát triển từ một loạt các bài thuyết trình của Wayne Winston, một nhà thống kê và giáo sư kinh doanh nổi tiếng, người chuyên về các ứng dụng sáng tạo, thực tế của Excel.