scorecard & dashboards

26
School of Business BI: Business Intelligence BI tools in Retail Industry from Marketing Perspective Prepar ed By : Kiran Varghese Jacob 10PG(J)19

Upload: sunam-pal

Post on 18-Nov-2014

876 views

Category:

Business


1 download

DESCRIPTION

 

TRANSCRIPT

Page 1: Scorecard & Dashboards

School of Business

BI: Business Intelligence

BI tools in Retail Industry from

Marketing Perspective

Prepared By :

Kiran Varghese Jacob 10PG(J)19

Sunam Pal 10PG(J)45

Page 2: Scorecard & Dashboards

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: Scorecard & Dashboards

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: Scorecard & Dashboards

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: Scorecard & Dashboards

Page | 5

1.1 Types of business intelligence tools

The key general categories of business intelligence tools are:

a) Spreadsheetsb) Reporting and querying software: tools that extract, sort, summarize, and present

selected datac) OLAP: Online analytical processingd) Digital Dashboardse) Data miningf) Decision engineeringg) Process miningh) Business performance managementi) 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: Scorecard & Dashboards

Page | 6

2.2 ForecastingMany 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.

2004 2005 2006 2007 2008 2009 2010 20110

5,000

10,000

15,000

20,000

25,000

30,000

f(x) = 1263.48571428571 x − 2514554.90476191R² = 0.987712388971676

No of Customer

Axis Title

Projected Figures

2004 2005 2006 2007 2008 2009 2010 2011 2012 20130

5,000

10,000

15,000

20,000

25,000

30,000

No of Customer

Page 7: Scorecard & Dashboards

Page | 7

2.3 DashboardThe 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: Scorecard & Dashboards

Page | 8

2.5 Finding correlation among different factorsThis 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: Scorecard & Dashboards

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: Scorecard & Dashboards

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: Scorecard & Dashboards

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 = ∑k=0

n

PCk1

1+d to power K

CLV: Customer Lifetime ValuePC : Profit Contributiond : Discount Raten : Number of yearsk : 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.

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

Page 12: Scorecard & Dashboards

Page | 12

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 ExcelTake 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 usually needs to be built. Excel is best used for business operations reporting and goals tracking.

Page 13: Scorecard & Dashboards

Page | 13

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: Scorecard & Dashboards

Page | 14

4.3 OLAP toolOLAP 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: Scorecard & Dashboards

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: Scorecard & Dashboards

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: Scorecard & Dashboards

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: Scorecard & Dashboards

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 BackgroundIn 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.

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

Page 19: Scorecard & Dashboards

Page | 19

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.

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.

Page 20: Scorecard & Dashboards

Page | 20

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.