The PandA Model – Ad Hoc Analytics/Reporting

In a previous section, you worked with the Income Statement spreadsheet provided to you, and—insofar as reporting is concerned—you selected different Filter Members to change the Slice view. Before returning to the Income Statement and its use for Planning (Forecasting and Budgeting)—see next section—now you can demonstrate some of the vast capabilities we have been referring to for ad hoc reporting and analysis, which will be done by slicing and dicing the cloud-based PandA model that the Income Statement is connected to. As below, you will start with a blank Excel worksheet, not the Income Statement.

A few key terms and points of information are worth making clear:

The Income Statement and indeed all the spreadsheets that follow are making use of PowerExcel, which is evidenced by the add-in ribbon shown previously. [See the last section—the PowerExcel Ribbon—for more on this topic.] As a user, you can make full use of PowerExcel when reaching the PandA model in the cloud, which this and following sections will demonstrate.
The PandA model exists and is saved as a Nexus database, a type of Olation® database, from PARIS Technologies, that provides the multidimensional modeling for collaborative planning, analytics, and reporting—by any number of users, from anywhere in the world.

Returning to the matter at hand: you will proceed, starting with a blank worksheet, by creating and rearranging a PowerExcel Slice, and then dragging and dropping Dimensions to the Filters, Columns and Rows boxes (along with making Filter selections, as shown previously). Further, you will select specific sets of Members to be displayed along the Columns and Rows. The end result will be a more basic Income Statement report than the spreadsheet provided to you.

Begin—as Finance professionals so often do—by launching a New Excel worksheet.

Next, to follow the example steps, so that they appear as they do in the images that follow, you will need to work with the PowerExcel Sidebar.

a. Go to on the PowerExcel tab.

b. Click on Options on the PowerExcel Slice control group (see arrow in the image below).

In the Slice Options window, enable (check) Automatically display sidebar (see arrow).

When this option is enabled, whenever a user clicks on any cell containing a PowerExcel reference, it will automatically display the PowerExcel sidebar on the right side of the Excel worksheet. Whereas when this option is disabled, you would have to click on any PowerExcel reference, and then click on the Show Sidebar icon on the PowerExcel ribbon before the sidebar is displayed.

A sample PowerExcel Slice is shown below. The section box in red is the PowerExcel sidebar, and, in this example, the PowerExcel reference clicked on is cel A11.

The PowerExcel sidebar (boxed in red in the above screenshot)

As an overview of the following exercises, we will step through the following:

1. Create a New PowerExcel Slice

2. Reorganize the Slice Orientation (switch Dimensions between Rows and Columns)

3. Change the Filter Member

4. Change the Display Members along Rows and Columns

5. Insert Empty Rows

6. Nest Dimensions

7. Enable the Format Cells Setting of a PowerExcel Slice

8. Create a Basic PandA Income Statement Report—one that is similar, but more basic, than the one you have been provided.

To begin with these PowerExcel exercises:

1. Create a basic PowerExcel Slice from the PandA model.

a. Go to the PowerExcel tab of the Excel ribbon and click on the New PowerExcel Slice icon.

b. The PowerExcel sidebar appears on the right section of the Excel worksheet.
Select the following:

Database/Database Connection: PandA QS Connect
Cube: Financial Data
Filters display: default
Columns display: default
Rows display: default
Insert into: Current Worksheet
Worksheet Location/Cell: $A$1
PowerExcel Slice: Perspective

c. Leave the rest of the checkbox options unchecked except Format Cells by Type.

d. Click on the Insert button. We will call this the default Slice.
If you try clicking on any cell with a PowerExcel reference, you will see the PowerExcel sidebar appear—the result of the option selection you made earlier.

Important

Notice how the Insert button changes to an Update button (see where the arrow points to).

2. Change the position of Dimensions within the Slice.
Re-arranging the Slice is as simple as dragging and dropping the Dimensions between the Filters, Columns, and Row boxes of the PowerExcel sidebar.
For this example, move the Year dimension to Columns and the Month dimension to the Filters.

a. Go to the PowerExcel sidebar and drag and drop the Month dimension from the Columns to the Filters area.

b. Next, drag and drop the Year dimension from the Filters to the Columns area.

c. Click the Update button.

The Slice will now be displayed with the Year members (All, 2021, 2022, 2023 and 2024) showing up along the Columns and with Month among the Filters, displaying the default Member (All)—see image below.

d. For this example, revert to the original Slice arrangement where Year is displayed among the Filters and Month is displayed in Columns. Make sure to click the Update button.

3. Change a Filter Member, so that the Version dimension shows only Actual values, and the Year dimension displays 2022 values.

a. Go to the Filter area of the PowerExcel sidebar and double-click on the Version dimension. The Select Members dialog appears (here, Select Members for Version). In this dialog, the Members tab lists all the Members that exist for the selected Dimension (Version), while the right-hand side defines the current display Member. Currently, Forecast is the Filter Member display for the Version dimension.

b. Remove the currently displayed Member (Forecast) by clicking the Clear button.

c. Drag and drop the Actual member from the Members tab (on the left) to the display window (on the right). Actual will now be the only Member in the right-hand pane.

d. Click the Use Selected Members button (green checkmark button) to commit the changes.

e. Back in the PowerExcel sidebar, the Version dimension now indicates Actual as the display Member.

f. Still in the Filter area of the PowerExcel sidebar, double-click on the Year dimension. Currently, the 2023 member is displayed for the Year dimension. Double-click on it. In the Select Members dialog that appears, delete the Members displayed on the right-hand pane (via the Clear button), and then drag and drop 2022 to the display window on the right.

g. Click the Update button to refresh the Slice.
The Slice now shows Actual as the display Member for Version (highlighted in yellow); and 2022 as the display Member for Year (highlighted in purple).

4. Change the Display Member for Columns.
The procedure for changing the Columns and Rows display Members is identical to the procedure for changing Filter Members. To change Column display Members to show only the individual months Jan to Dec, follow these steps.

a. Go to the Columns area of the PowerExcel sidebar and double-click on the Month dimension. The Select Members dialog appears.
Currently, all Members are displayed for the Month dimension.

b. Remove the currently displayed Members by clicking the Clear button.

c. Drag and drop the months Jan to Dec from the Members tab on the left to the display window on the right. (You can also shift-select Jan through Dec to do the same thing.)

d. Click the Use Selected Members button (green checkmark button) to commit the changes.

e. Back in the PowerExcel sidebar, click the Update button to refresh the Slice. Now, the Slice shows the 12 individual months displayed along the Columns (B12 to M12).

5. Change the Display Member for Rows.
Next, change the Account members displayed along the rows so that only individual Income Statement Accounts appear, as shown below:

Revenue
Cost of Sales
Gross Profit
Gross Profit %
Payroll and related expenses
Distribution
Occupancy Expenses
Research and Development
Sales and Marketing
Depreciation
Amortization
Administrative Expenses
Other operating Expenses (Income)

Operating Expense
Operating Expense %

Operating Profit
Operating Profit %
Other Revenue
Other (Expense)
Other Income (Expense)
EBIT
Interest Revenue
Interest (Expense)
Interest
Profit Before Tax
Income Tax Expense
Profit After Tax

a. Go to the Rows area of the PowerExcel sidebar and double-click on the Account dimension. Currently, all Members are displayed for the Account dimension. In the Select Members dialog that appears, delete the Members displayed on the right-hand pane (via the Clear button), and then drag and drop the correct Members (see list above) from the left-hand pane to the right-hand pane.

b. Click the Use Selected Members button (green checkmark button) to commit the changes.

c. Back in the PowerExcel sidebar, click the Update button to refresh the Slice. Now, the Slice shows the selected accounts displayed along the Rows (A13 to A39), as in the following image:

6. Insert EMPTY ROWS OR COLUMNS
Now, we will configure the PowerExcel Slice to include blank rows so that when updated, blank rows will show up in the appropriate areas of the Slice.

a. In the Rows section of the PowerExcel sidebar, double-click on the Account dimension.

b. Insert blank or empty rows/columns by using the Insert empty row/column after selected member button along the upper right-hand pane of the Select Members dialog. This will insert an empty row or column (see BLANK highlighted in yellow in the succeeding image) right after the currently selected Member. You can also reposition the empty rows/column by dragging and dropping them before or after any Member.

c. In the Select Members dialog, identify where you want to insert an empty row, and then select a Member directly above that area and click on the indicated icon.
d. In the above example, to put a blank row between Cost of Sales and Gross Profit, click to select Cost of Sales and then click the Insert empty row/column icon.

Follow the same procedure and insert empty rows as shown in the screenshot below:

e. Click the Use Selected Members button (green checkmark button) to commit the changes.

f. Click the Update button. This updates the PowerExcel Slice. Notice the blank rows now appear in the Slice (in Rows 15, 18, 28, 31, 34, 38, 40, 44, 46, 48, 50).

g. If you look at the Columns area of PowerExcel sidebar, notice that the areas where a blank row is inserted appear with a double comma (highlighted in the image below).

7. Nesting Dimensions
You can further customize the report by nesting or stacking the Dimensions. Try "nesting" the Year and Month dimensions along Columns. We will make a comparative report that shows 2022 and 2023 data, showing data per month of each year.

a. Since the Month dimension is already positioned along the Columns (and is displaying the individual months Jan to Dec), just drag and drop the Year dimension from the Filters to the Columns area right above the Month dimension.

b. Double-click on the Year dimension. In the Select Members dialog that appears, delete the Members displayed on the right-hand pane (via the Clear button), and then drag and drop 2022 and 2023 to the display window on the right.

c. Click the Use Selected Members button (green checkmark button) to commit the changes.

d. Back in the PowerExcel sidebar, click the Update button to refresh the Slice. The Slice appears as below, with 2022 months showing in columns B to M, while 2023 months are displayed along columns N to Y.

8. Create a Basic Income Statement.
Next, you can create a basic Income Statement from the PandA model, one which tracks, but does not match the Income Statement spreadsheet provided to you.

a. Drag and drop the Year dimension back to the Filters area, then select 2022 as the display Member.

b. Leave Month displayed along the Columns with individual months Jan to Dec as display Members.

c. Apply the Accounting format where numbers appear with two decimals.

d. Also apply the Percent format for numbers that should appear as percentages (i.e., Gross Profit %, Operating Expense % and Operating Profit %)

e. Add formatting where single lines appear above subtotals (e.g., Revenue, Cost of Sales, etc.) and a double line appear under the final net income (here, Profit After Tax).

f. You can also add a header for the Income Statement report such as:

         The Great Financials Company
         Income Statement
         For the year ended December 31, 2022

The Basic Income Statement report will appear as follows:

A basic Income Statement from the PandA model