Search:

MS Office Forum

Ask Question   UnAnswered
RSS Feeds

Convert calendar year to fiscal year

Asked By: Lourdes    Date: Feb 28    Category: MS Office    Views: 3960

I am trying to convert a budget from a calendar year to a fiscal year.For
example lets say
I have a contract starting 10/1/2005 and expiring 8/31/2007 for an amount of
\$36,000. What I will like to do is create a monthly budget for the 12 months of
the current fiscal year which starts 7/1/2004 and ends 6/30/2005.
How should I approach this?

Share:

3 Answers Found

Answer #1    Answered By: Jake Williams     Answered On: Feb 28

Depending on what accuracy you want the answer to be in, you can
calculate the contract time in either days or months. Let's start with
days:

'Date format = mm/dd/yyyy
'Excel will use Julian date format for calculations

Contract_End_Time = 8/31/2007
Contract_Start_Time = 10/01/2005
Contract_Value_Total = 36000

Contract_Length = Contract_End_Time - Contract_Start_Time

Contract_Value_per_Day = Contract_Value_Total / Contract_Length

Monthly_Budget = Contract_Value_per_Day * 30 '(Here you may have to get
elegant if required, to work on different days in a month)

-------------------------
If you want to work on a monthly basis (my preference), change the last
line of logic to the following:

Contract_Value_per_Month = Contract_Value_Total / Contract_Length *
365.25 / 12 '(365 days per year, adjusted to include leap years, and 12
months in a year)

To see whether a month in your budget year  falls within the contract
period, use an IF statement as follows:

IF(Evaluated_Month > Contract_Start_Time) AND IF(Evaluated_Month <
Contract_End_Time)
THEN
Do all the maths
ELSE ZERO.

Answer #2    Answered By: Muriel Dunn     Answered On: Feb 28

Vermeulen is quite Dutch. Are you in the NLs?

Answer #3    Answered By: Trae Thompson     Answered On: Feb 28

My forefathers were from the NL's. Was born in South Africa, and
currently residing in Perth, Australia.

Didn't find what you were looking for? Find more on Convert calendar year to fiscal year Or get search suggestion and latest updates.

Tagged: