Data Analytics: Consolidating Data
Description:
At the moment, Superstore has demonstrated the various operations and activities that occur across different regions with regard to the sale of its products. Accordingly, analyzing the dataset provided is significant since it will Help when making financial and statistical decision so as to increase both performance and productivity within the organization. For instance, the organization keeps records of the sales revenue relating to different kinds of products across various regions in the country. This sales data is recorded annually to be used to portray the sales revenue report for each region in the country.
Accordingly, the organization needs to enhance how to tabulate this data from each region without having to perform complex calculations. To do so, Microsoft Excel provides the “Consolidate data” function, which will enable the organization to tally information from the different regions when determining overall sales revenue report. Using this Excel function, additionally, will be efficient since any changes made to the individual reports will be automatically reflected in the consolidated revenue report.
Consolidating data
As mentioned, the company keeps separate records of sales revenue for each region it is operation within the country. Using these records, the organization is able to keep track of total sales revenue for each region annually, as shown below;
Using the Excel “Consolidate Data” function, it will be possible to tabulate the total sales revenue for all four regions, i.e. Central, East, South, and West.
a) Consolidated report without link
First, the excel function is used to consolidate data from different worksheets or workbooks to simplify the process of performing calculations for all of them. The figure below demonstrates the Excel function that will consolidate data from four different worksheets;
After consolidating data, the total sales revenue for all four regions based on specific months is portrayed. However, this report is calculated using accumulative constant, which means that future changes will not be conveyed in the consolidated report. While this is beneficial for generating one-time reports, it will not come in handy when new changes are made to the individual reports.
The following figure demonstrates the consolidated report;
b) Consolidated report with link
Since creating a consolidated report without links is not effective, the solution is using the Excel function to enable future changes to make modifications to the final consolidated report as well. Doing so will minimize cases of redundancy where the organization has to repeat the process every time changes are made in the consolidated report. Accordingly, the following figure illustrates the Excel function to do so;
The main benefit of using the Excel function is that the organization will be able to make changes in the individual reports, which will then be automatically updated in the consolidated report. The figure below illustrates this;
