ข้ามไปที่เนื้อหาหลัก
Office

การใช้ Solver สำหรับการจัดทำงบประมาณ

หมายเหตุ: เราต้องการมอบเนื้อหาวิธีใช้ปัจจุบันในภาษาของคุณให้กับคุณโดยเร็วที่สุดเท่าที่เราจะทำได้ หน้านี้ได้รับการแปลด้วยระบบอัตโนมัติ และอาจมีข้อผิดพลาดทางไวยากรณ์หรือความไม่ถูกต้อง จุดประสงค์ของเราคือเพื่อให้เนื้อหานี้มีประโยชน์กับคุณ คุณแจ้งให้เราทราบว่าข้อมูลดังกล่าวมีประโยชน์ต่อคุณที่ด้านล่างของหน้านี้ได้หรือไม่ นี่คือบทความภาษาอังกฤษเพื่อให้ง่ายต่อการอ้างอิง

บริษัทจะใช้ Solver เพื่อกำหนดว่าโครงการใดที่ควรดำเนินการอย่างไร

ในแต่ละปีบริษัทเช่นเอลีลิลลี่จำเป็นต้องระบุว่ายาเสพติดใดที่จะพัฒนา บริษัทเช่นไมโครซอฟท์ซึ่งเป็นโปรแกรมซอฟต์แวร์ที่จะพัฒนา บริษัทเช่นคอย & การพนันซึ่งเป็นผลิตภัณฑ์สำหรับผู้บริโภคใหม่ในการพัฒนา ฟีเจอร์ Solver ใน Excel สามารถช่วยให้บริษัททำการตัดสินใจเหล่านี้ได้

บริษัทส่วนใหญ่ต้องการดำเนินโครงการที่สนับสนุนมูลค่าปัจจุบันสุทธิที่ยิ่งใหญ่ที่สุด (NPV) ขึ้นอยู่กับทรัพยากรที่จำกัด (โดยปกติแล้วจะเป็นทุนและแรงงาน) สมมติว่าบริษัทการพัฒนาซอฟต์แวร์กำลังพยายามตรวจสอบว่ามีโครงการซอฟต์แวร์ใดบ้างที่20โครงการควรดำเนินการ NPV (ล้านดอลลาร์) ที่มีส่วนร่วมในแต่ละโครงการเช่นเดียวกับทุน (ล้านดอลลาร์) และจำนวนของโปรแกรมเมอร์ที่จำเป็นในระหว่างสามปีถัดไปจะได้รับบนเวิร์กชีตรูปแบบพื้นฐานในไฟล์ Capbudget ซึ่งเป็น แสดงในรูป30-1 ในหน้าถัดไป ตัวอย่างเช่น Project 2 ผลตอบแทน $๙๐๘,๐๐๐,๐๐๐ จำเป็นต้องมี $๑๕๑,๐๐๐,๐๐๐ในระหว่างปี 1, $๒๖๙,๐๐๐,๐๐๐ระหว่างปี2และ $๒๔๘,๐๐๐,๐๐๐ในระหว่างปี3 Project 2 ต้องการโปรแกรมเมอร์๑๓๙ในระหว่างปี 1, ๘๖โปรแกรมเมอร์ระหว่างปี2และ๘๓โปรแกรมเมอร์ในระหว่างปี3 เซลล์ E4: G4 แสดงเมืองหลวง (ล้านดอลลาร์) ที่พร้อมใช้งานในระหว่างสามปีและเซลล์ H4: J4 จะระบุจำนวนของโปรแกรมเมอร์ที่พร้อมใช้งาน ตัวอย่างเช่นในระหว่างปี1ขึ้นอยู่กับ $๒,๕๐๐,๐๐๐,๐๐๐ในตัวโปรแกรมเมอร์ทุนและ๙๐๐จะพร้อมใช้งาน

บริษัทต้องตัดสินใจว่าควรดำเนินการแต่ละโครงการหรือไม่ สมมติว่าเราไม่สามารถดำเนินการเศษส่วนของโครงการซอฟต์แวร์ได้ ถ้าเราจัดสรร๐.๕ของทรัพยากรที่จำเป็นตัวอย่างเช่นเราจะมีโปรแกรมที่ไม่ทำงานที่จะนำรายได้ $๐ให้กับเรา!

เคล็ดลับในสถานการณ์การสร้างตัวแบบที่คุณต้องทำหรือไม่ทำบางอย่างคือการใช้การเปลี่ยนเซลล์แบบไบนารี เซลล์ที่มีการเปลี่ยนแปลงไบนารีจะเท่ากับ0หรือ1 เมื่อมีการเปลี่ยนเซลล์แบบไบนารีที่สอดคล้องกับโครงการเท่ากับ1เราจะทำโครงการ ถ้าการเปลี่ยนเซลล์แบบไบนารีที่สอดคล้องกับโครงการจะเท่ากับ0เราจะไม่ทำโครงการ คุณตั้งค่า Solver ให้ใช้ช่วงของเซลล์ที่มีการเปลี่ยนแปลงแบบไบนารีโดยการเพิ่มข้อจำกัดให้เลือกเซลล์ที่เปลี่ยนแปลงที่คุณต้องการใช้แล้วเลือก Bin จากรายการในกล่องโต้ตอบเพิ่มข้อจำกัด

รูปหนังสือ

ด้วยพื้นหลังนี้เราพร้อมที่จะแก้ไขปัญหาการเลือกโครงการของซอฟต์แวร์แล้ว เช่นเดียวกับตัวแบบ Solver เสมอเราจะเริ่มต้นด้วยการระบุเซลล์เป้าหมายของเราการเปลี่ยนเซลล์และข้อจำกัด

  • เซลล์เป้าหมาย เราเพิ่มประสิทธิภาพของ NPV ที่สร้างขึ้นโดยโครงการที่เลือก

  • การเปลี่ยนเซลล์เราค้นหาเซลล์ที่เปลี่ยนแปลงเป็น0หรือ1แบบไบนารีสำหรับแต่ละโครงการ ฉันได้ตั้งค่าเซลล์เหล่านี้ไว้ในช่วง A6:25 ซม. (และชื่อช่วงdoit) ตัวอย่างเช่น1ในเซลล์ A6 ระบุว่าเราได้ดำเนินการโครงการ1 0ในเซลล์ C6 ระบุว่าเราไม่ได้ดำเนินการโครงการ1

  • จำกัด .เราจำเป็นต้องตรวจสอบให้แน่ใจว่าสำหรับแต่ละปีt (t = 1, 2, 3), ปีtทุนที่ใช้มีค่าน้อยกว่าหรือเท่ากับปีtเงินทุนที่ใช้งานได้และปีที่ใช้แรงงานน้อยกว่าหรือเท่ากับปีt

ในขณะที่คุณสามารถดูแผ่นงานของเราต้องคำนวณสำหรับการเลือกโครงการใดๆ NPV ทุนที่ใช้เป็นรายปีและโปรแกรมเมอร์ที่ใช้ในแต่ละปี ในเซลล์ B2 ฉันใช้สูตรSUMPRODUCT (doit, NPV)เพื่อคำนวณผลรวม NPV ที่สร้างขึ้นโดยโครงการที่เลือก (ชื่อช่วงNPVอ้างอิงถึงช่วง C6: C25) สำหรับทุกโครงการที่มี1ในคอลัมน์ a สูตรนี้จะรับ NPV ของโครงการและสำหรับทุกโครงการที่มี0ในคอลัมน์ a สูตรนี้จะไม่ได้รับการ NPV ของโครงการ ดังนั้นเราจึงสามารถคำนวณ NPV ของโครงการทั้งหมดและเซลล์เป้าหมายของเราเป็นเส้นตรงเนื่องจากมีการคำนวณโดยการรวมคำที่เป็นไปตามฟอร์ม(เปลี่ยนเซลล์) * (ค่าคงที่) ในแฟชั่นที่คล้ายกันฉันคำนวณทุนที่ใช้ในแต่ละปีและแรงงานที่ใช้ในแต่ละปีโดยการคัดลอกจาก E2 เป็น F2: SUMPRODUCT สูตร (doit,: E25)

ตอนนี้ฉันกรอกข้อมูลลงในกล่องโต้ตอบพารามิเตอร์ของ Solver ตามที่แสดงในรูป30-2

รูปหนังสือ

เป้าหมายของเราคือการเพิ่มประสิทธิภาพการ NPV ของโครงการที่เลือก (เซลล์ B2) เซลล์ที่มีการเปลี่ยนแปลงของเรา (ช่วงที่ชื่อdoit) เป็นเซลล์แบบไบนารีที่มีการเปลี่ยนแปลงสำหรับแต่ละโครงการ ข้อจำกัดE2: J2< = E4: J4เพื่อให้แน่ใจว่าในแต่ละปีเงินทุนและแรงงานที่ใช้มีค่าน้อยกว่าหรือเท่ากับเงินทุนและแรงงานที่พร้อมใช้งาน เมื่อต้องการเพิ่มข้อจำกัดที่ทำให้การเปลี่ยนแปลงเซลล์ไบนารีฉันคลิกเพิ่มในกล่องโต้ตอบพารามิเตอร์ของ Solver แล้วเลือก Bin จากรายการในกล่องโต้ตอบตรงกลางของกล่องโต้ตอบ กล่องโต้ตอบเพิ่มข้อจำกัดควรปรากฏขึ้นตามที่แสดงในรูป30-3

รูปหนังสือ

รูปแบบของเราเป็นเส้นตรงเนื่องจากเซลล์เป้าหมายถูกคำนวณเป็นผลรวมของคำที่มีฟอร์ม(เปลี่ยนเซลล์) * (ค่าคงที่)และเนื่องจากข้อจำกัดการใช้ทรัพยากรจะคำนวณโดยเปรียบเทียบผลรวมของ(การเปลี่ยนเซลล์) * (ค่าคงที่)เป็นค่าคงที่

เมื่อกล่องโต้ตอบพารามิเตอร์ของ Solver กรอกข้อมูลให้คลิกแก้ไขและเรามีผลลัพธ์ที่แสดงไว้ก่อนหน้านี้ในรูป30-1 บริษัทสามารถขอรับ NPV ได้สูงสุดของ $๙,๒๙๓,๐๐๐,๐๐๐ ($๙,๒๙๓,๐๐๐,๐๐๐) โดยการเลือกโครงการ 2, 3, 6 – 10, 14 – 16, 19 และ20

บางครั้งโครงการ-รูปแบบการเลือกมีข้อจำกัดอื่นๆ ตัวอย่างเช่นสมมติว่าถ้าเราเลือก project 3 เราจะต้องเลือก project 4 เนื่องจากโซลูชันที่ดีที่สุดในปัจจุบันของเราเลือก project 3 แต่ไม่ใช่โครงการ4เราจะทราบว่าโซลูชันปัจจุบันของเราไม่สามารถอยู่ได้อย่างดีที่สุด เมื่อต้องการแก้ไขปัญหานี้เพียงแค่เพิ่มข้อจำกัดที่การเปลี่ยนเซลล์แบบไบนารีสำหรับ project 3 มีค่าน้อยกว่าหรือเท่ากับเซลล์ที่มีการเปลี่ยนแปลงไบนารีสำหรับ project 4

คุณสามารถค้นหาตัวอย่างนี้ได้บนเวิร์กชีตIf 3 แล้ว 4แผ่นในไฟล์ Capbudget ที่แสดงในรูป30-4 L9 เซลล์อ้างอิงถึงค่าไบนารีที่เกี่ยวข้องกับ project 3 และ L12 เซลล์ไปยังค่าไบนารีที่เกี่ยวข้องกับ project 4 โดยการเพิ่ม L9< ข้อจำกัด= L12ถ้าเราเลือก Project 3, L9 เท่ากับ1และการบังคับใช้ข้อจำกัดของเรา L12 (Project 4 binary) เท่ากับ1 ข้อจำกัดของเราจะต้องปล่อยให้ค่าไบนารีในเซลล์ที่เปลี่ยนแปลงของโครงการ4ไม่จำกัดถ้าเราไม่ได้เลือก project 3 ถ้าเราไม่ได้เลือก Project 3, L9 เท่ากับ0และข้อจำกัดของเราจะช่วยให้ Project 4 ไบนารีเท่ากับ0หรือ1ซึ่งเป็นสิ่งที่เราต้องการ โซลูชันที่ดีที่สุดใหม่จะแสดงในรูป30-4

รูปหนังสือ

โซลูชันที่ดีที่สุดใหม่จะได้รับการคำนวณถ้าเลือก project 3 หมายความว่าเรายังต้องเลือก project 4 ในตอนนี้สมมติว่าเราสามารถทำได้เพียงสี่โครงการจากระหว่างโครงการ1ถึง10 (ดูที่ส่วนใหญ่4ของ P1 – P10 เวิร์กชีตที่แสดงในรูป 30-5) ในเซลล์ L8 เราคำนวณผลรวมของค่าไบนารีที่เกี่ยวข้องกับโครงการ1ถึง10ด้วยผลรวมสูตร (A6: A15) จากนั้นเราจะเพิ่มข้อจำกัดL8< = L10ซึ่งจะทำให้มั่นใจได้ว่าส่วนใหญ่, 4 ของโครงการ10ครั้งแรกจะถูกเลือกไว้ โซลูชันที่ดีที่สุดใหม่จะแสดงในรูป30-5 NPV ได้ลดลงไปยัง $๙,๐๑๔,๐๐๐,๐๐๐

รูปหนังสือ

รูปแบบเชิงเส้นเชิงเส้นในเซลล์ที่เปลี่ยนแปลงบางส่วนหรือทั้งหมดจำเป็นต้องเป็นแบบไบนารีหรือจำนวนเต็มจะยากต่อการแก้ปัญหามากกว่ารูปแบบเชิงเส้นซึ่งเซลล์ที่เปลี่ยนแปลงทั้งหมดจะได้รับอนุญาตให้มีเศษส่วน ด้วยเหตุนี้เรามักจะพอใจกับโซลูชันที่ใกล้เคียงที่สุดสำหรับปัญหาการเขียนโปรแกรมแบบไบนารีหรือจำนวนเต็ม ถ้าตัวแบบ Solver ของคุณทำงานเป็นเวลานานคุณอาจต้องการพิจารณาปรับการตั้งค่าความอดทนในกล่องโต้ตอบตัวเลือกของ solver (ดูรูป 30-6) ตัวอย่างเช่นการตั้งค่าค่าเผื่อของ๐.๕% หมายความว่า Solver จะหยุดในครั้งแรกที่ค้นหาโซลูชันที่เป็นไปได้ที่อยู่ภายใน๐.๕เปอร์เซ็นต์ของค่าเซลล์เป้าหมายที่เหมาะสมที่สุดของทฤษฎี (ค่าเซลล์เป้าหมายที่เหมาะสมที่สุดคือค่าเป้าหมายที่ดีที่สุดคือค่าเป้าหมายที่ดีที่สุดที่พบเมื่อ ข้อจำกัดแบบไบนารีและจำนวนเต็มจะถูกละเว้น) บ่อยครั้งที่เรากำลังเผชิญกับตัวเลือกระหว่างการค้นหาคำตอบภายใน10เปอร์เซ็นต์ของที่ดีที่สุดใน10นาทีหรือการค้นหาโซลูชันที่ดีที่สุดในสองสัปดาห์ของเวลาที่ใช้คอมพิวเตอร์! ค่าความอดทนเริ่มต้นคือ๐.๐๕% ซึ่งหมายความว่า Solver หยุดเมื่อพบค่าของเซลล์เป้าหมายภายใน๐.๐๕เปอร์เซ็นต์ของค่าเซลล์เป้าหมายที่เหมาะสมที่สุด

รูปหนังสือ

  1. 1. บริษัทมีโครงการ9โครงการภายใต้การพิจารณา NPV ที่เพิ่มเข้ามาโดยแต่ละโครงการและทุนที่จำเป็นในแต่ละโครงการในระหว่างสองปีถัดไปจะแสดงในตารางต่อไปนี้ (ตัวเลขทั้งหมดอยู่ในหน่วยล้าน) ตัวอย่างเช่น Project 1 จะเพิ่ม $๑๔,๐๐๐,๐๐๐ใน NPV และต้องใช้ค่าใช้จ่ายของ $๑๒,๐๐๐,๐๐๐ในระหว่างปี1และ $๓,๐๐๐,๐๐๐ในระหว่างปีที่2 ในระหว่างปี 1, $๕๐,๐๐๐,๐๐๐ในทุนจะพร้อมใช้งานสำหรับโครงการและ $๒๐,๐๐๐,๐๐๐จะพร้อมใช้งานในระหว่างปี2

NPV

รายจ่ายปีที่1

รายจ่ายปีที่2

Project 1

14

1.2

3

Project 2

17

๕๔

7

Project 3

17

6

6

Project 4

15

6

2

Project 5

๔๐

30

๓๕

Project 6

1.2

6

6

Project 7

14

๔๘

4

Project 8

10

๓๖

3

Project 9

1.2

18

3

  • ถ้าเราไม่สามารถดำเนินการเศษส่วนของโครงการแต่ต้องดำเนินการอย่างใดอย่างหนึ่งหรือไม่ทั้งหมดของโครงการเราจะเพิ่มประสิทธิภาพในการ NPV ได้อย่างไร

  • สมมติว่าถ้า project 4 ดำเนินการแล้วโครงการ5จะต้องถูกดำเนินการ เราจะเพิ่มประสิทธิภาพการ NPV ได้อย่างไร

  • บริษัทที่เผยแพร่กำลังพยายามตรวจสอบว่ามีหนังสือใดบ้างที่๓๖ควรประกาศในปีนี้ ไฟล์ Pressdata จะให้ข้อมูลต่อไปนี้เกี่ยวกับหนังสือแต่ละเล่มดังต่อไปนี้

    • รายได้และต้นทุนการพัฒนาที่คาดการณ์ (เป็นพันๆดอลลาร์)

    • หน้าในหนังสือแต่ละเล่ม

    • ไม่ว่าหนังสือจะมุ่งไปที่ผู้ชมของนักพัฒนาซอฟต์แวร์ (ที่ระบุโดย1ในคอลัมน์ E)

      บริษัทที่เผยแพร่สามารถประกาศหนังสือได้สูงสุดถึง๘๕๐๐หน้าในปีนี้และต้องเผยแพร่อย่างน้อยสี่หนังสือที่มุ่งไปที่นักพัฒนาซอฟต์แวร์ บริษัทสามารถเพิ่มกำไรได้อย่างไร

บทความนี้ได้รับการดัดแปลงจากMicrosoft Office Excel ๒๐๐๗การวิเคราะห์ข้อมูลและการสร้างตัวแบบธุรกิจโดยเวย์น l. วินสตัน

หนังสือสไตล์ห้องเรียนนี้ได้รับการพัฒนาจากชุดของงานนำเสนอโดยเวย์นวินสตันซึ่งเป็นที่รู้จักกันดีแสดงและนักวิชาการทางธุรกิจที่มีความเชี่ยวชาญในการสร้างสรรค์การใช้งานของ Excel

ขยายทักษะ Office ของคุณ
สำรวจการฝึกอบรม
รับฟีเจอร์ใหม่ก่อนใคร
เข้าร่วม Office Insider

ข้อมูลนี้เป็นประโยชน์หรือไม่

ขอบคุณสำหรับคำติชมของคุณ!

ขอขอบคุณสำหรับคำติชมของคุณ! เราคิดว่าอาจเป็นประโยชน์ที่จะให้คุณได้ติดต่อกับหนึ่งในตัวแทนฝ่ายสนับสนุน Office ของเรา

×