Fiscal year opening and closing

[mbayopanda]

Fiscal Year Module

This issue is about to :

• Define the opening fiscal year logic
• Define the closing fiscal year logic
• Put the current fiscal year at the top of fiscal year list
• Display an icon for closed fiscal year
• Fix the ending dates which are displayed in the fiscal year module
• Remove the deletion icon in the fiscal year list

Opening a fiscal year

• At the creation :

  • If the new fiscal year has N periods, Add the period 0 and the period N+1;
    • The period 0 is for all transactions relative to the openning of a fiscal year
    • The period N+1 is for all transactions relative to the closing of a fiscal year such as to sold all exploitation accounts
  • If for the new fiscal year there is not an openning balance in the BHIMA system, but there is it in others systems
    • Define fiscal year elements such as : Name, Starting date, End date, number of periods, previous fiscal year
    • Launch the configuration openning balance module and set the openning balance manually
  • If for the new fiscal year there is an openning balance in the BHIMA system, in the last fiscal year period 13

    • Define fiscal year elements such as : Name, Starting date, End date, number of periods, previous fiscal year

    • Import the balance of the period 13 of the fiscal year N-1 into the balance of the period 0 of the fiscal year N

      • The import is as follow (Ecriture des a-nouveaux) : if the sold is balanced,
        Put to debit values which are at debit, and put to credit values which are at credit for all Bilan accounts in period 0 of the fiscal year N

      In this step, we must have only data for Bilan accounts because there was a sold of all exploitation accounts in the fiscal year N-1

• Module to develop for the openning of a fiscal year :

  • Configuration openning balance : A module for defining for all accounts concerned the debit or the credit as the openning balance
  • Import openning balance : A module for copy the value which are in the period 13 of the fiscal year N-1 into the period 0 of the fiscal year N

• Database concerned tables :

  • fiscal year table

  CREATE TABLE `fiscal_year` (
    `enterprise_id`             SMALLINT(5) UNSIGNED NOT NULL,
    `id`                        MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `number_of_months`          MEDIUMINT(8) UNSIGNED NOT NULL,
    `label`                     VARCHAR(50) NOT NULL,
    `start_date`                DATE NOT NULL,
    `previous_fiscal_year_id`   MEDIUMINT(8) UNSIGNED DEFAULT NULL,
    `locked`                    TINYINT(1) NOT NULL DEFAULT 0,
    `created_at`                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`                TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    `user_id`                   SMALLINT(5) UNSIGNED NOT NULL,
    `note`                      TEXT,
    PRIMARY KEY (`id`),
    UNIQUE KEY `fiscal_year_1` (`label`),
    UNIQUE KEY `fiscal_year_2` (`enterprise_id`, `start_date`),
    KEY `enterprise_id` (`enterprise_id`),
    KEY `user_id` (`user_id`),
    FOREIGN KEY (`enterprise_id`) REFERENCES `enterprise` (`id`),
    FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE
  )

  • period table

  CREATE TABLE `period` (
    `id`                  MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `fiscal_year_id`      MEDIUMINT(8) UNSIGNED NOT NULL,
    `number`              SMALLINT(5) UNSIGNED NOT NULL,
    `start_date`          DATE NOT NULL,
    `end_date`            DATE NOT NULL,
    `locked`              TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `period_1` (`start_date`, `end_date`),
    KEY `fiscal_year_id` (`fiscal_year_id`),
    FOREIGN KEY (`fiscal_year_id`) REFERENCES `fiscal_year` (`id`)
  )

  for the period table we must choose how we will define the extremum period 0 and 13;
  the proposal is about to set dates accepts NULL values, in this case the extremum period will have dates NULL

  `start_date`          DATE NULL,
  `end_date`            DATE NULL,
  

  or the add a flag is_extremum which define if the period is for extremum operations (only for period 0 or period 13)

  `is_extremum`        TINYINT(1) NOT NULL DEFAULT 0,
  

  • period total table

  CREATE TABLE `period_total` (
    `id`                  MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `fiscal_year_id`      MEDIUMINT(8) UNSIGNED NOT NULL,
    `number`              SMALLINT(5) UNSIGNED NOT NULL,
    `start_date`          DATE NOT NULL,
    `end_date`            DATE NOT NULL,
    `locked`              TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `period_1` (`start_date`, `end_date`),
    KEY `fiscal_year_id` (`fiscal_year_id`),
    FOREIGN KEY (`fiscal_year_id`) REFERENCES `fiscal_year` (`id`)
  )

Closing a fiscal year

• Select the concerned fiscal year in the fiscal year module
• Choose the option Close the fical year
  • A modal will apear with all the sum of produit, the sum of costs and the result

• In the modal of closing the fiscal year, the user must select the result's account which can be a benefit or lose account in class 1 account

- *Note About the Sold of Exploitation Accounts* : 

    - result = sum(all profit) - sum(all charge)

    - if the result is > 0 => benefit, the result will be affected into a `131 (Resultat Net: Benefice)`

    - if the result is < 0 => lose, the result will be affected into a `139 (Resultat Net: Perte)`

    - Debits all profit accounts

    - Credits all charge accounts except `603 (Variation de Stock)`

- Closing transactions are written in the period `13` of fiscal year to close 

Fixing

Put the current fiscal year at the top of fiscal year list

Display an icon for closed fiscal year

Fix the ending dates which are displayed in the fiscal year module

[mbayopanda]

@jniles could you look this issue

[jniles]

@mbayopanda, great issue!

Your recommendations regarding UI are good.

The descriptions are clear. Thanks!

Questions:

1. Should the period_total table has a period_id in it instead of start_date, end_date?
2. Will the account to which the sum(all profit) - sum(all charge) result will be written have to be selected by the accountant each closing operation? Or will it be automatically selected? If automatic, where will it be stored?
3. Can we put end_date in the fiscal_year table? It would make calculations much easier for some reports.

[DedrickEnc]

@mbayopanda
Great! a shared document at google drive should be helpful though

here are some questions and concerns :

1. Are we really supposed to have period 0 for each fiscal year ? because it seems like the period 13 of the previous fiscal year can be considered as the period 0 of the next fiscal year.
2. I think it is good to check if every transaction of a concerned fiscal year is posted before closing it and also I think after closing process the system should be sure that assets accounts and liabilities accounts are balanced before committing the change.
3. Considering the fact that the fiscal year closing is a very serious accounting process, a roll back can be helpful if there is a doubt (???) but I am not very sure what do you think?

Nice issue!

[mbayopanda]

@jniles

1. I think you're right about the start_date and end_date in the period_total table, we can just use the period_id
2. The result account is selected by the accountant, but we can set to the correct account automatically with the option to select another because if the result is benefit Patrick said to use a 131 (Resultat Net: Benefice) and if the result is a loss use the 139 (Resultat Net: Perte)
3. yes we can use the end_date in the fiscal year if it's helpfull

[jniles]

@DedrickEnc good points. I agree on all points.

@mbayopanda, thanks for the feedback. For (2), I think we could set the closing accounts as properties on the enterprise table, similar to the gain_account_id and loss_account_id. Something like closing_income_account_id and closing_expense_account_id.

[mbayopanda]

@DedrickEnc

For the question 1), if we must create a new fiscal year, in the case where there is not any previous fiscal year, and for the new fiscal year that we are creating we must set a default opening balance manually, these transactions must be written in the period 0 of the new fiscal year. You can see it, in the Opening Fiscal Year section in this issue

[DedrickEnc]

@mbayopanda
I think we must ask Patrick because for me the opening balance of the first fiscal year can be considered as transactions of the first period instead of creating the period 0 for each fiscal year.

[jniles]

@DedrickEnc, you are right, but I think the idea is for developer ease. Every year will have:

1. Period 0 - the opening balances
2. Period 1 through N - the periods in the year with transactions
3. Period N+1 (13 if you have 12 periods) - the closing balances.

In the first fiscal year, period 13 of fiscal year N - 1 doesn't exist. Instead of handling this case in every report that requires opening balances, each report will just copy the values of period 0, which will always exist. If there is no N - 1 fiscal year, these values will be 0.

If the fiscal year N - 1 exists, then the values in period 0 will be the same as the values of period 13 in the fiscal year N - 1.

Does that make sense?

@mbayopanda correct me if I am wrong.

[mbayopanda]

@jniles you're right

[mbayopanda]

@jniles , @DedrickEnc , @sfount , @lomamech

What is the best way to identify profit accounts and charge accounts which must be solded in the structure of our database, precisely in the account table ?

[jniles]

@mbayopanda, the correct way is to look at the account_type_id. We should have charge (expense) and profit (income) account types. These will be fixed, like your voucher types.

[mbayopanda]

@jniles @DedrickEnc

A summary of fiscal year opening and closing process.

For developing correctly, the time is arrived to work with correct accounts. If it is possible to have a real chart of accounts which can help us to simulate real transactions and to see financial status correctly.

[jniles]

@mbayopanda, this is a really nice graphic. Cool!

A real chart of accounts is a good idea. Do we not have one from 1.x?

[jniles]

@mbayopanda, can this issue be closed? What is left to complete?

[jniles]

I think this issue has run its course. All the code is written for these issues.