Excel Accounting Actuals

Import your accounting actuals from any GL System via Excel to begin planning and reporting in Jirav

Article Contents

• Import Instructions
• Erase &/Or Update Actuals
• Jirav Departments and Excel Departments
• Helpful Hints

---

Import Instructions

To get started, import the chart of accounts, account balances, and optionally, a department structure from Excel following these steps.

Step 1: Download and Populate the Excel Import Template

1. Download the Excel Manual Import Template to Excel

   • Login to Jirav and navigate to Settings ⚙️ > Integrations > Locate the Excel Manual import Tile.

   • Click the Template link to download the template to excel.
     
     

2.   Populate the Excel Import Template with necessary details in each tab  following these guidelines:

• Company Setup:

  • Version: This is for informational purposes only; It is recommended to populate this field with the date the tab was last updated. 
  • Company Name: Enter the name of the company that will utilize this Jirav account.
  • Fiscal Year Start Month: Enter the first month of the fiscal year formatted as date (mm/dd/yyyy).
  • Accounting Method: Input either Cash or Accrual.
  • Currency: Specify the currency using the 3 letter abbreviation.
  • Close Month: Enter the last month closed formatted as date (mm/dd/yyyy).
  • An Example:
  • 
• COA (Chart of Accounts): 
  • Version: This is for informational purposes only; It is recommended to populate this field with the date the tab was last updated. 
  • Account ID: Enter a unique identifier for each account. It is recommended to populate this field with the account number from the accounting system and to avoid using spaces or special character. 
  • Parent ID: Optionally use for groupings/sub-accounts.
  • Account Name: Enter a unique identifier for each account - duplicate names are not allowed.This is the name that will be displayed in Dashboards, Reports and Plans by default.
  • Account Class and Account Category: Choose Asset, Liability, Expense, Revenue, or Equity from the Account Class dropdown. Available choices for Account Category will populate based on the selection of Account Class. Account Category and Account Class should be shaded in the same color when populated correctly.
  • Account Function: Optionally tag Balance Sheet accounts as Accounts Receivable or Accounts Payable. This tag is used to generate the values of the KPIs of the same names.
  • Account Number: Optionally input the account number from the accounting system. It is recommended to set this to be the same numerical ID assigned in Column A. This column determines the order of accounts in the COA. This field is also visible in the Chart of Accounts when making manual changes to the Chart of Accounts later in Jirav. This column should be formatted as text. 
  • Pro Tip: There can only be one account that is tagged with the Account Category Equity and Account Class Retained Earnings. 
  • Pro Tip: When managing the COA import tab, always add new accounts and never remove existing ones, even if there is no activity in an account going forward. Removing an account will delete the account from all time periods in Jirav, not just the month you are updating.  For example, if an account included in the January import is removed in February, it will be deleted from the Jirav structure, causing the January actuals to be adjusted to no longer include the deleted account. As a result, the actuals for January would likely be incorrect. 

• • An Example:
  • 
• Departments:
  • Version: This is for informational purposes only; It is recommended to populate this field with the date the tab was last updated. 
  • Department ID: Enter a unique identifier for each Department. It is recommended to populate this field with the department code from the accounting system and to avoid using spaces or special character. 
  • Parent ID: Optionally use for sub-departments.
  • Department Name: Enter a unique identifier for each department - duplicate names are not allowed.
  • An Example:
    
  • 
• Income Statement:
  • Version: This is for informational purposes only; It is recommended to populate this field with the date the tab was last updated. 
  • Account ID: Must match COA.
  • Department: Must match Department ID. If Departments are not outlined in the Department tab, leave blank.
  • Calendar Month: Enter the month formatted as date (mm/dd/yyyy). A single month or multiple months can be imported with the same file. 
  • Amount:
    • The amount must be formatted as currency.
    • This field cannot be empty. Enter zero dollars if necessary.
    • The amount should reflect the monthly activity for the specific month, not the year-to-date total.
    • The amount should generally be entered as a positive value, except for contra accounts such as discounts and returns.

• • Pro Tip: There cannot be duplicate rows with the same account, department, and calendar month combination. Aggregate the data prior to populating the Income Statement tab for a successful import. 
  • An Example:
    
  • 
• Balance Sheet:
  • Version: This is for informational purposes only; It is recommended to populate this field with the date the tab was last updated. 
  • Account ID: Must match COA.

• • Calendar Month: Enter the month formatted as date (mm/dd/yyyy). A single month or multiple months can be imported with the same file. 
  • Amount:
    • The amount must be formatted as currency.
    • This field cannot be empty. Enter zero dollars if necessary.
    • The amount should reflect the cumulative, ending balance for the specific month.
    • The amount should generally be entered as a positive value, except for contra accounts such as accumulated depreciation.

• • Format as currency; cannot be left blank (use zero-dollar if necessary). The amount should represent the ending balance for the given month. 
  • Pro Tip: There cannot be duplicate rows with the same account, department, and calendar month combination. Aggregate the data prior to populating the Income Statement tab for a successful import. 
  • Pro Tip: Do not include YTD Earnings in the import. This is a system calculation in Jirav.
  • An Example:
    
  • 

3.   Save the populated template on your computer in a location you can easily access.

• Pro Tip: Consider saving your files in a consistent location with a clear naming convention. For instance, you could create a folder named Jirav Actual imports and name the files using a convention like "Your Company Actuals April 2024 Close as of May 15".

Step 3: Import Accounting Data to to Jirav

1. Login to Jirav and navigate to Settings ⚙️ > Integrations.
2. Locate the Excel Manual import in the Actuals: Accounting section and click IMPORT.
   
3. From our file explorer, Open the applicable Excel workbook saved in Step 2.

After completing these steps, you will receive a confirmation indicating that the data import was successful.

You can now start exploring dashboards, reports & plans!

Erasing Actuals or Replacing

If your Actuals import has errors or needs updates, it's important to clear the originally imported data before importing a new file. Reimporting without clearing can leave stale data in the system, causing unexpected results. Follow these steps to reset the month to a clean slate:

1. Start with the Original File: Use the file that was originally uploaded for the given time period.
2. Zero Out the Data: Replace all values with zeros while keeping the structure (rows for dates and accounts) unchanged.
3. Clear the Old Data: Reimport the zeroed file into to erase the previously imported Actuals.
4. Verify the Old Data is Removed: View a Income Statement & Balance Sheet account for the given time period to verify it reflects no data. 
5. Upload Corrected Data: Upload the new file with updated data.

This method ensures data integrity and avoids unexpected values.

---

Jirav Departments and Excel Departments

Departments serve as a valuable tool for organizing financial data, facilitating accurate reporting, and gaining insights into various aspects of your business's operations and performance.

To enable this feature, go to Settings ⚙️ > Departments and map the chosen dimension values in the JIRAVEXCEL column to their corresponding Jirav Departments by clicking in the white space to the right of Jirav department in the MASTER column.

 

---

Back to top