Reverse Engineering Financial Models

A Step-by-Step Playbook for Immediate Implementation

There's been a lot of talk about how planning software can replace the need for a three-statement model. However, when it comes to ad hoc analysis, professionals often need to replicate the model from the software, and they may not know how to do that. That's where three-statement modeling skills become invaluable.

Reverse engineering a financial model is a powerful technique that can help you recreate and understand complex models, even those embedded in planning software. In this playbook, I'll guide you through the steps to reverse engineer a financial model, with practical steps you can implement right away. We'll break down financial statements, reconstruct the model, and validate it to ensure accuracy.

By the end, you'll be equipped with the knowledge to reverse engineer a financial model and apply these skills to your own work, making those ad hoc analyses a breeze. Let's get started and turn those financial puzzles into clear, actionable insights.

Understanding Reverse Engineering in Finance

Reverse engineering in finance means taking apart an existing financial model to understand how it works. It's like working backwards to see how all the pieces fit together.

There are a few key reasons why you might want to do this.

• First, it helps you replicate competitor models. If you can understand how a competitor built their model, you can apply similar strategies to your own.
• Second, reverse engineering helps validate your assumptions. By breaking down a model, you can see if the numbers and logic hold up. This makes your analysis more reliable and accurate.
• Third, for ad-hoc analysis and scenario building. You can reverse build a financial model embedding scenarios to it to test different strategies that will facilitate the decision making.

In short, reverse engineering is a handy skill that lets you decode and understand complex financial models, making it easier to build your own or improve existing ones.

Preparing Your Tools and Data

To reverse engineer a financial model, you need the right tools and data.

Let's start with the tools.

Some tools you may need include:

1. Excel: It's the go-to tool for financial modeling. Make sure you know how to use formulas, pivot tables, and charts.
2. Power BI: This helps you visualize data and create dynamic reports. It's great for spotting trends and insights.
3. SQL: If your data is stored in databases, knowing some SQL can help you extract the data you need quickly and efficiently.

The more data you have, the more complete your model will be. Look for:

1. Financial Statements:
   • Income Statement: Look for the company’s revenue, expenses, and profit over a period.
   • Balance Sheet: Find details on the company’s assets, liabilities, and equity.
   • Cash Flow Statement: Check for cash inflows and outflows from operations, investing, and financing.
   Start by downloading these documents from the company's website or financial databases like Bloomberg or Yahoo Finance.
2. Market Data:
   • Industry Reports: Gather information about the industry to understand external factors affecting the company.
   • Competitor Data: Look for financial data from competitors for benchmarking.
   You can get market data from financial news websites, industry publications, or specialized databases like Statista.
3. Internal databases, if you have access to them:
   • Volume data - This is a key information when determining revenue and cost
   • Expenses reports - the details in expenses may not be as critical as knowing the totals, but they can add a great dimension when analyzing the company’s financials. It’s important to differentiate expenses from costs from Investments.
   • General Ledgers - this is the lowest level of details you can get, so if you can put your hands in the general ledger report, you will be able to easily understand how actuals are mapped to the Financial Statements and replicate them to your projections. If your general ledger is not mapped out, that’s an extra step you will need to take.
   • Operations databases - This database will show you things like inventory usage and constraints. It will help you create realistic assumptions to your model.

Breaking Down the Three Statements

Dividing the financial statements into a few sections will help in understanding how the model was built and what are its connections. The ones I mention here are the most common sections you may find on a financial model but have in mind that depending on the model or your goals, you could have more sections to dive deeper.

1. The Business Model

Your first step is to understand how the company makes money and what are their costs . Look for:

• Revenue Streams: Identify the primary sources of revenue. Are they selling products, offering services, or both? Check the income statement for details.
• Cost Classifications: Differentiate between direct costs (cost of goods sold) and indirect costs (operating expenses). This information is also available in the income statement.

For Reverse Engineering a Competitor Model: Study the competitor’s revenue streams and cost structures. Compare them with industry benchmarks from market data. For Validating Assumptions: Ensure your revenue and cost assumptions align with actual financial statements. For Ad Hoc Analysis: Use historical data to project future revenue and costs under different scenarios.

There’s a large possibility that you won’t have all the information needed to match the exact financials the company shows in the statement. Make an effort to come up with assumptions and get as close as possible to a reasonable calculation of the revenue and cost.

2. Expenses

Detailing the expenses will help achieve a better understanding of the model. Consider:

• Operating Expenses: Include salaries, rent, utilities, and marketing. Break them down as much as possible.
• SG&A Expenses: Selling, General, and Administrative expenses. These can be found in the income statement.

For Reverse Engineering a Competitor Model: Look for detailed expense reports in public filings or industry reports. For Validating Assumptions: Cross-check your projected expenses with historical expense patterns. For Ad Hoc Analysis: Adjust expenses to see the impact of cost-saving measures or increased spending.

3. Operations

Understand the company's operational efficiency by looking at:

• Payables and Receivables: Found in the balance sheet under current liabilities and current assets, respectively. These show how quickly the company pays its suppliers and collects money from customers.
• Inventory: Also in the balance sheet, inventory levels can indicate how well the company manages stock.

For Reverse Engineering a Competitor Model: Compare the company’s operational metrics with those of competitors. For Validating Assumptions: Ensure your assumptions about payables, receivables, and inventory turnover are realistic. For Ad Hoc Analysis: Test different scenarios for inventory management and payment cycles.

You can calculate KPI’s for these lines based solely on the Balance sheet actuals. Add more details if you have them, such as more information on inventory or product lines. If you have a specific Business model, make sure to incorporate their operations model now, for example, SaaS business can operate completely differently than a manufacturing company. Adapt your model accordingly.

4. Assets and Investments

Identify the company's investments and asset structure:

• Fixed Assets: Look for property, plant, and equipment in the balance sheet.
• Investments: Include any financial investments or acquisitions, found in both the balance sheet and cash flow statement.

Determine how much the company is investing every year. Take into account the depreciation of the assets already owned. Depending on the company, it’s easy to guess the type of assets and the life of the assets groups, but if this is not an information you will need in your model, you can also use a KPI to project these lines, such as assets turnover. That will simplify the process and will result in a similar outcome.

For Reverse Engineering a Competitor Model: Analyze the competitor's asset base and investment strategies. For Validating Assumptions: Match your model’s asset growth with historical data. For Ad Hoc Analysis: Evaluate the impact of new investments or asset disposals.

5. Capital Structure

Understand how the company is financed:

• Debt: Check the balance sheet for short-term and long-term debt.
• Equity: Look at common stock, retained earnings, and other equity items in the balance sheet.

For Reverse Engineering a Competitor Model: Study the competitor’s debt-to-equity ratio and other financing details. For Validating Assumptions: Align your model’s financing assumptions with historical financing patterns. For Ad Hoc Analysis: Assess the impact of changes in debt levels or equity financing.

6. Other Information

Gather additional data to complete the model:

• Tax Rate: Found in the income statement or notes to the financial statements.
• Interest Rate on Debt: Check the notes to the financial statements or interest expense in the income statement.
• Number of Shares: Look for this in the equity section of the balance sheet or in the notes.
• Dividend Information: Found in the cash flow statement and the notes to the financial statements.

For Reverse Engineering a Competitor Model: Compare these metrics with those of competitors. For Validating Assumptions: Ensure your tax rate, interest rate, and share count assumptions match historical data. For Ad Hoc Analysis: Test the impact of changes in tax rates, interest rates, or dividend policies.

Determine if there are other gaps you will need to fill in order to have a complete model. You will know that by looking at the lines in the three statements and finding if you left any line uncovered or if you are not arriving to the same figures shown in the statements. Could there be a line of products or an additional revenue stream you are not considering? Or maybe there are other costs or expenses that you didn’t map in your data? Perhaps the company has more debt than you have considered? Try to close these gaps with assumptions for the moment. You will have the opportunity to test the model later and determine if these assumptions hold true or if you need more information.

Validating the Model

After breaking down the three financial statements and organizing your data, the next step is to validate and fine-tune your reverse-engineered model. This ensures that your model accurately replicates the original and can be reliably used for analysis. Here’s how you can validate and fine-tune your model:

1. Cross-Check with Historical Data

Compare Your Model's Output with Actuals:

• Income Statement: Ensure your calculated revenue, expenses, and net income match the historical income statement data.
• Balance Sheet: Verify that assets, liabilities, and equity in your model align with the historical balance sheet figures.
• Cash Flow Statement: Check that cash flows from operations, investing, and financing match the historical cash flow statement.

Steps to Take:

1. Historical Data Integration: Input historical financial data into your model and compare each line item against your projections.
2. Variance Analysis: Calculate the variance between your model's outputs and the actual historical figures. Identify and understand any significant discrepancies.

2. Sensitivity Analysis

Test the Impact of Key Assumptions:

• Revenue Assumptions: Adjust key drivers like sales growth rate, price changes, and market share to see their impact on revenue.
• Cost Assumptions: Modify cost drivers like COGS percentage, operating expenses, and fixed/variable cost ratios.

Steps to Take:

1. Scenario Planning: Create different scenarios (e.g., best case, worst case, base case) and adjust your model inputs accordingly.
2. Impact Assessment: Analyze how changes in assumptions affect your financial statements and key metrics.

3. Reconcile Differences

Identify and Correct Discrepancies:

• Mapping Errors: Ensure that all accounts from the general ledger are correctly mapped to the financial statements.
• Missing Data: Fill in any gaps in data that may cause discrepancies.

Steps to Take:

1. Detailed Review: Go through each line item and ensure all data is accurately reflected in your model.
2. Update Assumptions: Adjust your assumptions based on any new data or insights gained during the validation process.

4. Use Benchmarking

Compare Against Industry Benchmarks:

• Industry Averages: Compare your model’s outputs to industry averages for key metrics like profit margins, return on assets, and debt ratios.
• Competitor Analysis: Benchmark against competitors to ensure your model’s outputs are realistic and within industry norms.

Steps to Take:

1. Collect Benchmark Data: Gather industry and competitor benchmarks from reliable sources.
2. Adjust Model: Fine-tune your model inputs to align with industry standards and competitor performance.

5. Peer Review

Get a Second Opinion:

• Expert Review: Have colleagues or experts review your model for accuracy and completeness.
• Feedback Incorporation: Use their feedback to refine and improve your model.

Steps to Take:

1. Model Sharing: Share your model with peers or mentors and request a detailed review.
2. Implement Feedback: Incorporate their suggestions and corrections into your model.

6. Automate Error Checking

Implement Controls and Validations:

• Data Validation: Set up rules in Excel to ensure data entered meets expected criteria (e.g., positive numbers for revenues).
• Error Flags: Use conditional formatting to highlight potential errors or inconsistencies.

Steps to Take:

1. Validation Rules: Create validation rules for key inputs and outputs to prevent data entry errors.
2. Error Identification: Set up error flags to quickly identify and address discrepancies.

7. Continuous Improvement

Regularly Update and Refine Your Model:

• New Data Integration: Continuously update your model with new data as it becomes available.
• Model Refinement: Regularly review and refine your model to improve accuracy and reliability.

Steps to Take:

1. Data Updates: Schedule regular updates to incorporate the latest financial data and market information.
2. Model Review: Periodically review your model’s structure and assumptions to ensure they remain valid and relevant.

The Tactic Framework in reverse

If you adopt the Tactic framework for creating financial models, reversing engineering a three-statement model will be easier. You still start by the Target question which is the base of the model.  However, your Target question could be different from the one the model originally attempted to answer.  

Your target question will likely be around the reason you need to reverse engineer a model. The answer will help you determine what parts of the model you need to focus with more details ad what other parts you can use assumptions to arrive at figures close to what you have in the statements.

Continuing with the Tactic Framework, after determining the target question, list the data you would normally need to calculate the areas of main focus.  Check if you have all data, you will need and if you don't, try to fill the gaps with data about the market, such as industry's averages, competitors' data, etc.

You will need to recreate all the calculations (that's the C of the framework).  They can get very close to the calculations used originally to build the model, but if you arrive in very different results, iterate with the model until you have accounted for all the parts of the calculation.  You will know you've arrived at the right point when you can validate the insights ( letter I in the framework).  To validate the insights, you will need to build tools such as sensitivity analysis and scenarios.   Find more about the Tactic Framework.

Conclusion

Reverse engineering a financial model is a powerful skill that can help you understand, replicate, and improve complex financial models. Let’s recap the key steps and takeaways:

1. Understanding Reverse Engineering in Finance: We started by defining reverse engineering and exploring its key benefits, such as replicating competitor models, validating assumptions, and conducting ad hoc analyses.
2. Preparing Your Tools and Data: We highlighted essential tools like Excel, Power BI, and SQL. We also discussed gathering comprehensive financial statements, market data, and internal databases to build a robust model.
3. Breaking Down the Three Statements: We divided the financial model into key sections: business model, expenses, operations, assets and investments, capital structure, and other relevant information. Each section provided detailed guidance on understanding and analyzing the company’s financials.
4. Validating and Fine-Tuning the Model: We emphasized the importance of cross-checking with historical data, conducting sensitivity analysis, reconciling differences, benchmarking, peer review, automating error checking, and continuously improving the model.

By following this playbook, you will be well-equipped to reverse engineer any financial model, making it easier to replicate competitor models, validate your own assumptions, and conduct detailed ad hoc analyses. This skill is invaluable for financial professionals looking to gain deeper insights and make more informed decisions.

I encourage you to apply these steps to your own work. With practice, you'll become proficient in reverse engineering financial models, turning complex financial puzzles into clear, actionable insights.