ตัวอย่างการใช้ Solver ใน Microsoft Excel ตอนที่ 3

ตัวอย่างที่ 2

ผู้ลงทุนรายหนึ่ง มีเงินสดอยู่ 500,000 บาท ประสงค์จะนำเงินจำนวนนี้ไปลงทุนในหลักทรัพย์ประเภทต่าง ๆ ที่มีอยู่ 5 ประเภทด้วยกัน คือ หุ้นสามัญ หุ้นบุริมสิทธิ์ หุ้นกู้ พันธบัตรรัฐบาล และทรัพย์สินถาวร อัตราผลตอบแทนจากหลักทรัพย์แต่ละประเภทมีดังนี้คือ

หลักทรัพย์	ผลต่อแทน % ต่อปี
หุ้นสามัญ	10
หุ้นกู้	9.5
หุ้นบุริมสิทธิ	8
พันธบัตรรัฐบาล	8.5
ทรัพย์สินถาวร	12

ผู้ลงทุนต้องการผลตอบแทนจากการลงทุสูงสุดโดยกำหนดเงื่อนไขต่างๆ ดังนี้ คือ

1. เงินลงทุนในหุ้นสามัญ จะต้องไม่เกินผลรวมของเงินทุนทั้งสิ้นในหุ้นบุริมสิทธิ และพันธบัตรรัฐบาล
2.เงินลงทุนในหุ้นกู้และทรัพย์สินถาวร รวมกันแล้วต้องไม่น้อยกว่าเงินลงทุนในหุ้นบุริมสิทธิ์
3.เงินลงทุนในหุ้นกู้ จะต้องไม่มากกว่าเงินลงทุนในพันธบัตรรัฐบาล
4.เงินลงทุนในทรัพย์สินถาวร จะต้องไม่มากกว่าเงินลงทุนในหุ้นกู้

ผู้ลงทุนควรลงทุนอย่างไรจึงจะได้ผลตอบแทนสูงที่สุด

มาลองใช้ Solver หาคำตอบกันครับ

ขั้นที่ 1 : สร้าง File Excel ตามรูป

คลิกเพื่อขยายภาพ

จากโจทย์เราจะได้

ตัวแปร [Variable] ดังนี้

1. x1 = หุ้นสามัญ 
2. x2 = หุ้นกู้
3. x3 = หุ้นบุริมสิทธิ
4. x4 = พันธบัตรรัฐบาล
5. x5 = ทรัพย์สินถาวร

ข้อจำกัด [Constraints] ดังนี้

1. เงินลงทุนในหุ้นสามัญ จะต้องไม่เกินผลรวมของเงินทุนทั้งสิ้นในหุ้นบุริมสิทธิ และพันธบัตรรัฐบาล (x1 <= x3+x4)
2. เงินลงทุนในหุ้นกู้และทรัพย์สินถาวร รวมกันแล้วต้องไม่น้อยกว่าเงินลงทุนในหุ้นบุริมสิทธิ์ (x2 + x5 >= x3)
3. เงินลงทุนในหุ้นกู้ จะต้องไม่มากกว่าเงินลงทุนในพันธบัตรรัฐบาล (x2 <= x4)
4. เงินลงทุนในทรัพย์สินถาวร จะต้องไม่มากกว่าเงินลงทุนในหุ้นกู้ (x5 <= x2)
5. มีเงินลงทุนอยู่ไม่เกิน 500,000 บาท (x1+x2+x3+x4+x5 <= 500,000)

*** แก้สมการโดยการทำข้างขวาให้เป็น 0 แล้วย้ายตัวแปรมาฝั่งซ้ายเป็นค่าติดลบ จึงใส่ -1 ลงไปในตาราง excel ***

วัตถุประสงค์ [Objective] คือ ผลตอบแทนสูงที่สุด คือ จำนวนเงินลงทุนในหลักทรัพย์ประเภทต่าง ๆ x ผลตอบแทนต่อปี

ขั้น 2 : เมื่อเรารู้หลักการแล้วจึงนำมาใส่สูตรโดย

1. cell G3 พิมพ์สูตร ดังนี้ =(B3*$B$2)+(C3*$C$2)+(D3*$D$2)+(E3*$E$2)+(F3*$F$2)
2. copy สูตรมาวางที่ cell G5:G9

** ถ้าทำถูกต้องจะได้ผลลัพธ์ออกมาเป็น 0 **

ขั้นที่ 3 : ให้ Solver คำนวณผลลัพธ์ออกมาให้ ดังนี้

1. ไปที่เมนู Data > Solver
2. กำหนด Objective Cell ในที่คือ cell G3
3. กำหนด Changing Variable Cells ในที่นี้คือ cell B2:F2
4. คลิกปุ่ม Add เพื่อกำหนดข้อจำกัด [Constraints]
5. กำหนดข้อจำกัดโดยเลือก Cell Reference , เครื่องหมายเปรียบเทียบ , Cell Constraints ให้่ถูกต้อง ถ้าทำถูกต้องจะได้ผลลัพธ์ตามรูป
6. คลิกปุ่ม Solve
7. เลือกตัวเลือกตามต้องการ

คลิกเพื่อขยายภาพ

*** สามารถดูวิธีการ Add Constraints และรายละเอียดของ Solver Results ได้ที่ บทความตอนที่ 2 นะครับ ***

---

บทความที่เกี่ยวข้อง 

ตัวอย่างการใช้ Solver ใน Microsoft Excel ตอนที่ 1 Solver คืออะไร 

ตัวอย่างการใช้ Solver ใน Microsoft Excel ตอนที่ 2 การจัดสรรเนื้อที่วางสินค้าให้ได้กำไรสูงสุด