ARTICLES
Subscribe
ARTICLES
Subscribe

Unlocking Excel with AI: Streamlining Financial Analysis

Arthur Lee

July 19, 2024

AI-Powered Analysis of Excel Workbooks

As a former financial analyst, I know that one of the common challenges you face is inheriting an Excel workbook that you need to understand and maintain. Despite any existing documentation, it often may take hours to dive into the formulas, understand the structure, and make necessary adjustments. Imagine if there was a way to expedite this process using AI and LLM (Large Language Models).

I recently tackled this problem using a publicly available workbook from Exinfm. Here's how I leveraged AI to gain insights and expedite the understanding of the workbook:

The Problem

You open an inherited Excel workbook and see a maze of sheets, cells, and formulas. To fully grasp its purpose and functionality, you usually spend hours dissecting each formula, understanding the flow of data, and figuring out the dependencies. This process can be incredibly time-consuming and prone to errors.

The Solution: AI-Powered Analysis

By integrating AI with Excel, we can significantly reduce the time required to understand a complex workbook. Here's a step-by-step look at how I did it (note I am using the paid version of ChatGPT - you could in theory do this with a locally run LLM):

1. Extract Metadata:

I used Python and the openpyxl library to extract metadata from the Excel workbook. This included sheet names, cell formulas, data types, and dimensions.

2. Analyze Metadata with GPT-4:

Leveraging the power of OpenAI's GPT-4, I created a detailed analysis of the workbook's structure, purpose, and formulas. This provided an immediate understanding of the key elements and their interconnections.

Here’s the code snippet I used:

import os
import openpyxl
from openai import OpenAI

# Set your OpenAI API key
client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))

# Function to extract metadata from the Excel workbook
def get_workbook_metadata(file_path):
    workbook = openpyxl.load_workbook(file_path, data_only=False)
    sheet_names = workbook.sheetnames
    metadata = {"Sheet Names": sheet_names, "Sheets": []}

    for sheet_name in sheet_names:
        sheet = workbook[sheet_name]
        sheet_info = {
            "Sheet Name": sheet_name,
            "Dimensions": sheet.dimensions,
            "Formulas": [],
            "Data Types": []
        }
        for row in sheet.iter_rows():
            for cell in row:
                if cell.data_type == 'f': # Cell contains a formula
                    sheet_info["Formulas"].append({"Cell": cell.coordinate, "Formula": cell.value})
                sheet_info["Data Types"].append({"Cell": cell.coordinate, "Data Type": cell.data_type, "Value": cell.value})
        metadata["Sheets"].append(sheet_info)

    return metadata

# Function to analyze metadata with GPT-4
def analyze_metadata_with_llm(metadata):
    prompt = f"""
    You are an AI trained to analyze Excel workbooks. Given the following metadata, provide a summary and explanation of the workbook's structure, the purpose of each sheet, and the meaning of any formulas found.

    Metadata: {metadata}

    Provide your analysis in a detailed and understandable manner.
    """

    response = client.chat.completions.create(
        model="gpt-4o",
        messages=[
            {"role": "system", "content": "You are an AI that provides detailed analysis of Excel workbooks."},
            {"role": "user", "content": prompt}
        ],
        max_tokens=1500,
        temperature=0.7
    )

    return response.choices[0].message.content

# Example usage
if __name__ == "__main__":
    file_path = 'workbook.xlsx'
    metadata = get_workbook_metadata(file_path)
    analysis = analyze_metadata_with_llm(metadata)

    # Print analysis
    print(analysis)

The Results

By running the provided workbook through this code, I received a detailed analysis that included:

  • Sheet Structure: Understanding the layout and purpose of each sheet.

  • Formula Analysis: Detailed explanations of key formulas and their roles.

  • Data Flow: Insights into how data moves across the workbook.

Here are the results from the code:

Excel Workbook Analysis

Based on the metadata provided, we have a single sheet named CapBudgWS. This sheet appears to be designed for the financial analysis of a project, focusing on equity analysis. Here's a detailed analysis of the structure, purpose, and key formulas.

Sheet: CapBudgWS

Structure and Purpose

  1. Header Information (Rows 1-2)

    • A1: "Equity Analysis of a Project" – Indicates the overall purpose of the sheet.

    • E2: "INPUT SHEET: USER ENTERS ALL BOLD NUMBERS" – Instructs the user that bold numbers are input fields.

  2. Input Section (Rows 3-15)

    • Columns A-B/C: Contains labels and values for various financial inputs such as initial investment, revenues, discount rates, tax rates, etc.

    • Key inputs like Initial Investment, Revenues in year 1, Opportunity cost, Discount rate, etc., are provided in this section.

  3. Investment Calculations (Rows 24-31)

    • B25: =C4 – Initial investment.

    • B26: =C4*C9 – Tax credit.

    • B27: =B25-B26 – Net investment.

    • B28: =C13 – Initial working capital.

    • B29: =C5 – Opportunity cost.

    • B30: =C10 – Other investments.

    • B31: =B27+B28+B29+B30 – Total initial investment.

  4. Salvage Value Calculations (Rows 33-35)

    • C34-L34: =IF(C23=C6,C7,0) – Salvage value of equipment.

    • C35-L35: =IF(C23=C6,(C13+SUM(C48:L48))*C15,0) – Salvage value of working capital.

  5. Revenue and Expense Projections (Rows 38-50)

    • C38-L38: =IF(C23>C6,0,1) – Lifetime index, indicating if the year is within the investment's lifetime.

    • C39-L39: =G4 and subsequent calculations – Projected revenues.

    • C40-L40: =C39*G5 – Variable expenses as a percentage of revenues.

    • C41-L41: =G6 and subsequent calculations – Fixed expenses.

    • C42-L42: =C39-C40-C41 – EBITDA.

    • C43-L43: =C61 – Depreciation.

    • C44-L44: =C42-C43 – EBIT.

    • C45-L45: =C44*G7 – Taxes.

    • C46-L46: =C44-C45 – EBIT after tax.

    • C47-L47: =C43 – Adding back depreciation.

    • C48-L48: =(C14*C39-B28)*C38 – Change in working capital.

    • C49-L49: =C46+C47-C48 – Net after-tax cash flow (NATCF).

  6. Discounted Cash Flow and Investment Measures (Rows 50-56)

    • C50-L50: =C38*(1+K11)^C23 – Discount factor.

    • C51-L51: =(C49+C34+C35)/(1+K11)^C23 – Discounted cash flows.

    • C54: =SUM(B51:L51) – Net Present Value (NPV).

    • C55: =IRR(B49:L49,K11) – Internal Rate of Return (IRR).

    • C56: =SUM(C46:L46)/SUM(B62:K62) – Return on Capital (ROC).

  7. Depreciation and Book Value (Rows 59-62)

    • C60-L60: =C4 and subsequent calculations – Book value at the beginning of each year.

    • C61-L61: =IF(C8=1,((C4-C7)/C6)*C38,... – Depreciation calculation based on the method chosen (Straight line or Double Declining Balance).

    • C62-L62: =C60-C61 – Book value at the end of each year.

Key Formulas and Their Meanings

  1. Discount Rate (K11):

    =IF(K4=1,K5,(K7+K6*K8)*(1-K9)+K10*(1-G7)*K9) – Determines the discount rate based on whether a direct approach or CAPM is used.

  2. Revenue Growth and Projections:

    C39: =G4 – Initial revenue.

    D39: =C39*(1+D19)*D38 – Revenue growth based on the growth rate in D19 and the lifetime index.

  3. Investment Measures:

    NPV (C54): =SUM(B51:L51) – Sum of discounted cash flows.

    IRR (C55): =IRR(B49:L49,K11) – Internal Rate of Return.

    ROC (C56): =SUM(C46:L46)/SUM(B62:K62) – Return on Capital.

  4. Depreciation Calculation (C61):

    =IF(C8=1,((C4-C7)/C6)*C38,... – Calculates depreciation based on chosen method (Straight line or Double Declining Balance).

Conclusion

The CapBudgWS sheet is designed for detailed financial analysis of a project, focusing on calculating key investment metrics such as NPV, IRR, and ROC. It involves:

  • Inputting initial financial data.

  • Calculating various expenses and revenues over the project's lifetime.

  • Evaluating the project's financial viability using metrics like NPV and IRR.

The use of detailed formulas ensures that the calculations are dynamically updated based on user inputs, providing a robust tool for equity analysis of potential investments. Additionally, you can use an alternative approach by directly uploading the Excel file to ChatGPT and getting a detailed analysis without writing any code. You can see an example here.

Conclusion

The power of AI and LLMs in handling complex Excel workbooks is amazing. By automating the analysis process, financial analysts can save time, reduce errors, and focus on higher-level tasks. This approach enhances productivity and improves the accuracy of financial analysis.

Imagine the possibilities when this technology becomes a standard tool for every financial analyst. The future of financial analysis is here, and it's powered by AI.

<All Posts