This tool can be used by indirect and direct Microsoft CSPs to cross-check the charges between the interworks.cloud platform and Microsoft. It compares what Microsoft charges to the CSP against the cost of the invoice items our platform has generated.

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.

Image Removed

.

Image Added

Main Calculation Algorithm

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

1. The Reconciliation Period date filter is the key filter that starts the algorithm. With it, the tool performs the calculations and filters the appropriate invoice items.

2. Due to the varying number of days in a month, the days between ChargeStartDate and ChargeEndDate are calculated for each invoice item. The tool divides that value by 30 and assigns the result to the invoice item. This is used as a factor for calculating the invoice amount for the reconciliation period.

3. 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 invoice items will be collected will be:

   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. Parading of valid invoice items for the period 01/01/2023 - 31/01/2023

      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

4. The tool calculates the cost by checking the number days of the Reconciliation Period which are in the period between the ChargeStartDate and the ChargeEndDate, and getting the factor. E.g., if an item has Start Date 25/12/2022 and End Date 24/01/2023 and the reconciliation period is from 01/01/2023 to 31/01/2023, the factor will be 24 days for January divided by 30 days.

5. This factor is then multiplied with the cost and results in the cost for the specific reconciliation period

6. Since the aggregation must be on the Microsoft ID level (and some BSS IDs have the same Microsoft ID) the tool takes the cost of the items with the factor and adds them together, resulting in the final cost

7. The 2 final costs (BSS and MS) are compared, and the difference is displayed in the Cost Difference column

Prerequisites

There are certain prerequisites for using the Reconciliation Report tool. A user must have:

• A Power BI Pro user

• An API user on the interworks.cloud platform

• Last 6 months reconciliation files from MS (The files must be unedited, as downloaded from the MPC, and in .csv format)

Once the prerequisites have been secured, you may contact Customer Success to activate the tool.

Using the Tool

Filters

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

The following filters are available:

• CSP Partner - The name of your organization

• Type of Product - Select between Legacy or New Commerce Experience products

• Microsoft Product Types - Filter the results by one or more specific product types

• MS Subscription ID - The MPC subscription ID

• IWCP Account ID - The interworks.cloud BSS account ID

• IWCP Billing Account ID - The Bill To account ID from the BSS

• Date Slider - Select the date range using the slider(s). Note that the Valid Recon Period displayed above is in mm/dd/yyyy format.

• Results Filtering -

  • All - Show all entries within the specified reconciliation period

  • Discrepancies - Only show entries with discrepancies

  • Missing Data - Only show entries where data is missing

Tables

Reconciliation Table

The reconciliation table is used to look up subscriptions contains the following columns:

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

• BSS Total Cost - The total cost recorded by the BSS for the specified reconciliation period

• MS Total Cost - Same as above, recorded by Microsoft

• Cost Difference - Includes one of the following symbols:

  • Green Checkmark - No difference

  • Red X - A difference of over 1.00€

  • Yellow Exclamation Mark - Insufficient data

Image Removed

Image Added

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

BSS Data Table

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

The table contains the following columns:

• Invoice Code - The invoice reference code

• Invoice Date - The date the invoice was issued

• Billing Account ID - The Bill To account

• 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 subscription

• Qty - Quantity; the amount 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 discount applied to the retail price

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

MS Data Table

This table contains data obtained from Microsoft.

• Type - Legacy or NCE

• Product - The product the subscription concerns

• 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

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

• Qty - Quantity; the amount 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

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.

Image Removed

Image Added

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

Main Calculation Algorithm

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

The tool filters invoice items based on the reconciliation period date filter by checking the ChargeStartDate or the ChargeEndDate of the invoice item. For example, for the reconciliation period 10/06 - 30/06 invoices are collected which have: