|
|
Transcripts for the above video clip:
OFFSET FUNCTION
The
OFFSET function allows you to change the cell you refer to by moving
the reference either up, down, left or right as you require. This
can be done automatically and is very useful for cash flow
projections.
In this segment you will learn how to activate and use this
function.
In this example we have Sales figures for a five month period. But
what we know is that these Sales don稚 get paid until either a month
or two later, and what we壇 like to build is when the cash is likely
to be received, but to build in the option that we can change how
long it takes to get the money. This can be achieved by using the
OFFSET function. To activate the OFFSET function,
-
you click on the cell,
-
activate the Function Wizard
-
and find the OFFSET function,
-
and click Ok,
The first question is, what is its original reference point, and
that値l be just in the same time period so
-
we値l click up here
-
and then you can choose if you want it to move
along rows, or do you want to move along columns, in this case we
want it to look along columns
And what we want it to do is to look at this column, and depending
on the payment terms get the column coupled back. So if in Month 1
there were sales of 100, and there痴 a one month payment period we
expect that Month 2 we値l receive the money.
ｷ
so in
this case it値l be a negative,
ｷ
and
then we値l refer to this column
ｷ
and
make it absolute
You can also tell the OFFSET function to combine a number of rows,
if you put a number in the height
or a number of the columns, if you put a number in the width.
However for these purposes we are just going to keep it to a simple
OFFSET.
-
if you click ok
-
and copy it across,
-
what you値l see is at the moment it refers in the
same month
-
but if we say that it takes one month to receive
payment
-
if you just watch here
-
you値l see then it immediately, automatically
updates the fact that sales in this month will only be paid here
-
and we can put a 2 here
-
and that値l automatically update,
so Month 2 sales
of 200, are only going to be received in month 4.
|
.gif)
.gif)
