PBI gets the data you need enabling you to make the decisions that are necessary to improve and grow your company. We analyze the information with clear eyes and present solutions that can create dramatic improvements in your bottom line. Please view these case studies for examples of how we help businesses with their bottom line.
The company had an immediate need for a process improvement, accounting and Excel skills due to the sudden and unexpected departure of a key person. This key employee, of several decades, was responsible for the FAS 143 Asset Retirement Obligations accounting which encompassed hundreds of $ Millions in future liabilities.
There were 30+ interrelated spreadsheets that comprised the accounting system. However the spreadsheets were not linked. Data had been “cut and pasted” between spreadsheets and workbooks. Thus the tactical methodology of operating the spreadsheet system was lost when the key employee was lost.
The company’s ARO accounting process resulted from at least 3 different company mergers. Accounting methodologies and legacy data was inconsistent. As a result, there had been recent discoveries of individual liability variances in excess of $10 Million each.
The current IT system did not support the ARO accounting process. More than 350 separate projects spanning 20+ years were maintained on several series of enormous spreadsheets.
Process Improvement / Efficiency & Effectiveness:
The monthly accounting process took about one man week. It involved several data collection and validation steps between the engineering and accounting departments. Some calculations were very complex, (i.e. a 3 dimensional matrix allocation), while others involved 20 or more steps. Because of the process complexity and month-end time requirements, accounting was performed one month in arrears.
As with most consulting projects, we quickly assessed the project risk and established our list of immediate deliverables. Audit risk related to the complicated FAS 143 accounting requirements necessitated a quick study of the accounting concepts and requirements.
Quarter end financial statements for publically traded companies endure extra scrutiny. Thus we needed to quickly recreate the current process and demonstrate the same level of confidence to which the client had become accustomed.
Through a series of interviews with coworkers and file searches we were able to reconstruct the current accounting process. Additionally we thoroughly tested and documented the current spreadsheet process which provided management with greater understanding and confidence.
Professional Financial Modeling Techniques:
We developed new MS Excel financial models using professional standards. For those familiar with such professional standards, we used our own proprietary standards similar to those suggested in the PricewaterhouseCoopers white paper “The Use of Spreadsheets: Considerations for Section 404 of the Sarbanes-Oxley Act.”
We developed the new model in accordance with the Professional Business Intelligence standards “How to audit proof your financial models.” Each workspace, workbook and spreadsheet contains explicit documentation of purpose, data inter-relationships and links. Self-auditing formula were employed within each spreadsheet, workbook and between all workbooks that made up the combined workspaces. Thus, despite incredible complexity, the modeling was quick and easy to audit and validate.
In all, more than 600, 000 formulas were used of which about 150,000 were automatic data validation and testing formula. As we continued to add to the model over the years, the number of formula grew to total more than 750,000 with approximately 188,000 providing automatic data validation.
BPM and Automation:
The initial business process contained two significant constraints. First, there was the accountants’ data collection from the engineers. Then there was the manual / visual allocation of costs from 350+ projects across a 5 dimensional matrix over a 20 year period.
An iterative data collection and reporting process required multiple interactions with the engineering group. During which the accountants performed a series of calculations and engaged in additional querying of the engineers.
This iterative data collection and reporting process was one of the system’s constraints. We applied lean manufacturing principles and eliminated the iterative process by building in automatic data validation with the data collection. Data testing algorithms provided instant feed-back to the engineers via colored cells and conditional formatting rules. Thus data collection and testing occurred only once and the iterative process that consumed so many man-hours was eliminated.
Allocation of costs is a necessary part of the FAS 143 layering process. It this case it involved a nearly impossible manual / visual allocation process for which each annual forecast all 350+ projects was subjected to 5 interrelated validation tests. A few last minute changes to a few project estimates could add several hours of processing time.
We were able to codify the allocation process within a MS Excel Smart Business Application that simultaneously assigned costs to layers and individual years for all the projects, layers, and years.
As a result of these two process improvements we reduced the process time from several days to a few hours. Consequently the accountants were able to make entries within the current month’s data and the one month lag on the financial reporting was eliminated.
Data Integrity and Accessibility:
At the beginning of the engagement, the accounting department’s primary concern was managing the Asset Retirement Obligation Accounting (FAS 143). After implementation of the data collection models, and cost allocation codification, compliance with Sarbanes Oxley was the next focus. The next step became current data integrity and maintenance of legacy project management data
Large individual project variances (in excess of $10 MM) had recently become evident. These large variances were due to some of the legacy project data that was inconsistent with current accounting practices.
Obviously, maintaining such a large ARO accounting reporting system within a series of business objects such as workbooks creates several of its own hazards and risks. To alleviate some of these risks, a database was developed to manage not only current data but to maintain the integrity and accessibility of the legacy data. Legacy data was collected from a variety of information systems including JD Edwards, COGNOS, and SAP.
First the legacy data was collected and assimilated into the database / it solution. Then business processes were developed to efficiently collect data and maintain the ARO database going forward. Detailed procedures, including automation, were institutionalized. A direct link with SAP was not available, thus a “soft link” was developed. Data was manually downloaded from SAP. Then the transformation and assimilation into the database was automated.
Enhanced Project Performance Management:
A benefit of the data collection models was instantaneous forecast variance analysis for project managers. The next step was to exploit the new real-time feedback and provide business analytics and reporting dashboards. A live link was established with the COGNOS purchasing system. We then developed and implemented a variety of KPI’s, forecasts and project reporting tools.
Expenditure analyses covered a variety of angles. Ad hoc / real-time, business analysis were available to all the project managers. Thus spending by project, vendor, expenditure type or service, was available in real-time. Budget variances and business opportunities could be explained quickly and easily.
The final results of our engagement were remarkable. We have continued to support our client and the applications we put into place.
- Quarter end, Year end and budget accounting completed on time without overtime or incident. KPMG audit completed without any issues.
- Accounting process time cut from one week to a few hours. Current month data utilized rather than one month in arrears.
- Data management via a database has improved long term data security and integrity, especially as compared prior series of minimal documentation spreadsheets.
- Communication and collaboration between engineers and accountants improved. Engineering department’s focus has shifted to value added activities due to better, user friendly and timely reports. Project variances are proactively identified.
- An unexpected benefit was that sales tax audit reports were provided from the combined legacy and current system data.
Yet, even with a great success we realize that our smart business applications that use MS Excel are not a permanent solution. Thus we have advised our client to seek a more robust, database based system or allow us to redesign the MS Excel portion as a database solution.
After an exhaustive search and analysis of 9 months, our client was not able to find any solutions that fully matched the accounting and project performance management capabilities of our applications. One large company offered an accounting solution only that cost in excess of $1 MM. Since our entire engagement cost less than $40,000 this was quickly dismissed as absurd.
At Professional Business Intelligence of Tampa Bay, Florida, we have been the CFO’s and Line Managers that use IT solutions. We’ve also developed lean and cheap tools that get the job done quickly. We’ve implemented ERP systems that allow growing companies to prosper too. Let us perform your business process evaluation and you’ll have peace of mind that you’re moving down the right path.
The other day I was interviewing a potential business coach. Not that there is anything wrong with my business. I really love all areas of my business. Unfortunately there are only so many hours in a day and I have to decide where I’m going to spend my time.
You’d think that with all the initials behind my name I’d be able to figure out the answers by myself. But since I’m busy serving clients and enjoying all areas of my work, it’s difficult to focus. So my potential business coach says I need to allocate my time to the “highest and best use.” It was instantly clear.
My very best business turnaround used this principle. I didn’t call it that at the time but we applied the principle to the customer base of a company. You see, this company had plenty of sales. Yet it had never shown a profit in its 10 year history. This was the 3rd owner of the business and once again bank loan covenants were being violated and something had to change fast. So we developed the company’s business intelligence on the fly.
Sales were not the problem. The sales and customer service managers were constantly busy attending to customers. It was the profitability of the sales. We developed the business intelligence and analyzed the customers. This is when the “highest and best use” became instantly clear.
The customers with the lowest prices and lowest volumes made up only 20% of the sales $ yet they were almost 70% of all customers.
Then we looked at the cost of servicing these customers.
The customers with the lowest profit margin also required the largest amount of resources.
So for 68% of the company’s customers we raised their prices, required them to pay for inventory, charged them for special deliveries and cut off shipments until their accounts were brought close to current status. I think you know what happened.
We lost almost 70% of the company’s customers. At the same time, labor costs, delivery costs, aged inventory and aged accounts receivable all plummeted and profits skyrocketed. On time deliveries also increased so the good customers received better service. (For all the details, view the report at Financial look at Paper Products Manufacturer yields results in 8 months.)
We’re all busy running our businesses. Sometimes the obvious isn’t so clear. At Professional Business Intelligence, we combine financial acumen with information technology to help you gain perspective and achieve your highest and best use. And by the way, I hired this business coach for me too.
Professional Business Intelligence is a leader in developing strategic planning applications and Business Intelligence solutions, based in Tampa, Florida. The Experts at PBI create complex applications for large organizations to streamline their reporting mechanisms and capture the right information to make wiser strategic decisions. Contact PBI today to learn more.
How do small and medium sized businesses use business intelligence? What questions do they answer?
Obviously, it all depends on how well the business is doing financially. In these tough economic times, many focus on cash flow measures. These measures may include focusing on the details such as cash balances versus scheduled payments. Or they could answer larger questions such as “how long is our billing cycle from shipment to cash collection” and “how can we shorten the billing cycle”?
Business intelligence measures can also report on profitability and answer questions such as “Which of my customers generates the most or least profit for me”? As noted in my Case study: Financial Look at Paper Products Manufacturer yields results in 8 months,” analysis of the customer base was key to determining that the customers with the lowest prices and also had the highest costs.
“Shipping costs, customer service, design costs, and credits for returns and allowances were disproportionally high for the Low Price / Low Volume (LP/LV) customers. In addition, the related carrying cost of their inventory and accounts receivable was high.”
Business Intelligence is also used within operations. Here, only your imagination will limit the measures you may utilize. Just substitute any department name and process into the following sentence:
- Wouldn’t it be nice to know the effectiveness of preventive or predicative maintenance processes?
- Wouldn’t it be nice to know the effectiveness of the inventory control and production scheduling process?
- Wouldn’t it be nice to know the effectiveness of the shipping control and damaged goods processes?
Now that you know how businesses use business intelligence, how do you get it? Can you get it using the software you already own? Can you afford it? Professional Business Intelligence can answer these questions and get you started now.
Professional Business Intelligence is a business intelligence advising company based in Florida. The Experts at PBI can help small business owners create applications to streamline their reporting mechanisms and capture the right information to make wiser strategic decisions.
[frame_right]http://www.pbi-now.com/images/merging.jpg[/frame_right] Industry: Human Services Public Sector – Business Optimization
Merge two companies with very different work ethics and culture. Improve efficiency of service delivery. Construct and negotiate contracted services deliverables, measures and payments. Upgrade information systems.
- Customer service job enrichment: Cross train personnel between two organizations and merge customer service operations.
- Identify discrete, measurable service deliverables and develop performance metrics.
- Coordinate with program managers to develop individual deliverable goals and reporting system.
- Access state information systems and develop data mining tools to automate information collection, analysis and billing.
The two organizations came together as one with a singular focus. A new contract that accounted for the merged services was negotiated with a 5% increase in services provided. Actual services provided increased 28% and record profits were achieved. Customer service offices were remodeled and moral was high.
Contact PBI about streamlining your information structure to improve your business
Two box plants with combined annual sales of $30 Million had been purchased. The combined companies and annual losses of approximately ($1,000,000). Both companies’ production and inventory strategies caused them to be cash flow negative. Competitive pricing issues caused some customers to be unprofitable. The quality of their accounts receivables was deteriorating. New information systems had been implemented, but not all key functions were operating effectively.
- Stabilize financial reporting; assure bank auditors that financial controls were adequate.
- Review costing methodologies, ensure that customer pricing estimates and inventory valuations contained all relevant costs.
- Modify production and inventory strategy to one that would conserve cash.
- Analyze customer base, service requirements, profitability, overhead expenses and manufacturing capabilities.
- Breakeven was achieved within 1 year.
- Total inventory was cut by 50% for the two companies. The quality of the accounts receivable and inventory increased. Cash flow became positive and the borrowing base for both companies increased.
- Rationalization of manufacturing faculties was achieved. Less efficient facilities with high overhead expenses were closed. Production was moved to the primary manufacturing facility.
After production was moved and new lower production costs were achieved, rationalization of customer base was addressed. Prices were raised on unprofitable products. Some sales were lost, but costs were cut much quicker.
Want to change the direction of your business. Face the challenges of change in your business with Professional Business Intelligence.