Arrow-Tip #50 Establishing Key Performance Indicators (KPIs) - in this post I will described the process we used for determining what KPIs to use for my client Gulf Coast Welding (GCW) Corp as well as some good guidelines and ideas for choosing KPIs for other organizations.

Arrow-Tip #51 Creating Metrics to Illustrate KPIs - Once we picked out GCW Corp’s KPIs, we had to determine not only the best metrics to track them, but processes for gathering that data.  In this post I’ll tell you how we picked metrics based on data that we could realistically gather and track in a controlled and consistent way.

Arrow-Tip #52 Publishing KPI Metrics in Microsoft Excel - Next we created a workbook in Excel with a summary table housing KPI metrics and a chart graphically illustrating those metrics.  For each metric we created a worksheet with backup data much of which is pulled from other spreadsheets on SharePoint.  This post will supply some quick tips for organizing your data in Excel.

How to Create a Web Page from an Excel Worksheet - In order to display our Dashboard on SharePoint, we needed to convert it to a web page.  This How to will illustrate how we did it.

How to Reference a Web Page in a SharePoint Web Part - We wanted to display our KPI dashboard on our SharePoint Home Page which required creating a web part to reference the page we created in the aforementioned How to.

Arrow-Tip #53 Housing Your SharePoint KPI Dashboard Data - I agonized for quite a while over the best way to store KPI data so that it could both easily pull data from other sources as well as push to Dashboard table and chart easily to other locations on SharePoint.  This post will share some of the solutions we found for housing referenced data on SharePoint including the ones we didn’t go with.

Arrow-Tip #54 Internal Marketing for KPI Goals - “what gets measured gets managed” - no idea who said that, but it makes a lot of sense!  Over and over again during this exercise I got the question “why are we doing this again?”  It was not only an obvious question, but a darn good one and one that best be answered if you don’t want your whole KPI project to be a complete waste of time.  In this post I’ll discuss the benefits of tracking KPIs and communicating them with your employees, partners, vendors and customers in a productive manner.

I’ve got some calls and shouts out to a few folks that I’m hoping to interview to enhance the aforementioned posts which I will announce as responses are received.  In the mean time, look for Arrow-Tip #50 next week.

Because I work with several Outlook software and coaching clients on Outlook, I usually have at least 4 active Outlook Exchange profiles at any given time.  I also have a gmail profile that I use on occassion for my non-Microsoft pals.  One of my good friends Stephan Kinsella recently blogged about his frustration with maintaining multiple calendars so here is the solution I use.

1. Whenever you create a new email/calendar profile, add a distribution list to that profile with all of your other profile email addresses - you can call this list “Calendar Profiles” (see thumbnail below).  
   

   Distribution List Window

2. When creating a new appointment in any of your calendars, invite the Calendar Profiles distribution list using the Invite Attendees functionality (see thumbnail below).
   

   Appointment with Calendar Profile Distribution List

3. Your appointment should show up on all of your calendars now at least as “tentative” until you accept it from each profile at which time it will take on the Show as label in the original appointment.  If one of your profiles is set up to synchronize with your Blackberry, it will show up there too.

Be forewarned, this is not a perfect solution.  Other Outlook profiles will handle cancellation and time/date changes pretty well, but the Google Calendar is a bit clunkier in that area.  In general though, I’ve found this solution has helped keep me out of trouble by allowing me to publish my calendar in multiple exchange profiles for multiple clients.  Give it a try and I welcome your comments.

Normally, I keep my technology until people start making fun of me (my car is 13 years old) so for me to make a change after only 18 months is a pretty big deal. But with the great discount and rebate (upgrade cost $100), the change was definitely worth it. So, if you’re about ready to jump up and down on your 8830, check out the Tour!

Feel free to comment on your Blackberry model likes and dislikes - always interesting to hear about other experiences.

OK, so a week turned into two months, but better late than never - here is my post on creating a Quotation File Information Sheet.  What - doesn’t sound very glamorous to you?  How could having key proposal information neetly organized and readily available not interest you?  Ok, how about not having to re-invent the wheel each time you put together a proposal?  Still not excited?  What about having a way to systematically evaluate which deals you tend to win and tend to be most profitable?  If that’s not enough then I give up - skip to the next post, but if you are intrigued, please read on…

The Guts - What to Include

So that you are not just putting together another beurocratic document guaranteed to make your colleagues groan, you need to give some thought to which data really needs to be included on this form.  Ask your self the following questions:

• Is this information that I’m constantly looking for?
• Is this information that I might like to compare between proposals?
• Is this information that will help remind me what this proposal included?
• Am I forgetting something that I constantly have to cross reference somewhere else?

Here are some examples of data that you might want to include on a Quote Information Sheet and why you would want to include them:

• Quote # (unique sequential number assigned to all inquiries) - good for referencing the quote when communicating with other departments in your company as well as organizing supporting documents (all should have Quote # referenced)
• Prospective Customer Reference # (unique number assigned by your prospective customer to the inquiry) - good for referencing the quote in communications with your prospective customer, it is always a great idea to include this number in the subject of all emails to the prospective customer regarding the quote.
• Date of Inquiry (date prospective client asked you for a proposal/quote) - good point of reference and helps you calculated sales cycle or time to close the sale
• Prospective Customer Name & Primary Contact name (company name for prospective customer and full name of main contact at customer company) - this one is pretty self explanatory, but a purpose you might not think of immediately is being able to sort all inquiries by customer in a database or document search
• Due Date (date the prospective customer is requesting you have the quote back to them by) - good field to have available for sorting so that you can prioritize quoting tasks
• Projected Close Date (date the quoted project will likely be awarded by) - helps with forecasting revenue by close month
• Projected Revenue (the price quoted to the prospective customer in your quote or order of magnitude estimate price if in early stage of quoting process) - helps with revenue forecasting
• Change Request Table (table documenting additions/deletions/revisions requested by the prospective customer to the quotation or the project once it is awarded) - helps ensure changes to quoted price and delivery schedule are documented and communicated to the customer for approval

A quick note about the Change Request Table: I’ve seen clients lose a lot of money due to poor or non-existant change management processes.  Be sure to take into consideration all the costs of making a change once a project has already started.  It is important to be accomodating when you can, but your good clients want you to make money on their jobs.

Obviously there is other data relevant to your product or service that you will want to include.  For example, the attached Quote Information Sheet was created for a fabrication company that likes to track labor (man hours) as a separate component for each job to help generate plant production metrics used for planning and evaluation of efficiency.  Think about your company goals and what data you will need to help determine your progress toward acheiving them (Key Performance Indicators).  Pulling out data like man hours in an easy to find data field can also be very useful for comparison purposes when quoting future projects.

Using Form Fields

I’m often irritated when someone sends me a form in Word that looks great until I actually start typing in data e.g. there is an underlined space to fill in data, but when I start typing the underline trails the text (see Figure 1 below) - looks very messy.

Figure 1 Form Without Form Fields

When creating a form in Word, you should always use form fields for several reasons but especially the following three:

1. When the user types in data, your form will hold its formatting and look much neeter
2. The user can quickly and easily tab through the form from one field to the next without having to remove their hands from the keyboard
3. Data that is captured in form fields can easily be used to populate data bases or other documents using simple scripts and programming

In Word 2003 inserting form fields was relatively easy - there is a nice little form tool bar that allows the user to insert text, drop down and check box fields into your document.  In Word 2007, you have a to dig a little, but you can make the old form fields available on the Developer ribbon (see How to Make Form Fiels Available on Microsoft Word 2007 Ribbon).  Once those fields are available all you have to do is position your cursor where you would like to have the user type data and then click on the text icon on the form fields ribbon (see Figure 2 thumbnail below with Word 2007 example).

Figure 2 Insert Text Field

In the Figure 2 above I use form fields off of the Legacy Forms sub menu, but if you are going to be generating a lot of forms using Word 2007, you might want to take the time to learn more about the new Word 2007 form fields on Microsoft’s Office Online site.  Though for basic forms, I think you’ll find the legacy form fields are more than adequate and easier to use.  A simple text field is inserted in the figure above and this is the form field you’ll use most often for free form data in put.  I’ve also used check box fields for yes/no data input and drop-down fields for multiple choice type questions where you want to standardize answers.  In the attached example, “sales cycle” data is input into a drop-down field which my client uses to group data in a forecasting report and which is also used to calculate a formula field (see next section below).  If the sales cycle data is not input in the correct format (very likely if a free form text field is used instead of a drop-down field), the formula field calculation would produce an error.

Notice in Figure 2 above that I’ve underlined the form field - unlike in Figure 1, this underline will appear under the text a user inputs when they tab to the field as opposed to out in front of it.  I also highly recommend using tables for forms and putting each text field in its own cell so that the overall format of the document is maintained and text is not pushed out by preset tabs when long strings are entered in to preceeding text fields.  Remember, you can erase the border lines between cells, so data can go into different cells without having them appear in different blocks when printed.  For design purposes, gridlines for each cell can be hidden or made visible (see Figure 3 thumbnail below).

Figure 3 Gridlines Visible/Invisible

Calculated Form Fields

One additional benefit of using form fields is that you can use formulas to perform calculations based on data input into other fields.  In the attached example, ”Current Pipeline Value” is calculated by multiplying the  % value listed as part of the drop-down field text for “Sales Cycle” by the dollar value listed in the “Projected Revenue” field which has been formatted as currency.  Step by step instructions for created this field are below:

1. Name all fields that will be used in calculation:
   1. Click the ”Properties” button on the Controls portion of the Developer ribbon - the Text Form Fields Options window will open
   2. Type a name with no spaces in the “Bookmark” field (see Figure 4 thumbnail)
   3. Click the OK button.
   4. Note if you are using a drop-down field as in the attached example, you’ll want to enter the field name in the “Tag” field instead of “Bookmark” field. (see Figure 5 thumbnail)
2. Insert a text field from the Legacy Form sub menu where you want to display your calculated value
3. Right click on the text field in your document and select Properties from the pop up menu (or click the “Properties” button on the Controls ribbon) - the Text Form Field Options window will open
4. Select “Calculation” in the Type field (see Figure 6 thumbnail)
5. Enter your equation in the Expression field (see Figure 6 thumbnail) - this case the equation is “=SalesCycle*ProjectedRevenue”
6. I’ve added custom formatting to assure the value is shown in $ using the Number format field (see Figure 6 thumbnail)
7. Give your text field a name in the Bookmark field
8. Click the OK button on the Text Form Field Options window

Figure 4 Text Form Field Options Window

Figure 5 Content Control Properties Window

Figure 6 Text Form Field Options Window with Expression

Calculated fields could also be used to add sub estimates like a material estimate and a labor estimate or they can be used to combine text as well such as auto-assigning a name to your quote if you have a naming convention that your company uses for consistancy.  For example, if your quote file naming convention is [Quote #] [Prospective Customer Name]  then your Expression for the calculated Quote File Name field would be:

QuoteNum & ” ” & CustName

where QuoteNum and CustName are the Bookmarks you assigned respectively to the Quote # and Prospective Customer Name fields respectively.

Protecting Your Form

Users will not be able to input data into the form until it is “protected.”  To protect a form see instructions and Figure 7 thumbnail below:

1. Click the Protect Document button on the Protect portion of the Developer tab and the Restrict Formatting and Editing pane will appear on the right side of your Microsoft Word window
2. Make sure that “Allow only this type of editing in the document” check box is checked and that “Filling in forms” is selected in the drop-down field below it
3. Click the “Yes, Start Enforcing Protection” button on the Restrict Formatting and Editing pane and the “Start Enforcing Protection” window will open
4. If you would like enter a password (highly recommended) twice and then click the OK button

Figure 7 Protect Document Pane

To download the sample form used in this post click Quotation File Information Sheet - password for the form is: password 

Finally, please leave comments with suggestions for fields, etc. that you use in your company forms.

In the mean time, I had an interesting meeting with a prospect for one of my clients who reinforced my insistance on posting performance metrics. The team is engergized and ready to embrace metrics so I’ve been working on some interesting ways to use SharePoint for inputing and distributing information like on-time delivery rate, days without a lost time accident and percentage of rejected products. Look for an Arrow-tip on creating company dashboards to communicate performance metrics next.

Getting Started - Data Import

I have to admit that the transition of my old mailbox information in Outlook 2003 to Outlook 2007 was completely painless.  I’m on Exchange so all I really had to do was set up my profile on the new laptop (MailStreet hosts my Exchange service and they provide excellent instructions) and the data populated into the new Outlook 2007 environment.  Everything was still there and even my custom forms seemed to port over just fine.

I’m still using my old laptop in tandem with the new one so I’m actually maintaining data for the same profile in both an Outlook 2003 and an Outlook 2007 environment - so far absolutely no issues.

Activities

Anyone who has attended one of my seminars can tell you that I’m a huge user of the Activities tab on the Outlook Contact form.  This tab allows the user to see all Outlook items including emails, appointments, journal entries, task items and other contact records that are linked to the contact - I find this feature invaluable for tracking activities and history of commuincation with a given client or prospect. 

The first omen that this feature might be problematic in Outlook 2007 was that the Contacts button and field which allows a user to link items to a contact is hidden by default in new installations of Outlook 2007.  This issue is easily resolved by checking the Show Contact Linking on all Forms box on the Contact Options window (Tools/Options/Contact Options button). 

From there its a good news/bad news scenario.  The good news is that other users with access to your mailbox folders can now see this tab on your contact forms.  This functionality was not previously available which was a problem in environments where sales assistants or managers needed to share data with members of the sales team.  The bad news is that this tab doesn’t always populate very well.  Populations of items on this tab has always been a little slow - especially for users with large item volumes (like email) in their mailboxes.  But now, my clients and I are seeing behavior where the same items will sometimes populate the Activities tab and sometimes not.  This behavior appears to be inconsistent and unpredictable - very frustrating!  If you have found a solution for this problem I implore you to post it in a comment.

Cool Stuff

I would be remiss if I didn’t mention a few cool features that haven’t set the world on fire, but are useful:

• Improved searching - Since I maintain many levels of nested folders in my Inbox, it’s nice to be able to easily search not just my Inbox, but all my subfolders at the same time.
• To Do Bar - The To Do Bar or Task bar is now available in several folders complete with a nice little calendar shwoing an appointment summary for the current day.  I had a lot of clients clamouring for that dashboard like functionality prior to the release of Outlook 2007.
• RSS Feeds - I know I said before this wasn’t a big deal since Outlook 2003 gave you the ability to set a web page as the view for any folder, but really it is pretty cool.  I like seeing new posts as part of my unread mail.  CAUTION: don’t set up too many RSS feed folders or the volume of new posts will dilute the value of your Unread Mail features.
• SharePoint Integration - I admit that I didn’t use these features as much in Outlook 2003, but I find the integration and synchronization with team discussions and Tasks is very nice (see Arrow-Tip #45, Arrow-Tip #46, and Arrow-Tip #47for a three part serious on managing organizational tasks using Outlook , Exchange and SharePoint).

All things considered I’m glad I waited for some of the more troublesome bugs to be sorted out before making the jump.  But I sure will be a happier camper when the Activities tab bug gets fixed.  As always, I’d love to hear your experiences with the transition so please leave me a comment.

SharePoint tasks can be downloaded and synchronized with Outlook tasks by selecting Connect to Outlook from the Actions menu.  SharePoint will create a folder in your Outlook Folder list called SharePoint Lists and then a subfolder called Team Web Site - Tasks that will house SharePoint tasks. 

Although the SharePoint tasks do not show up in your default Outlook Tasks folder, you can see them in the To Do List views in your Outlook Tasks area if you are using Outlook 2007 (see thumbnail to the left).  Items get placed in the To Do List view if they are flagged which SharePoint automatically does for you when it downloads tasks to the Team Web Site Tasks folder. 

You can edit tasks either from SharePoint or Outlook and the edits will be synchronized in both places (you may need to refresh your browser in Share Point or click Send/Receive in Outlook for changes to show up).

I’ve been pretty happy with this arrangement and most of the team seems to have adjusted fairly well.  Probably my only issue with this methodoloy is that tasks from SharePoint do not sync on my Blackberry since it only syncs with my Outlook Tasks folder.  If anyone has a resolution for that issue, please leave me a comment.  This issue isn’t a huge deal for me though as I rarely manage tasks from my Blackberry.