# Multi-Currency Month End Revaluation Worksheet > Source: https://netray.co/tools/africa-multi-currency-month-end-revaluation-worksheet > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Revalue foreign currency debtors, creditors and bank balances at the closing rate and see the unrealised gain or loss per line. ## Category cost-finance ## Region Africa ## Audience - Financial manager - Group accountant - ERP finance consultant ## Tool **Kind:** worksheet **Mode:** editable **Currency:** R **Columns:** #### Account or balance **Key:** account **Type:** text #### Currency **Key:** ccy **Type:** select **Options:** - USD - EUR - GBP - ZMW - BWP - MZN - NGN - KES #### Type **Key:** type **Type:** select **Options:** - Asset - Liability #### Balance in foreign currency **Key:** fx_balance **Type:** number #### Rate booked (local per 1 foreign) **Key:** book_rate **Type:** number #### Closing rate (local per 1 foreign) **Key:** close_rate **Type:** number #### Carrying value in local currency **Key:** book_value **Type:** number **Expr:** fx_balance * book_rate **Format:** currency #### Revalued amount **Key:** reval_value **Type:** number **Expr:** fx_balance * close_rate **Format:** currency #### Change in local currency **Key:** change **Type:** number **Expr:** reval_value - book_value **Format:** currency **Rows:** **Account:** Debtor: Lusaka distributor **Ccy:** USD **Type:** Asset **Fx balance:** 48000 **Book rate:** 18.1 **Close rate:** 18.45 **Account:** Creditor: Imported steel supplier **Ccy:** EUR **Type:** Liability **Fx balance:** 62500 **Book rate:** 19.8 **Close rate:** 20.15 **Account:** USD bank account **Ccy:** USD **Type:** Asset **Fx balance:** 21500 **Book rate:** 18.2 **Close rate:** 18.45 **Account:** Creditor: Spares supplier **Ccy:** GBP **Type:** Liability **Fx balance:** 9800 **Book rate:** 23.4 **Close rate:** 23.1 **Totals:** #### Total carrying value **Key:** book_value **Agg:** sum **Format:** currency #### Total revalued amount **Key:** reval_value **Agg:** sum **Format:** currency #### Total change before sign **Key:** change **Agg:** sum **Format:** currency **Notes:** A rise in value of an asset is a gain, a rise in value of a liability is a loss. Read the Change column with the Type column: positive change on an Asset is a gain, positive change on a Liability is a loss. Use the closing rate your accounting policy requires. This is a working paper, confirm treatment with your accountant. ## Content **Intro:** Businesses that trade across African borders hold balances in dollars, euros and neighbouring currencies, and every month end those balances must be restated at the closing rate. This worksheet does the arithmetic per line and shows the change in local currency. **Sections:** #### How to use it **Body:** List each monetary balance still open at month end: debtors, creditors, foreign bank accounts and loans. Enter the foreign amount, the rate at which it is currently carried, and the closing rate. The sheet works out the carrying value, the revalued amount and the change. Mark each line as an asset or liability, because the same movement is a gain on one and a loss on the other. #### The method **Body:** Monetary items are retranslated at the closing rate at each reporting date, and the difference goes to profit or loss, as in the principle of IAS 21. Non monetary items measured at historical cost, such as inventory and fixed assets, are not revalued. Realised differences arise when you settle an item, and unrealised differences arise at month end and usually reverse on day one of the next period. **Bullets:** - Revalue monetary items only - Use a consistent rate source and time - Reverse unrealised entries next period if your ERP requires it #### Common mistakes **Body:** The most common error is a wrong rate direction: quoting foreign per local when the ERP expects local per foreign. The next is revaluing inventory or prepayments. In countries with fast moving or multiple official rates, such as when a parallel rate exists, document which rate you apply and why, because auditors will ask. **Bullets:** - Check rate direction before entering - Exclude stock and fixed assets - Keep a dated rate screenshot or source **Cta:** If you run several currencies across entities, we can build a month end report that compares ERP revaluation journals with an independent recalculation. **Faq:** #### Does the ERP not do this for me? **Answer:** Most ERPs have a revaluation routine. This sheet is an independent check, useful when the routine output looks odd, when you are testing rate tables, or when you handle a few balances outside the system. #### How do I read gain or loss? **Answer:** For an asset, such as a debtor or bank balance, a positive change is a gain. For a liability, such as a creditor, a positive change is a loss, because you owe more in local currency. #### Which closing rate should I use? **Answer:** Use the rate your accounting policy specifies, commonly the central bank or bank spot rate at the reporting date. Apply the same source every month and record it with the working paper. #### What about currencies with unstable rates? **Answer:** Document the rate source and any restrictions on conversion, and discuss the treatment with your auditor. The arithmetic here stays the same, but the choice of rate needs a defensible reason. ## Search terms multi currency revaluation worksheet, unrealised exchange gain loss calculation, month end forex revaluation template, africa multi currency erp, ias 21 revaluation excel, foreign currency creditors revaluation, syspro currency revaluation --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json