Welcome to Genius Sheets!
This blog post is for first-time users new to the Genius Sheets platform. With the custom formulas, you can build dynamic and flexible financial models and dashboards.
Where should I start -
The formulas use 3 arguments, the first is the category, the second is the starting date, and the third is the ending date. For the category, this is the EXACT name of the account from QuickBooks, including capitalization and spacing. You can see all of your accounts under the 'List Categories' tab within the add-in. This has the accounts for the Profit and Loss statement, the Balance Sheet, and the Cash Flow Statement. Dates can be referenced using cell references or putting the dates in quotes under this format - "2022/01/01".
Each statement has its own corresponding formula for each of the three.
"=GS.IS(category,startdate,enddate)" - For the Profit and Loss statement.
"=GS.BS(category,startdate,enddate)" - For the Balance Sheet.
"=GS.CF(category,startdate,enddate)" - For the Cash Flow Statement.
While using the formulas you can reference the category directly using quotes, such as "=GS.IS("95 Travel","2020/1/1",). You can copy and paste the exact name of the category from either the List Categories tab or the output from one of the reports. If you leave the end date blank the formula will automatically pull the full month's data for the month the start date is from.
You can also build your formulas using only cell references. For example, the above formula could have the category and date stored in cells within your spreadsheet, and you can use cell references to build the formula more dynamically.
This might look like "=GS.IS(A10,B12,)".
When using cell references you can also lock the row or column using the $ sign. If your categories are vertical, meaning each new row contains a category, you can lock the formula with the dollar sign before the A - "=GS.IS($A10, B12,)".
Similarly, if you have the dates going across your spreadsheet the long way, lock the dates with the $ before the 12 - "=GS.IS($A10, B$12,)".
By locking the references, you can drag your formula both across and down your spreadsheet to continue pulling from the correct category and/or date.
Combining line items
One of the largest benefits to using formulas is now you can combine your line items and categories in ways you wouldn't want to within your account software. To combine line items you can use multiple GS formulas as part of the same function.
"=GS.IS("A$10,B$12,)+GS.IS("95 Travel",B$12,)".
Now you can have multiple categories consolidated into one cell in your financial model or report. This works across the Balance Sheet, Cash Flow Statement and Profit and Loss formulas.
