Linking Cubes ​

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.

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.

LINK("Sales", ["Revenue", "All Products"])

LINK("Sales", ["Revenue", "All Products"])

Similarly, we will add the formula for Cost of Goods Sold as well.

-LINK("Sales", ["Cost of Goods Sold", "All Products"])

-LINK("Sales", ["Cost of Goods Sold", "All Products"])

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")

IF (LINK("Sales", ["All Products", "All Months", "Revenue"]) = 0, "Sales Model", "Sales Model")

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")

IF (LINK("Sales", ["All Products", "All Months", "Cost of Goods Sold"]) = 0, "Sales Model", "Sales Model")

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.