Management Reporting Systems
I’ve been doing management reporting for more than 30 years, it’s probably the thing I’ve done the most of in my career. My favorite way to do it is with Excel VBA. I typically make a connection to the back end of my accounting system, extract the required set of records, massage them with VBA and render them in a spreadsheet. This method is fast, reliable and flexible, and has never failed me yet.
I’ve done every kind of management reporting there is, basic financial statements, KPI dashboards, order input, revenue and backlog analysis, cash flow, budget versus actual, labor utilization, pricing and quotation systems, product line profitability, indirect rate analysis, project reporting, valuation analysis, AR aging and DSO, inventory turns, sales and use tax and many more. I’ve done all of these and more for my clients with great success using VBA programming. I also give them a friendly and intuitive user interface that makes it simple and easy for them sort and filter, so they get to the heart of the matter to get the job done quickly and easily.
Interactive Dashboards
Excel has many great features to create dashboard visualizations, however I have found the best tools to create powerful dashboards are a PivotTables, PivotCharts and Slicers. When these are used in combination with VBA’s ability to dynamically query your data source, it gives you the ability to get to what you’re looking for easily, present it any way you choose and quickly refresh the dashboard as new data becomes available.
Dynamics 365 CRM Integration
Using VBA code you can sync Excel with your Dynamics 365 CRM to automate the process of posting updates to it. This gives your field reps a more familiar tool and makes it easier for them to keep your system current.
Just as does with your ERP, VBA also gives you on-demand access to your CRM. This allow you to have it wherever and whenever instantaneously. It also allows you to combine CRM data with that of other systems and create powerful reports, analytics and visualizations. These can then be back-loaded into the CRM interface making the information readily available to CRM users remotely.
Custom Spreadsheet Formulas
We’re all familiar with the built-in Excel formulas such as SUM(), MIN(),MAX(), VLOOKUP() and so forth. These are great but they don’t always meet your specific needs. Getting the answer you want can often be achieved by combining the Excel formulas inside one another, this is called nesting. Nested formulas work, but if they get too complicated they become extremely difficult to write and very hard to read. If later on you have to make changes to them, rethinking the logic can be really difficult, you may even have to start from scratch. Then there’s the problem of making sure all the cells that use that formula have been updated properly, in an extreme case, you may end up having to debug the whole spreadsheet!.
These problems can all be solved by creating custom formulas. Custom formulas are used just like the standard ones, but are designed around your specific requirements. Basically, a custom formula puts a wrapper around all that complexity and gives you your own concise and descriptive formula that is simple to use, performs as well and has none of the maintenance problems.
Smart Spreadsheets
Below is an typical example of a spreadsheet that any given company might produces on a monthly basis. More often than not, a report of this type involves a quit a bit of manual effort. The raw data may come from two or three different sources, be in different formats and use different terminology to describe the same thing. All that has to somehow be incorporated and cleaned up before it can be used, that usually involves a lot of cutting, pasting and manipulation to get it into shape. The report itself probably relies heavily on complex Excel formulas, these have to be maintained because potentially, the data could change and the formulas may no longer work properly as written. Finally the report has to be reconciled against the source data to insure accuracy and if the numbers don’t tie, there could be a lot of rework and debugging necessary. So depending on the level of Excel knowledge and skill level of the preparer, this report might take hours to produce.
Using VBA, SQL and techniques I’ve developed over the years, this same report could be produced in seconds, with one or two clicks, by someone with little or no Excel knowledge. All the pulling together of data, cleansing, error checking and and reconciliation can be done automatically in the background, so all the user has to do is run it! The report can even be logically tested for problems and if any are discovered highlight them for you with explanations so problems can quickly resolved be resolved.
ERP Integration
Another thing I’ve been doing for many years is Excel/ERP integration using VBA and SQL.
No matter what type of ERP system you have, it runs on a SQL backend database and Excel can connect to it. This gives you the ability call up any data in your system and have it at your fingertips. I design intuitive user-friendly interfaces that allow you to get to information quickly and easily and get your answers to questions on the fly.Using VBA, SQL and techniques I’ve developed over the years, this same report could be produced in seconds, with one or two clicks, by someone with little or no Excel knowledge. All the pulling together of data, cleansing, error checking and and reconciliation can be done automatically in the background, so all the user has to do is run it! The report can even be logically tested for problems and if any are discovered highlight them for you with explanations so problems can quickly resolved be resolved.
Pivot Tables On-Demand
If you’re not using Excel Pivot Tables you should start now, because they are without a doubt, the best analytical tool for large datasets available, I use them every day and build them into practically every application I design.
A PivotTable is a powerful tool to quickly calculate, summarize, and analyze datasets that lets you see comparisons, patterns, and trends and answer unanticipated questions about the data. Pivot tables let you interactively group, sort, count, total or average and analyze the data from every angle and summary level. You can transform columns into rows and rows into columns. It allows grouping by any field (column), and using advanced calculations on them. All this is done on the fly and without changing underlying database itself, it simply “pivots” or turns the data to view it from different perspectives.
If you’re not all that familiar with them, creating pivot tables and knowing how best to structure them can be a little intimidating. So I have developed techniques that create them for you spontaneously, with just one click. I do all the setup work for you in the background so you can dive right into your analysis.
MS Office Integration
Using VBA code Excel can call up any of the other Office products and use it just as you would do normally, only it happens behind the scenes a lot faster and more accurately. For instance you can run a calculation in Excel and pass the results to a Word document. Or by using conditional logic, Excel can evaluate which Word document template is proper to use based on the results. Excel can also call up Outlook, compose an email, attach the spreadsheet and send it out to the recipient, all in one pass without you having to do it. Or if you want to present the latest financial statements in a PowerPoint presentation, Excel can create and populate it for you, simply by clicking your mouse. In other words, I can build a system for you that integrates all the Office products into one seamless user-friendly system that will save time and money.