How We Imported Prior Year’s Transactions of a Hotel In Excel Into QuickBooks

We had a meeting with one of our new clients who runs an established business in the hospitality industry here in Lagos, they needed an accounting software that will track all sales transactions on room booking, and bar sales at the back end office. Although, they already have a hotel management software for front-end sales, the major accounting features required to get necessary financial reports available were still missing out. So, ultimately, you would expect that the only solution is to import all daily sales transactions from the hotel software into QuickBooks which was our recommended path. But here is the challenge, the CFO also wanted all their past transactions in the last three (3) to be keyed into the accounting software so that they won’t miss out some transactions for reference purposes, here are what we did:

  • We designed an IIF excel spreadsheet that will import all sales transactions into QuickBooks 

An IIF excel file is QuickBooks’ recommended spreadsheet format for importing bulk transactions, which could run for 5-10 years, by mapping each account heads and types into their necessary chart of accounts so that when each account head is imported, they are rightly reported in their financials. For instance, when we imported sales transactions, we expected the income heads and their corresponding accounts which could be cash or receivables, depending on whether cash was paid or not, to be displayed in the profit or loss account. 

  • We downloaded the bank statement for the past years & imported into QuickBooks

While the IIF file handles the sales side of the prior year’s transactions, the bank statement shows all the payment made by their customers via direct transfer or POS and the expenses incurred so far, so it’s expected that by the time we import all banking transactions, they should have their prior year’s transactions in QuickBooks and when anyone reviews it, it would look like they have been using QuickBooks since inception.

I have already shared a guide on how we imported payments from school management portal into QuickBooks – This is not different from what we deployed in the hotel except that we added a new column that tracks expenses (cheque/withdrawal).

Like I said in that post, integration of third-party applications with QuickBooks is seamless on the latest version of at most 2-3 years. So, if you are still using an outdated version, you might need to upgrade before we implement.

While these tips can help hotel get started faster, it is still adapted to businesses in other industries. If you have prior years’ transactions in excel and looking for a solution that will help you push them into an accounting software, feel free to contact our support department, send a mail to info@accountingsoftware.com.ng or call 08105090001

Recent search terms:

  • accounting software with import from excel (1)

How We Integrated The Management Portal Of A Private School With The Quickbooks.

The flexibility of QuickBooks accounting software makes it the preferred accounting software for us at BigResources Solutions – We interact with several business owners in divers’ fields of work. With our experience and professional expertise around implementations, we were able to excellently resolve the toughest challenge of one of our clients, and we are also able to provide you with the simple tweak you need to perfect your accounting system.

Recently, we were at one of our client’s office who had previously installed QuickBooks. He was still having challenges in tracking school fees payment on a real-time. At the beginning of the term, there were countless credit flows from parents into the schools’ corporate account, the accounting staff were not able to manually input all the transactions due to limited manpower compared to the number of students in the school. This led to delay in report submission to the management as they could not generate a list of all the students and their receivables faster.

Based on our technical expertise in accounting software and application integration, our professional team was called on to help resolve this challenge.

The school currently has a school management portal deployed to help manage all their registers, teachers assessment of each student, exam scores etc., but what we needed from the portal was a detailed report of school fees payment (CSV format) so that our application can read it and import into QuickBooks.

After reaching a deal, we contacted the web portal developer to help us re-format the CSV format of the payment report using our QuickBooks acceptable file – this is very crucial as QuickBooks developers’ documentation for importing payment transactions clearly states an acceptable format and required details – data of payment, name of customers (which in this case are the students), amount paid, and the bank receiving the flow.

Moving ahead, we installed our application on the administrator’s system where the company file is domiciled, so that all import can be easily pushed into their necessary ledgers. But there was still an issue; the desktop based offline application could not connect to the QuickBooks Charts of Account nor display existing students as it is in the QuickBooks. On troubleshooting, we noticed that the client’s QuickBooks desktop version was 2013 and Intuit QuickBooks has stopped supporting such version. We tested the application on my system since I have the latest version and it worked. We had to back up the company file, upgrade the QuickBooks Pro 2013 software to QuickBooks Pro 2017 and then restored the file to the new version. This fixed it!

We eventually connected to the recently installed QuickBooks and ran our first payment import from the school portal into QuickBooks – all the invoice balances (amount due) were knocked off by the actual amount paid while their corresponding banks were credited.

I am excited that our solution helped our client to solve their toughest challenge and now, they can process hundreds of payment transactions into QuickBooks in less than a minute; a workflow that would have taken about 3 hours if done manually.

You might want to know if we can adapt our solution to your existing business. Yes, we can! We can help you import your banking transactions into QuickBooks!

Request for a meeting with our accounting software expert by sending a mail to info@accountingsoftware.com.ng or call 08105090001

Recent search terms:

  • video of school management in quickbook (1)

How We Customized QuickBooks Accounting Software For Property Managers.

Last week was a very busy one for us; a popular real estate and property management company here in Lagos called our office to help solve one of their toughest financial challenges. By organising their tenants’ record in QuickBooks according to properties. We implemented a simple system that tracks rental payment from these tenants and also helps to generate a report on the performance of various properties located in Ikeja, Ikoyi and Yaba, Lagos State. Although this type of implementation is not new to us as we had earlier shared a YouTube video on how property managers can explore QuickBooks for effective financial management. But the fact that the owner is interested in remote and unrestricted access financial reports from anywhere in the world makes the project an interesting one.

First, we gave a recommendation on the best QuickBooks version to install, and also, we recommended that it should be saved on a cloud folder on Premier Desktop. Why these recommendations? They needed 4 users – one at each property management office and the fourth for the manager who was interested in the reporting side of the software.

Another reason why we recommended desktop software was that they have a robust cloud server that is accessible from anywhere, all you need to do is to call the IP Address on an internet-enabled PC and log on to the company’s file; which means, staff can also update property information outside the office.

You can learn more about our QuickBooks Premier User License Software  & Prices here

Customizing QuickBooks for a property management company is a straightforward process if you understand the key reporting requirements of your client. For us, we consider these important points:

  • Properties & Locations
  • Tenants
  • Rent Collections & Service Charges
  • Security Deposit
  • Property Maintenance & Repairs
  • Remittance to Landlords/Property Owners
  • Report

If there are more specific areas of the property management business, outside the key points mentioned, we always request for such information so that some vital parts of setup aren’t skipped.

So, let’s look at how QuickBooks can be customized for a property manager:

Properties & Locations

Every real estate business starts from the asset they own, which is the unit that generates cash flow via rent and service charge. In QuickBooks, we set the various properties up as an asset – added as a customer (find the reason why we do this under Tenant Setup) and create a class. The class feature allows you track all revenue earned and expenses incurred on a particular property, so you can easily view the financial position of each of the properties you manage.

 

Tenants

In setting up all your tenants on the system, it will be completely unprofessional if you add all the names of the people occupying your properties without grouping them by property type and locations – this is the reason why we added the properties type as customers. Then import the tenants as a job classified under the property units they occupy. Apart from an organised reporting output, you can also generate a well detailed report on tenants, balances and the rent receivable of each property at every point in time. For instance, if you have a landlord who wants to see all the tenants occupying his/her properties at Ikoyi, you will easily generate such property specific report if these tenants are rightly grouped under the properties they currently occupy.

Also, when adding tenants to the properties they occupy, we also took into account, rent dues from each tenant, service charge payable and security deposits paid (if any) so that reports on transactions with these tenants can be accurate. For advanced payment or prepaid rent, just add a minus (-) to the figure on the balance field, or else, the figure will be tracked as receivable instead of rent.

Rent Collections, Commission & Service Charges.

The major source of income for a property manager is not rent but the commission portion of the rent paid by the tenant. Service charge is also an income but at times, it’s mostly spent on repair and maintenance.

Let’s discuss the process of setting these up in QuickBooks:

First, add “Rent” and “Service Charge” as services on the item list window and map them to their respective income account under the account drop-down. Without this, you can’t create an invoice for rent or service charge due, on a monthly or annual basis.

After this step, go ahead to create an invoice, map it to the properties the tenants occupy, print and send to your respective tenants. Without selecting the class, you will not be able to determine easily which property generated X amount, and what is expected from the other properties.

The email feature on the Invoice Window makes the distribution seamless as you can send an e-copy to all your tenants online.

The Receive Payment Window allows you to record payment made by a tenant, which could be an instalment or final payment of the invoice sent earlier. You can also select the bank account the fund is domiciled, and at the end of the period, check your bank statement in QuickBooks using the “register option under banking” to view all rents paid into your account and other banking transactions – bank charges, payments, and cash balances.

Security Deposit

For payment made as a security deposit, it should not be treated as an income because it’s a payment that is refundable to the tenants. Although this is rare in practice but the accounting treatment remains the same.

Go to “Record Deposit” in QuickBooks, select the tenants that made the payment, the bank where the money is paid into and Security Deposit, under the “From Account” – It’s assumed that you already have security deposit added to your Chart of Accounts as a current liability.

Property Maintenance & Repairs

This is not different from your normal expenses but you should select the properties such expenses are incurred on under the class drop-down; this is where you map your spending to the property so that your profit or loss by class can show you what you spent at the end of a specified period.

For instance, if I pay a plumber to fix the toilet at Ikoyi properties, write check records and the amount paid, while the class feature lets me state the property such expenses were incurred on.

Remittance to Landlord     

The figure you should recognise as an income for your property management business is not the rental fees paid by tenants but the commission portion agreed with the property owners (the landlord) – even though the money is paid into your bank account, you still need to pay the landlord the actual amount owned (tracked as remittance to landlord). Before you do that, ensure that you have added “Remittance to Landlord” on your Chart of Account setups under the “cost of sales”.

To pay the landlord, use the “write check” window – on the bank account drop-down, select the bank where payment will be debited from, enter the actual amount you are remitting to the landlord’s account, then select “remittance to landlord” under the expenses account at the button of your window, fill in other information like memo and select the class, which is the property rent was collected.

I already shared a key reason you should select a class for invoicing and payments- it will enable you to easily map your transactions by properties and generate a detailed profit or loss figure on each property you manage.

Report

Apart from the profit or loss by properties, you can also view report on rent due for payment based on properties/tenants, see all expenses incurred by properties, generate a trial balance, bank statement transactions, view your real-time balance sheet report (statement of financial position), cash flow and other relevant reports necessary for decision making in a real estate and property management business.

While these tips have been proven to be effective in the management of a property business, we also know that you may have some specific areas or questions you need clarifications on, don’t hesitate to send us your questions or inquiry about deploying QuickBooks for your real estate or property business.

Kindly send a mail to info@accountingsoftware.com.ng   or call us on, 08105090001, 08084219399.