การทำ Simulation ด้วย Excel

Simulation คืออะไร?

Simulation คือการใช้ Model หรือแบบจำลองที่ใช้ความสัมพันธ์ทางคณิตศาสตร์ มาช่วยคำนวณผลลัพธ์ของสิ่งที่เราสนใจ โดยที่หากเราสามารถสร้างแบบจำลองที่แม่นยำใกล้เคียงกับความเป็นจริง เราก็จะสามารถใช้แบบจำลองช่วยทำนายผลลัพธ์ที่อาจจะเกิดขึ้นได้ด้วย ซึ่งจะช่วยให้เราสามารถตัดสินใจได้ดียิ่งขึ้น

จริงๆ แล้วเราก็ใช้ Excel คำนวณเรื่องต่างๆ ตามปกติอยู่แล้ว เช่น คำนวณผลกำไรของธุรกิจ คำนวณพื้นที่ คำนวณปริมาตร แต่เรามักจะใช้ Input ของสูตรเป็นค่าที่มีความแน่นอนไปเลย เช่น

ผลกำไรของธุรกิจ = รายได้รวมรวม - ค่าใช้จ่ายรวม

หากคำนวณผลกำไรของธุรกิจที่เกิดขึ้นแล้ว ก็เอารายได้จริง ลบ ค่าใช้จ่ายจริง ไปเลย แบบนั้นก็ไม่ต้องมาทำ Simulation อะไรทั้งสิ้น

ผลกำไรของธุรกิจ (แน่นอน) =  
รายได้รวมรวม (แน่นอน) - ค่าใช้จ่ายรวม (แน่นอน)

//แบบนี้คำนวณตรงๆ ไปเลยไม่ต้องใช้ Simulation

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

ผลกำไรของธุรกิจ (ไม่แน่นอน) =  
รายได้รวมรวม - ค่าใช้จ่ายรวม 
(อย่างน้อยตัวใดตัวหนึ่งไม่แน่นอน)

//แบบนี้ใช้ Simulation เพื่อจำลองความไม่แน่นอน

เรื่องการแจกแจงความน่าจะเป็นเป็นความรู้ทางสถิติแบบนึง ใครไม่คุ้นสามารถเริ่มอ่านเรื่องสถิติได้ที่นี่

ทำไมต้องทำ Simulation?

เราใช้ Simulation เพื่อจำลองผลลัพธ์ที่ไม่แน่นอนนั้นๆ ออกมาให้เราได้เข้าใจ และเห็นภาพชัดขึ้นว่าจะเกิดอะไรขึ้นได้บ้าง ซึ่งดีกว่าการใช้ค่ากลางอย่างเช่นค่าเฉลี่ยมาคำนวณเพียงอย่างเดียว โดยที่เราไม่ต้องทำการทดสอบจริงๆ ซึ่งจะม่ีค่าใช้จ่ายสูง

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

Excel ใช้ทำ Simulation ยังไง?

การที่จะสร้างความไม่แน่นอนขึ้นมาได้นั้น เราจะใช้การสุ่มค่ามาช่วย ซึ่งใน Excel มีฟังก์ชันที่ใช้สุ่มตัวเลขอยู่หลายฟังก์ชัน เช่น

• RAND() ซึ่งเป็นการสุ่มเลขตั้งแต่ 0-1 โดยเป็นทศนิยมได้
• RANDBETWEEN(bottom,top) โดยเป็นการสุ่มเลขจำนวนเต็มตั้งแต่ botton ถึง top
• RANDARRAY(rows,columns,min,max,integer)

ตัวหลักๆ ที่เราจะใช้คือ RAND() เพราะได้ค่าทศนิยมละเอียด และใช้ได้ใน Excel ทุก version โดยที่เมื่อ RAND แล้วจะได้เลขโดยสุ่มเลขตั้งแต่ 0-1 ออกมาค่านึง

แล้วพอมีการคำนวณใหม่ (เช่น กด F9) ก็จะได้ค่าใหม่ออกมาเป็นอีกค่าหนึ่ง ซึ่งนี่ก็คือ Concept ของการสุ่มใน Excel นั่นเอง

หากเราเอาค่าที่สุ่มได้ตั้งแต่ 0-1 นั้น ออกมาใช้กับ Distribution รูปแบบต่างๆ ได้ เช่น Uniform Distribution, Normal Distribution, T-Distribution, Binomial Distribution, Poisson Distribution และอีกมากมาย

ตัวอย่างการสุ่มแบบง่าย

ถ้าเป็นการสุ่มแบบ Yes, No ที่มีโอกาสเกิด xx% เช่น โอกาสที่ทำงานสำเร็จ คือ 70% แบบนี้เราก็แค่ใช้ RAND เทียบกับค่า 0.7 ได้ เช่น

=IF(RAND()<=0.7,"สำเร็จ","ล้มเหลว")

ตัวอย่างการสุ่มแบบ Distribution ซับซ้อน

แต่ถ้าโอกาสที่จะเกิดขึ้นมันมีความน่าจะเป็นแบบ Distribution ที่ซับซ้อนขึ้นล่ะ??

วิธีที่ง่ายที่สุดอันนึงคือการใช้ Inverse ของความน่าจะเป็น Distribution เหล่านั้นในการหาค่าที่สนใจ

ตัวอย่างง่ายๆ เช่น กรณี Normal Distribution เราจะใช้ NORM.DIST กับ NORM.INV จะเป็นการคำนวณกลับกัน

Key สำคัญคือเราจะ Random ความน่าจะเป็นในการเกิดเหตุการณ์นั้นๆ ออกมา แล้วใช้ Inverse เพื่อหาค่าที่สนใจ ยกตัวอย่างเช่น

สมมติว่า Demand ของสินค้าที่เราจะขาย มีการแจกแจกแบบ Normal Distribution ที่มีค่าเฉลี่ยคือ 200 ชิ้นต่อเดือน และมีค่าเบี่ยงเบนมาตรฐานที่ 50 ชิ้นต่อเดือน ถ้าลองคำนวณยอดขายตลอด 1 ปีอาจจะได้คำตอบแบบนี้ (แน่นอนว่าถ้ากด F9 เลขก็จะเปลี่ยนอีก)

มันจะช่วยให้เราเห็นภาพได้ว่า โดยเฉลี่ยแล้วยอดอาจจะอยู่ประมาณ 200 จริงๆ แต่บางเดือนก็อาจจะน้อย บางเดือนก็เยอะ เป็นต้น

นอกจากนี้ เรายังสามารถใช้ Data Table มาช่วย Run Simulation หลายๆ รอบได้ตามต้องการ (Simulate เป็นพันครั้งเลยก็ได้ ยิ่งเยอะยิ่งเห็นความเป็นไปได้ชัดเจนมากขึ้น)

ถ้าอยากลองสร้างตารางแจกแจงความถี่ของยอดขายที่ได้ ก็สามารถใช้กราฟแบบ Histogram หรือว่าจะใช้ Analysis Toolpak โดยใช้การวิเคราะห์แบบ Histogram อีกทีก็ได้ครับ

นี่แค่เริ่มต้นเท่านั้น

ซึ่งอันนี้เป็นตัวอย่างง่ายๆ ในการใช้ความรู้ทางสถิติ มาผสมกับเครื่องมือใน Excel เพื่อสร้าง Simulation ยอดขายขึ้นมาเท่านั้น

ในชีวิตจริง มันอาจจะมี Simulation หลายๆ เรื่อง ผูกโยงกันจนกลายเป็น Simulation Model ที่ซับซ้อนขึ้น และตรงกับความเป็นจริงมากขึ้นในที่สุด

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