This article addresses the common challenge of periodically updating multiple sheet Excel files with ERP data.
Templates are an excellent invention.
In their shapes or uses, from painting templates to email templates to dough templates, templates allow you to boost your productivity while preserving your work’s quality.
Say you were a pâtissier who had dough templates for making cookies; imagine that after using a template for one cookie, the template would break, and then you would need to “rebuild” the template for the next cookie. Not very practical- especially when you create thousands of cookies- right?
We view this hypothetical scenario as similar to what happens when financial analysts prepare periodic financial Excel files based on ERP data. Each time they want to produce such reports with charts and graphs, they would need to
- Print report in its native/CSV/Excel form from the ERP system (whatever is available in the ERP package)
- Open file in Excel
- Edit out unnecessary data and organize the report in proper columns
- Prepare pivot tables, graphs, and other data that is derived from original data
This mundane work is both unnecessary and wastes precious time, and may cause mistakes.
So, although Excel truly excels in dealing with data, it still lacks the option of absorbing data straight from the ERP system and automatically applying the data from reports to pre-existing files.
We see, hear, and feel you. Identifying this issue has pushed us to devise an innovative solution.
Meet B2Win Suite, the platform where you can send each of your ERP reports into an existing Excel File (which you would have previously prepared) as a reusable template that can have multiple sheets. This would be where your role as a repetitive machine is over, and your creative side takes over.
How does it work?
- When applying the Excel file for the first time (keeping in mind that you want to do this every week/month/quarter):
- You print your ERP report into B2Win Suite and apply the Excel template to it. The Excel template is an Excel file that you have already prepared in Excel.
- This Excel template can be stored inside a B2Win Suite shared folder or picked from a Windows shared folder.
- You decide to which sheet inside the Excel template the report will go.
B2Win Suite can send data to a particular sheet and can also make an Excel table. Excel tables are particularly useful in calculations and making pivot tables.
You may refer to the MS website for further information about the Excel tables https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c
- You can reorder, rename, and add fields to make sure your data is processed accurately into the Excel template.
- While transferring data into the Excel sheet, you have the option to overwrite existing data with its latest updates or only append the new and updated data to the existing data. In the initial process of applying the template to your report, you run all data to the Excel sheet. In your second time running the report and on, and if you can tune your ERP report to bring out the “data delta” every day, you can simply instruct B2Win Suite to append data to the end of the already existing sheet.
- You save the new settings, run the report, and print it. B2Win Suite allows for storing the end result in the same exact location of the original template. This way, we can produce rich Excel files that are constantly updated with the latest data.
- B. Next time you wish to run the same report, you just choose the report and click RUN.
So, for example, if you created an Excel template that includes multiple sheets with each one processing and displaying data differently, then after you use B2Win to apply the ERP data report on your Excel template, you will see that the data that’s from the ERP system is in one sheet in the Excel file, and the rest of the sheets have graphs and tables automatically calculated from the report data (depending on how each sheet is set to process data).
From then on, every time you print this report, you will automatically get a rich excel file with different ways to view your data.
Running the report:
But why is this worth it?
As a report-producing user, we hoped the cookie-cutter analogy made it clearly simple why templates are efficient, especially when repeating the same action periodically. Furthermore, it is noteworthy that such an automation tool helps reduce the risk of error when doing these steps manually.
Applying reports of progressive data to pre-tailored templates saves time and even boosts your productivity, hence moving you from wasting money to saving money to profiting money!
Don’t take it from us. Take it from our customers’ testimonials:
1-Norweigan Aerospace: “[B2Win] improved our efficiency in generating, converting and distributing Infor LN reports. Especially the reduction in the manual work to convert Notepad-files to Excel.”
2- American Electrical Power Generation Systems Manufacturer: “It brought a significant increase in productivity and reduced IT support hours required.”
B2Win suite will soon have you look at the time and be surprised that you are already finished.