Tag: Finance

September 2017 NPFM Meeting: Spreadsheet Security Techniques

NPFM

Summary:
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.

Presenters:
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

May 2017 Meeting: Steering Through Uncharted Waters: Planning in Times of Financial Uncertainty

NPFM
Steering Through Uncharted Waters:  Planning in Times of Financial Uncertainty
 

These are uncertain times for people and organizations dedicated to providing crucial services to under-served U.S. Communities. But an unclear environment does not mean we should sit still and wait.

Jihye Gyde and Nicole Curtain, two senior consultants from Nonprofit Finance Fund, facilitated a session
on concrete first steps for assessing your organization’s financial situation and options to inform
decision-making. These are uncertain times for people and organizations dedicated to providing crucial
services to under-served U.S. communities. But an unclear operating environment does not mean we
should sit still and wait. This is exactly the time to prepare our organizations for whatever change is
coming in a thoughtful, data-driven way.  Topics included: gauging your organization’s financial health,
adaptability, and risk tolerance; assessing risks and opportunities; and developing potential scenarios
and corresponding action plans.

One of the main concerns for nonprofits in planning for the future is the predictability of funding. There
are 6 basic steps that agency leaders should take when planning for an uncertain future. First, open a
dialogue with your staff, board, and stakeholders about what everyone is concerned about. Second,
assess your current financial situation, risks, and opportunities. Do a “pulse” check and focus on the
state of your unrestricted operating revenue. How many months of unrestricted cash do you have?
Determine where your agency stands financially – do you have room to breathe or do you have to take
action now? Can the agency afford to absorb a deficit? On you balance sheet, identify the drivers of
your assets and identify the drivers of your liabilities. Are your receivables collectible and are your
building and equipment being used efficiently?

Third, identify and focus on what issues are controllable by you. Start with the shortest term and
proceed to the longest term. There are financial, human, intellectual, and social capital considerations to
take into account. You need to think through the various levels of change.

Fourth, do the planning –develop various scenarios and what steps you need to take. It is important to
write down each scenario and think it through. Identify the main issues and opportunities, what are the
priorities, and identify the areas of risk, variability, and viability. Identify the range of changes that may
occur, articulate the assumption that you are making, and then create an actionable plan with a
timeline, benchmarks, and objectives and priorities. You should o a program economic analysis, do a
cash flow projection, and determine how much working capital you will need. You can do a go no-go
analysis, an if-then analysis, a decision money/mission matrix, or develop some useful management
dashboards. Senior Managers and the Board should be involved in developing the scenarios. The Board
is used to looking at actual to budget financial results, but you have to look beyond revenue and the
budget. Look at the range of deficits or surpluses that your agency can absorb.

Fifth, monitor developments and operationalize your plan. Develop the means to monitor your plan and
a way to check in with staff to monitor developments. A plan of action should include: timeline,
benchmarks, identify the information /date needed to manage the plan, and identify the staff or team
responsible for implementation.

Finally, share information and develop a culture of collaboration. The goal is not necessarily to provide
answers, but rather to foster a healthy dialog about the future. Communicate often with staff and try
not to be reactive, rather be proactive. Adopt sound financial practices which allow the agency to take
advantage of opportunities and avoid pitfalls.

 

Link to the presentation: http://www.nff.org/news/nff-helps-social-sector-leaders-prepare-change

December 1 Meeting 2016: Common Errors in Financial Statement Preparation And how to Avoid Them

NPFM

Alexandria Regan, Partner at Citrin Cooperman, gave a presentation  about some of the most common errors found in not-for-profit financial statements, the impact that these errors could have on financial statements, and how to avoid errors in financial reporting, including reduction of adjusting entries during your audit. Alex is a an auditor and has over 21 years of experience working with non-profits.

Financial Statements are the responsibility of the organization that is being audited. The number of audit adjusting journal entries that are included in the final audit documents is an indicator of the adequacy of the organization’s internal controls. During the planning stage of the audit, management should use the previous years’ audited statements as a guide. Ask your auditor for advice about any complicated transactions that you are unsure of and try to resolve any issues before you send the final trial balance to the auditors for the audit.

The first area that Alex discussed where mistakes are commonly made, is revenue recognition.  There are various forms of revenue and sometimes there is a grey area when it comes to categorizing the type of revenue.  One of those areas is   contributions versus exchange transitions.  A contribution involves the donor making a donation to support the recipient’s programs, the donor determines the amount and delivery method of the payment, and the recipient is not penalized for non-performance.  An exchange transaction is more of a fee for service arrangement.  A cost reimbursement contract is an exchange transaction.  The resource provider makes it clear that it is making payment in exchange for certain benefits or outcomes, determines the delivery method and amount of payment, and the recipient can be penalized for non-performance.

Contributions can be either unconditional or conditional.  The organizational will recognize unconditional contributions when they occur, but it can only recognize conditional contributions when the conditions are met.  Another distinction occurs between intentions to give and promises to give. Intentions to give are not recorded until the contribution is received (such as inclusion in a will). Promises to give are subject to a different standard.  An example would be if a grant is promised over a 5 year period, but you have to match it – then you should not book it until the match is achieved.  Contributions or pledges receivable that are paid by donor advised funds should not be recognized until payment is received. If an organization receives a 5 year unconditional pledge, then you can book it as a temporary restricted asset. You should use a risk adjusted discount rate (present value calculation). The rate should be determined at the date the promise is initially recognized and should not be subsequently revised.  Please be aware the multi-year pledges are subject to an implicit time restrictions even if the donation is unrestricted for general operations. Multiyear grants should be released according to the due date schedule included in the grant. If expenses are incurred for which both restricted and unrestricted revenue is available, you should book the restricted revenue first.  Also, it is not possible to release funds greater than the net asset class balance even if you anticipate future funds.  Board designated net assets are recorded as temporarily or permanently restricted net assets However, even if funds are designated or restricted for a purpose by the Board, they are still unrestricted for GAAP purposes. Keep in mind that only donors can impose restrictions that create temporary or permanently restricted net assets.

Other common mistakes on financial statement is the failure to account for an operating lease in a straight-line basis; failure to report fundraising expenses fully; failure to report gifts-in-kind; and the failure to include a statement of functional expenses when required.

The PDF of her presentation is here: npfm-presentation-12-1-16

June 2016 Meeting: What Story Do Net Assets Tell About Your Organization and How to Explain it to Your Board

NPFM
Net Asset classifications are unique to nonprofit organizations and frequently management and board members do not have the financial literacy skills to interpret this information.  The senior finance staff member is often the only one available to educate the ED and the Board. It can be particularly challenging with board members that only have experience in the for-profit sector. The net asset classifications can provide insight into an organization’s revenue mix, available capital, overall financial condition, and ultimately, stability and sustainability.  Ed Mulherin from Ecratchit explained the unrestricted, temporarily restricted, and permanently restricted net asset classifications that appear on nonprofit financial statements, how to interpret this information, and provide some tips on how to explain it all to your Board.  Edward M. Mulherin, CPA, Esquire is Founder & CEO of eCratchit.  Ed has over 30 years of experience providing accounting and business consulting services to a variety of clients. In 2001, Ed founded eCratchit, which provides web-based bookkeeping and accounting services.  Ed has been the Virtual CFO for dozens of companies over the past 10 years providing strategic financial thinking, consulting on issues of cash flow, nonprofit sustainability; short and long term financial planning, banking and financing issues.
It is very important for the Management and Development teams of an organization to have a firm understanding of the concept of what net assets mean and how to explain it to the Board of Directors.  Net assets are comprised of Unrestricted Assets, Board Designated Reserves, Temporary Restricted Assets, and Permanently Restricted Assets. When a donor give funds to an agency, those funds will be classified as 1) unrestricted; 2) temporary restricted; or 3) permanently restricted, which can be set up as an endowment. Board designated funds can be restricted or unrestricted. When looking at the Statement of Activities in the Financial Statements, you should focus on the unrestricted column.  You should also do a monthly Actual to Budget statement, showing the variance between the two.  The Temp Restricted column on this Statement shows funds that are not currently available but it demonstrates strength for future periods. Over time, the temp restricted column will go up or down, depending on whether you receive new funds or bring existing funds into the operating fund.   Temporary Restricted Funds are those funds restricted by the donor to spend for a specific purpose or program or restricted to a certain time period.  According to Ed Mulherin, a good rule of thumb is to release funds from Temp Restricted  “early and often – get it out of Temp Restricted,” otherwise it can get stuck in the category.  If you are not sure whether you can bring certain temp restricted funds into operations, ask your auditor.  However, another rule of thumb is “don’t release it if you have not received it yet.”
On many occasions, the Development Team and the Finance Team are not aligned on terminology and goals when categorizing and managing funds.   The two teams need to be on the same page as to what temp restricted means, how the funds are handled, and when they can be released into operations.   Finance and Development should reconcile monthly in the following areas:  pledges, temp restricted, and unrestricted operating revenue.   As mentioned earlier, Board Designated funds can be restricted or unrestricted.  Sometimes it is a matter of form over function – the fact that the funds are Board designated informs the reader of the financial statements that the Board has acted to limit the use of the funds and this might also appeal to certain funders.
Establishing the appropriate level of reserves funds for you agency depends on a number of factors.  It depends on the size of the organization, the volatility of your funding, and the ability of your agency to raise funds quickly.  A good rule is to have at least 6 months s’ worth of opera ting reserves on hand at all times.  You can use temp restricted funds as working capital, but you need to have sufficient funds to cover for its use this way. Finally, it is very important to have a strong balance sheet. When management, the Board, or interested parties look at your balance sheet, they get a good sense of how the organization is doing by looking at the Net Assets line.
 Edward M. Mulherin, CPA, Esquire is Founder & CEO of eCratchit.  Ed has over 30 years of experience providing accounting and business consulting services to a variety of clients. In 2001, Ed founded eCratchit, which provides web-based bookkeeping and accounting services.  Ed has been the Virtual CFO for dozens of companies over the past 10 years providing strategic financial thinking, consulting on issues of cash flow, nonprofit sustainability, short and long term financial planning, banking and financing issues.

April 30 – Going Paperless: What You Need to Know

NPFM

Presentor: Anne Healy, Director of Finance & Administration, at Match Education
Anne has many years of experience as the CFO at various organizations. She shared her experience with paperless solutions that help tame and organize the back office.  She also provided information about several applications used for this purpose, including DocuSign, salesforce, bill.com, and tallie.

Anne Healy, Director of Finance & Administration, at Match Education, shared her experience with paperless solutions that help tame and organize the back office.  Anne has many years of experience as the CFO at various organizations, including Rosie’s Place.  She also provided information about several applications used for this purpose, including DocuSign, Salesforce, Bill.com, and Tallie.
Anne stated that many organizations are trying to get out using paper for the AP function – heading toward a paperless finance operation.  She first spoke about bill.com, which can be used to pay to process invoices and receivables electronically.  It is easy to use and synchs with Quickbooks.  The bill.com files can be uploaded into some accounting systems in CSV format. Bill.com allows you to store AP files and vendor files in their system and it can be used to issue w-9s to vendors.  You can set up a schedule of monthly bills for payment.  You can see all bills outstanding, who has approved the invoice for payment, where it is being charged, and when payments are made.  You can easily separate the duties of paying bills and the ability to input bills into the system. You can have your vendors send their invoices directly to your bill.com account and a manager can approve them for payment. You can just give the bill.com file to your auditors when they review your AP operations.
Tallie is a program that can be used to reimburse employees and to pay your AMEX accounts.  You can upload picture of receipts and it syns with Qucikbooks. Approvers can be set up centrally and documentation is easy to save.
You can use DocuSign to sign your documents – digital signature are now well-accepted in business.   You can cc. others that need to know and filing is easy.
Anne stated that she uses Salesforce to store her organizations personnel files.  It can also be used for program tracking.  It is easily customizable and access and settings can be set up.  For all of the electronic payment, storage, and signing programs, security is very important.  You have to be very careful about deciding who get access and implementing and enforcing the correct and secure settings.

Presentation can be found here: NPFMhandoutpaperless