by Warren Schultz
Last week, I started to describe the various things one can do to save time and money using Microsoft Excel. But I only scratched the surface. Here are some more, following the same format of introducing the problem and then the solution:
Excel automates accounts receivables. A large manufacturing company did business with a large multi-store retailer. Each shipment to each store created a receivable invoice. There were thousands of receivable invoices each month. When the stores sent a check, they also sent an electronic report of which invoices they were paying. The manufacturer was then manually allocating the payments to clear the invoices and balance the accounts.
The manufacturer needed to automate allocating and balancing the payments. The solution was to write a routine in Excel that read the customer’s electronic reports and converted the data into a format compatible with the manufacturer’s system. This allowed the payments to be uploaded into the receivables system where they were matched up and cleared automatically. The program also created discrepancy reports of the items that didn’t match, so the company could address problems much quicker.
Excel automates billing processes. A consulting firm required its employees to manually fill out monthly billing reports that went to the billing department, which were then hand-entered into the system. This resulted in a lot of wasted time and paper. Sometimes the consultants forgot to include some information on the report, resulting in incomplete billing for the month, or entries were incorrect. Either way, it resulted in lost revenue.
What was first needed was to create a master report sheet that consultants could fill out electronically while they were at the client›s office or at the end of the day. The report could be e-mailed at any time. The next step was to create an Excel program that combined and re-formatted all the billing reports into a file that could be uploaded directly into the billing system to generate invoices. The Excel programs made it much easier to bill clients and keep monthly reports current. It also prevented errors and saved time by eliminating the manual copying of consultants’ reports into the invoicing system.
Excel synchronizes data and finds errors. An HR department had problems keeping its employees’ insurance reports in sync with the insurance company’s reports because each company used a different data format and data structure. Because of the different formats and structure, the HR staff had to manually reconcile the reports of more than 1,000 employees. The company needed to find a way to automate the reconciliation and eliminate errors.
Excel has a program that matched employees’ names and addresses with their insurance packages, and highlighted inconsistencies between the two reports. The new program was also capable of reading both formats for items like dates and abbreviations. After implementing the new program, all the HR staff had to do was quickly glance at the spreadsheet and deal with the discrepancies or issues indicated by the report combined with the data.
For more information, you can contact web developer contact Warren Schultz at firstname.lastname@example.org or call him at 818-281-7628.