Modelling Tutorials will guide you through the development of some core components to a Corporate Planning model such as Operating Expenditure, Salaries and Wages, Travel and Depreciation planning.
Each of the tutorials has an estimate of how long the tutorial is likely going to take to complete. You can deviate from the tutorial at any point to customise the model for your business.
Note: The following tutorials assume you have read the initial tutorials about the interfaces found inside the MODLR cloud solution.
Creating an Operational Expenditure Model (in 45min)
Figure 1.0: A simple Profit and Loss model overview.
Profit and Loss Models are the core central model for Corporate Planning. All other models calculating specific areas of Expenditure and Revenue feed their resulting balances into this model which aggregates and reports on the resulting corporate plan.
The following tutorial demonstrates how easy it is to collect information from department managers who are geographically distributed without using Spreadsheets and Email. The aim is to provide the user with an understanding of cube linking functions (vlookup type formulae) to bring together results of planning performed in other models and data validation (drop-down lists).
Files that are used in this tutorial:
Creating an Operational Expenditure Model
We will begin by creating a new model from our home page. Let's create a new model named "Financial Performance" with the standard settings.
Figure 1.1: Creating a new model
You can see that the new model presently has no cubes or dimensions by default.
Creating a Dimension
Let’s create some of the necessary dimensions. Creating a dimension is very simple, we can use Actions on the top right of the page.
Figure 1.2: Creation of a new dimension
Once we hit the Create button, we are taken to a page where we can specify the hierarchy. We use tab spaces to indicate the dimension hierarchy.
Alternative hierarchies allow for multiple ways of reporting on the underlying information within a cube. We will create a dimension called Month with type Standard.
Figure 1.3: Month dimension
We will make a month dimension with two hierarchies. You can copy the hierarchies from the Sample Dimensions file. First is a list of months for the Default hierarchy:
Figure 1.4: Month Default Hierarchy
Click on the Save button to save the Default Hierarchy.
Next, to create the Quarters hierarchy by going to Actions → New Hierarchy.
Figure 1.4.1- Creating a New Hierarchy from Dimension Management page
Name the hierarchy as Quarters and set its contents as follows:
Figure 1.4.2: Month Quarters Hierarchy
The annual element at the bottom will be used to hold annual assumptions. Once we have entered the values in a hierarchy, we will hit the Save button.
Creating the Year Dimension
We will create a Year dimension. Set the name of the hierarchy to Default and paste the following values into the hierarchy editor:
Figure 1.5: Year Dimension
Creating the Department Dimension
The Department dimension will have two hierarchies, one for the full company structure and another which just focuses on our Profit Centres which are our storefronts and our online department.
Figure 1.6: Department Dimension - Default Hierarchy
Figure 1.6.1: Department Dimension - Retail Stores Hierarchy
Creating the Account Dimension
The Account dimension is a cut-down Profit and Loss structure.
Figure 1.7: Account Dimension
Creating the Scenario Dimension
For the Scenario dimension, we will choose the dimension type as Scenario.
Figure 1.8: Scenario - Dimension type
Please paste the following values into the hierarchy editor:
Figure 1.8.1: Scenario - Dimension Default Hierarchy
Creating a Cube
Now on the Model page, we can see all of the dimensions created so far.
Figure 1.9: Model Dimensions
We will proceed to the creation of a cube. We will use the Actions menu on the top right and click on New Cube.
When we create the cube, we will also be creating the final dimension, the Profit and Loss Measures dimension.
To add an existing dimension to our cube, we can either double click on the dimension name or use the forward arrow button after selecting a dimension. Once it's done, we will hit the Create button.
Figure 1.9.1: Creation of Cube
In our model page, we can see that the cube has been created. We will now modify the Measures dimension by hitting the Manage adjacent to the Measures dimension.
Figure 1.10: Manage dimension
Add the following values to hierarchy:
- $ - Thousands
“$” will hold the financial amounts and “$ - Thousands” will calculate the financial amounts in thousands. Once we are done modifying, we will hit the Save button.
Figure 1.11: Editing a dimension
Our cube is all ready to go. Let's take a look at the cube by clicking the Open button adjacent to the cube from our model page. Since we haven't uploaded any data the cube appears empty.
When opening a cube directly, MODLR automatically decides where the dimensions will be placed, but you can change these using the left-hand Dimension management panel.
Let's make a view showing financials for each department by month for FY17.
We can drag and drop the dimensions to rearrange them.
Figure 1.12: Cube view
We can expand groupings within a dimension by clicking on it. But if we want the default view to show all elements or a specific set, we can double click on the current selections and change the instructions.
This dialog lets us provide a list of instructions which determine what data will show within the report. We can double-click on the items under the Instruction Dictionary column to include them in the Set Instructions.
For now, we will simply expand all Departments. We will use the Reverse instruction before the expand all instruction so that the totals are shown at the bottom and click Save.
To rearrange the instructions, we can simply drag and drop them.
Figure 1.13: Instructions
For the Month dimension, we will mix it up a bit. We will rearrange the dimensions Month and Profit and Loss Measures
We will double click on All Months on the column header to open the dialog. We will add the 12 months, followed by a blank space and the full year total.
To add all 12 months, we will expand All Months on the left panel in the dialog and select all and hit Add to Selection Instructions
We will use the instructions to add a blank space and rearrange it as shown in the screenshot.
Figure 1.14: Instructions for Month dimensionFigure 1.14: Instructions for Month dimension
We will also update the instructions for Account dimension present in the filters at the top, so that any account is selectable.
Figure 1.15: Instructions for Account dimension
Adding Data to the Report
Data can be entered into the cube as long as none of the corresponding elements to the selected cell have children.
To enter data, simply double click on a cell. If you can’t enter data on the cell, make sure that the filters selected are not parents like in the following screenshot:
Figure 1.16: Choose filters that have no children
We can see that the various dimensions all consolidate the information based on the hierarchies we defined earlier. At any time, we can add additional hierarchies into dimensions to allow for even more flexible reporting.
Figure 1.16.1: Entering data
We will save the Workview by right-clicking on the column header and selecting Duplicate Workview. We will duplicate twice and name the first one as Departments by Month and the second one as Annual Profit and Loss.
Figure 1.17: Duplicating the Workview
If dimensions on the left side are hidden, we can click on the toggle button on the top to display them.
Figure 1.17.1: Manage (Toggle) Dimensions button
On our Annual Profit and Loss workview, we will swap the dimensions Department with the Account Dimension to provide a Profit and Loss view.
We will load the Actuals for Financial Year 2017 that we downloaded earlier.
From our Model page, we will click on Manage data which takes us to the data store page.
Figure 1.18: Manage Data
Click on Upload into new Table and click "Choose File" and select the sample csv file downloaded earlier and hit Submit.
Figure 1.19: Uploading data
We can see the data reflecting on our page now. Data within the data store can be added to, or updated at any time manually or by uploading another csv file of data.
We can also filter the dataset by clicking on the heading. Additionally, other systems can connect to the datastore and push data in for MODLR to process.
Now that the data is held within the internal data-store, we will create a data process to import it.
From our Model Page, we will create a new Data Process by going to Actions → New Data Import (Process).
We will provide the name of our cube. The data source should be set to our internal data store and the tables list will automatically select the first table, which happens to be the one we just uploaded.
Figure 1.20: Data Process
We will be updating the mappings so that our data fields match each of the dimensions within our cube
Note: If we miss any, the process will warn us that it will need to make changes to the cube, this is a good indication that we have missed a dimension.
Figure 1.21: Mapping the Data
We will name the process Data - Profit and Loss FY17 Actual and hit Validate & Save. Once it's done, we can click on Execute Process and click on Confirm Execution.
Figure 1.21.1: Validate and Execute the Process
Now let's head over to the logs page.
Figure 1.22: Go to the logs page through the top navigation
On the logs page, once we refresh, we can see that the data has been loaded successfully.
Figure 1.22.1: Process Logs
We will open our Workview from our model page to see how it looks.
Note: We can download an excel report by clicking on at the top right of our Workview.
Figure 1.23: Workview with updated data
We will add some basic formula to our cube. Open the default workview by clicking on open next to our cube in the Mode page.
We will add a formula to calculate $ - Thousands. Right click on the highlighted cell and select Edit Formula to open the editor.
Enter the formula ["$"]/1000 and click Save Formula.
Note: To choose the value of $, we can simply click on the cell while we are on the formula editor. This will insert the ["$"]part.
Figure 1.24: Formula for $ - Thousands
Template for Operating Expenditure
Double click on 800 - Profit and add the instructions shown in the screenshot by using the dropdown present in the Instruction editor. Click Save.
Figure 1.25: Editing instructions for Account Dimension.
Let's rearrange the dimensions to put Account on rows and everything else on columns.
Figure 1.26: Arranging the dimensions.
We didn't cover the concepts of Sets earlier. We will add a set to the left of the rows header and then two to the right. Each set is independent of the other sets and can have its own instructions.
We will begin by adding by inserting columns. Right click on the highlighted cell in the screenshot and select Insert column before selection under Change Table.
Figure 1.27: Inserting Columns.
Double click on a Year cell to open the Set Instruction dialog. Remove what's in there and add just FY 2018. Hit Save.
Figure 1.28: Adding FY.
Then right click on the last column on the right side, and click Insert column after selection twice to add two columns.
Figure 1.28.1: Adding Columns.
Double click on the cell in the top-right corner and set choose only "FY2018" and hit Save. Then, double click on the cell underneath it along the All Months row, select all elements under All Months. See the following screenshot for reference.
Figure 1.29.1: Adding Months.
Double click on the highlighted cell and select Budget under Default and hit Save.
Figure 1.29.2: Adding Scenario.
Double click on the highlighted cell and remove existing instructions. Then select $ under Default and hit Save.
Figure 1.30: Remove existing set instruction and choosing $.
Let's repeat that on the section on the right-hand side. Double click on the highlighted cell and remove existing instructions. Then select $ under Default and hit Save.
Figure 1.30.1: Choosing $.
Double click on the highlighted cell and remove existing instructions. Then select Budget under Default and hit Save.
Figure 1.31: Remove existing set instruction and choosing Budget
Double click on the highlighted cell and remove existing instructions. Then select Annual under the Quarters hierarchy and hit Save.
Figure 1.32: Remove existing set instruction and choosing Annual under Quarters.
Double click on the highlighted cell. Then select Add Blank Space from Instruction Dictionary and then hit Save.
Figure 1.33: Add Blank Space
The workview should now look like the following:
Figure 1.33.1: Our progress so far.
Comparing Data Against Actuals for 2017
Double click on the highlighted cell on the top left. Then select FY2017 under Default and click on Add to Selection Instructions and hit Save.
Figure 1.34: Adding FY2017.
Double click on the highlighted cell on the top left. Then select All Months under Default and hit Save.
Figure 1.35: Adding All Months.
Double click on the highlighted cell on the top left. Then select Actual under Default and hit Save.
Figure 1.36: Adding Actual.
Double click on the highlighted cell on the top left. Then select $ under Default and hit Save.
Figure 1.37: Adding $.
Let's duplicate/save Workview by right clicking on a column and selecting Duplicate Workview and name it as FY18 Opex Budgeting and hit Save.
Figure 1.38: Saving Workview.
To hold the Phasing method, we will need a new measure element. So we will leave the workview and modify the Profit and Loss measures dimension from our Model page.
We will add Phasing[S] under Default. The [s] tells Modeller to add the element as an element which holds string input.
Figure 1.39: Phasing Measure.
Creating Phasing Dimension
From our Model page, click on New Dimension from Actions Menu and name it Phasing and hit Save.
We will add Manual Entry, Even Phasing, Prior Year Trend which we will build now, and Calendar Days and Headcount which we will cover later.
Figure 1.40: Creating Phasing Dimension.
We will store the phasing method and amount in the middle columns set.
Open FY18 Opex Budgeting workview from our Model page.
Double click on the highlighted cell and choose Phasing under Default and click Add to Selection Instruction and hit Save
Figure 1.41: Adding Phasing to Set Instruction.
By using the data validation option we can point the "Phasing" measure to the "Phasing" dimension we just created. This limits the available options to the specific elements from the Phasing dimension.
Select the highlighted cell and click on the icon from the top right header. The data validation dialog will show up.
Then select Dimension: Phasing»Default against Validation and hit Save Validation
Figure 1.42: Validation Rule.
From the Department Filter, choose Finance
Click on the highlighted cell and choose Even Phasing from the dropdown and enter -80000 as the value in the cell to the left against Rent.
Figure 1.43: Even Phasing.
Now we can add our first Phasing rule to calculate even phasing across the Profit and Loss cube.
To begin, we will right-click on any of the months within the planning year and select "Edit Formula".
We will restrict this to only the months within our default hierarchy, otherwise, the rule would apply to the annual element also which would cause a circular reference. We will also restrict it to the Budget Scenario. The formula is already restricted to the dollar measure, which is correct.
Figure 1.44: Formula Editor.
Then click on Month - Don't Restrict and choose the Month - Restrict to the lowest elements in the Default Hierarchy, then Scenario - Don't Restrict to Scenario - Restrict to Budget
Then, enter the following formula:
IF(["Annual", "Phasing"]="Even Phasing", ["Annual"]/12, CONTINUE)
Set the description to Even Phasing.
Click "Save Formula" to apply the formula.
Figure 1.45: Even Phasing Formula
Making the Report look nicer
The rule reads, if the annual phasing method is "Even Phasing" then take the "Annual" phasing amount and divide it evenly by 12, otherwise, continue. The continue function allows the cube to look for other rules which may also affect this particular cell.
As we click save, the formula takes effect immediately and we can see the amount, negative eighty-thousand is being evenly phased across the budget year.
Figure 1.45.1: Even Phasing Formula works.
If we change the amount to Phase, the budget will automatically update in real-time.
We can use the explain value function on any cell to reveal the calculation logic taking place under the hood.
Right click on a value calculated by a formula and click Explain Value
Figure 1.46: Explain Value.
Prior Year Trend formula
Presently if we set the Phasing to "Prior Year Trend" no formula is applied and so I can change the value of the cell.
This formula is a bit more complicated. We could simplify it by writing a specific formula for each month but that would be a bit inefficient.
Instead, we will use the LINK function which can refer to a relative cell within this or any other cube.
Note: If when you right click to edit the formula you see the "Even Pashing" formula instead of seeing an empty formula, go to Formula List and click on "Add New Formula".
Figure 1.47: Prior Year Trend formula.
This rule begins in a similar way, first, we restrict the cells which the formula applies to as shown in the screenshot.
IF(["Annual","Phasing"] = "Prior Year Trend", LINK("Profit and Loss",["FY2017","Actual","$"]) / ["FY2017","All Months","Actual"] * ["Annual"],CONTINUE)
Figure 1.47.1: Prior Year Trend formula.
The formula will check that the phasing method is indeed "Prior Year Trend" in which case it will take the same month from the prior years actual.
This link function sources information from the specified cube at the specified location, any dimensions which are not represented in the location argument will refer to the target cells relative location.
The resulting value will be used as a portion of the prior year's total value and multiply that by our phasing amount.
As I save that we can see that the annual budget matches our phasing amount but now the months are populated using the relativity from the prior year's actuals.
We can change the phasing method and our plan will recalculate in real-time.
If we explain the calculated cell, we can see that it depends upon the values in each month of the prior year, it works out the given month's portion of last years actuals and multiplies that by our phasing amount.
Figure 1.48: Prior Year Trend explanation.
We can add a new custom header to the report on columns from the panel on the left side.
Figure 1.49: Custom Header.
By double-clicking on the custom heading we can type in a new value to display for each column. Make sure to add double quotes around the text.
Figure 1.50: Heading Editor
Hiding Dimension Headers
If we combine the custom heading with an advanced function to hide the actual dimension headers, we can reduce the clutter on the page dramatically.
Double click on the highlighted cell and add Hide Dimension Headers from the dropdown and hit save. We will repeat this process for all header rows except for custom heading.
Figure 1.51: Hide Dimension Headers.
Since we are polishing the planning template now, we will add a function to hide account rows which have no historic spend, so that the business can focus primarily on only the accounts which are relevant to them.
Double click on the highlighted cell and add the instructions shown and hit Save.
Figure 1.52: Hiding empty values.
Disable Set Instruction
If we need to make changes to the dimension sets which have been hidden we can pause the set instructions across the workview, this will prevent all the advanced instructions from processing and ensure that we can still see and modify the sets.
Figure 1.53: Disable Set Instruction.
One last step is to fix the account set so that it displays the totals at the bottom of the planning template.
Double click on the cell and rearrange the instructions and hit Save.
We can now change to another department and see that the planning template dynamically displays the accounts necessary for planning.
Figure 1.54: Reverse Total.
Creating a Travel Planning Model (in 30min)
Figure 1.0: A simple travel model overview.
Collecting and Calculating the Travel Expenditure is an area of corporate budgeting where MODLR can assist easily. The following tutorial demonstrates how easy it is to collect information from users who are geographically distributed without using Spreadsheets and Email. The aim is to provide the user with an understanding of cube linking functions (vlookup type formulae) and data validation (drop-down lists).
In this tutorial, we will be re-developing a basic Excel Spreadsheet used to collect Travel Plans from various business units in MODLR to present the various benefits of using the MODLR Solution. You can download a copy of the example spreadsheet here.
Creating Year Dimension
Within your chosen model, create a dimension named Year by going to Actions → New Dimension and then use the settings provided in the corresponding image and hit Create.
Figure 1.1: Creating Year Dimension
Provide the hierarchy name as Default and enter the following values:
Figure 1.1.1: Year Dimension Hierarchy
Creating Month Dimension
We will create a new dimension called Month using the settings provided in the corresponding image and hit Create.
Figure 1.1.2: Creating Month Dimension
Provide the hierarchy name as Default and enter the following values and hit Save.
- All Months
Figure 1.2.1: Month Dimension - Default Hierarchy
Create another hierarchy named Quarters by going to Actions → New Hierarchy and enter the following values and hit Save.
- Full Year
Figure 1.2.1: Month Dimension - Quarters Hierarchy
Creating Trip Dimension
Each individual trip will be entered on a separate row in the data entry screen. These “Trips” need to be added as a dimension in MODLR.
We will create a dimension named Trip and leave the dimension type as Standard.
Figure 1.5: Creating the Trip Dimension
Name the hierarchy as Default and then place the following element list in the large text area:
- All Trips
We can use tab spaces to indicate the dimension hierarchy. Click Save to build the dimension and add it to the Cube.
Figure 1.6: Trip Dimension Hierarchy
Creating Scenario Dimension
Create the Scenario dimension using the Scenario as the Dimension Type and hit Create.
Figure 1.3: Creating Scenario Dimension
We will add the following values for our hierarchy and name it as Default.
Figure 1.4: Scenario Dimension Hierarchies
Adding a Department/Cost Centre Dimension
Now we are going to add another dimension which represents the Business Structure. If you are customising this travel model for your business, you can easily substitute your business structure for the example we have provided here.
We will name it as Department. Leave the dimension type as Standard and then hit Create. Name the hierarchy as Default and paste the following.
- All Departments
- Sales - Online
- Sales - Retail
- Sales - Channel
- Field Operations
- Information Technology
Figure 1.7: Departments
We will create a new Dimension named for destinations. This will reflect the sample spreadsheet's Corporate Rate Card - Travel Worksheet rows.
Name the Dimension Destination and leave the dimension type as Standard and hit Create. Then name the hierarchy as Default and paste the following.
- Hong Kong
- New York
Click Save to update the dimension and return to the model page by clicking the Close button.
Figure 1.8: Creating Destination Dimension.
Creating the Travel Cube
Within your chosen model, create a Cube by going to Actions → New Cube. Set the Cube's Name to Travel and leave the Measure Dimension field as is. For the Cube Dimensions, select the all available dimensions except "Destination" as shown below.
To do that, simply double click on the dimension on the Available Dimension column or use the arrow buttons.
Figure 1.9: Creating Travel Cube
Creating the Travel Planning Workview
Now that we have our cube and dimensions ready, we will create a Workview.
Within your chosen model, create a new workview by going to Actions → New Workview (Report).
For the Action field, choose Create a new workview on an existing cube and then select the Travel cube from the dropdown.
Figure 1.10: Creating a new workview
We can now see a new workview based on the Travel cube. The workview includes the dimensions we created earlier.
Figure 1.11.1: Travel Planning workview
To rearrange the dimensions, simply drag and drop them to alter their positions.
Figure 1.11.2: Rearranging Travel Planning workview dimensions
NOTE: If certain parts of your workview become read-only, check any dimensions under Hidden Dimensions and ensure that the hidden dimensions’ first element is not a parent. Values cannot be entered into the cube when the dimension element is at a parent level.
Figure 1.11.3: Check Hidden Dimensions if cells are read-only
Double Click on the left-most heading (Grey Box) in the central grid. This grid cell represents a set from the “Trip” dimension since this dimension is “On Rows”.
You will be presented with the “Set Editor” and the “Default” hierarchy from the “Trip” Dimension.
Figure 1.12: The Set Editor Window
First, we will remove the existing instruction Set - [All Trips] (x1) by clicking on the x button next to it.
Now, Click on the plus icon next to the “All Trips” element. This will show the individual trip numbers 01-15.
Drag to select all the items including All Trips and click on the Add button under the Dimension Structure column. Rearrange the set instructions as shown.
Click the Save button to select all visible elements in the window.
Figure 1.13: All Trips
The workview will update to display the selected elements from the Trip dimension On Rows.
Figure 1.14: The updated workview
Updating the Measures Dimension with Travel Measures
The "Travel Measures" dimension presently "On Columns" still contains the default measure which is the default for a new cube. We need to update these to reflect the Excel Model we are rebuilding in MODLR. To edit the dimension click the icon next to the dimension "Travel Measures".
Add the following elements into the large text area:
- Travel Description[S]
- No People
- No Nights
- Travel Expenditure
- Flight Costs
- Accommodation Costs
- Transit Costs
The [S] added either before or after the measure name denotes that this is a String measure and holds text.
Figure 1.15: Editing a dimension
Configuring the Measures
Now that the Measure dimension has been updated for travel planning we can update our workview to report on the new measures we have just created.
Double click on the topmost grey column to open the Set Editor.
Expand "Travel Expenditure" to display the child elements.
Drag all the items to select them and click on the Add button under the Dimension Structure column. Rearrange the set instructions as shown in the screenshot.
Click "Save" to save all visible elements into the Set
Figure 1.16: Updating the "On Column" Set
The workview will update to reflect the new Set selection for the Measures Dimension.
Figure 1.17: Resulting Workview
If you are not customising your business structure (Department / Cost Centre) you will need to change the Department to Finance to match the screenshot.
In the filters at the top, if you would like to change which values are being displayed, you can modify the set by clicking on the highlighted button shown in the screenshot.
Figure 1.18: Set Editor
To increase a column’s width, right-click on the specific column and select “Change Column Width”.
We are now going to increase the “Travel Description” column’s width. This will give users more space to type.
Figure 1.19: Header Context Menu (Right Click)
Set the new width to 220 pixels.
Figure 1.20: Change Column Width Window
Note: The workview is write-enabled. A write-enabled cell will have a white background. If you are unable to enter data, please ensure that the dimension element chosen in the filters are either elements without children or child elements.
Figure 1.21: Entering data into the Cube
Adding Data Validation
Within the “Trip Planning View,” we can now make drop lists which restrict what options users have when editing Text fields. First, we will make the “Type” field provide the user with only “International” and “Domestic” options. Then we will make the “Destination” field restrict input based on the selected “Type”.
Select a white cell under the “Type” measure.
Click on the “Data Validation” toolbar icon
Figure 1.33: Data Validation.
The Data Validation window will show for the Measure “Type”. Note: This represents the entire column, not the single cell.
Within the validation field select the “Destination” dimension and “Default” hierarchy as the basis for Validation. Leave the other settings on their default values.
Click “Save Validation”.
Figure 1.34: Data Validation for Type.
Test the Data Validation by Double Clicking on a white cell under “Type”
Figure 1.35: Testing the Validation.
Select a white cell under the “Destination” measure. Click on the “Data Validation” toolbar icon
Figure 1.36: Destination measure Data Validation.
Since we want to restrict Destinations based on the value in the “Type” column. Set the “Level” field to “Top Level -1” (which means one level below the topmost elements). In the “Parent Measure” field, set the value to “Type” (since this is the name of the Measure we are filtering on). Click “Save Validation”.
Figure 1.37: Type Parent Measure
Test the Data Validation by Double Clicking on a white cell under “Destination” where the “Type” column is not empty.
Note: if the corresponding “Type” cell is empty then no filtering will be done. The “Destination” list will show all destinations.
Figure 1.38: Testing the Validation.
Enter some numbers into the "No People” and "No Nights” measures. We will use these to test the calculations we are about to add.
Figure 1.39: Entering Information into a Workview.
Pressing the left and right arrow while entering values will start editing the corresponding cell however the data is not saved to the MODLR Cube until you conclude editing a field with the "Enter” key.
Figure 1.40: Entering Information into a Workview.
Creating Another Cube and Workview for Travel Rates
Now that the Travel cube has all the dimensions we need to plan travel expenses, Its time to build a separate workview and cube to hold the corporate rate card for Airfares, Accommodation Expenses and Allowances.
Note: There are many ways to store assumptions and rates in MODLR as the environment is free form. We suggest segmenting rates and assumptions into their own cubes where possible to make it easier to see the flow of business logic.
Close the Workview and return to the Model Overview by clicking on the top-right Close button. You can see the existing dimensions, workviews and cubes from our model.
Figure 1.22: The Model Overview
Creating the Travel Measures Cube
Within your chosen model, create a Cube by going to Actions → New Cube. Select the Destination and Scenario dimensions by double-clicking them. Hit Create to save the Cube.
Figure 1.23: Creating Travel Measures Cube
From our Model page, Create the new workview called "Rates Entry" by going to Actions → Create New Workview (Report) from the top right.
We will choose Create a new workview on an existing cube and then select the Travel Rates cube from the dropdown. Name the Workview as Rates Entry
Figure 1.24: Creating a new Workview
Modifying Travel Rates Measure
Edit the “Travel Rates Measures” dimension to reflect the Sample Spreadsheet Rate card Worksheet columns.
To edit the dimension, from our Model page, expand Dimensions and click Manage next to Travel Rates Measure dimension. Then enter the following values.
- Flight Costs
- Accommodation Costs
Click “Save” to update the dimension and close the window.
Figure 1.25: Editing the Measures Dimension.
Let's open our Rates View workview from the model page.
Now, edit the “Destination" Set. Double-click on the “Destination” header cell on the left of the grid. Remove existing instructions by clicking on the x button next to them.
Expand both International and Domestic elements to display their children and drag to select all of them and click on Add to Selection Instructions.
Figure 1.26: The Set Editor
The resulting workview should appear the same as in the screenshot on the right.
Figure 1.27: The updated workview
We will copy the rates from the sample Excel Travel planning model into the Travel Rates cube in MODLR. You can download a copy of the Excel Example at the top of this tutorial.
Open the Excel file and select the numeric rates (check versus the screenshot).
Copy the Rates to the clipboard.
Figure 1.28: The MS Excel Spreadsheet.
Select the cell at the intersection of “Auckland” and “Flight Costs” and press “CTRL+V” to paste the numbers from Excel into the MODLR cube.
Figure 1.29: The updated workview.
Note: MODLR automatically aggregates numbers from elements into their parent elements. As an unintended consequence “International” is the sum of all the International Destinations below it.
Now that the Travel Rates have been saved, lets review and update the Travel cube with the Expense Calculations.
Close the Workview and return to the Model Overview by clicking on the top-right Close button.
Open the “Trip Planning View Workview”.
Figure 1.30: The Model Overview.
In the filters at the top, if you would like to change which values are being displayed, you can modify the set by clicking on the highlighted button shown in the screenshot.
You can choose different values for dimension filters.
Figure 1.31: Dimension Filter.
Adding Formula to a Cube (Business Logic)
To complete the Travel model, it will need to calculate "Flight Costs", "Accommodation Costs" and "Allowances" while allowing "Transit Expenses" to be entered manually.
To open the formula editor right-click on a "Flight Costs" white cell and click "Edit Formula".
Figure 1.41: Creating a New Formula.
Within the Formula Editor, leave the restrictions as they are but copy the formula from below and paste this into the Formula field.
LINKBY("Travel Rates",["Flight Costs"],["Destination"])*["No People"]
Click "Save Formula" to apply the formula.
Figure 1.42: Formula Editor.
Right-click on an "Accommodation Costs" white cell and select "Edit Formula". Within the Formula Editor, leave the restrictions as they are but copy the formula from below and paste this into the Formula field.
LINKBY("Travel Rates",["Accommodation Costs"],["Destination"])*["No People"]*["No Nights"]
Click "Save Formula" to apply the formula.
Figure 1.43: Formula Editor.
Right-click on a "Allowances" white cell and select "Edit Formula". Within the Formula Editor, leave the restrictions as they are but copy the formula from below and paste this into the Formula field.
LINKBY("Travel Rates",["Allowances"],["Destination"])*["No People"]*["No Nights"]
Click "Save Formula" to apply the formula.
All the calculations should now be applied and you can change the "Destination" or other Measures values to test the new calculations.
Figure 1.44: Formula Editor.
Making the Report look nicer
Open the Set Editor for the “Trip” Dimension by double clicking on any of the header items (grey cells) on the left-hand side of the grid.
The instructions panel on the right-hand side shows the current selection in the set. In order to update this set, we need to remove the current instruction.
Click the “x” next to the instruction to remove it.
Figure 1.45: Set Editor.
Select “All Trips” and click “Add to Selection Instructions”.
Figure 1.46: Set Editor.
There should be one instruction which is that the single “All Trips” element is added to the Set.
Figure 1.47: Set Editor.
Use the Instructions Dictionary to find and the same set of instructions as in the following screenshot. You can add them by double-clicking the instruction under Instruction Dictionary.
Figure 1.48: Set Instructions.
The resulting Set has the trips at the top, a blank row and then the total travel line for this Department at the bottom.
Figure 1.49: The updated Workview.
Review the Model
Close the current workview as it is ready for collaborators to plan with.
Note: The Model Overview now displays the link between the "Travel" and the "Travel Rates" cubes. This is because the formulas we added push data from the "Travel Rates" cube into the "Travel" cube in real time.
Figure 1.50: The Model Overview.
Build a Departmental Workview
Figure 1.51: The Model Overview.
Create a new Workview to report on the Travel Expenditure per Department by Month.
Figure 1.52: Departmental Workview Creation.
Pivot the Workview to display “Department” under “On Rows” and “Month” under “On Columns”. It is easy to create a quick and simple Departmental Report.
Figure 1.53: Departmental Workview.
Note: If you find any issues with this Tutorial please let us know via the Community forums. We love hearing from our Modellers.
Creating a Revenue Model (in 30min)
Earlier, we developed our operating spend phasing formula for even phasing and last year trend. Now we will start working on a revenue model.
Our revenue model will have two components. The first is a pricing cube which will allow for planning both sell price and cost price by product. The second is our Sales cube within which we will enter our sales targets by product and store. The resulting revenue and cost of goods sold will be pulled back into the profit and loss cube.
Figure 1.55: Revenue Model.
Creating Product Dimension
To begin let's create the Product dimension by providing the name as Product and choosing the dimension type as Standard
We will add two hierarchies, one that lists totals by Brand (Default) and another which is a simple list of Products (All Products). You can copy paste the hierarchies from the Dimensional Data file downloaded earlier.
Figure 1.56: Product Hierarchy - Default
Figure 1.56.1: Product Hierarchy - All Products
Creating Pricing Cube
Next we will create our Pricing cube. Since, in this tutorial, our prices do not vary by store, we will not need the department dimension.
Including the Scenario dimension allows us to run multiple scenarios of the plan which can simulate different pricing strategies.
Figure 1.57: Pricing Cube
We need to add some measures to the Pricing cube measures dimension. So from our model page, click Manage next to Pricing Measures
Figure 1.58: Pricing Measure
We need to add some measures to the Pricing cube measures dimension. So from our model page, click Manage next to Pricing Measures.
Since we would ideally like to only enter the prices once per product unless they change at some time during the planning period, we will make the prices carry forward from one month to the next. So let's add Retail Price and Cost Price and another measure which will act as an override for each of these in a given month.
Figure 1.59: Pricing Hierarchy
Let’s take a look at the new Pricing cube and create our planning template for Pricing. For this template it would be good to see the Retail price and Cost price for each product, along with the override measure for each of these.
We will open our Pricing cube and rearrange the dimensions as shown in the image.
Figure 1.59.1: Pricing Cube
Double-click on the highlighted cell and choose All Products hierarchy, expand and drag all the products shown under and click on Add to Selection Instructions. Then remove the existing instructions to depict the image shown and hit Save.
Figure 1.59.2: Product Dimension
Double-click on the highlighted cell and choose the Quarters hierarchy. Remove the existing instructions and add Annual to the Set Instructions by double-clicking on it. Then, add the Add Blank Space instruction.
Choose Default hierarchy and expand All Months. Now drag and select all the months under it and click on Add to Selection Instructions and hit Save.
Figure 1.59.3: Month Dimension
We will have the starting price for the year stored in the Annual month element,and we will write a formula shortly which pushes this into the months.
Select Budget from Scenario and the Year as FY2018 from the dimension filters at the top.
Enter 40 as the value against the cell at the intersection of Annual and Retail Price Override for Pencil.
To create the formula, right-click on the target cell (highlighted in yellow) and select "Edit Formula". Set the description as Retail Price - Jul. We will restrict the formula to the "Retail Price" measure, the month of July and the Budget scenario. (Restrict it as shown in the image) The formula will take the override if it has been entered, otherwise it will take the annual retail price.
IF (["Annual", "Retail Price Override"] = 0, ["Annual"], ["Annual", "Retail Price Override"])
Figure 1.60: Formula
We will add the same for the Cost price measure.
Right click on the target cell (highlighted in yellow) and select "edit formula" and add the formula as shown on the screenshot.
IF (["Annual", "Cost Price Override"] = 0, ["Annual"], ["Annual", "Cost Price Override"])
Figure 1.60.1: Formula
Once the formula has been added, set a value (26) against Cost Price Override for July and then for Cost Price and see how the formula affects it. You will notice that if Cost Price Override value has been entered, that value is taken by the formula.
So that we can have the prices rolling from one month to the next we will need a hierarchy which acts as an ordered list of months. We will feed this list into a Sequence function in our formula which rolls the prices.
From our model page, click Manage next to the Month dimension and then create a new hierarchy named Month List
Figure 1.61: Month List
We need to refresh the report so that it picks up the new hierarchy before writing this formula.
Right click on the highlighted cell and edit formula. Enter Description as Retail Price - Rolling
The formula will be restricted to the Months Default Hierarchy, the Budget Scenario and the Retail Price measure.
IF(["Retail Price Override"] = 0, SEQUENCE("Month", "Month List", -1, ["Retail Price"]), ["Retail Price Override"])
The equation will check if there is an override for a given month and if so, it will use this. If there is no override, it will get the previous months price
Figure 1.62.1: Rolling Price Formula
Now, as you can see, if we change the price in a given month using the override measure, the used price in this month will reflect the new price. This final price will be used to calculate revenue in our Sales cube. Now we will write a similar formula for Cost Price as shown in the screenshot.
IF(["Cost Price Override"] = 0, SEQUENCE("Month", "Month List", -1, ["Cost Price"]), ["Cost Price Override"])
Figure 1.62.2: Cost Price Formula
We will save this workview as Price Planning by right clicking on the column header and clicking on Duplicate Workview
Figure 1.63: Saving Workview
We haven't added the actual prices into the cube yet, so let’s create a view in which we can paste our current product pricing for the year.
From our model page, click Open next to Pricing cube.
Figure 1.64: New Workview
Double-click on the highlighted cell and select Retail Price Override and Cost Price Override and add to selection instructions. Then remove the existing instruction and hit Save.
Figure 1.65: Pricing Measure
Double-click on the highlighted cell and expand all the levels and drag across the values and add to selection instructions. Then remove the existing instruction and hit Save.
Figure 1.66: Product
Now select FY2018 from the Year filter and click on the highlighted icon to the Month filter. Then select Annual under Quarters and add it. Then remove existing instructions.
Figure 1.67: Month Dimension Filter
If you change the Scenario filter to Budget, you can see the values in the workview that we previously added.
From the Office Supplies Data file (named "Annual Price Sheet FY18 - Office Supplies Co.xlsx", copy the content and paste it with the highlighted cell as the selection using Control + V keys.
Figure 1.68.1: Data File
Once pasted, we can see the values being aggregated in the workview. Please note that we did not remove the manual override values that we entered earlier.
Figure 1.68.2: Workview
With that done, we can move on the the Sales cube which is where we will be planning unit sales by store.
From our model page, create a new cube called Sales with the dimensions selected as shown in the screenshot.
Figure 1.69: Sales Cube
From our model page, click on Manage adjacent to the Sales Measures dimension.
The measures will include Units, Revenue, Cost of Goods Sold, Net Sales and Margin %.
Figure 1.70: Sales Measures Dimension
We will start by making a simple reporting view showing unit sales by store.
From our model page, click Open next to the Sales cube. Then rearrange the dimensions as shown in the screenshot.
Figure 1.71: Rearranging Dimensions
For this we will use the "Retail Stores" hierarchy from the Department dimension. This will include our brick and mortar stores along with our online sales department.
Double click on All Departments and choose Retail Stores, expand and click Add to Selection Instructions. Then remove existing instructions and add Reverse (Subtotals at the Bottom) and Expand All
Figure 1.72: Department Dimension
Choose FY2018 in the Year Filter and double click on the highlighted cell and then expand All Months, select all values and add to set. Then add Add Blank Space and rearrange.
Figure 1.73: Months Dimension Set Instructions
We will also ensure that any Product or Brand is selectable. Click on the highlighted button next to Product filter and add Expand All instruction.
Figure 1.74: Product Filter
Change the dimension filters as shown in the screenshot and then duplicate the workview to save it as Sales Reporting by Store
Figure 1.75: Filter and Saving the Workview
Now we can create our planning template for unit sales. From our model page, click Open next to the Sales cube and rearrange the dimensions as shown in the screenshot.
Figure 1.76: Dimensions
Click on the highlighted button next to Department filter and remove the existing instructions from the set. Then select All Stores under Retail Stores hierarchy, add to the set and then add Reverse (Subtotals at the Bottom) and Expand All
Figure 1.76.1: Department Insruction Set
Double click on the highlighted cell, expand All Months, drag across the values and add to set. Then add Add Blank Spaceinstruction and rearrange as shown in the screenshot.
Figure 1.77: Month Dimension
Now we can create our planning template for unit sales. For this template we will use a number of row sets in order to show a different mix of measures and products down the report. Right click on the highlighted cell and go to Change Table → Insert row after selection (thrice).
Figure 1.78: Adding rows
Open the Instruction Editor on the highlighted cell and then expand All Products by Brand, add Stationmaster and add Expand All and Remove Consolidations to the Set Instructions.
Figure 1.79: Stationmaster Level
For each product in each brand we will show the units measure so that the user can enter their target unit sales. Double-click on the highlighted cell and remove existing instructions and then add just Units.
Figure 1.79.1: Units only
We will add a blank space after the Products. Double click on those cells and then Add Blank Space
Figure 1.80: Blank Space
Open Instruction editor from the highlighted cell and remove existing instructions. Then expand All Products by Brand, select Stationmaster, add to instructions.
Figure 1.81: Brand Total
After each of the products, we will show the brand total where we will show all of the measures.
Figure 1.82: Sales Measures
Let's take a break from formatting the report to calculate our measures, starting with Revenue. First, we will add some values to our workview.
Figure 1.83: Values
Right click and edit formula on the highlighted cell (in yellow). This formula will be applied to the Budget scenario only and calculate Units multiplied by the Retail Price from our Price cube.
To reference the price within the Pricing cube we need to use the link function. Since both cubes have year, month and scenario dimensions the only argument to this function is the cube name and the target measure to pull from within the Pricing cube.
["Units"] * LINK("Pricing", ["Retail Price"])
The system will automatically match up the elements from dimensions which are common between the cubes and ignores any dimensions which are in the Sales cube but are not in the linked Pricing cube.
Any changes made to the Pricing cube will now directly affect the Revenue line in the Sales cube in real time.
Figure 1.84: Linking the Cubes for Revenue
We will calculate the Cost Price measure. Right click and edit formula on the highlighted cell (in yellow).
["Units"] * LINK("Pricing", ["Cost Price"])
Figure 1.85: Linking the Cubes for Cost Price
We will calculate the Net Sales measure as well. Right click and edit formula on the highlighted cell (in yellow).
["Revenue"] - ["Cost of Goods Sold"]
Figure 1.86: Net Sales
We will calculate the Margin % measure as well. Right click and edit formula on the highlighted cell (in yellow).
With this margin calculation we could also change the type from Normal Calculation to Average or Rate Calculation. This would ensure that the formula calculates at all levels of the cube and prevents the percentages from adding up.
For now, we're going with Normal Calculation.
Figure 1.87: Margin %
Double Click on the highlighted cell and remove existing instructions. Then set up the formatting as shown in the screenshot.
We have a bunch of measures which all need different number formatting within this workview, for instance, units should be displayed as a whole number and margin as a percentage. We can use set instructions to add basic number formatting to groups of measures. The instruction Ignore new formatting on prior members acts as a break between different formats and allows us to use a number of different number formats within a single set.
Figure 1.88: Number Formatting
On the same set, add Margin % and then choose Format 0.00% for percentage values.
Now we can see that the values are all nicely formatted!
Figure 1.89: Formatted Values
We can bring in the remaining Product Brands similarly if required. The formula wouldn't need to be defined again, as we didn't restrict it.
Figure 1.90: Adding the other Product Brands
At the bottom, we will add All Products by Brand to get an overview. Add a row to the bottom and then double click on the cell, add a blank space, then select All Products by Brand and add it to the set.
Figure 1.91: All Products by Brand
Just like we did it earlier, we will be adding Measures and format them. We will save this workview as Sales Planning by Product
Figure 1.92: Measures
The last thing we need to do is link the Profit and Loss to the Sales cube to pull through our Target Revenue and Cost of Sales amounts. From our model, open the Annual Profit and Loss report.
We need to restrict the formula to the Revenue account and once again we are using the Link function to bring information in between cubes.
LINK("Sales", ["Revenue", "All Products"])
Because the Product dimension does not exist within the Profit and Loss cube, we need to specify a single element to use from this dimension. We will use All Products which in this case, will return the sum of all products revenue.
Figure 1.93: Linking Revenue
Similarly, we will add the formula for Cost of Goods Sold as well.
-LINK("Sales", ["Cost of Goods Sold", "All Products"])
Figure 1.94: Linking Cost of Goods Sold
To let the planners know why they cannot enter amounts into the Revenue and Cost of Sales accounts, we will write a formula which hard codes the phasing method for these accounts.
Let's open the FY18 OpEx Budgeting report and add a formula at the highlighted cell.
In order for MODLR to be efficient with its calculations all formulas need to refer to another cell. So in order to make the annual phasing cell display a hard coded string, we will link to the sales cube in an IF statement. In both cases of the IF statement we will return the same label.
IF (LINK("Sales", ["All Products", "All Months", "Revenue"]) = 0, "Sales Model", "Sales Model")
Figure 1.95: Phasing Method for Revenue
We will do the same for Cost of Goods Sold.
IF (LINK("Sales", ["All Products", "All Months", "Cost of Goods Sold"]) = 0, "Sales Model", "Sales Model")
Figure 1.96: Phasing Method for Cost of Goods Sold
We now have a simple Revenue planning model connected to our Profit and Loss cube.
Note: If you find any issues with this Tutorial please let us know via the Community forums. We love hearing from our Modellers.