Formula: Calender and Fiscal years

Topic

New Reply

Hi,

I’m working on an input model that needs to be able to display an awry fiscal year correctly in tables
(Attached book, Rows 14-15).

Users provide:
1. Fiscal year – end date (Cell: B7 – format dd/mm)
2. Indicator: Awry Y/N? (Cell: B8)
3. Quarter ends – four dates per annum (Row 11, format dd/mm/yyyy) first four are typed in, then dragged across)
4. Indicator: A/E (Row 12, is the year “Actual” or “Estimated”)

Then Rows 14 and 15 each calculate their respective values for the year in question.

Assumptions:
a. Awry = “N”, Calendar and Fiscal are the same ie. year-end is 31-12. This works fine.
b. Awry = “Y”, Calendar != Fiscal.

Re. b.
As can be seen from the “Examples” section (Rows 18-33) the timeperiods resulting in the headings can vary a lot, but the headings will be the same in the cases where Awry = “Y”.

I believe the crucial part of the problem is to get the “Fiscal year” cells right (Row 14). Then Row 15 should be a lot easier to concatenate.

But right now my problem is to get the year right in all instances of “Awry’s”. When those vary I keep getting at least one wrong.

Any tip, idea or insight on this will be highly appreciated.

Thanks,

Reply | Quote

Replies

Have a look at the attached file, In it the fiscal year end is entered in E10; the balance of row 10 is calculated.

Rows 14 and 15 are all calculated in blocks of 4 columns. The formulae were entered into B:E then copied across.

Cells B7 & B8 were not used.

You will get into trouble if the year end is set to a date greater than 28 which is not a month end, otherwise all should be fine.

Note: The formula in row 15 has been revised in the attached file (…_r2.xls), the previous version had an error.

Further Note: Row 15 has been further revised to provide the 4 digit year when the fiscal and calendar years coincide. The updated file is “Fiscal Awry_r3.xls”.

Final Note: Row 10 has been revised to remove the restriction on the year end being greater than 28 when not a month end. Like rows 14 & 15, row 10 is now calculated in blocks of 4. The formulae were entered into F:I then copied across; B C & D are unique.

Reply | Quote

Hi Don,

Thanks for sharing the insights. It’s a refreshing different approach and a lot more simple than my initial one. So I think it looks really good.

A single question to the formula in Row 15 though (taken from cell F15):

=F11&” “&IF(YEAR(I10+1)YEAR(I10),
TEXT(YEAR(I10),”0000″),
TEXT(MOD(YEAR(I10)-1,100),”00″)&”/”&TEXT(MOD(YEAR(I10),100),”00”))

The test in the IF condition (IF(YEAR(I10+1)YEAR(I10)…) – when will that ever be true, and if it won’t, why is it there in the first place??

I’ve tried to modify your suggestion further, but keep ending up in incorrect situations. So – so far what you’ve provided seems to be the most stable solution.

Thanks,

Reply | Quote

That “IF” condition will be true when the fiscal and calendar years are coincident; this is the mechanism for providing the 4 digit year as required by the examples at row 21.

Reply | Quote

OK, so this is only when the date is 31-12-yy. Then by adding 1 day the year comparison actually gets “out of sync”, regardless that you are actually comparing the value to itself… – I like that – clever solution.

Thanks.

Reply | Quote