Zoho Data Tables in Magnifi

Data Tables

What is a table?

A table is basically like a spreadsheet. It includes rows and columns and the raw data required to compile the reports (eg charts).

The default workspace includes the following tables that are linked to Magnifi;

  1. Accounts; Lists the monthly account balance in Magnifi for every account. 
  2. Finance; Summarises the monthly account balance for key account types (eg Income).
  3. Capacity; Summarises the capacity data including total capacity and effective capacity.
  4. Finance (excluding Inter-Entity); For organisations with multiple divisions, this table is identical to the Finance table except it excludes inter-entity transactions.

The Accounts Table explained

What is the Accounts Table?
      
The Accounts Table is the detailed list of ALL accounts in Magnifi including Profit and Loss and Balance Sheet accounts. It reports the Actual results, Budgets, Last Year balances and also the Forecasts. The Forecast column will adopt the actual results if they exist, otherwise it reports the budgets. This can be useful if you want to prepare a chart that shows the projection for the year based on actual results to date and budgets for the remainder of the year.

When would I use the Accounts Table?

You would typically use the Account Table when you want to report the balance for a specific account. For example, the Income chart below has been derived from the Accounts Table so that a user filter can be applied to view the chart by income account;





You can also use the Accounts Table to chart how specific accounts are trending, For example;




The Finance Table explained

What is the Finance Table?
      
The Finance Table represents a summary of the account balances for key account types. Some of the key Profit and Loss account types include;
  1. Income
  2. DLC (Direct Labour Cost)
  3. ODC (Other Direct Costs)
  4. OH (Overhead Expenses)
  5. Direct Profit
  6. Operating Profit
  7. Net Profit
  8. NPATD (Net Profit after Tax & Dividends)

Some of the key Balance Sheet account types include Current Assets, Non-Current Assets, Total Assets, Net Assets etc.

The table reports the Actual results, Budgets, Last Year balances and also the Forecasts. The Forecast column will adopt the actual results if they exist, otherwise it reports the budgets. This can be useful if you want to prepare a chart that shows the projection for the year based on actual results to date and budgets for the remainder of the year.

When would I use the Finance Table?

You would typically use the Finance Table when you want to report the balance for a specific account type. For example, the Direct Profit chart;



Or the following Direct Profit % chart which is based on a formula that divides Direct Profit by Income. 


When would I use the Finance (excluding inter-entity) table?

This table is identical to the Finance Table except it excludes the inter-entity transactions. You may use this if you want to prepare a consolidated chart which excludes the inter-entity transactions. For example, if you have a file with multiple divisions and there is a service fee paid from one division to another (inter-entity), the income that is reported in this table will exclude the service fee income.

The Capacity Table explained

What is the Capacity Table?
      
The Capacity Table reports the Total Capacity, Effective Capacity (Total x Recovery %), Income and # FTE's. It also includes formulae to calculate the average income per FTE.  

When would I use the Capacity Table?

You would typically use the Capacity Table to chart the projected income v projected capacity to ensure there is enough capacity to meet demand. For example;



How do I view the existing formula's?

We have already created some formula's within the tables which can be viewed by going to "Add" and then "Edit Formulas";



There are 2 types of formula that appear;

  1. Formula Column; This will add a separate column in the data table and show the formula which will calculate the value for each row.
  2. Aggregate Formula; This is used to apply calculation over your data by aggregating/grouping/combining them. For example, using a SUM function so it will sum the value when reporting for a specified time period. Most of the formula's derived from Magnifi data are aggregate formula's and these do not appear as a separate data column;




Although the aggregate formula's do not show as a separate data column, the formula's will be available when you prepare the charts. For example;




Advance features for Data Tables


For more advanced features including;
  1. Import/Create a new table
  2. Joining tables
  3. Data Types
  4. Adding Formula
Visit Zoho Support library.