Templates should be simple, clean and easy to understand. They can come in a myriad of different formats, styles and cover a multitude of various models. As a template designer, the key is to try and make the template itself disappear into the background, while at the same time coaxing the actual information it contains into the foreground.

So no crazy fonts,

and no crazy colours.

You will give your users a headache, especially if they have to stare at it for a long time.

There are a lot of interesting sites out there about the aesthetics of template design, such as How to Make Your Spreadsheets Less Lame and Black & White Aesthetics of Web Designs, so I will not reinvent the wheel here.

What I wanted to cover off is a simple, standard P&L Template format on a Jedox Cube. Using the Bikers P& cube, I have built a couple of examples (here for excel and here for web template). All you will need to get this working , is to add a numeric attribute to the Months dimension in the Bikers database called “AP”. This will hold the numeric value for the Accounting Period represented by the month. In Australia, our financial year typically runs from Jul – June, so July will have and AP value of 1.

Once you have added and populated this (July=1 through to June = 12), the template will work.

The normal requirement (here in Australia, anyway) for a standard P&L level planning template is the following:

1. It must have prior months actuals

2. It must have future months forecast (including current month)

3. The total for the Planning Template must be the sum of the Actuals and Forecast.

4.  It must be dynamic as possible (eg new accounts show up on the template automatically)

The two examples I have given are very simple – there are no rules or ETL processes involved – just a test to see if a month is actual or forecast based on the AP Attribute. Then there is some conditional formatting to show visually if a column is Actual or Forecast, and finally a SUM() total column to add up the rows. In the web version, I use cell locking in conditional formatting to show if it is editable or not. Of course, you would also have security employed to make actuals read-only and forecasts writable. The Current Month cell would usually be hidden – not user selectable – and here for illustration purposes only.

Typically templates can be “actualised” by ETL or by rules. That way, the Forecast version has the combination embedded in the element. There is advantages to this – the users can then use Paste View and analyse the data with the forecast line adding up to the template.