Imagine a fixed monthly amount due to an Organisation for services rendered to various customers. While an invoice is raised every month by this Organisation, not all pay up the dues on time. For unpaid dues, the Organisation charges its client interest ranging from 3% to 9% per annum. The objective is to determine cumulative […]
Read More →
Assume a simple three column dataset showing hours worked by different machine on different dates. So column A is Date, column B is Machine Name and column C is hours worked. There are duplicates appearing in column A and B . Blanks in column C depict machine idle time. The task is to create a simple […]
Read More →
{ 30 Comments }
Visualise Sales Data of a Non-Alcoholic Beverage Company with basic columnar information such as Date of Sale, Time of Sale, Brand, Stock Keeping Unit (SKU), State, City, Quantity sold, Unit Price and Salesman Code. In this sales dataset, each line item represents one visit for one SKU. If nothing is sold in a certain visit, then the SKU […]
Read More →
{ 0 Comments }
Visualise an MS Excel file with two worksheets: Employee headcount – a multi column dataset with information such as Employee code, Date of Joining, Age, Division, Department and Location. Each row represents data for one employee. The number of rows on this worksheet is approximately 700. Training Data – a multi column dataset with information […]
Read More →
{ 2 Comments }
Assume a dataset with a Key Performance Indicator (KPI) [appearing in one column] data for years ranging from 1985 to 2010 for 114 countries. This dataset has 170,000 rows of data and one row below the last row for every country, there is a total of the KPI column. So, if there are 25 rows […]
Read More →
{ 6 Comments }
With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations. Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story. To be able to interact with/create visualizations, […]
Read More →
Assume that there are three separate tables showing the following information: 1. Date of visit data for visitors to a certain recreation facility. The same visitor may visit the facility multiple times 2. Fee per visit in different cites 3. Region in which each city lies The question is to analyse the three tables above via […]
Read More →
A tournament has 18 participating teams with 25 players in each team. Each team has to play five rounds of the Tournament and not all players play all rounds. Scores earned by each player in each round are shown in individual cells. If a player does not play a round, that cell is left empty. The task […]
Read More →
Assume a database of Maximum allowed pay and Actual pay for each employee. The employees have been further categorized into Groups and labour categories. The task is to create three Pivot Tables (one each with Employee name, Group and Labour category in the row labels) with the following information in the Value area section: 1. […]
Read More →
{ 12 Comments }
Assume a three column table arranged as follows: Circle, Date of Fault and ID. Dates in the date range span one week – November 26, 2012 to December 2, 2012. A particular equipment can be only one specific Region and the same equipment an go faulty multiple times within one week. Data for one week […]
Read More →
Determine cumulative interest payable on an annuity with varying time periods
{ 0 Comments }