
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:
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).
.png)
.png)
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.
.png)
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.
.png)
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.
.png)
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
 Important

Notice how the Insert button changes 
 to an Update button (see where 
 the arrow points to).
.png)
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.
.png)
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.
.png)
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.
.png)
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). 
.png)
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.)
.png)
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).
.png)
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 Operating Expense | 
 | Operating 
		 Profit | 
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.
.png)
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:
.png)
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.png) 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.
  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.
.png)
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:
.png)
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).
.png)
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).
.png)
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.
.png)
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.
.png)
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:
.png)
A basic Income Statement from the PandA model