Microsoft Excel is the business community’s largest and most widely used office productivity program. The program offers a wide array of functions including: formulas, sorts, graphs, data imports, and pivot tables. These functions are used to manually create simple reports, or automatically used to create complex reports. Equally important and worth getting-to-know is Microsoft’s Access database program. Access is used to create, manipulate, store, and extract large amounts of data.
At the December 8, 2011, roundtable, CFOs, controllers, and business owners gathered to discuss best practices, share success stories and uses of the programs, and simply ask questions related to using Microsoft Office’s productivity suite. Ideas and comments from the attendees are summarized below.
How do most people use Microsoft Excel?
More than anything, people export their financial system data to Excel to review important financial information including sales reports, inventory and work-in-process, budgets, time and expense analysis, cash flow, commission calculations, and the detail of any general ledger account. Others simply use the program as a way of maintaining data.
How do I know if I am using Excel efficiently?
Many of the attendees questioned if they were using Excel to the fullest extent possible. To validate this, if you were given a sales report Excel with these headers: Customer, Product, Ship to State, Quantity, Date Sold, Cost and Sales Person, how would you go about finding the answers to the following questions: “What are my total sales in December to Company ABC?” “What are my margins on Product 1 in the first half of the year versus the second?” “How much commission do I need to accrue for Salesperson A, based on November’s sales?” If you answer, “I’m not sure, it’ll probably take too much time, so I’ll ask someone else to do this,” “I’ll just jump in and start scrolling through the data creating and adding figures on my 10-key,” or “I’ll just call my IT person and have them create a one-time-only report,” chances are there is a more efficient way to use Excel’s “reporting” (e.g., sorting, summarizations, pivot tables, etc.) tools.
What examples can you provide of ways to use Excel efficiently?
Using the same example from the last question, pivot tables can be used to answer questions. Even further, the data sets (i.e., the set of data selected and highlighted within Excel to be used in the pivot table) can easily be modified and adjusted.
Have you ever performed the exact same task, in similar fashion, numerous times, to modify data or recreate a template? One attendee gave an example of how they’ve adopted Excel macros to help with their budgeting/forecasting process. On a yearly basis, the old procedure was to roll-forward the prior year budget template. Across multiple Excel tabs and line items, this task involved not only the removing of prior year financial data and replacing it with a “$0”, but retaining the integrity of existing formulas. This requires a significant amount of valuable time. Through trial-and-error, and without any programming knowledge, the attendee was able to select the record-macro-feature, which automatically captured all key strokes and mouse clicks from the user across the numerous tabs and lines within a file. By creating this macro, they were able to access the programmed function during the next budgeting period, at which point, selecting the “play”-macro-feature would automatically replicate this task without user interaction and save significant time.
Other participants provided even more simplistic, yet highly efficient uses of Excel, such as creating a bank covenants template. In this example, the participant needed to calculate monthly covenant reports based upon their financial results to determine compliance. Instead of manually calculating results, they created an Excel template that allowed them to enter categorized results (e.g., eligible receivables, finished goods, total debt, etc.) from their financial system into specific cells that were referenced in another part of the workbook which automatically tabulate a visual “Yes” / “No” compliance result for each of the formulated covenants.
When should I use Access vs. Excel to maintain my data?
One example detailed a business user that was trying to maintain a spread sheet of their company’s large fleet of vehicles. The spread sheet was to maintain vehicle information (e.g., make, model, color, options, VIN number), maintenance data (e.g., oil changes, tire rotations, transmission flush/fills), insurance / tab data (e.g., premiums, deductibles, tab expiration dates, costs), and user data (e.g., employee ID, employee name, position, date vehicle was provided to them, business mileage usage, personal mileage usage, accidents history, etc.). As imagined, for one vehicle, the spreadsheet could easily be 50+ columns wide with a range of possible data inputs. To make things more complicated, it was necessary to maintain detailed records of vehicle history (i.e., not only “What vehicles does Employee 22 own now?” but, “What did Employee 22 own in 2008?”). In addition, multiple users were requested to enter the data into the report themselves – which could create a slue of problems, especially relating to information validity and integrity (e.g., “How do I assign vehicle 27 to two employees to share?”). This example was a clear instance of when Access could be used to create a relational database that would protect the integrity of the data and allow multiple users to input data simultaneously. More importantly, the database would allow management to easily query data to answer questions. For example, a user could use Access to determine answers to some of the following questions: “What are my average premiums paid for Vehicle X?” “What is the accident history for Employee 22?” “How many vehicles do I have approaching the 100K mark, and thus require replacement and cash out-flow?”
In short, Access should be considered when there are multiple users, multi data tables, data input/output controls (i.e., data management) are required, and user-limitations are necessary.
Where can I go to learn more about using Microsoft Excel and Access?
There are many tutorials and training programs available. Examples discussed during the roundtable included:
- microsoft.com (someone mentioned there are great pivot table videos there)
- New Horizon’s Training Course (for day-long classes at different experience levels)
- CFOOnline.com (for business uses and tips)
- Lynda.com (subscription-based tutorials for business uses and tips)
- Powerpivot.com (a free powerful pivot table add-on for MS Excel 2010)
- TheExcelAddict.com (a daily email blast for those wanting to learn more)
- Youtube.com (thousands of “how to” videos)
Are there any other productivity tips or tricks people are using in other Microsoft programs?
Various additional topics were quickly discussed:
- eGrabber.com – capture contact information from email signatures and websites
- “=HLookUp” and “= VLookUp()” – Powerful excel functions used to compare and grab data between two different excel tabs, given a common identifier (e.g., Inventory ID, Check No/ID, etc.)
- Word’s Mail Merge – Used to create letters, labels and other information by pulling information from a database, such as Excel
- Excel’s Text-to-Columns – Used to separate data that was exported into a single cell
- Sharepoint – Allowing document sharing and collaboration via the web
