Data Foundation: Enterprise Profitability Template

Data Foundation builds a ready-to-use Enterprise Profitability project for you. Start a project from the Enterprise Profitability template, connect NetSuite or add CSV and Excel files, and press Load. PlaidCloud conforms the data into the Common Finance Model, checks that it ties out, and builds a profit and loss model with margin bridges against last year and budget. Everything happens on the project’s Foundation tab.

One Load reads every source, conforms it, checks it and rebuilds the model.

Create an Enterprise Profitability Project

1. In Analyze, open Projects and click New Project.

2. Enter a name. Under Start from, choose Enterprise Profitability (template) instead of Blank project.

3. To explore before you connect your own data, tick Start with sample data.

4. Click Create.

The new project opens on its Foundation tab. It is private to you, and you are its only Architect, until you share it.

The template creates:

• The Common Finance Model tables, empty or filled with sample data.
• Entity, Account and Department dimensions built from those tables.
• The Load workflow and the workflows it runs: NetSuite pull, Load files, Conform, Readiness and Profitability model.
• A profit and loss table with revenue, COGS, gross margin, direct and overhead operating expenses, contribution and operating margin, plus margin bridges year over year and against budget.
• Metrics and an analysis path for asking questions about the results.

With sample data, the project runs a first Load straight away, so the Foundation tab shows a complete example. When you connect your own NetSuite account or add your own files, the sample rows are cleared and your data takes their place.

The Foundation Tab

The Foundation tab is where you connect, load and check your data. It has these sections, top to bottom:

Section	What it shows
Last load	Whether the last load succeeded, failed or is still running, with the Load, Refresh and Refresh nightly buttons.
Setup	The setup checklist: Connect your data, Load your data, Check that totals tie out, Review your hierarchies and Share with your team. The current item is in bold.
Files	Each file you added, its dataset, and whether its mapping is ✓ Confirmed or ○ Waiting for confirmation.
Data loaded	For each entity, the first and last period loaded and how many periods are ready.
Periods	Each entity’s fiscal periods and whether each is ✓ Closed or ○ Open.
Readiness	The readiness grid: one row per entity, period and check, with its status and What to do.
NetSuite connection	Connect a NetSuite account, choose subsidiaries and how many months of history to load.
Files (add)	Add a CSV or Excel file and confirm how its columns map.
Load	Start a load.

While a load runs, the tab refreshes on its own.

Connect NetSuite

Data Foundation reads NetSuite through a NetSuite connection that uses OAuth 2.0 client credentials with a certificate. PlaidCloud only reads from NetSuite.

1. Under NetSuite connection, pick an existing NetSuite connection, or choose Create a new connection.

   Each existing connection lists the workflows that already use it, or says Not used by any workflow yet.

2. For a new connection, fill in:

   Field	Value
   Connection name	A name for the connection, for example NetSuite Production.
   Environment	The connection environment, such as production or sandbox.
   Account ID	Your NetSuite account ID, for example 1234567, or 1234567_SB1 for a sandbox.
   Client ID	From the NetSuite integration record.
   Certificate ID	From the NetSuite certificate mapping.
   Private key	The private key that matches the certificate, pasted as the whole PEM block.

   Don’t have these yet? The form shows a ready-made request for your NetSuite administrator. Click Copy request or Email request, send it, and enter the Account ID, Client ID and Certificate ID they send back. The private key never leaves your team: paste it in yourselves. See NetSuite Access for Data Foundation for what the administrator sets up.

3. Click Test connection. PlaidCloud signs in and tries to read each dataset, then lists each as Readable, Missing permission or Error. When something is missing, it names the permissions to ask your NetSuite administrator to add to the role.

4. Set Months of history — how many months back to load, from 1 to 120. The default is 24.

5. Under Subsidiaries, tick the subsidiaries to load, or leave them all clear to load every subsidiary. Inactive and elimination subsidiaries are hidden until you tick Show inactive and elimination subsidiaries. A new connection lists its subsidiaries once it is created; until then, all of them are loaded.

6. Click Connect. After creating a new connection, choose its subsidiaries and click Connect again to save them.

Once connected, the card says which connection, environment, subsidiaries and months of history the project loads.

Each load reads only what changed: open periods and the last closed period are read again, and closed periods already loaded are kept. Raising Months of history reads just the added months.

Add CSV and Excel Files

Use files for data that isn’t in NetSuite, such as a budget or allocation drivers, or to run Data Foundation on files alone.

1. Under Files, choose the Document account to store the file in.

2. Drop a CSV or Excel file on Drop a CSV or Excel file here, or click Choose a file. The file is uploaded to the /foundation/ folder of the document account.

   For a file that is already in the document account, enter its File path, for example /foundation/budget.csv, and click Add file.

3. PlaidCloud reads the file and proposes a mapping:

   • Detected delimiter, decimal separator and dates. The delimiter (comma, semicolon, tab or pipe), the decimal separator and the date format are detected for you.
   • Dataset — the Common Finance Model table the file belongs to, matched from the file name and column headings. When the match isn’t clear, choose it from the list.
   • Decimal separator — Period (1,234.56) or Comma (1.234,56).
   • Date format — shown when the file has dates. Change it if the detected format is wrong: YYYY-MM-DD, DD.MM.YYYY, DD/MM/YYYY or MM/DD/YYYY.
   • A grid of Column in file, Type, Examples and Goes to. Change Goes to to send a column to a different field, or to Don’t load.

4. Check where each column goes, then click Confirm mapping.

When PlaidCloud can’t decide the dataset, the decimal separator or the date order on its own — for example a file whose dates could read as day/month or month/day — it asks you to choose before you confirm. Adding a second file for the same dataset replaces the first.

Accepted files are CSV, TSV and TXT text files, Excel .xlsx files (read from the first sheet) and Parquet files.

Note

A file that is missing or empty never stops a load. Adding it says there is nothing to map yet, and a load finishes with a warning that names the path, leaving that dataset empty. Add the file again once it exists.

Load Your Data

Click Load on the Load card or the Last load card. The load reads NetSuite and your confirmed files, conforms them into the Common Finance Model, runs the readiness checks and rebuilds the profitability model.

• Only one load runs for a project at a time. A second Load says a load is already running.
• If other workflows use the same NetSuite connection, the first load lists them and asks you to confirm, because a load reads from NetSuite and can slow those workflows down. Click Load anyway to go ahead.
• A failed load shows red and never hides behind earlier results. Rows from an earlier load show From an earlier load. Load again. until a load succeeds.
• If one subsidiary and period fails to read from NetSuite, the rest still load. Run Load again to fetch only what failed.

Readiness Checks

After each load, the Readiness grid lists every check for every entity and period, with one of these statuses:

Status	Meaning
● Ready	The check passed.
▲ Needs attention	Worth a look, but the numbers are usable.
■ Problem	Fix this before you rely on the results.
– Not applicable	There is no data for this check yet.

The checks are:

Check	What it confirms
Data loaded	Every fiscal period has general ledger lines, and every NetSuite read succeeded. A closed period with no lines is a problem; an open period with none needs attention.
Trial balance ties to GL	The trial balance equals the general ledger for each entity, period and account, and for each department when the trial balance carries departments.
Sales tie to GL	Sales lines equal the general ledger revenue and COGS accounts for each period.
Unknown products/customers	Every sales line names a product and customer that exist in the product and customer masters.
Accounts missing from chart	Every general ledger line names an account in the chart of accounts. Lines with an unknown account are left out of the profit and loss.

Each row that isn’t ready says what to do in the What to do column, such as loading the chart of accounts or running Load again for a period.

The grid also flags setup that needs attention, such as an overhead allocation that is only half set up or a budget comparison with no budget rows in the chosen version. The project variables overhead_department, overhead_driver and budget_version control these.

Finish the Setup Checklist

• Connect your data and Load your data tick themselves once a source is attached and a load succeeds.
• Check that totals tie out ticks itself when the tie-out checks pass.
• Review your hierarchies — click Open dimensions to review the Entity, Account and Department hierarchies, then tick I have reviewed them. You confirm this one yourself.
• Share with your team — share the project when it is ready. See Managing Project Access.

Refresh Nightly

After a load from NetSuite succeeds with no problems, Refresh nightly appears on the Last load card. Click it to run Load every night at 2:00 in your time zone. The card then shows Nightly refresh: Every night at 2:00 and who set it up.

The nightly load runs as the person who set it up. If that person can no longer use the NetSuite connection, the grid says the nightly load is skipped; set it up again. A project that has only sample data can’t be scheduled.

Common Finance Model Tables

Data Foundation conforms every source into the same twelve tables, so reports and models built on them work the same whatever the source. Each row also records its source system and when it was read.

Dataset	Table	Holds
Entities	cfm_entity	Legal entities with parent, local and group currency, and country.
Fiscal calendar	cfm_fiscal_calendar	One row per entity fiscal period, with start and end dates and whether it is closed.
Chart of accounts	cfm_account	Accounts with parent, type (Revenue, COGS, Opex, NonOperating, Asset, Liability, Equity), statement and normal balance.
Departments	cfm_department	Departments and cost centers with parent and profit center.
Products	cfm_product	Product master with family, group, unit of measure and standard cost.
Customers	cfm_customer	Customer master with group, region and country.
General ledger	cfm_gl_journal	Journal lines in transaction, local and group currency.
Trial balance	cfm_trial_balance	Period balances by entity, account and department.
Sales lines	cfm_sales_line	Invoiced sales lines with quantity, gross, discount, net and COGS amounts.
Inventory balances	cfm_inventory_balance	Period-end inventory by product and warehouse.
Budget	cfm_budget	Budget and forecast by scenario, version, account and department.
Allocation drivers	cfm_stat_driver	Statistical drivers such as headcount, square feet or units.

Periods are written YYYY-MM. From NetSuite, transactions are placed by posting period, keys are NetSuite internal IDs, accounts are account numbers, and class and location are kept as extra segments. Only the primary accounting book is read. Budget is optional: a NetSuite account without budgets still loads everything else.

Current Limits

• No currency conversion. Amounts are kept in each subsidiary’s base currency. Group totals across subsidiaries with different currencies are summed as they are, not translated.
• Flat NetSuite hierarchies. Accounts and departments read from NetSuite load as a flat list. Build the parent-child structure in the Account and Department dimensions, or supply it in a file.

Troubleshooting

Symptom	What to do
Test connection shows Missing permission for a dataset	Ask your NetSuite administrator to add the permissions it names to the role.
“NetSuite refused the credentials.”	Check the Account ID (including any _SB1 suffix), Client ID and Certificate ID, and that the private key matches the certificate NetSuite holds.
A NetSuite error message is shorter than NetSuite’s own	PlaidCloud removes credentials, tokens and keys from provider errors before showing them, so some detail is left out. Run Test connection to see which dataset is affected.
Data loaded is a problem for a period	A NetSuite read for that period failed. Run Load again; periods that loaded are skipped.
Accounts missing from chart is a problem	Load the chart of accounts, or add the missing accounts to it, and run Load again.

Related

• NetSuite Access for Data Foundation — what your NetSuite administrator sets up.
• NetSuite: Import SuiteQL Query — the step the Load workflow uses to read NetSuite.
• Connect to NetSuite
• Dimensions
• Managing Projects