report on dasborad & scorecard
DESCRIPTION
TRANSCRIPT
NewGate India
Hyderbad, Andhra Pradesh- 500038
Website: www.newgate.in
Email: [email protected]
Slideshare URL : http://www.slideshare.net/newgateindia
BI: Business Intelligence
BI tools in Retail Industry from
Marketing Perspective
Page | 2
Table of Contents
1.Business Intelligence ................................................................................................................. 4
1.1 Types of business intelligence tools .............................................................................. 5
2. Business Intelligence Tools ...................................................................................................... 5
2.1 Business operations reporting ....................................................................................... 5
2.2 Forecasting ..................................................................................................................... 6
2.3 Dashboard ...................................................................................................................... 7
2.4 Multidimensional analysis ............................................................................................. 7
2.5 Finding correlation among different factors ................................................................. 8
2.6 Predictive Gravity Modelling ...................................................................................... 8
3. Marketing and Business Intelligence ....................................................................................... 9
3.1 Marketing Scorecard ...................................................................................................... 9
3.2 Sensitivity Analysis ....................................................................................................... 10
3.3 Customer Life Time Value ( CLV) ................................................................................. 11
3.3.1 Churn rate ...................................................................................................... 11
3.3.2 Discount rate ................................................................................................. 12
3.3.3 Retention cost ................................................................................................ 12
3.3.4 Period ............................................................................................................. 12
3.3.5 Periodic Revenue ........................................................................................... 12
3.3.6 Profit Margin Profit ........................................................................................ 12
4 . BI & Marketing Reporting ..................................................................................................... 12
4.1 Excel ............................................................................................................................. 12
4.2 Reporting tool .............................................................................................................. 13
4.3 OLAP tool ..................................................................................................................... 14
4.4 Data mining tool .......................................................................................................... 15
5. BI for Retail Industry ........................................................................................................... 15
5.1 Reporting capabilities for key performance metrics such as .................................... 16
5.2. Performing complex analysis to derive measures for: ............................................ 16
5.4 Putting Decision Support to Work ............................................................................ 17
Page | 3
Table of Contents
6. Business Intelligence and Management ............................................................................... 18
6.1 Background .................................................................................................................. 18
6.2 Why Is BI Useful in Retail Management? .................................................................... 18
7. Some Current Developments ................................................................................................. 19
8. Implications for the future of Retail Management Applications .......................................... 20
9. Conclusion ............................................................................................................................ 20
Page | 4
1.Business Intelligence
Business Intelligence refers to a set of methods and techniques that are used by organizations for tactical and strategic decision making. It leverages technologies that focus on counts, statistics and business objectives to improve business performance.
A Data Warehouse (DW) is simply a consolidation of data from a variety of sources that is designed to support strategic and tactical decision making. Its main purpose is to provide a coherent picture of the business at a point in time. Using various Data Warehousing toolsets, users are able to run online queries and 'mine" their data.
Many successful companies have been investing large sums of money in business intelligence and data warehousing tools and technologies. They believe that up-to-date, accurate and integrated information about their supply chain, products and customers are critical for their very survival.
Each BI Scorecard is unique for a particular company based on its own set of:
BI business drivers BI requirements Established IT standards BI Project history
Page | 5
1.1 Types of business intelligence tools
The key general categories of business intelligence tools are:
a) Spreadsheets b) Reporting and querying software: tools that extract, sort, summarize, and present
selected data c) OLAP: Online analytical processing d) Digital Dashboards e) Data mining f) Decision engineering g) Process mining h) Business performance management i) Local information systems
2. Business Intelligence Tools
Business intelligence usage can be categorized into the following categories:
2.1 Business operations reporting
The most common form of business intelligence is business operations reporting. This
includes the actuals and how the actuals stack up against the goals. This type of business
intelligence often manifests itself in the standard weekly or monthly reports that need to be
produced.
Page | 6
2.2 Forecasting
Many of you have no doubt run into the needs for forecasting, and all of you would agree
that forecasting is both a science and an art. It is an art because one can never be sure what
the future holds. What if competitors decide to spend a large amount of money in
advertising? What if the price of oil shoots up to $80 a barrel? At the same time, it is also a
science because one can extrapolate from historical data, so it's not a total guess.
It is the process of analyzing current and historical data to determine future trends.
Projected Figures
y = 1263.5x - 3E+06 R² = 0.9877
0
5,000
10,000
15,000
20,000
25,000
30,000
2004 2005 2006 2007 2008 2009 2010 2011
Axis Title
No of Customer
0
5,000
10,000
15,000
20,000
25,000
30,000
2004 2005 2006 2007 2008 2009 2010 2011 2012 2013
No of Customer
Page | 7
2.3 Dashboard
The primary purpose of a dashboard is to convey the information at a glance. For this
audience, there is little, if any, need for drilling down on the data. At the same time,
presentation and ease of use are very important for a dashboard to be useful.
2.4 Multidimensional analysis
Multidimensional analysis is the "slicing and dicing" of the data. It offers good insight
into the numbers at a more granular level. This requires a solid data warehousing / data mart
backend, as well as business-savvy analysts to get to the necessary data.
Page | 8
2.5 Finding correlation among different factors
This is diving very deep into business intelligence. Questions asked are like, "How do
different factors correlate to one another?" and "Are there significant time trends that can be
leveraged/anticipated?
2.6 Predictive Gravity Modeling
The model should be designed to mesh with the data that will be used when the model is
implemented. In GIS analysis and most other large data analyses, the characteristics of
individuals are summarized into the neighbourhood’s demographic or psychographic
profile.Forecast the total potential sales available from each neighborhood in the trade
area
Page | 9
3. Marketing and Business Intelligence
Marketing is becoming more analytical as more performance data is available, as best
practices are established and as better analysis tools come to the market. Business
intelligence (BI) software is one of the tools that can be used to improve marketing reporting.
3.1 Marketing Scorecard
A BI project for marketing often starts with creating a marketing scorecard that contains
all metrics and KPIs that are relevant for your marketing organization. This scorecard should
contain metrics for the various aspects of your marketing department, ranging from
awareness measurements, to leads and lead conversion and marketing ROI.
A Business Intelligence [BI] Scorecard is a tool to aid the evolution along the BI Maturity Lifecycle and increase the strategic business value of the BI Program. A BI Performance Scorecard is used to track an organizations business intelligence and data warehouse deployments map against BI best practice.
Scorecards have long been used by organizations as a means of implementing strategy down through the enterprise and assessing progress against holistic, enterprise-wide performance indicators [KPI's].
Page | 10
A Business Intelligence Scorecard works in exactly the same way. Once BI Opportunities have been defined and BI Roadmap developed, the scorecard provides tracking against both the roadmap and the BI Maturity Lifecycle.
A BI Scorecard acts as a performance reality check on whether your BI projects are on track,
and if not, how to get them back on track. It acts as a visual connector between the BI Strategy
and the BI Program
3.2 Sensitivity Analysis
Sensitivity analysis (SA) is the study of how the variation (uncertainty) in the output of a
mathematical model can be apportioned, qualitatively or quantitatively, to different sources
of variation in the input of the model
In more general terms uncertainty and sensitivity analysis investigate the robustness of a study when the study includes some form of mathematical modeling. Sensitivity analysis can be useful to computer modelers for a range of purposes,[3] including:
support decision making or the development of recommendations for decision makers (e.g. testing the robustness of a result);
enhancing communication from modelers to decision makers (e.g. by making recommendations more credible, understandable, compelling or persuasive);
increased understanding or quantification of the system (e.g. understanding relationships between input and output variables); and
model development (e.g. searching for errors in the model
Page | 11
3.3 Customer Life Time Value ( CLV)
In marketing, customer lifetime value (CLV), lifetime customer value (LCV), or lifetime value
(LTV) is the net present value of the cash flows attributed to the relationship with a customer
CLV = ∑
to power K
CLV: Customer Lifetime Value
PC : Profit Contribution
d : Discount Rate
n : Number of years
k : Time unit
Most models to calculate CLV apply to the contractual or customer retention situation. These models make several simplifying assumptions and often involve the following inputs:
3.3.1 Churn rate The percentage of customers who end their relationship with a company in a given period. One minus the churn rate is the retention rate. Most models can be written using either churn rate or retention rate. If the model uses only one churn rate, the assumption is that the churn rate is constant across the life of the customer relationship.
Page | 12
3.3.2 Discount rate the cost of capital used to discount future revenue from a customer. Discounting is an advanced topic that is frequently ignored in customer lifetime value calculations. The current interest rate is sometimes used as a simple (but incorrect) proxy for discount rate.
3.3.3 Retention cost the amount of money a company has to spend in a given period to retain an existing customer. Retention costs include customer support, billing, promotional incentives, etc.
3.3.4 Period The unit of time into which a customer relationship is divided for analysis. A year is the most commonly used period. Customer lifetime value is a multi-period calculation, usually stretching 3-7 years into the future. In practice, analysis beyond this point is viewed as too speculative to be reliable. The number of periods used in the calculation is sometimes referred to as the model horizon.
3.3.5 Periodic Revenue The amount of revenue collected from a customer in the period.
3.3.6 Profit Margin Profit as a percentage of revenue. Depending on circumstances this may be reflected as a percentage of gross or net profit. For incremental marketing that does not incur any incremental overhead that would be allocated against profit, gross profit margins are acceptable.
4 . BI & Marketing Reporting
Once you have established the scorecard, Business Intelligence software can be set up
to collect all necessary data and store it in its database. Data snapshots are taken, so you
have access to the full history. Based on this data warehouse, wealth of reports is available
that each can be customized to fit each employee’s needs. It often starts with a high-level
overview, but also provides the opportunity to drill down into more detailed reports.
The most common tools used for business intelligence are as follows. They are listed in
the following order: Increasing cost, increasing functionality, increasing business intelligence
complexity, and decreasing number of total users. The different tools used are
4.1 Excel
Take a guess what's the most common business intelligence tool? You might be
surprised to find out its Microsoft Excel. There are several reasons for this:
It's relatively cheap.
It's commonly used. You can easily send an Excel sheet to another person without
worrying whether the recipient knows how to read the numbers.
It has most of the functionalities users need to display data.
In fact, it is still so popular that all third-party reporting / OLAP tools have an "export to
Excel" functionality. Even for home-built solutions, the ability to export numbers to Excel
Page | 13
usually needs to be built. Excel is best used for business operations reporting and goals
tracking.
4.2 Reporting tool
In this discussion, I am including both custom-built reporting tools and the commercial
reporting tools together. They provide some flexibility in terms of the ability for each user to
create, schedule, and run their own reports. Business operations reporting and dashboard
are the most common applications for a reporting tool.
Page | 14
4.3 OLAP tool
OLAP tools are usually used by advanced users. They make it easy for users to look at
the data from multiple dimensions. OLAP tools are used for multidimensional analysis.
Page | 15
4.4 Data mining tool
Data mining tools are usually only by very specialized users, and in an organization, even
large ones, there are usually only a handful of users using data mining tools. Data mining
tools are used for finding correlation among different factors.
5. BI for Retail Industry
As retail markets become increasingly competitive, the ability to react quickly and decisively to market trends and to tailor products and services to individual clients is more critical than ever. A business intelligence system can be a very effective means of organizing and analyzing the vast amount of information generated in a retail business, and help you generate a more effective business model for keeping your business profitable. Retail and Business Intelligence Successful retailers strive to accomplish three basic
objectives:
to align their business with client needs; to differentiate from competitors; and To optimize product mix and space utilization.
Page | 16
To achieve these goals, retailers must be able to successfully manage inventory, product mixes, promotions, supply chain dynamics, and a number of other factors. Furthermore, as retail markets become increasingly competitive, the ability to react quickly and decisively to market trends is more critical than ever. Lack of information is not the problem—data to assist in making these kinds of decisions is readily available from a variety of sources. On the contrary, the problem is that the volume and complexity of information available to organizations is overwhelming. Increasingly, successful retailers will be those that can effectively categorize and utilize these data for category management, client loyalty programs, promotions, etc. in short, Business Intelligence.
Retail Data Sets These are: traditional retail information, including point of sale data, gross margins, turns, and gross
margin return on inventory investment (GMROI);
market data, including market share and competitor pricing; promotional data, including special pricing offers and vendor contributions, such as
promotional allowances and coop advertising fees; and Client data, including demographics and various loyalty and client value metrics.
Category management software applications have traditionally focused on the first category of data, with occasional forays into the second. While these are certainly critical metrics in determining profitability and product mix, companies are increasingly taking a more client-centric view of their business, and are looking to the third and fourth categories of data to provide new insight into marketing and sales.
Adding Value Through Decision Support
5.1 Reporting capabilities for key performance metrics such as
product profitability;
units sold;
category management;
gross revenue; and
Client frequency and loyalty.
5.2. Performing complex analysis to derive measures for:
Evaluating success, timing, and duration of promotion campaigns;
evaluating shopper buying patterns and products, i.e., market basket analysis;
determining optimal forward buying opportunities;
determining optimal assortment mix by category;
evaluating pricing and promotion strategy by category; and Understanding issues and measuring improvements in merchandise flow.
Page | 17
5.3. Developing statistical models that predict client needs and behaviors.
Buy a new product;
generate high profitability;
respond to contacts through specific channels (e.g., direct mail, telemarketing, email, etc.); and
Remain loyal to products in the face of variables such as price, availability, etc.
5.4 Putting Decision Support to Work
Now that we have discussed the decision support capabilities that will be crucial to surviving in tomorrow’s retail business landscape, let’s take a look at how these capabilities align to the classic needs of the business. Consider the case of adding new products to the inventory.
A new product is under consideration for introduction into a chain of grocery stores. When introducing a new product, we want to know whether it is expanding the category or merely cannibalizing sales of existing, higher margin products.
Retailers frequently introduce products into one or two markets to gauge their success before rolling them out to all of the stores.
To judge success of the new product, we want to compare sales and margin of the entire category in the test store to a control store where no new product was introduced.
This could be accomplished using Business Intelligence.
We would look at percent changes over a specific time period, and be able to drill down to greater detail once we have formulated a hypothesis
For example, let’s say we introduced the product at a significant discount. Consider this scenario: Overall sales in the category did not increase relative to the control store (both stores increased absolute sales by about five percent.) Drilling down on product suggests that sales of the new product cannibalized sales of existing products, rather than driving increased demand and expanding the category.
Furthermore, the discount on the new product is shrinking the category margin.
The combined effects of cannibalization and aggressive discounting have seriously hurt the bottom line in this category.
Depending on the goal that has been defined for this category, this may not have been a successful product introduction.
Drilling down to the product level highlights the results of new product introduction on the sales and margin of existing products.
Based on this analysis, we may choose not to introduce the new product at other stores. Or, depending on the products that it competes with, we may choose to introduce the product but maintain margin by pricing it more competitively.
Insights from the Business Intelligence system enable us to accurately assess the true impact of this business event, and evaluate its effectiveness.
Page | 18
6. Business Intelligence and Management
A major theme in 2002 was the increasing use of Business Intelligence (BI) for business
process management (BPM). BI goes beyond static data snapshots to enable users to identify
and analyze ongoing business trends and patterns.
6.1 Background
In the 1980's, finance and telecommunication companies pioneered BI to support
financial and market analysis of the large volumes of data that they had begun to accumulate
electronically. The need for BI capabilities grew in the 80's and 90's in other industries as
companies began capturing data electronically across the full range of their business
activities. This need was further compounded by the growing interest in real time data access
which required effective tools to mine and analyze dramatically increased data volumes.
To support this growing need, large software and services providers like IBM and Oracle
launched major initiatives to bring data warehousing capabilities to the marketplace. These
data warehouses, or data marts, are the most common sources of data for BI applications.
ERP systems have also been used to capture data and enforce consistency, but they tend to
be too inflexible to support ad hoc exploration of data. Fortunately, better tools for access
and analysis have emerged. These tools usually start with flexible query and reporting
capabilities that are combined with some mix of online analytical processing (OLAP),
statistical analysis, forecasting and data mining techniques.
6.2 Why Is BI Useful in Retail Management?
BI use is expanding from finance to other business functions because it provides a quick
Return on Investment (ROI). It complements supply chain planning because BI applications
provide incremental benefits while a business lays the foundation for more sophisticated
tools and related business process changes.
To reap some quick returns and support their supply chain projects, some companies
are using BI tools to:
Improve data visibility so as to reduce inventory levels by 5% to 15% in some
businesses.
Analyze customer service levels to identify specific problem areas.
Better understand the sources of variability in customer demand to improve forecast
accuracy.
Analyze production variability to identify where corrective measures need to be taken.
Analyze transport performance to reduce costs by using the most efficient transport
providers.
Page | 19
By providing wider visibility to plans and supporting data, BI tools increase the return on
existing SCP applications because they help companies understand where and how they
deviate from their plan objectives. In addition, they provide shared data availability that
encourages a global perspective on business performance. As a result, people are more likely
to make decisions based on their global impact.
7. Some Current Developments
BI capabilities are now being integrated into other products. In fact, Microsoft has vowed
to bring BI to the workforce with their next release of SQL Server. Not surprisingly, its
ownership of the desktop work environment gives Microsoft an edge over Oracle and IBM,
who have also announced enhancements to their OLAP and data mining capabilities. All this
puts pressure on traditional BI applications providers. The CRM (Customer Relationship
Management) community has even come up with a name for BI analysis of customer
behavior (CRM analytics).
Although SCP vendors are offering BI capabilities as well by adding layers of products from
the traditional BI vendors, the resulting mix of applications can be cumbersome to implement
and support. In addition, IT publications report that end users often have difficulty using
generic tools that were not designed to support specific roles or job functions. New SCP
products, like Zemeter, have an advantage because these BI capabilities are easily
incorporated as part of a single offering. These newer tools can also be configured to support
specific business roles.
BI applications have become increasingly cost effective because they utilize the
connectivity provided by the Internet and by intranets and because component-based
software development speeds implementation. Since implementation consists of installing
the software and connecting the data feeds, BI tools with good user interfaces can be put
into use within a few weeks.
Some companies that haven't developed these capabilities in an organized fashion are
seeing independent, often underground, local projects popping up in their businesses. While
it is encouraging to see employees take the initiative in addressing business problems, this
fragmented approach often produces a series of applications with overlapping functionality
drawing on a Hodge podge of different technologies. Sustaining these applications becomes a
headache, and effective support often hinges on the continued presence of a local super
user.
Page | 20
8. Implications for the future of Retail Management Applications
BI applications will become part of the standard technology set used by most
businesses and will have a synergistic effect on current and future SCP applications.
The continued evolution of component based software development will lead to
increased consideration of internal development, particularly around BI applications.
The option of internal development will put downward pressure on software prices.
Vendors will move from pricing based on estimates of value added (which are often
optimistic) to pricing based on development costs.
9. Conclusion
As retail markets become increasingly competitive, the ability to react quickly and decisively to market trends and to tailor products and services to individual clients is more critical than ever. Although data volumes continue to increase at an astounding rate, the problem is no longer simply one of quantity; at the heart of the issue is how companies are using their information.
Increasingly, particularly in the retail industry, it is important to understand client preferences and behavior. A business intelligence system can be a very effective means of organizing and analyzing the complex barrage of information generated in our business, and helping us generate a more effective business model for keeping our client base happy and profitable.