Bookkeeping » History » Revision 39
Revision 38 (Jon Goldberg, 07/05/2024 12:01 AM) → Revision 39/40 (Jon Goldberg, 07/06/2024 06:41 PM)
{{last_updated_at}} by {{last_updated_by}}
# Bookkeeping
Megaphone Technology uses InvoiceNinja for A/R and Quickbooks Desktop for bookkeeping.
### Transfer client data from Invoiceninja
* Get clients from InvoiceNinja. IN **Settings » Import/Export » Export Clients to CSV**. Open in LibreOffice Calc.
* In Quickbooks, **Lists menu » Add/Edit Multiple List Entries**. List is **Customers**. Paste new clients in the *Name* and *Company Name* columns.
* Set the list *View* to **All Customers**. Otherwise it's not clear which Invoiceninja clients are new vs. already archived in Quickbooks.
* *Work to Reassign* isn't a real client, don't transfer to Quickbooks.
#### Automated invoice/credit/payment import
This is new in 2023. Manual steps are in older versions of this wiki page.
* Note the last recorded invoice in Quickbooks. You can find that by clicking on **Create Invoices** from the home screen, and clicking "back" once.
* Go to Invoiceninja **Reports** tab. Select *Type* of **Invoice**, the date range of invoices we need, and press **Export** (to CSV).
* Rename the file to `invoices.csv` and place it in the `invoiceninja-to-iif/input` folder.
* Run `invoices.php`.
* Import the resulting IIF file into Quickbooks.
Do the same for payments ("Record Payments" in QB, file is `payments.csv`, run `payments.php`).
Same for credits - but manually add the `Date` field to `credits.csv` in ISO format.
### Cross-check: Compare outstanding client credits/debits in Quickbooks/Invoiceninja
* QB's list is easily available by pulling up the customer center.
* You can see Invoiceninja balance from the [clients](https://invoices.megaphonetech.com/clients) screen. **Note:** If the client has a *credit*, this will NOT show up on the client screen (this may be a bug). If the numbers don't match Quickbooks, click into the client to look for a credit.
* This is also a good time to mark QB customers inactive that have been marked inactive in Invoiceninja. Double-click their name and check off "customer is inactive".
### Record the deposits - Checks
Using the bank statements, you'll now record deposits.
**Note:** The vast majority of checks are e-deposited, so each "deposit" is a single check.
* From the QB home screen, click "Record Deposits".
* Sort by payment method, we're only looking at *Checks*.
* Put a checkbox next to the first check, press **OK**.
* Find the check on the bank statement (will be listed as *Mobile Check Deposit*), enter the deposit date in Quickbooks.
* Sometimes one check pays multiple invoices, so you need to put them in the same deposit.
* Press "Save and New", repeat until you're done.
* If your numbers are off, look at "Checks Deposited".
### Record the deposits - ACH
ACH is entered into Invoiceninja from the bank statement, so you can just deposit these without checking the bank statement.
Make the deposit date the same as the payment date. If there are 2 payments on the same date from the same vendor, combine them.
Do *not* combine ACH payments from different vendors.
### Record the deposits - Wire transfers
* From the QB home screen, click "Record Deposits".
* Locate wire transfers on the bank statements.
* Check off the corresponding payment in Quickbooks.
* On the next blank line, set the *Account* to "Bank Service Charges* and enter a *negative* amount that equals the service charge.
* Enter the deposit date from the bank statement, then press "Save and New" until you're done.
### Record the deposits - PayPal
* From the QB home screen, click "Record Deposits".
* Log into Paypal.com, go to **Activity > All Reports** menu, then **Activity Download**.
* Run a report of type **Balance Affecting** for the appropriate time period, download as a CSV.
* Check off the corresponding group of payments in Quickbooks.
* On the next blank line, set the *Account* to "Bank Service Charges* and enter a *negative* amount that equals the service charge. Note that all Quickbooks fields allow you to do arithmetic, so you can put in the amount from the statement minus the total of payments.
### Record the deposits - Stripe
* From the QB home screen, click "Record Deposits".
* Log into Stripe.com, go to *Balances » Payouts*
* Page forward until you have the first payment you haven't accounted for. You may have to poke around a bit (see below for how)(TODO: Is there an easier way to see last Stripe deposit?)
* Right-click an entry in the Balances table to see details.
* "Transactions" will list all the payments you need to check off in Quickbooks.
* The "Completed" date at the top is the date for the deposit.
* On the next blank line, set the *Account* to "Credit Card/e-Check Fees* and enter a *negative* amount that equals the "Fees" listed in the "Summary" section.
* Press "Save and New" until you're done.
### Import Payroll data
The bank statement can't be used because we need to record the gross wages and the employer taxes, but the bank statement lines are net wages and all taxes.
* First, find the last payroll imported. In QB's Report Center, run a Profit and Loss Detail report. (make sure your report covers the necessary date range).
* In Gusto, go to **Payroll >> Payroll History**. Scroll down to "Payroll Expenses". Note the most recent date
* Find the payroll summary dated 2-3 days later. Click **View Details**.
* Near the top will be an **Export** link. Click it for this and all more recent payroll history details.
* Copy all the exported IIF files to a folder accessible to Quickbooks.
* Import them one by one (unless we figure out how to be more clever) from **File >> Utilities >> Import, IIF files**. Keyboard shortcut is `Alt-F, U, I, I`.
* Reimbursements will be imported under "Office Supplies". Drill down in the P&L Detail on all office supplies to enter what the payment was for, and optionally to change the category (e.g. books fall under Training Expenses).
### Record accounts payable
This is *semi-automated*. The "payables.php" script will handle
Using the bank statements and/or the Amalgamated website, we can put all the payments it can recognize, into Quickbooks. Also a bit long/tedious.
*Note*: Ignore all "Gusto/NET" and "Gusto/TAX" since we handled those above but many are one-offs to be entered manually. We must download transactions from Amalgamated, which only go back 90 days. If any time period is missing, they must be manually entered from the bank statement. record "Gusto/FEE".
* From the home screen, click "Write Checks". Click on "Previous" to note the last transaction recorded in QB. QB
* Find Record payments on the downloaded transactions in the "bank statements" folder. They will look like this: `Export-01092023.csv`. The date format is odd - this one is from September 1, 2023.
* Open the CSV and remove and transactions that overlapped a previous export. Save. Or better yet, concatenate the CSVs.
* Copy the file to `~/local/invoiceninja-to-iif/input/payables.csv`.
* Run `php payables.php`.
* This will generate two files in `output`: `payables.iif` and `unknownpayables.csv`.
* Review the unknown payables. If any are an expense that recurs, modify `determineVendorAndAccount()` in `payables.php` to reflect that and re-run the script.
* Import the IIF.
* Handle the unknown payables (and any transactions we missed in the CSVs) as follows.
#### Recording "unknown" payables
Using the bank statements and/or `unknownpayables.csv`:
*Note*: If working off the bank statement, make sure we ignore those handled above - "Gusto/NET" and "Gusto/TAX", "SERVICE CHARGE FOR WIRE TRANSFER". But we DO record "Gusto/FEE".
* From the home screen, click "Write Checks".
* Record payments: statements.
* Make sure payments are categorized correctly.
* When setting up a new vendor, don't do Quick Add; enter a default category for the payment.
* Make sure the date and check number is correct.
* The check number for debit card transactions is "DB". The check number for direct transfers from the bank account is "EFT".
* Put any memos from the check into the memo field in QB.
### Reconcile the bank acccount
Bank reconciliation (aka "bank rec") is a cross-check to ensure that your ending balances in the bank account match that of Quickbooks. You do it once for each physical bank statement you have - though you can also just do it through any date if you know what the ending balance on that day was.
* On the home screen, press the "Reconcile" button.
* Enter the Statement Date and the Ending Balance, press "Continue".
* Check off all the debits and credits that are on or before the statement ending date. If you're lucky, it'll match, and your difference will be $0.00.
** If not, go through the items one by one (or bifurcate!) and find the discrepancies and correct them.
### How to handle unusual transactions in Quickbooks:
* Tax refund: Make Deposits (Rec'd from: NYS Tax and Finance, From Account: Taxes, Memo: NYS Tax Refund, Payment Method: Check, Amount: $73)
* Bounced checks (to us, not from us: http://support.quickbooks.intuit.com/support/articles/HOW12221)
* Payroll Tax Adjustment (bank statement shows GUSTO PAYROLL/TAX): Go to Gusto **Reports >> Taxes and Compliance >> Tax Reconciliation**. Find the correct one and View Details twice. You'll find, e.g., Dennis, NYS SUI adjustment, -$12. Go to **Company >> Make General Journal Entries**. Debit the bank (Amalgamated) the $12. Credit Payroll Taxes $12. If we owe additional (i.e. not a refund) flip the debit/credit.