Tools and resources

Equipping Yourself for Success

Financial modeling is a craft that requires both conceptual understanding and practical tools. While this eBook has focused primarily on principles and techniques, having the right tools and knowing where to find additional resources dramatically accelerates your learning and enhances your capabilities. This chapter surveys the software tools, Excel functions, training resources, and industry standards that professional modelers rely on.

The good news is that financial modeling doesn't require expensive specialized software. The vast majority of financial models are built in Microsoft Excel, which is widely available and powerful enough for even the most sophisticated applications. What matters more than having exotic tools is mastering the fundamental capabilities of Excel and knowing where to turn when you need to expand your knowledge.

7.1 Software Tools and Platforms

Microsoft Excel: The Industry Standard

Excel dominates financial modeling for good reasons. Its flexibility allows you to structure models however makes sense for your specific situation rather than conforming to rigid templates. Its calculation engine is powerful enough to handle complex models with thousands of formulas. Its ubiquity means everyone can open, view, and work with your models without specialized software. And its formula language, while sometimes cumbersome, is learnable and sufficient for virtually any modeling need.

Excel's dominance means that Excel skills are highly transferable. Learn to model in Excel, and those skills apply across companies, industries, and geographies. Job descriptions asking for "financial modeling experience" almost always mean Excel-based modeling.

Recent versions of Excel have added capabilities particularly useful for modeling: better handling of large datasets, improved formula functions like XLOOKUP, dynamic arrays, and enhanced charting. If you're using an older version, consider upgrading to access these improvements.

Google Sheets: Cloud-Based Collaboration

Google Sheets offers similar functionality to Excel but in a cloud-native environment. Its primary advantage is real-time collaboration—multiple people can work simultaneously, seeing each other's changes instantly. This makes it attractive for teams that need to collaborate on model development or updates.

However, Google Sheets has limitations compared to Excel. It's slower with large, complex models. Some advanced Excel functions aren't available or work differently. And the financial industry still defaults to Excel, meaning you'll likely need to convert Sheets-based models to Excel for external sharing.

Google Sheets works well for simpler models and collaborative environments. For complex institutional-grade models, Excel remains the better choice despite its weaker collaboration features.

Specialized Financial Modeling and Planning Software

Various software platforms target corporate performance management and financial planning:

Adaptive Insights (now part of Workday) provides cloud-based financial planning and analysis tools with pre-built templates and workflows designed for FP&A professionals.

Anaplan offers a connected planning platform that integrates finance, sales, supply chain, and workforce planning in a multi-dimensional environment.

Quantrix provides multi-dimensional modeling capabilities that can be more elegant than spreadsheets for certain applications, particularly those involving multiple dimensions (products x regions x time periods x scenarios).

Vena combines Excel's interface with a centralized database backend, giving users the familiar Excel environment while adding version control, workflow management, and data governance.

These platforms can offer advantages for enterprise-wide planning processes, particularly around collaboration, data governance, and workflow management. However, they typically require significant implementation effort and ongoing cost. For most individual modelers and many companies, Excel remains perfectly adequate.

7.2 Excel Functions Essential for Modelling

Mastering key Excel functions dramatically improves modelling efficiency and capability. You don't need to know every Excel function—there are hundreds—but deep proficiency with core functions used in financial modelling is essential.

Basic Calculation Functions:

SUM adds numbers together. Use it extensively for totals and subtotals throughout your model. AVERAGE calculates means. These are fundamental building blocks.

IF, IFS: Logical functions that return different values depending on conditions. IF allows one condition, IFS (in newer Excel versions) allows multiple conditions. These functions enable scenarios, toggles, and conditional logic throughout your model.

Lookup Functions:

• VLOOKUP searches vertically for a value and returns a corresponding result from another column. Despite its limitations, VLOOKUP remains widely used. HLOOKUP does the same horizontally.
• INDEX and MATCH used together provide more flexible lookup capabilities than VLOOKUP. INDEX returns a value from a specified position in a range; MATCH finds the position of a value. Combined, they create powerful two-way lookups.
• XLOOKUP (in newer Excel versions) is the modern replacement for VLOOKUP, offering greater flexibility and functionality with simpler syntax. If you have access to XLOOKUP, use it.
• NPV calculates the net present value of a series of cash flows, discounting them to present value. Critical for valuation.
• IRR calculates the internal rate of return—the discount rate that makes NPV equal zero. XIRR handles irregularly timed cash flows, which is often more realistic than the standard IRR function.
• PMT calculates payment amounts for loans. IPMT calculates the interest portion of a payment. PPMT calculates the principal portion. These functions are essential for debt schedules.
• FV calculates future value. PV calculates present value. Both are useful for various time-value-of-money calculations.

Financial Functions:

• OFFSET returns a reference to a range that's offset from a starting cell by specified rows and columns. Useful for dynamic ranges that expand or contract.
• INDIRECT converts text strings into cell references. This enables dynamic references but makes models harder to audit, so use sparingly.
• SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF: Conditional aggregation functions that sum, count, or average only cells meeting specified criteria.
• EOMONTH: Returns the last day of a month, useful for date calculations in monthly models.

Advanced Functions:

Data Tables (found under Data > What-If Analysis) enable sensitivity analysis by automatically calculating results across different input values. This isn't a function per se but a critical Excel feature for scenario analysis.

Array Formulas perform calculations on arrays of values rather than single values. While powerful, array formulas can be opaque and are often unnecessary—consider whether simpler approaches would work before implementing complex array formulas.

7.3 Learning Resources and Professional Development

The field of financial modelling has rich learning resources available at every level, from beginner to advanced.

Professional Training Programs:

Wall Street Prep has earned a reputation as a leading provider of financial modelling training, particularly for investment banking-style modelling. Their courses cover company valuation, DCF analysis, LBO modelling, M&A modelling, and other topics. Many banks and financial institutions use Wall Street Prep for training programs.

Breaking Into Wall Street offers training specifically oriented toward breaking into investment banking and private equity, with extensive modelling courses and practice exercises.

Corporate Finance Institute (CFI) provides the Financial Modelling & Valuation Analyst (FMVA) certification, which combines self-paced online learning with a formal certification. CFI's content covers modelling, valuation, financial analysis, and related topics across finance domains.

Training The Street delivers in-person and virtual financial modelling bootcamps, often used by financial institutions for new hire training or professional development.

Online Learning Platforms:

Coursera partners with universities to offer financial modeling and corporate finance courses, often at an academic level with strong theoretical foundations.

Udemy hosts numerous practical Excel and financial modeling courses, typically focused on specific skills with hands-on practice. Quality varies, so check reviews and ratings.

LinkedIn Learning offers business-focused training including Excel, financial analysis, and modeling courses integrated into the LinkedIn ecosystem.

YouTube contains vast amounts of free training content. Channels dedicated to Excel and financial modeling provide tutorials ranging from basic to advanced. While quality varies and content is fragmented, YouTube can be an excellent free resource for learning specific techniques.

Books and Publications:

Beyond the references listed in Chapter 9, consider subscribing to financial publications that often discuss modeling applications and techniques. The CFA Institute, various accounting bodies, and financial modeling associations publish articles and resources about modeling best practices and emerging techniques.

Industry conferences and webinars offer opportunities to learn from experienced practitioners, see case studies, and network with other modelers. Many professional organizations host modeling competitions and challenges that provide hands-on learning opportunities.

7.4 Industry Standards and Best Practice Frameworks

The financial modeling community has developed standards and frameworks that codify best practices. Familiarity with these standards helps you build models that meet professional expectations and facilitates communication with other modeling professionals.

The FAST Standard:

FAST (Flexible, Appropriate, Structured, Transparent) is a widely recognized set of principles for financial model design. The standard emphasizes:

• Flexible: Models should be easy to use, understand, and modify. They should accommodate changes in assumptions and structure without requiring complete rebuilds.
• Appropriate: Model complexity and detail should match the decision being made. Not too simple to miss critical factors, not too complex to obscure them.
• Structured: Consistent organization and layout make models easier to navigate and audit. Similar calculations should appear in similar locations following similar patterns.
• Transparent: Formulas should be clear and traceable. Assumptions should be explicit. Methodology should be documented. Users should be able to understand how results are derived.

The FAST Standard's website provides detailed guidance on implementing these principles in practice.

The SMART Method:

• Separation: Separate inputs from calculations and calculations from outputs. This separation improves flexibility and reduces errors.
• Minimization: Minimize complexity and repetition. Use consistent formulas that can be copied rather than recreating similar calculations differently in each cell.
• Articulation: Make the model's structure and logic clear through formatting, labeling, and documentation.
• Reusability: Build models as templates that can be adapted for different situations rather than single-use custom builds.
• Testing: Implement comprehensive testing procedures including error checks, sense checks, and scenario tests.

SMART (Separation, Minimization, Articulation, Reusability, Testing) focuses specifically on Excel-based model construction:

Professional Certifications:

Several organizations offer financial modeling certifications that validate your skills and knowledge:

The Financial Modeling Institute offers the Advanced Financial Modeler (AFM) designation.

Corporate Finance Institute's FMVA certification is probably the most widely recognized credential specifically for financial modeling.

CFA (Chartered Financial Analyst) certification, while broader than just modeling, includes significant modeling content and is highly respected in the investment community.

CIMA (Chartered Institute of Management Accountants) and similar accounting bodies offer certifications that include financial modeling and analysis components.

Whether certifications are worth pursuing depends on your career goals and current credentials. They can differentiate candidates in competitive job markets and provide structured learning paths, but practical modeling skill demonstrated through work experience often matters more than credentials.

7.5 Communities and Continuing Resources

Financial modeling is a field where ongoing learning never stops. Stay connected to the modeling community through:

Online Forums and Communities: Websites like Reddit's r/financialmodelling, specialized LinkedIn groups, and professional forums provide opportunities to ask questions, share knowledge, and learn from others' experiences.

Excel and Modeling Blogs: Many experts maintain blogs sharing tips, techniques, and insights. Following key voices in the Excel and modeling space keeps you current with techniques and best practices.

Professional Networks: Connecting with other modelers in your industry or geography creates opportunities for knowledge sharing, problem-solving, and professional development.

The tools and resources described in this chapter represent starting points rather than comprehensive lists. As you progress in your modeling journey, you'll discover resources particularly relevant to your industry, role, or interests. The key is maintaining a learning mindset, continuously seeking to expand your skills, and staying engaged with the broader modeling community.