Help dealing with a fiscal year rather than a calendar year
 

Hello,
I am trying to get my mind around how to project a future cost of replacing
a piece of capital equipment. I can do it for a calendar year but since we
are on a July to June year the costs may or may not fall into the correct
year heading. Here is where I am so far:
=IF(YEAR($O2)+$AA2=AC$1,ABS(FV($AB2,$AA2,0,$S2,1)) ,0)
with: O2 being the purchase date of the original capital equipment
AA2 being the useful life or time in years before we need to replace the
capital equipment.
AC1 being a year or fiscal year column ie. 2007, 2008,.... where the
replacement cost of the capital equipment would calculate
AB2 being the cost inflator ie. 1.5%
S2 being the cost of the equipment when originally pruchased.

As always thanks for your help.

Help dealing with a fiscal year rather than a calendar year
 

Hi tyler,

What about having 2 columns for your fiscal year and insert actual start and
finish dates for the fiscal year. Eg 1 July 2007 in column AC and and 30 June
2008 in column AD. You also need an actual date for the purchase not just a
year but it could just be the first day of the fiscal year and it would work.

If you want you can even custom format them to yyyy so that they only
display the years but calculations are based on the actual underlying date.

Your formula can then test for = than the start date and =< than end date
as per the following example:-

=IF(AND($O2+$AA2=AC$2,$O2+$AA2<=AD$2),ABS(FV($AB2 ,$AA2,0,$S2,1)),0)

Note: The above formula has all the values in row 2. Your formula had the
fiscal year in row 1. I don't know how your worksheet is set up but there is
no reason that the start and end dates cannot be in row 1.


Regards,

OssieMac


"Tyler" wrote:

Hello,
I am trying to get my mind around how to project a future cost of replacing
a piece of capital equipment. I can do it for a calendar year but since we
are on a July to June year the costs may or may not fall into the correct
year heading. Here is where I am so far:
=IF(YEAR($O2)+$AA2=AC$1,ABS(FV($AB2,$AA2,0,$S2,1)) ,0)
with: O2 being the purchase date of the original capital equipment
AA2 being the useful life or time in years before we need to replace the
capital equipment.
AC1 being a year or fiscal year column ie. 2007, 2008,.... where the
replacement cost of the capital equipment would calculate
AB2 being the cost inflator ie. 1.5%
S2 being the cost of the equipment when originally pruchased.

As always thanks for your help.

Help dealing with a fiscal year rather than a calendar year
 

Thanks OssieMac,
I will start to work through your example. I thought that I had it with the
following but the calculation was not correct however the formula seemed to
worked. I wanted it to add or substract months based on fiscal to calendar
ration and compare to the year
=IF(YEAR($O12+YEAR($AA12)+(MONTH($O12)5))=AC$1,AB S(FV($AB12,$AA12,0,$S12,1)),0)

Note I was working in row 12 on this one in a spreadsheet set up as follows:
O: date purchase 4/3/2002
S: cost 1072
AA: replacement yrs 5
Ab:cost inflator 1.5%
my answer:
AC12 - 2001: 0
AD12 - 2002: 0
AE12 - 2001: 0
AF12 - 2002: 0
AG12 - 2001: 0
AH12 - 2002: 0
AI12 - 2001: $1154.85
AJ12 - 2002: 0


"OssieMac" wrote:

Hi tyler,

What about having 2 columns for your fiscal year and insert actual start and
finish dates for the fiscal year. Eg 1 July 2007 in column AC and and 30 June
2008 in column AD. You also need an actual date for the purchase not just a
year but it could just be the first day of the fiscal year and it would work.

If you want you can even custom format them to yyyy so that they only
display the years but calculations are based on the actual underlying date.

Your formula can then test for = than the start date and =< than end date
as per the following example:-

=IF(AND($O2+$AA2=AC$2,$O2+$AA2<=AD$2),ABS(FV($AB2 ,$AA2,0,$S2,1)),0)

Note: The above formula has all the values in row 2. Your formula had the
fiscal year in row 1. I don't know how your worksheet is set up but there is
no reason that the start and end dates cannot be in row 1.


Regards,

OssieMac


"Tyler" wrote:

Hello,
I am trying to get my mind around how to project a future cost of replacing
a piece of capital equipment. I can do it for a calendar year but since we
are on a July to June year the costs may or may not fall into the correct
year heading. Here is where I am so far:
=IF(YEAR($O2)+$AA2=AC$1,ABS(FV($AB2,$AA2,0,$S2,1)) ,0)
with: O2 being the purchase date of the original capital equipment
AA2 being the useful life or time in years before we need to replace the
capital equipment.
AC1 being a year or fiscal year column ie. 2007, 2008,.... where the
replacement cost of the capital equipment would calculate
AB2 being the cost inflator ie. 1.5%
S2 being the cost of the equipment when originally pruchased.

As always thanks for your help.

Help dealing with a fiscal year rather than a calendar year
 

Hi

You could insert a named value
InsertNameDefineName Fyear Refers to
=YEAR(Sheet1!$O1)+(MONTH(Sheet1!$O1)6)

This will set the value of Fyear to 2007 for date 01 Jun 2007 or 2008 for 01
Jul 2007 i.e the fyear refers to the financial year ending date.
If you wanted it to refer to the beginning date, then either subtract 1 from
the formula or use
=YEAR(Sheet1!$O1)-(MONTH(Sheet1!$O1)<7)

Now, in your formula just substitute Fyear
=IF(Fyear+$AA2=AC$1,ABS(FV($AB2,$AA2,0,$S2,1)),0)

--
Regards
Roger Govier



"Tyler" wrote in message
...
Hello,
I am trying to get my mind around how to project a future cost of
replacing
a piece of capital equipment. I can do it for a calendar year but since
we
are on a July to June year the costs may or may not fall into the correct
year heading. Here is where I am so far:
=IF(YEAR($O2)+$AA2=AC$1,ABS(FV($AB2,$AA2,0,$S2,1)) ,0)
with: O2 being the purchase date of the original capital equipment
AA2 being the useful life or time in years before we need to replace the
capital equipment.
AC1 being a year or fiscal year column ie. 2007, 2008,.... where the
replacement cost of the capital equipment would calculate
AB2 being the cost inflator ie. 1.5%
S2 being the cost of the equipment when originally pruchased.

As always thanks for your help.