How to Dynamically Pivot Data in SQL Server

 

SQL is the lifeblood of any data professional. If you can’t leverage SQL and you work with data, your life will be more difficult than it needs to be.

In this video I am using SQL Server Express to turn a simple normalized dataset into a pivoted dataset. This is not a beginner video as I assume you are familiar with basic SQL concepts.

T-SQL is Microsoft’s SQL language that contains additional functions and capabilities over and above ANSI standards. We’ll use some of these functions to turn the following data set that displays average rents in major American cities into a pivoted denormalized dataset.

The City values in the City column will become individual columns in a new pivoted dataset with their respective Average Rent values appearing underneath.

We’re going to transform this:

Normalized Data

Into this:

Pivoted Data

Notice how the city values are now column heads and the respective Average Rent values are underneath.

Make sure you watch the video but here is the code used in the example.

IF OBJECT_ID('tempdb..##TBL_TEMP') IS NOT NULL
DROP TABLE ##TBL_TEMP

--This parameter will hold the dynamically created SQL script
DECLARE   @SQLQuery AS NVARCHAR(MAX)

--This parameter will hold the Pivoted Column values
DECLARE   @PivotColumns AS NVARCHAR(MAX)

SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME([City])
FROM [dbo].[tbl_Rent]

/* UNCOMMENT TO SEE THE NEW COLUMN NAMES THAT WILL BE CREATED */
--SELECT   @PivotColumns

--Create the dynamic query with all the values for
--pivot column at runtime
--LIST ALL FILEDS EXCEPT PIVOT COLUMN

SET   @SQLQuery =
   N'SELECT [City Code],[Metro],[County],[State],[Population Rank],' +   @PivotColumns + '
   INTO ##TBL_TEMP
   FROM [dbo].[tbl_Rent]
   
   PIVOT( MAX([Average Rent])
      FOR [City] IN (' + @PivotColumns + ')) AS Q'

/* UNCOMMENT TO SEE THE DYNAMICALLY CREATED SQL STATEMENT */
--SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery

/* VIEW PIVOTED TABLE RESULTS */
Select * from ##TBL_TEMP

 

Big shoutout to StackOverflow for help with this example.

 

Advertisements

Calculate Bar Chart Percent of Total in Power BI

The humble bar chart is the heart and soul of any visualization tool and is the most effective way to compare individual categorical values. We as humans are very adept at detecting small differences in length from a common baseline [1].

To quote the Harvard Business Review [2], “The ability to create smart data visualizations was once a nice-to-have skill. But in today’s complex business world, where the amount of data is overwhelming, being able to create and communicate through compelling data visualizations is a must-have skill for managers.”

If you’re going to start learning a new visualization tool, there is no better place to start than with bar chart basics. In this video I will share how to place a “percent of total” measure (i.e. value) on a Power BI bar chart. We’ll also briefly touch upon customizing the chart’s diverging color scheme.

Since Microsoft is basically giving away Power BI Desktop for free, it may become as ubiquitous as Excel. Don’t be left out!

References:

[1] Cotgreave, A., Shaffer, J., Wexler, S. (2017). The Big Book of Dashboards: Visualizing Your Data Using Real-World Business Scenarios. Hoboken, NJ: John Wiley & Sons, Inc.

[2] https://hbr.org/webinar/2018/02/the-right-stuff-chart-types-and-visualization-best-and-worst-practices

Tableau Sales Dashboard Performance

The following is a guest post contributed by Perceptive Analytics.

Business heads often use KPI tracking dashboards that provide a quick overview of their company’s performance and well-being. A KPI tracking dashboard collects, groups, organizes and visualizes the company’s important metrics either in a horizontal or vertical manner. The dashboard provides a quick overview of business performance and expected growth.

An effective and visually engaging way of presenting the main figures in a dashboard is to build a KPI belt by combining text, visual cues and icons. By using KPI dashboards, organizations can access their success indicators in real time and make better informed decisions that support long-term goals.

What is a KPI?

KPIs (i.e. Key Performance Indicators) are also known as performance metrics, performance ratios or business indicators. A Key Performance Indicator is a measurable value that demonstrates how effectively a company is achieving key business objectives.

A sales tracking dashboard provides a complete visual overview of the company’s sales performance by year, quarter or month. Additional information such as the number of new leads and the value of deals can also be incorporated.

Example of KPIs on a Sales Dashboard:

  • Number of New Customers and Leads
  • Churn Rate (i.e. how many people stop using the product or service)
  • Revenue Growth Rate
  • Comparison to Previous Periods
  • Most Recent Transactions
  • QTD (quarter to date) Sales
  • Profit Rate
  • State Wise Performance
  • Average Revenue for Each Customer

Bringing It All Together with Dashboards and Stories

An essential element of Tableau’s value is delivered via dashboards. Well-designed dashboards are visually engaging and draw in the user to play with the information. Dashboards can facilitate details-on-demand that enable the information consumer to understand what, who, when, where, how and perhaps even why something has changed.

Best Practices to Create a Simple and Effective Dashboard to Observe Sales Performance KPIs

A well-framed KPI dashboard instantly highlights problem areas. The greatest value of a modern business dashboard lies in its ability to provide real-time information about a company’s sales performance. As a result, business leaders, as well as project teams, are able to make informed and goal-oriented decisions, acting on actual data instead of gut feelings. The choice of chart types on a dashboard should highlight KPIs effectively.

Bad Practices Examples in a Sales Dashboard:

  • A sales report displaying 12 months of history for twenty products; 12 × 20 = 240 data points.
    • Multiple data points do not enable the information consumer to effectively discern trends and outliers as easily as a time-series chart comprised of the same information
  • The quality of the data won’t matter if the dashboard takes five minutes to load
  • The dashboard fails to convey important information quickly
  • The pie chart has too many slices, and performing precise comparisons of each product sub-category is difficult
  • The cross-tab at the bottom requires that the user scroll to see all the data

Now, we will focus on the best practices to create an effective dashboard to convey the most important sales information. Tableau is designed to supply the appropriate graphics and chart types by default via the “Show me” option.

I. Choose the Right Chart Types

With respect to sales performance, we can use the following charts to show the avg. sales, profits, losses and other measures.

  • Bar charts to compare numerical data across categories to show sales quantity, sales expense, sales revenue, top products and sales channel etc. This chart represents sales by region.

1

  • Line charts to illustrate sales or revenue trends in data over a period of time:

2

  • A Highlight table allows us to apply conditional formatting (a color scheme in either a continuous or stepped array of colors from highest to lowest) to a view.

3

  • Use Scatter plots or scatter graphs to investigate the relationship between different variables or to observe outliers in data. Example: sales vs profit:

4

  • Use Histograms to see the data distribution across groups or to display the shape of the sales distribution:

5

Advanced Chart Types:

  • Use Bullet graphs to track progress against a goal, a historical sales performance or other pre-assigned thresholds:

6

  • The Dual-line chart (or dual-axis chart), is an extension of the line chart and allows for more than one measure to be represented with two different axis ranges. Example: revenue vs. expense
  • The Pareto chart is the most important chart in a sales analysis. The Pareto principle is also known as 80-20 rule; i.e roughly 80% of the effects come from 20% of the causes.

7

When performing a sales analysis, this rule is used for detecting the 80% of total sales derived from 20% of the products.

  • Use Box plots to display the distribution of data through their quartiles and to observe the major data outliers

8

Tableau Sales Dashboard

Here is a Tableau dashboard comprised of the aforementioned charts. This interactive dashboard enables the consumer to understand sales information by trend, region, profit and top products.

9

II. Use Actions to filter instead of Quick Filters

Using actions in place of Quick Filters provides a number of benefits. First, the dashboard will load more quickly. Using too many Quick Filters or trying to filter a very large dimension set can slow the load time because Tableau must scan the data to build the filters. The more quick filters enabled on the dashboard, the longer it will take the dashboard to load.

III. Build Cascading Dashboard Designs to Improve Load Speed

By creating a series of four-panel, four cascading dashboards the load speed was improved dramatically and the understandability of the information presented was greatly enhanced. The top-level dashboard provided a summary view, but included filter actions in each of the visualizations that allowed the executive to see data for different regions, products, and sales teams.

IV. Remove All Non-Data-Ink

Remove any text, lines, or shading that doesn’t provide actionable information. Remove redundant facts. Eliminate anything that doesn’t help the audience understand the story contained in the data.

V. Create More Descriptive Titles for Each Data Pane

Adding more descriptive data object titles will make it easier for the audience to interpret the dashboard. For example:

  • Bullet Graph—Sales vs. Budget by Product
  • Sparkline—Sales Trend
  • Cross-tab—Summary by Product Type
  • Scatter Plot—Sales vs. Marketing Expense

VI. Ensure That Each Worksheet Object Fits Its Entire View

When possible, change the graphs fit from “Normal” to “Entire View” so that all data can be displayed at once.

VII. Adding Dynamic Title Content

There is an option to use dynamic content and titles within Tableau. Titles can be customized in a dynamic way so that when a filter option is selected, the title and content will change to reflect the selected value. A dynamic title expresses the current content. For example: if the dashboard title is “Sales 2013” and the user has selected year 2014 from the filter, the title will update to “Sales 2014”.

VIII. Trend Lines and Reference Lines

Visualizing granular data sometimes results in random-looking plots. Trend lines help users interpret data by fitting a straight or curved line that best represents the pattern contained within detailed data plots. Reference lines help to compare the actual plot against targets or to create statistical analyses of the deviation contained in the plot; or the range of values based on fixed or calculated numbers.

IX. Using Maps to Improve Insight

Seeing the data displayed on a map can provide new insights. If an internet connection is not available, Tableau allows a change to locally-rendered offline maps. If the data includes geographic information, we can very easily create a map visualization.

10

This map represents sales by state. The red color represents negative numbers and the green color represents positive numbers.

X. Developing an Ad Hoc Analysis Environment

Tableau facilitates ad hoc analysis in three ways:

  1. Generating new data with forecasts
  2. Designing flexible views using parameters
  3. Changing or creating designs in Tableau Server

XI. Using Filters Wisely

Filters generally improve performance in Tableau. For example, when using a dimension filter to view only the West region, a query is passed to the underlying data source, resulting in information returned for only that region. We can see the sales performance of the particular region in the dashboard. By reducing the amount of data returned, performance improves.

Enhance Visualizations Using Colors, Labels etc.

I. Using colors:

Color is a vital way of understanding and categorizing what we see. We can use color to tell a story about the data, to categorize, to order and to display quantity. Color helps with distinguishing the dimensions. Bright colors pop at us, and light colors recede into the background. We can use color to focus attention on the most relevant parts of the data visualization. We choose color to highlight some elements over others, and use it to convey a message.

Red is used to denote smaller values, and blue or green is used to denote higher values. Red is often seen as a warning color to show the loss or any negative number whereas blue or green is seen as a positive result to show profit and other positive values.

Without colors:

11

With colors:

12

II. Using Labels:

Enable labels to call out marks of interest and to make the view more understandable. Data labels enable comprehension of exact data point values. In Tableau, we can turn on mark labels for marks, selected marks, highlighted marks, minimum and maximum values, or only the line ends.

Without labels:

13

With labels:

14Using Tableau to enhance KPI values

The user-friendly interface allows non-technical users to quickly and easily create customized dashboards. Tableau can connect to nearly any data repository, from MS Excel to Hadoop clusters. As mentioned above, using colors and labels, we can enhance visualization and enhance KPI values. Here are some additional ways by which we can enhance the values especially with Tableau features.

I. Allow for Interactivity

Playing, exploring, and experimenting with the charts is what keeps users engaged. Interactive dashboards enable the audiences to perform basic analytical tasks such as filtering views, drilling down and examining underlying data – all with little training.

II. Custom Shapes to Show KPIs

Tableau shapes and controls can be found in the marks card to the right of the visualization window. There are plenty of options built into Tableau that can be found in the shape palette.

15

Custom shapes are very powerful when telling a story with visualizations in dashboards and reports. We can create unlimited shape combinations to show mark points and create custom formatting. Below is an example that illustrates how we can represent the sales or profit values with a symbolic presentation.

16

Here green arrows indicate good sales progress and red arrows indicate a fall in Year over Year Sales by Category

III. Creating Calculated Fields

Calculated fields can be used to create new dimensions such as segments, or new measures such as ratios. There are many reasons to create calculated fields in Tableau. Here are just a few:

  1. Segmentation of data in new ways on the fly
  2. Adding a new dimension or a new measure before making it a permanent field in the underlying data
  3. Filtering out unwanted results for better analyses
  4. Using the power of parameters, putting the choice in the hands of end users
  5. Calculating ratios across many different variables in Tableau, saving valuable database processing and storage resources

IV. Data-Driven Alerts

With version 10.3, Tableau has introduced a very useful feature: Data-Driven Alerts. We may want to use alerts to notify users or to remind that a certain filter is on and want to be alerted somehow if performance is ever higher or lower than expected. Adding alerts to dashboards can help elicit necessary action by the information consumer. This is an example of a data driven alert that we can set while displaying a dashboard or worksheet.

17

In a Tableau Server dashboard, we can set up automatic mail notifications to a set of recipients when a certain value reaches a specific threshold.

Summary

For an enterprise, a dashboard is a visual tool to help track, monitor and analyze information about the organization. The aim is to enable better decision making.

A key feature of sales dashboards in Tableau is interactivity. Dashboards are not simply a set of reports on a page; they should tell a story about the business. In order to facilitate the decision-making process, interactivity is an important part of assisting the decision-maker to get to the heart of the analysis as quickly as possible.

Author Bio:

This article was contributed by Perceptive Analytics. Neeru Gupta, Chaitanya Sagar, Prudhvi Sai Ram and Saneesh Veetil contributed to this article.

Perceptive Analytics provides data analytics, data visualization, business intelligence and reporting services to e-commerce, retail, healthcare and pharmaceutical industries. Our client roster includes Fortune 500 and NYSE listed companies in the USA and India.

Add a “Filters in Use” Alert to Your Tableau Dashboard

In this video we will learn to add a “Filters in Use Alert” to a Tableau Dashboard. If you have a dashboard with multiple filters, apply this quick and easy tip to inform your users that filters are in play. This tip builds upon the dashboard that I showcased recently in a previous post: Add a Reset All Filters Button to Your Tableau Dashboard.

I learned this current tip from a presentation given by Tableau Zen Master Ryan Sleeper, so I have to give credit where credit is due.

If you’re interested in Business Intelligence & Tableau subscribe and check out my videos either here on this site or on my Youtube channel.

Tableau for Marketing: Become a Segmentation Sniper

This article is a guest post contributed by Perceptive Analytics.

Did you know that Netflix has over 76,000 genres to categorize its movie and tv show database? I am sure this must be as shocking to you as this was to me when I read about it first. Genres, rather micro-genres, could be as granular as “Asian_English_Mother-Son-Love_1980.” This is the level of granularity to which Netflix has segmented its product offerings, which is movies and shows.

But do you think is it necessary to go to this level to segment the offerings?

I think the success of Netflix answers this question on its own. Netflix is considered to have one of the best recommendation engines. They even hosted a competition on Kaggle and offered a prize money of USD 1 million to the team beating their recommendation algorithm. This shows the sophistication and advanced capabilities developed by the company on its platform. This recommendation tool is nothing but a segmentation exercise to map the movies and users. Sounds easy, right?

Gone are the days when marketers used to identify their target customers based on their intuition and gut feelings. With the advent of big data tools and technologies, marketers are relying more and more on analytics software to identify the right customer with minimal spend. This is where segmentation comes into play and makes our lives easier. So, let’s first understand what is segmentation? and why do we need segmentation?

Segmentation, in very simple terms, is grouping of customers in such a way that that customers falling into one segment have similar traits and attributes. The attributes could be in terms of their likings, preference, demographic features or socio-economic behavior. Segmentation is mainly talked with respect to customers, but it can refer to products as well. We will explore few examples as we move ahead in the article.

With tighter marketing budgets, increasing consumer awareness, rising competition, easy availability of alternatives and substitutes, it is imperative to use marketing budgets to prudently to target the right customers, through the right channel, at the right time and offer them the right set of products. Let’s look at an example and understand why segmentation is important for marketers.

There is an e-commerce company which is launching a new service for a specific segment of customers who shop frequently and whose ticket size is also high. For this, the company wants to see which all customers to target for the service. Let’s first look at the data at an aggregate level and then further drill down to understand in detail. There are 5 customers for whom we want to evaluate the spend. The overall scenario is as follows:

Chart1

Should the e-commerce company offer the service to all the five customers?

Who is the right customer to target for this service? Or which is the right customer segment to target?

We will see the details of each of the customers and see the distribution of data.

2

Looking at the data above, it looks like Customer 1 and Customer 2 would be the right target customers for company’s offering. If we were to segment these 5 customers into two segments, then Customer 1 and Customer 2 would fall in one segment because they have higher total spend and higher number of purchases than the other three customers. We can use Tableau to create clusters and verify our hypothesis. Using Tableau to create customer segments, the output would look like as below.

3

Customer 1 and customer 2 are part of cluster 1; while customer 3, customer 4 and customer 5 are part of cluster 2. So, the ecommerce company should focus on all the customers falling into cluster 1 for its service offering.

Let’s take another example and understand the concept further.

We will try to segment the countries in the world by their inbound tourism industry (using the sample dataset available in Tableau). Creating four segments we get the following output:

4

There are few countries which do not fall into any of the clusters because data for those countries is not available. Looking at clusters closely, we see that the United States of America falls in the cluster 4; while India, Russia, Canada, Australia, among others fall in the cluster 2. Countries in the Africa and South America fall in the cluster 1; while the remaining countries fall in the cluster 3. Thus, it makes it easier for us to segment countries based on certain macro-economic (or other) parameters and develop a similar strategy for countries in the same cluster.

Now, let’s go a step further and understand how Tableau can help us in segmentation.

Segmentation and Clustering in Tableau

Tableau is one of the most advanced visualization and business intelligence tool available in the market today. It provides a lot of interactive and user-friendly visualizations and can handle large amounts of data. It can handle millions of rows at once and provides connection support to almost all the major databases in the market.

With the launch of Tableau 10 in 2016, the company offered a new feature of clustering. Clustering was once considered a technique to be used only by statisticians and advanced data scientists, but with this new feature in Tableau it becomes as easy as simple drag and drop. This feature can provide a big support to marketers in segmenting their customers and products, and get better insights.

Steps to Becoming a Segmentation Sniper

Large number of sales channels, increase in product options and rise in advertisement cost has made it inevitable not only for marketers but for almost all the departments to analyze customer data and understand their behavior to maintain market position. We will now take a small example and analyze the data using Tableau to understand our customer base and zero-in on the target customer segment.

There is a market research done by a publishing company which is mainly into selling of business books. They want to further expand their product offerings to philosophy books, marketing, fiction and biographies. Their objective is to use customer responses and find out which age group like which category of books the most.

For an effective segmentation exercise, one should follow the below four steps.

  1. Understand the objective
  2. Identify the right data sources
  3. Creating segments and micro-segments
  4. Reiterate and refine

We will now understand each of the steps and use Tableau, along with, to see the findings at every step.

  1. Understand the objective

Understanding the objective is the most important thing that you should do before starting the segmentation exercise. Having a clear objective is the most imperative thing because it will help you channelize your efforts towards the objective and prevent you from just spending endless hours in plain slicing and dicing. In our publishing company example, the objective is to find out the target age group which the company should focus on in each of the segments, namely philosophy, marketing, fiction and biography. This will help the publishing company in targeting its marketing campaign to specific set of customers for each of the genres. Also, it will help the company in identifying the target age group that like both business and philosophy or business and marketing, or similar other groups.

  1. Identify the right data sources

In this digital age, data is spread across multiple platforms. Not using the right data sources could prove to be as disastrous as not using analytics at all. Customer data residing in CRM systems, operational data in SAP systems, demographic data, macro-economic data, financial data, social media footprint – there could be endless list of data sources which could prove to be useful in achieving our objective. Identifying right variables from each of the sources and then integrating them to form a data lake forms the basis of further analysis.

In our example, dataset is not as complex as it might be in real life scenarios. We are using a market survey data gathered by a publishing company. The data captures the age of customer and their liking/disliking for different genres of books, namely philosophy, marketing, fiction, business and biography.

  1. Creating segments and micro-segments

At this stage, we have our base data ready in the analyzable format. We will start analyzing data and try to form segments. Generally, you should start by exploring relationships in the data that you are already aware of. Once you establish few relationships among different variables, keep on adding different layers to make it more granular and specific.

We will start by doing some exploratory analysis and then move on to add further layers. Let’s first see the results of the market survey at an aggregate level.

5

From the above analysis, it looks like fiction is the most preferred genre of books among the respondents. But before making any conclusions, let’s explore a little further and move closer to our objective.

If we split the results by age group and then analyze, results will look something like the below graph.

6

In the above graph, we get further clarity on the genre preferences by respondents. It gives us a good idea as to which age group prefers which genre. Fiction is most preferred by people under the age of 20; while for other age groups fiction is not among the top preference. If we had only taken the average score and went ahead with that, we would have got skewed results. Philosophy is preferred by people above the age of 40; while others prefer business books.

Now moving a step ahead, for each of the genre we want to find out the target age group.

7

The above graph gives us the target group for each of the genres. For biography and philosophy genres, people above the age of 40 are the right customers; while for business and marketing, age group 20-30 years should be the target segment. For fiction, customers under the age of 20 are the right target group.

Reiterate and refine

 In the previous section, we created different customer segments and identified the target segment for publishing company. Now, let’s say we need to move one more step ahead and identify only those age groups and genres which have overlap with business genres. To put it the other way, if the publishing company was to target only one new genre (remember, they already have customer base for business books) and one age group, which one should it be?

Using Tableau to develop a relation amongst the different variables, our chart should look like the one below.

8

Starting with the biography genre, age group 30-40 years comes closest to our objective, i.e., people in this age group like both biography and business genre (Biography score – 0.22, Business score – 0.31). Since, we have to find only one genre we will further explore the relationships.

For fiction, there is no clear overall with any of the age groups. For marketing, age group 20-30 year looks to be clear winner. The scores for the groups are – marketing – 0.32, business – 0.34. The relation between philosophy and business is not as strong as it is for business and marketing.

To sum it up, if the publishing company was to launch one more genre of books then it should be marketing and target customer group should be in the range of 20-30 years.

Such analysis can be refined further depending on the data we have. We can add gender, location, educational degree, etc. to the analysis and further refine our target segment to make our marketing efforts more focused.

I think after going through the examples in the article, you can truly appreciate the level of segmentation that Netflix has done and it clearly reflects the reason behind its success.

Author Bio:

This article was contributed by Perceptive Analytics. Vishal Bagla, Chaitanya Sagar, Saurabh Sood and Saneesh Veetil contributed to this article.

Perceptive Analytics provides data analytics, business intelligence and reporting services to e-commerce, retail, healthcare and pharmaceutical industries. Our client roster includes Fortune 500 and NYSE listed companies in the USA and India.

 

 

You Don’t Need an MBA to Use Net Present Value

Net Present Value (NPV) and Discounted Cash Flow Analysis (DCF) are financial/MBA wonky terms that may intimidate a majority of people. But in actuality they are simple concepts to understand. Once you realize that a dollar received in the future is worth less than a dollar received in the present, you’re halfway home.

Why Should You Care?

The basics of NPV and DCF are not just applicable to CFOs and financial analysts, they’re also applicable to the small business deciding whether to purchase additional equipment, or the person deciding whether to lend money to a friend. These tools help you determine if an investment is profitable considering the time value of money.

If someone offers you $10,000 today, you can immediately invest that $10K in order to start earning money. If someone offers you the same $10,000 one year from now, you have missed a year’s worth of time to take that money and start earning interest.

What is the Discount Rate?

Consider if you will the concept of a “discount” interest rate. If you take your cash and simply invest it at this “discount” rate, you will have earned some return.

What is the discount rate you ask? The discount rate (or r) is the minimum rate of return that you require of an investment in order to take action.

  • For example, you believe that $10,000 invested in the stock market today will offer you a 10% return in one year’s time (i.e. $1000)
  • In order for you to consider an alternative option for investing your $10,000, it has to at least return greater than $1,000 in profit (i.e. return higher than the discount rate that you have established)

A Discount Metaphor

In this example, think of money as a collection of items from a high end clothing line that you are considering purchasing. If you buy the clothes today at present value, you are fine with the current price. But if someone tries to offer you the exact same set of clothes 1 year in the future for today’s prices, you would demand a discount to compensate for the indignity of wearing outdated clothes.

You might have paid $200 for a pair of Jordache jeans and a Member’s Only jacket in 1982, but if someone offered you those items for the same price 36 years later, you would demand a steep discount to compensate for the passage of time. Similar to cash received at a future date, the clothes are valued less the more years you move into the future.

Members Only

Are you still a member?

Receiving $10,000 5 years in the future is not the same as receiving $10,000 today. You would need to discount that $10,000 future payment to reflect the passage of time. Applying your selected discount rate of 10% to the $10,000 received 5 years in the future, you would value that money at $6,209.21; i.e. $10,000/(1+0.1)^5. Don’t worry about the math yet.

If someone ever asks you for a $10,000 investment today only to repay you $10,000 in 5 years, please decline because you would be losing money. The $10K received 5 years in the future would be identical to $6,209 received today. This is because you can immediately invest the $6,209 today and let it compound for 5 years at 10% per year, and have the same $10K in 5 years’ time. Hopefully I haven’t lost you.

Here Comes the Math, Don’t be Scared:

Present Value Example:

  • $10,000 in Present Value * 1.1 = Future Value of $11,000
  • This is the same as saying that $10,000 today returning 10% interest is worth $11,000 in one year

Alternatively, to reverse the scenario:

  • $11,000 in Future Value / (1+0.1) = Present Value of $10,000
  • This is the same as saying that $11,000 received in one year has a present value of $10,000 at 10% interest

In both scenarios, receiving $11,000 in one year would be identical to having $10,000 today. Like those 80’s Members Only jackets, you are essentially applying a 10% discount off of the $11,000 received one year in the future, in order to value those future dollars in present terms.

Present Value Formula2

FV = future cash to be received in year n

This formula is simply plug and chug. Remember that FV = the dollars you expect to receive at some point in the future (i.e. a future cash flow).

Years

You don’t have to limit yourself to evaluating a cash flow one year in the future. The variable n is used in this formula to represent the number of years in the future that you wish to evaluate money received.

Present Value with Years:

  • $10,000 in Present Value * 1.1 * 1.1 = Future Value of $12,100 in two years’ time.
  • This could also be expressed as $10,000 * (1.1)^2 = $12,100 where 2 represents the number of years in the future.

This is the same as saying that $10,000 today at 10% interest is worth $12,100 in two years.

Alternatively, to reverse the scenario:

  • $12,100 in Future value / (1+0.1) / (1+0.1) = Present Value of $10,000.
  • This could also be expressed as $12,100 / (1+0.1)^2 = Present value of $10,000

This is the same as saying that $12,100 received in two years has a present value of $10,000 today at 10% interest).

Net Present Value Scenario (Rolling in NPVs)

Now that you have an understanding of Present and Future Value, we can broach the concept of Discounted Cash Flow analysis or DCF. Again, don’t let the fancy wording intimidate you. This is simply a way of applying the Present Value formula we learned above to multiple cash flows (i.e. money that you receive at different years in the future).

Suppose that you are a financial analyst at the very prestigious Wu-Tang Financial investment firm. At this firm, cash rules everything around you so you have to make the right decisions in order to maintain your job. The firm is considering the following option in regards to investing its money and they’ve come to you for a breakdown of the analysis after firing the last financial analyst.

  • Option 1: The firm is considering investing $10K in a Brooklyn Zoo. This option requires a $10K upfront investment and pays out cash flows as follows over the course of 5 years

NPV

In our example, the firm invests $10K today (i.e Year 0) and receives $2K in one year, $3K in two years, $4K in 3 years and a final payment of $3k in 4 years’ time. The initial $10K investment is represented as a negative number to indicate that it is a payment and those funds are no longer available to the investor.

The previous financial analyst would have simply added all of the cash flows together and determined if the sum of those cash flows were positive. He would have recommended that the firm move forward with the Brooklyn Zoo investment because it returned $2K to the firm over the course of 4 years.

You immediately recognized that this approach does not consider the time value of money. You determine that if the firm were to simply diversify its bonds, it could earn an easy 10% per year on that initial $10K investment. You choose 10% as your discount rate and protect your neck by applying a discounted cash flow analysis.

You proceed to calculate the present value of all of the expected cash flows in the year that they are expected.

Cash Flow 2

With an NPV of -$648.18, this investment is a money loser

By taking the Cash Flow numbers in column B and individually dividing them by (1+.01)^Year, (where .01 represents the 10% discount rate) you were able to calculate the present value (column D) of all the cash flows by the year in which they were received.

For example, the expected $2,000 received in Year 2 is only worth $1,818.18 today. That’s because $1818.18 invested today at 10% interest will produce $2,000 in one year. This same concept applies to the cash flows represented for each year.

By adding and subtracting all of the present values in column D, you calculated the Net Present Value of the Brooklyn Zoo investment (considering you could have earned 10% compounded yearly in an alternate investment).

NPV Formula

When calculating NPV, initial investment is typically a negative number

By evaluating the investment in this manner, you easily determined that a potential investment in the Brooklyn Zoo would lead to a loss of $648.18. If the firm were to simply take that initial $10K and collect 10% interest per year compounded for 4 years, it would have collected an additional $648.18 in profit.

You decide to tell management “Shame on you if you move forward with this investment.”

Summary

Hopefully if you made it this far, the following summary will make sense. If you need to analyze the profitability of a potential investment, take the following steps:

  1. Pick an interest rate as your “discount rate”. Investing your dollars at this rate of return will always be your default fallback option
    • The discount rate (or r) is the minimum rate of return that you require of an investment in order to take action
  2. Calculate the Present Value of all cash flows received in the future (i.e. Future Value) with the following formula:

NPV Formula

  • This method is known as a Discounted Cash Flow analysis. Summing all of the Present Values of Future Cash Flows determines the Net Present Value (NPV) of the investment
  1. If the NPV returns a positive number, then this initiative should be pursued as it provides value over and above dollars invested at the discount rate of return
  2. If the NPV returns a negative number, then the initiative should not be pursued as it subtracts value in comparison to the same dollars invested at the discount rate of return. You should instead invest your dollars at your initial discount rate.

Tableau NPV Dashboard

If you follow my blog, you know Tableau has to figure in to my post somehow. Here is the sample dashboard used at Wu-Tang Financial to present the results of our Brooklyn Zoo NPV case. The bar chart discounted values or present values are highlighted in yellow (e.g. $1.8K, $2.5K, etc.), while the expected future cash flows are a muted color and labeled above the present values. As you change the Discount Rate Parameter slide, the NPV is recalculated and the bar charts are updated.

Feel free to download and/or interact with it on Tableau Public.

Dashboard 2

Always remember, as with any analysis, the results are only as good as the inputs!

Header Image Copyright: photonphoto / 123RF Stock Photo

Add Totals to Stacked Bar Charts in Tableau

 

In this video I demonstrate a couple of methods that will display the total values of your stacked bar charts in Tableau. The first method deals with a dual axis approach while the second method involves individual cell reference lines. Both approaches accomplish the same objective. Hope you enjoy this tip!

If you’re interested in Business Intelligence & Tableau subscribe and check out my videos either here on this site or on my Youtube channel.