Invoice Reporting for Accounts

Upmind is designed to be compliant for accounting and capably handles businesses with $100m+ turnovers that need to pass external audits.

While simple reports can be obtained through the Upmind app, we suggest established users ingest reports to their own data warehouse.

In this article we'll explain the process and how invoice data is structured.

Accessing data

Reports can be downloaded from Insights and Reports> Reports

You can set automatic sends of daily reports to Amazon S3 or equivalent under Settings> Accounting and Reports - this will then send updated rows of each database to your S3 instance for ingestion into whichever datalake software you use (e.g. Snowflake).

For users on our Enterprise plan, we can on request set up read access to a replica database for you to read information in real time.

Fundamentals

There are a few fundamentals that we should explain first

Relations

• Ids within Upmind are stored as uuid strings.

Currencies

• Each brand has a base currencywhich is set and cannot be changed. All our amounts are stored in the base currency only. If invoices or payments are in other currencies, we store the exchange rate to the base currency.
• If you have multiple brands, then they can all have different base currencies.

Invoices and Credits

• If an invoice is cancelled, there will always be a credit note. There is no leakage from invoices. If you add up the sum of all invoice totals, you will get to the sum total of payments received + accounts receivable + wallet balance.

Service periods

• Every contract product will not have missing service dates. So there will be a consistent service date period.
• If the due date is changed for a contract product, or a contract product is added as an existing service with a start date in the past or future, then there might be zero (0) value items.

Tax

• Tax rules are flexible in Upmind, so we sum up the tax on each invoice item
• Tax reports are then given separately.

Product codes

• Product codes (product code 1 and product code 2) can be defined on the product level for product reporting. (See How to Create Products). These are set on each individual product. They are freeform and typically are used so you can map in your reporting.

Billings vs Revenue

• On the basis that billings tracks invoice creation date, and the revenue apportions the revenue over the lifetime of the service, you would typically take the creation date as the billing date.

Constructing revenue data

Upmind invoices are broken down into Invoice Items - These are individual rows on each invoice. Each row contains the following:

Service Start and End Dates

📘

In some instances you will see that the service start and service end dates are reversed. This is the case in credit notes where a due date might be rolled back.

If a service has not yet been created, then we do not know the service start and end date. A recommended approach is:

• If the start_date and end_date of the invoice is null and billing cycle months = 0 then it is replaced
  with the create date of that invoice.
• If the start_date and end_date of the invoice is null and billing cycle months > 0 then:
  ▪ In the billings model, the create date is taken as the start and end date
  ▪ In the revenue model, it remains null (as the product is considered unprovisioned)

Status inclusion

Invoice statuses can have various statuses. In logic you should include

• Paid
• Unpaid
• Allocated (credited against a Paid invoice)

Currency Conversions

Here is an explanation for currency conversions used.

You may choose to report either in the Upmind base currency, or you may want to convert first from the brand currency to the document currency, and then use your own spot rate (we recommend openexchangerates.com) to convert to your own reporting currency.

Examples

Calculating Billings

• Convert native net / total amount from brand currency back to the document currency code using the
  Currency Exchange Rate
• Convert to reporting currency at a spot or constant exchange rate.

Calculating Payments (example in USD)

• If invoice_paid_amount = 0, then Payment Amount is zero
• If Document Currency Code equals Payment Currency Code, Reported Payment Amount equals USD (Total
  / Net) Amount
• If Document Currency Code does not equal Payment Currency Code then:
  • If Payment Currency Exchange Rate = 1, then use:
    ▪ Invoice_paid_amount as the amount
    ▪ Payment Currency Code as the currency code from which to convert to USD using
    own exchange rates
  • If Payment Currency Exchange Rate does not equal 1 (and is not null) then:
  • Convert the total amount from brand currency back to the document currency code
    using the Currency Exchange Rate to reach the value / currency listed on the invoice
  • Take the product of this amount and the Payment Currency Exchange Rate to calculate
    the local amount paid by the customer in the Payment Currency Code
  • Convert this amount / code to USD using own exchange rate
  • If Payment Currency Exchange Rate is null then:
    • USD Payment Amount equals USD (Total) Amount

Building Revenue

We distinguish here between two types of revenue:

• Commercial revenue - to give accurate periodic representations. So if a service is credited or never paid, it could affect past periods.
• Accounting revenue - this does not backdate revenue

You can build revenue by aggregating the invoice line data, as follows

Examples

Commercial Revenue

Commercial revenue can be calculated by multiplying the days in a revenue period by the day value.

• Days in Month is calculated by finding the number of days in a month which fall on or after the service
  start date and whichever occurs first between the service end date & end of month
• Commercial revenue is calculated by taking the product of Days in Month and Day Value for each
  given month covered by the service period
• This process is repeated for net and total values as well as spot and constant conversions

Commercial Deferred Revenue

Commercial Deferred Revenue is calculated using the following steps:
• The product of cumulative days and Day Value is taken to calculate the total revenue that has
currently been earned since the beginning of the service period
• This value is subtracted from the overall value of the invoice line to give the remaining value of liability
yet to be delivered

Accounting Revenue

Accounting revenue does not backdate revenue, so no revenue is allocated to days in the service period before
the create date of the invoice. It is calculated using the following steps:

The Day Value is calculated using the same methodology as with commercial revenue

To calculate the number of days which are multiplied by the Day Value, and by extension calculate
the Accounting Revenue in a month, the following logic is followed:

• If the month listed in the row (date_period) is less than the month of the invoice date (create
  date), then the accounting revenue is zero
• If the month listed in the row (date_period) is equal to the month of the invoice date (create
  date), then take the product of Cumulative Days and Day Value
• If the month listed in the row (date_period) is greater than the month of the invoice date (and
  within the service period) then take the product of Days in Month and Day Value

Accounting Deferred Revenue

Accounting Deferred Revenue is calculated using the following steps:

• If the month listed in the row (date_period) is greater than or equal to the invoice date, the product of
  cumulative days and Day Value is taken to calculate the total revenue that has currently been
  earned since the beginning of the service period
• This value is subtracted from the overall value of the invoice line to give the remaining value of liability
  yet to be delivered
• If the month listed in the row (date_period) is less than the invoice date then there is no deferred
  revenue allocated
• This process is repeated for net and total values as well as spot and constant conversions

Payments

The Payments table is based primarily on the Payments export data and derives daily data at a transaction line
aggregation, with a row for each month a customer has a new transaction type-product-date combination.
The following calculations are used to reach the KPIs in reporting:

• Convert the Payment Amount using the Currency Exchange Rate (that is native to the
  Payments Export table) to calculate the local amount for the transaction.
• Take the product of this amount and the local exchange rate using the Currency Code which is
  native to the table to convert from local to reporting currency:
  ▪ For spot exchange rate use the date which is native to the table
  ▪ For constant exchange rate use today’s date

Tracking Customer Status

The invoice items table can also be used to track customer status.

Customer status is determined based on the presence and timing of active invoices. How you define this is down to your logic, but one example might be:

Accounting in transition

When importing to Upmind, Upmind only reports invoices generated in Upmind and does not report out historic invoices. While those can be imported to Upmind as legacy invoices, those are effectively just meta data for your customers.

Therefore the important thing on your accounting is just to credit off any historic unpaid invoices from old systems.

You can map Upmind contract products and clients to external services to help with your reporting. We store there an import id as well as an external id which you can use for your mapping.