Tags: calendar, capital, cost, dealing, equipment, excel, fiscal, future, microsoft, mind, msdn, piece, project, replacing, software

Help dealing with a fiscal year rather than a calendar year

On Microsoft » Microsoft Excel

6,969 words with 3 Comments; publish: Fri, 23 May 2008 18:11:00 GMT; (30646.88, « »)

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.

All Comments

Leave a comment...

  • 3 Comments
    • 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.

      >

      #1; Fri, 23 May 2008 18:12:00 GMT
    • 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

      "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.

      > >

      #2; Fri, 23 May 2008 18:13:00 GMT
    • 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)

      --

      Regards

      Roger Govier

      "Tyler" <Tyler.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:C91D50FC-4C6A-4A58-BBE4-18B84E6CF4AC.excel.todaysummary.com.microsoft.com...

      > 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.

      >

      #3; Fri, 23 May 2008 18:14:00 GMT