Yamazumi charts

yamazumi chart new

The Yamazumi chart – also known as the operator line balance chart – is a load chart that breaks down the individual work tasks in a process, detailing the time they take. Obviously, it’s used in lean six sigma to good effect. You can download my version of the chart here.

While there are lots of different ways to plot this data, often a stacked bar chart is used (especially in Excel), usually in combination with another chart to show the % Waste, VA and NVA. The point though, is to help you to identify Value Added activities (VA), Non-Value added Activities (NVA) and Waste activities, so that you can work on reducing NVA and Waste.

Having been asked to help a colleague with a lean dashboard I did some research on the subject and found some great examples online. One chart in particular by Adaptive Business Management Systems is particularly good having a stacked bar chart with a pie chart to show the 2 sets of data. You can download their version for your personal / commercial use.  It even comes loaded with vba to update the chart as needed.

excel yamazumi chart

There are even online versions, like this one by Lean Lab.

yamazumi chart

Personally though I’d prefer to have all my information in one chart, have it organized and show very simply where I need to concentrate. It took a bit of work, but I came up with this version, a stacked bar chart with the VA, NVA & Waste plotted on the secondary axis behind the individual tasks. Basically any task that falls within the red (Waste) or yellow (NVA) areas needs to be eliminated / reduced. I’ve put the task name and category in the data label to make it clearer, similar to the ABMS version above.

yamazumi chart new

From the chart it’s clear to see that process 3 is already streamlined, whereas processes 1, 5 and 6 need the most work.

You can download my sample workbook and chart here if you wish to use it. Let me know what you think! Obviously this is still in draft form.  I need to add vba to make it update automatically as new data is added and I’ll probably tweak the formatting further – remove the visible secondary axis for example, but overall I’m pleased with the results.

Hope it helps!

+Alesandra Blakeston

Freebie: Open Blue template in Keynote and PowerPoint

Open Blue

It was my birthday last week, a fact I celebrated by going out and splurging on a macbook pro with retina display and a huge amount of memory! Obviously using a mac is very different to using windows, and though I’ve had a desktop mac before I think I’d forgotten most of the shortcuts. As a result I am getting back to grips with Yosemite OS, not forgetting the amazing software available for the mac e.g. Garageband, Keynote and Pages, and loving every minute of it.

While exploring, it occurred to me that I’d never offered a template in Keynote before on my blog, which that was enough to spur me into making one. Keynote is a very different animal to PowerPoint and you can’t just import a new master and colour scheme the way you do in PowerPoint – unless you are importing one of the amazing templates offered with the software. That plus the unfamiliarity meant it took me a while to create something I felt was worthwhile. You can have a first look at what I created below.  It’s a dark theme, with a bold contrast filled with hexagons and rectangles – very clean and simple.

Open Blue

It’s also the first template I’ve done in the 16×9 format rather than the 3×4 format I am more familiar with, but I think it works.  Anyway, let me know what you think. The font used is Open Sans and the icons are from iconsdb.com.  You can download the two versions of the template here: Keynote, PowerPoint 2010.

Hope it’s useful!

+Alesandra Blakeston

Coin chart in Excel 2010

Coin chart

Whether you are presenting total spend, or simply analyzing spend over time, using the right chart is always key. If you are a regular visitor to my blog, you will have seen the dollar chart & total spend chart I created a while back:

Create a dollar graph using an overlay image as the fill of a series plotted on the secondary axisdollar chart 3

Both of these charts show the breakdown of expenditure over a set period. However one thing I haven’t shown you (yet) is a chart showing spend over time like the one below. This chart uses a simple coin edge graphic to show the amount spent each month.

Coin chart

I’m not going to post the how to, as I think it’s pretty simple, but it just shows you what using the right graphic can do to help present your data.  Without saying a single word you can tell immediately that this chart is about money…

Hope you like it, you can download the example worksheet here.

+Alesandra Blakeston

Animate a single series in a chart

animating a series

Last week I posted a simple video showing a couple of graphs that had a single (or couple) of series animated for emphasis.  I thought this week I would post the how to!  It’s actually very simple.  You can download my PowerPoint here.

Adding an animation to a PowerPoint Chart is the same as adding an animation to any other object.  Click on the chart, then click on “Animation” on the ribbon and choose the animation you want to add.  (I chose “Wipe”).  You can then adjust the options for the animation using the “Effects Options” button.  Unfortunately when you add an animation it adds the animation to the whole chart – including the axes etc.  To see this, while the chart is selected click on the “Animation Pane” button in the animations toolbar.

animation pane

To be able to adjust the animation to the different elements of the chart, right click on the animation, and then select effect options > Chart animation.

chart animation

To be able to animate the series individually, click on the down arrow on the right hand side of the text “As one object” and select “By Element in Series” or “By Series” depending on the configuration of the chart.   I also deselected the “Start animation by drawing the chart background” check box.  Then press the OK button.

A double down arrow will now appear in the animation pane underneath the animated chart.

double down arrow

Click the double down arrow to see the individually animated series.

4 series animated

My chart has 4 series, so there are four elements that have been animated.  Since in my chart I want to emphasize series 3 and 4 only, I simply click on the ones I don’t want to see and press the delete key.  You can then adjust the options of the 2 animated series so that they start together or individually as you prefer.

I adjusted my charts so that the animation starts as soon as the viewer navigates to the page (using the start with previous option) and so that both lines start together.  You can do this using the down arrows next to each animation in the animation pane.  Click on the down arrow and then select “Start with previous”.

And that’s it – hope you find it useful.

+Alesandra Blakeston

Using animation with charts

animating a series

Recently I posted a blog on how to simplify charts so that the key message came across simply by looking at the chart.  I got a few messages after the post from colleagues saying that while the overall message of “Less is more” is great, what about adding emphasis to really sell your message?  Can you animate charts?

Well the simple answer is yes, of course you can animate charts.  That being said, I would immediately qualify it with – less is still more.  You don’t want to emphasize the entire chart and every single data point.  The short video below shows 2 charts that have been animated.  The first is a column chart.  The last series has been animated to emphasize the improvement.  The second is a line chart where the progress of the last 2 series have been animated.

Let me know what you think!  I’ll be posting the how to do this later next week, but until then you can download the PowerPoint presentation here.

Hope this helps

+Alesandra Blakeston

Free Dynamic Navigation PowerPoint Template

Dynamic Navigation Example

While browsing for examples of dynamic navigation in PowerPoint over the weekend, I came across this amazing work on youpresent.co.uk.  Using vba they have created buttons on the top, bottom, left and right of the screen to allow the user to navigate.

youpresent example

Since it is a free download under the creative common licence, I wondered if it would work for an onboarding presentation I had been asked to do. All I would need to do was attribute the original file accordingly: “based on a project by http://www.gmark.co”.

Their version uses vba to create the animation and navigation between the slides.  While it is very elegant, I did have to wonder if it was overkill.  In addition, since the code is based on having the slide numbers on the buttons, it didn’t really do what I needed. Of course, I could have altered the code to get it to look for a slide title rather than a number, but I wanted custom text in the button each time.

In the end, I took the principle of having navigation at the top, bottom, left and right, removed the vba and added simple hyperlinks to each of the buttons.  I appreciate that my way is harder in the long run (you need to manually set up the hyperlink each time rather than having vba), but I like the end result.

Dynamic Navigation Example

Incidentally, the graphics are courtesy of a free new hire orientation PowerPoint template by Tom Kuhlman from the Articulate community. Feel free to download my example (as shown in the picture above) and to check out the original on youpresent.co.uk


+Alesandra Blakeston

Less is more

less is more

In the business world I come across some really awful charts.  Take for example the one below (I’ve changed the categories and title to protect the innocent!):

awful chart

What exactly is the message behind this chart?  What data is important to know?

It turned out that the “bird” data was important.  The category had increased by 60% in the last month.  Looking at that chart can you tell that the “bird” category has improved?  Can you even tell that the “bird” category is important?

If you want to show an improvement, plot the before and after.  This can be done very simply using a line chart with the before and after being plotted, or even as a stacked bar chart.  Either of those alternatives would have presented the data in a better way.  Leaving aside the incorrect chart choice, and the improvement for now though, how could you simplify the above graph so that the important data is being shown?  Have a look at the video below to learn more.


I leave it to you to decide which is the better version!

awful chart     better chart

Hope this helps!

+Alesandra Blakeston

Using Powerpoint like Prezi

Using transitions to create movement

Don’t misunderstand me.  Prezi is a very powerful, very awesome tool!  If you haven’t tried it out, well you should.  That being said, it is possible to do some of what Prezi does in PowerPoint.  I give you this very simple example:

In the video above you will see a PowerPoint presentation consisting of 10 slides.  The movement is created with very simple transitions between the slides called PUSH transitions.  You can download the PowerPoint here.

You can use this effect to create some very powerful effects and animations in your presentation, though beware – people come to see a person speak, not to see how mad their PowerPoint skills are!

Hope this helps!

+Alesandra Blakeston

Create a series of traffic light charts

traffic lights

datapoint-3-add-new-connectionI’m always on the lookout for new and interesting ways to present data in Excel and PowerPoint. So when I saw this DataPoint add in for Powerpoint I was definitely interested. As I looked through the site and of course their blogs, I stumbled across this post: “Traffic Lights as Dynamic Semaphores” and was immediately inspired. Don’t misunderstand me, DataPoint is a very sophisticated add in for PowerPoint and does a lot more than just this one chart, but it got me wondering… Do I need the software to create this effect? Could I recreate this manually and still have the traffic lights update when I adjust the data?

If you’ve read any of the posts on this blog, you’ll already know that I love a challenge, and so off I went. The idea was to create a series of traffic lights that show a warning light (orange) when the stock level is the same as the reorder level, a danger light (red) when the stock is lower than the reorder level and of course a full steam ahead light (green) when the stock levels are healthy and therefore above the order level for that product.  As usual I started in Excel and created a data table.

table 1The figures above are just an example and if you want to recreate this using your own data, you can download my worksheet here.

Then I needed to create some calculations to plot the different graphs.  So I added three columns labelled “Red”, “Orange” and “Green”.  Using IF statements, I then got excel to calculate whether the traffic light should be red, orange or green by putting a 1 in the appropriate column. The three calculations I used can be seen below:

Assuming your titles are in Row 1 and your stock column is in Column D and your reorder column is column E:

  • Cell F2 =IF(D2<E2,1,””)
  • Cell G2 =IF(D2=E2,1,””)
  • Cell H2 =IF(D2>E2,1,””)

Copy and paste the calculations down the table and then your table should look like the one below:

table 2

As you can see, the stock for product ax234 (row 2) is higher than than the reorder level so there is a 1 in the Green column for that row.  Product ss213 on the other hand has less stock than the reorder level, so there is a 1 in the red column.  And so on.

As it stands though, all we have is a nice table.  We could use conditional formating and create a table like the one below, but it doesn’t really look like traffic lights.  In theory, we could change the formatting of the cells etc., change the direction of the table so that it works, but I wanted something a little more flash!

table 3

What I did was create a series of 3 pie-charts for each line.  Two would be blank as there is no number in their cells, the third would be filled and create the “light”.  Then behind the three pie charts, I create the illusion of the traffic light. Again, if you want to see more, you can download the worksheet here.

How is it done?

Step 1 – create a basic pie chart

With cell H2 highlighted, click on Insert > Pie > 2-D Pie > Pie

insert chartYou may get a blank pie chart.

original pie

If this happens, right click on the chart and choose “Select Data”.

select data

Click on the “Edit” button and then adjust the fields as shown below and press the OK button:

edit seriesYou should then have a filled pie chart. Delete the legend and title. Then adjust the fill of the chart and the background to “No fill” by pressing Ctrl+1 to bring up the formatting menu box and adjusting the parameters. Finally remove any border on the chart by selecting “No line” on the border color. Your chart should now look like this:

new pie chart

You can then adjust the fill of the pie chart so that it has a green radial gradient as shown below:

green gradientI’ve chosen a vivid green that changes gradually to a darker green with a radial gradient radiating from the top left corner.

Finally adjust the position and size of the pie chart so that it is perfectly centered inside your rectangular chart background, taking up as much of the space as possible.  You do this by dragging the corners of the pie chart to the edge of the background.  Once done, you can adjust the size as you wish, just keep the aspect ratio squared i.e. length and width the same to ensure you have a perfect circle.

Step 2 – create 2 more pie charts

Copy and paste the pie chart you have just created and position the new chart above it.  Repeat so that you have three pie charts on top of each other.  You can use the align commands on the Drawing tools tab to ensure that they are vertically aligned and correctly spaced.

three green lights

We need to adjust each pie so that it is looking at the data from the correct cell.  Click on the topmost pie chart.  The table should now have lines on it indicating which cell the pie chart is taking its data from.

cell reference

Drag the blue square from cell H2 to cell F2. The pie chart should update and appear blank. Repeat for the middle pie chart, dragging its blue square from cell H2 to cell G2. Your three lights should now look like this:

updated lights

We need to adjust the fill of the top and middle lights. It’s easier to do this when there is data in there, so TEMPORARILY, put a 1 in cells F2 and G2. The fill should reappear in the pie charts. Click on the top most pie chart again, press Ctrl+1 to bring up the formatting box and give it a red radial fill.

red radial fill

Then click on the middle pie chart and give it an orange radial fill. Your charts should now look like this:

three lights

Once you’ve adjusted your fill, don’t forget to put the calculations back into cells F2 and G2. The fills will disappear again, but that is normal!

Step 3 – Create the background and highlight

To give the traffic lights a real flair, I added a black rounded rectangle behind the three lights. I also added 2 circles as shown below:

additional shapes

The first circle is black with 40% transparency.  The second is red with a red 12pt outer glow. When put together they look like this:

background red

I copy / pasted the 2 circles twice and adjusted the glowing circle to be orange and green for the other two “lights”.

traffic light background

All that’s left is to position the three pie charts on top of the background and you have your first traffic light.

traffic light 1

You can see that the green light shines brightly, showing that for the product in row 1 we have plenty of stock.

Step 4 – Repeat for the other rows

Before doing anything, click on the home tab of the ribbon and then choose Find & Select > Select Objects.

find and select

This will let you select all of the objects that make up the traffic light by dragging your cursor over them.  Once done you can press Ctrl+C to copy the traffic light and then Ctrl+V to paste another one.  Click on the first “light” and update the cell reference as shown in step 2 above.  It should now be looking at cell F3. Repeat for the other 2 lights, for cells G3 and H3.  You should now have 2 traffic lights for the first 2 rows.

Keep copying and pasting and adjusting the cell references until you have a traffic light for every row:

traffic lights

And that’s it! You can download the worksheet here.

Hope you find it useful!

+Alesandra Blakeston