shelbynext | financials: general ledger special reports
TRANSCRIPT
©2017 Shelby Systems, Inc. Other brand and product names are trademarks or registered trademarks of the respective holders.
ShelbyNext | Financials: General Ledger Special Reports
(Course N39)
Presented by: Bill Ballou Shelby Training Manager
2
Objective
This session covers the purpose and use of General Ledger Special Reports in ShelbyNext |
Financials.
This session presents the following topics:
The purpose of Special Reports
The basics of how to create a Special Report
Several good examples and uses of Special Reports
3
Move through the Program.
Here are just a few things that might be helpful as you learn how to navigate through this
application. While this browser interface feels significantly distinct from the Shelby v.5
environment, many of the terms and certainly the functionality remain the same. You can
always use the Help icon to find answers about the page you are using.
Navigate to the application.
To begin using the General Ledger application, select
Applications ‐> General Ledger from the Top Menu.
Always use this path to move between applications. Note
that you can hold the Ctrl key on your keyboard while clicking
an application to open that application in a new tab in your
browser.
Navigate to Special Reports.
Use the drop‐down lists on the top menu to navigate to Special Reports. You can do this
two ways (Setup Special Reports and Run Special Reports):
Reports – Special Reports
(Use this to run the Special
Report once it is created.)
Modify – Special Reports
Setup (Use this to design
the report view and
determine which type of
output is needed.)
4
Personalize Navigation. (Create favorite tasks or reports.)
Users often have regularly used tasks associated
with their duties. You can save these most often
needed tasks or reports in the right navigation bar.
To add something to this right navigation, click the
Add to My Tasks link in the upper right of your
work area. This task is added to your shortcuts
found under My Tasks in the right navigation bar.
To add a report, click the similar link labeled Add to
My Reports after running that particular report.
Current Financial Settings
In the upper right corner of every ShelbyNext | Financials page you have a control that
allows you to alter your financial settings. You can control which Company, Fiscal Year,
Period, Bank Account, and Credit Card you are set to use. This controls where you post,
what shows up on the reports, and what bank account you use.
5
What is so Special about a Special Report?
A Special Report in General Ledger allows the designer to map accounts to lines which puts the information in a specific order, to combine accounts together to show a summary, and to even show activity in a single line to reflect accounts with the same purpose in different departments or even funds. Often a Special Report is needed to display consolidated activity, but special reports can also allow the designer to create a specific order. You can also combine activity, such as in a Cash Flow report, to show the opening balance for the period, what money is left, and (depending on the report design) where the funds are allocated.
Each Special Report design has at least one report output (Budgeted Financial, Statement of Activities, Cash Flow, Statement of Financial Position, etc.). Special Reports allow an organization to have a primary chart of account design for general purposes, and to have the option to alter that design and structure for specific reports. Some examples might be:
Grouping payroll accounts by staff assignments in the chart of accounts, but reordering and combining those accounts by type (compensations, benefits, allocations).
Summarizing expenses across departments and even funds to show totals for supplies, curriculum, and other expenditures.
Organizing accounts to create simplified summaries for staff, board members, or leadership to get the big picture.
6
Create a new Special Report
To create a new report design you begin by navigating to:
Modify (hover)
Special Reports Setup This brings you to the screen where you create, copy, edit, delete, or run Special Report designs. Each design can be secured to prevent those without the password from changing or deleting it. You can also prevent anyone from running the report without using the password.
Because you can choose specific accounts or account ranges to make up each line in each design, a utility has been added to alert the user when changes are made to the chart of accounts. The option is given to run a report showing all changes and to clear the alert once you have reviewed the designs to confirm whether these changes have affected the integrity of any of your reports.
To begin a new report, click the Add New Report button. (Another option is to use the Auto Generate New Report button. Due to the current functionality, this is best used when the report is restricted to one department.)
A report is comprised of Groups and Lines. Lines show the detail and Groups collect the activity into totals or sub‐totals. It is typically easiest and most logical to create all your groups first and then add the detail with the lines.
7
Therefore, the steps to creating a new Special Report are:
Give the report a title. (It is good to choose a descriptive title to help you and others know what purpose the report serves.)
Decide whether you need to secure the report. (The most common option is not to secure a report.) The options are to leave the report unsecured; require a password to Change, Delete, or Run; or require a password to Change or Delete.
Create groups and sub‐groups (to control your totals and subtotals).
Create lines linked to accounts, account ranges, or a collection of accounts in the chart of accounts.
Attach at least one report output to the Special Report.
Save the report by clicking the Update button.
The construction of each report layout is distinct to match the needs of that report design. It is important to note that Special Reports converting from Shelby v.5 do not work automatically until you add a report output to that Special Report design.
You can also begin a new report by making a copy of an existing report. You can do that for
individual reports by clicking the copy icon to the left of any existing report. Or you can check several reports to copy and then click the Copy Reports button at the bottom of the grid.
8
When adding or editing a Group, you can specify the Description, whether you want to Reverse sign when printing, Show Heading, and to Include under a location of your choice. When adding or editing a Line, you can specify the Description, whether you want to Reverse sign when printing, or to Include under a location of your choice. The option to Reverse sign when printing has a special function. This option is used to reverse the sign on credit balances. If you check this box, then Income accounts (which are credits) show up as positive on your report. If you check this box for Expense accounts (which are debits), then the amounts show as negatives on your report. Reverse sign has a distinct function when running a Cash Flow report, which we cover when creating that report. Show Heading is a matter of personal preference. After designing your report, run the report and look at how the heading, as well as your Group and Sub‐Group total lines, appear on the report.
The option to Include under is a drop‐down list allowing you to select whether to position the group or line at the top or place it under another group. This is also a quick way to move a line or group under a different group.
9
Some Good Examples of Special Reports
Rather than focus on one way to create a Special Report design, we look at several examples of what you can do with a Special Report and show how you create each design.
Total Budget Income and Expense Summary The goal for this special report is to summarize all budgeted income and expenses into one line
each. With this summary, you can use the 12‐month spreadsheet to see at a glance the net
income for each month for the last 12 months. This helps give the big picture as to the church’s
monthly income and receipts.
The budgeted financial statement is also a good report to use with this special report design. It
is very helpful to create a 5‐year history of actual income and expenses that can be used for
future financial planning and to give to lending institutions.
Use a Financial Spreadsheet to create a Graph.
1. First, create a Group to total the income and expense activity.
a. Title the group the way you need your total line to read.
b. Check the box to Reverse sign when printing so Credit totals show as a positive
number.
c. Do not check to Show Heading.
d. Choose Position at Top for this group as it is your grand total.
10
2. Print all income on one line.
a. Reverse the sign to show the credit amounts as positive.
b. Add a single line and use 40000 to 49999 if your income account numbers all
begin with “4” and your account numbers are five digits.
c. Leave the Fund and Department fields open to include all fund and departments.
d. Include under the group you just created “Income Over/Under Expense” so that
this line is included in that total.
e. Click the Apply button to add the second detail line.
3. Add a line for the Expenses.
a. Do NOT check the box to reverse the sign.
b. Use the range 50000 to 99999 and leave the fund and department fields blank.
c. Include under the “Income Over/Under Expense” group you created earlier.
d. Click Update to save this line and finish this part of the design.
11
4. Upon returning to the layout screen (showing all the groups and lines), move your mouse to the right of the screen and click on the Financial Spreadsheet.
a. Check the box to Change Appearance to pull the columns as close together as possible and make the text as small and narrow as you can (even though we export this to MS Excel for our final report).
b. Choose Actual for Report Type. c. Check the boxes to Use Reverse Signs and Show Whole Dollars Only. d. Leave the Print Format as Wide. e. Click Update to save your settings.
5. Click Update on the next screen to save this Special Report design. 6. To run this report check the box to the left of the report name in the list, then click the
Run Reports button.
12
7. Once the Report appears, export the report to Excel to create the graph and clear comparison between income and expense month by month.
13
Use Budgeted Financial Report to show History.
Use the same account design, but instead choose the Budgeted Financial Statement as the output for this report to display the information in a very different fashion.
With the ability to create report
preferences in the Budgeted Financial
Statement, it is very easy to create a
multi‐year report reflecting total income
and expenses for the desired time frame.
Modify Budgeted Financial Formats to
design a multi‐annual report to take
actual figures from multiple years.
Since this report can interface with Excel, it is a simple process to create a graph.
This is a great analytical tool for projecting future income and expenses.
With the ability to create special report designs, you can produce a high‐level report such as in
this example or drill down to the department or account level.
14
Operating Fund Summary by Department The goal is to create a one‐page budget summary. This special report design has each
Department total on a separate line.
For each line you summarize the
whole department, so leave the
account fields empty so you
select all accounts in this
department.
Repeat this with a new line for
each department.
A good practice is to create the Account Groups first, then click
the green plus icon to add lines to that group (or you can drag and drop the line or change the Include in to move it to the right group).
In the example shown, there are groups (sub‐totals) for Income and Expense, then a group (grand total) to show the net of Income and Expense.
You should reverse the sign on the Income lines or groups to show credit as a positive number (totaling Income or totaling Income and Expense).
15
This gives the person reading this report a quick and, typically, single‐page, high‐level review of the accounting by department for this period.
16
Consolidated Balance Sheet
Combine Funds and Consolidate Accounts.
Set up each line consolidating accounts of similar type to show a summary view. Leave the Fund
and Department fields undesignated to show all Funds.
17
This can be a simplified and focused Statement of Financial Position to show consolidated totals from multiple funds. It is good practice to run your regular Statement of Financial Position report as a comparison to confirm your settings.
18
Or . . . You can change the report settings and show this report as Columnar by Fund to see columns for each Fund.
19
Cash Flow Statement To create and run a Cash Flow Statement in the General Ledger, a Special Report is required.
Once the Special Report design has been set up, then it is a simple matter to run this report.
It is helpful to understand how to print the report design. We review this concept by examining
at least one good way to set up a Cash Flow Statement.
Print Report Setup
Select the report design(s) you want included in the setup report and then click the View Setup
List button.
A pop‐up screen gives the option to show the
General Ledger Links (showing the range of
accounts requested for each line) and/or the
Account Details (listing each account linked to
each line). This shorter version shows the
General Ledger Links, but if you want to confirm
which accounts are showing in each line, then
the Account Details may be preferable.
20
This is the layout for a simple Cash Flow Statement. (Your account numbers and ranges are
different, but this is a good standard design that may work for your reporting.)
21
Cash Flow/Balance List – Simple Cash Flow
In this report type setting a line to Reverse the Sign changes the nature of the total being
reported. Check the box to show the period activity and leave the box unchecked for assets to
show the opening balance for that period.
This setup covers all funds, but you may choose to run this report individually by fund.
22
This report gives you an idea of your beginning balance for the period and the allocation of
funds to show the actual balance that matches the balance sheet.
The balance should match with the total cash balance in your Statement of Financial Position
report. If you include all funds in the Cash Flow, then use the Columnar by Fund Name format.
23
Balance Sheet – Cash Available Report
This format uses Special Reports to roll up accounts and subtract from Cash accounts. This is
not a Cash Flow Statement but is extremely helpful in looking at unrestricted (available) cash.
You print this using a Statement of Financial Position report output.
24
25
Additional Tips 1. Your chart of accounts is the key to Special Reports.
2. The more consistent your chart of accounts is, the easier it is to design your Cash Flow Report.
3. If your organization has two or more funds, it is very important to develop the chart of accounts so that accounts from both funds are included.
4. Think about how you want your report to appear, FIRST.
5. Groups come first.
6. Create a “Main Group”.
7. Create Sub‐Groups, as needed.
8. Add your Lines under your Sub‐Groups.
9. Should you separate each fund’s Income and Expenses?
10. If you are on Accrual Accounting, be sure you include changes in assets and liability accounts on your report.
26
Q&A
Class Discussion
Bill Ballou Shelby Training Manager [email protected]
Bill Ballou has been a part of the Shelby training team for over 18 years and trains on both v.5 and ShelbyNext. He greatly enjoys working with the gifted trainers on his team. Bill brings a vitality and life to training software and uses humor to help customers feel at ease with new ideas. He has helped dozens of churches find solutions to tracking financial and gifts information so they can do ministry more effectively. While Bill is known to geek out over finding a new trick in Excel or in MS Reporting Services, his real excitement comes at that moment in training when he sees that a trainee makes a key connection and has that "ah‐ha" moment light in his or her eyes. Bill worked for almost twenty years on staff at various churches, so he has an appreciation for the questions and challenges of many of our users.