Excel: Financial Rebate Report Tool

Overview:

A large and complex Excel spreadsheet with custom functions and macros was used by a company to track suppliers and clients’ rebate payables. The data entered into the spreadsheet was also manually entered into the ERP system to invoice relevant parties, resulting in duplicated data entry. This spreadsheet was critical for the company’s budgeting forecasts and business decisions but had become slow and outdated, and needed drastic improvements to incorporate new features and requirements.

Solution: I was tasked with reverse-engineering the current spreadsheet, understanding its purpose, and identifying what worked well and what didn’t. Staff received financial data in Excel format from the suppliers and clients and manually entered the data monthly for each supplier and client figures.

Improvements Implemented in the rebuild:

  1. Data Loading Mechanism: Built a mechanism allowing staff to select multiple templated files from the suppliers and clients, perform data integrity checks on the file and then load the data, reducing manual data entry and errors.
  2. Efficient Calculations: Designed the new version to generate reporting views only when needed, improving calculation efficiency.
  3. Dynamic Reporting: Developed new dynamic reporting and custom functions, dramatically improving reporting quality.
  4. ERP Integration: Generated an import file for the ERP system to reduce manual data handling, autonomously storing files in the correct server folder and printing copies as part of company processes.
  5. Automated Reporting: Created automated summary reports for the CEO, suppliers, and clients at the end of the financial year, previously manually generated.
  6. Proactive Investigation: With automation, staff had more time to investigate and follow up on anomalies with suppliers and clients as well as well as be an additional resource to the business.
  7. Backup and Version Control: Added automatic file backup and version control, cross-referencing ERP system invoicing data with Excel to ensure accuracy.

Problems:

  • Duplicated Data Entry: Manual data entry into both the spreadsheet and ERP system led to inefficiencies.
  • Outdated Spreadsheet: The existing spreadsheet was slow and lacked new features.
  • Manual Processes: Monthly manual data entry for each supplier and client was time-consuming and error-prone.
  • Full-Time Workload: The previous spreadsheet required the bookkeeper to work full-time on it daily.

Solutions:

  • Automated Data Loading: Reduced manual data entry and errors with a data loading mechanism.
  • Optimized Calculations: Improved efficiency by calculating and generating reports only when needed.
  • Enhanced Reporting: Implemented dynamic reporting and custom functions for better reporting quality.
  • ERP Integration: Automated the generation and storage of import files for the ERP system.
  • Automated Year-End Reporting: Freed up staff time by automating financial year-end reports.
  • Improved Accuracy: Ensured data accuracy with automatic backups, version control, and cross-referencing with the ERP system.

Impact:

  • Efficiency: Reduced the bookkeeper’s workload from full-time to 5-10 hours per week.
  • Accuracy: Decreased incorrect data entry errors.
  • Time Savings: Freed up staff time for proactive anomaly investigations.
  • Improved Reporting: Enhanced the quality and efficiency of financial reporting.

Contact US

Fill out the form below, and we will be in touch shortly.
Contact Information
What can we do to help:

Thank you for viewing your sample site!

We take the stress out of websites for you and offer quick, easy web design so you can get your business online as quick as possible.  Start growing and get exposure now!

Below is what you get when you sign up to our site:

  • Unlimited website content updates*
  • Professional email address
  • Online Contact forms
  • Domain name
  • Web Server Hosting
  • Full site maintenance, updates, backups and restore

*Content and images need to be supplied

We also offer additional growth packages to Jet your business to the sky!

  • SEO keyword reviews ($2500 per month)
  • Marketing Content Generation
  • Online Ecommerce Store
  • Online Booking & Appointment System
  • Online Payment System (with Paypal/Stripe/Square)
  • And MORE!

Let us know below if you would like your site started

Lets Get Started!