Overview

I offer different approaches for each proficiency level in Excel because the productivity gains and thus teaching needs for each level are different. Following is a summary of where people are often stalled at each level and how I help increase profitability in different ways.

* These are generalizations. Each group has its particular needs so I do an assesment, interviewing both supervisors/managers and participants, to find what the most productive goals should be for each session.

• Novice and Beginner

  I use the term "novice" for someone who has never touched an Excel spreadsheet and "beginner" for someone who has used Excel but wants to be properly trained. Confidence is the watchword with these trainees and I start people feeling confident in limited areas of Excel.

  Novices and beginners are often clerical personnel. Their skills can be put to far better use in an office. They can take on much more of a report load, relieving higher paid personnel of such tasks.

  Management and professionals staff ...well, usually shouldn't be beginners but often are! Savings here can be substantial and just doesn't need to take months.

  Get all of your beginners and novices using Excel to manage lists, to improve the appearance of existing reports and even become resident experts in formatting.

• Intermediates

  may use Excel extensively and are capable of teaching themselves everything they need. The problem is that most can't afford the time to explore Excels's apparently endless functions for what they really need.

  I'll look at what your office does and set your intermediate users on a productive path through Excel's wilderness. Expect a major boost in the speed and capability of your users.

• Testing and Spreadsheet Integrity

  Studies consistently show that over 80% of spreadsheets may contain errors; unfortunately these errors are not confined to beginners' spreadsheets and are not minor problems.

  As students take more advanced courses I devote more attention to careful testing of spreadsheets and spreadsheet protection.

• Advanced users

  Advanced users tend to produce larger and more mission critical spreadsheets than less skilled practitioners. There are three major challenges:

  • There are a few advanced techniques—often using undocumented Excel features—or "tricks" to force Excel to do things that are natural for business but apparently unnatural for Excel
  • Spreadsheet integrity: creating spreadsheets that are rigidly constructed and self-checking to protect against errors.
  • Users are lured into writing macros because of the enormous potential productivity gains (and some protection against entry errors) that automation can provide. But writing macros is an entirely different skill from any other; it may take several years of writing macros before the writer gains sufficient experience to write efficient, well-documented code that has no major bugs. Only a small percentage of those who start down this road actually achieved this key goals. The rest are writing the cumbersome, error-prone spreadsheets that gave rise to the stories above.

  I can help assess whether something is worth automating or not and teach users to write well-documented, well-constructed, self-checking code. In a few instances it may be a better strategy to undertake a consulting project where I write the code and train users to maintain it (an easier task)

Novices

Learn how to do basic exercises in Excel:

• Open a workbook, create a nicely formatted table and then print and save the workbook
• Basic to intermediate formatting, working on speed and fluency
• Use these methods to create simple lists and tables
• This course will provide some calculations, but calculation methods will be kept to simple addition mathematics, with no percentages.

Beginners

Beginners are using Excel but often doing so inefficiently and with no great confidence. Our goal is to increase speed for simple operations by a factor of 2 or perhaps 3, and to make users a great deal more capable and confident.

• This class covers all the "novice" material but takes each component a little further
• Conditional formattting
• Date formatting and some calculation
• Printing

Intermediates

Intermediates usually include trainee accountants, analysts and other junior staff who assist with the preparation and presentation of data. It is essential that these people execute their regular tasks with high efficiency and know how to solve more advanced problems of presentation and organization.

There is a variety of options for courses and a free consultation will help assess where to start. Usually 3 of the following topics will be covered and will produce significant gains.

Intermediate skills

1. Basic Skills check: increase speed and scope of data formatting and presentation
2. Check good practise skills, such as naming conventions, range name usage
3. Conditional statements (IF). Handling complex conditions and column sums.
4. Table reference and lookups
5. Intermediate references (external spreadsheets: pros and cons)
6. Beyond Beginner Charting
7. Date manipulation and calculation
8. Text manipulation functions
9. Protection
10. Spreadsheet integrity: testing and checking

Reluctantly

I do not share Microsoft's enthusiasm for PivotTables. While this feature offers brilliant power if you can tap it at its simplest level, its utility almost completely disappears if you want variations. There are a few weak channels of development possible. Ditto scenarios

Advanced

Generally advanced users are confident enough to be able to learn new techniques on their own but there are few areas where training can cut out the large amount of time and experimentation sometimes needed, and that is often a barrier to progress.

• Spreadsheet testing methods; data integrity
• Advanced Referencing methods
• Simple automation with macros
• Using controls
• How and when to use arrays
• Charting
• Forecasting: using the regression functions

There are some areas where a professional, familiar with database and web technologies can help even the most advanced user.

Consulting and Private Tutoring

Consulting

Consulting is not generally a preferred option since it always better to have in-house people create and maintain in-house products. However, there are occasions when outside expertise can complement internal skills:

• For overhaul or perhaps just a second opinion on reporting systems
• For assessment of the potential for automation
• To write specific advanced spreadsheets requiring macros or advanced methods

Tutoring

I provide an at-your-desk-tutoring service that can't be beat. For as little as $25 an hour, staff can phone me and I can resolve the problem over the phone by using desk-to-desk software that enables me to see your employee's computer screen.

Projects I've completed with Excel. This is a small sample of both projects and techniques.

• Billing System

A single master workbook creates invoice workbooks that can be sent to clients, itemizing time charges and current as well as past invoices. The master workbook tracks payments; reconciles bank deposits; analyzes revenue streams

Techniques:Advanced use of automation (macros) to create output spreadsheets; intermediate level calculations, using different worksheets and some database functions.

• TOWES

TOWES (a Test Of Workplace Essential Skills) is a workplace literacy test now administered by Bow Valley College in Alberta. All the answers for all the tests submitted for hundreds of thousands of test takers are stored in a database originally designed by me. Written primarily in Access it used Excel spreadsheets to import data and output statistical analysis (Pearson and other) of data. Subsequently used Excel for stripping data from complex statistical package output and analyzing the results.

Techniques:Advanced statistical functions and operations in Excel; import to Access from Excel and Export from Access to Excel using Visual Basic (macros).

• Treeplanting Project Financial Analysis

Timesheet field data collected as Excel worksheets and massaged for input into AccPac

Data exported directly from AccPac into project analysis spreadsheets

Techniques: Advanced layout and Excel methods--including array calculations; ODBC methods for moving data between Excel, AccPac and Access.

• Owner's Financial Reporting

  Replaced an unreadable (inch thick) quarterly financial report with a printout from a 2 page spreadsheet that immediately highlighted (using key financial ratios) strong and weak areas of company performance

  Techniques: Intermediate level formatting and reporting; Intermediate charting methods; Advanced layout and data organization; Advanced use of macros to automate consolidation of related spreadsheets; Advanced use of controls (form) to guide process

Biography

John:

My engineering background was a base for years of writing computer programs specifically to analyze data and provide financial reporting for a variety of companies. It is the combination of strengths in financial analysis, in the use of spreadsheets and in writing computer programs to serve data to and from Excel, that provides useful expertise centred on Excel. I also have many years experience as a teacher, facilitator and coach.

• Graduated from University College London, one of the UK's top 3 engineering courses, with a B.Sc. Chemical Engineering;
• Spent early years designing a variety of computer financial reporting systems, from complex (large company) payrolls (GE) to a several province-wide data collection systems
• Developed data collection system for credit unions in BC; this system was the forerunner of the system currently in place at FICOM and used by Stabilization Central.
• Information Technology Manager for the Financial Institutions Commission (FICOM)
• Taught computer courses (Capilano, Kwantlen, Douglas colleges...now universities)
• Facilitator: Strategic and other Planning
• Management consulting: my first tasks was often to improve the reports that management was getting from its accounting and other data.

Services and Rates

• General Rate: $50 per "working hour" (= approx $100 per classroom hour)
• "Working hour" is actual hours spent on a project/task and is the rate charged for consulting and other work requiring no preparation;
• Workshops require preparation hours and so a "classroom hour" is quoted at about 2 x working hour rates

Standard packages

4 Hour Excel Sessions for up to 4 people $500

6 hour Excel Sessions for up to 4 people $600

Each module provides 2-3 class sessions of 2 hours each (4-6 hours total class time) and is conducted at your site. I take a maximum of 4 people to a module. Included in the cost is 1-2 hours of interviews with staff; 4 hours for developing class exercises.

Your company must be willing to supply current spreadsheets that can be used as examples. Absolute confidentiality is guaranteed and no such materials methods or even general information will be revealed to others. I am willing to sign any confidentiality agreements you want me to.

I provide a money-back guarantee of satisfaction under the above conditions.

There is flexibility in these arrangements but the guarantee is not offered with changes that reduce the effectiveness of our usual approach.

• Sessions can be held off-site but the rental of an alternate site will be added to the price. It also adds considerably to the time that staff are away from their desks;
• If you have major concerns about confidentiality and are unwilling to use your own spreadsheets in class examples, I will work from from standard exercises.

Consulting: Free preliminary asssessment (about 1 hour) $50/hr for hourly consultation; $120/half day; $1,000 per week

Tutoring

$25 / hour online with retainer of $500; 15 minute min per call
$40 / hour online billed monthly
$50 / hour on-site; minimum 3 hours; billed monthly

Tutoring = Online Excel Help Desk

What about a service where, if your employees get stuck while trying to solve a problem with Excel, expert help is only a phone call away?

Two-way desktop-to-desktop software allows me to see your employee's screen and for him or her to see mine if I want to demonstrate a solution while we're talking on the phone.

This is a highly effective way of getting employees to succeed with new skills learned in a workshop and does not seem to be something that employees over-use. Your staff don't have to get up from their desks and the rate reflects the fact that I don't have to get up from mine--the wonders of modern technology!

To earn the best rate of $25 per hour, pay a retainer of $500 and allow employees to phone as they need to (for a total of 20 hours). A monthly statement is provided and management will be notified if there appears better alternatives for a given employee.

$25/hr phone (min 30 mins) by retainer; $40 billed by month (not retainer). $50/hr at site (min 3hrs)

Frequently Asked Questions

• Why are your rates so low?

I'm almost retired and do this for fun. I don't need the money but I do like work that I enjoy and is satisfying

• This all seems kind of laid-back. Is this a good product?

If it's not, your manager isn't going to be happy and I'm not going get paid (see "Guarantee").

• The sessions sound kind of scary. Can I keep up?

If anyone feels left behind they're not going to be happy and the billpayer is not going to be happy, and I'm not going to get paid! But I do rely on people to tell me if they'd like me to slow down. The ideal pace is a little uncomfortable for everybody but not too uncomfortable for anybody.