Easily Unpivot Your Data in Excel Using Power Query

Use the unpivot functionality in Power Query (a free Excel add-in) to easily turn your cross-tabbed data into a more normalized structure. The normalized data structure will grant you the flexibility to create additional analyses in a more efficient manner.

See also:

Download: Power Query Excel Add-In

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

Advertisements

Create a Map with Multiple Layers in Tableau

In this video you’ll learn how to create a map with multiple layers in Tableau using Tableau’s included superstore data set.

  1. We’ll start by building a filled map that represents the profit by state.
  2. We’ll layer on top of this map a pie chart that breaks down Sales by Category.
  3. As a bonus tip we’ll touch upon the FIXED Level of Detail (LOD) expression in order to calculate a percentage of sales by state and category for the pie chart.

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

The Definitive Walmart E-Commerce and Digital Strategy Post

Introduction:

Walmart has long been a dominant player in the traditional “bricks & mortar” retail space. The retailing giant has about 4,600 stores in the United States and about 6,000 stores worldwide that helped it generate fiscal year 2017 revenues of $485.9 billion. However, this retailing “Death Star” has a weakness as technological changes and innovations in its industry represent both an opportunity and a threat. The biggest threat to Walmart is the consumer preference shift from traditional in-store purchases to on-line digital channels. E-commerce is a small piece of the retail pie currently (roughly 10.4% of all retail sales in 2015), but it is growing at a pace that is much faster than growth at bricks and mortar locations. If Walmart does not evolve to defend its dominant market position, the company will erode (see Montgomery Ward, Woolworths, K-Mart, Sears) allowing other industry competitors to capitalize.

Previous disruptions in the retail space have not been kind to dominant players. Sears was able to overtake dominant retailing incumbent Montgomery Ward in the 1950’s by aggressively investing into suburbs (which was a new phenomenon for the time), while Montgomery Ward skittishly hoarded cash in anticipation of another great depression [1].

Walmart is not willing to be a Montgomery Ward in this scenario as the company became aware of the risks of e-commerce underinvestment and complacency. However, e-commerce giant Amazon is more than willing to be Sears in this example by over-investing in the more recent retail business model (e-commerce). Furthermore, Amazon recently encroached into Walmart’s home turf (i.e. physical locations) by purchasing Whole Foods for $13.7 billion. This high profile acquisition signaled to Walmart and the rest of the retail industry that Amazon is willing to take unanticipated bets to develop a competitive advantage across multiple channels.

Walmart certainly has a challenging road ahead if it wishes to catch Amazon in overall e-commerce sales but it is finally competing effectively. Although the company does not break out specific e-commerce dollars, it stated that its e-commerce sales had increased 64% domestically in the first quarter of 2017. Consider that Amazon generated $136 billion in annual sales during 2016, which accounted for half of all online shopping in the United States [2].

“With approximately 160 million items for sale, Amazon has become the go-to outlet for anything. In comparison, Walmart.com sells “only” 15 million items — and just 2 million of them are available for the free two-day shipping. It’s no wonder 52% of online shoppers start their search on Amazon, according IHL Group.” [3]

Walmart will not be able to overtake Amazon’s position as the dominant e-commerce player in the near future, but the company is positioning itself to remain competitive.

Walmart’s Main Strategic Risks in E-Commerce

Walmart’s annual 2017 10-K filing (a comprehensive summary of financial performance) details the strategic risks that the company faces. As mentioned previously, Walmart is aware of the risks of e-commerce underinvestment and complacency. Consumer preferences are shifting to shopping online and mobile platforms.

Failure to grow our e-commerce business through the integration of physical and digital retail or otherwise, and the cost of our increasing e-commerce investments, may materially adversely affect our market position, net sales and financial performance [4].

Many companies fail to adequately capitalize on the shift in consumer preferences (e.g. Smith Corona, Blockbuster, Kodak), while other firms are able to successfully capitalize (e.g. Intel, Apple). Unsuccessful companies either refuse to risk capital, lack the vision, or lack the execution competency to produce the new products and/or technologies necessary to maintain success. With that being said, Walmart plans to increase its investments in e-commerce and technology, while moderating the number of new store openings.

Screen Shot 2017-06-11 at 5.52.14 PM.pngFigure 1. [4]

Walmart’s capital expenditures back up its strategy. Observe that a $1.023 billion reduction in new stores and clubs dove-tails with a $199 million dollar increase in already impressive expenditures related to information systems, distribution and digital retail ($4.162 billion line item).

Walmart recently divested itself of its Walmart Express brand which contributed to the reduction in new store capital expenditures. These convenience store sized locations were originally conceived in 2011 to compete in the price conscious dollar store segment. Dollar General (a digitally un-savvy competitor) purchased 41 Walmart Express stores and plans to rebrand them under the Dollar General moniker. In an age of stalled wage growth, Walmart is experiencing pricing pressure from both Dollar General and Family Dollar for the most price conscious consumers.

The bottom line is that Walmart has to walk a fine line in the implementation of its e-commerce strategy. On the one-hand, the company may not be successful in implementing and integrating its physical and digital retail channels. As of late the company has been criticized for “overpaying” for growth in regards to its acquisitions. If its e-commerce acquisitions underperform or sustain large losses, this can harm Walmart’s market position and financial performance.

On the other hand, if the company is “too successful” with their e-commerce strategy, the company runs the risk of lowering physical store traffic which could also adversely impact in-store economics. The company seems to be facing a “dammed if you don’t, damned if you do” conundrum.

“The challenge for Walmart, and for all other retailers in the e-commerce era, is to protect both sales and profits. But these goals nay be mutually exclusive. Retailers face pressure to offer both free shipping and competitive prices, which generally makes selling a product online less profitable than doing so in existing stores. To expand sales online, retailers must spend on technology, which squeezes margins further. Making matters even worse, retailers are often not gaining new customers but simply selling the same item to the same person online for less profit. ‘You pour from one bucket into a less profitable bucket,’ explains Simeon Gutman of Morgan Stanley.” [5]

Backend E-Commerce Acquisitions

Walmart’s initial e-commerce forays focused on acquiring companies that helped bolster its prowess in backend technologies. This approach was a departure from the company’s traditional “build rather than buy” philosophy which helped it obtain and retain technological competitive advantages in its supply chain processes. Its research division @WalmartLabs, augmented its e-commerce war chest by making multiple purchases in the first half of the decade. “Between 2011 and 2014, Walmart acquired 15 small companies tied in some way to e-commerce. The other thing most of them had in common was that they were selling for a bargain after failing to attract a new round of venture funding.” [6]

For example, in 2013 @WalmartLabs purchased a company named Inkiru for its predictive analytics technology to target customers in marketing campaigns. The company purchased Kosmix in 2011 to revamp its Walmart.com search capabilities; a project known as Polaris. Site optimization start-up Torbit was purchased in 2013 to optimize page loading of its e-commerce sites. The acquired technology compresses files to an optimum size based upon display by phones, tablets or desktops. The company also purchased Adchemy for its strong pool of data scientists and PhDs who have specialized knowledge in the areas of ad technology and search engine optimization (SEO).

As an aside, “CEO Murthy Nukala and four top executives all got payouts of between $1.5 million and $2 million in the deal” while employees who held common stock saw their holdings become worthless [7]. 

The point of these acquisitions along with others of similar characteristics, was an attempt to grow e-commerce sales organically.

The Acquisition of Marc Lore and Jet.com

cdn.corporate.walmart.jpg

Walmart learned that it is both difficult and time consuming for a firm to obtain organic growth intrinsically. When asked of his biggest regret at the helm of the company, former CEO Mike Duke who held the position from 2009 to 2013 said that the company should have moved faster to expand in e-commerce. One could draw the conclusion that Walmart either believed that growth in e-commerce would shift too much volume from bread and butter physical stores or that Amazon’s rise to e-tail prominence was not a significant threat to its dominant market position.

“When I look back, I wish we had moved faster. We’ve proven ourselves to be successful in many areas, and I simply wonder why we didn’t move more quickly. This is especially true for e-commerce. Right now we’re making tremendous progress, and the business is moving, but we should have moved faster to expand this area.” – Former Walmart CEO Mike Duke [8]

As Walmart’s sales growth continued its trend downward, new CEO Doug McMillon was tapped in 2014 to implement a new e-commerce, digital and technology focused strategy. In fact, for the first time since Walmart became a publicly traded company in 1970, annual sales shrank for the first time in 2015. McMillon was asked why did it take so long for Walmart to get into e-commerce and if the profitability of their original model affected its urgency to change. McMillon responded.

“We wish we had been more aggressive early on, no doubt. In some ways we experienced what Clay Christensen calls the ‘innovator’s dilemma.’ We hired talent, invested, and just kind of meandered along rather than hammering down, being aggressive, and making it a must-win aspect of our business. That’s partly because we had a bird in hand. We knew that if we continued to open Walmart Supercenters, they would do well.” – Walmart CEO Doug McMillon [9]

McMillion, true to his mandate, made a splash by acquiring online retailer Jet.com for 3.3 billion in cash and stock. The deal is reported to be the largest ever purchase of a U.S. e-commerce startup [10]. There were multiple reasons stated by the company for making a splashy purchase of this nature. However, the crown jewel of the acquisition was the procurement of e-commerce wunderkind Marc Lore who was immediately tapped to head both Jet.com and Walmart.com.

Marc Lore established his digital retailing bona fides by founding Quidisi. The start up was known for its diapers.com and soap.com sites amongst others. Quidisi was sold to Amazon in 2010 for $550 million. The purchase of Quidisi at the time was an attempt by Amazon to stifle competition.

“Amazon was slashing the price on diapers on its own site, putting pressure on Quidsi’s margins and making outside investors hesitant to put in more money. Furthermore, Amazon promised to keep dropping prices if Quidsi sold to Wal-Mart.” [15]

Lore stayed at Amazon for two years and then left to ponder his next move. Subsequently, in 2014 Lore founded Jet.com based upon the premise of charging members a yearly fee, encouraging consumers to buy in bulk and incentivizing consumers to purchase items from the same distribution center to lower product prices. On the strength of his name and new business model, Lore was able to raise $500 million in investment capital on this venture. Lore earned $243.9 million in 2016 making him the highest compensated CEO in the United States after the sale. Expect Lore to be at Walmart for at least five years, as he will lose substantial compensation if he exits beforehand.

Walmart previously missed out on buying Quidisi in 2010 as both Walgreens and Amazon were in a bidding war for Lore’s e-commerce property. Walmart decided with the Jet.com acquisition that they were not going to lose an opportunity to buy Marc Lore’s services again.

How Will Walmart Benefit from Jet.com?

walmart_jet.gif

In just one year of operation, Jet.com scaled up to 12 million different products and reached a run-rate of $1 billion in gross merchandise value [11]. With this acquisition, Walmart is buying additional diversity of online product offerings. The brands that Jet.com offers are those that appeal to consumers that reside outside of Walmart’s more suburban, rural, older cost conscious demographic. Jet.com’s brand positioning is targeted to younger, “urban”, millennials who constitute a faster growing demographic than the demographic that Walmart has traditionally attracted. Walmart plans to keep the Jet.com brand identity separate from Walmart.com. Jet.com has relationships with more upscale brands that may not want to sell their products on Walmart.com. Additionally, this brand separation helps maintain Jet’s appeal to higher income consumers.

According to CNBC, Jet.com shoppers are more likely to have $150,000 and up incomes. Additionally, only 20% of Jet.com buyers also purchased from Walmart.com in the past six months (as of August 8th, 2016) [12]. There was little overlap between the customer bases of both companies making the acquisition by Walmart highly attractive. Furthermore Jet.com’s innovative supply chain business model and focus on low prices dovetailed with Walmart’s penchant for supply chain innovation and focus on low prices. Here is how Marc Lore described the company’s novel “smart cart” business process:

“Here’s how it works: If you have two items in your cart which are both located in the same distribution center and can both fit into a single box, then you will pay one low price. If you add a third item that is located at a different distribution center and cannot be shipped in a single shot with the other two items, you will pay more. As you shop on the site, additional items that can be bundled most efficiently with your existing order are flagged as ‘smart items’ and an icon shows how much more you’ll save on your total order by buying them.”

It should be noted that Jet was experiencing a high cash burn rate prior to being acquired by Walmart. Jet.com dropped its annual $50 membership fee which caused it to lose money on every shipment. The advantage of Jet.com being acquired by a deep pocketed industry player like Walmart was to help alleviate the stress of private fund-raising for an unprofitable company [13].

Walmart has to allow Jet.com to maintain its startup, entrepreneurial culture or risk losing talent. For instance, Walmart’s conservative, southern influenced culture clashed with the office drinking, happy hour culture of Hoboken New Jersey based Jet.com. Walmart eventually reversed course and did not impose this “in-office prohibition” rule on subsequent startup acquisitions. However, the more conservative Walmart did ask Jet employees to be mindful of swearing in the office [14].

Jet.com has the potential to infuse Walmart with much needed digital innovation. This fresh perspective has the potential to add tremendous value to the organization as a whole. The “old guard” rooted in Walmart’s core business model needs to allow acquisitions to thrive instead of imposing the more conservative legacy culture. According to CEO McMillon, the core business itself must learn to become more digital.

“The people who run the older parts of our business must also become digital. We can’t have some people live in yesterday while others live in tomorrow. And given the effects of inertia, we need people to lean into the future even more than other companies might. We’re trying to move large numbers of people to change their established habits.” [9]

E-Commerce Executive Shakeup

There was an immediate shakeup in the executive ranks once the Jet.com acquisition materialized. Neil Ashe, Walmart’s global e-commerce head previously ran CBS Interactive and had been named head of technology shortly before the acquisition, was transitioned out to make room for Marc Lore. Lore will assume the title of president and CEO of e-commerce at Walmart. Lore will head not only Jet.com but also all of Walmart’s e-commerce functions. Also leaving is Michael Bender, Walmart’s global e-commerce COO.

Fernando Madiera who previously headed Walmart.com and was previously CEO of Walmart’s Latin American e-commerce business was transitioned. Mr. Madiera had just taken the Walmart.com post in 2014. Other high level executives that transitioned were Dianne Mills, senior vice president of global e-commerce human resources; and Brent Beabout, senior vice president of e-commerce supply chain. Not even Wal-Mart’s chief information officer Karenann Terrell was spared, as she left the company late February of 2017.

Key executives from Jet.com that will join Marc Lore’s new team include Scott Hilton who was previously chief revenue officer at Jet.com. Jet.com co-founder Nate Faust will become the senior vice president for U.S. eCommerce and supply chain for Walmart’s domestic operations.

The point of this game of executive musical chairs is to provide Marc Lore with the executive team he deems necessary to launch an effective attack on Amazon’s e-commerce dominance. Walmart has 3.3 billion reasons to make sure Lore feels he has the necessary team in place to win.

Walmart & Jet.com E-Commerce Timeline

  • February 2016: Jet.com purchases online furniture retailer Hayneedle.com for $90 million. The move is seen as way for Jet.com to acquire revenue growth. Of note, the Hayneedle CEO (John Barker) received a parachute package worth $3.4 million while other employees saw their investment stakes effectively wiped out.
  • August 2016: Walmart purchases Jet.com for $3.3 billion. The deal is reported to be the largest ever purchase of a U.S. e-commerce startup.
  • January 2017: Jet.com purchases Boston based ShoeBuy for $70 million. The purchase increases Jet’s online catalogue of items substantially and will allow the same items to be sold across Walmart.com, Jet.com and Shoes.com.
  • February 2017: Walmart purchases hip Michigan based outdoor retailer Moosejaw for $51 million. Moosejaw sells brands like Patagonia and North Face online and in its 10 brick and mortar stores. Moosejaw has expertise in online sales and social marketing that Walmart wishes to tap. Moosejaw and Its 350 employees will continue to exist as a standalone subsidiary.
  • March 2017: Jet.com purchases women’s online clothing retailer Modcloth for $75 million. The site caters to size diversity and body positivity. The acquisition represents an attempt to appeal to a younger, hipper demographic than Walmart currently courts.
  • March 2017: Walmart launches a Silicon Valley based tech incubator called Store No. 8. The initiative is named after a store where Sam Walton was known to experiment. Walmart plans to invest in businesses like a venture capitalist firm would and then grow this group of startups as a portfolio. “The incubator will partner with startups, venture capitalists and academics to promote innovation in robotics, virtual and augmented reality, machine learning and artificial intelligence, according to Wal-Mart. The goal is to have a fast-moving, separate entity to identify emerging technologies that can be developed and used across Wal-Mart.” [18]
  • June 2017: Walmart purchases NYC based men’s clothing retailer Bonobos for $310 million. The brand started modestly by selling chino pants and expanded its line of offerings for sale in its own stores and in Nordstroms. “Its co-founder and chief executive, Andy Dunn, will oversee Walmart’s digital brands, which also include the independent women’s brand ModCloth.” [16] Passionate Bonobos fans have mocked the acquisition on social media snarkily asking if the popular chinos will be refitted for the average Walmart customer.  Bonobos has a vertically integrated supply chain as it designs and manufactures all products in-house, which allows it to cut out middlemen costs [17]. Walmart is eager to tap founder Andy Dunn for his expertise in this area.

Peddling upscale merchandise will allow Walmart to expand its reach from low and middle income consumers to a more affluent base. As middle income consumers slowly shrink, Walmart is diversifying its customer base.

“Between 2000 and 2014, middle-class populations decreased in 203 of the 229 metropolitan areas reviewed in a Pew Research Center study. In an economically divided America, Walmart has tried to sell not only to shoppers looking for extreme discounts, but also to shoppers with higher incomes seeking higher-quality items. Walmart has been working to increase its sales to more affluent customers for years, especially in e-commerce.” [19]

Conclusion

Walmart’s e-commerce strategy appears to be reaping dividends as of the writing of this post. As mentioned earlier, Walmart stated that its e-commerce sales had increased 64% domestically in the first quarter of 2017.

For years, Walmart has dominated the retail space with its low cost/low price strategy (see my Micheal Porter post). In today’s e-commerce environment, the key is to compete on low prices and convenience, as well as appeal to diversified income groups. Only time will reveal if Walmart has the innovative capacity and leadership to overtake Amazon. The company is making bold bets in the e-commerce space and is aware of the shift in consumer preferences.

Walmart’s core business must be willing to be disrupted by its internal innovators. The current retail landscape is one of declining profits and closing stores. The organization as a whole must not be ideologically wedded to its massive assortment of physical stores while ignoring threats from outside competitors (namely Amazon).

Additionally, Walmart cannot ignore fresh retail ideas emanating from internal disrupters like Marc Lore, Andy Dunn or successful Store No. 8 startups if they materialize. The company must cross-pollinate successful ideas and quickly post-mortem and move on from unsuccessful ones. If Walmart continues to buy online growth at the expense of organic growth, then it must ensure that it does not continually overpay for growth and assets. If its e-commerce acquisitions underperform or sustain large losses, this can harm Walmart’s market position and financial performance.

For more Walmart coverage please check out Part 1Part 2 and Part 3 of my series on Walmart’s overall technology strategy, where I address areas such as:

  • Strategy for Technology Infrastructure
  • Strategy for IT Capability & Staffing
  • Strategy for Information Risk & Security
  • Strategy for Stakeholder Requirements, Testing & Training/Support
  • Project ROI and Key Success Measures
  • Strategy for Data Acquisition and Impact on Business Processes
  • Strategy for Social Media/Web Presence
  • Strategy for Organizational Change Management, Project Strategy and Complexity

If you’re interested in Business Intelligence & Tableau check out my videos here: Anthony B. Smoak

References:

[1] Kaufman L. & Deutsch, C. Dec 29, 2000. Montgomery Ward to Close Its Doors. New York Times. http://www.nytimes.com/2000/12/29/business/montgomery-ward-to-close-its-doors.html

[2] Abrams, R., May 18 2017. Walmart, With Amazon in Its Cross Hairs, Posts E-Commerce Gains. New York Times. https://www.nytimes.com/2017/05/18/business/walmart-online-sales-jump-63-percent.html?mcubz=0

[3] Yohn, D., March 21, 2017. Walmart Won’t Stay on Top If Its Strategy Is “Copy Amazon”. Harvard Business Review. https://hbr.org/2017/03/walmart-wont-stay-on-top-if-its-strategy-is-copy-amazon

[4] Walmart STORES, INC., ANNUAL REPORT ON FORM 10-K FOR THE FISCAL YEAR ENDED JANUARY 31, 2017. http://d18rn0p25nwr6d.cloudfront.net/CIK-0000104169/c3013d40-212d-409e-bf30-5e5fd482fc2f.pdf

[5] The Economist. June 2, 2016. Walmart: Thinking outside the box. As American shoppers move online, Walmart fights to defend its dominance. http://www.economist.com/news/business/21699961-american-shoppers-move-online-walmart-fights-defend-its-dominance-thinking-outside

[6] Levy, A. April 24, 2017. Is Wal-Mart’s New E-Commerce Acquisition Strategy Any Better Than Its Old One? https://www.fool.com/investing/2017/04/24/is-wal-marts-new-e-commerce-acquisition-strategy-a.aspx

[7] Edwards, J. May 27, 2014. Some Employees Are Furious At Management Payouts In Walmart’s Big Adtech Acquisition. http://www.businessinsider.com/adchemy-stock-payouts-in-walmartlabs-acquisition-2014-5

[8] Lutz. A. Dec 12, 2012. Walmart CEO Mike Duke Shares His Biggest Regret. Business Insider. http://www.businessinsider.com/walmart-ceo-shares-his-biggest-regret-2012-12

[9] Ignatius, A. March 2017. “We Need People to Lean into the Future”. Harvard Business Review. https://hbr.org/2017/03/we-need-people-to-lean-into-the-future

[10] Nassauer, S. Nov 1, 2016. Wal-Mart E-commerce Executives Depart in Wake of Jet.com Purchase. Wall Street Journal. https://www.wsj.com/articles/wal-mart-e-commerce-executives-depart-in-wake-of-jet-com-purchase-1478038997

[11] Gustafson, K. August 8, 2016. Wal-Mart: This is why Jet.com is worth $3.3 billion. CNBC. http://www.cnbc.com/2016/08/08/wal-mart-this-is-why-jetcom-is-worth-33-billion.html?view=story

[12] CNBC Interview with Marc Lore. Aug, 9. 2016. https://www.nytimes.com/2016/08/09/business/dealbook/walmart-jet-com.html?mcubz=0

[13] Abramsaug, R. & Picker, L. August 8, 2016. Walmart Rewrites Its E-Commerce Strategy With $3.3 Billion Deal for Jet.com. New York Times. https://www.nytimes.com/2016/08/09/business/dealbook/walmart-jet-com.html?mcubz=0

[14] Baskin, B. & Nassauer, S. June 25, 2017. It’s 5 O’Clock Somewhere—Unless You’ve Been Acquired by Wal-Mart. The retailing giant bought Jet.com for $3.3 billion, then had to cope with its weekly happy hour. Wall Street Journal. https://www.wsj.com/articles/its-5-oclock-somewhereunless-youve-been-acquired-by-wal-mart-1498410840lipi=urn%3Ali%3Apage%3Ad_flagship3_feed%3Bu3d9V%2FcPTBqo%2BB0cP7nSZQ%3D%3D

[15] Levy, A. August 9, 2016. Why Wal-Mart couldn’t let Jet.com’s founder get away…again. CNBC. http://www.cnbc.com/2016/08/09/why-wal-mart-couldnt-let-jetcoms-founder-get-away-again.html

[16] de la Merced, M. June 16, 2017. Walmart to Buy Bonobos, Men’s Wear Company, for $310 Million. https://www.nytimes.com/2017/06/16/business/walmart-bonobos-merger.html?smid=li-share

[17] Sergan, E. June 19, 2017. Bonobos Founder Andy Dunn Knows You Might Be Mad At Him For Joining Walmart. Fast Company. https://www.fastcompany.com/40432313/bonobos-founder-andy-dunn-knows-you-might-be-mad-at-him-for-joining-walmart

[18] Soper, S. March 20, 2017. Wal-Mart Unveils ‘Store No. 8’ Tech Incubator in Silicon Valley Bloomberg. https://www.bloomberg.com/news/articles/2017-03-20/wal-mart-unveils-store-no-8-tech-incubator-in-silicon-valley

[19] Taylor, K. March 24, 2017. Walmart’s latest move confirms the death of the American middle class as we know it. Business Insider. http://www.businessinsider.com/walmart-invests-as-american-middle-class-shrinks-2017-3

Photo Copyright: moovstock / 123RF Stock Photo

Create a Gantt Chart in Tableau

 

Learn to create a Gantt Chart in Excel following the steps I laid out in the above video. In case your tool of choice is Excel, check out my other video on how to create a Gantt Chart in Excel. Your inner project manager will thank you!

If you’re interested in Business Intelligence & Tableau subscribe and check out my videos either here on this site or on my Youtube channel. You will be smarter for it!

 

Coursera Review: Creating Dashboards and Storytelling with Tableau

Discounts Harm Profits

I recently finished the “Creating Dashboards and Storytelling with Tableau” course on Coursera. The course was taught by adjunct faculty at the University of California Davis. Although it is the fourth course of five in the “Data Visualization with Tableau” specialization, it is only the third course that I have taken. I skipped the very basic first course and will concentrate next on finishing the capstone. 

If you do take this course be prepared to put in a fair amount of work on weeks three and four when the dashboard and story project are respectively due. I put in at least five hours of effort on each individual assignment not including watching videos, reading materials and taking quizzes.

I found the storytelling course to be informative and worthwhile. Unlike a Udemy course on Tableau that wades right into the applied aspects of clicking and dragging items, Coursera courses offer more of an academic background on the subject matter.

The point of this course is to hammer home that stories provide context and meaning that can’t be matched by a list of facts. We’re informed that stories engage more of your brain than simply absorbing a list of facts.

We learn that you should always try to make your stories relatable to the viewer so that they personally connect or identify with some aspect of the story. You should find a specific story of a person who exemplifies the larger narrative rather than starting with a lot of general facts and figures.

Politicians employ this tactic all of the time. Instead of spouting off a list of facts about their particular issue, the politician will first paint a picture regarding Joe the small businessman or Jill the single mom. They’ll then discuss how legislation (or lack thereof) will affect their constituents particular situations; in the hope that the listener will relate to the individuals. This is an exercise in using the particular to illuminate the general.

Here are a few of the tips I learned in regard to telling stories with data:

  • Use time based trends and consider a line or bar graph depending upon the data;
  • Use rank ordering (e.g. use a bar graph to rank salespersons by sales);
  • Use data comparisons where appropriate (e.g. polling data showing candidate support over a period of time);
  • Use counter intuitive visualizations (e.g. most people are surprised to learn that the United States has the highest incarceration rate by far amongst OECD countries);
  • Tell stories through relationships (e.g. use scatterplots to illustrate the relationship between sales and profits);
  • Check your facts;
  • Focus on a key statistic or intriguing piece of information;
  • Make your story insightful; don’t leave the audience guessing on what you want them to take away form your presentation;
  • Make your story relatable;

By all means check out my submission for the final project. I illustrated the relationship between discounted orders and profits to show that discounted orders are by far less profitable. This was accomplished by creating a set in Tableau to identify all discounted orders.

Until next class!

See also:

Coursera Final Assignment: Essential Design Principles for Tableau

Coursera Final Project: Data Visualization and Communication with Tableau

Perform Fuzzy Lookups / Approximate String Matching in Excel

Most likely you have a love/hate attitude towards spreadsheets. This highly useful tip will make you fall back in love with Excel. Microsoft does a great job at providing a solid add-in that makes fuzzy lookups relatively easy to perform.

Are there more powerful approximate string matching tools out there? Of course. But if you’re using Excel, this tool should be used before applying more complicated methods.

Everyone loves visualizations but sometimes you have to roll up your sleeves and clean up the data.

Enjoy!

See also: Get Out of the Spreadsheet Abyss

Download: Fuzzy Lookup Add-In for Excel

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

 

Airborne Express & The Evolution of the Air Express Industry 1973-2002

Once again, I’m digging into my digital “crates” to share a brief writeup. This one deals with Airborne Express and the Air Express Industry. I composed this brief synopsis back in February of 2007; one year before the organization ceased operations in the United States. Airborne Express was once a low cost alternative to the FedEx-UPS duopoly and was eventually acquired by the parent company of DHL Express in 2003. DHL Supply Chain as of 2017 is the 4th largest supply chain and logistics company in North America [1]. Although DHL is a market leader in various countries internationally, the company could not find success with the Airborne Express acquisition in the United States. After five years of trying to make inroads, DHL shut down Airborne Express in 2008 and eventually lost 10 billion (USD) on the venture [2].

History and Development of the Industry

The majority of the freight industry circa 1973 was constituted of the major players in the passenger airline industry. A handful of all cargo airlines most notably Flying Tiger (founded by 10 pilots from the famous WW2 volunteer fighter unit), also participated in this space [3]. The dynamics of the industry were soon changed by Fred Smith Jr. and Federal Express. While a student a Yale, Smith envisioned “that as companies relied more on computers and technology, they would want to keep their equipment working without creating a huge inventory of parts.” [4] Federal Express pioneered the hub and spoke route system. The central hub was located in Memphis and the “spokes” were the routes between Memphis and the cities that Federal Express served. The model proved to be a success due to its many efficiencies and new entrants into the industry copied this operating process.

As federal regulations were relaxed on the air cargo industry, all-cargo carriers began to increase their route structure. The result was a substantial withdrawal from all-cargo flights by the major airlines and an increase in demand for next-day package delivery services.

The Eighties: Rapid Growth and Low Returns.

Despite the rapid growth in the air express industry during the nineteen eighties, profit margins were declining. Federal Express needed to be aware of the strong competitive force represented by UPS’s potential entry into the overnight delivery industry. UPS decided to enter the industry in 1982 and by 1984 “it had a daily volume of about 175,000 packages for its overnight and second-day service, compared with about 290,000 packages for Federal Express.” [5] UPS’s aggressive push into the industry included a strategy to compete on price and offer overnight parcel services at prices that were half that of other industry players. In 1987 Federal Express further shocked the industry by initiating price cuts on its overnight service [6]. The move was designed to preempt expansion by UPS into this space. Brazen price discounting increased the rivalry amongst established firms and inevitably led to reduced profitability in the industry.

Another threat to industry profitability came from the strong competitive force represented by the bargaining power of buyers. Powerful buyers in an industry can squeeze profits out of that industry. Major corporations that utilized the air express industry began to pool together and leveraged their purchasing power to bargain for more price reductions. “To bag a three-year deal as International Business Machines Corp.’s primary U. S. overnight carrier, Airborne Freight Corp. dangled discounts as much as 84% below Federal’s rate card. With price-cutting of that magnitude, it’s clear why a 34% jump in volume produced only 13% more revenues for the air-express industry in the first half of 1987.” [7]

Furthermore, new technologies during this time were seen as a threat to industry players. Due to the burgeoning popularity of the fax machine during the eighties, financial analysts were predicting that they could eventually displace 30% of Federal Express’s overnight-letter shipments [8].

Rising Prices in 1989

The industry during this period of time had just emerged from the shakeout phase and was entering the mature phase of the industry life cycle model. Purolator Courier, Emery, CF Air Freight and Flying Tiger had either been acquired or were marginalized due to poor operational efficiencies. As a result the industry was dominated by a small number of companies. After many years of trying to take share from FedEx and Airborne Express, UPS recognized that the market had matured at this point in time. During the mature stage of the industry life cycle model companies tend to reduce the industry competition and preserve industry profitability. UPS for the first time since it entered the air express market in 1982 began to raise prices on its next day air service. UPS wanted to convey to the rest of the industry that the price war was over. Price signaling was clearly utilized to influence the rest of the players in the industry to raise their prices. Federal Express and Airborne were more than happy to implement a tit-for-tat strategy and thus raise their prices. UPS had succeeded in raising industry profitability although the upcoming recession rendered this effect short lived.

Airborne Express Strengths and Weaknesses

Airborne Express remains a distant third in the US air express industry in terms of market share. However, the company did manage to survive the industry shakeout of the late nineteen eighties due to its distinctive competencies and low cost structure. (See Figure 1)

One of the company’s main strengths is its low cost structure relative to its peers. This low cost structure helped Airborne maintain a 16.5 share of the U.S. domestic express market in 2001, roughly the same as in previous years [10]. Airborne’s other strengths include ownership of its own airport, its exclusive grant of a foreign trade zone and its patent on C-containers. In addition, Airborne has very high brand loyalty. It won the Brand Keys Customer Loyalty Award for the parcel delivery category for five years in a row [9]. Brand loyalty is a significant asset as it helps a company retain market share.

In order to compete with its more powerful rivals in the industry, Airborne concentrated on the niche market of high volume corporate accounts. While this strategy provides constant volume it does have its drawbacks. Number one, a predominately corporate customer base will make Airborne much more sensitive to downturns in the economy. Number two, high volume customers are able to drive down prices and can command significant discounts.

Recommendations

Airborne needs to continue to cut its costs and increase its productivity in order to compete with the larger players in the industry. Its operating costs and capital spending were slashed from 368 million in 2000 to 126 million in 2001 [10]. On the productivity side, Airborne has taken positive steps by centralizing its customs brokerage service at its sort facility in Wilmington, Ohio. “This change will improve customer service and provide Airborne with greater regulatory control.” [11] Lower costs and productivity improvements will begin to pay off when the economy rebounds and help place the company in a more favorable position.

Airborne also needs to ramp up its domestic ground services in order to hedge against the shrinking market in high margin overnight deliveries. Domestic overnight shipments fell from 58% of total volume in 1998 to 52% in 2001 [12]. The company needs to be ready to respond to this shift in demand and capture revenues in ground delivery services.

Although airborne needs to maintain ties to its corporate customer base, it also needs to pay more attention to smaller customers. Its GDS service was introduced on a limited basis and was targeted at large corporate customers. This service needs to be expanded to individual customers and small businesses in order to take advantage of the large capital spending Airborne undertook to establish ground services. Increasing the number of shipping kiosks available to individual customers could be established through a strategic alliance with a retail partner. In this manner Airborne can compete with the Fedex/Kinko’s and UPS/Mailboxes Etc partnerships.

References

[1] Transport Topics: http://www.ttnews.com/top50/logistics/ (retrieved May, 13, 2017)

[2] http://content.time.com/time/specials/packages/article 0,28804,1855948_1864555_1864556,00.html (retrieved May, 13, 2017)

[3] http://www.answers.com/topic/flying-tiger-line

[4] Miller, Karin. “FedEx founder favors ‘Buck Rogers Ideas’” Fort Wayne Journal Gazette. Final Edition (2 Dec. 2001): 1D. Factiva

[5] Rotbart, Dean. “Federal Express Sinks Near Its 52-Week Low, And ‘Buy’ Recommendations Are Appearing.”  The Wall Street Journal (20 Mar 1984): Factiva

[6] Foust,Dean “Top Of The News FEDERAL EXPRESS DELIVERS A PRICE SHOCK — It’s firing the first shot in what could be an industrywide war” Businessweek (30 March 1987): Vol 2991, pg 31. Factiva

[7] Foust, Dean. “The Corporation WHY FEDERAL EXPRESS HAS OVERNIGHT ANXIETY — UPS, facsimiles, and a mature market have it worried” Businessweek (9 Nov 1987): Vol 3025, pg 62. Factiva

[8] Foust, Dean “Cover Story MR. SMITH GOES GLOBAL — HE’S PUTTING FEDERAL EXPRESS’ FUTURE ON THE LINE TO EXPAND OVERSEAS”  Businessweek (13 Feb 1989): Vol 3091, pg 66. Factiva

[9] “Air & Express Briefs.” Traffic World Magazine (15 July 2002) Factiva

[10] Putzger, Ian “Airborne Again” The Journal of Commerce Week (11 Mar 2002) Factiva

[11] “Airborne Express Enhances International Express Services” PR Newswire (7 Oct 2002) Factiva

[12] “Moody’s Lowers Airborne Express Senior Notes To Ba1” Dow Jones Corporate Filings Alert (15 Mar 2002) Factiva

More Than You Want to Know About State Street Bank’s Technology Strategy Part 3

This article is a continuation of my earlier analyses (Part 1, Part 2 here) where I waded into State Street’s strategy for Technology Infrastructure, IT Capability and Staffing, Information Risk & Security, Stakeholder Requirements, and Project ROI. In this final part of my three part series I will broach the company’s strategy for Data Acquisition, Social Media, Organizational Change Management and Project Strategy. State Street’s cloud implementation and virtualization initiative is a worthy example of business strategy/need influencing the firm’s information technology strategy.

State Street: Strategy for Data Acquisition and Impact on Business Processes:

The nature of State Street’s business as a custodian bank with trillions of dollars under custody management and multiple clients distributed worldwide means that the organization houses and processes a tremendous amount of data (internally generated and externally collected). The sheer volume and complexity of this data presents challenges as the bank looks to file regulatory reports and provide data back to its clients. The company receives an untenable 50,000 faxes a month from its client base (Garber, 2016). According to consulting firm Accenture, “(State Street) was unable to adequately track trades through each step in the trading lifecycle because there were multiple reconciliation systems, some reconciliation work was still being done manually and there was no system of record. To maintain industry leadership and comply with regulations, the company’s IT platform had to advance” (Alter, Daugherty, Harris, & Modruson, 2016).

The bank’s cloud initiative helped facilitate and speed up the burdensome process of transferring data back and forth between its clients. In addition, (as of 2016) a new digital initiative (code named State Street Beacon) will potentially help drive more cost savings. The cloud architecture project was a boon to data analysis capabilities as it enabled clients to access their data in the State Street cloud and subsequently enrich the data from multiple sources to support forecasting (Camhi, 2014). In this case, the bank’s infrastructure as a service (IaaS) enabled platform as a service (PaaS) capabilities.

The bank has embarked upon the development of 70 mobile apps and services that support its PaaS strategy. In one case, the bank developed a tablet and mobile accessible app for its client base named State Street Springboard. This application put investment portfolio data in the hands of its client base. Additionally, “Since State Street’s core competency is transaction processing, its Gold Copy app is one of the most important new tools it offers: The app lets a manager follow a single ‘gold’ version of a transaction as it moves through all of the company’s many departments and office locations — say, as it makes it way through trading, accounting, and reporting offices globally” (Hogan, 2012). This capability of the Gold Copy app enables more effective management of counterparty risk as an asset moves through the trading process.

State Street’s new infrastructure and massive data collection provides the bank with new big data capabilities that can better inform business units in the area of risk management. Data insights can potentially fortify stress testing, “What-If” and “Black Swan” scenario modeling. As we’ve seen in the recent 2016 case of Britain’s pending withdrawal from the European Union (i.e. “Brexit”), uncertainty in global financial markets is a certainty.

State Street: Strategy for Social Media/Web Presence:

State Street has traditional Facebook, LinkedIn and Twitter social presences but it has also used other social platforms to support various aims such as employee interaction and brand awareness. The bank was named a “Social Business Leader for 2013” by Information Week. As part of a “social IT” strategy (in which technology supports a collaboration initiative), the company held an “Innovation Rally” on its eight internal online forums to gather employee ideas on how best to improve its business. From 12,000 total submitted postings, employees could attempt to build a business case around the best ideas for executive management to implement (Carr, 2013). The company also launched an internal “State Street Collaborate” platform with the aim of crowdsourcing employee knowledge to help people find an appropriate in-house expert regarding diverse work related topics of interest.

Additional social initiatives include a partnership with TED to provide employees the opportunity to give a “Ted Talk” in front of their peers; the aim is to promote knowledge sharing within the organization. The bank also experiments with a presence on the (almost defunct) video sharing platform “Vine” where it can showcase the organization in quick six second sound bites. This approach caters to clients and the future millennial talent base.

State Street: Strategy for Organizational Change Management, Project Strategy and Complexity:

As stated earlier in this series, State Street started migrating its new cloud applications to production by selecting those with low volume and low complexity and then gradually ramped up to migrating the more complex applications (McKendrick, 2013). The standardization and virtualization aspects of cloud infrastructure that the bank implemented is conducive to agile development. Standardization and a reusable code approach reduces complexity by limiting development choices, simplifying maintenance and enabling new technology staff to get up to speed on fewer systems. Developers are placed in agile teams with business subject matter experts to provide guidance and to increase stakeholder buy-in. Per Perretta, “We circle the agile approach with additional governance to ensure that the investments are paying off in the appropriate timeframe” (High, 2016).

Another key approach that State Street uses to gauge project complexity is that of predictive analytics. The bank can help its internal business teams better understand the project costs and delivery timetables by analyzing historical data on all of the projects implemented over the years. The predictive analytics model uses inputs such as “…scope, team sizes, capability of the team, the amount of hours each team member spent, and ultimately, how well it delivered on these programs” (Merrett, 2015). As the business teams list their project requirements, the predictive model is created in real time which provides all parties with additional clarity.

Finally, it would be remiss to mention banking and transformation in the same breath without mentioning the requisite layoffs and outsourcing activities. For all the benefits of the bank’s cloud computing initiatives, technology workers who do not fit in with the new paradigm find themselves subjected to domestic and non-domestic outsourcing initiatives. A standardized infrastructure platform leads to fewer distinct systems in the technology ecosystem, an emphasis on code reuse and increased automation. This perfect storm of efficiency gains has lead to roughly 850 IT employees shuffled out of the organization to either IBM, India based Wipro or outright unemployment. Staffing cuts occurred amongst the employees who maintained and monitored mainframes and worked with other non-cloud based infrastructure systems. The bank was interested in shifting fixed costs for variable costs by unloading IT staff who were perceived as not working on innovative cutting edge technologies. The layoffs amount to “21% of State Street’s 4,000 IT employees worldwide” (Tucci, 2011b).

Revisit earlier analyses here:

References:

Alter, A., Daugherty, R., Harris, J., & Modruson, F., (2016). A Fresh Start for Enterprise IT. Accenture. Retrieved from https://www.accenture.com/us-en/insight-outlook-journal-fresh-start-enterprise-it

Camhi, J. (2014). Chris Perretta Builds Non-Stop Change Into State Street’s DNA. Bank Systems & technology. Retrieved from http://www.banktech.com/infrastructure/chris-perretta-builds-non-stop-change-into-state-streets-dna/d/d-id/1317880

Carr, D. (May 30, 2013). State Street: Social Business Leader Of 2013. Retrieved 6/25/16 from http://www.informationweek.com/enterprise/state-street-social-business-leader-of-2013/d/d-id/1110179?

Garber, K. (February 29, 2016). State Street doubles down on digital. SNL Bank and Thrift Daily. Retrieved from Factiva 6/20/16.

High, P. (February 8, 2016). State Street Emphasizes Importance Of Data Analytics And Digital Innovation In New Role. Retrieved from http://www.forbes.com/sites/peterhigh/2016/02/08/state-street-emphasizes-importance-of-data-analytics-and-digital-innovation-in-new-role/#a211b1320481

Hogan, M. (September 3, 2012). State Street’s Trip to the Cloud. Barron’s. Retrieved from Factiva 6/20/16

McKendrick, J. (January 7, 2013). State Street’s Transformation Unfolds, Driven by Cloud Computing. Forbes. Retrieved from http://www.forbes.com/sites/joemckendrick/2013/01/07/state-streets-transformation-unfolds-driven-by-cloud-computing/#408e1acf64cf

Merrett, R. (April 2, 2015). How predictive analytics helped State Street avoid additional IT project costs. CIO. Retrieved from http://www.cio.com.au/article/571826/how-predictive-analytics-helped-state-street-avoid-additional-it-project-costs/

Tucci. L. (July, 20, 2011). State Street tech layoffs: IT transformation’s dark side. Retrieved from http://searchcio.techtarget.com/blog/TotalCIO/State-Street-tech-layoffs-IT-transformations-dark-side

How to Build a Waterfall Chart in Tableau

In this video I will show you how to go “Chasing Waterfalls” in Tableau (apologies to TLC). Waterfall charts are ideal for demonstrating the journey between an initial value and an ending value. It is a visualization that breaks down the cumulative effect of positive and negative contributions. You’ve probably seen them used in financial statements or at your quarterly town hall meeting. Enjoy!

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

Coursera Final Assignment: Essential Design Principles for Tableau

Dashboard 1

I recently completed Essential Design Principles for Tableau offered by the University of California Davis on Coursera. I’ll offer some review commentary. I thought it was a solid class as it covered data visualization concepts such as pre-attentive attributes and the Gestalt principles. This class was a bit more heavy on the conceptual side of the house as opposed to delving into practical Tableau instructions. However, there are other classes in the specialization that have a more hands on practical approach.

In this assignment we had to highlight the three worst performing product Sub-Categories in each region. Additionally, we had to demonstrate how these worst performers compared to other product Sub-Categories in their respective regions. Finally, the visualization had to highlight the three worst performing Sub-Product Categories overall with a color emphasis. The scenario given to the class was that a sales manager had to cut the three worst performing Sub-Categories in her region and needed a visualization that addressed her concerns.

Guidance was not provided on how to identify the three worst performing categories. Some people in the class simply used profit as their key performance indicator (KPI) which I think is misguided. You learn in business (or business education) that profits do not equal profitability.  From Investopedia:

Profitability is closely related to profit, but it is the metric used to determine the scope of a company’s profit in relation to the size of the business. Profitability is a measurement of efficiency – and ultimately its success or failure. It is expressed as a relative, not an absolute, amount. Profitability can further be defined as the ability of a business to produce a return on an investment based on its resources in comparison with an alternative investment. Although a company can realize a profit, this does not necessarily mean that the company is profitable.

For these reasons I used the Average Profit Ratio of the products in each Sub-Category as my KPI as opposed to raw profits. If you had to sell $100,000 of product A to make $1,000 in profit (1% profit ratio), would you eliminate product B which requires $1000 in sales to generate $500 in profit (50% profit ratio)? Only if you want to go out of business!

In order to complete the visualization you see above on Tableau Public I had to incorporate nested sorting principles and also highlight the three worst performing elements on a bar chart. Luckily for you, I have videos that will demonstrate how to accomplish these tasks.

You can check out the rest of my videos on my Youtube Channel or find them on this site under Videos.