Build Horizontal, Trend and Ratio Analysis in Excel with AI
What the source describes
A Journal of Accountancy technology Q&A shows how ChatGPT-4o can create Excel spreadsheets from scratch and analyze uploaded files. In the financial analysis example, the author pasted raw financial data and ChatGPT returned a workbook with tabs for Raw Financials, Horizontal Analysis, Trend Analysis and Ratio Analysis. The author advises being specific in the prompt if you want working formulas rather than values.
Result or outcome
According to the source, ChatGPT produced a four-tab Excel workbook and the author checked the calculations and found them all correct, while stressing that users should verify formulas and outputs.
How to run it
- Export two or more years of the client's income statement and balance sheet to Excel, removing the client name and other identifying details.
- Paste the data into ChatGPT, Claude, Grok or Gemini, or upload the Excel file, then run the prompt on this page, asking specifically for live formulas rather than values.
- Download the workbook and confirm it has separate tabs for raw data, horizontal analysis, trend analysis and ratio analysis.
- Check the calculations against your own figures, and keep asking the assistant to change, add or remove items until the workbook is right.
Try it yourself
Copyable prompt
I am attaching [two / three] years of financial statements for a client, with the business name and other identifying details removed, as an Excel file (or pasted below): [paste income statement and balance sheet]. Build me a downloadable Excel workbook with four tabs: Raw Data, Horizontal Analysis (dollar and percent change year over year), Trend Analysis (each year indexed to [base year] = 100), and Ratio Analysis (current ratio, quick ratio, gross margin, net margin, debt to equity, days sales outstanding). Use live Excel formulas that reference the Raw Data tab, not hard-coded numbers. Label every ratio with its formula. After building it, list the three most significant changes in plain English and note any ratio you could not calculate because data was missing.
Replace the [bracketed] placeholders. Remove client names, account numbers, and personal data before pasting into any AI tool.
What to review
The author checked every calculation before relying on the workbook, and you should too: recompute a few dollar changes, index values and ratios by hand, and click into cells to confirm they hold formulas pointing to the raw data tab. The article warns against entering client or confidential information into ChatGPT, and the same applies to any assistant, so anonymize the statements first.
Related free download: 50 AI Prompts for Bookkeepers (Prompt pack (PDF))
Source: Journal of Accountancy
See also
- Reclassify Miscoded Expenses in Excel with AI (Receipts & Expenses, same source)
Not professional advice. This is general information, not accounting, tax, legal or financial advice. Check AI output yourself and consult a licensed professional for your situation. See our Terms of Use.
This entry summarizes a public source. Features, plans, and availability change, so confirm details with the vendor. Tool names are trademarks of their respective owners.
Related workflows
- Receipts and Expenses
Reclassify Miscoded Expenses in Excel with AI
In this Journal of Accountancy Tech Q&A, a CPA and accounting professor shows an accountant uploading a journal entry log where every expense was coded to Office Supplies. ChatGPT scans the Description column for keywords like laptop and monitor and builds a formula that adds a Reclassified Account column, for example moving those items to Computer Equipment. The author stresses checking the AI's work and never entering client or confidential information into ChatGPT.
Works in ChatGPT, Claude, Grok, Gemini
Microsoft Excel
Journal of Accountancy - Reporting and Analysis
Benchmark a Client P&L Against Industry Norms by NAICS
This TechNews.Tax newsletter post describes how a tax firm reviews Profit and Loss statements from clients who do their own books, using an AI prompt that compares the P&L to typical expense categories and percentages for the client's NAICS code. The prompt asks for a NAICS summary, a category and percentage comparison, a judgment on whether expenses are in line (within a 10% variance), and a risk explanation for outliers. The author uses the OpenAI API but notes the same process works in ChatGPT, Gemini, Copilot or similar tools.
Works in ChatGPT, Claude, Grok, Gemini
TechNews.Tax (newsletter) - Reporting and Analysis
Draft a Client Financial Health Review from an Income Statement
CPA Practice Advisor walks through pasting an anonymized landscaping company's income statement into ChatGPT and Gemini Advanced and asking how financially healthy the business is and what to recommend. Both tools identified the net loss, flagged salaries as a large share of expenses, and suggested cost and revenue actions. The author stresses anonymizing data, getting client permission, having an expert check every answer, and reviewing terms of service.
Works in ChatGPT, Claude, Grok, Gemini
CPA Practice Advisor - Reporting and Analysis
Write Bookkeeping Excel Formulas with ChatGPT
OpenAI's help article on data analysis says ChatGPT can analyze uploaded spreadsheets, including .xls, .xlsx and .csv files, answer questions about the data and create tables or charts. It recommends descriptive column headers in the first row, one row per record, and no empty rows or columns splitting the data. For some tasks ChatGPT writes and runs Python code, and OpenAI says to review the generated code, outputs and assumptions before relying on the result. The article also notes that the number and complexity of formulas in a spreadsheet can affect processing.
ChatGPT, Microsoft Excel
OpenAI Help Center (Data analysis with ChatGPT)
If you want somewhere to check the numbers from this report or analysis, the Small Business Bookkeeping Spreadsheet Bundle is on our sister store.