How to build a Google Sheets pro forma template guide

1. Manual ways to build a pro forma balance sheet in Google Sheets

‍

1. Start from a blank Sheet

• Go to https://sheets.google.com and create a new spreadsheet (see Google’s docs on creating spreadsheets: https://support.google.com/docs/answer/6000292).
• Rename the file to something like "Pro Forma Balance Sheet – FY2026".
  ‍

2. Set up core sections

• In column A, create headings in this order: Assets, Current Assets, Non‑Current Assets, Liabilities, Current Liabilities, Long‑Term Liabilities, Equity.
• Under each heading, list line items inspired by templates from SCORE and Macabacus: Cash, Accounts Receivable, Inventory, Fixed Assets, Accumulated Depreciation, Accounts Payable, Short‑Term Debt, Long‑Term Debt, Owner’s Equity, Retained Earnings.
  ‍

3. Create historical and projection columns

• In row 1, add time columns: "Last Actual", "Month 1", "Month 2" … or "Year 1", "Year 2", "Year 3" depending on your planning horizon.
• Enter your last actual balance sheet numbers in the "Last Actual" column, pulling from your accounting system or CPA.
  ‍

4. Add formulas to total and balance

• At the bottom of each section, add SUM formulas for total current assets, total non‑current assets, total assets, total current liabilities, total long‑term liabilities, total liabilities, and total equity.
• Use a check cell: in a dedicated row, create a formula like =TotalAssetsCell - (TotalLiabilitiesCell + TotalEquityCell). Conditional-format it to red if it is not zero so you instantly see if the sheet is out of balance (see conditional formatting docs: https://support.google.com/docs/answer/78413).
  ‍

5. Layer in drivers and assumptions

• Create a separate "Assumptions" sheet to hold drivers: revenue growth %, margin %, days sales outstanding, new debt amounts, planned capex, etc.
• Link those assumptions into your pro forma: for example, project Accounts Receivable based on revenue and DSO, or project debt balances based on planned new loans and amortization.
  ‍

6. Build scenarios manually

• Duplicate the entire pro forma sheet for each scenario: Base Case, Best Case, Downside.
• Adjust assumptions for each copy and review how assets, liabilities, and equity shift.
  ‍

7. Review and iterate

• Share the Google Sheet with your team or clients using the Share button, giving them comment-only access for feedback.
• Use version history (https://support.google.com/docs/answer/190843) to track changes and revert if a formula breaks.

‍

2. No‑code automation methods for your pro forma template

1. Connect live accounting data to Google Sheets

• Use a no‑code data sync tool like Coupler.io, Zapier, or a native connector from your accounting platform to push your latest balance sheet into a "Raw Data" sheet.
• Schedule the sync daily or weekly so your pro forma always starts from fresh actuals.
  ‍

2. Automate assumptions and roll‑forwards with Google Apps Script

• Even if you are not a developer, you can copy simple Apps Script snippets to automate repetitive updates. For example, a script that copies last month’s closing balances into the new projection column and applies growth percentages.
• Learn how to attach triggers so scripts run on a schedule: https://developers.google.com/apps-script/guides/triggers.
  ‍

3. Use named ranges and data validation

• Turn key assumption cells into named ranges (e.g., RevenueGrowth, NewDebtAmount) so formulas are readable and less error-prone.
• Add data validation dropdowns for scenario selection (Base, Upside, Downside). Use IF or CHOOSE formulas tied to that dropdown to dynamically switch between assumption sets instead of maintaining three separate tabs.
  ‍

4. Build a simple dashboard on top

• On a "Summary" tab, use IMPORTRANGE or direct references to pull totals and ratios from your pro forma sheet.
• Add charts for Debt/Equity over time, Cash balance runway, and Total Assets growth so non‑financial stakeholders can understand the story at a glance.

‍

3. Scaling and automating with AI computer agents (Simular)

Here is where you stop being the spreadsheet operator and become the CFO of your own AI back office.

1. Let an AI agent maintain the Google Sheet

• With Sai (https://www.sai.work/), you can spin up an AI computer agent that behaves like a power user on your desktop and browser.
• Give it access to Google Sheets and your accounting portal. The agent can log in, export the latest balance sheet, paste or import it into the "Raw Data" tab, and check that totals reconcile.
• Because Sai is designed for production‑grade reliability and transparent execution, every step it takes in your browser and in Sheets is logged and inspectable. You can review and tweak the workflow until it is bullet‑proof.

‍

Pros: removes 80–90% of the routine updating work; repeatable across many client files; no brittle API integrations needed as the agent simply works through the UI like a human. Cons: you should invest time up front to design and test the workflow.
‍

2. Use an AI agent to generate and compare scenarios

• Task your Simular agent to duplicate the base pro forma tab into new scenario tabs, apply specific assumption changes (e.g., +20% marketing spend, new term loan, headcount plan), and then compute key ratios and flags.
• The agent can compile a "Scenario Summary" sheet comparing all cases side by side, ready for your next leadership or client meeting.

‍

Pros: scenario analysis becomes a one-click task instead of an afternoon in spreadsheets; especially powerful for agencies modeling multiple campaigns or offers for clients. Cons: requires clear prompt design so the agent knows exactly which cells and ranges to duplicate or adjust.
‍

3. Integrate pro forma updates into your wider workflow

• Because Sai exposes webhooks for production pipelines, you can trigger the agent whenever a milestone happens: a new funding round closes, a big contract is signed, or a monthly close completes.
• For example, your CRM or billing system can hit a webhook; Simular spins up an agent, which updates the Google Sheets pro forma, exports a PDF snapshot, and emails it to stakeholders.

‍

Pros: fully hands-off, consistent, and timestamped snapshots for investors and lenders; ideal for fast-growing teams that cannot afford manual refreshes. Cons: you should monitor the first few runs closely and keep a rollback mechanism (using Google Sheets version history) in place.

By combining a solid Google Sheets template with no‑code automation and a Simular AI computer agent, you turn pro forma balance sheet modeling from a fragile one‑off spreadsheet into a scalable, reliable part of your operating system.