Weekly tricks of the trade: How to do a run chart in Excel to See Instantly and Understand Next Steps
Start with a table of data
In this case I downloaded the Department Store Inventory Price Index from the Bureau of Labor Statistics from 2003 to 2013. Basically it is a formula that stores can use to put a value to their inventory for tax purposes. If you study it I am sure you can find possible connections, but without context there is a 50/50 chance that those connections will not be real. Get over yourself, it is just a model: what matters is what it is modeling, don't you think?
GET RID OF WHAT YOU DON'T NEED
To be able to look at something and understand it, it is important to get rid of anything that is unnecessary that may mislead you. For instance, the Annual Average column, since inventories vary seasonally.
CLEAR THE FIRST COLUMN HEADING
Or spend hours trying to get the chart to work right, trust me (school of hard knocks.) When the first cell of the first column is empty you can choose all columns and rows of a table and make a chart with the right labels, otherwise you have to go back and type them in by hand.
HIGHLIGHT TABLE DATA
CHOOSE LINE CHART
Resist the deeply ingrained but completely misleading impulse to mark each data point with a star, or a box, not even a triangle: what matters is the flow of the line and marking points invites us to give them importance they don't have, 98% of the time. When they do matter, you won't need a mark, it will jump out at you.
PRESTO, BUT A BIT TOO MUCH
Like you would need an hour to decipher what it is trying to tell you, and could make mistakes, as too much information tends to make us do. Can't we simplify even further, to what matters, what is needed, what can make a difference?
FIRST LETS FORMAT THE CHART TO MAKE IT MORE READABLE
In the upper right you will see a green highlighted format and a yellow box with Layout 5 written in it. Depending on the version of Excel this may differ, but the format you want is with the line chart (run chart) and the table of data together.a
TIME TO RESIZE
On the corners and in the middle of each frame there are four dots, hover over them until you see a line with an arrow head on each side. Click and pull with your mouse to resize and make it more readable.
NOW LETS PUT A TITLE ON THE CHART...
Click on the title and when you see the "I" bar, select all and type in the title, but lets face it, this is just too much visual information and data in a table to absorb quickly, and really, at this point, we care about what is happening in the present moment, or as close to it as possible, in order to get the right context from which to see the real meaning, so lets select just 2012 and 2013, the latest years. What happened in 2003 is interesting, but won't do us much good today.
SELECT THE ROWS OF ALL BUT RECENT
Then right click or control click on mac and choose hide, or use the menus. The chart will adjust itself as long as your are using the original table data and not the "for show" table in the chart window.
YOU HAVE ARRIVED SAFELY
Just enough data to understand visually. Pay no attention to individual data points, look at the shape of the line. Work happens in sequence in time, and time never stands still. Seems wave like? That is the mark of a process that is predictable.
- What else do you see?
- Could you see it as easily in the table of data?
- Isn't it obvious?
NOW IF WE LOOK AT ALL THE YEARS WE CAN SEE SOMETHING
Is the shape of all years pretty much the same? When are the low points and high points? What is going on in 2011, the light green line that breaks away from the lower pack? Why is year end inventory so much higher in 2011 to 13 than in 2010 earlier?
What does this mean? It would be easy to take a guess, but the truth is we don't know. But we sure have an idea of what to look at! Did inflation have an effect? A change in buying patterns? What are you thinking? Could we see that in a table alone. I doubt it!
What other real things can you count and put in a run chart. Sales dollars are real, for instance, and can be counted Share with us in the comments below. AND SEND REAL DATA in return I'll look at it, preserving your privacy by sharing it in a way no one will know where it came from, especially your competitors!
ALWAYS PLAY THE DEVILS ADVOCATE... SAY THINGS LIKE IT APPEARS
Until you test a theory it is just an opinion. Now is the time to be critical. Starting with the data: a price index is a calculation, not a real thing. It is a twice removed data source. First, it is a computed value based on wholesale price. What would be real was the actual quantity: the price used to create the valuation are calculations: DANGER! Secondly, knowing human nature, companies will tend to overvalue things they can take off their taxes.
TAKE THE COURSE
LEARN HOW TO USE RUN CHARTS FOR YOUR IMPORTANT FINANCIAL DATA FOR ONLY $10
