CONSULTING
We provide consulting and project development services using Excel which include all areas of Excel, VBA, Power BI, Dynamic Arrays, external data linking. We work with businesses, large and small, with projects large and small, from a simple yet powerful single workbook to a full solutions based application. Most development projects are fee based with rates in the $150 per hour range dropping significantly for larger projects. Each project’s fee is based on the scope of the project. Many clients like to purchase a block of time up front which allows them priority access for questions, tweaks, etc. and not be billed for each call.
The unique aspect about our projects is that they solve very challenging issues reducing reporting time, providing reports that increase revenue or decrease cost substantially. While the solutions provided are using Excel, they are also using our skills and experience which provide for successful completion of the objectives. Some of projects are listed below, ranging from multi-million dollar refinancing to seven figure litigation support settlements.
Our extensive CPA, CFE and litigation support backgrounds and 30 years of providing these services gives us an edge over others keeping the costs down and quick completion of the projects. Of interest, about 30% of the projects listed below are CPA firm support services where we worked with their client to accomplish the needed objective resulting with a Solution that far exceeded expectations.
Substantially all of our completed projects allowed for either increased revenue, decreased costs or both in which far exceeded the cost of the project. Additionally, most projects allow for greatly reduced input and reporting time which is an intangible benefit allowing for greater productivity in the sales or refinancing process and other related processes. Most all of the projects listed are not a typical Excel project. They used Excel to create complex methods, nesting functions, VBA automation and proprietary algorithms which results in fast input, error trapping and timely reporting.
A General Motors Auto Dealership Marketing and Sales Promotion. The dealership was implementing a $200,000 marketing campaign focusing on different car models. In summary, “if we spend this much here and get this many sales, how will this affect net profit by model line and in total”. The pre-Excel Lotus 1-2-3 spreadsheet was in the General Motors P&L format which was in great detail. Phase One was to perform the analysis and provide the marketing focus and the ongoing process. Phase Two was to integrate actual-to-projected amounts. The net result of this project was that the owner was extremely enlightened as to effect of where and how money was spent, totally contrary to the prevailing thinking. The marketing program was extremely successful and opened the door for many other consulting projects. Of interest, while this was a pre-Excel, Lotus 1-2-3, project in which many of the processes and algorithms created became the foundation for my future Excel based projects.
The San Diego Chapter of the Association of Legal Administrators (ALA) requested our services for their annual Salary and Benefits Survey. The technology at that time was the survey questionnaire was a paper document and paper mailed to the participating law firms. The returned questionnaires would then be “typed” into the Excel. From there complex array formulas were performed on the survey results and report. Over 250,000 calculations were performed in less than a day. Fast forward eight years; the survey questionnaire was done online; the data was downloaded into Excel with the results being almost immediate. This is just one example of Info-Stat’s “Zero Time Reporting”. Of interest, I created several very complex Excel procedures (some of which included array formulas), custom functions and macros to accomplish this. Fast forward to today, Excel’s Dynamic Arrays would make this project extremely efficient.
This linked file is a project organization chart of a high profile litigation support project where “follow the money” was the key focus. The chart represents the factual organization structure, the entity names and notes have been changed for privacy and are 100% fictitious. This project is the focus of my current “follow the money” webinars.
We hear a lot in the news about this government investigation, that investigation, special councils and so on. And many times the fact pattern is layered with both operating and shell corporations and LLCs along with many family members and close business relationships. These government investigations typically take forever, and the report is delivered well after its usefulness, yet all the information was at their fingertips (or most of it) from the start. This project was much more complex than these and was completed in about twelve weeks with a seven-figure settlement. Why am I mentioning this? To simply show that good professional services working in a focused manner, weeding through the clutter with a clear objective in mind and with the concept that time matters is what makes a project successful and useful.
One aspect of this project was distinguishing between a true money trail and transfers that were designated as loans and peeling back the disguise to reveal the true consideration in the transfers. While “loan repayment” or other notes in the memo section of the check and how it was recorded in the books are seemingly evidence of a loan, the lack of a promissory note, no periodic repayments and lack of interest reveal the true nature of the scheme(s). Proprietary automated Excel tools helped in matching transfers in-and-out, in the same or other entity.
Info-Stat was contracted by the Hahn Shopping Centers to build an entity-wide template to track the remodeling of all Hahn Shopping centers in preparation for the sale of the enterprise. The big decision was the tool to be used, Lotus 1-2-3 or Excel. Lotus was the better choice due to familiarity, ease of use, and overall power. However, Excel prevailed as it was clear it was the future. This resulted in a huge learning curve and the severe limitations built into Excel v4. Despite these challenges the end product allowed for a great tool for the process, unlimited scenarios, continuous ROI calculations, and more resulting in reducing the total remodeling cost, reducing the remodel time, and greatly facilitating the sales process and higher selling price. You will recognize the result when you visit one of the many Westfield Malls in San Diego County.
The project culminated with their refinancing of the entire project and the processes and reports, that we developed, allowed for the due diligence team to cut the time in more than half, allowing for the $225M refinancing to be completed well ahead of the original projections.
Privacy in the healthcare industry has always been a challenge. With HIV in the headlines, the issue was very much in the forefront. We
developed a system for the San Ysidro Health Center where the HIV patient would enter a clinic and enter only information known to them to
provide access and allow the clinic to see their record without knowledge of their identity. Their identity could be revealed with their
permission during the patient visit. This system was so successful we continued to expand the system. This was Excel based which allowed for
fast development while being extremely technologically sound, yet flexible. This was later moved to an internet solution, using our work as the
foundation. As large internet providers brought similar systems online, ours continued to be a top solution as it was fast, easy to use as compared
to cumbersome and less secure systems from the the larger providers.
Biotech company was using an incredibly cumbersome Excel based series of spreadsheets tracking budget, actual and budget-to-actual system. This was created in Excel using basic formulas, copying data for monthly updates, massive reentering data when the budget was updated, multiple linked files and so on. Our project reduced this to a “zero-time” task. Simply click on the month in the drop down list box and all the reports were generated with just the one click using a combination of embedded dynamic formulas that we’re updated with the month number. The spreadsheet required data to be imported from external sources. Once this external data was normalized, a one click macro brought in the current month’s actual data allows for manual review before the actual report generation.
This project provided management and the staff with much needed reporting the same day or next day after month end accounting close. Additionally, the algorithms used in this project were used again in several other similar projects, each one becoming more powerful with the new updates.
Company was improperly calculating 401(K) contributions which was not discovered until after the sale of business and was going to result in a large correcting contribution. This error spanned over the full life of the plan. They entered into a Voluntary Compliance Program (VCP) with the IRS where they would calculate the deficiency with a sample of employees and apply the result to the population as a whole. The IRS was hesitate to accept the VCP calculations as they were concern with the accuracy in applying the sample representative to the whole. My Excel spreadsheets were comprised of several imports, cleansing that data, and reformatting the data as several payroll services were used over the years each with their own format. Most importantly, the only way to track the employees was with their social security number as employees marry, divorce or otherwise have name changes, including the payroll service abbreviations. I created a system of holding the social security numbers in a series of separate Excel files and encrypted the SSN in those files. They would be decrypted and imported into the primary Excel file each day and then linked to the appropriate employee. At day’s end the process was reversed so that it wasn’t possible to see a particular employee’s SSN or identify the data with the employee. These processes were automated with my proprietary tools and took only a couple of minutes. On the regular Excel side, the formulas, methods and procedures were highly customized and complex including error trapping. Remember we have employees changing names, departments, pay rates (including exempt and nonexempt) and so on. The end result was the IRS signing off on the work of only one sample (after several lengthy discussions and negotiations) allowing for complete resolution and the company’s buyer being satisfied that the contingent liability was resolved. Of course, the seller paid the amount to the buyer for the correctiog contribution.
The promoter and general partner needed a tool to calculate and report on the funds invested by investor in total, by month and annually with a wide variety of reporting requirements. Since operating funds were from both investors and traditional finance sources, monthly payments (principal, interest and amortization) needed to spread across each monthly and annual worksheet along with investor rate of return and other present value calculations. This tool allowed for more precise timing on the purchase and related leasing of vehicles maximizing cash flow and investor ROI which included investment tax credits.
Rate of return, net present value and other present value concepts are critical in this industry. I was engaged to review the entire process in evaluating commercial real estate projects resulting in calculations that were more appropriate for the project as well as correcting errors both in the math and appropriate calculations for the related project and objective. Additionally, the Excel files were redesigned allowing for individual team members to prepare the project calculations. The end result was a five-fold decrease in time to project approval (or project modifications) and decreased lead time for project decision making needed when the commercial real estate market was turning.
Manufacturer of metal containers and ends (think tuna cans and lids) with manufacturing processes performed at different physical locations (stamping at one facility, heating at another, etc.) The client had some very elaborate, but cumbersome, Excel spreadsheets calculating and comparing costs to help make the entire process more efficient and to reduce manufacturing costs. The meat of the spreadsheets was VLookup formulas. My completed Excel project helped to streamline this process, contained error-trapping and helped to make decision that changed the ultimate course of the business. (Van Can, was Ralston Purina’s commercial endeavor to manufacture cans and ends for their Chicken of the Sea tuna and Ralston pet food products, a 1973 make vs. buy decision.)
The company had a very robust but dated standard cost accounting system. Once installed it was quickly realized the chart of accounts was designed with unnecessary detail, creating a cumbersome set of tasks to group and otherwise manipulate the accounts into more meaningful reporting. The nature of the dated system made it cost prohibitive to make the needed changes within the system. Using advanced but not widely known Excel features and techniques, the entire monthly reporting was reduced from 155-196 pages to 9 pages of meaningful reporting with drill-down capability. While printed reports were delivered to top management, live interactive Excel file reporting was available for the entire manufacturing and accounting team. The immediate result was information to change the production lines and scheduling increasing output (delivery timing) without an increase in related cost.
Tracked ship movements (think North Island and the San Diego Bay) was done 100% manually. The manual process wasn’t all that bad but it didn’t allow for accurate or timely reporting. This became very important as it was not understood by many how many ship movements occurred prior to deployment. Additionally, the tool aided in berthing assignments as ships don’t always return to same berth depending ship size and other ships arriving while others are out for exercises or deployment.
As can be imagined, this was a massive project which, overall, was well done and effective by government standards. But as with any large organization there were massive inefficiencies in some areas. One area was the manipulation of external data to be integrated into the web based budget format. Several automated Excel tools were created to solve this issue. One interesting area was protection of the Excel files. This was not a secure file issue, it was needed to prevent staff from changing/updating budget drafts in which there were several over a 2-3 month period. It was a two day project to password protect the many files and an equivalent amount of time to remove the passwords for the next draft update. I created a new automated process with sophisticated linking and VBA macros to accomplish this in about two hours. At my suggestion, an automated Excel tool was created to track changes to the spreadsheets; the before and after changes along with who made the change and date and time of the change. The changes were in a hidden secure file with access only at the higher level. (The files were not centralized.) Of interest, Naval Base San Diego, along with Naval Base Norfolk, VA are the two largest Naval Stations and budget input into the U.S. Navy Budget.
When the purchase card program was first implement there were several internal control issues on of which was the lack of understanding how they were to be used and not used. The lack of accounting background by most everyone lead to unworkable solutions. Our task was to educate the purchase card holders and their supervisors scattered all over southwest US.. The Navy staff had created a (typical government approach) to have a game plan for the education in four weeks, the training for four more weeks followed by testing. We created a website for the purchase card education and instructions and tested 87% of the holders (minimum passing grade) in less than three weeks. The first benchmark meeting (at the four week mark) was totally disruptive it was a challenge to get the team to believe the progress made. Two key results: the purchase card issues dropped to less than 10% making the issue more manageable and secondly opened door for us to expand our contract as they saw what we could do in understanding the Navy issue and finding and executing the solution. While this was primarily a web based project the end product was a Region wide Excel reporting tool that was instrumental in getting everyone on the same page.
Dashboard
Created several automated dashboard style reporting tools. Preparation time was reduced by over 300% and accuracy was increase to substantially zero errors. My Zero Time Reporting solutions allow for fast development of these tools. Of interest, access to the dashboard input and output (reports and presentations) needed top level security. In order for a successful project this access needed to be easy, not getting bogged down with endless logins and passwords.
My Role
I was engaged by Anteon Corporation (San Diego) to help with the Navy Contract that included the above tasks (about 50% of the total contract). My expertise with large Excel based projects, web-based projects, systems integration, history of completing large and complex projects and having the required security clearance was instrumental in my selection. Shortly after the completion of the contract Anteon was acquired by General Dynamics and became an integral part of General Dynamics Information Technology (GDIT), a wholly owned subsidiary of General Dynamics.
The existing monthly reporting process was well over an eight week process. Our project, when completed, reduced the reporting time to about two days. The budgeted and actual financial data was downloaded from the University’s main system. It was filtered upon downloading to keep the number of records to a workable number, then filtered, grouped, etc. within Excel creating a data-mining environment. The final project was budget vs actual, in total, by departing, by sub-department and by category of classes. This includes separate fund account reporting. The reporting was interactive by the user for each of the desired reports and was both paperless, printable and PDF at the users choice.
After the system was in place for a few months and receiving input from the users, the automated process was further updated with the reporting time reduced to about four hours and immediately available to the users in the Universities intranet system.
The immediate reporting was critical to determine the profitability of the extension courses and the projected profitability of courses that have run their course and have slow declining attendance allowing for more timely termination of the course before the profitability turned negative.
Entity constructed, held and with the intent of the eventual sale of the individual apartment complexes. The grossly inflated selling prices were subsidized by loan terms, lower interest rate, balloon payment due dates, etc. which brought the overall terms down to the real fair market value. The purpose of the inflated sales prices was to inflate the revenue of the entity for eventual sale of the entity, which eventually did occur. The acquiring entity immediately soon began to suffer from these terms and initiated legal proceedings. Our litigation support role was to calculate the actual value of each project using present value techniques. Our challenge was twofold. First to obtain the information and perform the calculations; and second, to present the results in an easy to grasp and understand resulting in an accelerated settlement agreement. The result was to restate the audited financial statements from the inflated net worth to an equivalent accumulated deficit and the ultimate dissolution of the company as result of the settlement with buyers.
The company had several capital leases that were not capitalized as lease assets. Performed the calculations to determine the correct capitalized cost. This type of calculation today is mainstream with sophisticated programs but at the time was not widely available. With hindsight the developed Excel tools were more flexible than many of the tools available today.
Various organizations utilize fund accounting which can be a challenge without specialized accounting software. I have developed several customized solutions by linking (or utilizing downloads) to create the needed fund accounting reporting. Consistent with my Zero Time Reporting concept, the fund accounting is presented along with other desired reported immediately upon the entering of the data into the existing accounting system. The important concept that many organizations fail to remember is that the basic accounting must be in accordance with GAAP (or another accepted comprehensive basis of accounting) that provides good reporting under that system and then design the desired reports (such as fund accounting) that run automatically as needed. With many of these organizations, I worked with the client to design and implement solid accounting procedures which included practical, yet solid, internal controls that ensured solid accounting and reports that the user could understand and rely upon.
The UCSD Extension Budgeting project noted above is the largest project utilizing this concept.
A poorly capitalized photo lab with massive wide fluctuations in revenue due to the seasonality of the customers resulting in the need to lease photo lab equipment. Since capital leases are reported at their net present value and the lease payoff is the full amount of the lease payments for the remaining term, it would be easy for the financial statement and related reports reader to not grasp the severity of the remaining amounts due. The buyer of the company was “the smartest person in the room” and negotiated terms that assumed a lower payoff of the leases. Once all their “superior negotiating skills” failed in this regard they came back to the company demanding a significant reduction in the selling price. The interesting part of this is that each lease was fully reported with no open questions, presented both in a straight forward and financially sound manner with all the cash flow and somewhat complex present value concepts clearly displayed in a matter that “even the jury could understand.”
From a practical matter it was amazing their total lack of understanding, but we were prepared with the proper reporting supported and FASB 13 rules, solid present value concepts and math and easy to read supporting schedules.
Separately, the company had several thousand clients. As photo lab, the type of customer was widely varied with different profit margins and turn-around times. This information was generally available but not in a usable format for analysis. Using Excel pivot tables, many analysis were performed to highlight the needed information. The end result was many pricing changes, increasing the companies gross profit by 22% overall and over 40% in some of the product lines. This helped dramatically in the later negotiations for the sale of the company.
A high profit financial services firm signed a contract for a new service to help with proposed stock and other security trades. Once implemented, the service far exceeded expectations. What was missing was a reporting section on the back-end to track and to calculate the gain or loss when the trade was closed (sold), which internal department the trade was associated with and a easy method to catch errors. This was a large firm with hundreds of trades per day. The cost of reconciling the transactions was expensive both in time and missed deadlines for catching errors and the related adjustment. The firm was so enamored with the new system the focus was on the the front end, which turned out to be greater than anticipated, that they simply didn’t ask these questions. In this case, when the back-end question came up, management simple said “those are details for the accounting staff to work out”. The resulting cost of this oversight was well into six figures, far in excess of the cost of the system. I got involved to create an automated Excel based system to reduce the back end accounting to two hours per day for one person compared to 6-8 hours per day for six people.
The automated Excel tool would import the trades, reformat the layout, match the buy and sell transaction, create a customized CUSIP list (the primary key to reconciling), match the CUSIP and provide reports in several different formats.
The first example is each department sending Excel files for consolidate at the corporate level. Each file was in the same format. Upon receipt of the files the staff then combined them into a consolidated version taking several hours and prone to error. There are a couple of easy ways within Excel to accomplish this. The one selected was that upon opening the master consolidation file the consolidation was completed immediately upon opening the file. Zero Time Reporting at it’s best.
In the second example, the client needed to put over 500 files and worksheets into separately designed files. No consolidation of numbers just reorganizing the files. The estimated time for the project was astronomical. I completed the task manually (no automation for this one) in about three hours with much better worksheet and file organization than originally hoped for.
As an add-on to the project I created a macro to create a table of contents on the first worksheet in the file from the worksheet tab names. This allowed easy reorganizing the worksheet or renaming them with just click to update the table of contents. A simple click on the table of contents item jumped the user to that worksheet.
Of particular interest is the CPA industry. These same tools have been enhanced for the CPA to customize the imports, trial balances, financial and other statements meet their needs for audits, etc. plus custom tailored for the clients. While there are third party tools available for these tasks, many are too cumbersome, many times very limited in features and very costly. We supplement these tools not competing with them.