12.3: Integrating Microsoft Excel and Accounting Programs
By the end of this section, you will be able to:
- Discuss the pros and cons of using Excel for accounting purposes
- Describe how Excel and QuickBooks can be integrated
WorldCorp uses Microsoft Excel for managerial accounting and data analysis, but they use an accounting software suite to handle the financial accounting. Excel can be used in conjunction with your accounting system, but it cannot replace the accounting system. Accounting software also allows for real-time acceptance and recording of transactions. That means that all purchases through a retail store using a cash register and printed receipts are also recorded in the accounting software at the same time. While all these transactions are happening all over the country and internationally, the financial statements are automatically updated. Accounting suites allow for more automation than Excel, but Excel can still be useful in data analysis. This section will compare Excel with accounting suites like QuickBooks and explain how to import or export data into and out of accounting software suites.
Pros and Cons of Using Excel for Accounting
Accounting principles are not programmed into Excel, so it does not perform automatic calculations or create documents in the same way accounting suites do. Accounting suites are designed specifically for accounting and bookkeeping, whereas Excel is a spreadsheet program that has broader uses than accounting, such as calculating mathematical equations using functions and formulas, building tables, and performing data analysis. You can use Excel as a tool for deeper analysis and comparison of decision alternatives by importing the information from the accounting-specific software programs. It can be used for analyzing any data in numerous disciplines, such as science, humanities, social sciences, and sports. It can create charts and graphs, which makes it easy to share information and collaborate using OneDrive or SharePoint, and thus it is very useful for managerial accounting.
Graphs, statistical tools, and charts offer insight and are used in reports and presentations. These Excel tools are not commonly found in financial accounting suites. Within Excel, these tools can build relatable visualizations using PivotTables to increase comprehension of data. This level of data analysis is not offered in financial accounting suites. You may find some of these features in larger accounting systems, but they are not typical. Also, as technology advances and user needs are incorporated into software updates, it is possible some additional data analysis capabilities could be added in accounting suites. But for now, data analysis of the accounting data is often done in Excel. You would need to extract the data and import it to Excel to make it visually impactful. You can also combine several accounts from the accounting software program in a single export file to Excel and analyze them together using a table or chart.
QuickBooks is one common accounting suite that is easy to use. Its interface has icons that represent the most common needs of business managers. The program is divided into the areas of vendors, customers, employees, companies, and banks. The vendor section is for handling billing and accounts payable and is also a central database for all vendors in the company. Having this list reduces the need for each department to have their own database of vendors. In the vendor section, you can manage the vendor information such as the contact email and the area of the company to which the vendor is assigned. The customer section is for generating invoices and handling accounts receivable and includes all the payment methods that customers may use. Just as the vendor section is used to manage vendors and create the database for the company, the same is true for the customer section. The customer section includes the master list of all customers in the company. The employee section is for handling the payroll. The company section is for organizing the Chart of Accounts and performing other similar functions. The banking section is for writing checks, tracking checks, recording deposits, and reconciling transactions.
The main advantage of an accounting suite compared with a spreadsheet is that it makes all needed tables and interconnections automatically. In Excel, you would have to build all tables from scratch and use cell references to make them communicate, which would be time-consuming. In an accounting suite, you can track transactions and costs without building tables like in Excel. QuickBooks even adds its own specialized features, such as scanning invoices from vendors automatically, without any typing.
Link to Learning
Running a small business is easier using an accounting suite like QuickBooks, but you should still consider using an accountant to help you with things like bookkeeping and taxes. Read this article on basic business accounting and the types of daily, weekly, monthly, quarterly, and annual tasks you should be performing. Maintaining good bookkeeping will save you time and money; if you use QuickBooks, you will save on hourly CPA charges, as the consultant will have to work less. Using Excel for bookkeeping requires the CPA to work more hours to accomplish the same tasks.
Integrating Excel and QuickBooks
You have learned different situations in which a company may want to analyze accounting data in Excel, but you have also learned that Excel should not be used alone for a company’s bookkeeping. Accounting suites allow for easy data export for analysis in Excel. For accounting in small- to medium-sized businesses, over two-thirds of the market has been held by Intuit’s QuickBooks for many decades. QuickBooks is easy to use for most people comfortable with technology and data, and to use it, you don’t have to be an accountant. You create a chart of accounts when you first install the software or sign up online and feed it with daily invoices and expenses. Once it’s set up, it automatically forms your financial statements in real time. The application can generate tax-related reports such as the annual business tax filings or sales tax for submitting to government agencies.
Link to Learning
If you want a more visual tutorial on extracting data from QuickBooks to Excel, see this detailed guide that walks you through the steps in the desktop version.
Fortunately, Intuit and Microsoft have made their applications compatible. Excel also integrates well with QuickBooks and other accounting software. Both accounting applications readily accept importation of Excel files, and they both can export data to Excel. Excel opens their exported file with ease, and you can gather insights with these datasets by utilizing data analytics processes, forming data visualizations, and designing business intelligence queries. Most accounting programs have a tool or command button for importing Excel data into the program or for exporting the data to Excel for additional analysis. When the data is imported into the program, it is converted to that program’s data file type. Importing data from Excel into these specialized programs can be helpful to check for errors and to uncover inconsistencies in financial data.
When starting to implement QuickBooks in your business, you may need to import a lot of data from Excel files. Maybe you have your listing of products in an Excel file. In QuickBooks, there is a quick, automated way to import this data. In the online version, you use the Import Data feature. This process lets you import many different types of data, as you can see in Figure 12.14. For importing customer data, you will need to make sure that the Excel spreadsheet matches the instructions provided in QuickBooks so that it can map the data (Figure 12.15). It may involve some formatting and cleaning up of your Excel spreadsheet. If you want to export from QuickBooks to Excel, you can easily filter out what kind of data you want to export (such as certain transaction types or a date range) and export it to Excel format (Figure 12.16).
Spotlight on Ethics: Financial Auditing
The purpose of the financial audit is to ensure that the company is following the rules and governmental guidelines for managing and reporting financial information. It is also designed to assess the risk of the company for potential legal action for inaccuracies or wrongdoing. Audits are greatly facilitated with the use of software. It reduces human error and can even detect issues that might not be discoverable by the individual auditor. Deliberate manipulation of financial information can cost the company millions of dollars. In 2001, Enron was found to be inflating their company earnings by hiding debt. This results in Enron’s shareholders losing billions of dollars and the share price collapsed. Enron employees were convicted of wrongdoing and the company eventually went bankrupt.