การใช้ Solver เพื่อตรวจสอบการผสมผลิตภัณฑ์ที่ดีที่สุด

ฉันจะระบุการผสมผสานของผลิตภัณฑ์รายเดือนที่เพิ่มผลกำไรได้อย่างไร

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

• การผสมผลิตภัณฑ์ไม่สามารถใช้ทรัพยากรเพิ่มเติมได้มากกว่าที่พร้อมใช้งาน

• มีความต้องการที่จำกัดสำหรับแต่ละผลิตภัณฑ์ เราไม่สามารถผลิตสินค้าได้มากขึ้นในระหว่างเดือนที่ไม่ใช่คำสั่งเนื่องจากการผลิตส่วนเกินจะสูญเสียไป (ตัวอย่างเช่นยาเสพติดที่เน่าเปื่อย)

ตอนนี้ลองแก้ไขตัวอย่างต่อไปนี้ของปัญหาการผสมผลิตภัณฑ์ คุณสามารถค้นหาวิธีแก้ไขปัญหานี้ในไฟล์ Prodmix ที่แสดงในรูป27-1

สมมติว่าเราทำงานกับบริษัทยาเสพติดที่ผลิตผลิตภัณฑ์ที่แตกต่างกันหกชนิดในโรงงานของพวกเขา การผลิตผลิตภัณฑ์แต่ละรายการจำเป็นต้องใช้แรงงานและวัตถุดิบ แถว4ในรูป27-1 แสดงจำนวนชั่วโมงของแรงงานที่จำเป็นในการผลิตปอนด์ของแต่ละผลิตภัณฑ์และแถว5แสดง£ของวัตถุดิบที่จำเป็นในการผลิตปอนด์ของแต่ละผลิตภัณฑ์ ตัวอย่างเช่นการผลิตปอนด์ของผลิตภัณฑ์1จำเป็นต้องใช้แรงงาน6ชั่วโมงและ£๓.๒ของวัตถุดิบ สำหรับยาแต่ละรายการราคาต่อปอนด์จะได้รับในแถวที่6ต้นทุนต่อหน่วยต่อปอนด์จะถูกกำหนดให้อยู่ในแถวที่7และผลกำไรต่อปอนด์จะถูกกำหนดให้ในแถวที่9 ตัวอย่างเช่นผลิตภัณฑ์2ขายสำหรับ $๑๑.๐๐ต่อปอนด์ค่าใช้จ่ายต่อหน่วยของ $๕.๗๐ต่อปอนด์และก่อให้เกิด $๕.๓๐กำไรต่อปอนด์ ความต้องการของเดือนสำหรับแต่ละยาเสพติดจะได้รับในแถวที่8 ตัวอย่างเช่นความต้องการของผลิตภัณฑ์3คือ๑๐๔๑ปอนด์ เดือนนี้๔๕๐๐ชั่วโมงของแรงงานและ£๑๖๐๐ของวัตถุดิบที่พร้อมใช้งาน บริษัทนี้สามารถเพิ่มกำไรรายเดือนได้อย่างไร

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

กุญแจสำคัญในการแก้ไขปัญหาการผสมผลิตภัณฑ์คือการคำนวณการใช้ทรัพยากรและกำไรที่เกี่ยวข้องกับการผสมผสานผลิตภัณฑ์ใดๆที่ได้รับอย่างมีประสิทธิภาพ เครื่องมือที่สำคัญที่เราสามารถใช้เพื่อทำการคำนวณนี้คือฟังก์ชัน SUMPRODUCT ฟังก์ชัน SUMPRODUCT จะคูณค่าที่สอดคล้องกันในช่วงของเซลล์และจะส่งกลับผลรวมของค่าเหล่านั้น ช่วงของเซลล์แต่ละช่วงที่ใช้ในการประเมิน SUMPRODUCT ต้องมีขนาดเดียวกันซึ่งหมายความว่าคุณสามารถใช้ SUMPRODUCT กับสองแถวหรือสองคอลัมน์ได้แต่ไม่มีคอลัมน์หนึ่งคอลัมน์และหนึ่งแถว

เป็นตัวอย่างของวิธีการที่เราสามารถใช้ฟังก์ชัน SUMPRODUCT ในตัวอย่างการผสมผสานผลิตภัณฑ์ของเรามาลองคำนวณการใช้งานทรัพยากรของเรา การใช้แรงงานของเราถูกคำนวณโดย

(แรงงานที่ใช้ต่อปอนด์ของยาเสพติด 1) * (ยา1ปอนด์ที่ผลิต) +
(แรงงานที่ใช้ต่อปอนด์ของยาเสพติด 2) * (ยาเสพติด2ปอนด์ที่ผลิต) + ...
(แรงงานที่ใช้ต่อปอนด์ของยาเสพติด 6) * (ยา6ปอนด์ที่ผลิต)

เราสามารถคำนวณการใช้แรงงานในแฟชั่นที่น่าเบื่อมากขึ้นเป็นD2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * ส่ง ในทำนองเดียวกันการใช้วัตถุดิบอาจได้รับการคำนวณเป็นD2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5 อย่างไรก็ตามการใส่สูตรเหล่านี้ในเวิร์กชีตสำหรับหกผลิตภัณฑ์คือการใช้เวลานาน ลองนึกถึงระยะเวลาที่จะใช้ถ้าคุณกำลังทำงานกับบริษัทที่ผลิตตัวอย่างเช่นผลิตภัณฑ์๕๐ที่โรงงานของพวกเขา วิธีที่ง่ายยิ่งขึ้นในการคำนวณการใช้แรงงานและวัตถุดิบคือการคัดลอกจาก D14 เป็นSUMPRODUCT สูตร ($D $๒: $I $ 2, D4: ส่ง) สูตรนี้จะคำนวณD2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * ส่ง(ซึ่งเป็นการใช้แรงงานของเรา) แต่จะง่ายต่อการเข้าถึงมาก! โปรดสังเกตว่าฉันใช้เครื่องหมาย $ ที่มีช่วง D2: I2 เพื่อให้เมื่อฉันคัดลอกสูตรที่ฉันยังคงจับภาพผลิตภัณฑ์จากแถวที่2 สูตรในเซลล์ที่ใช้ในการคำนวณการใช้วัตถุดิบ

ในแฟชั่นที่คล้ายกันกำไรของเราจะถูกกำหนดโดย

(ยาเสพติด1กำไรต่อปอนด์) * (ยา1ปอนด์ที่ผลิต) +
(ยาเสพติด2กำไรต่อปอนด์) * (ยาเสพติด2ปอนด์ที่ผลิต) + ...
(ยาเสพติด6กำไรต่อปอนด์) * (ยา6ปอนด์ที่ผลิต)

กำไรจะได้รับการคำนวณอย่างง่ายดายในเซลล์แบบ mb-d12 ด้วยสูตรSUMPRODUCT (D9: I9, $D $๒: $I $๒)

ขณะนี้เราสามารถระบุองค์ประกอบสามประการของตัวแบบการผสมผสานผลิตภัณฑ์ของเรา

• เซลล์เป้าหมายเป้าหมายของเราคือการเพิ่มกำไร (จากการคำนวณในเซลล์ mb-d12)

• การเปลี่ยนเซลล์จำนวนปอนด์ที่ผลิตของแต่ละผลิตภัณฑ์ (แสดงอยู่ในช่วงของเซลล์ D2: I2)

• จำกัด . เรามีข้อจำกัดดังต่อไปนี้:

  • อย่าใช้แรงงานหรือวัตถุดิบมากกว่าที่พร้อมใช้งาน นั่นคือค่าในเซลล์ D14: (ทรัพยากรที่ใช้) ต้องมีค่าน้อยกว่าหรือเท่ากับค่าในเซลล์ F14: F15 (ทรัพยากรที่พร้อมใช้งาน)

  • ห้ามผลิตยาเสพติดมากกว่าที่อยู่ในความต้องการ นั่นคือค่าในเซลล์ D2: I2 (ปอนด์ที่ผลิตจากยาแต่ละรายการ) จะต้องน้อยกว่าหรือเท่ากับความต้องการของยาแต่ละรายการ (ที่แสดงอยู่ในเซลล์ D8: I8)

  • เราไม่สามารถผลิตจำนวนลบของยาเสพติดใดๆได้

ฉันจะแสดงให้คุณเห็นวิธีการใส่เซลล์เป้าหมายการเปลี่ยนเซลล์และข้อจำกัดเป็น Solver จากนั้นทั้งหมดที่คุณต้องทำคือการคลิกปุ่มแก้ไขเพื่อค้นหาการผสมผสานผลิตภัณฑ์ที่มีกำไร

เมื่อต้องการเริ่มต้นให้คลิกแท็บข้อมูลและในกลุ่มการวิเคราะห์ให้คลิก Solver

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

คลิกกล่องตั้งค่าเซลล์เป้าหมายแล้วเลือกเซลล์กำไรของเรา (เซลล์ mb-d12) คลิกกล่องโดยการเปลี่ยนเซลล์แล้วชี้ไปที่ช่วง D2: I2 ซึ่งประกอบด้วย£ที่ผลิตจากยาเสพติดแต่ละรายการ ในตอนนี้กล่องโต้ตอบควรมีรูปภาพ27-3

ขณะนี้เราพร้อมที่จะเพิ่มข้อจำกัดให้กับตัวแบบแล้ว คลิกปุ่มเพิ่ม คุณจะเห็นกล่องโต้ตอบเพิ่มข้อจำกัดแสดงในรูป27-4

เมื่อต้องการเพิ่มข้อจำกัดการใช้งานทรัพยากรให้คลิกกล่องการอ้างอิงเซลล์แล้วเลือกช่วง D14: เลือก < = จากรายชื่อกลาง คลิกกล่องข้อจำกัดจากนั้นเลือกช่วงของเซลล์ F14: F15 กล่องโต้ตอบเพิ่มข้อจำกัดควรมีลักษณะเหมือนกับรูป27-5

ขณะนี้เราได้มั่นใจว่าเมื่อ Solver พยายามค่าที่แตกต่างกันสำหรับเซลล์ที่เปลี่ยนแปลงเพียงการรวมกันที่ตอบสนองทั้งD14< = F14 (แรงงานที่ใช้น้อยกว่าหรือเท่ากับค่าแรงงาน) และD15< = F15 (วัตถุดิบที่ใช้น้อยกว่าหรือเท่ากับ วัตถุดิบที่พร้อมใช้งาน) จะได้รับการพิจารณา คลิกเพิ่มเพื่อใส่ข้อจำกัดของความต้องการ กรอกข้อความในกล่องโต้ตอบเพิ่มข้อจำกัดตามที่แสดงในรูป27-6

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

• D2< = D8 (จำนวนที่ผลิตของยาเสพติด1มีค่าน้อยกว่าหรือเท่ากับอุปสงค์สำหรับยาเสพติด 1)

• E2< = E8 (ปริมาณของการผลิตของยาเสพติด2น้อยกว่าหรือเท่ากับความต้องการสำหรับยาเสพติด 2)

• F2< = F8 (จำนวนที่ผลิตของยาเสพติด3มีค่าน้อยกว่าหรือเท่ากับอุปสงค์สำหรับยาเสพติด 3)

• G2< = G8 (จำนวนที่ผลิตของยาเสพติด4ที่เกิดขึ้นน้อยกว่าหรือเท่ากับความต้องการของยาเสพติด 4)

• H2< = H8 (จำนวนที่ผลิตของยาเสพติด5มีค่าน้อยกว่าหรือเท่ากับอุปสงค์สำหรับยาเสพติด 5)

• I2< = I8 (จำนวนที่ผลิตของยาเสพติดที่6ทำมีค่าน้อยกว่าหรือเท่ากับอุปสงค์สำหรับยาเสพติด 6)

คลิกตกลงในกล่องโต้ตอบเพิ่มข้อจำกัด หน้าต่าง Solver ควรมีลักษณะคล้ายกับรูป27-7

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

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

• เซลล์เป้าหมายถูกคำนวณโดยการเพิ่มข้อกำหนดของฟอร์ม(เปลี่ยนเซลล์) * (ค่าคงที่)

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

เหตุใดปัญหา Solver จึงเป็นเส้นตรง เซลล์เป้าหมายของเรา (กำไร) ถูกคำนวณเป็น

(ยาเสพติด1กำไรต่อปอนด์) * (ยา1ปอนด์ที่ผลิต) +
(ยาเสพติด2กำไรต่อปอนด์) * (ยาเสพติด2ปอนด์ที่ผลิต) + ...
(ยาเสพติด6กำไรต่อปอนด์) * (ยา6ปอนด์ที่ผลิต)

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

ข้อจำกัดด้านแรงงานของเราจะได้รับการประเมินโดยการเปรียบเทียบค่าที่ได้มาจาก(แรงงานที่ใช้ต่อปอนด์ของยา 1) * (ยาเสพติด1ปอนด์ที่ผลิต) + (แรงงานที่ใช้ต่อปอนด์ของยาเสพติด 2) * (ยาเสพติด2ปอนด์ที่ผลิต) + ... (แรงงานเราed ต่อปอนด์ของยาเสพติด 6) * (ยา6ปอนด์ที่ผลิต)กับแรงงานที่พร้อมใช้งาน

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

ข้อจำกัดความต้องการของเราใช้แบบฟอร์ม

(< 1 ที่ผลิต) = (ยาเสพติด1ความต้องการ)
(ยาเสพติด2ที่ผลิต) < = (ยาเสพติด2อุปสงค์)
§
(ยา6ที่ผลิต) < = (ยาเสพติด6อุปสงค์)

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

ได้แสดงให้เห็นว่าผลิตภัณฑ์แบบผสมผสานผลิตภัณฑ์ของเราเป็นรูปแบบเชิงเส้นทำไมเราจึงควรดูแล

• ถ้ารูปแบบ Solver เป็นเส้นตรงและเราเลือกสมมติว่ารูปแบบเส้นตรง Solver จะได้รับการรับประกันเพื่อค้นหาโซลูชันที่เหมาะสมที่สุดกับตัวแบบ Solver ถ้ารูปแบบ solver ไม่ใช่เชิงเส้น Solver อาจหรืออาจไม่พบโซลูชันที่ดีที่สุด

• ถ้ารูปแบบ Solver เป็นเส้นตรงและเราเลือกที่จะถือว่าเป็นรูปแบบเชิงเส้น Solver ใช้อัลกอริทึมที่มีประสิทธิภาพมาก (วิธีการรวม) เพื่อค้นหาโซลูชันที่ดีที่สุดของรูปแบบ ถ้ารูปแบบ Solver เป็นเส้นตรงและเราไม่ได้เลือกสมมติว่ารูปแบบเชิงเส้น Solver ใช้อัลกอริทึมที่ต่ำมาก (วิธีการ GRG2) และอาจมีปัญหาในการค้นหาโซลูชันที่เหมาะสมที่สุดของรูปแบบ

หลังจากคลิกตกลงในกล่องโต้ตอบตัวเลือก solver เราจะกลับไปยังกล่องโต้ตอบ Solver หลักที่แสดงไว้ก่อนหน้าในรูป27-7 เมื่อเราคลิกแก้ไข Solver จะคำนวณโซลูชันที่ดีที่สุด (ถ้ามี) สำหรับรูปแบบการผสมผลิตภัณฑ์ของเรา ตามที่ฉันระบุไว้ในบทที่26ซึ่งเป็นโซลูชันที่ดีที่สุดในการผสมผสานผลิตภัณฑ์จะเป็นชุดของการเปลี่ยนค่าของเซลล์ (£ที่ผลิตจากยาเสพติดแต่ละรายการ) ที่เพิ่มกำไรมากกว่าชุดของโซลูชันที่เป็นไปได้ทั้งหมด อีกครั้งโซลูชันที่เป็นไปได้คือชุดของการเปลี่ยนค่าของเซลล์ที่พอใจกับข้อจำกัดทั้งหมด ค่าของเซลล์ที่มีการเปลี่ยนแปลงที่แสดงในรูป27-9 เป็นโซลูชันที่เป็นไปได้เนื่องจากระดับการผลิตทั้งหมดไม่ใช่ค่าลบระดับการผลิตไม่เกินอุปสงค์และการใช้ทรัพยากรจะไม่เกินทรัพยากรที่พร้อมใช้งาน

ค่าของเซลล์ที่มีการเปลี่ยนแปลงที่แสดงในรูป27-10 บนหน้าถัดไปจะแสดงโซลูชัน infeasibleเนื่องจากสาเหตุต่อไปนี้:

• เราผลิตเพิ่มเติมของยาเสพติด5กว่าความต้องการของมัน

• เราใช้แรงงานมากกว่าสิ่งที่พร้อมใช้งาน

• เราใช้วัตถุดิบมากกว่าสิ่งที่พร้อมใช้งาน

หลังจากคลิกแก้ปัญหา Solver จะค้นหาโซลูชันที่เหมาะสมที่สุดที่แสดงในรูป27-11 ได้อย่างรวดเร็ว คุณจำเป็นต้องเลือกเก็บโซลูชัน Solver เพื่อรักษาค่าโซลูชันที่ดีที่สุดในเวิร์กชีต

บริษัทยาเสพติดของเราสามารถเพิ่มกำไรรายเดือนในระดับของ $๖,๖๒๕.๒๐โดยการผลิต๕๙๖.๖๗ปอนด์ของยา 4, ๑๐๘๔ปอนด์ของยาเสพติด5และไม่มียาเสพติดอื่นๆ! เราไม่สามารถระบุได้ว่าเราสามารถบรรลุผลกำไรสูงสุดของ $๖,๖๒๕.๒๐ได้หรือไม่ ทั้งหมดเราสามารถมั่นใจได้ว่ามีทรัพยากรและความต้องการที่จำกัดของเราไม่มีวิธีใดที่จะทำให้มากกว่า $๖,๖๒๗.๒๐ในเดือนนี้