In this tutorial video series we will guide you through developing a collaborative annual budgeting model for Whizz, a stationery company, including Revenue, OPEX, Labour, Marketing and Balance Sheet modules. We begin with Whizz's Revenue Planning module, which comprises of a price planning, discount planning and volume planning work-view; this includes a technical guide on building dimensions, cubes and processes; and writing formula to calculate specific cells.

Additional Instructions

To follow along with this tutorial you can sign up to a free trial account on MODLR by registering an account here, you can find the formulas and sales file data in the instructions below.

• Download Sales File
• Download Formulas
• Download and Print Instructions

We will set up three workviews (or reports), which will allow us to do some simple forecasting of retail sales for Whizz:

• A price planning workview which will allow us to easily adjust product prices.
• A discount planning workview which will allow us to plan discounts and see how it affects a product’s price.
• And a volume planning workview which will allow us to forecast unit volumes and see how that translates into sales revenue and margin.

Revenue Cube - 01:29

1. First, let’s create the model which we will call “Whizz Performance Management”.
2. Set the time granularity as months - as this will be the lowest level of our time dimension.
3. Set the Financial Year to July to June.

Creating Dimensions - 01:52

1. Now that we have our model we will create a time dimension, by clicking ‘Dimensions’ in the model’s navigation menu to the left and click ‘New dimension’.
2. We will name the dimension “Time” and the dimension type will be ‘Time’.

Creating Variables - 02:45

1. We should also create some initial variables for the current month and current year.
2. To create a variable for the current month, click on ‘Variables’ in the model’s navigation menu and click ‘New variable’.
3. In the open new variable modal, in the key field we will write “Time.Current Month” making sure there’s no space in between time and current.
4. For the value we will write “2019 - Jul” and click ‘Create variable’.
5. We will create another variable for the current year, by clicking on ‘New variable’ again, and in the modal, change the word month to year. We will edit our value field this time to be “2020” because the time dimension uses the financial year. Tip: These variables are useful when building reports or if you want actuals to be pulled into your rolling forecast scenario.
6. Now, we have Whizz’s Actuals Sales data for their stationary products which we want to use to create and populate a cube.
7. To use this data, we can upload the Sales data file into ‘MODLR’s file manager’ on the far left navigation menu. And Click the ‘Upload File Button’ and then either browse for the file in question by clicking ‘Choose file’ or simply drag and drop the file and click ‘Upload’.

Building the cube - 04:15

1. Now, the file is in the system, let’s build a cube with it.
2. We will create a process called “Load Sales”.
3. Go to ‘Processes’ in the navigation menu and click ‘New Process’ and we will call it ”Load Sales”.
4. Double click on ‘Load Sales’ in the processes, to open it in a new tab and we can then edit it.
5. We want to select the action ‘Build a cube from data’, we will call the cube “Revenue”.
6. Then in the ‘Datasource’ field to the right we need to select the ‘MODLR File System’, and then as our table choose the ‘Sales.csv’ and click Preview.
7. This will show us a preview of the table’s first 8 rows of our sales file and now we can open the 'Build actions' by clicking the dash icon on the right hand side, and start to map the table’s columns.

Mapping Columns - 05:25

1. Data will be an element, so click the build dropdown and select ‘Element’, name the dimension “Product”.
2. Product_name will be an Element alias in the Product dimension. We will call this dimension “Product” and we will call the alias “Name” and select ‘Sku’ under the Element Field.
3. Brands will be used to group the products in the product dimension, so select ‘Grouping’ in the build dropdown field, the dimension again will be “Product” and we will create a hierarchy called “All Products by Brand”, and once again that will affect sku, so select ‘Sku’ in the Child Field.
4. The brand_name will also be an alias for the product dimension, so we will select the ‘Element Alias’ in the build the dropdown and the dimension will be “Product” and again the alias will be “Name” but we will instead select ‘Brand’ under the element field.
5. For the department column we will select ‘Element’ in the build dropdown and we will create a “Department” dimension.
6. For the scenario column, we will select this time, ‘Element (scenario)’ under the build field and create a dimension called “Scenario”.
7. The next four columns are filled with values for different measures. We have units, price, cost price, and discount %.
8. So for units we choose ‘Data’, and the measures is “Units”.
9. For price, it’s ‘Data’, and the measure is “Price”.
10. For cost price, it’s ‘Data’, and the measure is “Cost Price”.
11. And discount % is ‘Data’, and the measure is “Discount %”.
12. And the final column will match the date to the month elements in our Time dimension. We will select the ‘Element (Time)’ in the build and call the dimension “Time”, and the format we will be using is dd/MM/YYYY.
13. We will also add a calculated column which will group Brands under a parent called “All Brands”, so click ‘Add Calculated Field’.
14. So in the build dropdown we choose ‘Grouping’ and add a field calculation, by clicking the small calculator icon on the left and in the open modal, type under field formula “All Brands”, still in speech marks.
15. The dimension for ‘All Brands’ will be ‘Product’ and the hierarchy will be called “All products by Brand”, we want this hierarchy to exactly match the hierarchy we used for Brands. And the child field will be ‘Brand’.

Execute Process - 08:55

1. We have named the process “Load Sales” and then click ‘Validate and Save’, and then Execute the process.
2. And in the open modal, click ‘Execute’.
3. We can look at the logs for the process at the bottom of the page and because the data set is quite small, it only took a couple of seconds to run.

Revenue Cube Elements - 09:45

1. Now we will need to add some new elements which were not included in the sales file into some of the dimensions, so we will go back.
2. We will go into ‘Dimensions’, and in the ‘Scenario’ dimension, which we double click to open, we will add a Scenario Hierarchy by clicking the ‘Add’ button next to it, and call it “Planning Scenarios”.
3. We will then edit the ‘Planning Scenarios’ hierarchy, and replace the information there with “Live Forecast”. Then click ‘save changes’ and go back to ‘dimensions’.
4. Open the ‘Revenue Measures’ dimension and here we will also add “Price Override” and “Cost Price Override” and “Price and (post Discount)”, we will also add “Sales”, “Cost”, “Margin” and “Margin %”.
5. And click ‘Save Changes’.

Calculations Workview - 11:28

1. Now we have those elements in, let's create our first workview so go into ‘Workviews’. This workview won’t be used by planners, we will simply use it to make it easier for us to set up the initial formulas for the Revenue cube.
2. You can create a new workview by selecting ‘New workview’ or by selecting ‘Actions’ ‘New Workview’, our action will be ‘Create a new workview on an existing cube’, and the workview name will be “Revenue Calculations” and working off of the ‘Revenue Cube’.
3. We will double click to open the ‘Revenue Calculations’ workview.
4. Now we’re in the workview we can rearrange the dimensions. We will put ‘Measures’ on rows and ‘Time’ on columns, and the rest we will leave as ‘Selectable Dimensions’.
5. We will change the instructions for the time dimension so it can be entirely expanded, by double clicking on the time dimension to open it and removing ‘enable drill down’ from the set instructions and selecting ‘Expand All’ by double clicking it, from the group set manipulation in the instruction dictionary.
6. And now we can see all years, quarters and months expanded.
7. We will also change the settings for the product and department dimensions so we can select any element from their drop down, by clicking on the icon to the right of their drop downs menus to open the set instructions and deselecting ‘Enable Drill Down’ and selecting ‘Expand All’ and click ‘Save’.
8. And we repeat these steps for the department dimension.
9. We will select ‘Brisbane’ as the department and ‘Binders’ as the product.
10. For the scenario dimension, we can change the hierarchy settings to ‘planning scenarios’, from the hierarchy dropdown under the dimension structure on the left, and now we can double click ‘Live Forecast’ to add it to the set instructions.
11. We will also remove ‘enable drill down’ from the set instructions. And click save.
12. When there’s a red dot visible in the workview tab, it means it has unsaved changes. So I’ll save the workview by clicking the workview dropdown and click ‘save’.
13. Now, we can start adding in our formulas by right clicking on a cell where we want the formula to apply and selecting ‘Edit Formula’.
14. For Sales, the formula is simply “Units x Price x (1 - Discount %)”, and click ‘Save’.
15. Next, the Cost formula is “Units x Cost Price”.
16. And, then the Margin formula is “Sales - Cost”.
17. And, the Margin % formula is “Margin / Sales”. And we will select this to be an ‘Average or Rate Calculation’ so that it works on parent elements.
18. The next formula is a bit more complicated. We want a rolling forecast, so we want values in historic months to equal actuals.
19. So we will select ‘Live forecast’ in the scenario dropdown. Then open the formula editor on any cell. Because we want this formula to apply across all measures we will click on ‘Revenue Measures’ and change the setting to ‘Don’t Restrict’. However we want this formula to only apply to the Live Forecast, so we will click on Scenario and change the setting to ‘Restrict to Live Forecast’.
20. It’s important to call this formula, “Actuals into Forecast” and we will use this formula here:

21. IF(
    POSITION("Time","Month List",ELEMENT("Time"))
    <
    POSITION("Time","Month List",VARIABLE("Time.Current Month"))
    ,
    ["Actual"]
    ,
    CONTINUE)

22. And then we will ‘save’ that formula.
23. Then open the formula editor and rearrange the formula in the formula list so “actuals into forecast” is at the top which is the highest priority and then update the new order.

Price Planning Workview - Viewed by planners - 17:30

1. Now we have the initial formulas setup, let’s create some new workviews for Whizz. We can either leave the workview and open a new workview and start from scratch. Or instead we can use this workview as our base by making a copy of it.
2. So let’s hit ‘Save As’ and call our new workview “Price planning”.
3. Now we still have our original workview but also a copy with a new name.
4. For price planning we won’t need to see all measures so let’s change the instructions for the measures dimension by double clicking it, so we only show “Price”, “Price Override”, “Cost Price” and “Cost Price Override”.
5. We also don’t want to have to select each product one by one, so we’ll rearrange dimensions so that ‘products’ are on rows just above measures.
6. We also don’t need to see the price for all products, so we will double click on the ‘All Products’ dimension and select ‘Remove Consolidations’ from the instruction dictionary.
7. Now, to tidy up the report, we can add a blank space after each cost price override to separate the products, choose ‘Add Blank Space’ from the set formatting in the instruction dictionary.
8. Let’s ‘save’ the workview.
9. We can also tidy up the time dimension by double clicking a time dimension cell and ‘Removing set - Default’ and ‘Expand All’ and adding ‘Insert Variable Time: Current Year’ from the model variables group, and add a blank space after from the set formatting group, then we will add from the sequential functions, ‘insert next member from default’. We can then add ‘Expand All’ and ‘Remove Consolidations’.
10. Also because this report will only be used for planning we will open and edit the scenario, and remove the ‘default to actuals’ instruction and leave the Live Forecast in the instructions, so now we have restricted the scenario elements and we can only see the ‘Live Forecast’ in the scenario dropdown.
11. And ‘save’ the workview.

Formula for rolling selling price and cost price forward - 20:24

1. Now we’ve built this report's dimensions, let’s create two formulas which roll the sale price and cost price forward.
2. Open the formula editor and we will restrict the scenario to the Live Forecast. As this formula will only be used in future periods.
3. For ‘Price’, we will use the formula below, this formula grabs the previous month’s Price, which is what we want however we also need a way to change the Price, this is where the Overrides come in.

4. SEQUENCE("Time", "Month List", -1, ["Price"])

5. So we will go back into the formula editor and change it to:

6. IF(["Price Override"]=0,
   SEQUENCE("Time", "Month List", -1, ["Price"]),
   ["Price Override"])

7. And now if we add a Price Override, such as 6, we can see the price in August 2019 grabs the Price Override.
8. You can then copy this formula for the price and add it to Cost Price.
   • We will also restrict the scenario to Live Forecast and in the formula we will replace all ‘Prices’ with ‘Cost Prices’ instead.

9. IF(["Cost Price Override"]=0,
   SEQUENCE("Time", "Month List", -1, ["Cost Price"]),
   ["Cost Price Override"])

10. And if we add a Cost Price Override, of say 2, the Cost Price in August 2019 grabs the Override.

Discount Planning Workview - Viewed by planners - 22:18

1. Now we can create a new workview for Whizz’s Discount Planning.
2. Once again, we will ‘Save As’ and name our new workview “Discount Planning”.
3. The only things we will change are what Measures we will be showing.
4. So double click on a ‘Measures’ dimension cell.
5. We will show Price, Discount % and Price (post Discount).
6. And we will create a formula for Price (post Discount) which is “Price x (1 - discount %)”. And ‘Save’ this formula.
7. We will ‘Save’ this workview.

Volume Planning Workview - Viewed by planners - 23:22

1. And, finally we will create a Volume planning workview by clicking ‘Save As’. And name it “Volume Planning”.
2. In this workview we will show each product with the units measure but we will group them by brand and also show the brand’s total ‘Units’, ‘Sales’, ‘Cost’, ‘Margin’ and ‘Margin %’.
3. To do this we will need to use multiple sets.
4. For our first set we will select the pencil set, and change the hierarchy from default to ‘All Products by Brand’ and we want ‘Expand All’ and ‘Remove Consolidations’ and ‘Show: Alias Name’ in the set instructions for brand 1, and move brand 1 to the top.
5. And we will change the measures to only use Units.
6. Then to show Brand 1’s total units, sales, cost, margin, and margin % we can right click on the last row header, go to ‘Change table’ and select ‘Insert row set after selection’. So with this new set for brand 1’s totals, we will change the hierarchy again to ‘All products by brand’ we will use brand 1 and we will select ‘Show Alias Name’ from the element formatting group in the instruction dictionary.
7. And for the measures, we will add ‘Units’, ‘Sales’, ‘Cost’, ‘Margin’ and ‘Margin %’. We’ll also add a ‘Blank Space’ which will separate this set from the next.
8. We’ll repeat these steps and create two new sets for each brand, (a set for individual products and a set for brand totals).
9. So right click on the last row header and go to ‘Change table’ and select ‘Insert row set after selection’.
10. So for brand 2, begin with changing the hierarchy to ‘All Products by Brand’ and add Brand 2, in the set instructions add ‘Expand All’, ‘Remove Consolidations’ and ‘Show Alias Name’.
11. For the measures we will select just ‘Units’.
12. To show Brand 2’s totals, we will again add a blank product row below, and change the hierarchy to ‘All products by Brand’ and select ‘Brand 2’ and ‘Show Alias Name’. The respective measures will be ‘Units’, ‘Sales’, ‘Cost’, ‘Margin’ and ‘Margin %’ and ‘Add Blank Space’.
13. Add two more new sets for Brand 3, by repeating the steps from Brand 2.
14. Then we will add one more set which will hold a total for all products under the default hierarchy.
15. So this time instead of selecting brands we will just select ‘All Products’.
16. The measures will be ‘Units’, ‘Sales’, ‘Cost’, ‘Margin’ and ‘Margin %’.

Forecasting - 27:49

1. While planning we might want to see and compare yearly totals, so once again we will right click on a row header but instead of a new row set we will select ‘Insert column set before selection’. And in this set we will use our ‘Insert Variable: Time.Current Year’ and we will use both the ‘Previous Member: from Default Hierarchy’ and ‘Next Member: from Default Hierarchy’, in the sequential functions group.
2. Now we can start forecasting for 2020.
3. We can enter in the numbers manually but that would be very time consuming.
4. Instead because we expect sales to follow largely the same trend as 2019, we can copy 2019’s number by double clicking the units for all products for 2020’s cell and typing in “copy “2019””.
5. Also to forecast an increase in sales for 2020, in the same cell, we can either set the amount by typing “set” and the amount, for example “$18,500” or we can add to the current amount by typing “add” and either an amount or a percentage increase, for example “10%”.
6. Here you can see 10% has been added to 2020’s sales.

This concludes our Revenue Planning tutorial. Don’t forget to sign up to MODLR’s free trial account or submit any questions to MODLR’s community forum. Thanks for watching.