I need better reporting but I’m not familiar with reporting tools
Reporting – Everybody wants it perfect, easy, accurate and yesterday. Systems will always provide the basic financial reports, because the basic financial information is what any active system will receive in daily doses. But a growing business is often much larger than their accounting system, meaning that there are many transactions taking place that are not captured by their accounting system. How do you get a report that is easy and accurate when the system is not even capturing that data? This example required me to look outside the box for a solution that still makes me happy to think about.
The client was a manufacturer of high tech gas detection sensors used in industry and by the government. They were based in Silicon Valley, but were also doing business in Copenhagen, Beijing, Shanghai and Hong Kong. There was manufacturing, sales and shipments from each of these locations, and internal transfers of finished goods and subassemblies between them all. The US and Shanghai systems were using the same version of Sage Pro software, the Beijing and Copenhagen were using systems unknown in the US, and the Hong Kong office operated on paper alone. When I received a call from the VP of IT, she was asking for a daily report that tracked new orders, shipments and invoices from each of these locations. The report was to show daily, week to date, month to date, quarter to date and year to date subtotals for each of these categories, and be on her table at the morning sales meeting each day.
For starters, the data in the Sage programs was the best organized and the most easily accessible. We would start there.
The new orders and shipments needed to be filtered to make allowances for intra company transactions. After that it was easy to get the daily totals. Not so simple for the branches using paper or other unknown systems. These offices would be able to calculate the correct amounts for us, but emails going back and forth were not what a high tech company was expecting. For these branches we created a simple program for them to run on any of their workstations. When the program opened up, the user would log in and the program would check the internet connection. The user would see a screen which asked for all of the significant daily numbers. When they were entered and saved, the program would create a simple file with the data and send it to the company FTP server in California. Shanghai was using familiar Sage software, so we wrote a custom program within the accounting system to extract those totals and send them to the FTP server as well. While everyone back in California was asleep, Copenhagen, Beijing, Shanghai and Hong Kong were sending their info.
In the morning the VP of sales would go to her custom report in Sage, and here’s what happened: First, the daily data from the other offices would be copied to the local server. The program would then copy each of their contents into a custom table used for reporting. In that table you would see a record for each office, for each day. Next, we would extract the totals for the previous day in the USA, and add that to the reporting table. Finally, we would generate the daily report with all of the appropriate columns and subtotals.
This daily operations report was of great value to their growth, and allowed managers in the US to see trends and stay up to the minute on their operations in a way that was previously impossible to achieve.Tags: Database Management, Financial Modules, How To's