Someone from this forum described reporting CARs (tracking metrics) by using a High-Low-Close chart in excel. For completed CARs they suggested using the days from initiation to completion and for open cars number of days form initiation to the reporting month. This would show the monthly 'cycle' of the CARs. Does anyone know how to set up the data in excel to be able to track this and would it work if your tracking multiple plants? Thanks!!
To describe word for word how to set up the data would take a bit, so I’ve attached a file showing how to set it up. If you are familiar with Excel, I believe you will be able to see how it all goes together. You can use any number of columns you like as long as all the necessary dates are in place; just change the cell references to reflect the position of the data. The information needed to create the “Stock” chart is located in columns P through S. Click on any of those cells to see how the formulas are constructed. Please notice that the formulas in Columns Q & R are array formulas (entered with Ctrl+Shift+Enter).
I don’t see the value in the “Close” number so I just copied the “Low” number to the “Close” column. Note: To make a “Stock” chart, the “Close” data must be available. Select the “Reports” worksheet to see the chart.
If you are just looking to see the impact of changes you’ve made to your system, it isn’t necessary to make a chart, however, to learn how to create the stock-type chart, go to
http://office.microsoft.com/en-us/excel/HA011179421033.aspx. I like the idea of adding the volume to the chart. It seems more complete to me.
This could be used to track multiple plants. You could make a separate database for each plant and make separate charts, or just enter all the data from all the plants into the same database.
If you have any questions, just let me know.