In writing this material, we researched one heck of a lot of organizations' PowerPoint and Excel graphical templates for compliance metrics. They were all, in a word, awful. So we dug in our heels and began researching graphical representation formats. After countless books and articles and a few phone calls to Edward Tufte and Gene Zalazny (two of the greatest minds regarding the graphical representation of information), we found the folks at PowerFrameworks. PowerFrameworks, headed by Kathy Villella, has provided the graphical formats that the Unified Compliance Framework uses to represent the compliance metrics that we have mapped.

After examining each of the UCF's currently mapped metrics, Kathy's team has come up with a set of PowerPoint presentations that include the four types of charts mentioned above, placed into a simple to use PowerPoint template that is integrated with Excel to produce the complex stacked bar and waterfall charts we just mentioned. You'll find the PowerPoint templates file in your Resources folder with the filename Compliance pack.pot.

1. The metric's Control ID that identifies it as unique among all others.

2. The metric's title.

3. The metric's calculation.

4. The source of information from which the metric was drawn.

5. The target goal for which the organization is shooting.

6. The current difference of measurement showing progress or regression.

7. If any huge changes occur (such as shown in our example), notes that help the reader understand such a large change.

One of the 100+ custom designed PowerPoint graphics we supply

We've found that if you display at least this much information in each of your metrics presentations, that should be enough to carry your message forward even if you are not present to interpret the chart for a potentially future reader.

Installing the PowerFrameworks add-ins

Because Excel cannot natively create or edit either stacked or waterfall charts, you will need to install a couple of files that PowerFrameworks has created for this purpose. These files are found within your To Be Installed directory that we shipped to you.

Prepping your system

In order to install these files, you will have to ensure that you can see hidden folders within your computer.

1. With the To Be Installed folder open, select the Tools menu followed by the Folder Options menu as shown below.

Tools > Folder Options...

2. With the Folder Options window open, ensure that the Hidden files and folders directory's radio button is set to Show hidden files and folders as demonstrated below. Then click the OK button to set this view.

View set to show hidden files and folders

Placing the add-ins into your system's Microsoft Add-Ins folder

Within your computer is a hidden folder called Add-Ins. To get there, take the following path:

• • Open the My Computer icon.

  • Open your C: disk icon.

  • Open Documents and Settings folder.

  • Open either the Administrator or whatever your local user folder is.

  • Open the Application Data folder.

  • Open the Microsoft folder.

  • Open the Add-Ins folder, as this is where you are going to place the files.

  • Drag the four files mentioned earlier (and shown below) into this folder.

These three files go into the Add-Ins folder within the Microsoft folder

Setting the security level for Excel

In order for the macros to work within Excel, you need to ensure that your security levels are adjusted correctly.

1. Click on Tools on the Menu bar and select Macro > Security...

Selecting Macro Security

2. Click on the Security Level tab, and set the security level to High or Medium.

Setting security to Medium

3. Click the OK button.

Setting PowerPoint's security and installing the add in

Now you are ready to finish off the installation process by setting PowerPoint's security and then adding the PowerFrameworks menu.

1. Click on Tools on the menu bar and select Macro > Security...

Selecting Macro Security

2. Click on the Security Level tab, set the security level to High or Medium.

Setting security to Medium

3. Click the OK button.

4. Click on Tools in the menu bar and select Add-Ins...

5. When the Add-Ins window appears, click the Add New... button.

6. The Add-Ins folder will be selected. Navigate to the PowerFrameworks file, select it, and click the OK button.

Selecting the PowerFrameworks add in

7. The menu will now be added to PowerPoint.

After the complete installation, a new toolbar will appear on your PowerPoint menu. There will be four choices within the toolbar: Waterfall Chart, Stacked Chart, Edit, and About.

Using the provided compliance template pack

We worked with the wonderful folks over at PowerFrameworks to create a series of PowerPoint slides that map exactly with the metrics guides found within the authority documents mapped by the UCF. As with the standards report templates, the presentation deck is well over 100 pages in depth.

This deck ships to you as a PowerPoint template file, with two additional files that must be loaded into your Add-Ins directory because Excel cannot natively create or edit either stacked or waterfall charts. We are going to skip the installation and in-depth usage instructions at this point (they are included in the appendix of this booklet) and just show you how to edit the graphics provided.

Sample pages from the PowerFrameworks deck

Editing the existing charts

The charts in the chart pack have already been predefined according to the metric reporting standards found within the UCF. There are certain items we think that you shouldn't change:

1. chart titles

2. IDs, and

3. authority sources,

unless you no longer want them associated with the titles and IDs followed in the UCF. However, other than the chart itself which you'll have to change, you might or might not want to change other items as appropriate.

4. The target might be changed to meet your organization's expectations.

5. Some of the introductory or explanatory comments are left blank (as with this sample), and some are filled out. Even if they are filled out, you might want to tailor them to your situation.

6. Many times the PowerFrameworks folks have provided callouts for certain areas. These are just for informational purposes and should either be changed or discarded as the case may be.

7. When appropriate, you will want to change the charting notes that the PowerFrameworks team added. As with item 6, these are put on the sample charts for informational purposes only and must be edited or deleted accordingly.

Sample chart from the chart pack

All of the above can be edited directly on each of the slides. However, when editing either the waterfall or stacked charts, you'll need to make use of the PowerFameworks menu, as PowerPoint cannot natively work with these types of charts. In order to edit these types of charts, follow the directions below.

After you've installed the appropriate additional files, a new menu within PowerPoint will appear, entitled PowerFrameworks. This is the menu you'll be using to edit the waterfall and stacked bar/column charts. Simple bar and pie charts don't need the PowerFrameworks custom coding as they can be edited directly.

PowerFrameworks menu

To edit the data in any of the waterfall or stacked bar/column charts, there are only a couple of steps. However, they must be followed in order.

1. Select the chart (not the whole page, just the chart) that you want to edit. You'll know that it is selected because you'll see little round white balls surrounding the chart as shown below.

The chart has been selected

2. Click the Edit button (ignore both the Waterfall and Stacked buttons, even if that is the type of chart you are editing - those are for adding new charts) within the PowerFrameworks menu. This will launch Excel and will bring up a window with the original values in it.

The Excel window

3. Edit the original values. Add additional data by adding more rows in the worksheet or inserting rows/cells in the worksheet. Remove unwanted data by highlighting the data and labels you don't need by right-clicking and selecting "Clear Contents" [important: do not delete either cells or rows, as this will disrupt the macro being used].

4. When your data has been entered, click on the ToPPT button within the spreadsheet. This will cause the macro to refresh the charts and take you to the second tab of the spreadsheet. You will see your chart in both bar and column format, as well as have a small dialog displayed on screen. You have a choice as to which of the two chart formats you wish to use. The column format is the default.

Bar & Column choice dialog

5. Clicking the Go button will place the updated material back into the slide.

Note for working with Waterfall Charts

The waterfall chart will subtotal and total the preceding segment values. The words "Subtotal" and "Total" prompt the functions. Don't use any other label text when you want to subtotal or total your segment values.

Changing the template files for colors, fonts, and chart sizes

If you prefer to use your own company's template and your own color scheme, you will need to set the colors in the Excel template files just as you would with any other customized template. To customize the templates, you need to open them from within Excel using File > Open. Do not open the file by double-clicking in Windows Explorer. Once you set the Excel file with your colors, all the charts you import will be consistently colored.

Finding the template files to edit

In order to edit the templates, you'll have to navigate to them within Excel. Follow these steps to find the files to edit:

• • Select File from the menu bar and click Open...

  • Navigate to your C: disk icon.

  • Open the Documents and Settings folder.

  • Open either the Administrator or whatever your local user folder is.

  • Open the Application Data folder.

  • Open the Microsoft folder.

  • Open the Add-Ins folder.

  • Open the Excel file (either the Stacked or the Waterfall file).

Resizing the charts

There are two ways to resize the charts. If you want to resize individual charts after you bring them into PowerPoint, follow these steps:

1. Click on the chart you wish to size and click Edit in the PowerFrameworks toolbar.

2. The worksheet with data will appear.

3. Click the Refresh button.

4. Click on the chart that you wish to resize. Make sure that you've selected the Chart Area or you will not be resizing the whole chart. If it says Plot Area, click again on the border of the chart until you see Chart Area.

5. Resize the chart by moving one of the handles to the desired size.

6. On your keyboard, simultaneously press Ctrl-shift-p and the newly resized chart will re-import into PowerPoint.

If you want to resize the charts permanently, change the sizing in the template and save the changes. All new charts will import into PowerPoint at the new size.

Changing the font information

You can also change the font faces and sizes. Change the fonts in the Excel charts, not in the Excel worksheets.

1. Click on the numeric value sets in the segments and change the font attributes as you would in any Excel chart.

2. Save your changes.

All subsequent charts will reflect the new font and size.

Changing the color information

If you want to format the charts using your company's specialized palette of colors, follow these instructions:

1. Click on the Tools tab on your menu bar.

2. Select Options...

3. Click on the Color tab.

The Color tab

4. Click on one of the color squares in the Chart Fills row.

5. Click the Modify... button.

6. Click on the Custom tab.

The Custom tab

7. Type in the RGB formula for the color you wish to use and click OK.

8. Repeat steps 4 through 7 until all of your organization's custom colors have been assigned to the color squares in the Chart Fills row, and then click OK on the Color tab.

9. Save the file and re-launch Excel, and you are ready to use the PowerFrameworks templates with your custom colors.

Creating new metrics presentations with waterfall or stacked charts

There's nothing that says you must use the charts that we've created. Or the templates we've created. You can, and just might, want to add either waterfall or stacked charts that you've created on your own, and in your own templates. To insert either a waterfall or stacked segment chart, follow these instructions:

1. Click on either button on the PowerFrameworks menu. A worksheet will appear, into which you can enter your data.

The PowerFrameworks worksheet

2. Add additional data by adding more rows in the worksheet or inserting rows or cells in the worksheet.

3. Remove unwanted data by highlighting the data and labels you don't need by right-clicking and selecting "Clear Contents". This is important: do not delete either cells or rows in the spreadsheet as this will corrupt the macro. You will achieve the same effect by merely clearing the contents.

4. When your data has been entered, click the ToPPT button within the spreadsheet. This will cause the macro to refresh the charts and take you to the second tab of the spreadsheet. You will see your chart in both bar and column format, as well as have a small dialog displayed on screen. You have a choice as to which of the two chart formats you wish to use. The column format is the default.

Bar & Column choice dialog

5. Clicking the Go button will place the material onto the center of the slide.

The newly added waterfall chart