Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weeks left in a Fiscal year | Excel Discussion (Misc queries) | |||
Birthdate as fiscal year | Excel Discussion (Misc queries) | |||
change the year in a calendar template to different year | Excel Discussion (Misc queries) | |||
Fiscal Year in date field | Excel Discussion (Misc queries) | |||
Fiscal Year Calculation | Excel Worksheet Functions |