Special Purpose Worksheets Basics

Special Purpose Worksheets provide the ability to build line item budget data from calculations and individual budget details and post the results to specific budget worksheet(s).  

SPWs Use Cases

Examples where a Special Purpose Worksheet might be useful:

• Budgeting for attendance at conferences, where registration revenue and expenses are per participant
• Budgeting for an event, where there is income expected from registrations and expenses associated with the event
• Budgeting for expenses related to the purchase of office furnishings, where there are multiple components to be specified (desks, chairs, and filing cabinets) but all costs are to be assigned to the same account
• For churches, budgeting for income, where tithes can be predicted based on historical giving rates and levels of attendance
• Budgeting for an expense based on a value calculated within the worksheet (such as a 10% tithe to Missions on the basis of predicted income, or a 5% license fee based on predicted revenue)
• Budgeting at the departmental level for amounts allocated by management. For example, applying rent or salary cost to departmental budgets, so that the amounts budgeted are predetermined and cannot be changed at the departmental level.
• Budgeting several locations that have similar types of underlying calculations and assumptions; for example, a school that charges tuition and program fees that vary from location to location
  
  

Types of SPWs

There are 3 types of SPWs.

• WSPW – Worksheet-specific Special Purpose Worksheets
  • Accessed from within a budget worksheet
  • Listed on the Planner/Special Purpose Worksheets page
  • Worksheet-specific, only affecting the single worksheet that they are part of
  • Available to any user with access to that worksheet
  • May be modified by importing one or more SPWTs (SPW Templates)

• GSPW – Global Special Purpose Worksheets
  • Accessed from the Planner/Special Purpose Worksheets page
  • Available to non-admin users (with functional privileges and dimension access)
  • Used to impact multiple budget worksheets
  • Used to budget amounts that cannot be overridden at the individual worksheet level

• SPWT – Special Purpose Worksheet Templates
  • Accessed only from the Planner/Special Purpose Worksheets page
  • Available to Admin users only
  • Imported to WSPWs by users
  • Used to post updates to the WSPWs that use them
    
    

Planner > Special Purpose Worksheets

Both Admin and non-admin users can manage and maintain SPWs from Planner > Special Purpose Worksheets. The non-admin user privilege “Global SPWs” determines which non-admin users can access GSPWs via this screen, and dimension restrictions do apply. 

SPW Actions

• Load Budget Year - The default is the current budget year.
• Select Budget Type - The default Planner budget for the specified budget year is displayed. All other budgets for which the user has access are available via the dropdown.
• SPW filters - Depending on user privileges, elect to filter on SPW templates (Admin users only), global SPWs (Admin users and non-admins with the Global SPWs privilege), and/or worksheet-specific SPWs (Admin users and any non-Admin user with the Worksheets privilege).
• Add New Global SPW - Add a new Global SPW.
• Add New Template SPW - Add a new Template SPW.
• Post All Templates and SPWs - Post all SPWs to any affected worksheets.
• Recalculate all Reporting Groups - Recalculate all worksheets where SPWs use reporting groups.
• Load Prior SPW - Load a prior year SPW to the list for use with the new budget.

User Permissions for SPWs

Admin users can:

• Add and update any global SPWs
• Add and update SPW templates
• Use the button to Post All Templates and SPWs and Recalculate all Reporting Groups (Reporting Groups are part of Martus’ Advanced Plus subscription level.)

Non-admin users who can edit worksheets will see the Special Purpose Worksheets menu selection and will:

• See their worksheet-specific SPWs as illustrated here
• If granted the Global SPWs privilege, add and update global SPWs that match their dimension permissions
• Use the Post All SPWs button to post all of the SPWs that they can maintain
  
  

SPWs in the Worksheet

On the budget worksheet, any budget line that is controlled by an SPW has an SPW icon next to it, seen in the image below.

These budget lines can only be updated from the SPW, which is accessed by clicking on the SPW icon.

• If the worksheet is locked, you can view the SPW but cannot change it. 
• If the budget line is based on a global SPW, and you are not an Admin user or do not have the “Global SPWs” privilege and access to all worksheets on that global SPW, you cannot view the SPW.

Use Worksheets Actions to Post Global SPWs, and to see all Applicable SPWs for a worksheet.

Reusing SPWs from Prior Years

SPWs can be reused (rather than rebuilt) when starting a new budget year. 

• If the new budget was copied from the prior year, then the SPWs are copied along with it. Access them from the individual worksheet or the Special Purpose Worksheets page.  
• If the new budget was created using the “Framework” method, then budget preparers will need to specifically load any SPWs that they want to use.  

To load a WSPW from a prior year:

• In Planner > Worksheets,  select the worksheet to edit.
• Click Worksheet Action > Create Special Purpose Worksheet. This will display a blank SPW screen. 
• Click Load Prior Year SPW. The WSPW will be filled out with all details from the prior year’s SPW, based on the current default Planner budget for that prior year.
  
  

To load a global SPW or SPWT from a prior year:

• Go to Planner > Special Purpose Worksheets.
• Verify that the current budget year is shown in the dropdown at the top of the screen
• Use the dropdown at the bottom of the screen to select the global SPW or SPWT to be loaded, and click Load Prior SPW.  
• The SPW will be added to the list shown for the current budget year.

Creating and Editing the Special Purpose Worksheet

Types of SPWs

There are three types of SPWs, each with specific uses.

A Worksheet Special Purpose Worksheet (WSPW) is created from the Worksheet Actions menu on the worksheet, and is specific to a single worksheet in a budget. Detail for lines in that worksheet are captured in the WSPW.

A Global Special Purpose Worksheet (GSPW) is created in Planner > Special Purpose Worksheets.  GSPWs are used to effect multiple lines across multiple worksheets in a budget by using calculations and values to allocate non-editable amounts.

A Template Special Purpose Worksheet (TSPW) is created in Planner > Special Purpose Worksheets. TSPWs are used to provide a template for import into an individual WSPW, with editable and non-editable lines.

All SPWs can be accessed from Planner > Special Purpose Worksheets.

Creating and Editing a SPW 

WSPWs are usually and most conveniently accessed from the worksheet to which it applies. To create a new WSPS, click Create Special Purpose Worksheet from the Worksheet Actions menu.

To edit an existing WSPW, click Edit Special Purpose Worksheet from the Worksheet Actions menu.

GSPWs and TSPWs are created and edited in Planner > Special Purpose Worksheets. To create either, click Add New... at the bottom of the screen.

To edit an existing GSPW or TSPW, click on Edit to the right of the name.

The SPW Edit Page

An SPW is made up of multiple lines, typically organized into sections. Those lines may contain values or calculations, and some may post to a defined worksheet line.

To begin to build your SPW, follow these steps:

1. Click Add Line to create a new line.
2. On the Info tab, choose an existing Section name from the dropdown, or define your own by typing in the Section field.
3. Enter a Description for this line of the section. The Description must be unique.
4. If this line will be the line that is posted, click Post to Budget Worksheet and specify the Worksheet, Account and any other applicable dimensions.
   
   
   
5. On the Values tab, enter the values for the line using the Budget Widget, or use Formula to make calculations.
   
   
   
   
6. Add Show Notes to enter any Notes desired.
7. Click Preview to preview the amounts.
8. Click Save to save your changes.

Continue to add as many sections and lines as needed, building the detail of the SPW.

When ready, click Post to Worksheet(s). The lines in the Summary will be posted to the worksheet(s).

Special Purpose Worksheet Actions

You have the following options:

• Cancel - Cancels any changes made since the last time the SPW was saved.
• Worksheet - Shown on WSPWs only, this button returns directly to the associated worksheet.
• Save - Saves any changes made, but does not post to worksheet(s) for this SPW.
• Post to Worksheet - All budget numbers and notes in the Summary section are posted to the appropriate budget worksheets. 
• Post to WSPWs and Worksheets - Post the updated SPWT to any WSPWs into which it has been imported, then post those WSPWs to their respective worksheets.
• Delete - Deletes the entire SPW. Any amounts and notes already posted to a budget worksheet are retained in that worksheet and can be edited directly through the worksheet. If an SPWT is deleted, imported lines remain, but they are no longer connected to the SPWT.
• Export Printable Version to Excel – Creates an Excel file with all the details of the SPW, including notes.
  
  

Notes about SPWs

Interactions between SPWs and WSPWs

• If you have multiple SPWs and/or WSPWs that affect the same budget line, the contents of the budget line will be determined by the SPW that was most recently posted to the worksheet.

Updates to Frozen Months

• When posted, GSPWs and WSPWs do not update frozen months.

Updates to Locked Worksheets

• SPWs cannot be posted to locked worksheets.

Copying Budgets

• When a budget is copied, all SPWs within it at are also copied and posted. SPWs are posted in this order: first any SPWTs are posted to WSPWs, then all WPSWs are posted, then any global SPWs are posted.

Tutorial - SPW Basics (Video)

Reporting Groups - Using Reporting Groups on SPWs

Reporting Groups are used to define combinations of accounts and dimensions. Reporting groups can be used within a SPW to calculate budget amounts based on the amounts currently budgeted for those combinations of accounts and dimensions. 

Those totals are shown in the SPW as positive amounts, except if the Reporting Group contains both income and expense accounts, and the budgeted expenses exceed the budgeted income; in that case, the amount shown in the Global SPW will be negative. 

This feature is part of the Advanced Plus subscription level. 

Introduction

This feature allows Reporting Groups to be set up to define combinations of accounts and dimensions. Reporting groups can be used within a global SPW to calculate budget amounts based on the amounts currently budgeted for those combinations of accounts and dimensions.   

Here are three examples of situations in which you would want to use reporting groups.

1. A fee based on the total of specific income accounts is paid by some satellite locations to a central location. You want to be able to automatically compute an expense for the fee in the satellite location, along with income for the fee in the central location. Define a reporting group for each location and the specific income accounts on which the fee is to be based, then use that reporting group (which identifies the budgeted totals for those accounts) as the basis for calculations within a global SPW in order to budget the fee as an expense to the satellite locations and as income to the central location.
   
   
2. Rent and utilities costs are budgeted to the Facilities department but are to be allocated to other departments within the organization, using an “allocations” account to reduce the total expenses for the Facilities department and increase the expenses for the other departments within the organization. Define a reporting group for the Facilities department and the rent and utilities accounts. Use that reporting group as the basis for calculations with a global SPW to budget an expense for this allocation to each other department and budget the credit for the total allocation to the Facilities department.
   
   
3. The HR department expenses are budgeted to that department within one entity but are to be shared by a separate entity (a school), using an “allocations” account to reduce the total expenses for the HR department in the first entity and increase the expenses for the other entity. To set this up, define a reporting group for the HR department expenses to be allocated, then use that reporting group as the basis for calculations with a global SPW to budget an expense for this allocation to the second entity and credit the amount of that allocation to the original entity.

This document assumes that you are already familiar with SPWs within Martus. If you are not, review the documentation and tutorials within Martus’ Knowledge Base before continuing with this document.

Set Up Reporting Groups

Use Setup/Reporting Groups to add and edit reporting groups.

There are two methods for assigning combinations of dimensions and accounts to a reporting group.   You can use either method, or both, to set up each reporting group.

Method 1. This is the Flexible Selection method. Use the filters in the Manage Reporting Group Items section of the screen to add combinations individually and make use of the “All” wildcard selection. You should use this method if you anticipate making changes to the referenced budget worksheets that you will want to automatically include in the reporting group.  This method is the best choice for Example 3 in this document, where you want to include specific HR costs regardless of the project to which they might be budgeted. If you only selected currently-budgeted lines (Method 2), then the reporting group would be limited to the projects identified on those lines.

Method 2. The second method is the Select Worksheet Lines wizard. This is the best method to use if you know exactly which budget lines are to be included in the reporting group, and if you do not anticipate that this will change frequently. This method is perfect for Examples 1 and 2 in this document.

Start by clicking the Select Worksheet Lines button to get a list of all dimension/account combinations on all your budget worksheets.

Use the filters to limit this list by dimension, account number, and/or account category.

Use the checkboxes on the left to pick the lines to add to the Reporting Group.

Reporting Group Samples to Match Examples in this Document

The illustrations below give examples of reporting groups that were set up to match the three examples used in this document.

Example 1  Percentage Fee to be Paid to Central by Satellite Locations

Example 2 Reporting Group for Rent, Utilities & Maintenance Costs

Figure 3 Reporting Group for Allocating All HR Department Costs (Excluding Allocation Accounts)

Set up Global SPWs to Use the Reporting Groups

Reporting groups can be used on global SPWs only.

• Reporting groups are specified on the Quantities line within the SPW.  If using a reporting group, the values in each month are based on the budget lines indicated by the reporting group, and those values cannot be updated directly within the SPW.
• Reporting group selection is optional on the Quantities lines. If you don’t use a reporting group on a Quantity line, you must specify the desired values in each month column either by keying them in or using the budget widget.
• If you change the reporting group on a Quantity line, immediately save the SPW to record this change and recalculate the values in the month columns.
• The buttons at the bottom of a global SPW are illustrated here:

• Use the Update Reporting Group Values button to update the values for all the reporting groups on this SPW. This also performs all the calculations on the SPW and updates the totals in the Summary section. This button does not post anything to the worksheets identified on the SPW.
  
  

When a Global SPW is posted, any reporting group values on the SPW are updated and all calculations are recalculated before the SPW is posted.

Example 1 – Fee to be Paid to Central Based on a Percentage of Some Income Accounts at Satellite Locations

For this example, note:

• There are two different Rates entries. The expense is associated with one GL code and the income is associated with another.
• There are two entries in the Quantities section: one for each satellite location for which the fee is to be calculated.
• There are two calculations for each satellite location: one for the expense to the satellite location, one for the income to the central location.

Example 2 – Facilities Costs to be Spread Among Departments

For this example, it is assumed that certain costs for rent, utilities, and maintenance are budgeted to the Facilities department. However, the Accounting department is to be allocated 40% of those costs, the Operations department is responsible for 50% of the costs, and the Facilities department is only responsible for 10% of the original costs. The allocated expenses and offsetting credit are all to be assigned to a single account; the allocations are not being done at the individual GL account level.

In the screenshot below, you’ll notice:

• There are three rates. Two of them, with a positive rate amount, reflect the proportion of this cost that is to be allocated to the Accounting and Operations departments. The third rate, with a negative rate amount, is the total proportion to be credited to the Facilities department. That value is only -90%, since 10% of the original cost is still to be borne by the Facilities department.
• There is only one line in the Quantities section, which specifies the reporting group that was set up to aggregate the facilities costs.
• There are three calculations. The first uses the Accounting Department rate to calculate the amount of the allocation for these facilities expenses to that department’s budget worksheet. The second uses the Operations department rate to calculate the allocation to that department’s budget worksheet. The third uses the credit rate to calculate the total credit to the Facilities department budget worksheet.

Example 3 – Department Costs (Except Other Allocations) to be Split Between Entities

For this example, note:

• There are two rates. One, with a positive rate amount, is the proportion of the HR department cost that is to be allocated to the School entity. The second, with a negative rate amount, is the total proportion of the cost to be credited to the HR department.
• There’s only one line in the Quantities section, which specifies the reporting group that was set up to aggregate the HR department costs. It is important to note that this reporting group includes all accounts budgeted to HR except for the GL account used for the allocation – this avoids the circularity of trying to calculate the allocation based on a total that already includes an allocation.
• There’s two calculations. One computes the charge to the School, the other computes the credit to the HR department.

Notes

Generally, it is a best practice to use a single global SPW for all of the same kind of allocations. It is important to be sure that you are not allocating to the same account and dimension combination on more than one global SPW. If you do that, the “last one wins” and only the most-recently-posted SPW will be reflected on that account and dimension combination. 

A reporting group cannot be used to update an account that is referenced within the reporting group. If this is attempted, you’ll get an error like this when the SPW is posted. This error must be corrected before the global SPW can be posted.

The reporting group values are always updated and any related calculations are recomputed whenever a global SPW is posted. Remember, all SPWs are always posted whenever a budget is copied. 

When setting up reporting groups, it is useful to review the difference between a “blank” dimension and “all” dimensions. Using the Worksheet Lines wizard to select the items in a reporting group always picks specific budget lines (which may have blank dimension values). Using the flexible selection, you may specify any dimension value, including blank – and you can also use “all” dimension values. 

When defining a reporting group, picking a parent dimension value does not include any of its child dimensions. If you want to include the child dimensions in the reporting group, you must add them specifically to that reporting group. 

The “Show Matches” feature of the Reporting Groups page is useful to see what budget lines are currently used when you’ve selected a broad range of items via the flexible selection method.

Modeling with SPWs (Video)

This video illustrates how to set up drivers within SPWs. This example illustrates this by calculating expected tuition and a fee discount. You can use these same methods for many kinds of drivers!

Quick Guide - SPW Templates (SPWTs)

The SPWT is a Special Purpose Worksheet Template (SPWT).  

Each SPWT is essentially a combination of assumptions and calculations that end-users can import into individual budget worksheet WSPWs. An SPWT can be used to define a set of pre-determined Rates, Quantities, and Calculations, and even establish specific Other Budget Items.  The SPWT can contain values for any or all of these, or the specific rates, etc., can be left blank on the SPWT so that they can be entered at the WSPW level. 

SPWTs are particularly useful for organizations with many locations or departments that have a very similar budget framework. Depending on the needs of your organization:

• You may use SPWTs primarily so that individual worksheet preparers don’t have to figure out how to set up calculations; they can just fill in quantities and rates that are specific to their budget area.  The SPWT would assist your worksheet preparers by providing complex calculations. When an SPWT is used for this purpose, you would set the rates and quantities on the SPWT to zero; the WSPW preparer will enter department-specific values.
• Alternatively, you may use SPWTs to pre-define certain rates (for example, a standard travel cost, a standard mileage rate, or a certain percentage license fee).  When an SPWT with rates on it is imported into a WSPW, the rate is imported too. When a specific rate on an SPWT is updated and you choose to “post to WSPWs and Worksheets”, the new rate is pushed to any WSPWs into which the template was imported, it is applied to any calculations in which it was used, and the new values are posted to the worksheets for those WSPWs.

This feature is particularly useful for organizations with many locations or departments that have a very similar budget framework.

Click the link below for a Quick Guide to the power of SPWTs.