Page 72 - Finanancial Management_Feb_Apr23

I want to know when the first in a sequence provided in any order.

Thus, the first TRUE occurs in the eighth
non-negative period occurs. position, hence eight [8] is returned, ie:

This will provide my initial =MATCH(TRUE,$I$17:$Q$17>=0,0) = 8
Therefore, we now know that
payback period, which is what breakeven occurs after the seventh period
but sometime up to or equal to the date
I intend to calculate here. of the eighth period. The aim is to find at
what point in this time interval, and to do
this, I need one of my favourite functions
I forever talk about, OFFSET.

Calculating the timing of each cash in the above example, this is simply OFFSET recap
flow is simple: not the case). The oft-maligned OFFSET function
y This logic fails to consider whether considers disposition or displacement
=I$12-$I$12 there are any further outflows later and has the following syntax:
when payback may have already
This formula simply subtracts the occurred (eg, material maintenance OFFSET(reference, rows, columns,
start date (I12) from the date of the capital expenditure). This can lead to [height], [width])
particular cash flow on row 12. For entirely erroneous results, regardless
example, the third cash flow occurs on of any regularity of periodicity The arguments in square brackets
9 April 2023, which is 98 days after the considerations. (height and width) may be omitted from
start date of 1 January 2023, etc. Therefore, I will not do it this way. the formula and are not germane to our
The cumulative cash flow is nothing I want to know when the first non- problem covered here.
more than a challenge of anchoring cell negative period occurs. This will provide In its most basic form,
references correctly. For example, the my initial payback period, which is what OFFSET(reference, rows, columns)
formula in cell I17 is given by: I intend to calculate here. It will not be will select a reference rows rows down
distorted by any future negative cash (-rows would be rows rows up) and
=SUM($I$13:I$13) flows. I can determine this using the columns columns to the right (-columns
formula: would be columns columns to the left)
Again, column I is anchored, with all of the reference. For example, consider
cash flows summed from the first period =MATCH(TRUE,$I$17:$Q$17>=0,0) the downloadable screenshot “Example
onwards. Dataset”.
We simply need to ascertain Here, the MATCH function considers OFFSET(A1,2,3) would take us two
payback. Many define this as when the range $I$17:$Q$17 and assesses rows down and three columns across
the cash flow first becomes non- whether the values are non-negative. In to cell D3. Therefore, OFFSET(A1,2,3) =
negative (ie, greater than or equal to this instance, this will be evaluated as: 16. (See the downloadable screenshot
zero [0]). Consequently, many people “OFFSET Function Example 1”.)
model using COUNTIF to calculate FALSE, FALSE, FALSE, FALSE, FALSE, OFFSET(D4,-1,-2) would take us
how many negative periods there are. FALSE, FALSE, TRUE, TRUE one row up and two rows to the left to
I disagree with this approach for two cell B3. Therefore, OFFSET(D4,-1,-2) =
reasons: MATCH then seeks TRUE in this 14. (See the downloadable screenshot
y This methodology often assumes array and match_type zero [0], the third “OFFSET Function Example 2”.)
periods are equal in length (often, as argument, locates the first occurrence This is especially useful when you

Calculating proportion of period that is non-negative

28 I FM MAGAZINE I April 2023