Rearranging Example

Worked example of Rearranging

Setup

For our forecast periods (2023 onwards), we want to calculate Revenues (1) as an output, so we start with a simple calculation, Revenues = Widgets Sold * Price (2).

Revenues Variable before rearranging

We set up our Widgets sold Variable so that we need to enter Assumptions for 2021 and 2022 (3), and from 2023 onwards Widgets sold will be equal to the previous period (4). However, we don't know how many Widgets were sold in 2021 and 2022, so we cannot initially calculate Revenues, meaning we have blank values in our output (5).

Widgets sold Variable before rearranging

Entering Actual Revenues

We know what our revenues were for 2021 and 2022, so we create a new Time Segment in our Revenues Variable (6), with an Assumptions formula type, and add our historical revenues data to this Time Segment.

Revenues Variable after entering Assumptions for 2021 and 2022

Models now sees that for 2021 and 2022, it is expecting input data for all three Variables, Revenues, Widgets sold and Price. This is not a valid state, as in any Calculation there must be exactly one unknown Variable to be calculated, otherwise there is the potential for conflicts (i.e. we could enter values for the three Variables that do not match the Calculation Revenues = Widgets sold * Price).

To rectify this, Models displays a message on the first Time Segment of Revenues (7), warning that a "Child needs to be rearranged for this segment". We can read this as saying that one of the input Variables, Widgets Sold or Price needs to become the output of the Calculation to maintain logical consistency.

Rearranging

To rearrange the segment, we simply click the Resolve button (8), which opens a new dialog, shown below.

We are offered a choice of child Variables to rearrange. We need to select the Variable that we want to be used as the new output of the Time Segment. In this case, we know the price, but we don't know the number of widgets sold, so we select Widgets sold as the new output (9).

Rearranging dialog

This results in the below state, where we no longer have warnings in our Variables. The first Time Segment for Widgets sold has been rearranged, with a Calculation of Revenues / Price (10).

Widgets sold Variable after rearranging

Widgets sold is now calculated from Revenues and Price for 2021 and 2022, and is static thereafter.

Note that the Values table visually indicates the change in calculation method with a border to the right of the 2022 period (11).

Last updated