Common Solution – Custom Microsoft Excel Sales Forecasting Tool
One of our long-term business clients recently reached out to hire us for our professional Microsoft Excel consulting services. Basically the client wanted to contract us to program a new sales forecasting tool in Microsoft Excel. But first a little background.
A little background: For this small business client based right here in Orange County California, just down the street from our office in Irvine, we have built several custom and highly advanced models in both Microsoft Excel and Microsoft Access in the past. Some of those Excel and Access consulting projects we did were pretty straightforward finance models, the same type as those we have built for other members of the local Irvine Chamber of Commerce that hire us, just your basic Excel programming type projects, forecasting, projections, costing, dashboards, etc., where you would automate the monthly reporting with a few simple Microsoft Excel macros (VBA). There were also several other larger budgeting and forecasting type projects that were more advanced, pulling data from Microsoft SQL Server or Oracle. You know, the type of projects where an expert Excel programmer can fully integrate the two Microsoft data programs with Microsoft Outlook, Word, PowerPoint, etc., so Excel, Access, and these other Microsoft Office applications would be fully integrated and automated. You do so using advanced VBA (Visual Basic for Applications, often called macros) to to the automation and integration. The vba code basically eliminates the mundane and repetitive tasks. This usually means that the macros would have to be extremely advanced, with all of the code being contained in the master Excel workbook. The cool thing about this system is that the users never needed to open any of the Access, Outlook, or other integrated files during the monthly update process; instead they used the point and click interface contained in the main Excel file, simple and easy to use.
Step 1: To run those monthly reports all that the user needed to do was to make a few selections using dropdown boxes and checkboxes on the Excel UserForm. For those of you that do not know what an Excel UserForm is, it is a form in Microsoft Excel that hovers above the Excel workbook, similar in appearance to an Access form.
Step 2 – The details: After the user makes the various update selections on the Excel UserForm, setting the filters and such on what they want the file to do in the update process, say filtering to new records only, or this month’s records only for example, the user then clicks the run button on the MS Excel UserForm and that action would run the Excel based VBA/Macros. So how would this work? One of the things that the code would do first would be to move data from Microsoft Excel workbook and load that into Microsoft Access. Once the data was dropped into a handful of Access tables, the Excel VBA would then run several Access action queries, such as MakeTable or Update queries, that would manipulate the Access data, running advanced calculations. The vba code would then pull subsets of the processed Access data back into Excel, just the data needed for this report, not all of the data. That means Access stores the big data sets and that Excel contains just the data needed. So once the data is back in Excel, the Excel macro would run many of the more common Excel functions such as VLookups, SumIfs and If statements in code, to populate the cells with the new figures, hard coded as values, not formulas. This keeps the file size down. Finally, and here is the cool part, the Excel vba code would automatically distribute the various Excel and PowerPoint reports to each report’s recipients via the company network (Saving files to particular folders) and emailing reports via Microsoft Outlook. Think about it, all of this with a few simple clicks of the mouse. What used to take a day and a half to run now runs in less than two minutes. User error is eliminated. This is why they repeatedly hire us, we save them loads of money each time they use our automated systems.
Microsoft Excel Sales Forecasting Tool: This time our local US client said that one of their other divisions, being in the UK, wanted to hire a local firm with extensive Excel programming experience with local offices in the UK versus working with a US firm because they wanted the Microsoft Excel consulting team to go onsite at their London location. They basically wanted to work with our UK division which was perfect; our UK office helping their UK office. Their UK division decided to hire us for our expert Excel consulting services based on the referral from their US office. So that was pretty nice. This division wanted to hire us to have us build a custom Microsoft Excel sales forecasting tool so that they would be able to better track their historical sales. This application would be very similar to the workbook we built for their US office down the street from us in Irvine California. Being able to reference the US application saved the UK client about 30% of the project cost, and there was no risk as we had worked with their US office on multiple occasions. That is why you always want to work with the same Excel consulting firm, preferably local. The MS Excel application that we built would provide the KPIs that the client needed in order monitor the results of their new marketing campaign, based on historical sales, which allowed them to more accurately forecast future sales. Their hopes were to find the best ways of increasing business.
The current Excel forecasting tool that we were being hired to replace was a series of Microsoft Excel workbooks, about 17 in all. Each MS Excel workbook had its own design and layout, literally, thus making integration and automation and the monthly updates much more complicated and time consuming, where it should have actually been pretty quick and pretty straightforward. In their old Excel based sales forecasting system, each month each of their sales persons would take a day and a half of their time to manually populate their Excel spreadsheets with the latest figures. After the user entered their data into the Excel files their manager would manually copy and paste all the records into the master sales forecasting Excel workbook. This would take the manager about a week to do. Again, another large amount of time, which is money, wasted, not to mention the opportunity cost of lost sales as these are sales reps typing numbers into an Excel workbook, when they could be client facing, sales.
The next manual step in the process was also very meticulous and time consuming. One again time is money, money wasted. Each month the manager would personally invest his/her time to analyze and verify each of the 23 tabs in each workbook to make sure that the data was correct and that it matched the numbers given by accounting. The workbooks did not have built in validation and verification as is the norm. As you might imagine, visual verification is prone to errors.
As the sales manager went through the process of reviewing the series of Microsoft Excel workbooks for incorrect values, the manager would manually correct any issues found, one by one. It would not be far fetched to image that the sales manager would miss one or more errors. If the manager missed an incorrect value and if the reports went out with wrong numbers in them that would cause a lot of problems as you would imagine, and it would waste even more of everyone’s time that used the incorrect reports. In addition to finding and correcting the previously missed incorrect values, the sales manager would spend even more time sending out the revised the reports. So it is very important that the sales manager thoroughly analyzes the Excell workbooks for accuracy, the first time.
In the old system, once the numbers in all of the Excel workbooks were visually verified by the manager, the manager would then create a series of charts and graphs as well as pivot tables based on the mounds of sales data. This was done on a monthly basis and would take several days each month to complete. Once again, another costly waste of time, more opportunity costs and lost revenues.
Microsoft Excel Sales Forecasting Tool Solution: After we analyzed their current Excel sales forecasting application we took a long look at their BIG data sets. We took the time to listen to what they needed in their new sales forecasting tool, this one built in Microsoft Excel 2016. We came up with a robust yet affordable solution. And given our previous work for their US office, we were able to cut 30% off of the cost of the work given much of the work was already done.
This time we decided to create a custom SQL Server database to hold all of their BIG data sets. We created a highly advanced Excel forecasting template with Excel UserForms for their sales people to enter their figures and with the click of a button those numbers where uploaded to the database using vba code. Using the same MS Excel template the sales manager could consolidate all of the records into a series of Excel tables with the click of a button on the Excel UserForm. The sales people with a click of a button could also see a summary to see how the numbers looked via an Excel Pivot Table. We made sure to create an admin Excel UserForm in the master workbook for the manager to use when he needed to update product descriptions, employee names and which divisions each sales manager is in charge of.
We then created a custom Excel dashboard with live charts, graphs and slicers. The sales manager could quickly update these with a simple click of a button on the Excel UserForm. That is how easy it is to get real time sales data, with comparisons to forecasts. The senior team could then use this amazing tool to filter by sales person, by product, or specific regions to better see how thing where going and where improvement could be made. The company now has everything they need to manage their sales staff, ultimately driving-up sales revenue business with just a click of a button. That is what we do, both in the UK and in Irvine California.