OA Writing a Reporting Specification

Applies to Corporate Accounts/Management Accounts/Project Ledger Reports Only

What is a Report?

Let’s start by answering the obvious question. Any report is a way to gather a load of facts together and to present it in a simple, readable fashion. The reports from OpenAccounts are nothing more than big tables of data, meaning they all have a basic structure of Rows and Columns that, when combined in a particular way, will provide the result needed.

But how do you know what is needed? This is where a specification document comes in. A good specification will help to clarify your thinking and lay out the requirements in a way that the report writers can understand. In basic terms:

When you are clear in asking for what you want, you are more likely to get it

There is nothing difficult to writing a report from OpenAccounts provided you are clear on the data you can access. After all:

You can’t get something out if you didn’t put in to begin with!

Defining the Requirements

Before we start our specification, we must ask ourselves these questions:

What is the required end result? – or What do I want to get out?
How do I populate this data? – or Where do I get that information from?
How do I wish to present this? – or What do I want it to look like?
What is my timeframe? – or when do I need this by?

There are many ways to approach all of the above, but perhaps the easiest is to lay it out on a piece of paper. This will help clarify your thinking, and give you a visual representation of the answers to the above 3 questions.

For example:

You need a table showing utilities spend over a number of years – the End Result
You want to see a list of the utilities (Gas, Electric, Water) down the page and the years (2017, 2018, 2019) across the top. You know which Expense codes relate to Utilities (and which don’t) and will use values recorded against these codes to put in the table – Populating the data
You want to see headers and sums highlighted, and the appropriate accounting lines to define the totals/ sub totals – Presenting the data
I need it for the 28^th of the month – the Timeframe

When defining your OpenAccounts report, you must also consider the type of report you need. This is either:

Management Accounts – Accounts data from one company only

Corporate Accounts – Accounts data from more than one company

Project Ledger – Data from the Project Ledger

This is the first piece of information OpenAccounts needs, so it is the first item you must decide.

N.B. If your requirements are outside of the parameters detailed below, please seek assistance

Rows

Rows hold the detailed breakdown of a report. As a result, there are usually quite a number of rows. This will be the biggest area of the report to specify and will need the most attention paid to it.

Firstly, decide how much detail you want to see.

For example:

You could have details for Salaries, NI and Pensions on separate lines, or
You could group them together on one line entitled Staff Costs

You can group as many items together as you like, provided the groupings make sense

The Row Formats in OpenAccounts normally display the Cost Centre and Expense Code data. You can group codes together provided you specify the codes you want to see on each line carefully.

For example:

A separate line for Salaries, NI and Pensions will need to collect data (per line) from codes 27-3300, 27-3304 and 27-3308 respectively
A line for Staff Costs will need to collect data from codes 27-3300 to 27-3308
If you want data for Salaries and Pension, but not NI, you will need to collect data from 27-3300 and 27-3308, but not from 27-3304

Provided you consider carefully what the requirements are, there are no limits to the combinations of Cost/Expense codes available.

Columns

A maximum of 99 columns can be added into any Column Format. This should be adequate for general reporting.

Column Formats normally display the Timeframes and the Data Models being used. The following are available for reporting purposes:

Timeframes

Data Models

Annual

Actual

Period

Budget (the “Live” Budget)

Opening Balances for Year

Budget Model (Other Budgets e.g. Forecast)

Quarter to Date (Q.T.D.)

Total Quarter

Year to Date (Y.T.D.)

Columns can display Cost Centre data but only through Hierarchies (not covered in this document).

Every Column needs to have a name. This should be clear, meaningful and relate to the data you want to see in the column below.

For example:

A column entitled Budget is vague and open to interpretation
A column entitled Annual Budget 2018/19 clearly defines the required contents

Giving your Column a clear name will help you decide the Timeframe and Data you need. So, as you list out the columns you will need on your report think about what you need to see and specify these accordingly.

Calculations

It is possible to have certain calculations completed inside OpenAccounts. However, if you choose to do this, the results will then be shown in the report as a number only, with no detail of how the calculation was made.

Best practice is to add any formulas to the Excel template for the report. By doing this, the user can clearly see how the figure is defined.

List the calculations you require on the Excel template as you would any other Excel based Formula. Simply type the formula in, using = followed by the row/column headers appear in.

For example:

Your report has lines for Staffing Costs, Premises Costs and Stationery. You wish to summarise these on a line entitle Administrative Costs. Show the formula as

= Total of Staffing + Premises + Stationery

If a calculation is required within OpenAccounts, please seek assistance

Specifications Template

The preferred method for submission of a specification is via an agreed template.

You do not have to use this template, provided your requirements are clearly defined

Assistance

If you require assistance to write your report specification, please speak to you designated Report Writer