Select Sample Work

While we primarily focus on the Microsoft suite of tools, we've worked with many other applications, helping users get the most out of them - from data analysis and text editors to computational computing and process modeling. If you're curious about another app, just ask.

Banking & Finance

Credit History Analysis of Bank's Customers

Problem Customer credit decisions are partially based on credit report data. The bank performs "soft data pulls" from the credit reporting agency 1-2 times each year. The bank found it difficult to sift through the current and previous report files to understand how customer data had (or had not) changed. Solution The fix for this common problem is to concentrate all of the soft pull data reports into a single database and to query the database to answer the bank's questions. When a new report is pulled, the file is placed in the same location as the others and the database is refreshed with a single click. Some questions addressed by this solution included:

  • How has customer X's credit trended
  • Which of my customer's credit has dropped more than X points recently
  • Which of my customers currently have tax liens or changed their address
  • Which of my customers have a new credit freeze?
Tools Used Power Query in Microsoft Excel

Loan Organizer: New Information from Old Data

Problem Over many years, dozens (hundreds) of weekly "loan status" report files were created and saved. Each file is a snapshot of the credit department's active loans. At the time, it was not possible to find how a loan had changed over time or to simply see an overview of oustanding loans, by type, by credit score, etc. Additionally, because of the occasional format change, there existed three different report file formats. Solution We were able to concentrate the loan data from all report files (those already archived and new ones as they are generated), while being flexible in order to handle varying number of loans in each file. All loan data - from each report file format type - was appended into a single database that could be queired, analyzed and reported on. New ability to create trends and summarize month-over-month and year-over-year changes was added. Tools Used Microsoft Excel, Power Query, Pivot Tables

Project Management & Research

Excel Email: Documentation Tracker

Problem A document tracker coordinator was tasked with sending project document-tagged emails to team members depending on their role in the project. Each one of the several hundred documents for the current project could have different recipients. Further complicating, each email is either seeking Review or Approval of the referenced document but may also be for Information only. If Approval is needed, a deadline must be included. Solution DREW (the "Document Review Email Writer") to the rescue! Emails are generated directly from Excel, based on a project role distribution table. There are adaptations for including a calculated (or custom) due date, a custom subject line and a custom body message. A second user form manages the Excel workbook settings for transparency and simple updating to the next project. Tools Used Microsoft Excel, Microsoft Outlook, VBA, Excel Tables

Improving Excel's Solver for Physical Properties Research

Problem Using Excel's built-in solver on one problem is simple. Changing the target and input ranges to solve a second problem on the same worksheet is a hassle. Changing these settings for 7 problems slows research to halt. Updating the solution to an eight (8) component, physical property model of solid-liquid equilibrium required just that. Solution We used VBA to programmatically change the inputs to (and then run) each mini problem/solver block. If a solution was not found, a second "Perturb" routine quickly modified the original guesses by a small amount so the solver could run again. The time to solve the 7 solver blocks was reduced by 95%, enabling additional, deeper research. Tools Used Microsoft Excel, VBA

Business Development

Benchmarking the Competitive Landscape with Cost Curves

Problem A cost curve is only as valuable as the underlying data is accurate and current. The owner of this data found the process to update the existing cost curve to be extremely difficult, suffering from multiple file versions, hard-coded numbers and no single version of the truth. Solution

Created a flexible "Cost Curve Generator" where each type of input data had its own table. Cell dropdown options were used to manage user selections and a formating table was used to set which data to include in the chart as well as the fill color.

Data is now simple to update (and only has a single source) and charts are easily ported for use in PowerPoint or other report.

Tools Used

Microsoft Excel, VBA, Excel Tables

Custom Tools

Remove Blank Rows in Text File

Problem The workflow of this client required repeated use of newly generated text files. The step of processing the text files would break if it encountered a blank line. Before this tool, the blank lines had to be removed manually. Solution A simple Excel add-in was created to clean the text files (processing two files simultaneously, per the user's request) by removing all blank lines. Additional features of remembering the last file (because newly generated files often have the same name) and the ability to open in Excel or Notepad (easy confirmation the cleaning worked), makes this new workflow a breeze. Tools Used Microsoft Excel, VBA