How to Excel in Business to Save You Time and Money Part 1 thru 6
Microsoft Excel is one of the most underused tools business professionals have at their fingertips. If they
would just take advantage of Excel’s benefits, they would have more free time to do what they want.
People who use Excel save more time and money and gain a competitive edge on their competition.
It is so versatile that it can be tweaked to work with almost any project. It can be used for things as simple as a calculator to something as complex as a full accounting system. Some of the things Excel can be used for to save you time and money are: Merging Lists from Different Sources into A Single Spreadsheet. A retail call-center manager needed to monitor employee phone usage and determine if any staff was abusing phone privileges. The manager needed to combine the lists of inbound and outbound calls that he had with the customer phone number information from corporate headquarters. The manager needed to find a faster way to merge the data than manually inputting the information. TAP Solutions created a spreadsheet using an Excel macro that combined the two sources of information and compared the data. Phone numbers that didn’t match up with the customer list were separated out to determine if the employees were abusing their phone privileges. The new spreadsheet also enabled the manager to see the phone activity in each division. This information allowed the company to balance the call load throughout the call center and saved them overtime costs. Converting PDF Files Into Excel. A manufacturer received client orders in large PDF documents. The company had to print them out, then manually enter the information into the system. The manual process required many hours and contained numerous errors. Under the new TAP Solutions system, the PDF files were converted into text files that could be read by Excel and turned into spreadsheets. The Excel format enabled the company to directly upload the orders into their processing system. The company could also then analyze the data and import and export the information as needed. Eliminating the manual data entry step resulted in much faster, more accurate order processing. How to Excel in Business to Save You Time and Money, Part 2 of 6Last 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 multistore 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. How to Excel in Business to Save You Time and Money, Part 3 of 6I continue with Part 3 of the various ways Excel can save you time and money.Excel transfers data into MS Word files. An online retailer downloaded customer orders into an Excel file. Then an employee created packing slips by copying and pasting all the pertinent information from the Excel file into a Microsoft Word template. The employee also had to manually change the customers’ formatting to make it compatible with the company’s style. The retailer’s manual conversion and correction process was extremely time-consuming. The key is to create an Excel program that reads the orders, automatically corrects the formats and transfers the data to packing slips. The new process can take seconds to convert the Excel format orders into Word packing slips instead of minutes, or even hours. Excel calculates estimates. A window blind company owner never knew exactly how much material he needed for any single project. He would estimate the amount and put that figure into Excel. When he estimated too much, it cost him money for materials he didn’t use. When he estimated too little, it delayed the project’s completion because he had to get more material from his supplier. Plus, he used Excel as if it was just a paper chart. By using Excel, one can create a worksheet for each order as part of a workbook for orders to suppliers. After all the individual orders are entered into the workbook as worksheets, the new Excel workbook can calculate how much material is needed for the entire group of orders. This worksheet will save the company time and money, because the owner can order the right amount of material from his suppliers and allocate it correctly to the customers’ jobs. Excel sorts data. A large retailer used a contact form on its website to solicit inquiries and received many. When the staff went to read the emails, there was a large amount of spam mixed in. The staff member responsible for the incoming emails had to weed out the junk mail and copy the legitimate inquiries into Excel spreadsheets. The retailer needed to find a way to sort the emails and to speed up the intake and distribution process. By writing and installing a code into the retailer’s website, emails that were sent from the website link now automatically carry the code so they are recognized and kept on the retailer’s email processor. Emails from spam and sources outside the website don’t have the code, so they’re kicked out. As part of that solution, it’s a good idea to also write an Excel macro that goes through all the email and deletes the junk mail that doesn’t have the inquiry code, thus eliminating the need for manual review. Next, the macro sorts the information from the emails and distributes it to the appropriate sheets based upon the type of the inquiry. The user could then quickly and easily send the inquiries to the appropriate responders throughout the company. The new process saves many hours and greatly improves the company’s response time. How to Excel in Business to Save You Time and Money, Part 4 of 6Did you know that Excel sorts data, processes data and compiles data? As I’ve said in previous articles, there’s virtually nothing Excel can’t do with data.Once, a manufacturer received a multi-store report with no page breaks from a vendor. The manufacturer needed separate pages for each store, but he wanted to stop manually going through the reports and putting in page breaks before printing them. The solution to that problem was to write a macro that went through the large report and logically figured out the page breaks according to the store codes and line breaks. Excel also works really well with data over different worksheets. A client was given a spreadsheet with more than 200 worksheets with about 30 data elements on each sheet. Her task was to combine all the information into one sheet so it could be used as a master roster. She didn’t want to copy and paste each field, which she estimated would take a week. By coming up with a formula that combined all the data in seconds, it saved her hours and hours of copying and pasting each cell. Finally, if the data is repetitive, Excel can process it. A client had some extremely large data files that he got from his ongoing clients. This data was uploaded into the main system to update information. The problem was the clients often supplied invalid data, causing the updates not to load and causing delays in processing the information. By writing a spreadsheet that reviewed all the data files and showed all the errors before they went into the main system, the files were cleaned up before the main load, thus saving countless reloads. How to Excel in Business to Save You Time and Money, Part 5 of 6A friend who knows how to use Excel is a supremely qualified friend, indeed. Everybody should either realize how powerful and efficient Excel is – or find a friend who does.Did you know Excel can produce promotion sheets and website data feeds? It’s true. A local importing company had some old software that handled the website data and tied it to its weekly promotional sheet that was sent to their clients. Unfortunately, the software was old, buggy and could not handle the growing business. The process was extremely time-consuming and frustrating. Solution: The first thing to do is evaluate the current software. Talk to the staff and find what they like and what they wish and need it to do. That input allows you to better develop an Excel spreadsheet that lets the staff enter the data in one place. Then the spreadsheet automatically produces the promotional sheet along with the data feed for the website. It completely replaces the buggy, old software. Another thing Excel can do: streamline project estimates. A local contractor wanted a way to produce an estimate while on a job site. He also wanted the spreadsheet to produce the other needed documents such as contracts and permits. Excel allows one to enter all the needed data, then spread it to all the needed forms. This way, if a person needs a form, all he has to do is click on the proper tab, make any needed tweaks and print it out. It saves a ton of time typing in things over and over. Finally, Excel works really well with online calendars. An education company used a calendar to keep track of all office appointments. Then two days before the appointments, staff would call every client. If the client didn’t speak English, somebody would call and confirm the appointment in that language. This proved very time consuming, and sometimes it did not get done. The staff decided they needed an automated process, but no one knew how to have their calendar communicate with their auto-caller software. Excel to the rescue! The solution is to write a spreadsheet that is able to accept the calendar data and convert it into a file that could easily be uploaded into the auto-caller software, along with time and codes to tell it what language to speak. Become Excel-lent and see the great many things Excel can do for you. How to Excel in Business and Save Time and Money – Part 6 of 6Given how business works today, if Microsoft Excel didn’t exist, somebody would have to invent it, because it is the best tool for saving businesses time and money. Read on for ways Excel streamlines schedules, standardizes professors’ grading processes, eliminates the need for an adding machine, tracks events more efficiently and creates a pre-flight checklist. Streamlining SchedulesProblem: A large maintenance company had six different departments feeding various scheduled and emergency appointments to one department. So many departments meant the incoming data was in different formats, and critical data sometimes was missing. This resulted in scheduling nightmares. Additionally, for legal and reporting reasons, the company needed to keep logs of what was done, when and where. Solution: Excel allows one to develop a standard input form for each department that has data validation embedded into it. When somebody needs a scheduling report, all they have to do is enter the date range. This pulls in all the data from all the sheets and produces a scheduling sheet for the date range. The company now sees what needs to be done, which makes scheduling more efficient. Standardizing Professors’ Grades Problem: A local college wanted to standardize the way professors tracked and assigned grades. The current method was deemed impractical and inefficient. Solution: Excel lets you develop a customizable spreadsheet, so professors grade the way they want while still keeping all the needed record-keeping information and backup. Eliminating the Adding Machine Problem: A large maintenance company needed to do an analysis to find out how mechanics were spending their time and how much time was being spent on specific tasks. The clerical staff used an adding machine to figure it out. Solution: An Excel spreadsheet can take all electronic work orders, extract the needed data and automatically create the dashboards that management wants and needs. Tracking Events More Efficiently Problem: A company that ran networking groups tracked events manually, but everyone involved had a different way of doing things. It was a nightmare for the corporate office to reconcile the events, payments and costs. Solution: The first step is to meet with group and management leaders to see what each side needs. Then you can produce a spreadsheet that automatically loads the event information. At the event, use a laptop to check in people and gather additional information. After the event, email the spreadsheet to the corporate headquarters. Since all the information was in that one spreadsheet, it’s easy for corporate to gather what it needs. Creating a Pre-flight Checklist An aircraft charter company needed a pre-flight checklist that each crewmember could use on his or her laptop before taking a trip. It had to be simple, yet provide a warning if something was not correct. Solution: An Excel spreadsheet is simple and quick to use, but nonetheless, includes logical warnings if something is not correct and/or missing. For more information on how to find the right MicroSoft Excel Developer contact Warren Schultz at Email : warren@tapsolutions.net or call him at 818-281-7628. Website : www.tapsolutions.net #TAPSolutions #TAPSolutions.net #ExcelConsulting #ExcelSupport #ScvDevelopment #ExcelSupport #ExcelApplications #Hire #Scv #Sfv #SantaClarita #tappublish |