Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Nonprofit companies

Ratio Analysis

This template automatically calculates frequently used ratios that can be used by either a nonprofit organization’s auditors as part of analytical procedures performed during the planning, substantive testing, or final stages of an audit or by an organization’s employees or external accountants to help manage the organization’s operations.

Entering Data

All of the information needed to calculate the ratios is entered on the Input worksheet. The first section of the Input worksheet requests the organization's name, preparer’s name, preparation date, and financial statement date. (If the Shared Data functionality of PPC's Workpapers™ for Nonprofit Organizations is used, the organization’s name and the financial statement dates are automatically generated.) If a financial statement date is shown, the appropriate periods in the template’s column headings will populate automatically. Otherwise, the default column headings will be visible.
To complete the remaining portion of the Input worksheet, enter the requested data into the nonshaded cells. The worksheet accommodates three years of financial statement information for comparative purposes, but you can enter just one or two years of data, if you like. All input amounts should be entered as positive numbers. Also, if a financial statement balance is zero, type a zero in the appropriate cell. Simply leaving the input cell blank may prevent the ratio from calculating. However, if the financial statements do not include a line item depicted on the Input worksheet, do not type zero amounts on the Input worksheet. Doing so may calculate ratios that are truly not applicable to the particular organization. The user should exercise judgment when determining where specific financial statement amounts should be entered on the Input worksheet.

Reviewing Ratios

The ratio worksheets automatically calculate based on the data from the Input worksheet. The ratios are organized into broad categories (support & promises to give, revenue & receivables, and expenses), which are presented in three separate worksheets. Each worksheet is then organized into subcategories to efficiently review the ratios presented. Each ratio provides the ratio name, an illustration of the ratio’s formula, and up to three years of the calculated ratios. (Some ratios include additional explanation within comment boxes.)
If the required data for a particular ratio is missing from the Input worksheet, the ratio will not calculate and the corresponding cell will be blank. Generally, this is to distinguish an incomplete ratio from a ratio that has a result of zero. This is critical for ratios that include averages (average accounts receivable, promises to give, etc.). Ratios using averages will not calculate unless the beginning and ending amounts (including zeros) are entered on the Input worksheet.

Updating Data

With this template, you can carry forward the data from year to year to develop an historical summary for comparison purposes. As each year passes, the ratio analysis should be “rolled forward” so that it always covers the most recent three-year period.  This can be accomplished by clicking the Rollforward to next year button, which deletes the data from the oldest year (Two Years Ago) and moves the other two years of data one column to the right. Thus, the user will have to fill in the data only for the most recent year (Current Year) in the left side of the Input worksheet.  See the section “General Workpaper Functionality and Tips for Entering Data” for further information concerning the effects of roll forward functionality.