Home Design Alternatives, Inc

How Things Are Built, Inc.

Systems Analysis of their

Accounting Software Replacement

Strategy Mistake

How Things Are Built, Inc.

How Things Are Built, Inc. (HTAB) is a small, but growing to medium sized corporation. They are a distributor servicing stores across the United States, Mexico, and Canada. HTAB is a privately held company that was begun in the early 1990’s. In the beginning they solely provided home plans that were purchased by home builders, professional and individuals. By 1997 they had grown enough to expand into the other areas after purchasing a distributor of various publications. Currently they distribute in excess of 250,000 CDs, books and magazines weekly.

The staffing is made up of 80 employees at the corporate office, 150 at two distribution centers, all located within the same campus, 25 field service managers located strategically all across North America, and over 900 contract workers, service representatives, that go into the stores and maintain the presentation by removing the out of date or recalled magazines from displays at all locations and sending them back so they can receive reimbursement from the publishers.

As with most companies, HTAB is made up of various departments used to help the flow of information to the accounting department so that they can properly reflect the company’s financial position. The Sales Department brings in new customers as well as keeps the old ones happy. Purchasing finds the best deals available at the lowest cost with the ability to meet their ongoing needs. Information Technology (IT) maintains the systems flow of information by keeping the hardware and software functioning properly, with minimal downtime.

The Accounting department is comprised of the Director of Finance and Accounting, Controller, Assistant Controller, Accounts Payable Accountant and Coordinator, Accounts Receivable Accountant and Coordinator, Magazines Accountant, and an Accounting Assistant. These individuals are responsible for receiving and fairly presenting all pertinent data regarding the financial position of the company. They are reliant upon the various systems that feed the accounting system. They receive data and transfer that data into Great Plains. This department is responsible for the accurately recording of all transactions affecting the general ledger and the generation of timely financials each month. It is commonly known that, according to Generally Accepted Accounting Principles (GAAP), the functions of accounting must be split, to alleviate the possibility of fraud and embezzlement. Even the slightest hint of impropriety is grounds for an audit point.

As HTAB began to grow, certain issues needed to be addressed, such as how to account for the recently acquired magazine trade and how to plan the displays at new Lowe’s stores across the country. Each problem was addressed, independently, with a “band-aid” approach. They fixed each problem they incurred to the satisfaction of that particular instance, without giving foresight as to whether this was going to be a recurring problem or if it would affect other areas, this resulted in creating a much larger problem in the future.

Accounting systems are made up of Accounts Receivable, Accounts Payable, Inventory control, General Ledger, etc. These systems are connected, passing information between them, for a consistency in the presentation of financial data. When a magazine, or book, is received in to the warehouse, it is measured and weighed, and its pricing information is keyed into the inventory system. For example, a magazine, “Gary Gardner Designs His Garden in 12 Easy Steps” is received. It is entered as being 8½” by 11” in dimensions, weighing .56 pounds with a cost to HTAB of $0.56 per magazine. Another shipment is received, this time books, “Harry Houseman Builds a House”, measuring 8½” by 14”, weighing 1.25 pounds, with a cost of $1.25. This information is used to calculate the size of boxes needed to ship the product, the expected weight of said box containing the declared number of issues, and the expected cost to the recipient.

Within HTAB there were five independent systems. Each system is used to acquire and record the various accounting data used to produce the cost, weight, and dimensions of the product to be sold, the quantity required to fulfill the customer’s expectations, the quantity on hand, the value of the shipment, and the resultant financials of the cumulative transactions by month. Brief descriptions of these systems follow:

The Warehouse Management System, (WMSPro) is used to record the inventory received and track it through the various Distribution Center locations and ultimately onto the UPS or FedEx trucks for delivery. Information from this system directly impacts accounts payable when shipments are received, accounts receivable when orders are filled and shipped out, and inventory control for both of these situations. Reports were created and transmitted to the accounts payable department so that invoices could be matched with purchase orders and entered for payment. Reports were created when sales were made and transmitted to the accounts receivable department so that the receivable could be established. Reports were made after the inventory was counted so adjustments could be made to correct for keying errors made on quantity shipped and received.

“WMSPro was designed to give you the tools to efficiently and accurately handle inventory movement by tracking merchandise from arrival at your dock through shipment to your customer. You will know exactly what items you have in stock and where they are. WMSPro will assist you in handling your merchandise as effectively as possible, reducing labor costs and eliminating losses due to poor product rotation. WMSPro will improve customer satisfaction by ensuring order accuracy and reducing turnaround time on orders.”

The Store Rep. and Magazine system (SRM) is used to track the time for the personnel in the field, the Field Service Reps that service the stores across the United States and Canada. This information is recorded in the field and transmitted to corporate via the internet. It is then downloaded into a excel spreadsheet and adjustments are made. It is then transmitted back to the various managers so that they may also ‘tweak’ the data before it is retransmitted back to corporate. Again it is downloaded to excel, reviewed, and then passed to the accounting department for payment. This is an in-house program written by the HTAB IT department. Even though it was created by HTAB, it does not communicate with the accounting system.

The Publisher Title Plan-O-Gram (PTP) is used to do two things. It records the accounting distribution of all books and magazines so that when a quantity is entered, it calculates and records the cost and adjusts the inventory accordingly. This data is recorded directly into the accounting system. It also serves as a modeling system to design floor layouts and displays of the magazine racks in the various stores. This system acts independently of the accounting system. Often prices are keyed into this system with a different price keyed in to the accounting system. Once these differences are discovered corrections are made and journal entries are prepared to adjust the discrepancies. This is an in-house program written by consultants contracted by HTAB. This system communicates with the accounting system, to a point. When items are processed through PTP, a cost per item is transferred to the accounting system, but not to the inventory system. If the price changes from the early estimate and is not noted, prices will be different in the two systems and this is very difficult to catch.

Plan-O-Gram (POG) is a work in progress, schematic, or diagram showing how the racks and magazines should be set up within the store to promote accessibility and allure to the customers to increase purchases.

Magazine Accounting Return Control (MARC) used to track all magazine purchases and shipments. This system also acts independently of the accounting system. Each month reports are generated from this system and manually keyed into the general ledger. Reconciling the two systems is painstakingly slow and tedious. Often data is lost due to malfunctions and has to be rebuilt or consultants from Computac, the designer of the software, must come to St. Louis to effect changes in programming.

The magazine volume is tremendous. HTAB ships in excess of 250,000 items per week, sometimes, during peak seasons such as summer resets or the end of year holidays, they double this rate. The magazines have an expected sell-thru rate of approximately 40%, meaning that of the 250,000 shipped out each week, 60% will be returned in some form or another. This volume, in and out, takes a tremendous amount of time to maintain. Again, reports are generated out of the MARC system and used as backup for manual journal entries for the accounting system.

Visual AccountMate (VAM) was the overall accounting system used to record the trial balance via journal entries, accounts receivable and payable transactions, and to generate the monthly financials, the income statements and balance sheets of the various companies.

“AccountMate has given us the best of both worlds in that Visual AccountMate^® is a powerful ‘canned’ package that also offers the flexibility for quick and easy customization. We can meet the specific needs of our client base, which has contributed to our excellent client loyalty. Considering that roughly 90% of our installations involve some sort of customization, Visual AccountMate^® fits our objectives like a glove.”

Luis Leon, Microtask Consulting, Inc.

Through the years VAM had been tweaked and modified in efforts to make it conform with the new software’s that had come into existence as HTAB grew. This was a partial success. The IT department created several “back doors” to go into the system and make adjustments when needed. These back doors were abruptly closed when the outside auditors discovered the manipulation of data. It violated several Generally Accepted Accounting Principles and threatened to reduce the annual opinion written by the auditors.

The main issue that confronted HTAB was that they had to enter data multiple times. Since these systems did not “talk” to each other they were unable to transfer information. This increased the odds that mistakes would be made, mistakes that could materially distort the financial statements. Reports are generated from each of the systems and certain of these reports are then manually entered in the accounting system. A “reasonableness” check is then made and if the totals are within an acceptable percentage of variance the entry is posted. From an accounting stand point this is very unsettling because these totals should agree and if they do not, care should be taken to find the discrepancies. In most instances the materiality rule is put in play, if it is less than a 5% variance, move on.

Morgan Kaufmann discussed the importance of systems communication in his book “Computer Organization and Design”. He stated that the use of direct point-to-point link for communication is essential and should be maintained either through natural or artificial means. To not have systems communicate is inefficient, ineffectual, and error prone. Several other articles in the collection of journals regarding this issue in Computer Communications stress how important it is to have a network of communications between distributed systems which is exactly the setup that HTAB has.

In 2003 HTAB management realized that there were problems with their recordkeeping and assessed the inadequacies of the transmission, or lack thereof, of information transfers. It was also discovered that there was an inability to store all the data for prior years due to lack of space in their current system, VAM. They determined that due to this lack of space, the consistency of their financial statements was erratic and the space issue must be addressed immediately. They went about analyzing how to make the flow more efficient and accurate. With the systems not transferring data there was concern for the accuracy of the financial information being distributed to various banks and other suppliers. The review committee was made up of the members of the IT department, the Director of Finance and Accounting, and the Vice President.

In late 2003 the committee decided that the solution to the problem was to purchase and install a new accounting package, one that could quickly assess the information provided and generate timely financials. The committee selected several accounting packages to examine, including an upgraded, windows based Visual AccountMate which boasted new and improved features and instructed HTAB that conversion would be minimal since the original software was there own. HTAB, feeling that VAM had previously let them down, coupled with the fact that they had bastardized the original VAM to the point that it would be unrecognizable to the new system, continued to look elsewhere. They inquired about IBM MAS200 as well as Microsoft Great Plains. The systems representative on the committee leaned towards the upgraded VAM because of the familiarity with the product and towards the IBM MAS200 because of its stability and longevity; however the financial representative was leery and gravitated toward the Microsoft product because of the brand name and the company behind the product and eventually he won the debate. Great Plains touts that its system is capable of working with several other systems, which is something that was previously not the case.

“Microsoft Dynamics GP (formerly Microsoft Business Solutions–Great Plains) is a comprehensive business-management solution built on the highly scalable and affordable platform of Microsoft technologies. It offers a cost-effective solution for managing and integrating finances, e-commerce, supply chain, manufacturing, project accounting, field service, customer relationships, and human resources.”

“ Integration with widely-familiar applications

Microsoft Dynamics GP works like and provides a high level of integration with other widely-familiar tools, such as Microsoft Office System and Microsoft SQL Server. This allows people across your organization—as well as business partners—to use skills they already have to adopt new processes. It also expedites data sharing across applications. For example, employees can view and share Microsoft Dynamics GP data from within Microsoft Office applications, or view budgeting, planning, and reporting from a Web browser. And because all of the components are integrated, information is automatically shared across applications. This helps eliminate time wasted on redundant administrative tasks.”

In 2004 HTAB’s accounting system was once again assessed because of several apparent flaws and was once again deemed inadequate and plans for the conversion were put into place. The company was potentially misstating the financial statements due to the inability of their various systems used to gather pertinent data to communicate, causing recorded cost per item to not be reflected in the financials, because it had been entered erroneously into the accounting system since it was entered two times, once when received, and again when it was transferred into the accounting system. It has been said that the most flawed component of any system in the human intervention. From the earlier example, let’s say the first 1,000 Harry Houseman books were received at $1.25, keyed into the accounts payable system at the correct cost, they were sold and the next shipment came in at $1.55, but the information was not passed along to accounts payable. This will cause the payables to be understated because it was recorded at $0.30 to little. When the invoice came for payment the actual amount is higher than what is recorded causing a deficit. There was hope that the conversion to Great Plains would alleviate this problem along with the data storage problem.

Conversion to Great Plains began in late 2004 and immediately began to run into issues with the programming. It seemed that since they had done a multitude of programming changes to the original software the mapping that was available to the Microsoft consulting firm in charge of the installation was of little or no use and had to begin anew. This proved to be extremely time consuming and continued through the year 2005 and well into 2006. During the conversion it was decided that the first group of consultants hired to effect the transition were incompetent and were doing too little, so they were terminated and a new set of consultants was brought in. This further delayed the conversion until they were brought up to speed. The conversion finally took place at the end of June 2006, not because it was ready, but that it was determined that the end of a quarter was the most advantageous conversion point from the perspective of the outside auditors.

Once the conversion took place, and the pieces began to settle, it became apparent that the initial assessment of the system and the problem was flawed. Too much emphasis was placed to the “here and now” issue of not having enough storage space within the previous accounting system Too much effort was placed in studying which system would provide this capacity and absolutely no effort was placed in how to resolve the issue of communication between systems to prevent the distortion of the financial statements. The statement by Microsoft that there was a “high level of integration with other widely-familiar tools” was assumed to mean that it would communicate with the other software’s currently in place. Further inspection of their claims revealed that the other systems they referred to were other Microsoft systems and not WMS Pro or MARC. The Microsoft consultants advised HTAB that there were teams available to come analyze the situation and “bridges” could be written to transfer data more smoothly, for a nominal fee. These fees were in the high thousands, some approaching 6 figures. This was unacceptable and it was decided to make due with what they had and to investigate other options when time permitted.

Data continues to have to be re-keyed in to the new Great Plains system, similarly to how it was done when using VAM. Keying errors continue to be made and financials continue to potentially be misstated. The only “checks and balance” in place are the various eyes reviewing the financials. They use several charts and schedules to determine if the ratios appear to be correct and if something appears out of the ordinary they examine the detail, and discover that there has been a keying error somewhere along the line. It was also decided that there would be no “tinkering” with the software to make it work with the other systems. Doing this would bastardize Great Plains to the point where VAM ended up and should there be another conversion problems would again arise.

The ‘bottom line’ of this analysis is that care should have been taken to properly analyze the issues at hand and determine what the lists of problems were. They should have assessed the list to determine the impact of the problems, and address them as a whole and not piecemeal. What HTAB did, in the end, was merely place another band-aid on their storage problem, created more work for the accounting department, and did nothing to help the flow of data between systems. What this failure to address the real problem will cause is making the cost of the new system extremely high, because of the additional purchases required to help the various systems communicate. If this would have been first on their list, or even if it had been considered during the review of other products, and questions asked, the cost could have been kept to a minimum by going with the product that easily allowed manipulation and was compatible with the program changes that had been put in place. They should have gone with the upgraded Visual AccountMate; worked with the consultants to effect their changes, modified it to communicate with the other systems, closed the back doors before going live, and moved on. It may take years before HTAB is ready to expend more money to do these modifications.

What IT has done is once again modified the old VAM software, renaming it Book and Order Sub System (BOSS) to allow it to communicate with several of the other systems, then having it communicate with Great Plains. It is apparent that this is yet another band-aid approach because it cannot transfer the most important information, the cost per item, from the beginning to the accounting system. It makes for a good data storage point, to gather information, before passing it along, coupled with the various reports from the other systems, to be keyed by the accounting department.

HTAB’s analyses of their system lead them to the wrong conclusion, that getting a new accounting system with a greater capacity would resolve the major issues. What they failed to realize is that the inability of the various systems used to gather and store the accounting data needs to be able to transfer data, directly between themselves, and not require human interaction. Through modifications to their old software, VAM, they have been able to construct pathways between other systems and it, then it to Great Plains, but this in and of itself is woefully inadequate.

HTAB Systems Relationships Before Great Plains

Flowchart: Process: PTP Flowchart: Process: MARC Flowchart: Process: HTAB
VAM Flowchart: Process: SRM Flowchart: Process: EDI Flowchart: Process: WMS Flowchart: Multidocument: Book and Magazine Plan O Gram Flowchart: Multidocument: Magazine Data Flowchart: Multidocument: Electronic Payment Data Flowchart: Multidocument: Sales Rep Hours and Salary Data Flowchart: Multidocument: Financial Information

Before Great Plains, each system generated its own reports which then had to be keyed into the accounting system sacrificing accuracy and several man-hours of keying and verifying data.

HTAB Systems Relationships after Great Plains

Flowchart: Process: PTP Flowchart: Process: MARC Flowchart: Process: BOSS Flowchart: Process: SRM Flowchart: Process: EDI Flowchart: Process: WMS Flowchart: Process: Great
Plains Flowchart: Multidocument: Book and Magazine Plan-O-Gram Flowchart: Multidocument: Magazine Data Flowchart: Multidocument: Sales Reps Hours and Salary Data Flowchart: Multidocument: Electronic Payment Data Flowchart: Multidocument: Financial Reports

With Great Plains, some communication was established between systems, the inaccuracies continued, the workload was increased due to restrictions imposed by Microsoft.

HTAB Systems Relationships as they should be

Flowchart: Multidocument: Sales Reps Hours and Salary Data Flowchart: Process: PTP Flowchart: Process: MARC Flowchart: Process: BOSS Flowchart: Process: SRM Flowchart: Process: EDI Flowchart: Process: WMS Flowchart: Process: Great
Plains Flowchart: Multidocument: Book and Magazine Plan-O-Gram Flowchart: Multidocument: Magazine Data Flowchart: Multidocument: Electronic Payment Data Flowchart: Multidocument: Financial Reports