Purpose
The purpose of this document is to outline the steps for data collection, aggregation and charting for the General Improvement Group using the control chart template.
Preparation
- Download the General Improvement Group Control Chart Template (3.1.24)
- Save the template to your shared drive or upload to your organization's Microsoft 365 cloud. This could be either a One drive, Teams space, or SharePoint location for your project.
NOTE: You need a version of Excel later than Excel 2019 to support the template. If you use Excel 2016 or 2019, you will encounter a XLOOKUP error.
Overview
For the General Improvement Group we have the following worksheets available within the template.
Setup Data Summary P Chart I Chart
Setup
On the Setup worksheet you will enter your organization's name in cell B3.
Data Summary
The Data Summary worksheet is where you enter your weekly aggregate data in columns A, B, and D. Column C can be used if you need to enter an additional numerator.
NOTE: The data is aggregated weekly, Sunday to Saturday. For example, if you reviewed an exam with procedure date of Friday June 3, it will be aggregated under Date 5/29.
P Chart
We will use the P chart to visualize the weekly exam review data results.
On the P Chart worksheet you will label, designate mean groupings and target values, and annotate your control chart. No exam data is entered on the P Chart worksheet.
How to add a chart title and label chart axes: Input your chart title, left axis title, horizontal axis title, and right axis title in the fields within the Chart Title and Axes section appropriately. The titles will automatically appear on the P chart.
How to group data means: Enter the applicable start and end date for your baseline data in row 8 of columns B and C, in the Mean Groupings section. When a shift in your data is confirmed, you can group a second or third mean by entering the applicable start and end date for these ranges in rows 9 and 10. Based on the start and end dates, the mean and control limits will be graphed on the P chart.
NOTE: Control limits will only appear for the ranges identified in the Mean Groupings section.
How to add target dates and value: Enter the start date and target value specified in your SMART goal on row 14 in the Smart Goal Date and Value section. The target line will be graphed on the P chart between the stated start date in cell B14 and end date stated in cell C14. You only need to enter the SMART goal end date on the I Chart tab - the same end date will then automatically populate on the P chart tab.
How to add annotations: To add annotations to your P chart, enter the date for a data point that need an annotation using the dropdown menu in column A, in the Annotations section. Enter the annotation text in column B. The annotation will appear on your P chart for the defined date.
I Chart
We will use the I chart to visualize the weekly exam review data results.
On the I Chart worksheet you will label, designate mean groupings and target values, and annotate your control chart. No exam data is entered on the I Chart worksheet.
How to add a chart title and label chart axes: Input your chart title, left axis title, horizontal axis title, and right axis title in the fields within the Chart Title and Axes section appropriately. The titles will automatically appear on the I chart.
How to group data means: Enter the applicable start and end date for your baseline data in row 8 columns B and C, in the Mean Groupings section. When a shift in your data is confirmed, you can group a second or third mean by entering the applicable start and end date for these ranges in rows 9 and 10. Based on the start and end dates, the mean and control limits will be graphed on the I chart.
NOTE: Control limits will only appear for the ranges identified in the Mean Groupings section.
How to add target dates and value: Enter the start and end date and target value specified in your SMART goal on row 14 in the Smart Goal Date and Value section. The target line will be graphed on the I chart between the stated start date in cell B14 and end date stated in cell C14. You only need to enter the SMART goal end date on the I Chart tab - the same end date will then automatically populate on the P chart tab.
How to add annotations: To add annotations to your I chart, enter the date for a data point that need an annotation using the dropdown menu in column A, in the Annotations section. Enter the annotation text in column B. The annotation will appear on your I chart for the defined date.
