Accounting Instruction, Help, & How To (Financial & Managerial)

Welcome to QuickBooks Online 2023! In this update, we will explore how to efficiently manage your bank books using the latest features and functionalities. One common scenario we will address is handling e-commerce transactions, specifically inventory sales conducted online through third-party platforms like Shopify or Amazon. Our goal is to seamlessly integrate the data from these platforms into QuickBooks Online to generate accurate financial statements. In this blog, we will focus on using bank feeds as a method to import and reconcile data. Let’s get started!

Setting up Bank Feeds: To begin, ensure that you have the necessary accounts in your chart of accounts. If you don’t have a checking account already set up, follow these steps:

1. Navigate to the “Accounting” section on the left-hand side menu.
2. Select “Chart of Accounts” at the top.
3. Click “New Account” and choose the account type as “Bank.”
4. Name the account (e.g., Checking Account).

For our example, let’s also assume we have a PayPal account. PayPal can be connected using a similar process to bank feeds but offers additional integration options.

Connecting Bank Feeds:

1. Access the “Banking” section from the top menu.
2. If you have already set up bank feeds, you will find tags for the connected accounts. If not, click “Link account” to connect your bank.
3. Choose the desired bank and follow the instructions to establish the connection. This process is straightforward and should pull the required information into QuickBooks Online.

Note: PayPal integration offers two options – PayPal and PayPal Connect. Research both methods to determine which one suits your needs better. While PayPal Connect provides more comprehensive data, using the standard PayPal bank feed may be simpler.

Uploading Data from a File: For practice purposes, we will upload data from a CSV file created in Excel. Here’s how:

1. Prepare a test file in Excel containing the transaction data you want to import.
2. Go back to the “Banking” section in QuickBooks Online.
3. Choose the account for which you want to upload data.
4. Select the “Upload from File” option.
5. Locate and upload the prepared CSV file.

Preparing Mock Bank Feed Data: To begin, we will create mock bank feed data using an Excel worksheet. Open a new Excel file and create the following columns: Date, Amount, and Bank Memo/Description. Enter a sample transaction for now, ensuring that the Bank Memo/Description field contains relevant information such as customer or vendor names for electronic transfers.

Converting Excel File to CSV: QuickBooks Online accepts CSV files for bank feed imports. To convert the Excel file into CSV format, follow these steps:

1. Save the Excel file.
2. Click on the “File” tab in Excel and select “Save As.”
3. Choose the desired location and change the “Save as type” to “CSV (Comma delimited).”
4. Save the file with a suitable name.

Uploading CSV File to QuickBooks Online: Now, let’s upload the prepared CSV file into QuickBooks Online. Follow these steps:

1. Access the “Banking” section in QuickBooks Online.
2. Select the relevant bank account (e.g., Checking Account) to which you want to import the data.
3. Click on the drop-down menu and choose “Upload from file” instead of linking to the bank.
4. In the uploading process, select the CSV file option.
5. Locate and upload the CSV file you created.

Assigning Bank Feed Account and Header: Upon uploading the file, QuickBooks Online will prompt you to assign the bank feed account and specify if the first row in your file is a header. Follow these steps:

1. Confirm that the uploaded file corresponds to the correct bank account (e.g., Checking Account).
2. Select the account to which you want to assign the bank feed data.
3. If your CSV file contains a header row (which it should), mark the option stating that the first row in the file is a header.

As we explore the functionalities of QuickBooks Online in 2023, one important feature to understand is the process of importing bank feed data. Bank feeds allow you to connect your bank accounts and easily import transactions into QuickBooks Online, ensuring accurate and up-to-date financial records. In this blog, we will walk through the steps of importing bank feed data and discuss the mapping of columns to QuickBooks fields. Let’s dive in!

Creating Mock Bank Feed Data: To begin, we need to create mock bank feed data using an Excel worksheet. In this example, we have a single transaction. The columns in our data include Date, Amount, and Bank Memo/Description. We will use negative numbers to represent decreases and follow the date format of MM/DD/YYYY.

Converting Excel File to CSV: QuickBooks Online requires CSV (Comma-Separated Values) files for bank feed imports. To convert our Excel file to CSV format, follow these steps:

1. Save the Excel file.
2. Click on the “File” tab in Excel and select “Save As.”
3. Choose the desired location and change the “Save as type” to “CSV (Comma delimited).”
4. Save the file with an appropriate name.

Uploading CSV File to QuickBooks Online: Once we have the CSV file, we can proceed to import it into QuickBooks Online. Let’s walk through the process:

1. Access the “Banking” section in QuickBooks Online.
2. Choose the relevant bank account (e.g., Checking Account) where you want to import the data.
3. Instead of linking to the bank, select the option to “Upload from file.”
4. Locate and upload the CSV file you created.

Mapping Columns and Reviewing Data: Upon uploading the CSV file, QuickBooks Online will prompt you to map the columns to the appropriate QuickBooks fields. Follow these steps:

1. Review the column headers and compare them to the required fields in QuickBooks Online (Date, Amount, and Bank Memo/Description).
2. Map the Date column in your CSV file to the Date field in QuickBooks.
3. Map the Bank Memo/Description column in your CSV file to the corresponding field in QuickBooks.
4. Map the Amount column in your CSV file to the Amount field in QuickBooks.
5. Proceed with the import process.

Bank Feed Integration and Options: Once the import is complete, you can observe the imported transactions in the bank feeds. In this example, we have two accounts: a checking account and a PayPal account. The bank feed integration may vary depending on the type of account you’re working with.

• Checking Account: The bank feed for a checking account generally allows you to record deposits and categorize them as increases to the bank account.
• PayPal Account: The PayPal bank feed integration, using the PayPal Connect app, provides additional options and flexibility. It allows you to choose whether to create a sales receipt, a deposit, or a transfer. It also considers PayPal fees, showing them as separate transactions.

When it comes to recording revenue using bank feeds, there are multiple options and paths that can lead to varying degrees of confusion. In this blog, we will discuss an alternative approach to using bank feeds for revenue recording by utilizing Excel and QuickBooks Online. By visualizing the process in Excel, we can gain a transparent understanding before implementing it in QuickBooks Online. Let’s delve into this methodology and explore its implications.

Utilizing Excel for Transparent Revenue Recording: To gain a clear understanding of the revenue recording process, we can use Excel to map out the steps and visualize the flow. This approach allows us to grasp the concept more transparently before translating it into QuickBooks Online. Let’s outline the process in Excel:

1. Create a new Excel worksheet.
2. Set up columns to represent the necessary data fields, such as Date, Customer/Vendor Name, Amount, and Description.
3. Enter the revenue transactions in the respective columns, ensuring accuracy and consistency.
4. Apply appropriate formulas and calculations to manipulate the data, if necessary.
5. Analyze the results and evaluate the transparency and effectiveness of the process.

Translating Excel Methodology to QuickBooks Online: Once we have a clear understanding of the revenue recording process in Excel, we can proceed to implement it in QuickBooks Online. Here’s how:

1. Access the “Banking” section in QuickBooks Online.
2. Choose the appropriate bank account where the revenue transactions will be recorded.
3. Follow the steps to import the bank feed data, ensuring proper mapping of columns to the relevant QuickBooks fields (Date, Customer/Vendor, Amount, Description).
4. Review and verify the imported transactions in QuickBooks Online, cross-referencing them with the data from the Excel worksheet.
5. Make any necessary adjustments or corrections to ensure the accuracy of the recorded revenue.

Advantages and Considerations: By utilizing Excel as an intermediate step, we gain several advantages in recording revenue through bank feeds in QuickBooks Online:

1. Enhanced Transparency: The visual representation in Excel helps us understand the process clearly and identify any potential pitfalls or errors.
2. Test and Refine: Excel allows us to experiment with different scenarios, formulas, and calculations before implementing them in QuickBooks Online, minimizing the risk of mistakes.
3. Improved Accuracy: By cross-referencing data between Excel and QuickBooks Online, we can ensure the accuracy and integrity of recorded revenue.
4. Streamlined Workflow: Preparing the revenue transactions in Excel beforehand allows for a smoother and more efficient data entry process in QuickBooks Online.

Conclusion: When it comes to recording revenue using bank feeds, leveraging Excel as an intermediate step can provide valuable insights and enhance the overall process. By visualizing and manipulating data in Excel, we can gain a transparent understanding before implementing the methodology in QuickBooks Online. This approach allows for testing, refining, and optimizing the revenue recording process, ultimately leading to more accurate and efficient financial management.

Note: The instructions provided in this blog are general guidelines for using Excel and QuickBooks Online for revenue recording. The actual implementation may vary depending on your specific business needs and the functionalities offered by QuickBooks Online at the time of use. Always refer to the official documentation and seek professional advice when necessary.