Menu
COVID-19 Excel Dashboard
Pre-Implementation: One of the Excel-based dashboards that we have developed for one of our clients is COVID-2019. On this dashboard, we are displaying visualisations of data from over 200 countries, based on the number of people in each country who were affected by COVID.
The data on the dashboard is linked from the worldometers webpage, so the dashboard automatically refreshes itself in every 10 minutes. This indicates that the dashboard is dynamic.
We are showing different numbers like –
There is a country filter where the user can select a specific country and the dashboard will only show data from that country.
Benefits:
Power BI Sales Dashboard
Excel Production Dashboard
Company Health Dashboard
Hotel Feedback Dashboard
Quality Dashboard
Sales Dashboard as per Segments
Detailed HR Dashboard
Complete Sales Dashboard
ARKI Census Project
Pre-Implementation: There is a master sheet that contains raw data from thousands of employees in 13 different columns. Because this is raw data, the user must manually check each entry to see if it contains correct data based on various conditions or not. Once the data validation is complete, the user will copy the specific column information and manually paste it in 10 different sheets in various formats, which is a time-consuming task. One of the challenges is that as the number of employee data increases, the user will need more time to complete this task.
Post-Implementation: To solve the problem, we added 3 automation buttons to the project:
Benefits: The benefits of using this automation are as follows:
Excel CRM for taxation
Pre-Implementation: The client has a team of 15 employees who offer a variety of services to their clients, including company registration, MSME registration, GST filing, GST registration, and many others. They get new clients from a variety of sources, including their website, social media platforms, client, or friend referrals. They were keeping the data in multiple excel files as different employees worked on it, which caused confusion about which employee was working on which project, as well as not being able to track when and with whom the follow-up was required, how much payment was received, and how much was pending. As a result, the client requires a platform where all these activities can work together, and data can be saved directly on the server to ensure its safety and security.
Post-Implementation: We have created a small CRM in Excel with multiple features at various stages.
Because the data is directly saved on the server and retrieved from their only, multiple employees can work on this Excel-based CRM tool. This tool records the user's login and logout details automatically. It is divided into five stages, which are as follows:
Stage 1: New Lead - It opens a user form in which the user enters information about the new lead such as Lead Name, Address, Website, Company Name, Mobile Number, Source Type, Assigned To, and so on. Finally, the user can enter the new lead's information into the system.
Stage 2: At this stage, the user can easily view the existing clients' follow-up list for the next three working days. If they need more follow-ups with the client, they can also add future follow-up dates, and the tool will give an alert of the follow-up dates.
Stage 3: The client can now check the payment status of their existing clients. At this stage, the user can only update any of their clients' partial payments. Similarly, users can see what documents have been submitted from the client side and what are still pending.
Final Stage: So far, we've been discussing the tool's front end. At this point, if the user is confident that the lead has paid and their client has shared their completed requirement, they can convert that lead into a customer, which will be assigned directly to the backend team.
Benefits:
Multiple users can work together
Feed Budget Report
Pre-Implementation: Dairy cattle farming is one of the best agricultural businesses in Australia. The client has 9 different types of cows. The client was manually entering data into different excel sheets, updating formulas manually when new data entered, calculating monthly feeds, and many other tasks. This was consuming a lot of time and causing errors because a lot of manual activities were required. The client has all the data, but it is in an unstructured format. As a result, he has no idea how to manage this information, which comes from various excel files. The client is trying to figure out the number of animals (cows) that must be fed, the monthly feed demand for each animal, the total feed surplus or deficit each month, and the milk production/growth targets.
Post-Implementation: To solve the problem, we have done the automation in excel which have the following features:
Benefits:
Send Emails Automatically to 500+ clients
Objective – To send emails to many people with different context and multiple attachment files.
Pre implementation – Our client drafts emails attach different files manually which needs to be send to many people, so lot of manual work.
Post implementation – Client needs to fill the below given screenshot template in excel and click on “Send an Email” button.
Total Detail Load Project (Word Doc + Excel data)
Pre-Implementation: There is one Word document with approximately 1000 pages having 1500 multiple tables on different pages. The goal is to get all the data from word document tables into an Excel file so that it can be compared to other data that is already in an Excel file.
The client used to open the doc file, go through all 1000 pages one by one, and copy all the tables data into excel from all pages as needed. After he copies the data, another person will review or audit some of the entries just to make sure that the correct entries has been copied. Then finally do the comparison of Excel and Word document data which takes almost 2 days to complete the task.
Post-Implementation: To solve the problem, Mdata Finnovatics added 3 automation buttons to the project:
Step 1: Total Load – The macro asks the user to select the work document at this point. The macro should then open the Word document and visit each page individually. The automation determines which page contains a table and then copies and pastes the data from each table into the Excel file. The summary of the word document has been created in one of the sheets in excel.
Step 2: Detail Load – At this point, the macro asks the user to select the other Excel file containing comparison data. The data will be imported into a new sheet that will be created.
Step 3: Final Output – In the final stage, the automation compares the data received from the word document in step 1 with the data extracted from the excel file in step 2 and generates an appropriate summary.
Benefits:
IBR Salary Project
Pre-Implementation: The client is a labor law compliance and manpower outsourcing company. They have over 500 employees who have been outsourced. They keep track of each employee's data in Microsoft Excel. They pay all employees on their payroll, generate their salary slips into PDF format, and then send an email to each employee with their salary slips attached. The challenge is that they are performing this entire task manually, generating over 500 pdf files and sending emails to each employee separately, which takes approximately 3 days.
Post-Implementation: To solve the problem, we added 4 automation buttons to the project:
Benefits: The benefits of using this automation are as follows:
Create PDF Salary slips Automatically
Objective – Client have a list of more than 500 employees data in Excel whose salary slips he has to create manually.
Pre implementation – The client opens the excel file, enter each employee’s data into predefined salary slips format and then save the copy in PDF format. After that he send the salary slip copy to each employee via email. Same process he follows for all the 500+ employees which takes approx. 3 days’ time.
Post implementation – We have created an automated file where the client must click on a button based on which all 500+ employee salary slips get salary in a folder in PDF format and email automatically triggered to the specific employee with their salary slip copy. The complete process takes less than a min where previously he was spending 3 days’ time.
Collate data from more than 100 files
Objective - There is one master file (sheet) which needs to be consolidated by pulling data from other 100 client excel files. Each excel file contains 2 sheets which needs to be appended, client wise.
Pre implementation – My client used to open all 100 files manually one by one, copy the data from source files and paste it into master file one by one. Chances would be to skip any excel file or may be data wrongly copied and pasted by doing manual work. And it takes approximately whole day for a professional who is working daily.
Post-implementation – We have automated this process. Now they must just save all those 100 files in a folder and press a button. Within a minute’s time, all the work gets completed.
Validate PDF & Excel Data
Objective - There is a PDF file of around 250 pages with approx. of 1000’s Purchase Order (PO) details and same PO information is given in of the Excel file. The purpose of this Automation is to validate the Excel data with the PDF file.
Pre implementation – It was very lengthy process as my client used to open the Excel file and match all the data from the Excel file with the PDF file which have 1000’s of PO numbers. It was very time-consuming process because every PO had to search manually and also to note down the missing information from the PDF file to the excel file.
Post implementation – We have automated the complete process. Now the client only must click on a button which ask for the PDF file and Excel file. After selecting the files, the automation will be completed within 60 seconds which generated the final Excel file stating which PO numbers are matching and which are not as shown in the screen sort.
Create Bulk Quotation Automatically
Pre-Implementation: The goal is to automatically generate bulk quotations in pdf format based on the data in the Excel file and share the quotation with their respective customers.
The client opens the excel file, enters each customer's information such as name, address, GST number, PAN number, date, project description, and so on into the predefined quotation template, and then creates and saves a copy in PDF format. He then emailed a copy of the quotation to each customer. He goes through the same process for multiple customers every day, which is a monotonous and time-consuming task.
Post implementation: We have developed an automated excel file in which the client simply needs to click on a button for all customers' quotation copies to be generated and stored in a particular folder in PDF format (Shown image is the sample of the quotation PDF format). The automation programme sent an email to the selected customers with a copy of their quotation attached, and the email was automatically triggered. The entire procedure takes less than a minute, whereas in the past he was spending a significant amount of time daily.
Benefits:
PSNS TBE Tracking Application
Pre-Implementation: The customer works in the petroleum and natural gas industry. Its operations consist of the production of oil and natural gas, as well as their transportation, processing, and marketing. Most of the participants in this project are people who work in engineering. The customer desires that all 100 of their employees in the same department be able to collaborate on this application while maintaining limited access to the data based on their respective roles. Additionally, the client desires that the data must be safe and secure.
Post-Implementation: We developed an Excel-based application that communicates directly with the server, and the database is Microsoft SQL Server. The user/employee can begin by opening the application, entering their credentials, and then proceeding if the credentials match. Multiple users can work on the application at the same time, with each having limited access based on their role. We created a common dashboard for each employee, but the visualisations only appear for the employee who logs in. So, he can see how many projects he worked on, the trending of the last six months, the status of various projects, and much more.
Users can add project details to the server, update or delete existing bids or clarifications, perform advanced quick searches based on different categories/headings, and export data into Excel based on the quick search.
Benefits:
This is one of the tools designed to input the data via user form
This is one of the tools designed to input the data via user form. It has the following features:
1 – Data is to be entered through user form. So, no back dated entries can be possible.
2 – Some fields will be auto-populated based on the selection (Like – Services, Location, Supplier Number, Supplier Site will be auto-populated once Supplier Name select)
3 – There is an option to generate the pdf or give the print of the data given in the form.
4 – The few fields are auto-calculated like – Invoice Amount, GST Amount, TDS, GST recoverable and non-recoverable amounts.
5 – Digital Signature also saved in image form.
Marketing Tool
Objective – Client have a list of more than 1000 customers data in Excel whose quotations he has to create manually.
Pre implementation – The client opens the excel file, enter each customer details like – Name, Address, GST Number, PAN Number, Date, Description of the project, etc into predefined quotation template and then create & save the copy in PDF format. After that he send the quotation copy to each customer via email. Same process he follows for multiple customers on daily basis which takes lot of time.
Post implementation – We have created an automated file where the client must click on a button based on which all customers quotation copy is created and saved in a specific folder in PDF format (Below image is the sample of the quotation PDF format). The Automation automatically triggered the email to the specific customers attaching their quotation copy. The complete process takes less than a min where previously he was spending huge time on daily basis.
Loan Amortization
This is a Loan amortization tool which we made for one of my client for banking purposes. This tool helps to calculate the periodic installments (Monthly, Quarterly, Semi Annually, Annually) of fixed term loans for any specific time-period with any specific rate of interest after down payment deduction. This tool does not only show you the periodic instalments but also shows the loan amortization schedule, which helps to know following things – Count of total instalments, Payment date of every instalment, Principal and interest amount of every instalments, Remaining principal amount after instalments deduction. This tool is so user friendly that the user can get immediate results just by providing inputs of their requirement in the tool.
Approved Amount Dashboard
Track updations in excel
This tool has been created for one of my clients who was not able to track the changes done in excel files. It has following features:
1- Tool can calculate how many times changes has been done in the excel file.
2- Helps to know who has done the changes.
3- Helps to compare what was the actual value and what is the current value.
4- Specifically track the changes of each cells of the excel file.