Analytics, Reports, Models & Excel Programming

The client ships products to all states in the US and Canada. They use USPS and two other leading freight companies. They wanted to review the shipping pattern and develop a model to predict freight costs. The model was used to evaluate the impact on overall cost for several parameters. The optimization model helped us develop a best case scenario to negotiate freight contracts with shipping companies.

The client had approximately 24,000 contact records in a 4000 page word directory that needed to be converted into an excel database. It had a complex directory structure and we had to reproduce it. We completed the conversion very quickly while preserving most of the original formatting, structure and content.

The client gave us four files, three of which were input files and one of them was a template for the output file. The input files had one tab per zip code (united states) with various demographic data for that zip code. Every tab was exactly the same format. The output file included three tabs, one for each of the input files. We transformed data from the format in the input files to the corresponding tabs on the output file in columnar format. This was done accurately and swiftly.

The client had a website which offered foreclosure listings, auction tracking, management and analysis. We designed a custom spreadsheet that took imported data from the client's website, run calculations and store results. We had to use a combination of excel formulae together appropriately to get the calculations right and deliver accurate results.

The client wanted to reduce shipping costs by optimizing the sizes of boxes in which the articles are packed and shipped. The analysis of packing slips for 6 months data was done in conjunction with the zone-wise shipping patterns. The distribution pattern was simulated for various configurations and the packing efficiencies evaluated. On completion of the analysis, Inteleants provided an optimum box sizes for packing to minimize on shipping costs and also inventory of packing materials.

We developed monthly performance reports for an organization using MS Excel which allowed their managers to see the most useful information in the shortest-possible reading time and see the key trends and relationships that drive their companies. We took advantage of the formatting and presentation capabilities of Microsoft Excel to generate easy-to-read and informative reports.

The client required a list of comprehensive summary of car insurance quotes for the 10 most popular car models in the USA, UK, Germany, Australia and Canada. The quotes can be got from 15 major insurance companies, which were analyzed by Inteleants to develop a uniform evaluation system. Inquiries got from queries structured by Inteleants are to made available online and this was done with the services provided by the insurers. Inquiries were stored in an Excel sheet for quick retrieval of data.

Inteleants developed an interface in VBA for different formulations in the Excel spreadsheet. The client wanted an application to help people cross hurdles while getting a job and this required an excel-based tracker application. The following were to be included in the tracker: head-count of jobseekers along with required calculations and graph. The VBA data entry module provided access to the reports, charts and statistics sheets. Formulations were incorporated to calculate and record date, text manipulation, conditional sum and statistics. We designed the tracker system to generate the chart using VBA with data from the data-table and regenerate the Excel sheet by the VBA code on account of data updations.

We were required to combine data from various worksheets, populate and update pre-built charts automatically in an Excel spreadsheet. This required highly complex data-manipulations and consolidations, as the structure of each spreadsheet was different. The complexity was high as numbers were stored in text format and each spreadsheet had around 40,000 records. Text manipulation techniques of Excel were used to maintain consistency across all spreadsheets and consolidate data certain formulations. Inteleants designed Pivot Tables and Charts to extract and represent consolidated data.