Help dealing with a fiscal year rather than a calendar year

[Tyler]

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.

 

[OssieMac]

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]

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,ABS(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

 

[Roger Govier]

Hi

You could insert a named value
Insert>Name>Define>Name 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)