Worksheet Tips & Tricks

Getting the Most Out of Your Worksheet

Once your worksheet is generated, you are looking at a live, fully functional Google Sheet. This is where the magic happens! Because it’s a standard spreadsheet, you have the ultimate freedom to customize it.

Before you start typing, there are a few Golden Rules to understand, followed by some tips on how to use spreadsheet features to build an incredibly powerful forecast.

The Golden Rules of the Worksheet

1. Never Delete Column A

If you look closely at Column A, you’ll notice a string of letters and numbers for each account. These are unique “Account IDs.” When you eventually click Generate Reports, CompassForecasting scans Column A to know exactly which row belongs to which Xero account.

• Rule: Never delete or type over the IDs in Column A. If you delete an ID, the system won’t know where to put your numbers! (Tip: You can hide the column if you don’t want to look at it, but don’t delete it).

2. We Forecast “Movements”, Not “Balances”

When entering numbers into the grid, you aren’t typing what the final bank balance or loan balance will be at the end of the month. Instead, you are entering the movement (the change) for that specific month. (Note: If your company is registered for GST/VAT, these numbers should always be entered NET / exclusive of tax).

• Positive Numbers Mean “Increase”

  • Revenue: Entering 5000 means you made $5,000 in new sales this month.
  • Expense: Entering 1000 means you incurred $1,000 in new expenses.
  • Asset: Entering 20000 for a Vehicle account means you bought a $20,000 car (the value of your vehicles increased).
  • Loan / Liability: Entering 50000 for a Bank Loan means you received $50,000 in new loan funds (your debt increased).

• Negative Numbers Mean “Decrease”

  • Loan / Liability: Entering -1000 means you made a $1,000 principal repayment on your loan (your debt decreased).
  • Asset: Entering -5000 means you sold off $5,000 worth of equipment (your assets decreased).
  • Expense: Entering -200 means you received a $200 refund from a supplier (your expenses decreased).

3. Where Are My Missing Accounts?

You might notice some accounts from your Xero aren’t visible in the worksheet. This is intentional! The system hides certain accounts because it is calculating them automatically behind the scenes:

• System Accounts: Bank Accounts and Retained Earnings are calculated automatically by the engine based on everything else happening in your business.
• Loan Accounts: If you used the “Loans” step in the Wizard to build an amortization schedule, those loan accounts are hidden here because the system is already handling the monthly payments and interest for you.
• Wizard Accounts: Accounts tied to automated features you set up in the Wizard (like Inventory Assets, Cost of Goods Sold, and Income Tax Expense) are managed automatically.

Pro-Tips & “Funky” Spreadsheet Tricks

Because your forecast lives in a real Google Sheet, you have the full power of spreadsheet magic at your fingertips. Here are a few ways to level up your forecasting:

Insert Rows for Custom Sub-Calculations

Need to calculate payroll? Don’t just guess a flat number! You can insert empty rows right into the worksheet (just above your Wages account) to list out individual employees and their specific salaries. Then, use a simple =SUM() formula in the actual Wages account row to add them all up. (Just remember: leave Column A totally blank for those extra rows you inserted, so the system ignores them when generating reports!)

Use Extra Tabs for Detailed Workings

If your calculations get really complex (like a detailed marketing budget or a multi-tiered sales commission structure), don’t clutter up the main worksheet. Simply create a brand-new tab at the bottom of your Google Sheet. Do all your complex math there, and then link the final monthly totals back to the main Worksheet tab using a simple formula (e.g., ='Marketing Budget'!D25).

Bring in Outside Data with IMPORTRANGE

Do you have another Google Sheet where your sales team tracks their CRM pipeline? Or an operational spreadsheet where you track raw materials? You don’t have to copy and paste that data! Use Google’s =IMPORTRANGE("URL", "Sheet1!A1:B10") formula to automatically pull those live projections directly into your forecast worksheet. As your sales team updates their pipeline, your financial forecast updates automatically.

Modify Pre-Populated Forecasts

If you set your accounts to use an automated forecasting method (like a 12-Month Average or Statistical Forecast), the system will automatically pre-populate the worksheet cells for you. You can still edit these numbers before generating your final reports! For example, if you are running a “Best Case” scenario and want to further increase a pre-calculated revenue line by 10% starting in a future month, simply click into that cell and adjust the formula or value directly on the worksheet.

Use Formulas for “What-If” Scenarios

You can build dynamic formulas right into the grid. Instead of typing 10000 for sales in February, you could type =B2 * 1.05 to say “make February exactly 5% higher than January.” If you ever change January’s number, all the future months will automatically ripple forward and update themselves.