Data Handling and Business Intelligence

This report explores the concept of data handling and its connection with business intelligence. It analyzes a big data set of a superstore to determine the reasons for its decline in sales and profits. It also evaluates the use of Microsoft Excel for pre-processing, analyzing, and visualizing the data. Various Excel functions are demonstrated, and the importance of data analysis is highlighted.

DATA HANDLING AND
BUSINESS
INTELLIGENCE

Contents
INTRODUCTION...........................................................................................................................1
PART 1............................................................................................................................................1
Determining the reasons for the store’s decline in sales/profits over the years...........................1
Evaluating the use of Excel for pre-processing the data, analysing the data and visualising the
data...............................................................................................................................................2
Practical use of excel functions...................................................................................................4
CONCLUSION................................................................................................................................4
REFERENCES................................................................................................................................5

INTRODUCTION
Data handling is a concept of managing and controlling the data using software applications
such as Microsoft Excel (Anagnostopoulos, Zeadally and Exposito, 2016). The concept of data
handling is directly connected with business intelligence as the large information or data is
controlled by using information technology (Chalana and Dhillon, 2018). The main aim of this
report is to identify current trends in data warehousing, business intelligence and data mining.
For this, in the present report a big data of superstore is analysed in order to ascertain the reasons
for the decline in sales and profits of the superstore. Along with this, various functions of
Microsoft Excel such as Pivot tables, IF, Count are used in order to demonstrate the ability of
processing data. Various uses of Microsoft Excel including pre processing of data, analysing the
data and visualizing the data is also mentioned in this report.
PART 1
Determining the reasons for the store’s decline in sales/profits over the years
The data information about the superstore is provided for 4 years which are 2009-2012. By
using filter and sort option in Excel, it has been ascertained that the revenue and profit of this
store is reducing over the years. There are various reasons due to which sales and revenue of this
store is declining. These reasons are mentioned below:
 High Shipment Cost – It is observed that the maximum revenue recorded in 2009 was
86061 pounds but the maximum revenue recorded in 2012 was 413343.21 which
approximately half than 2009. In the course of 4 years’ maximum record sales of this
store has been cut to half and the result behind this case is high shipment cost which
organisation has to pay in order to ship the products so that it can be delivered to their
clients. This store is working for 4 years and the minimum shipping cost paid this store is
same in years 2009 and 2012. This store has not provided any concession in the shipping
cost for over 4 years due to which sales are declining of the company. Minimum shipping
cost in year 2009 and 2012 are 0.49 pounds.
 Low Discounts – Discounts are the promotion technique which are used by organisations
to increase their sales revenue. Using maximum function in Excel, it has been ascertained
that even after lowering sales revenue and profit, this store does not increase its discount
rates. Maximum discount provided in 2009 was 0.21 which decreased as 0.1 and 0.16 in
1

2010 and 2011. It is promotion technique of the store to provide discount when they are
not earning reliable revenue but Superstore does not adopt this technique and not is
facing the issue of low revenue.
 Unit Price – Profitability of an organisation shows the profit earning ability. The
maximum profit gained in 2009 by Superstore was 27220.69 pounds which adversely
decreased in 2012 as 10521.33 pounds. It evidently proves that the profit of this store has
majorly declined in last few years. The reason behind this decline is unit price, the
maximum unit price in every year was 6783.02 pounds. Every organisation has to bring
change in their organisation according to the situations. But even after facing continuous
low profit and revenues, superstore did not change its maximum unit price with
ultimately resulted in low profits.
 Product base margin – Product base margin is the margin which is set on a product by
which an organisation earns the profit. With the help of maximum function of excel,
maximum product base margin in 2009 was 0.85 which did not change at all in preceding
years due to which profit of the store kept declining. As mentioned above, change is an
important part for any organisation; so in order to increase profit and revenues, it is
important for an organisation to increase their product base margin so that any contingent
situations can be avoided.
From all the above arguments, it is evident that why revenues and profit s of Superstore were
reducing year by year.
Evaluating the use of Excel for pre-processing the data, analysing the data and visualising the
data
Microsoft Excel is a software application which is a spreadsheet creating tool which helps in
recording, analysing and interpreting the data using various features of Pivot table, charts, graphs
etc. This software application has various uses and some of them includes pre processing the
data, analysing the data and visualising the data (Cheng, Zhang and Qin, 2016).
Pre processing the data
Pre processing the data is a procedure in which raw data is converted in an understandable
format (Psenka, Booty and Duncan, 2015). In procedure includes importing the data set and then
checking for missing values and see the categorical values and at last splitting the values
according to their features. This procedure can be done by using features of “Filter” and “Sort
2

data” in which values can be filtered categorically and data can be sorted in various orders such
as ascending and descending.
Analysing the data
Data which is recorded and refined in Excel can also be analysed according to the
objectives (Vieira and et.al., 2019). In order analyse the data, there are various FORMULA such
as “AVERAGE”, “MEDIAN”, “RANGE”, “COUNT”, “IF”, “LOOKUP” etc. Analysing a data
is a procedure in which data is interpreted according to the objectives. In the given case, data is
analysed in order to ascertain the reasons behind reducing revenues and profit of Superstore. For
this, analysing techniques which were used are MAXIMUM and IF.
MAXIMUM formula helps in ascertaining the maximum value in a row or column.
Maximum sales and profit for the year 2009 and 2012 were ascertained using this formula. From
which it was determined that maximum revenue and profit of Superstore was much higher in the
year 2009 from the year 2012.
Besides MAXIMUM, IF formula was also used in which a logical argument is tested in
order to identify whether is it true or false. This formula was used in the case of superstore in
order to identify whether or not 2012 sales were higher than 2009 but the result came as “false”,
which means revenue of this organisation were reduced adversely.
Visualising the data
Visualising the data is the technique which helps in effectively present the data using
various tables, charts and graphs so that results can be observed quickly and easily (Mohanty,
Bhuyan and Chenthati, 2015). Understanding the data is a complex task which everyone cannot
do, so in order to present and visualise the data in a manner which can be understood by
everyone, features of graphs and tables are used in Excel. In the present case, PIVOT TABLES,
pie chart and a bar graph is used to present the data in much more effective manner. In order to
analyse the relationship between various variables and present them in Excel, Pivot tables are
used. These tables show the relationship between three variables which are “revenue”, “profit”
and “order date”.
Besides this, a bar graph is created to present the maximum revenue order in 2009 and
maximum revenue order in 2012 so that it can be effectively presented that revenue of the
Superstore are declining.
3

Lastly, a pie chart is also created. Pie chart is also a type of graph in which a round shaped
chart is used to identify the share of each variable. In the present case of Superstore, pie chart is
used to represent the maximum profit earned by the store from their customer in year 2009 and
2012 so that it can be evidently shown that profit in year 2009 was higher.
Practical use of excel functions
Excel Functions can be used by an organisation in order to analyse the data, the practical use
of Excel functions is presented in Excel spreadsheet. Various functions which are used are:
PIVOT TABLE – Pivot table is used to summarise the data. In the case of superstore, there
are three most important variables named as “Order date”, “profit” and “sales”. From the Insert
tab, a pivot table was created which shows relationship between these three variables.
MAXIMUM – After developing pivot table, maximum function was used using the formula
“MAX(number1,number2, ... )”. By using this maximum amount of profit, sales, discount
availed, unit price and product base margin is calculated.
MINIMUM – This formula is used to return the minimum value from an array. Formula of
MIN(number1,number2, ...) is used for shipment cost paid by the organisation in 2009 and 2012.
IF – This function is used to test the logical argument. Formula used for this function is
=IF(M3>M2,"true","false") and =IF(P3>P2,"true","false") by which it is analysed that whether
or not sales and profit of this organisation is reducing.
LOOKUP – This function is used to identify a value by looking in an array. In the Excel
Sheet of Superstore, Lookup function is used by the formula “LOOKUP(value, lookup_range,
[result_range])”.
CONCLUSION
From the above report, it has been concluded that analysing an information is important in
order to get desired results. This information can be analysed by application software of
Microsoft Excel. It is also observed that three main functions of Excel are to pre processing the
data, analyse the data and visualise the data.
4

REFERENCES
Books and Journals
Anagnostopoulos, I., Zeadally, S. and Exposito, E., 2016. Handling big data: research challenges
and future directions. The Journal of Supercomputing. 72(4). pp.1494-1516.
Chalana, V. and Dhillon, D., Winshuttle LLC, 2018. Methods and systems for mitigating risk in
deploying unvetted data handling rules. U.S. Patent 10.148.525.
Cheng, S., Zhang, Q. and Qin, Q., 2016. Big data analytics with swarm intelligence. Industrial
Management & Data Systems. 116(4). pp.646-666.
Mohanty, H., Bhuyan, P. and Chenthati, D. eds., 2015. Big data: A primer (Vol. 11). Springer.
Psenka, M., Booty, M.D. and Duncan, B., ETHORITY, LLC, 2015. Enhanced user interface and
data handling in business intelligence software. U.S. Patent 9,165,044.
Vieira, A. L., and et.al., 2019. A user-friendly excel spreadsheet for dealing with spectroscopic
and chromatographic data. Chemometrics and Intelligent Laboratory Systems, 194,
p.103816.
5