PS: Understanding ProfitWizard - Building a Report – ProfitSword

In this article we will be looking how to build a report in ProfitWizard, but before we get started let's review a few key terms that we will be using to build the desired report(s).

These are the main 5 elements we will use to set up our data cubes that create the parameters for our reports.

Site - Hotel or Property List
Date - Day, Period, PTD (Period To Date), Year etc.
Mode - Data Set (Forecast, Budget, Actuals etc.
Item - Any GL account or total in the system
Measure - AMT, QTY, Rate, % of Revenue

We will be using the Pivot Table Fields Box to set-up how we would like our report to display.

Filters - Any selections such as Mode, Item, Period or Sites not selected as Row or Column will need to be included in the filters box
Columns - Whichever dimension that will be used as the Report Column Header will be placed in this box
Rows - Whichever dimension will be used as the Row Header will be placed in this box
Values - This section is used for the measure selected. This will depend on what item is selected and which quantitative measure the user would like to see

We have a few recommendations to utilize so that the report generated is as accurate as possible. We recommend building the report from the bottom - up:

1. Select Data on your Excel spreadsheet (Make sure you are using the spreadsheet that has the 2 connections: Day Summary and Period Summary connected)

2. Select Existing Connections

3. Select Period Summary Connection

4. Click Open

The Pivot Table Fields box will appear and we will use this to create our report. We will begin with the Site selection and the last element we will enter will be Measure which will always be automatically moved to the Value section of the Pivot Table:

1. Click on the inverted Triangle next to Sites to reveal the drop down list

2. De-select the "Select All" box

3. Place a check mark in the "All Properties" Box

4. Click "OK"

5. Once "All Properties" is selected, drag the "Sites Tab" down to the "Rows" Field box.

Next we will move to "Period" field. Click on the inverted Triangle next to Period to reveal the drop down list.

1. Click on the inverted Triangle next to "Period" to reveal the drop down list

2. De-select the "Select All" box

3. Place a check mark in the "Period" or "Time Frame" you would like to view. For this example we have selected November 2019. Select the year "2019" and expand the "+" sign to reveal the available months and place a check mark in the "2019-11" box.

4. Click "OK"

5. Once "desired date(s)" are selected, drag the "Period Tab" down to the "Filters" Field box.

Next we will move to the "Mode" field.

1. Click on the inverted Triangle next to "Mode" to reveal the drop down list.

2. De-select the "Select All" box

3. Place a check mark in the "Mode(s)" you would like to view. For this example we have selected "Actual" and "Primary Forecast" by placing a check mark in each box.

4. Click "OK"

5. Once "desired mode(s)" are selected, drag the "Mode Tab" down to the "Rows" Field box.

Next we will move to "Item" field.

1. Click on the inverted Triangle next to "Item" to reveal the drop down list

2. De-select the "Select All" box

3. Place a check mark in the "Items" you would like to view. For this example we have selected "Rooms" and then "Total Rooms Revenue" by placing a check mark in the box.

4. Click "OK"

5. Once desired "items" are selected, drag the "Items Tab" down to the "Rows" Field box.

Finally we will move to "Value" field.

1. Click on the Triangle next to "Period" located under "Fact Period" section to reveal the drop down list; this will be our Measure.

2. Place a check mark in the desired measure that you would like to see. For this example we will select "Amt".

3. The measure selected will automatically be populated in the "Values" field.

Now that we have all 5 elements selected we are ready to view our report. Below is a snippet of where each of these items appear on the truncated report.

From here the report can be expanded and the user can format the report as needed and change some of the parameters to better suit the user's needs.

Once you have the report set-up to the user's satisfaction you can now begin the conditional formatting process. **Advanced knowledge of Excel is required to be able to navigate the report quickly and efficiently.

1. Click on "Analyze" on the excel spreadsheet.

2. Click on "OLAP Tools" drop down.

3. Select "Convert to Formulas"

4. The system will display a pop-up box asking the user to confirm their selection. Place a check mark in the "Convert Report Filters" box in order to convert all the selections to Excel Formulas. For some users, they may prefer to leave the filters connected to the pivot table for easier navigation of data, however keep in mind that this may not display correctly when sent to a user who does not have the connections live on their workstation.

5. Most Excel versions will give an error message as shown below. To correct the error, Right click next to the box(es) that contain a main "Total Column" sections and select "Field Settings".

6. Select "Automatic" and then click on "OK". Repeat this for each of the boxes adjacent to the "Total Column" if needed.

7. Once complete, the Excel spreadsheet will begin refreshing the data as below.

8. Once the Data loads, it is a best practice to create a drop down list for the modes selected. This will make it easier for the user to make changes to how the data displayed.

a. In a blank column, enter "Amt", "QTY" and "Rate" (or whichever modes were selected when creating the pivot table) as shown in the image below
b. Click on the cell where we want to create the drop down list. This will be the cell that contains the default mode measure.
b. Click on "Data"
c. Click on "Data Validation"