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.

Deleting a Line From an SPW

• If within an SPW a line is deleted make sure to click 'Post to Worksheet' after to ensure that your worksheet 'unties' the line from within your budget worksheet

Creating 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 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.

Anatomy of a SPW Page

An SPW is made up of multiple lines, typically organized into sections, like the example below. Those lines may contain values or calculations using formulas, and some may post to a defined worksheet line. When unfiltered, formula lines show the other lines used in the formula below the amounts. Posting rows show the account and dimensions, and any lines used in formulas, below the amounts.

The Summary section contains the rows that will post, with the location and amounts to be posted. Multiple posting rows may post to the same Summary line.

Before you create an SPW, you should consider how you want it to be organized. The best-practice recommendation is to structure each SPW with the sections that make it easy to follow a logical process from the models to the totals.

Building a SPW

Every SPW line contains related information: Info and Values.

• The Info tab is used to maintain the general information for that particular line: the Section it belongs to, the Description or name, and posting information if desired. Sections and Descriptions are listed in alphanumeric order; use a numbering system to control the sequence of lines. 
• The Values tab is used to manage the values and maintain Notes for each month. On the Values tab, use the Budget Widget or a formula, based on calculations using other lines.

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.
• Post to Worksheet - All budget numbers and notes in the Summary section are posted to the appropriate budget worksheets. 
• Load Prior Year SPW - Load the SPW for this worksheet from the prior year's default Dashboard budget. 
• 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 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.

SPW Formulas (Video)

SPWs become more powerful when utilized with calculations. Calculations can be used in any Special Purpose worksheet (WSPW, GSPW, TSPW) in order to make updates easier.

Navigating the Special Purpose Worksheet

There are many options for navigating a special purpose worksheet - whether Global, Worksheet or Template.

Working in the SPW Page

To expose or limit data within the view, use Hide Details or Filter.

Hide Details - Click Hide Details to hide the details for lines that include formulas or post to the Summary section.

Filter - Click Filter to limit the information displayed using various options. Remove filters by clearing the Filter dropdown, and/or associated dropdowns.

Filter options:

• Sections - Display a Section dropdown, used to limit the display to a specific section.
  
  
  
• Posts to Summary - Display lines that post to the Summary, and the Summary section.
• Summary Lines  - Show only the Summary section.
• Reporting Group - Display a Reporting Group dropdown, used to limit the display to lines that are based on a specific reporting group.
• Account - Display an Account dropdown, used to limit the lines to a specific account.
  
  
  
• Formula - Show all lines that are based on a formula. Use the Formula dropdown to select a specific formula.
• Dimensions - If your organization uses line item dimensions, you can choose any of them to use as a filter. Choose a dimension to list all rows that make use of that dimension. This also displays a dropdown, and you can select any specific value for that dimension to show only lines that use that value.
• Worksheet - Available for GSPWs only. Show all lines that are based on a specific worksheet in a GSPW.
  
  

SPW Tips and Tricks

• Click on the Total column for a calculation to view only lines that are used in that calculation. 
• Use "-" or "+" on the far right side of the header line for each section in order to collapse or expand that section.
• Click on the Description of a line to go straight to the Info tab for that item.
• Click on any monthly amount or the calculator icon to go straight to the Values tab.
• Click on the total for any line in the Summary to see just the lines that make up the total shown there.
• Click on elements on any detail line to see all lines that include that same detail.
• Click on the Totals icon in the top line of any section that includes lines that are posted to the summary to see the impact of that section on the Summary: the total income, total expense, and net gain/loss for that section.
• Click on the Section title to update the title.

Reporting Groups - Using Reporting Groups on SPWs (Video)

This feature is part of the Standard Plus, Advanced Plus, and Enterprise subscription levels. 

Reporting Groups are used 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. Like an account category is a group of accounts, a reporting group is a group of lines (dimensions and accounts) that can be referenced in SPWs, and in ReportBuilder, too!

Reporting Group totals are shown in the SPW as positive amounts, unless 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. 

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.

Setting Up Reporting Groups

Go to 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 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. Use this method if you anticipate making changes to the referenced budget worksheets that you will want to automatically include in the reporting group.  

Method 2. The second method is to select each worksheet line. This is the best method to use if you know exactly which budget lines are to be included in the reporting group, and you do not anticipate that this will change frequently. 

Start by clicking Select Worksheet Lines 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.
• Click Add Selected Worksheet Lines to Reporting Group at the very bottom of the list.

Verify the Reporting Group

The Show Matches feature of the Reporting Groups page is useful to see what budget lines are in play for your reporting group.

Setting Up Global SPWs to Use Reporting Groups

Reporting groups can be used in Global SPWs only.

• Click Update Reporting Group Values 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.
• Click Post to Worksheets to post the summary lines to the worksheets as needed. 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.
  
  

Below is an example Reporting Group and how it is used in the GSPW.

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

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!