How to sync stock to QuickBooks?
This article describes how to record stock levels in QuickBooks based on Katana data.
Katana x QuickBooks integration enables creating invoices and bills in QuickBooks based on Katana Sales and Purchase Orders with a click of a button. The integration does not enable syncing stock levels automatically from Katana to QuickBooks, but you can easily record journal entries periodically in QuickBooks based on Katana data to update your stock.
Depending on your accounting principles and income statement structure, there are different ways to handle stock sync:
1. Not tracking stock in QuickBooks at all.
2. Recording journal entries in QuickBooks periodically in case you are tracking Cost of Goods Sold in your income statement.
TIP: Katana provides real-time stock management and statuses, so if it is not required by accounting principles, you might not need to track stock in QuickBooks at all and could only rely on Katana for stock management. Even if you are used to tracking stock in QuickBooks today. ;)
Approach 1: not tracking stock in QuickBooks
If not required by your local accounting principles and rules, we suggest not tracking stock levels in QuickBooks at all. Only use Katana for tracking and managing your stock levels for products and materials.
- You will always have a real-time stock status available in Katana, no need to duplicate it in QuickBooks.
- Easy to manage as you do not need to record any stock-related entries in QuickBooks. Just record sales and expenses in QuickBooks when they occur.
- You might be able to use a cheaper QuickBooks package that does not include any stock management functionalities.
- Cannot be used if stock tracking is required by local accounting principles.
Approach 2: tracking stock in QuickBooks and using an income statement schema with Cost of Goods Sold
If you need to track stock in Quickbooks, you can periodically create manual journal entries based on information available in Katana to keep your stock updated in QuickBooks. The frequency of making those journal entries depends on your requirements and preferences. It can be done weekly, monthly, quarterly or yearly, for example.
If you are using an income statement schema where Cost of Goods Sold is recorded, then create the below journal entries to update your stock in the balance sheet and expenses in the income statement.
In the following example, we are making adjustments on a monthly basis and, before the journal entries, have not recorded any stock or Cost of Goods Sold for the period. We have recognized all material and salary expenses when they occurred. Our Income Statement in QuickBooks for last month looks like this:
And balance sheet as at the end of the period:
Required journal entries in QuickBooks
- If you have recorded all purchased items directly as expenses in QuickBooks during the period, add all purchased items to stock.
- In QuickBooks, go to "Reports", generate a "Profit and Loss" statement for last month and find the account related to purchased materials. In our example, it's the "Purchases (materials)" account with the total amount of $40,367.80 (see above).
b. Create a journal entry for increasing the stock and decreasing material expenses by the total amount for the period.
2. If you are recording material and finished product stock on different accounts in QuickBooks, then decrease the material stock by the amount used in production during the period and increase the finished product stock.
Note: if you have a single account for all stock in your balance sheet, then you can skip this step.
a. In Katana, go to "Make" screen -> "Schedule" and navigate to "Done" Manufacturing Orders section. Filter the "Done" orders by "Done date", keeping only orders for "Last month". Find the "Materials" cost for those manufacturing orders.
b. In QuickBooks, create a journal entry for decreasing the stock for materials, and increase the stock for finished products by the same amount.
Note: if you are recording material and subassembly stock on different accounts in QuickBooks, look for further instructions below.
3. Add operations cost (e.g. employee salaries) to the finished product stock cost in QuickBooks. Decrease the salary cost in the income statement and increase the finished product stock by the relevant amount.
a. In Katana, on the same "Done" manufacturing orders screen where you have filtered out orders completed in the last month, find the "Operations cost" related to those orders.
b. In QuickBooks, create a journal entry for decreasing the salary cost in the income statement and increasing the stock cost for finished products by the same amount.
4. Decrease the stock of finished products by the amount sold during the period and increase Cost of Sales account in the income statement.
a. In Katana, go to "Sell" screen and navigate to the "Done" sales orders list. Filter the orders by "Picked date", keeping only orders for "Last month". Find the "COGS" amount for those sales orders.
b. In QuickBooks, create a journal entry for decreasing the stock of finished products and increase the Cost of Sales.
5. Create a journal entry to reflect stock movements related to stock adjustments in Katana. For example, let's assume that a product with an Average Cost of 886.9 USD got broken and a stock adjustment was created to reduce the stock in Katana.
a. In Katana, go to "Stock" screen and "Stock adjustments" list. Filter the adjustments by "Adjusted date", keeping only adjustments for "Last month". Find the "COGS" amount for those sales orders.
b. In QuickBooks, create a journal entry for adjusting the stock and record income or cost related to the case.
This is it!
After the journal entries, your income statement for the last month would look like this...
... and your balance sheet like this:
What if I am using subassemblies?
If you are using subassemblies and a separate account for subassembly stock in QuickBooks, you would, firstly, need to convert materials into subassemblies and then those subassemblies to finished products. You would need to do some additional steps that are similar to recording movements for materials and finished stock.
1. If you are recording material and subassembly stock on different accounts in QuickBooks, then decrease the material stock by the amount used in subassembly production during the period and increase the subassembly stock. Filter the "Done" manufacturing orders list to keep only subassemblies produced during the period, and then record the respective stock movement in QuickBooks from the material stock to subassembly stock. Check the total materials cost for subassembly manufacturing orders and convert the materials to subassemblies in QuickBooks in the respective amount (similar to step 2 above).
Tip: For easy filtering of the manufacturing orders list, we suggest using distinctive product names or manufacturing order numbers for subassemblies, so that the relevant rows would be easily identifiable on the list.
2. Then add operations cost to the subassembly stock cost in QuickBooks. Find the operations cost in Katana for the completed subassembly manufacturing orders for the period, then decrease the salary cost in the income statement and increase the subassembly stock by the relevant amount in QuickBooks (similar to step 3 above).
3. To get the correct numbers from Katana for recording the production of finished products from subassemblies and materials, firstly, you would need to eliminate the materials cost related to subassemblies from the total materials cost of all completed manufacturing orders during the period in Katana (find the total materials cost and deduct the materials cost for subassemblies that you used above). Otherwise, you would count the materials cost double. Then, create a journal entry in QuickBooks for converting the materials into finished products. In addition, create a journal entry for converting the subassemblies used in the production of finished goods, using the subassemblies cost from Katana as the amount.