Augmenting your team's capability through spreadsheet and database development

Our spreadsheet and database Development Services take a customized approach.  Before starting development, we work to understand the requirements in the context of the existing model and workflow.  We also retain as much of the original model as possible to ease user adoption, minimize training and facilitate your on-going maintenance.

When should you consider our spreadsheet and database Development services?

• Some of your models are not reaching their potential as they require more advanced techniques than current staff are capable of and with the added benefit of learning these techniques by looking over an expert's shoulder
• Your department has models that require too much manual processing – inevitably errors creep in that require repeating the process or get uncovered after the report is produced
• You are aware there of new self-service BI tools (eg PowerPivot, PowerQuery) that would enhance your analysis, reporting and dashboarding, but need help getting started
• Flow of data through models is complex (cascading links) – controlling the data flow in a quick repeatable manner would ensure the final reports are based on the latest data
• Some of your models might be considered intellectual property, and it is desirable to protect these models so that they are not used outside the company
• You have a small number of vital spreadsheets that would benefit from an expert review to look for actual and potential errors and to get advice on how to improve the design (maintainability, robustness)
• Some models have grown in complexity over time and over multiple developers and have become cumbersome, unwieldy and fragile – staff hesitate to touch certain sections for fear it will be difficult to fix
• Certain key reports need to turn around quickly to support management decision making – reducing the processing time would give your staff more time to analyze the data and prepare thoughtful insight into what has changed and why
• Migration projects are in place, but are taking too long and you need short term enhancements
• Migration projects are being considered and you want to iron out the requirements using Excel as a prototype tool

Ryan Excel Consulting's approach

We strive to take a balanced approach to model and macro design, recognizing that others will likely be performing the maintenance.  As such, we do not use complicated techniques unless warranted from a performance, clarity or redundancy avoidance perspective

Services

Optimize and Enhance Existing Applications

• Make reports more readable and informative
• Redesign models so that they are easier to use, have less risk of breaking when modified, are easier to maintain and have a significantly lower probability of errors
• Implement structural changes to the model to alleviate processing bottlenecks (eg. reducing redundant parts of the model, moving data to a database)

Task Automation

• Speed up processing and reduce potential for errors by automating manual processing steps using PowerQuery and/or Macros (VBA – Visual Basic for Applications)
• Improve control over model build cycles when models have interdependencies, by using automation and data management techniques  – where possible, copy data between workbooks (via macros), rather than use cross-links to improve control and build an audit trail

Secure Intellectual Property

• Move critical functionality outside the model into secure libraries - Excel's password protection is weak and can be easily cracked
• Render the model unusable when someone attempts to run it outside your work environment

Data Manipulation and Extraction

• Extract data directly from the controlled source through database and query development
• Move data out of an Excel spreadsheet table and into a database to improve control over the data and allow many users access without having uncontrolled versions spread throughout the organization

New Model Design and Development

• Build models for feasibility studies, business plans, valuations and other specific decision requirements
• Redesign existing models which have outlived their useful life and are considered too fragile, unreliable, and error ridden
• Prototype new software and IT systems using Excel to help assess the complications, provide end users with an idea of the look, feel and functionality of the system, and get feedback ahead of formal development and software purchasing

Spreadsheet Review

• Financial models are reviewed for robustness, maintainability, errors, unnecessary complexity and size, and repetitive manual processing
• The deliverable is a report detailing the key issues and recommendations

Spreadsheet Maintenance

• Maintain existing models – fix problems and add enhancements as necessary

Spreadsheet Risk Management

• Assess your departments models and highlight those with the highest risk of error
• Help mitigate the risk through spreadsheet risk management techniques and best practices

Summary

• We offer a wide range of development services to help augment your team’s capabilities
• Not only can we help address any immediate resource issues, our models or model enhancements will be useful as examples, templates and guides for your existing staff
• We work with Excel and database tools (eg Access, SQL) and other office products (eg. Word, PowerPoint, Outlook, Visio)
• In many instances migrating applications away from Excel is not the best solution – as these new tools do not provide sufficient flexibility to meet your work flow and deliverable needs – in these situations, getting more out of Excel can be a better investment of time, resources and money
• Task automation has the immediate benefit of reducing processing time for your staff (and if these tasks are repeated weekly or monthly, it can quickly add up) – in many cases, though, the driving factor is reduced opportunity for errors to creep in unnoticed