The comparison is made per Microsoft subscription. It contains a comprehensive list of subscriptions which can be filtered in numerous ways, to find and address any discrepancies.

The services that can be reconciled from the tool are the following:

• Legacy Office 365 and Dynamics offers

• New Commerce Experience offers

• Azure Plans

• Software subscriptions

Image Removed

Reconciliation Algorithm

This section explains the algorithm the tool uses to show the results, and how the calculations are made.

1. The selected Reconciliation Period is the key filter that starts the algorithm. The tool performs the calculations and filters the appropriate invoice items based on the reconciliation period.

   1. For platform invoices: We collect invoice items that do not belong to canceled invoices. We collect the analytical items since the analytical lines are always linked with a single subscription.

   2. For Microsoft reconciliation lines: We collect all items that are part of the reconciliation period

2. For the platform charges, the tool filters invoice items based on the reconciliation period by checking the invoice item's Start or End Date. For example, for the reconciliation period 01/01/2023 - 31/01/2023, the tool will collect the following items:

   1. Items that belong to invoices created between 01/12/2022 - 30/04/2023

      1. We collect the invoices that were created at least one month before the reconciliation period because they might have items that charge days in the reconciliation period.

      2. We collect the invoices that were created at most 3 months after the end of the reconciliation period for covering scenarios where charges were missing and were created later.

   2. The invoice item's start or end date is in the reconciliation period. Paradigms of valid invoice items for the period 01/01/2023 - 31/01/2023 are:

      1. Item for the period 14/12/2023 - 13/01/2023

      2. Item for the period 05/01/2023 - 23/01/2023

      3. Item for the period 19/01/2023 - 18/01/2024

3. The tool calculates how many days of the invoice item’s billing period belong to the reconciliation period for defining the coverage factor. Due to the varying number of days in a month, we assume that a month always has 30 days. If, for example, an invoice item is for the period 22/12/20203 - 21/01/2023 and the reconciliation period is from 01/01/2023 - 31/01/2023, the coverage factor will be 21 (days in recon period) / 30 = 0,7.

4. This coverage factor is then multiplied by the item's total cost to calculate the cost for the specific reconciliation period.

5. Since the calculations are done per Microsoft Subscription (based on Microsoft’s ID), the cost per Microsoft subscription for the given reconciliation period is calculated by aggregating all the costs of all items for this subscription.

6. The 2 final costs (platform's and Microsoft’s) are compared, and the difference is displayed in the “Cost Difference” column.

Activation of the Reconciliation Tool

The activation can be done by your Customer Success Manager or by contacting our support department. To complete the activation we will need from you:

1. The credentials of an API user and your authentication keys - We suggest creating a new one specifically for the reconciliation application. We will use this user for collecting the invoices from your inteworks.cloud organization.

2. A Power BI Pro user. This is the user we will invite to our Power BI Reconciliation application. You can have more than one user accessing the application, but all your users must hold a Power BI Pro license.

3. The Microsoft reconciliation files for the previous month. The files must be unedited, as downloaded from the MPC, and in .csv format. Please check Upload your Microsoft Reconciliation Files for the naming of the files.

Using the Tool

Filters

Configure the the available filters to narrow down the results and filter out irrelevant data.

Image Removed

The following filters are available:

Filters

Configure the available filters to narrow down the results and filter out irrelevant data.

Image Added

The following filters are available:

• Reconciliation Period: Select the date range using the slider(s). The reconciliation period cannot be more than six months, and the earliest start date is February 1st, 2023. Note that the Valid Recon Period displayed is in mm/dd/yyyy format.

• Account ID: Use this filter if you wish to reconcile the subscriptions of a specific end customer. You should enter the ID of the BSS account record.

• Billing Account ID: For indirect CSPs, this filter can be used for reconciling the subscriptions of a specific reseller. You should enter the ID of the BSS account record.

• MS Subscription ID: You can use this filter to reconcile a specific Microsoft subscription for the defined reconciliation period.

• Microsoft Product Types: Use this filter for reconciling only specific types of Microsoft services:

  • Dynamics 365 - Legacy Dynamics 365

  • MSCloudServices - Legacy Office 365

  • MSOnlineServicesNceProductType - New Commerce Experience

  • MSAzurePlanProductType - Microsoft Azure Plan Subscriptions. This field will not be displayed if the specified reconciliation period is not equal to one or more full calendar months. Please see the related subsection here https://interworkscloud.atlassian.net/wiki/spaces/ICPD/pages/743440414/Power+BI+Reconciliation+Tool#Reconciliation-Algorithm

  • MSSoftwareSubscriptionsProductType - Software Subscriptions

• CSP Partner - By default will be the name of your organization. This field will have more options only if you have country tenants and you have asked the same Power BI user to be able to reconcile all your country tenants.

• Results Filtering -

  • All - Show all entries subscriptions within the specified reconciliation period

  • Discrepancies - Only show entries subscriptions with discrepancies

  • Missing Data - Only show entries subscriptions where data is missing

Tables

Reconciliation Table

• • . These are subscriptions that have been billed either from our system or from Microsoft but not from both systems.

Results Set

The results are displayed in the Reconciliation table. The reconciliation data can be found in the “BSS Data” and “MS Data” tables: The “BSS Data” table includes the platform invoice items, and the “MS Data” table includes the items found in MS reconciliation files.

Reconciliation Table

It includes a line per Microsoft subscription billed at least from one system (ours or Microsoft’s) in the given reconciliation period. It contains the following columns:

• Subscription ID - The subscriptions for which there was a transaction during the specified reconciliation period: The Microsoft Subscription ID.

• BSS Total Cost - : The total cost recorded by of the BSS invoice items for the specified reconciliation period.

• MS Total Cost - Same as above, recorded by Microsoft: The total cost of the Microsoft items for the specified reconciliation period.

• Cost Difference - Includes It is the difference between the two systems (in absolute value) along with one of the following symbols:

  • Green Checkmark - No difference or difference less than 1.00€

  • Red X - A difference of over 1.00€

  • Yellow Exclamation Mark - Insufficient data

Hovering over an entry shows more details in an expanded tooltip.

BSS Data Table

This table contains data the invoice items returned by the interworks.cloud platform API.

The table contains the following columns:

• Invoice Code - : The invoice reference code

• Invoice Date - The : the date the invoice was issued

• Billing Account ID - : The Bill To account ID

• BSS Subscription ID - : The ID of the subscription in the BSS

• Product - : The product the subscription is for

• Start Date/End Date - : The start and end dates of the subscriptioninvoice item.

• Qty - Quantity; the amount Number of licenses

• Unit Cost -: The cost per license

• Total Cost - : The cost for the entire quantity

• Unit Price - : The retail price without discount(s)

• Disc. % - : The percentage of the discount applied to the retail price

• Final Amount - The total amount for the entire quantity, with the discount applied

• . This is the invoice item’s total.

MS Data Table

This table contains data obtained from included in Microsoft reconciliation files.

• Type - : Legacy or NCE

• Product - : The product the subscription concernsis for

• Start Date/End Date - : The start/end dates of the subscription

• Change Type - : The type of change which created the record

• New - A subscription was created

• Renew - A subscription was renewed

• Convert - The billing cycle of a subscription was changed (i.e. a monthly subscription becomes annual)

Add Quantity - License(s) added

as found in Microsoft’s reconciliation file.

• Charge Start Date/Charge End Date - The start and end dates that are charged

• Qty - Quantity; the amount : Number of licenses

• Unit Price - : The price for a single license

• Sub Total - : The total for the entire quantity. VAT is not included.

• Total - : The sub-total including VAT

The excel fields we are using from Microsoft’s reconciliation files are the following:

Detailed Invoices Tables

Select a subscription from the Reconciliation Table, and then select See Detailed Invoices to show the detailed invoices table.

The Detailed Invoices Table is separated into BSS Data and MS Data.

BSS Data

The BSS Data contains the following columns in addition to those seen in the previous section:

• Account ID - The account issuing the invoice

• Invoice Type - Is either debit or credit

• Invoice Stage - The status of the invoice

• Invoice Due Date - The expiration date of the invoice

MS Data

The MS Data contains only the ID of the subscription, in addition to the columns seen in the previous section.

Upload your Microsoft Reconciliation Files

The reconciliation files obtained from MPC must be uploaded to the Sharepoint link provided by Customer Support. Please ensure that the following naming convention is followed:

• For NCE subscriptions:
  MONTHYEAR_MSRECON_NCE

• For legacy subscriptions:
  MONTHYEAR_MSRECON_Legacy

Image Removed