Case studies

Below are a few examples of how we can help your organization to be more productive.

This is of course only a subset of areas of our expertise. Contact us with your questions and find out how we can help your business operations: +36 30 289 2050 or info@excel1.co.uk

1. HR new hire administration process

When a company has no integrated ERP solution for the HR processes, quite usually the HR team fills out every single hard-copy document which is required for a new employee to start his or her employment. These documents can be:

– employment contract
– job description
– employee’s rights as per labour law
– new hire register
– pre-employment health screening sheet
– non-disclosure agreement

These documents at least need to be signed by the new employee with the date on the sheet. This means that the HR team needs to modify each template for each new hire. Of course, the employee contract contains many more variables and often are filled in by HR manually.

How this can be automated with Excel?

Every new hire needs to fill out a ‘new employer details’ template prior to employment. If the template is created in Microsoft Excel and it is amended with a section filled-in by HR then all the information is in one place for the hard-copy documents.

With a professional Excel-based application all of these documents can be created with simply a few clicks.

And one more thing: HR will need to store all the employees’ records electronically in a table format. The table holding all employee’s details is also usually filled in manually by the HR team. And once an employee’s department, salary or employment status changes, it often means long and tedious work to retrieve employment history (salary, promotions, etc..)

We can automate this process too with Excel macro programming, amending the current HR table by programming all these functions. This means that you will keep your current repository with all your present data, but will gain the new functionality the day you introduce the new process.

2. Daily turnover/delivery/outstanding reports

Do you start each morning creating the daily reports in Excel?

  • Working from multiple source files
  • Deleting rows and adjusting row-order in the raw data tables
  • Checking for abnormalities and exceptions in the raw data
  • Correcting or deleting data from the raw data set
  • Copying all your data to the appropriate files and sheets
  • Copying your service-column formulas
  • Looking for new customers and add those to your customer base
  • Refreshing your pivot tables
  • Filtering the data to the actual day’s analysis
  • Adjusting your charts to have the right data and format
  • Creating multiple versions of the final report for different stakeholders…

…and your time is gone. Every single day..?

We help you automate the repetitive process of creating daily reports with application development in Excel. With our custom-built Excel marcos all you will need to ensure is that the raw reports are downloaded to a specific folder and the macro will take care of the report, upon your command.

3. Fragmented, bad quality raw reports

Do you often need to work hours with manual data reconstruction before you can start the analysis of the data?

It happens quite frequently that the data is downloadable from the system, everything is there, but the format of the data keeps us from using a Pivot table and do our due diligence.

A slightly disturbing example would be a raw monthly customer report where sales line items for each customer are grouped in the raw report, but the customer name & ID is only highlighted at the top of each group.

So, how do you create a sales by customer analysis or an outstanding AR (accounts receivable) report by customer for your month-end closing report? Without the customer name or ID being in each the rows of your raw data it will be really difficult and time consuming.

We can help you to automate such report with VBA programming, so you will have all the customer ID-s and names next to each line item in a separate column. Of course, in addition we can create the summary tables and charts for you, too.

4. Intelligent data collection templates and data aggregators

It happens from time to time that information needs to be gathered from employees, partners or vendors and then all the incoming information needs to be placed in a single data table for analysis.

We offer a reasonable and easy solution for these tasks in Excel. We use foolproof fields, buttons, switches, drop-down lists, protected areas and an automated aggregator which puts all the data into a single table for easy handling and reporting. This saves a lot of misunderstanding, time, resources and especially frustration. The more fail-safe and automated a form is, the less extra effort will be needed to complete the data collection exercise.

Planning your data gathering with our services using VBA programming will save you time and give you peace of mind.

5. Quote generation

Picking from hundreds or thousands of products and copy those to a single quote sheet. Creating the summary of the products, the details, the header, adding general terms and conditions, annexes, printing in PDF. Create a slightly different extract of the quote for logistics, and another one – of course with a different layout – to finance.

When you experience that data is already in Excel and you need to type or even copy it to a different location, it is a strong signal that the process can be automated with macro programming.

So, we can automate such processes using Excel macros and VBA programming to free up time for your team letting them use their resources to do more creative tasks.

6. Data-driven decisions

You do have a clear idea where you want to take your company? Imagine that you have clear understanding where your company is actually going.

We deliver dashboards for you which give you visibility of your processes and let you make the right decision using data that is already available in your organization. We help you organize that data and make it possible to gain insightful information with the Excel-based solution: VBA programming.

You probably also have a good idea of what metrics you require to gain the right level of control and visibility over your business operations. However, if you feel that you need support to develop the right set of Key Performance Indicators (KPIs), we can help you. Just a few insights that would probably be beneficial for you to be up-to-date about:

  • Turnover month-to-date/year-to-date vs prior year vs budgeted amounts
  • Turnover by brand / location / sales team / divisions
  • Cost of operations month-to-date/year-to-date vs prior year vs budgeted amounts
  • Cost of goods sold / Gross Profit
  • Item per customer / Sales per customer
  • Project-based return on investment
  • Late delivery / defective unit share
  • Service Level Agreement (SLA) compliance (both timeliness and quality measures)
  • Outstanding Accounts Receivable (unpaid customer invoices) portfolio – 30/60/90/180/360 days overdue items
  • Built-up inventory causing waste in your processes

With our Excel application development capabilities all of this data and more can be automatically available to you, as frequently as you need.

7. Budgeting for complex services or projects

Planning budgets for customers or internal projects and continuously monitoring cost & revenue, sharing the updates with 3rd parties and creating pdf copies of the main figures? Having multiple cost elements and categories, often changing setup and prices while the need to update customers is constant?

Consult with us and we review your processes. With Excel-based VBA programming we automate parts or the whole process for you. As data protection is paramount to us, prior to working with you we sign the data protection and confidentiality agreement to provide you safeguards for protecting your data.

8. Controlling system for project-based cost/turnover assessment

Do you have a control system behind your project-based operation? Do you know at hand how profitable a project is at your company? We help you build one with VBA programming, using the raw data available from your systems.

If your team of consultants or lawyers is collecting the billable hours to each of your projects then we can build an Excel-based automated system for you that tells you how profitable your individuals or your teams are, what is the cost/turnover share in your projects. To emphasize the importance of data protection for us, prior to our cooperation we sign adata protection and confidentiality agreement to provide you safeguards for protecting your data.

9. Summary reports based on special criteria

Do you have special tables where you need to store so much information associated to a single cell that you need to colour-code the fonts, set various border styles, border colours, fill colours or font size? You probably know that building summary tables based on these attributes (font colour or size, border style or colour, fill colour) is not a straightforward exercise in Excel.

However, with VBA macro programming we can help you build summaries for you so that you get your full summary and analysis in just seconds.

10. Webshop stock maintenance

Quite often webshops require more maintenance than desired. Upon receiving the new product lists from the supplier, it often needs to be uploaded to the website. But prior to that we need to identify certain kind of products in the new list manually:

  • New products which have never been in the shop before – these products need to be profiled in the webshop
  • Products which have different IDs in your shop then in the supplier’s list – these ones need to be treated with special care
  • Existing products with new information, such as changed price or other parameters

In addition, one needs to keep track which products are becoming obsolete so therefore need to be removed from the webshop.

This is another great example where Application development in Excel can help you. With VBA programming we create a program for you which create all of these lists automatically so that you only need to update the data in your webshop without the long process of identifying which products are to treat with special attention.

11. Special reporting to authorities

Do you have special reporting obligations to certain authorities based on various criteria in your business? Sweeping through big sets of data to find the line items fitting the criteria for these reports can be tedious and frustrating. If you share the criteria & parameters with us, we automate this task for you with Excel-based macro programming, saving you time and frustration.

12. Creating frequent randomized tests

Do you need to create frequent randomized tests from a big pool of questions for various exams?

We offer Excel-based solution for that. Store your questions on an Excel spreadsheet and with VBA programming we create a solution that will give you the random tests with all the headers needed, the date of the test and the answer keys to make it easy to correct the tests.

Remember, these are just a narrow subset of opportunities.

Contact us to discuss your own task to automate: +36 30 289 2050; info@excel1.co.uk

Automation, Application development in Excel, Excel VBA programming, Excel Macro programming

Minden jog fenntartva NÉGYZETEKCopyright © 2021 Excel1 Ltd.