financial statements with ease
Most business owners seek to better
understand their finances in order to ensure their success. Profit and loss
statements can help explain the bottom line of your business. However, quickly
understanding and communicating your gains, losses and balances by viewing
financial statements can be challenging. With a Waterfall chart, you can
quickly illustrate the line items in your financial data and get a clear
picture of how each item is impacting your bottom line. The example below shows
the income statement for a bookstore. It’s clear to see that the cost of
inventory nearly cut net revenue in half while operating costs accounted for an
additional third of net revenue.
A Waterfall chart provides a simple visual of
the running total of your financial data, identifies the contributions and
provides clear subtotals, giving you a ready-to-present financial report in a
analyzing a distribution
For a bookstore owner, it is important to continuously
find new ways to attract customers. Stocking books with both high-end and
low-end prices can help appeal to a wider range of readers. The new Histogram
chart can display the distribution of the book prices in inventory so the
bookstore owner can ensure inventory can meet the customer’s needs.
Commonly used in statistics, a histogram
automatically displays the frequencies within a distribution. In this example,
the horizontal axis represents the book price. Each column, called a “bin,”
shows the number of books within a given price range. Here we see that this
bookstore has a good distribution of books, both high-end and low-end.
Excel now makes it is easy for you create the
Histogram chart. After creating the chart, use the intuitive options to change
the bin ranges to dig deeper into the data.
Pareto—finding the largest
Continuing with the bookstore example, the
owner now wants to focus on quality control by reducing the number of returned
books. Each day, a number of books are returned and tabulated for various
reasons—maybe the book is a defect or the customer bought the wrong book. The
Pareto chart will help the bookstore owner to see the most common reasons
customers return books.
Using the Pareto chart, you can automatically
sort the frequency of the most prevalent issues (the bar graph) and then show
the additive contributions of each issue as you move along the horizontal axis
(the line graph).
In the example below, each column represents
a reason for a book return. The line graph shows how each column, or issue,
contributes to the overall total of returned books. Notice, from the bar graph,
that the “defect” category caused 2,025 book returns. From the orange Pareto
line in the chart, we see that this means defects contributed to 40 percent of
all book returns. By improving on just the top three reasons for
returns—defects, incorrect pricing and wrong products—the bookstore owner can
address over 80 percent of the returns!
The Pareto chart allows you to prioritize the
improvements you want to make in the bookstore to address the most critical
Box & Whisker—bringing
statistics to distribution
Like the Histogram chart, the Box &
Whisker chart shows the distribution of information. For deeper analysis, this
chart goes further by providing key insights about the distribution in one
view, including range, quartiles, mean and outliers. And you get all of this
information with a few clicks.
In this example, we are able to compare the
price distribution of books by genre. The Box & Whisker chart automatically
groups the books by their genre and displays the characteristics of the
distribution of pricing in a way that can be easily analyzed. Notice that the
book prices of Romance have a wider range and is especially skewed by the one
Box & Whisker chart adds a visual angle
to Excel’s statistical functionality, creating a simple snapshot view of the
hierarchies in one view
For the bookstore owner, it is very useful to
know which book genres provide the largest source of revenue. But what if you
could easily identify the largest revenue generators for each level of genre
categorization … in one view? The Treemap chart is an ideal visualization for
this purpose because it provides a hierarchical view of your data and an easy
way to compare different levels of categorization.
In this example, we can see each sub-genre
grouped to its parent genre automatically, by color and proximity. The size of
each node, marking a sub-genre, represents the total revenue of all books under
that category. You can easily see that most of the revenue comes from
Children’s books and Romance books, but also that 1st Readers and
Young Adult titles are the most lucrative.
With Treemap, large datasets with innate
groupings can be effectively visualized in a simple way. Treemap draws the big
picture, so you can draw comparisons between similar or competing products.
level of your hierarchy
While using a Treemap chart is ideal for
comparing the relative sizes of groups, the Sunburst chart shows the full
hierarchy of the groups to provide deeper analysis capabilities. With a
Sunburst chart, it’s easy to see the largest contributing segments within a
hierarchy of multiple levels.
The visual layout is intuitively natural for
finding how each slice is broken down to the most basic contribution. The
Sunburst is versatile, displaying any number of levels for any category.
These six new chart types provide a rich new set of storytelling tools
in Excel, Word and PowerPoint that enable you to do more with your data.
Additionally, each chart can be customized to fit your specific needs with the
intuitive design tools you are already familiar with in Excel. Use these
features to change style and layout of the chart, add chart elements, like
legends and data labels and fine-tune the fonts, colors and effects.