บริษัทจะใช้ Solver เพื่อกําหนดโครงการที่ควรดําเนินการได้อย่างไร
ในแต่ละปีบริษัทอย่าง Eli Lilly จําเป็นต้องพิจารณาว่าจะพัฒนายาใด บริษัทอย่าง Microsoft ซึ่งโปรแกรมซอฟต์แวร์ที่จะพัฒนา บริษัทอย่าง Proctor & Gamble ซึ่งเป็นผลิตภัณฑ์อุปโภคบริโภคใหม่ที่จะพัฒนา ฟีเจอร์ Solver ใน Excel สามารถช่วยบริษัทในการตัดสินใจเหล่านี้ได้
บริษัทจะใช้ Solver เพื่อกําหนดโครงการที่ควรดําเนินการได้อย่างไร
บริษัทส่วนใหญ่ต้องการดําเนินโครงการที่สนับสนุนมูลค่าปัจจุบันสุทธิ (NPV) มากที่สุด โดยขึ้นอยู่กับทรัพยากรที่จํากัด (โดยปกติคือทุนและแรงงาน) สมมติว่าบริษัทพัฒนาซอฟต์แวร์กําลังพยายามพิจารณาว่าควรดําเนินการโครงการใดจาก 20 โครงการ NPV (เป็นล้านดอลลาร์) ที่บริจาคโดยแต่ละโครงการ รวมถึงเงินทุน (เป็นล้านดอลลาร์) และจํานวนโปรแกรมเมอร์ที่จําเป็นในแต่ละปีในอีกสามปีข้างหน้าจะอยู่ในเวิร์กชีต โมเดลพื้นฐาน ในไฟล์ Capbudget.xlsx ซึ่งแสดงในรูปที่ 30-1 ในหน้าถัดไป ตัวอย่างเช่น Project 2 ให้ผลตอบแทน 908 ล้านดอลลาร์ ต้องใช้เงิน 151 ล้านดอลลาร์ในระหว่างปีที่ 1, 269 ล้านดอลลาร์ในระหว่างปีที่ 2 และ 248 ล้านดอลลาร์ในช่วงปีที่ 3 Project 2 ต้องการโปรแกรมเมอร์ 139 คนในช่วงปีที่ 1 โปรแกรมเมอร์ 86 คนในช่วงปีที่ 2 และโปรแกรมเมอร์ 83 คนในช่วงปีที่ 3 เซลล์ E4:G4 แสดงตัวพิมพ์ใหญ่ (เป็นล้านดอลลาร์) ที่มีอยู่ในแต่ละสามปี และเซลล์ H4:J4 จะระบุจํานวนโปรแกรมเมอร์ที่ว่าง ตัวอย่างเช่น ในช่วงปีที่ 1 มีเงินทุนสูงถึง 2.5 พันล้านดอลลาร์และโปรแกรมเมอร์ 900 คน
บริษัทต้องตัดสินใจว่าควรดําเนินการแต่ละโครงการหรือไม่ สมมติว่าเราไม่สามารถดําเนินการโครงการซอฟต์แวร์ได้เพียงเล็กน้อย ตัวอย่างเช่น หากเราจัดสรรทรัพยากรที่จําเป็น 0.5 รายการ เราจะมีโปรแกรมที่ไม่ทํางานที่จะทําให้เรามีรายได้ 0 ดอลลาร์!
เคล็ดลับในการสร้างแบบจําลองสถานการณ์ที่คุณทําหรือไม่ทําอะไรคือการใช้เซลล์ที่เปลี่ยนเลขฐานสอง เซลล์ที่เปลี่ยนแปลงเลขฐานสองจะเท่ากับ 0 หรือ 1 เสมอ เมื่อเซลล์การเปลี่ยนแปลงเลขฐานสองที่สอดคล้องกับโครงการเท่ากับ 1 เราจะทําโครงการ ถ้าเซลล์ที่เปลี่ยนแปลงเลขฐานสองที่สอดคล้องกับโครงการเท่ากับ 0 เราจะไม่ทําโครงการ คุณตั้งค่า Solver ให้ใช้ช่วงของเซลล์ที่เปลี่ยนแปลงไบนารีโดยการเพิ่มข้อจํากัด ให้เลือกเซลล์ที่เปลี่ยนแปลงที่คุณต้องการใช้ แล้วเลือก Bin จากรายการในกล่องโต้ตอบ เพิ่มข้อจํากัด
ด้วยภูมิหลังนี้ เราจึงพร้อมที่จะแก้ไขปัญหาการเลือกโครงการซอฟต์แวร์ เช่นเคยกับโมเดล Solver เราจะเริ่มต้นด้วยการระบุเซลล์เป้าหมาย เซลล์ที่เปลี่ยนแปลง และข้อจํากัด
- เซลล์เป้าหมาย เราเพิ่ม NPV ที่สร้างขึ้นโดยโครงการที่เลือกให้สูงสุด
- การเปลี่ยนเซลล์ เราค้นหาเซลล์ที่เปลี่ยนแปลงเลขฐานสอง 0 หรือ 1 สําหรับแต่ละโครงการ ฉันได้ระบุตําแหน่งเซลล์เหล่านี้ในช่วง A6:A25 (และตั้งชื่อช่วงว่า doit) ตัวอย่างเช่น 1 ในเซลล์ A6 แสดงว่าเราดําเนินโครงการ 1 0 ในเซลล์ C6 แสดงว่าเราไม่ได้ดําเนินโครงการ 1
- ข้อจํากัด เราจําเป็นต้องตรวจสอบให้แน่ใจว่าสําหรับแต่ละปี t (t = 1, 2, 3) เงินทุนปี t ที่ใช้น้อยกว่าหรือเท่ากับเงินทุนปี t ที่มีอยู่ และแรงงานปี t ที่ใช้น้อยกว่าหรือเท่ากับแรงงานปี t ที่มีอยู่
อย่างที่คุณเห็นเวิร์กชีตของเราต้องคํานวณ NPV เงินทุนที่ใช้ประจําปี และโปรแกรมเมอร์ที่ใช้ในแต่ละปีสําหรับโครงการที่เลือก ในเซลล์ B2 ฉันใช้สูตร SUMPRODUCT(doit,NPV) เพื่อคํานวณ NPV ทั้งหมดที่สร้างโดยโครงการที่เลือก (ชื่อช่วง NPV อ้างอิงถึงช่วง C6:C25) สําหรับทุกโครงการที่มี 1 ในคอลัมน์ A สูตรนี้เลือกค่า NPV ของโครงการ และสําหรับทุกโครงการที่มี 0 ในคอลัมน์ A สูตรนี้จะไม่เลือกค่า NPV ของโครงการ ดังนั้น เราสามารถคํานวณ NPV ของโครงการทั้งหมดได้ และเซลล์เป้าหมายของเราเป็นแบบเชิงเส้น เนื่องจากมีการคํานวณโดยการรวมข้อมูลตามรูปแบบ (เซลล์เปลี่ยน)*(ค่าคงที่) ในทํานองเดียวกัน ผมคํานวณทุนที่ใช้ในแต่ละปีและแรงงานที่ใช้ในแต่ละปี โดยการคัดลอกสูตร SUMPRODUCT(doit,E6:E25) จาก E2 เป็น F2:J2
ตอนนี้ผมกรอกข้อมูลในกล่องโต้ตอบ Solver Parameters ดังที่แสดงในรูปที่ 30-2
เป้าหมายของเราคือการเพิ่ม NPV ของโครงการที่เลือกให้สูงสุด (เซลล์ B2) เซลล์ที่เปลี่ยนแปลงของเรา (ช่วงที่มีชื่อว่า doit) คือเซลล์ที่เปลี่ยนเลขฐานสองสําหรับแต่ละโครงการ ข้อจํากัด E2:J2<=E4:J4 ทําให้มั่นใจได้ว่าในแต่ละปี ทุนและแรงงานที่ใช้น้อยกว่าหรือเท่ากับทุนและแรงงานที่มีอยู่ เมื่อต้องการเพิ่มข้อจํากัดที่ทําให้เซลล์ที่เปลี่ยนแปลงเป็นเลขฐานสอง ฉันจะคลิก เพิ่ม ในกล่องโต้ตอบ Solver Parameters แล้วเลือก Bin จากรายการตรงกลางของกล่องโต้ตอบ กล่องโต้ตอบ เพิ่มข้อจํากัด ควรปรากฏขึ้นดังที่แสดงในรูปที่ 30-3
แบบจําลองของเราเป็นแบบเชิงเส้น เนื่องจากเซลล์เป้าหมายถูกคํานวณเป็นผลรวมของพจน์ที่มีรูปแบบ (เซลล์ที่เปลี่ยน)*(ค่าคงที่) และเนื่องจากข้อจํากัดการใช้ทรัพยากรถูกคํานวณโดยการเปรียบเทียบผลรวมของ (เซลล์ที่เปลี่ยน)*(ค่าคงที่) กับค่าคงที่
เมื่อกรอกกล่องโต้ตอบ Solver Parameters แล้ว ให้คลิก Solve และเราจะได้ผลลัพธ์ที่แสดงก่อนหน้านี้ในรูปที่ 30-1 บริษัทสามารถรับ NPV สูงสุด 9,293 ล้านดอลลาร์ (9.293 พันล้านดอลลาร์) โดยเลือกโครงการ 2, 3, 6–10, 14–16, 19 และ 20
การจัดการข้อจํากัดอื่นๆ
บางครั้งโมเดลการเลือกโครงการมีข้อจํากัดอื่นๆ ตัวอย่างเช่น สมมติว่าถ้าเราเลือก Project 3 เราก็ต้องเลือก Project 4 ด้วย เนื่องจากโซลูชันที่เหมาะสมที่สุดในปัจจุบันของเราเลือก Project 3 แต่ไม่ใช่ Project 4 เราจึงทราบว่าโซลูชันปัจจุบันของเราไม่สามารถคงไว้ในตัวเลือกที่ดีที่สุดได้ เมื่อต้องการแก้ปัญหานี้ เพียงแค่เพิ่มข้อจํากัดที่เซลล์เปลี่ยนเลขฐานสองสําหรับโครงการ 3 มีค่าน้อยกว่าหรือเท่ากับเซลล์การเปลี่ยนแปลงเลขฐานสองสําหรับโครงการ 4
คุณสามารถดูตัวอย่างนี้ได้บนเวิร์กชีต If 3 แล้ว 4 ในไฟล์ Capbudget.xlsx ซึ่งแสดงในรูปที่ 30-4 เซลล์ L9 อ้างอิงถึงค่าไบนารีที่เกี่ยวข้องกับโครงการ 3 และเซลล์ L12 เป็นค่าไบนารีที่เกี่ยวข้องกับโครงการ 4 เมื่อเพิ่มข้อจํากัด L9<=L12 ถ้าเราเลือก Project 3 แล้ว L9 จะเท่ากับ 1 และข้อจํากัดของเราบังคับให้ L12 (ไบนารี Project 4) เท่ากับ 1 ข้อจํากัดของเรายังต้องปล่อยค่าฐานสองในเซลล์ที่เปลี่ยนแปลงของ Project 4 ไม่จํากัด ถ้าเราไม่ได้เลือก Project 3 ถ้าเราไม่เลือก Project 3 แล้ว L9 จะเท่ากับ 0 และข้อจํากัดของเราอนุญาตให้เลขฐานสอง Project 4 เท่ากับ 0 หรือ 1 ซึ่งเป็นสิ่งที่เราต้องการ โซลูชันใหม่ที่เหมาะสมที่สุดแสดงในรูปที่ 30-4
ผลลัพธ์ใหม่ที่เหมาะสมที่สุดจะถูกคํานวณ ถ้าการเลือก Project 3 หมายความว่าเราต้องเลือก Project 4 ด้วย สมมติว่าเราสามารถทําได้เพียงสี่โครงการระหว่างโครงการ 1 ถึง 10 (ดูเวิร์กชีต At Most 4 Of P1–P10 ที่แสดงในรูปที่ 30-5) ในเซลล์ L8 เราจะคํานวณผลรวมของค่าเลขฐานสองที่เชื่อมโยงกับโครงการ 1 ถึง 10 ด้วยสูตร SUM(A6:A15) จากนั้น เราจะเพิ่มข้อจํากัด L8<=L10 ซึ่งรับรองว่าสูงสุด 4 จาก 10 โครงการแรกจะถูกเลือก โซลูชันใหม่ที่เหมาะสมที่สุดแสดงในรูปที่ 30-5 NPV ลดลงเหลือ 9.014 พันล้านดอลลาร์
การแก้ปัญหาโปรแกรมไบนารีและจํานวนเต็ม
แบบจําลอง Linear Solver ซึ่งเซลล์ที่เปลี่ยนแปลงบางเซลล์หรือทั้งหมดต้องการให้เป็นเลขฐานสองหรือจํานวนเต็มมักจะแก้ได้ยากกว่าแบบจําลองเชิงเส้นซึ่งเซลล์ที่เปลี่ยนแปลงทั้งหมดได้รับอนุญาตให้เป็นเศษส่วน ด้วยเหตุนี้เราจึงมักจะพอใจกับวิธีแก้ปัญหาที่ใกล้เคียงที่สุดสําหรับปัญหาการเขียนโปรแกรมไบนารีหรือจํานวนเต็ม ถ้าโมเดล Solver ของคุณทํางานเป็นเวลานาน คุณอาจต้องการพิจารณาปรับการตั้งค่า ความคลาดเคลื่อน ในกล่องโต้ตอบ ตัวเลือกของ Solver (ดูรูปที่ 30-6) ตัวอย่างเช่น การตั้งค่าความคลาดเคลื่อนเป็น 0.5% หมายความว่า Solver จะหยุดในครั้งแรกที่พบคําตอบที่เป็นไปได้ ซึ่งอยู่ภายใน 0.5 เปอร์เซ็นต์ของค่าเซลล์เป้าหมายที่ดีที่สุดทางทฤษฎี (ค่าเซลล์เป้าหมายที่ดีที่สุดทางทฤษฎีคือค่าเป้าหมายที่เหมาะสมที่สุดที่พบเมื่อข้อจํากัดเลขฐานสองและจํานวนเต็มถูกละเว้น) บ่อยครั้งที่เราต้องเผชิญกับทางเลือกระหว่างการหาคําตอบภายใน 10 เปอร์เซ็นต์ของค่าที่เหมาะสมใน 10 นาที หรือการค้นหาโซลูชันที่เหมาะสมในเวลาสองสัปดาห์ของเวลาที่ใช้คอมพิวเตอร์! ค่าความคลาดเคลื่อนเริ่มต้นคือ 0.05% ซึ่งหมายความว่า Solver จะหยุดทํางานเมื่อพบค่าเซลล์เป้าหมายภายใน 0.05 เปอร์เซ็นต์ของค่าเซลล์เป้าหมายที่เหมาะสมที่สุดทางทฤษฎี
ปัญหา
- บริษัทหนึ่งมีเก้าโครงการที่อยู่ระหว่างการพิจารณา NPV ที่เพิ่มโดยแต่ละโครงการและเงินทุนที่แต่ละโครงการต้องการในช่วงสองปีข้างหน้าแสดงอยู่ในตารางต่อไปนี้ (ตัวเลขทั้งหมดเป็นล้าน) ตัวอย่างเช่น โครงการ 1 จะเพิ่ม NPV 14 ล้านดอลลาร์ และต้องใช้จ่าย 12 ล้านดอลลาร์ในระหว่างปีที่ 1 และ 3 ล้านดอลลาร์ในระหว่างปีที่ 2 ในระหว่างปีที่ 1 มีเงินทุน 50 ล้านดอลลาร์สําหรับโครงการ และ 20 ล้านดอลลาร์พร้อมใช้งานในช่วงปีที่ 2
| NPV | รายจ่ายของปีที่ 1 | รายจ่ายของปีที่ 2 | |
|---|---|---|---|
| โครงการ 1 | 14 | 1.2 | 3 |
| โครงการ 2 | 17 | 54 | 7 |
| โครงการ 3 | 17 | 6 | 6 |
| โครงการ 4 | 15 | 6 | 2 |
| โครงการ 5 | 40 | 30 | 35 |
| โครงการ 6 | 1.2 | 6 | 6 |
| โครงการ 7 | 14 | 48 | 4 |
| โครงการ 8 | 10 | 36 | 3 |
| โครงการ 9 | 1.2 | 18 | 3 |
- หากเราไม่สามารถดําเนินการเพียงเศษเสี้ยวของโครงการ แต่ต้องดําเนินการโครงการทั้งหมดหรือไม่ดําเนินการเลย เราจะเพิ่ม NPV ให้สูงสุดได้อย่างไร
- สมมติว่าถ้าดําเนินโครงการ 4 โครงการ 5 จะต้องดําเนินการ เราจะเพิ่ม NPV ได้อย่างไร
บริษัทสํานักพิมพ์กําลังพยายามพิจารณาว่าหนังสือเล่มใดจาก 36 เล่มที่ควรตีพิมพ์ในปีนี้ ไฟล์ Pressdata.xlsx ให้ข้อมูลต่อไปนี้เกี่ยวกับหนังสือแต่ละเล่ม:
- รายได้และต้นทุนการพัฒนาที่คาดการณ์ไว้ (นับพันดอลลาร์)
- หน้าในหนังสือแต่ละเล่ม
- หนังสือเล่มนี้มุ่งเป้าไปที่ผู้ชมของนักพัฒนาซอฟต์แวร์หรือไม่ (ระบุด้วย 1 ในคอลัมน์ E)
บริษัทสํานักพิมพ์สามารถตีพิมพ์หนังสือรวมได้ถึง 8500 หน้าในปีนี้ และต้องตีพิมพ์หนังสืออย่างน้อยสี่เล่มที่มุ่งเป้าไปที่นักพัฒนาซอฟต์แวร์ บริษัทจะทํากําไรสูงสุดได้อย่างไร?
เกี่ยวกับบทความ
บทความนี้ดัดแปลงมาจาก Microsoft Office Excel 2007: Data Analysis and Business Modeling โดย Wayne L. Winston
หนังสือสไตล์ห้องเรียนเล่มนี้ได้รับการพัฒนาจากชุดการนําเสนอโดย Wayne Winston นักสถิติและศาสตราจารย์ด้านธุรกิจที่มีชื่อเสียงซึ่งเชี่ยวชาญด้านการประยุกต์ใช้ Excel อย่างสร้างสรรค์และใช้งานได้จริง