Creating a UK Fiscal Tax Year in numbers

Hi Dale,

I found it difficult to do exactly what you want (combining two incompatible aims into one document):

• Sum the transactions that fall between the fiscal dates, and then
• Separate them again into calendar months.

How about a compromise? Try this.

I moved the Inventory Table from the Inventory Sheet to the Summary Sheet. I find it is easier to construct formulas when I can see all the tables at once. Also, it makes for an easier explanation in this forum.

Formulas in the Inventory Table

B2: IF(AND($A2≥UK Fiscal Year::$A$2,$A2≤UK Fiscal Year::$B$2),"Yes","No")

(and some visual aid with Conditional Highlighting 'Text is Yes').

H2: IF(AND($A2≥UK Fiscal Year::$A$2,$A2≤UK Fiscal Year::$B$2),G2,"")

Footer cell in Column H: SUM(H)

Formulas in the Summary Table

B2: Inventory::H$22

This will automatically adjust as you add or delete rows to the Inventory Table.

Footer cell in Column B: SUM(B)

Now move(Cut and Paste) the Inventory Table to the Inventory Sheet

The formulas in the Summary Table automatically adjust if necessary.

I hope that the UK Tax Office sees the sense in using whole months in the fiscal year!

Regards,

Ian.

Hi Dale,

Hoddsy wrote:

The uk tax year is the beginning of March to the end of April.

Umm... the UK tax year is confusing:

https://www.gov.uk/self-assessment-tax-returns/deadlines

"The last tax year started on 6 April 2020 and ended on 5 April 2021"

And yet:

https://www.gov.uk/government/publications/exploring-a-change-to-the-uk-tax-year-end-date

" A clear majority of those responding to us thought that the UK should adopt a different year end - but there was a range of views on whether to move to 31 December or to 31 March. This has been borne out by a range of surveys."

What start and end dates are you using? If part way through this year's April until part way through next year's April, we can't use Month and Year. We perhaps must use SUMIFS with Date is "greater than or equal to" and Date is "less than or equal to".

Example:

Thanks for the screen shots, but the formula editor is hiding the cells that contain the formulas. You can move the formula editor by dragging the dot to the left of fx.

Regards,

Ian.

Hi Hoddsy,

Do you mean dates that fall within the range of 1st July and 30 June? You could filter your tables to show only the data with dates that are greater than or equal to 1st July in one year, and less than or equal to 30th June in the following year. More information will help us to help you.

Regards,

Ian.

Not all of us (and I daresay 'not the majority of us') are familar with "the UK tax year," which could make it difficult to offer useful responses to your question.

What calculations are done on the tables you have now? What changes need to be made to make your tables suitable for "the UK Tax year"?

Regards,

Barry

Hi Dale,

Screen shots of the formulas. The Inventory Table is temporarily on the Summary Sheet for clarity.

Formula in B1:

Formula in H1:

Fill both formulas down to the last Body Row.

Column B is simply a visual aid.

Regards,

Ian.

Hi Dale,

I had an "Aha!" moment.

If you are using this setting in Menu > Numbers >Preferences > General > Cell References: > Use header names as labels

Untick to see formulas as Column letter and Row number, as in my screen shots.

Regards,

Ian.

Edit: A slip up on my part. Category, not Month.

Ian.

Hi Dale,

I am glad to help and thank you for the green tick and your feedback.

Also, thank you for a challenging question!

Regards,

Ian.