แนวทางปฏิบัติที่ดีที่สุดสำหรับการสร้างแบบจำลองทางการเงินขั้นสูง: การแฮ็กสำหรับการสร้างแบบจำลองที่ชาญฉลาดและปราศจากข้อผิดพลาด

บทสรุปผู้บริหาร

บทนำ: โมเดลทางการเงิน

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

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

การวางแผนโมเดลของคุณ

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

1. กำหนดเป้าหมายสุดท้ายของโมเดล

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

2. ทำความเข้าใจไทม์ไลน์สำหรับทั้งการสร้างแบบจำลองและอายุการใช้งาน

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

3. กำหนดการแลกเปลี่ยนที่เหมาะสมระหว่าง "รายละเอียด" กับ "การนำกลับมาใช้ใหม่"

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

ด้วยขั้นตอนพิมพ์เขียว/การวางแผนที่เสร็จสมบูรณ์และการตัดสินใจที่สำคัญต่างๆ ได้รับการแก้ไขแล้ว เราอาจก้าวไปสู่ขั้นต่อไปของการสร้างแบบจำลอง

โครงสร้างโมเดลของคุณ

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

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

1. หน้าปก (แท็บ): ชื่อรหัสโครงการ คำอธิบายเจตนาของโมเดล ข้อมูลติดต่อของผู้เขียน และข้อจำกัดความรับผิดชอบที่เกี่ยวข้อง
2. แท็บไดรเวอร์: อินพุตและสมมติฐาน
3. แท็บแบบจำลอง: การคำนวณ (เช่น การคาดการณ์และการคำนวณงบการเงินสามรายการ)
4. แท็บผลลัพธ์: บทสรุปที่ชัดเจนและเรียบร้อยของไฮไลท์ที่สำคัญที่สุดของโมเดล
5. แท็บความละเอียดอ่อน: ช่วงของสถานการณ์สมมติ ความอ่อนไหว และผลลัพธ์ของข้อมูลที่ผู้บริหารจะต้องพึ่งพาเมื่อเปลี่ยนไปเป็นกระบวนการตัดสินใจ

ฉันจะแบ่งแต่ละส่วนให้คุณทีละส่วน ดังนี้

หน้าปก

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

1. ชื่อของรุ่น: ตัวอธิบาย.
2. วัตถุประสงค์ของแบบจำลอง: ย่อหน้าที่อธิบายการใช้งานตามวัตถุประสงค์
3. ดัชนีโมเดล: ตารางสั้นๆ ที่มีรายละเอียดคำอธิบายและวัตถุประสงค์ของแต่ละแท็บ ส่วนนี้จะเป็นประโยชน์อย่างยิ่งต่อผู้ปฏิบัติงานที่ไม่ใช่ด้านการเงิน โดยช่วยให้พวกเขา "แยกแยะ" โครงสร้างและขั้นตอนของโมเดลโดยเน้นว่าแท็บใดที่พวกเขาต้องใช้สำหรับอินพุต เอาต์พุตใดที่ควรเน้นระหว่างการตัดสินใจ และแท็บการคำนวณที่ซับซ้อนที่ควร ปล่อยให้ไม่มีใครแตะต้อง
4. ประวัติเวอร์ชันของโมเดล: การลงทุนไม่กี่วินาทีในการพิมพ์ตามวันที่ การเปลี่ยนแปลงที่สำคัญที่เกิดขึ้นกับโมเดลในขณะที่คุณดำเนินการจะช่วยประหยัดเวลาลงได้เสมอ โดยเฉพาะอย่างยิ่งหากคุณต้องการย้อนรอยและย้อนกลับ/แก้ไขการเปลี่ยนแปลง โดยเฉพาะอย่างยิ่งสำหรับโมเดลที่ซับซ้อนและแบบจำลองที่คุณอาจใช้เป็นเทมเพลตในอนาคต
5. ข้อมูลติดต่อของผู้เขียน: อธิบายตนเองได้
6. ข้อสงวนสิทธิ์ทางกฎหมายที่เกี่ยวข้อง (ถ้ามี ตามที่ที่ปรึกษากฎหมายของคุณกำหนด) : อธิบายตนเอง

โปรดทราบ: ฉันขอแนะนำว่าหน้าปกควรล็อกไว้สำหรับทุกคนและทุกคนโดยไม่มีอำนาจชัดแจ้งในการเปลี่ยนแปลงภายนอกผู้เขียน

แท็บไดรเวอร์: อินพุตและสมมติฐาน

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

ตัวอย่างแท็บไดรเวอร์และสมมติฐานตัวอย่าง

ภายในส่วนอินพุตแบบส แตติก และ ไดนามิก ทั้งสองด้านบน ฉันแนะนำให้คุณแยกข้อมูลของคุณออกเป็นสองประเภทอย่างชัดเจน: (1) ตัวเลขที่ฮาร์ดโค้ดซึ่งไม่เปลี่ยนแปลงโดยไม่คำนึงถึงสถานการณ์สมมติ และ (b) พารามิเตอร์ที่มีความอ่อนไหวที่จะ ขับเคลื่อนสถานการณ์สมมติที่แตกต่างกันและท้ายที่สุดคือตารางความไวของคุณ อย่างไรก็ตาม โปรดทราบว่าคุณไม่มีทางรู้อย่างถ่องแท้ว่าพารามิเตอร์ใดที่จะประกอบเป็นพารามิเตอร์ที่ละเอียดอ่อน และคุณจะไม่ทราบจนกว่าจะถึงขั้นตอนสุดท้ายของโปรเจ็กต์ สำหรับข้อมูลเพิ่มเติมเกี่ยวกับการสร้างแบบจำลองความไว โปรดอ่านบทความต่อไปนี้

แท็บแบบจำลอง: การคำนวณโดยละเอียดและการสร้างการดำเนินงาน

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

ตัวอย่างแท็บแบบจำลองตัวอย่าง

สถานการณ์และความไว Tab

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

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

หมายเหตุเล็กน้อยเกี่ยวกับภาพด้านบน:

1. ผู้ใช้โมเดลควรจะแก้ไขได้เพียงสิ่งนี้ เนื่องจากเป็นที่ที่พวกเขาจะเลือกหมายเลขสถานการณ์ ตัวเลขนี้อ้างอิงถึงหนึ่งในสถานการณ์ที่แสดงทางด้านขวาของสเปรดชีต จากนั้นผู้ใช้จะนำเสนอสถานการณ์ที่เลือก (ในกรณีนี้คือ หมายเลข 6) ในคอลัมน์แรก นี่เป็นคอลัมน์เดียวของสเปรดชีต Scenario และ Sensitivity ที่อ้างอิงในแบบจำลอง
2. เพิ่มฟิลด์คำอธิบายสองสามฟิลด์ที่นี่ ซึ่งสรุปได้อย่างมีประสิทธิภาพว่าสถานการณ์ที่เลือกแสดงถึงอะไร
3. ฉันพบว่ามีประโยชน์มาก โดยเฉพาะอย่างยิ่งหากมีคนอื่นใช้โมเดลนี้ เพื่อเพิ่มคอลัมน์ที่ระบุอินพุตแต่ละหน่วย
4. คอลัมน์นี้จะดึงสถิติ/ฟิลด์เลเวอเรจของสถานการณ์ที่เลือก (ในกรณีนี้คือหมายเลข 6) ซึ่งจะแสดงทั้งหมดทางด้านขวา (สีน้ำเงิน) สูตรที่จำเป็นในการขับเคลื่อนคือฟังก์ชันออฟเซ็ต เช่น “=OFFSET (แทรกเซลล์ว่างทางด้านซ้ายของสถานการณ์แรกที่ไฮไลต์ด้วยสีแดงด้านบน เซลล์ที่เลือก/ไฮไลต์สถานการณ์)” โปรดทราบว่ามีช่องว่างระหว่างสองเซลล์ ดังนั้นเครื่องหมายจุลภาค (,,) ทั้งสองจึงไม่เป็นการสะกดผิด
5. จัดกลุ่มสมมติฐานของคุณในหมวดหมู่มหภาคและหมวดหมู่ย่อย วิธีนี้จะช่วยให้ทั้งคุณ (ผู้สร้างโมเดล) และผู้ใช้ของคุณเข้าใจอย่างชัดเจนว่าแบบจำลองได้เลือกสถานการณ์ใดไว้

ตัวอย่างตารางความไวของตัวอย่าง

แท็บเอาต์พุต

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

1. แท็บผลลัพธ์ทางการเงิน: นี่คือข้อมูลสรุปโดยย่อของข้อมูลทางการเงินที่มีรายละเอียดในแท็บแบบจำลอง มักจะนำเสนอเป็นประจำทุกปี (แม้ว่าแบบจำลองอาจเป็นรายไตรมาส) เอาต์พุตนี้ควรอยู่ระหว่าง 50 ถึง 150 แถว และควรแสดงรายการโฆษณาหลักทั้งหมดจากแท็บการคำนวณ โปรดตรวจสอบให้แน่ใจว่าได้นำเสนอรายละเอียดที่เพียงพอ เพื่อให้แน่ใจว่าผู้ใช้จะไม่สลับไปมาระหว่างแท็บนี้กับแท็บการคำนวณต่างๆ โปรดทราบด้วยว่า ตามแนวทางปฏิบัติที่ดีที่สุด แท็บเอาต์พุตไม่ควรทำการคำนวณใดๆ ซ้ำ และข้อมูลนี้ควรรวมเฉพาะลิงก์โดยตรงเท่านั้น
2. แท็บบทสรุปสำหรับผู้บริหาร: แท็บนี้เป็นแท็บมาตรฐานที่ค่อนข้างธรรมดา และมักจะแสดงกราฟ แผนภูมิ และตารางต่างๆ ผสมกัน ซึ่งแสดงตัวอย่างที่เข้าใจง่ายและเข้าใจง่ายที่สุดเท่าที่จะเป็นไปได้ แนวโน้ม การวิเคราะห์ และสถิติสรุปที่สำคัญต่างๆ ที่ผู้บริหารและสมาชิกคณะกรรมการจำเป็นต้องใช้ในการนำทาง การตัดสินใจที่สำคัญ
3. แท็บผลลัพธ์เฉพาะ: แท็บนี้ประกอบด้วยผลลัพธ์ที่เฉพาะเจาะจง ซึ่งมักจะกำหนดโดยเทมเพลตของบันทึกการลงทุน การนำเสนอของคณะกรรมการการลงทุน หรือคำขอของผู้บริหารและสมาชิกคณะกรรมการตามความจำเป็นเพื่อไปยังจุดตัดสินใจของพวกเขา

ตัวอย่างแท็บผลลัพธ์ของโมเดล รวมถึงตาราง แผนภูมิ และกราฟ

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

การจัดรูปแบบโมเดลของคุณ

ประการแรก สิ่งสำคัญคือต้องสังเกตว่าแต่ละบริษัท/กลุ่มอาจมีความชอบหรือแนวทางปฏิบัติภายในของตนเอง ดังนั้น ขณะสร้าง สิ่งสำคัญคือต้องเช็คอินก่อนและปฏิบัติตาม ไม่ว่าบริษัทของคุณจะกำหนดรูปแบบรูปแบบใด อย่างไรก็ตาม ในกรณีที่ไม่มีแนวทางปฏิบัติเฉพาะของบริษัท เนื้อหาด้านล่างให้รายละเอียดภาษาสากลของ Wall Street สำหรับการจัดรูปแบบโมเดล

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

สีน้ำเงิน = อินพุต หรือข้อมูลที่ฮาร์ดโค้ด เช่น ค่าในอดีต สมมติฐาน และไดรเวอร์

สีดำ = สูตร การคำนวณ หรือการอ้างอิงที่มาจากชีตเดียวกัน

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

สีม่วง = ลิงก์ อินพุต สูตร ข้อมูลอ้างอิง หรือการคำนวณไปยังไฟล์ Excel อื่นๆ (โปรดทราบว่าบางรุ่นข้ามขั้นตอนนี้ทั้งหมดและใช้สีดำสำหรับเซลล์เหล่านี้ด้วย)

สีแดง = ข้อผิดพลาดที่ต้องแก้ไข

ตัวอย่างบทสรุปทางการเงินที่มีรูปแบบที่ดี (รหัสสี)

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

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

คำแนะนำในการสร้างมาโคร (สำหรับ Excel ทั้งเวอร์ชัน Mac และ PC):

1. กด Alt + W + M + R พร้อมกันเพื่อตั้งชื่อและเริ่มบันทึกมาโครของคุณ
2. กด F5 (“Jump to Cell”) จากนั้นกด Alt + S พร้อมกันเพื่อไปที่เมนู “Go to Special”
3. กด "O" เพื่อเลือกค่าคงที่และ "X" เพื่อยกเลิกการเลือกข้อความ
   
4. ตอนนี้ให้กด Alt + H + FC (หรือ Ctrl + 1) พร้อมกัน แล้วเลือกสีแบบอักษรสีน้ำเงินสำหรับค่าคงที่เหล่านี้
5. กด Esc
6. ตอนนี้ทำสิ่งเดียวกันโดยเริ่มจาก F5 แต่เลือกสูตร (F) แทนค่าคงที่แล้วกด "X" เพื่อยกเลิกการเลือกข้อความ
7. ตอนนี้กด Alt + H + FC (หรือ Ctrl + 1) แล้วเลือกสีแบบอักษรสีดำสำหรับค่าคงที่เหล่านี้
8. หยุดบันทึกมาโครด้วย Alt + W + M + R หรือ Alt + T + M + R

ลิงค์ไปยังสมุดงานอื่นๆ

การค้นหาลิงก์ไปยังเวิร์กบุ๊กและเวิร์กชีตอื่นๆ เป็นเรื่องยาก และคุณจะต้องใช้ VBA เพื่อให้ทำงานได้อย่างถูกต้อง นี่คือแนวคิดพื้นฐาน: ค้นหาการมีอยู่ของสัญลักษณ์ “!” ในแต่ละเซลล์ที่มีสูตรในเวิร์กบุ๊กของคุณ แล้วเปลี่ยนสีฟอนต์เป็นสีเขียว คุณจะต้องแก้ไขสิ่งนี้ใน VBA Editor และทำให้เป็น for each ลูปผ่านทุกอินสแตนซ์ของ “!” คุณพบแล้วเปลี่ยนสีแบบอักษรสำหรับแต่ละรายการ

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

แนวทางปฏิบัติที่ดีที่สุดสำหรับการตรวจสอบแบบจำลอง

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

ด้านล่างนี้คือชุดแนวทางปฏิบัติที่ดีที่สุดเกี่ยวกับวิธีสร้างด้วยกรอบความคิดของผู้ตรวจสอบบัญชี ดังนี้

1. หนึ่งแถว หนึ่งสูตร

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

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

วิธีง่ายๆ วิธีหนึ่งในการจัดการกับอินสแตนซ์เหล่านี้ คือการใช้แฟล็ก (เช่น 1/0, TRUE / FALSE ) ที่ตำแหน่งบนสุดของสเปรดชีต จากนั้นจึงอ้างอิงโดยใช้คำสั่ง IF ผ่านเนื้อหาของโมเดล ภาพประกอบง่ายๆ ของสิ่งนี้ในที่ทำงานมีดังนี้:

ตัวอย่าง "การใช้แฟล็ก" ในการสร้างแบบจำลอง Excel

2. ไม่มีตัวเลขแบบตายตัวที่ฝังอยู่ในสูตร

ห้ามใช้ตัวเลขแบบตายตัวที่ฝังอยู่ในสูตร เนื่องจากจะสังเกตได้ยากหากผู้ใช้ไม่ค่อยคุ้นเคยกับโมเดล ให้เน้นและแยกอินพุต/ฮาร์ดโค้ดออกจากสูตรให้ชัดเจน ยังดีกว่า รวบรวมอินพุต/ฮาร์ดโค้ดทั้งหมด (ตามความเหมาะสม) และรวมไว้ในแท็บเดียวกัน จากนั้นให้สูตรของคุณดึง/อ้างอิงตามความเหมาะสมจากเซลล์ที่ต้องการและจากแท็บที่เหมาะสม

3. ความเรียบง่ายย่อมดีกว่าเสมอ

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

4. ปฏิบัติตามอนุสัญญาการลงนามของคุณอย่างสม่ำเสมอ

คุณควรตัดสินใจในเวลาเป็นศูนย์ว่าแบบแผนการลงนาม/คีย์ของคุณจะเป็นอย่างไร ตามภาพประกอบ ถามตัวเองในขั้นตอนการออกแบบแบบจำลองของคุณว่า “ค่าใช้จ่าย ค่าใช้จ่าย การหัก ค่าเสื่อมราคา CapEx ฯลฯ จะถูกนำเสนอเป็นตัวเลขติดลบหรือบวกหรือไม่” ความชอบส่วนตัวของฉันคือการนำเสนอค่าใช้จ่ายเป็นตัวเลขติดลบเสมอด้วยเหตุผลสองประการ: (a) ผลรวมจะเป็นผลรวมโดยตรงและคุณจะลดข้อผิดพลาดของผู้ใช้ให้น้อยที่สุด และ (b) จะเห็นข้อผิดพลาดโดยใช้เพียงสัญลักษณ์ได้ง่ายขึ้น

5. หลีกเลี่ยงการตั้งชื่อเซลล์ของคุณ แทนที่จะใช้ Grid Logic ของ Excel

หากเป็นไปได้ ผมขอแนะนำอย่างยิ่งให้หลีกเลี่ยงการตั้งชื่อเซลล์ของคุณ เนื่องจากเป็นเรื่องยากที่จะหาแหล่งอินพุตสำหรับเซลล์ที่มีชื่อดังกล่าว (เช่น "อัตราเงินเฟ้อ") ที่ถนน เราขอแนะนำให้คุณใช้แบบแผนตารางของ Excel ภายในสูตรของคุณแทน (เช่น เพียงเชื่อมโยงไปยังเซลล์ C4 หรือตำแหน่ง [Tab Name]l'!G21 หากการอ้างอิงอยู่ในแท็บหรือเวิร์กบุ๊กอื่น)

6. ไม่เคยมีอินพุตเดียวกันในหลาย ๆ ตำแหน่ง

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

7. หลีกเลี่ยงการลิงก์ไฟล์

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

8. อย่าซ่อนแผ่นงานหรือแถว

ภายในสเปรดชีตที่ยาวขึ้น ให้ "จัดกลุ่ม" แถว/คอลัมน์ แทนที่จะ "ซ่อน"

9. แท็บที่น้อยกว่าและใหญ่กว่านั้นดีกว่าแท็บที่เล็กกว่าหลายแท็บ

การปฏิบัตินี้ขึ้นอยู่กับประสบการณ์ 100% การติดตามและตรวจสอบอาร์เรย์ที่ต่อเนื่องกันสำหรับข้อมูลในสเปรดชีตขนาดใหญ่ที่ต่อเนื่องกันนั้นง่ายกว่าการข้ามหลายแท็บหรือที่แย่กว่านั้นคือมีหลายสเปรดชีตที่มีการเชื่อมโยงข้าม

10. สร้างการตรวจสอบทั่วทั้งแบบจำลองของคุณผ่าน “การตรวจสอบข้อผิดพลาดรวม” ที่อยู่ในแท็บเดียว

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

ตัวอย่างงบดุล "ตรวจสอบ"

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

แกะออก – สำหรับผู้ใช้ขั้นสูง: Excel Tips

ส่วนนี้ครอบคลุมแนวทางปฏิบัติที่ดีที่สุดของ Excel สำหรับผู้ใช้ขั้นสูงของเรา สิ่งเหล่านี้อาจต้องมีการปรับเปลี่ยนเล็กน้อย แต่จะช่วยประหยัดเวลาในการทำงานได้หลายชั่วโมงในภายหลัง และง่ายต่อการนำไปใช้งาน มีดังต่อไปนี้ อย่างสั้น รวบรัด ตรงประเด็น:

1. ใช้แป้นพิมพ์ลัดให้มากที่สุด มีหลายไฟล์ในอินเทอร์เน็ตบนแป้นพิมพ์ลัดของ Excel ที่เหมาะกับความต้องการที่แตกต่างกัน ฉันจะอ้างอิงสองสามที่นี่:
   • การสร้างแบบจำลองคีย์ Cheat Sheet
   • รายการเคล็ดลับ Excel ที่ครอบคลุม
2. ใช้ F5 (“ไปที่แบบพิเศษ”) เพื่อค้นหาตัวเลขหรือสูตรที่ฮาร์ดโค้ดทั้งหมดอย่างรวดเร็ว
3. ใช้ Trace Precedents และ Trace Dependents เพื่อตรวจสอบแบบจำลอง
4. ใช้ XNPV และ XIRR เพื่ออนุญาตให้ใช้ วันที่กำหนดเอง__ กับกระแสเงินสด ระหว่างทางไปยังการวิเคราะห์ผลตอบแทน ซึ่งต่างจากฟังก์ชัน NPV และ IRR ของ Excel ซึ่งถือว่าช่วงเวลาเท่ากันสำหรับการคำนวณโดยปริยาย
5. ใช้ฟังก์ชัน INDEX MATCH เหนือ VLOOKUP เพื่อค้นหาข้อมูลในสเปรดชีตขนาดใหญ่
6. VLOOKUP นั้นเหนือกว่าคำสั่ง IF เกือบทุกครั้ง สบายใจกับมัน
7. สร้างนิสัยในการรวม IFERROR ไว้ในไวยากรณ์ของสูตรของคุณ
8. ใช้ฟังก์ชันวันที่ คำสั่ง EOMONTH และ IF ร่วมกันเพื่อทำให้วันที่เป็นไดนามิก
9. ลบเส้นตารางเมื่อนำเสนอหรือแชร์แบบจำลองทางการเงิน ทำให้เอกสารออกที่สะอาดและสวยงามยิ่งขึ้น

รักหรือเกลียด...

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

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