How to model a cost as a percentage of another line in sherloc

Some costs move in line with another figure in your forecast, such as commission tracking sales or cost of goods tracking revenue. Rather than updating them separately, you can link them so one drives the other.

1. Identify the relationship

Decide which line should move automatically, and which line it should be based on.

Example: setting Cost of Goods Sold to 30% of a sales line, so it rises and falls with sales.

2. Add it as an assumption

Select Create assumption. In the Create Assumption drawer, give the assumption a name (this is required), choose the sheet that holds the cost from the Select the sheet impacted by the assumption dropdown, and tick the cost line you want to drive automatically. Select Continue to date range.

Choose All months if this should apply continuously, or Date range for a set run of months, then select Continue to criteria.

Choose Set to a percentage of a line item and answer the four questions in turn:

Which sheet? → Which line item? → Which value? (choose Current Period (latest actuals)) → What percentage of…?

Check the In plain terms text, then select Add assumption.

Example: Cost of Goods Sold, all months, based on your sales line. Enter 30. If that sales line is £10,000 in one month and £12,000 the next, Cost of Goods Sold becomes £3,000 and then £3,600. It's recalculated from each month's own figure.

3. Check the numbers

Look at both lines side by side to confirm you've picked the right line to base it on, and that the cost moves in step with it each month. If you chose a date range, the cost returns to its previous figure once the range ends.

Two things to know

On the Profit And Loss sheet, only individual lines are offered as the line to base it on, so pick a single sales line rather than a total for sales.

If the line you're basing it on has no value in the months you've chosen, sherloc may show a warning that the result will be 0 each month.