You may be well aware that Excel is a very powerful tool for doing financial calculations. What most people do not realize, however, is that Excel also has a very powerful programming language called Visual Basic for Applications (VBA) that is rarely used but can be used to automate and improve the quality of your processes, even outside of finance.
Here are just a few of the many ways that Excel can make your life easier in business:
Automating and standardizing your printing
Cleaning up data pulled from your data warehouse
Creating reports and charts from data you pull in at regular intervals
Perform quality checks on calculations that you make
Cut and paste data automatically
Automate many of your calculations
Set up triggers when numbers hit certain targets up or down
Program simulations and show results for a variety of circumstances
Perform complex calculations that would be difficult to do with manual formulas
Develop menus for nice navigation by your users in your worksheets
Here are some of the practical uses we have built for clients using the programming language in Excel:
Business plan models for many clients, with user friendly menus for easy navigation and powerful calculations such as simulations or cash flow iterations. The result is plans that are very presentable for the investors and board that have won many compliments. These have been one factor that have helped several clients land bank or equity financing or get their budgets approved by their parent company and board. Check out our separate section on the unique features of our business plan models.
Sales prospecting and quotation tool for the sales force of a telecom company. Think the rate plans for telecom companies can sometimes be complicated to figure out? You are right. Now compound it with trying to do that for the competition too. We built an Excel based menu driven tool that allowed the business salespeople to learn about the needs of prospective business customers, determine the best rate plans that fit their needs and show how those plans stacked up against the competition. The client did a very fast rollout of their service in selected regions of the country, but did not have a tool for their salesforce. We saved the day and had this developed in time for the launch. Later, they followed our methodology when putting up a consumer version on the Internet.
Data analysis on operating results. A telecom client grew rapidly in the consumer area and realized that bad debt costs might be higher than planned. We worked with their data warehouse area to pull in acceptable data. We then programmed Excel to clean up this data, calculate the results and then dynamically create reports and charts with the monthly updates on the data. The result- we showed them that bad debts were running much higher than expected, around 55%. We then used our other consulting skills to develop and execute a plan to reduce their bad debt levels to industry norms. With our programming in Excel, we were able to quickly show them how they progressed and highlighted which areas needed still more improvement. Ultimately, the client cut bad debts to under 5% of sales while maintaining aggressive marketing programs. They later were able to sell the company, which would not have been possible had bad debts not been brought under control.
Monthly reporting of results. A health care client needed to document the savings they were generating for major customers with their disease state management program. We worked with their IT people to get the appropriate data, then followed up with their finance and operations people to develop the methods to calculate the savings. We then programmed Excel to take this data, strip out irrelevant data, perform the calculations and analyze the savings. We also used Excel to package these results in a presentable format for monthly reporting to top management and to their customers. Their first customers were pleased with the savings and renewed. The client was also able to collect on the success fee portion of their service based on the documented savings.
Jon Paul brings the best of both worlds:
He is an expert programmer in Excel, having taken numerous courses in Excel VBA programming as well as studied the best books on Excel VBA out there. He is a power user in Excel and cut his teeth in VisiCalc, then Lotus, until moving to Excel over 10 years ago.
He also has the user perspective from his management and consulting experience.
Contact Us:
Jon Paul
President, Value Added Finance Inc.
jon@valueaddedfinance.com
jpaul@mba1980.hbs.edu
847 372-1963