Our sales forecast reports are showing incorrect totals when deals involve multiple currencies. We’re running SuiteAnalytics workbooks that pull opportunity data with projected revenue, but the currency conversion seems inconsistent. Some opportunities convert at current rates while others use historical rates from when the forecast was created.
The issue becomes critical in our quarterly forecast validation reports. When we compare SuiteAnalytics totals to what our CRM shows natively, we’re seeing variances of 5-8% in aggregate pipeline value. Our finance team needs accurate multi-currency forecasts for board reporting, and right now we can’t trust the numbers.
Here’s a sample of how we’re pulling the data:
SELECT opportunity.name,
opportunity.projectedtotal,
opportunity.currency
FROM opportunity
WHERE opportunity.forecasttype = 'Forecast'
The projectedtotal field seems to return values in the opportunity’s native currency, but when we aggregate in the workbook, the conversion logic doesn’t match our exchange rate updates. Has anyone solved multi-currency forecast reporting accuracy in SuiteAnalytics?
Let me walk through a complete solution for accurate multi-currency forecast reporting that addresses your currency conversion logic, exchange rate updates, and forecast validation needs.
Understanding SuiteAnalytics Currency Conversion:
The core issue is that SuiteAnalytics workbooks don’t automatically apply current exchange rates to forecast data. Your SQL query retrieves projectedtotal in the opportunity’s transaction currency, but workbook aggregations need explicit conversion instructions.
Proper Dataset Configuration:
Modify your dataset to include the consolidated exchange rate table with a current-rate join:
JOIN consolidatedexchangerate cer
ON opportunity.currency = cer.fromcurrency
AND cer.tocurrency = {base_currency}
AND CURRENT_DATE BETWEEN cer.effectivestartdate
AND COALESCE(cer.effectiveenddate, '2099-12-31')
This ensures each opportunity converts using the most recent exchange rate available. The COALESCE handles open-ended rate records that don’t have an end date.
Exchange Rate Update Workflow:
Establish a systematic rate update process:
- Schedule exchange rate imports weekly (or daily for volatile currencies) via Setup > Accounting > Consolidated Exchange Rates
- Create a saved search that identifies opportunities with currency mismatches or missing rate conversions
- Set up workflow alerts when exchange rate gaps exist for active forecast currencies
For your quarterly board reporting cycle, lock exchange rates at period-end. Create a custom field on opportunities called “Forecast Rate Lock Date” and modify your dataset join to use locked rates when present:
AND COALESCE(opportunity.forecasteratelock, CURRENT_DATE)
BETWEEN cer.effectivestartdate AND cer.effectiveenddate
Forecast Report Validation Framework:
Implement three-way validation to ensure accuracy:
-
Source Validation: Build a reconciliation workbook comparing:
- SuiteAnalytics aggregated forecast (with current rates)
- Native NetSuite forecast reports (using system conversion)
- Manual calculation using your documented rate table
Any variance >1% triggers investigation. Common culprits: missing rate entries, currency field changes, or subsidiary mismatches.
-
Rate Consistency Check: Create a dashboard showing:
- Last exchange rate update timestamp per currency
- Count of opportunities using each currency
- Average rate variance between current and 30-day historical rates
This helps finance understand rate volatility impact on forecast accuracy.
-
Currency Change Audit: Track opportunities where currency changed after creation. Build a saved search:
- Opportunity.Currency.LastModified within current quarter
- Opportunity.Probability >= 50%
- Alert sales ops for review
Workbook Formula Best Practices:
In your SuiteAnalytics workbook, create calculated fields for converted amounts:
- Projected Revenue (Base Currency) = opportunity.projectedtotal * cer.exchangerate
- Weighted Forecast (Base Currency) = (opportunity.projectedtotal * cer.exchangerate) * (opportunity.probability / 100)
Always display both native currency and converted amounts in forecast reports so stakeholders can verify conversion logic.
Implementation Roadmap:
- Week 1: Update dataset with proper exchange rate joins and validate on small sample
- Week 2: Build reconciliation workbook and document variance investigation process
- Week 3: Implement currency change controls and rate update workflows
- Week 4: Train finance team on new validation framework and board report refresh schedule
This approach eliminated our 5-8% variance issues and gave finance confidence in multi-currency forecasts for investor reporting.
Also watch out for opportunities that change currency after creation - we had forecast variances traced to sales reps updating deal currencies mid-quarter without realizing it triggered re-conversion. Implement validation rules to lock currency once an opportunity reaches a certain probability threshold. And document your exchange rate update schedule clearly so finance knows when to refresh forecast reports for accurate board decks.
You need to modify your dataset to include an explicit join to the consolidatedexchangerate table. The key is joining on both currency and a calculated effective date. For current rate forecasting, use TODAY() as your effective date. For historical rate analysis, join on the opportunity’s close date or forecast period end date. The exchange rate table stores rates with effective date ranges, so your join condition needs to find the rate where your chosen date falls between effectivestartdate and effectiveenddate. This gives you control over which rate applies to each forecast record.
The issue is that SuiteAnalytics uses the exchange rate effective date differently than native NetSuite reporting. By default, workbooks convert currencies using the rate from the transaction date, not the current rate. For forecasts, this means opportunities created months ago convert using old rates. You need to explicitly specify which exchange rate table to use in your dataset joins.
I see the exchange rate table in the dataset relationships, but I’m not clear on how to force current rates vs historical rates. Should I be using a specific date field in the join condition?
Check your consolidated exchange rate settings under Setup > Accounting > Consolidated Exchange Rates. We had a similar problem where our weekly exchange rate updates weren’t being applied to forecast calculations. The fix was ensuring our SuiteAnalytics datasets joined to the currency exchange rate table with the correct effective date logic. Also verify that your base currency in the workbook matches your subsidiary’s reporting currency - mismatches there cause silent conversion errors that are hard to trace.