W  I  L  L      B  E  A  U  C  H  E  M  I  N
1)  Let people do what adds value by automating tasks that do not.

2)  Reduce error by applying standards and procedures.

4)  Make formulas and rules reviewable and editable.

4)  Repurpose information for competitive advantage.

My background as an intelligence analyst gives me a perspective on information that few programmers have.  In one of my assignments, I would spend up to 50 hours in one week doing data entry and double-checking to have the data I needed for my intelligence analysis.  The data I was entering had to be perfect --- but those 50 hours of data entry and double-checking were 50 hours I was not spending on analysis.

That experience and others have shaped how I approach MS Office automation:  I do not simply automate a task, I make it more efficient.  I build quality assurance throughout.  I have it add value to the information it processes.

Below are a few case studies on non-sensitive projects where I did this.  Also see the case study for "CANINE" on the Creative Problem-Solving page.

For samples of my VBA, see this slideshow.

MTS Systems:  Tool for making informed pricing decisions on thousands of products

SITUATION

A team of six product managers sets prices each year for as many as 1,000 products per managerThese products range from washers and screws to large product assemblies.  Because after-market parts are a significant revenue source for the company, the pricing of even washers and screws requires attention.  

Besides the large number of prices to be set was the large amount of information needed to make informed pricing decisions on each:  cost, cost change from last year, last year's price, sales, how the part or finished product was used, and more.  

ANALYSIS

  • Many products have similar characteristics and so could be priced in a similar way.
  •  
  • The volume of information available could obscure things the product manager needs to be aware of. 
  •  
  • Recording the reason for a price was important, but unreasonable to expect someone to record 1,000 reasons by hand.
  •  
  • The volume of prices to be set meant a high probability of mistakes.

 

My solution would therefore need to address four areas:

  • Maximum flexibility in how to filter and sort things.

    Permit things to be viewed and priced as a group.

    Automatic recording of pricing reasons.

    Use of large symbols and conditional formatting to bring attention to important things.

 

SOLUTION

I created a distributed MS SQL Server/Access/VBA application called PACRT:  Price And Cost Review Tool (pronounced "pack-rat").  With PACRT, product managers can review product information as they like and to set prices as they like.  Each manager sees only the information on the products for which they are responsible.

The primary features of PACRT are:

Users create product categories and assign products.
Users can view cost, price, and sales info by category or individual product.  They can sort and filter by a large set of characteristics, including wild card searches of product descriptions.
Users can set prices for an entire category either by a fixed price or a target margin (with some customizable exception rules if desired). 
Users can still review and set prices for individual products if they wish.
A quality assurance screen lets users identify problems:  they select a problem type from a menu,  then see which of their prices have that problem.

With PACRT, a product manager can now make informed pricing decisions on 1,000+ products in a few days.  PACRT was so successful that I was asked to create something similar for the pricing of services.

Click the image below for a slide show on PACRT:

Minnesota Astronomical Society: Data quality and ease of maintenance

SITUATION

The Minnesota Astronomical Society (MAS) is a non-profit organization promoting public knowledge and enjoyment of astronomy.  MAS maintains an observatory and several observation sites around Minnesota and has a growing membership of almost 500 individuals and households.

Managing membership dues, renewal reminders, rosters of key holders to the observation sites and other membership responsibilities is done by one volunteer.  The Access database that was used to manage this was created almost 15 years earlier by a non-programmer.  Most of the database's limited features had ceased to work years ago.  As a result, the Membership Coordinator had to spend almost a day each month manipulating the data with Excel to produce letters and reports.  

ANALYSIS

  • Almost all of the Coordinator's tasks could be automated.
  •  
  • Mail merges and emailing required that data be perfect.
  •  
  • Adding functionality meant practice would be needed, especially when features were only used monthly.
  •  
  • The organization could not afford to hire a programmer whenever a change was needed, so the new app had to be easy to maintain.

My solution would therefore need to address four areas:

  • Automate tasks wherever possible.

    Let that automation be maintainable by a laymen.

    Verify data quality before mass communication.

    Let user easily practice with a copy of REAL data.

 

SOLUTION

The MAS Membership Database I created is a wholly MS Access client/server application built with MS Access 2013.  It consists of four Access files:  the user interface, the LIVE data file, a TEST data file, and an Admin (administrative settings) file.  The most prominent features are:

The SQL, parameters, exclusion lists, and other aspects of automated mail merges and mass emailing are all customizable by the user through the user interface.
The opening screen of the app is a to-do list of what needs to be done that month and what data quality issues there are.  A click of button takes the user to the members affected by the to-do list or the members with the incorrect data.  
The Admin (administrative settings) file stores information on the default location for importing and saving, the parameters for automated tasks, and more.
The user interface can switch between using LIVE and TEST data with the flick of a switch.  Visual cues and warning messages remind the user they are working with TEST or LIVE data. 


The ability to switch easily between TEST and LIVE mode lets the user practice a task and see what the counts and output are like using a copy of the latest data. Then, while the knowledge is fresh, the user can switch to LIVE mode and repeat the task for real.  

Click the image below for a slide show on this.

For samples of my VBA, see this slideshow.

 

GTT:  A generator of highly-customizable reports

SITUATION

The client's CRM application did not provide the level of customized reporting needed.  For example, it could not provide aggregate (summarized) data for customers by quarter, product class, and other categories.

ANALYSIS

  • The CRM application used a SQL Server back-end.
  •  
  • Users needed to search by numeric range, multiple criteria for a given field, date, look-up, and wild cards.
  •  
  • Simplicity in the user interface was challenged by the amount of information by which to search and display in results.
  •  
  • The information needed for searching and displaying in results was in many tables, so multi-join SQL strings had to be composed on-the-fly to produce results.

My solution would need to address three areas:

  • Displaying things on the user interface only as  needed.

    A flexible way of producing complex SQL.

    Providing summary (aggregate) data regardless of the results of the search

 

SOLUTION

I produced an MS Access/VBA application called "aSSRT": Self-Serve Reporting Tool.  Key to its operation was an administrative table that recorded how one table joined to another and what type of control should be displayed based on the criteria method selected.  For example, if the user selected "Opportunity Title (lookup)", a list box would appear on the form.  If they selected "Opportunity Title (wildcard)", a text box would appear and would accept wildcard characters.  

Shown below is a portion of this admin table:

The admin table determined the type of object to capture the user's search criteria.  For example, a drag-and-drop pop-up form would appear if the user selected a "multi" search option.
Unless needed by the user's search criteria, many controls remained hidden.  Only those that reminded the user what was available were left visible.
Aggregate data was provided through static queries that were joined to the results table according to the rules I defined in the admin table.
The user could specify which fields to have in their results and what their order and sort order should be.
The results were output to Excel to permit further manipulation, such as with pivot tables and filters.

aSSRT proved so successful that I was later contracted to add an additional type of output to meet other business needs.

Click the graphic below to see a slideshow on aSSRT:

Website designed and created by Will Beauchemin.  Graphics 2013 Iconshock by Unusual Minds and 2002 Riverdeep Interactive Learning Ltd and its licensors.  Map graphics 1988-2012 Microsoft Corporation and/or its suppliers.  Except as noted, website 2015 Will Beauchemin.