Dùng bộ giải để lập ngân sách vốn

Lưu ý: Chúng tôi muốn cung cấp cho bạn nội dung trợ giúp mới nhất ở chính ngôn ngữ của bạn, nhanh nhất có thể. Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Vui lòng cho chúng tôi biết ở cuối trang này rằng thông tin có hữu ích với bạn không? Dưới đây là bài viết bằng Tiếng Anh để bạn tham khảo dễ hơn.

Làm thế nào có thể sử dụng một công ty bộ giải để xác định dự án mà nó nên thực hiện?

Mỗi năm, một công ty như Eli Lilly cần xác định mà ma túy phát triển; một công ty chẳng hạn như Microsoft, phần mềm nào chương trình phát triển; một công ty như Proctor & đánh bạc, những người tiêu dùng sản phẩm mới phát triển. Tính năng bộ giải trong Excel có thể giúp một công ty ra những quyết định.

Hầu hết các công ty muốn thực hiện dự án đóng góp giá trị hiện tại ròng lớn nhất (NPV), phải tuân theo giới hạn tài nguyên (thường là vốn và lao động). Giả sử một công ty phát triển phần mềm đang cố gắng xác định trong dự án phần mềm 20 nó nên thực hiện. NPV (trong hàng triệu Dollar) đóng góp của mỗi dự án, vốn (trong hàng triệu Dollar) và số cần thiết trong mỗi ba năm tiếp theo lập trình được cung cấp trên trang Mô hình cơ bản trong tệp Capbudget.xlsx, tính Hiển thị trong hình 30-1 trên trang tiếp theo. Ví dụ, dự án 2 tạo ra 908 triệu. Nó yêu cầu $151 triệu trong năm 1, $269 triệu trong năm 2 và $248 triệu trong năm 3. Dự án 2 yêu cầu lập trình 139 trong năm 1, 86 lập trình trong năm 2 và lập trình 83 trong năm 3. Hiển thị ô E4:G4 Hoa (trong hàng triệu Dollar) sẵn dùng trong quá trình từng ba năm và ô H4:J4 cho biết số lượng lập trình sẵn. Ví dụ, trong năm 1 đến 2,5 tỉ Hoa và 900 lập trình sẵn dùng.

Công ty phải quyết định xem nó nên thực hiện từng dự án. Giả sử rằng chúng tôi không thể thực hiện phân số của một dự án phần mềm; Nếu chúng tôi phân phối 0,5 tài nguyên cần thiết, ví dụ, chúng tôi sẽ có một chương trình nghỉ sẽ mang lại cho chúng tôi doanh thu $0!

Mẹo trong lập mô hình trường hợp mà bạn thực hiện hoặc không thực hiện điều gì đó là sử dụng ô thay đổi nhị phân. Một số nhị phân thay đổi ô luôn bằng 0 và 1. Khi một nhị phân thay đổi ô tương ứng với dự án bằng 1, chúng tôi có thể thực hiện dự án. Nếu một nhị phân thay đổi ô tương ứng với dự án bằng 0, chúng tôi không thực hiện dự án. Bạn thiết lập bộ giải để dùng một phạm vi nhị phân thay đổi các ô bằng cách thêm ràng buộc — hãy chọn các ô thay đổi bạn muốn sử dụng và sau đó chọn rác từ danh sách trong hộp thoại thêm ràng buộc.

Ảnh quyển sách

Với nền 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 tôi bắt đầu bằng cách xác định ô đích của chúng tôi, các ô thay đổi và các giới hạn.

  • Ô đích. Chúng tôi tối đa hóa NPV được tạo bởi dự án đã chọn.

  • Thay đổi ô.Chúng tôi tìm một 0 hoặc 1 ô thay đổi nhị phân cho từng dự án. Tôi đã định vị các ô trong phạm vi A6:A25 (và có tên là phạm vi doit). Ví dụ, 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 rằng chúng tôi không thực hiện dự án 1.

  • Ràng buộc.Chúng tôi cần phải đảm bảo rằng cho mỗi năm t (t = 1, 2, 3), năm t vốn được dùng là nhỏ hơn hoặc bằng năm t vốn sẵn dùng, và năm t lao động sử dụng là nhỏ hơn hoặc bằng năm t lao động sẵn dùng.

Khi bạn có thể nhìn thấy, trang tính của chúng tôi phải tính toán cho bất kỳ vùng chọn của dự án vào NPV, vốn được sử dụng một năm, và lập trình được sử dụng mỗi năm. Trong ô B2, tôi sử dụng công thức SUMPRODUCT(doit,NPV) để tính tổng NPV được tạo bởi dự án đã chọn. (Tên phạm vi NPV tham chiếu đến phạm vi C6:C25.) Cho mỗi dự án với 1 trong cột A, công thức này chọn lên NPV dự án và cho mỗi dự án với 0 trong cột A, công thức này không nhấc NPV dự án. Do đó, chúng tôi có thể tính NPV tất cả dự án và ô đích của chúng tôi là tuyến tính vì nó được tính cho chúng bằng cách tổng hợp thuật ngữ theo biểu mẫu (thay đổi cell)*(constant). Một cách tương tự, tôi tính thủ dùng mỗi năm và lao động sử dụng mỗi năm bằng cách sao chép từ E2 F2:J2 công thức SUMPRODUCT(doit,E6:E25).

Tôi bây giờ điền trong hộp thoại tham số bộ giải như minh họa trong hình 30-2.

Ảnh quyển sách

Mục tiêu của chúng tôi là tối đa hóa NPV của dự án đã chọn (ô B2). Chúng tôi thay đổi ô (phạm vi có tên là doit) sẽ nhị phân thay đổi ô cho từng dự án. Ràng buộc E2:J2< = E4:J4 đảm bảo rằng trong mỗi năm Hoa và lao động sử dụng lớn hơn hoặc bằng hoa và lao động sẵn dùng. Để 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 và sau đó chọn rác 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ư minh họa trong hình 30-3.

Ảnh quyển sách

Mô hình của chúng tôi là tuyến tính vì ô đích được tính như tổng của thuật ngữ có biểu mẫu (thay đổi cell)*(constant) và vì ràng buộc sử dụng tài nguyên được tính cho chúng bằng cách so sánh tổng (thay đổi cells)*(constants) cho một hằng số.

Hộp thoại tham số bộ giải được tô trong, bấm giải quyết và chúng tôi có kết quả hiển thị phiên bản cũ hơn trong hình 30-1. Công ty có thể có được một NPV tối đa của $9,293 triệu (9.293 tỉ) bằng cách chọn dự án 2, 3, 6-10, 14-16, 19 và 20.

Đôi khi dự án lựa chọn mô hình có ràng buộc khác. Ví dụ, giả sử rằng nếu chúng tôi chọn dự án 3, chúng tôi 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 sẽ chọn dự án 3 nhưng không 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ể vẫn giữ nguyên tối ưu. Để giải quyết vấn đề này, chỉ cần thêm ràng buộc nhị phân ô thay đổi cho dự án 3 là nhỏ hơn hoặc bằng nhị phân ô thay đổi 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 tệp Capbudget.xlsx, được hiển thị trong hình 30-4. Ô L9 tham chiếu đến giá trị nhị phân có liên quan đến dự án 3 và ô L12 giá trị nhị phân có liên quan đến dự án 4. Bằng cách thêm ràng buộc L9< = L12, nếu chúng tôi chọn dự án 3, L9 bằng 1 và ràng buộc của chúng tôi yêu cầu L12 (dự án 4 nhị phân) để bằng 1. Ràng buộc của chúng tôi cũng phải rời khỏi nhị phân giá trị trong ô thay đổi của dự án 4 không hạn chế nếu chúng tôi không chọn dự án 3. Nếu chúng tôi không chọn dự án 3, L9 bằng 0 và ràng buộc của chúng tôi cho phép 4 dự án nhị phân bằng 0 hoặc 1, đó là những gì chúng tôi muốn. Giải pháp tối ưu mới được hiển thị trong hình 30-4.

Ảnh quyển sách

Giải pháp tối ưu mới được tính toán nếu chọn dự án 3 có nghĩa là chúng tôi cũng phải chọn dự án 4. Bây giờ, giả sử rằng chúng tôi có thể thực hiện chỉ có bốn dự án trong số dự án 1 đến 10. (Hãy xem trang tính Tối đa 4 của P1 – P10 , Hiển thị trong hình 30-5.) Trong ô L8, chúng tôi tính tổng các giá trị nhị phân được liên kết với dự án 1 đến 10 với công thức SUM(A6:A15). Sau đó chúng ta thêm ràng buộc L8< = L10, vốn sẽ đảm bảo rằng, nhất, 4 của dự án trước tiên 10 được chọn. Giải pháp tối ưu mới được hiển thị trong hình 30-5. NPV có mất để 9.014 tỉ.

Ảnh quyển sách

Tuyến tính giải mô hình trong một số hoặc tất cả thay đổi ô nào được yêu cầu có nhị phân hoặc số nguyên là thường khó để giải quyết hơn tuyến tính mô hình trong đó tất cả các ô thay đổi được phép là phân số. Lý do này, chúng tôi thường được hài lòng với một giải pháp gần tối ưu cho một vấ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 sẽ chạy trong nhiều thời gian, bạn có thể muốn cân nhắc điều chỉnh thiết đặt sai trong hộp thoại tùy chọn bộ giải. (Hãy xem hình 30-6). Ví dụ, một thiết đặt khoan dung 0,5% có nghĩa là bộ giải sẽ ngừng lần đầu tiên, nó sẽ tìm giải pháp khả thi vốn nằm trong 0,5 phần trăm của giá trị ô mục tiêu tối ưu thuyết (giá trị ô mục tiêu tối ưu thuyết là giá trị tối ưu đích tìm thấy các ràng buộc nhị phân và số nguyên được bỏ qua). Mức độ thường xuyên, chúng tôi phải đối mặt với một lựa chọn giữa tìm câu trả lời trong 10 phần trăm của tối ưu trong 10 phút hoặc tìm giải pháp tối ưu việc theo một trong hai tuần của máy tính thời gian! Giá trị sai mặc định là 0,05%, điều này có nghĩa giải dừng khi nó tìm một giá trị ô đích trong 0,05 phần trăm của giá trị ô đích thuyết tối ưu.

Ảnh quyển sách

  1. 1. một công ty có chín dự án trong phần xem xét. NPV thêm theo từng dự án và vốn được yêu cầu theo từng dự án trong hai năm tiếp theo được hiển thị trong bảng sau đây. (Tất cả các số nằm trong hàng triệu.) Ví dụ, dự án 1 sẽ thêm 14 triệu trong NPV và yêu cầu chi phí của $12 triệu trong năm 1 và 3 triệu trong năm 2. Trong năm 1, $50 triệu trong vốn sẵn dùng cho dự án, và $20 triệu là sẵn dùng trong năm 2.

NPV

Chi phí năm 1

Chi phí năm 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 tôi không thể thực hiện phân số của một dự án nhưng phải thực hiện tất cả hoặc không có dự án, làm thế nào có thể chúng tôi tối đa hóa NPV?

  • Giả sử rằng nếu dự án 4 thực hiện, dự án 5 phải được thực hiện. Làm thế nào, chúng tôi có thể phóng to NPV?

  • Một công ty phát hành đang cố gắng xác định trong 36 cuốn sách nó sẽ phát hành này năm. Tệp Pressdata.xlsx cung cấp thông tin sau đây về từng sổ:

    • Chi phí dự kiến doanh thu và phát triển (trong hàng ngàn Dollar)

    • Trang trong mỗi sổ

    • Việc cuốn sách hướng tiến tới người xem của người phát triển phần mềm (biểu thị bằng 1 trong cột E)

      Một công ty phát hành có thể phát hành sổ tổng cộng trang 8500 tối đa năm và phải phát hành sổ ít bốn hướng tới người phát triển phần mềm. Làm thế nào có thể công ty tối đa hóa lợi nhuận của nó?

Bài viết này được điều chỉnh từ Microsoft Office Excel 2007 phân tích dữ liệu và lập mô hình kinh doanh bằng Wayne L. Winston.

Cuốn sách lớp học-kiểu này đã được phát triển từ một chuỗi các bản trình bày bằng Wayne Winston, một phổ thống kê và các giáo sư business ai chuyên sáng tạo, thực hiện các ứng dụng Excel.

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×