In our consultancy, life is never dull. We are constantly finding new ways to use the incredibly powerful Excel platform to provide value to our clients. In this post, we will showcase the following applications that our expert Microsoft developers have built:

• Client Proposal Generator for High-End Insurance Provider
• Pricing Tool for Event Management
• Automated Data Validation for Tax Form Processing
• Automated Reports for Manufacturers Reps
• Billing and Payment Reports to Supplement EMR for Health Care Providers
• Meter Readings for Hydro Electric Plant
• Sales Data Validator
• Media Client Proposal Generator
• Investment Advisor Analysis Tool
• Nursing Home Staffing Tool

Client Proposal Generator for High-End Insurance Provider

Our client’s insurance agents were spending an inordinate amount of time generating client proposals. For each proposal, they were copying and pasting information downloaded from multiple insurance carriers for each insured into Excel spreadsheets. This information was then manually consolidated on a summary sheet for comparison, selection and presentation to clients. Along the way, client names and birthdates had to be re-entered in multiple places. The client came to us looking for help creating a streamlined, efficient process of generating proposals.

The Solution:

Our Microsoft experts developed an Excel VBA application to automate the process generating client proposals and quote calcuation. The home page provides an interface for entering client information, which then is used to auto-populate cells throughout the application. It also provides functionality for browsing to and selecting import files for the various insurance carriers.

Key Client Benefits:

Insurance Agents can now develop client proposals in a fraction of the time that they previously spent on them. This gives them more time to customize and optimize the proposals for their clients.

Pricing Tool for Event Management

An improved and automated Excel pricing tool was needed for the sourcing, estimating, contracting and invoicing phases of each program and its associated events. Since each program involves third party vendors for the various program components, the application needed a streamlined workflow with interfaces for entering data, automated calculations, and automated generation of invoices and other documents.

The Solution:

Our Microsoft developers create an Excel application that allows program planners to easily manage client information and view alternate cost and pricing scenarios. The Client Info tab contains an interface for accepting client information, which automatically flows through to other areas of the application, eliminating the need for duplicate data entry. Furthermore, the Invoice Output and Detail Output Tabs are automatically populated and used to generate PDF documents for the clients and event staff.

On the Client Info Tab, the event pricing summary allows the planner to check or uncheck program components to view their impact on pricing. This allows the planner to test various scenarios when honing in on the optimal solution for each client. The information presented in the event pricing summary is automatically calculated from the individual component tabs that contain more detailed information.

Key Client Benefits:

Program planners can focus their attention on obtaining information from their clients and vendors and quickly develop estimates, contracts and invoices. Since information is automatically calculated and populated, errors and time expended are reduced.

Automated Data Validation for Tax Form Processing

Tax filing software has certain data type and formatting requirements for each field pertaining to each tax form. A tax preparer needed a process for their clients to use in validating their data before attempting to upload it into the tax preparer’s online software.

The Solution:

Our Microsoft developers created an Excel application with VBA code. The application runs a data validation macro and notifies the user of any invalid data. Once the user corrects the data, the validation can be run again to make sure all errors have been rectified.

Key Client Benefits:

Users can now easily see the errors in their data and address the issues before spending time uploading data into the tax filing software. This saves time and frustration, reducing delays in submitting information needed for tax filing compliance.

Automated Reports for Manufacturers Reps

Each month, management sends sales reports to manufacturer’s representatives. These reports are a compilation of information from several different systems and include calculations based on logic pertaining to costs, pricing tiers, rebates, and fulfillment percentages.

The Solution:

Our Excel developers created a branded, polished Excel application with VBA. The application automatically imports unit costs, rebates, fulfillment percentages and other information and places it in reference tables within the application. The VBA code contains formulas with logic rules that are specified by the client. Once the data is imported, the results report can be automatically generated.

Key Client Benefits:

Reports can now be generated in a fraction of the time that was formerly spent generating these reports on a monthly basis.

Billing and Payment Reports to Supplement EMR for Health Care Providers

The accounting department of a medical group needed to send monthly statements to each provider, documenting billing and payment information for services rendered to their patients. Although they have an electronic health records (EHR) system in place, which includes billing data, it doesn’t generate the provider statements. They want an application that automatically extracts information from reports generated by the EHR system, creates separate reports for each provider, and then emails those reports.

The Solution:

An Excel application was developed with functionality for selecting the time period, providers and CPT codes to include in the reports. After the selections are made, pressing the “process” button generates the reports and sends emails to each provider with their report attached.

Key Client Benefits:

The client was previously spending a few days each month generating provider reports, which can now be completed by the application in minutes. This results in the providers receiving their reports in a timely manner and frees up the time of the accounting staff to work on other matters.

Meter Readings for Hydro Electric Plant

Our client needed a way to analyze and visualize historical as well as current meter reading data from equipment in a Hydro Electric Plant.

The Solution:

Our data experts extracted approximately 2.5 million electrical meter and peak readings from 1925 to the present; 1.9 million hydrology readings from 1926 to the present; and several hundred additional miscellaneous data points. Using Excel, this data was cleansed, prepared and then imported into data tables in an Access database.

Our Excel developers then created a frontend application with a data connection to the Access backend. This frontend application contains an interface for entering new meter readings and functionality for generating reports for visualizing the data in various ways.

Key Client Benefit:

With the historical data in a database, the client can now use it for a variety of analytical purposes.

Sales Data Validator

The client wanted to improve the process of validating and correcting sales data prior to uploading it into a corporate reporting system.

The Solution:

An Excel application was created to import, validate and export sales data. A user interface allows the user to select the company name from a dropdown list and the file and folder locations for the import files. From a popup menu, the user can then load the data into the Excel file from the source files, validate it and generate an export file.

Key Client Benefits:

The salesforce can quickly and easily correct errors in their data prior to attempting to import it into the corporate reporting system. Because this new process is much faster than their previous process, the sales team can provide information to the corporate reporting system on a timelier basis.

Media Client Proposal Generator

Our media client needed a more automated means for their salesforce to generate proposals. Their products are included in publications that span a variety of brands and media platforms, including both electronic and print media. As a result, the application needed flexibility to accurately handle product specifications and bulk pricing tiers for each brand and platform specified. Furthermore, they wanted the ability to easily select publication dates for the selected quantity. When the quantity specified is 24, they wanted to easily select 24 dates to include in the proposal.

The Solution:

An Excel application leads the salesperson through a sequence of selections, where the available choices are filtered based on prior selections. The backend tables were designed to allow easy update by the administrator, which is extremely important for accommodating ever changing product offerings.

Key Client Benefits:

Salespeople can now focus on helping their clients select the most suitable publications, products and platforms rather than scrambling to find the most current offerings of each.

Investment Advisor Analysis Tool

A group of Investment Advisors needed an efficient method of importing ticker data and associated financial, firm and industry information into an application for further analysis. Due to the large amount of data, the current system was slow and cumbersome to use.

The Solution:

A hybrid system consisting of an Microsoft Access backend and an Excel front end application was developed. The Access backend stores the data, while the Excel frontend performs the calculations. This is a very good way for users to feel comfortable adopting a powerful solution because they are simple, straight forward and feel like they are still in “Excel”.

Nursing Home Staffing Tool

A nursing home needed an application that imports staffing and patient census data from a larger database and compares the current and projected staffing levels to the required staffing levels during each shift.

The Solution:

An Excel application was developed with an automated process for importing and visualizing data.

Contact Us for A Free Consultation

Contact us for a free consultation by calling 1-800-682-0882 or submitting an inquiry at ExcelHelp.com.