September 2017 NPFM Meeting: Spreadsheet Security Techniques

Scott Hagerty presented on how improve spreadsheet security using tools like Microsoft Excel and Google Sheets. He discussed and demonstrated methods to protect specific data or the entire spreadsheet. He also presented strategies for protecting the integrity of the data with conditional formatting, data validation rules, linking data from various sources, and other advanced spreadsheet tips.

Scott Hagerty is currently an IT Manager with Insource.  He is an experienced technical professional with an extensive background in software applications and hardware, workstation, server, and network-related troubleshooting.  At Insource he is responsible for managing the technical needs of his clients and drawing upon his knowledge of Windows and Mac environments to design and implement projects and solutions that best fit their specific needs.

Topics Covered During the Presentation

  • Excel vs. Google Sheets
  • Spreadsheet design
  • Spreadsheet security
  • Data integrity, protection from unintentional errors

Excel vs. Google Sheets

Excel’s advantages and features:

  • It’s the de facto standard spreadsheet application;
  • It includes powerful tools, especially in terms of macros & scripting;
  • It’s designed to function offline;
  • Excel can handle enormous datasets;
  • It’s able to link data between sheets and documents, and share selectively.


Google Sheets’ advantages and features:

  • It’s cloud-based: You don’t need to worry about backup, transporting files between computers, and keeping track of multiple versions;
  • Multiple people can collaborate on the same document simultaneously;
  • Files and data can be manipulated quickly and simply;
  • It covers most common Excel functions;
  • Like Excel, it can link data between sheets and documents, and share selectively.


Spreadsheet design recommendations

  • Show your work: That is, be explicit, label everything, and keep your steps visible.
    • Use titles — be descriptive;
    • Keep calculation components visible and label them clearly;
    • Be explicit – don’t hide too much in formulas lurking inside cells.
  • Be consistent in formatting. When possible, use templates and standardize reports.
  • Use color fill (pastels are easier on the eyes?)
    • To highlight important values or totals;
    • To communicate purpose to others.
  • Break down figures into comprehensible pieces, and use comments in nearby cells to let other users know what they’re looking at.


Spreadsheet security: Sharing and folder permissions

  • On a traditional file server, sharing is generally managed by mapping drive letters to different shares on the server.
    • Each user has drives mapped to home, shared, and public network shares;
    • Work with your IT department to tailor the system to your particular needs.
  • With cloud-based file sharing, such as Box, Dropbox, and Google Drive, there are additional considerations.
    • Internal sharing, within your organization, is managed by setting access levels on shared folders and files;
    • Sharing with external users who have their own accounts can be managed by granting specific permissions (such as download, upload, edit, view) to particular folders and files.
    • Options are fewer for sharing externally with users who lack or don’t want an account.
      • You can set the link to expire after some amount of time;
      • You can password-protect a file.
    • Caution: Read-only does not hide information within your spreadsheet. It only prevents changes from being saved.
  • Encrypted files and email
    • Email is very commonly used for external collaborators. Email is not necessarily secure.
    • Encrypted email is a more secure option. With this, the entire message is encrypted from end to end. To decrypt the message,  the receiver signs in through a secure portal. The system is usually set up by your IT department. You can create a method for signaling when a message is encrypted — for example, by putting a tag in the subject;
    • You can attach an encrypted folder, using zip or another archive format. An attached folder can hold several files, requires a password to unlock. It’s recommended that you provide the password through an entirely separate medium–that is, not email.
    • You can also lock or encrypt an Excel file. A password is required to unlock it, but the password can be “brute forced,” since there’s no mechanism to stop someone from trying an unlimited number of passwords.
  • Protecting & locking spreadsheets
    • You can protect a sheet within a workbook, or protect the entire workbook. This makes the sheet or workbook read-only. However, even if a sheet is locked, a reader is free to unhide hidden cells or ranges. When you protect a sheet, it prevents someone from saving changes to the same file.
    • You can protect a sheet with or without a password. If you’re mainly concerned about inadvertent changes, forgo the password.
    • When protected, the default is for every cell to be locked. If you want to allow editing of specific cells or ranges, you can manually reformat them as unlocked.
    • You can allow users to edit specific ranges with a password you provide, while the rest of the sheet remains locked. For someone to change the requirement on a range, they would need the password for the entire sheet.
    • You can track changes, which creates a record of changes made to the workbook, and allows a reviewer to accept or reject each change.


Data integrity: Preventing errors through spreadsheet design

  • Leave raw, source data that you want to remain unaltered in a separate, protected sheet to prevent accidental changes;
  • If data will be frequently referenced, use named ranges for transparency and easy adjustments in the future;
  • When performing calculations, use summary sheets or an additional row or column to reconcile, using control totals. For example, sum subtotals to expose any discrepancies from expected total values.
  • If preparing sheets for others to modify:
    • Enact data validations or warnings;
    • Protect or lock sheets, ranges, and cells to allow changes only where desired;
    • Hide sensitive data, either within the spreadsheet, in a separate protected sheet, or via an external link to a protected file.
  • Name commonly referenced ranges:
    • Doing so simplifies interpretation of formulas, especially when using VLOOKUP functions;
    • It also allow you to easily expand and contract ranges, for example, if you need to add additional rows or columns for time-based values.
    • When using ranges, a single change will affect all formulas referring to range.
  • Link your data:
    • Within a workbook, references are generated automatically;
    • Between workbooks (separate files), the same process applies only when the other file is already open. When linking between workbooks, make sure you know the filename and location of each;
    • Note that named ranges work both within and between workbooks;
    • Lastly, linked files are susceptible to errors or disconnections when:
      • File names are altered;
      • A file is moved to an inaccessible location;
    • When a link to another file is disconnected, breaking the link will convert it to the last known value, as a static, unlinked value.
  • Hide sensitive information:
    • When protecting a sheet, you can opt to prevent selection of locked cells within the protected sheet;
    • You can also enable the  “hidden” checkbox under the protection tab (format cells) to show the calculated value but not the formula, when a sheet is protected;
    • As mentioned, you can link to data within hidden sheets in a protected workbook;
    • You can make a summary workbook that pulls data from multiple submitted files. You can make either the source data or the summary accessible only to authorized users;
    • Quick hack for hiding data: Set the background and text colors to match each other. When you do this, the cell’s contents are visible only when selected.
  • Use data validation tools:
    • Configure drop-down lists for ranges. The choices can be manually entered, or can be set to pull from a range of cells elsewhere in the spreadsheet;
    • Data can be limited to certain calculated values, which can be set up using custom cell references;
    • If a user enters data that does not pass validation, you can configure it to stop or disallow the entry, to pop up a warning, or to pop up an informational message, which you can customize;
  • You can also apply conditional formatting to highlight values that do or don’t meet your criteria.
  • When working with multiple sheets:
    • Shift + select all tabs at the bottom (for separate sheets), and what you enter in the open sheet will be entered in the same place on all sheets.

Link to Google Form to get access to materials: Link to Insource Services

March 2017 Meeting: Information Security for Nonprofits

Information Security for Nonprofits

Dan Keleher from KPM Consulting gave a presentation on Information Security for Nonprofits. Dan Keleher is the Executive Director of KPM Consulting, LLC, the information technology consulting arm of the CPA firm, KPM. Prior to entering consulting Dan had a distinguished 18 year career with Liberty Mutual, where he was responsible for creating cohesive integration of business needs and information technology. Dan discussed some of the key ways data breaches can occur at a nonprofit organization and steps that every employee can take to help minimize exposure. He reviewed: the top risks for nonprofits after exposure to a data breach; how to identify spear phishing attempts; and tips for improved password security

What is the goal of information security? Everything that applies to business organizations also applies to an individual’s own computers and personal devices. Information security is designed to reduce or eliminate the risk of exposure from a data breaches and the ensuing damage to your company’s reputation. It is especially important to safeguard donor/customer/ and employee data: names, addresses, date of birth, phone numbers, social security numbers, bank account information, and much more. By law, companies have to report data breaches. Since January of this year, there have been over 100 data breaches in Massachusetts alone. Hackers often use “phishing” schemes where they trick workers to unknowingly reveal a password or download malicious software. The data thieves are not necessarily going after money – it is more likely that they are going after data.

How do businesses (and individuals) protect themselves? You can protect yourself from outside threats by installing very robust next generation firewalls. You also need to install very strong internal controls. You need to have access to data controls, both physical (locked doors, file cabinets) and logical. Logical Access involves authentication controls to ensure that persons logging into the system, are who they say they are. The best way to do this is to combine two or more types of authentication: username, password, code number, secret questions, biometrics, etc. You also need to restrict access to data based on job requirements, separation of duties, and by adopting the principle of least privilege: if you don’t need access to it, you don’t get it, and if you do need access to it, you only get access for the time you need it. Having strong passwords which are complex and are changed on a regular basis is very important.

Next, you need to have a 100% reliable back-up system. You need to back-up your hard drives, networks, software programs, everything. Back-ups need to be on-site or remote and either by tape, disk, or in the cloud. If you get hit with malware or ransomware, you will either need to pay the hackers to restore your system or you can completely flush the system and restore it with your backup. You need to have a formal plan for doing periodic restore tests and validations. Keep in mind that most hackers can get into any system, given enough time and money.

Finally, you need to educate your employees and users of your system to recognize threats to the system, to avoid letting those threats in (by not clicking on or opening suspicious emails and attachments), and by reporting suspicious activity to your system administrator or help desk. Education and training of users is your last line of defense. Also, you can create an incident response plan, periodically review your MA Data Privacy Written Information Security Plan, and run vulnerability scans on your network.

A summary of the keys steps to protected your IT system and prevent data breaches are as follows: 1) Defend the perimeter with a next generation firewall; 2) control access to your system with authorization controls; 3) have strong and complex passwords; 4) keep your software up to date; 5) secure your data with strong reliable back-up and recovery plans; 6) train you users and make them aware of potential threats; 7) train your staff to be aware of spear phishing and ransomware; and 8) report incidents to your administrator or help desk.


The presentation can be found here: KPM_InfoSecurity_03-30-2017-1

December 2015 Meeting: Using Technology to Gain Efficiency and Internal Control in Major Business Processes

Summary: Robin Kelley and James Jumes, partners at AAFCPAs, will present and discuss ways in which technology may be used to gain efficiency and internal control in major business processes. Improvements and thoughtful implementation of technology solutions have the ability to reduce process costs, increase quality, reduce the number of errors, reduce the risks of fraud and more.  Attendees will be able to:

  • Better understand how technology can be used to accomplish greater process efficiency, effectiveness, and internal control.
  • Learn how to get started, plan for, and implement technology solutions.
  • Learn the key considerations and the benefits.


Write-up from the session:

Robin Kelley and James Jumes, partners at AAFCPAs, presented and discussed ways in which technology may be used to gain efficiency and internal control in major business processes. Improvements and thoughtful implementation of technology solutions have the ability to reduce process costs, increase quality, reduce the number of errors, and reduce the risks of fraud and more.  Robin and James presented ways to better understand how technology can be used to accomplish greater process efficiency, effectiveness, and internal control; discussed how to get started, plan for, and implement technology solutions; and covered the key considerations and the benefits.

When is a good time to assess using technology to enhance operational efficiency and the effectiveness of internal controls?  One occasion is when the organization is going through a rapid growth.  Others are when your current financial system is old or when your system’s existing capabilities are not being fully utilized.    Changes in regulations, a slow system in place, and inefficiencies in internal controls are all occasions to assess technology improvements.  When thinking about improvements to make, look at your major business processes for areas of opportunity. First look at the purchase to pay cycle.  Maybe you could use electronic purchase orders.  Next, consider workflow technology – approvals don’t have to be in paper format.  Next look into ACH (automated clearing house – electronic bill pay) and EFT (electronic funds transfer) to reduce paperwork.    You can also use Positive Pay to send the bank a list of epays, and you can request debit blocking, to make sure that no one is withdrawing funds from your organization without proper authorization.  All of these measures involve the use of software, sometimes through the cloud or sometime you can use your internal server.

Another area that you can use technology to increase efficiency is in the area of payroll and human resources.  For payroll, use can use workflow technology, direct deposit, debit card/pay card, positive pay, and electronic timesheets.  Electronic timesheets need to integrate with the payroll system that you are using and with your financial system.  When assessing financial operations, you should do a flowchart of your current systems to look for inefficiencies and bottlenecks.  Some options are to use automatic allocations and check scanning for bank deposits.   It is very important for new systems to integrate with each other.   Increasing reporting efficiencies will help management get information to program managers in a timely manner.

When making major technological changes and upgrades, there are some key considerations.  First, since everyone is probably already very busy, hire an outside consultant to plan and implement the changes.  Next, some management staff that are resistant or oppose the changes may have to go or be transferred.   Also, you will have to manage the expectation of the users, the executive team, and the Board of Directors.  The IT department should not be the driver of these changes.  You need to determine what the management team wants to get out of any new system, and then look at software solutions.  Examine your business processes – walk through them with everyone who has a stake in them.  Look for opportunities to make the processes more efficient.  You may have to balance the need for internal controls with the need for efficiencies – they are not always the same.  For successful implementation, you will have to appoint a process owner and facilitator, with an outside consultant providing a “third party push.”

March 2015: Assessing IT Risk and Mitigation

March 2015 Meeting:

Assessing IT Risk and Mitigation

Russell Greenwald, Vice President and Director, Technology Consulting Practice at Insource Services, Inc. gave a presentation that outlined areas of possible risk in your organization in relation to databases, files, email, computer network, and personnel. He also covered how to rate the risks, prioritize them and determine next steps.  Russell has been consulting with nonprofits, for-profits, and technology companies for the past 14 years. Read More

Maximize your Online Donation System

May 2014 Meeting: Maximize your Online Donation System

Russell Greenwald, Vice President and Director of Insource’s Technology Consulting Practice, shared his experiences with online donation systems with those in attendance. Through demonstrations and process review, he showed the group how to attract more donors, save staff time, and gather better metrics. Russell serves as CIO for many of Insource’s Technology clients and is backed by a talented team of staff and partners and has been with Insource for the past 13 years.

Read More