Cloudmore functionality ingests data from Microsoft and then applies pricing rules, depending on the service, that calculates the sales price. This information from all services is available.
The purpose of this essential monthly routine is twofold -
1. To check your margin at a customer and service/product level
2. To check for any anomalies in cost prices, credits, or other changes in the vendor pricing APIs (only applicable for some vendors)
This routine is vital if you provide Microsoft services, including Microsoft365 and Azure Plan, as there can be unexpected and unforeseen results from the Microsoft billing APIs at times
Important Note - For this billing report check exercise, it is essential that the general billing report is used to load data into the excel billing template.
This guide will walk you through
- The exporting of the data is required to perform the reconciliation.
- The import of the data into the Excel template.
- The review of the results.
Please follow the steps outlined in the document, and if you have any problems, please check the steps you have taken. If you still are having issues, please contact the Cloudmore support Team
The Excel template can be a great help in identifying billing issues. If you ask Cloudmore support to help with a billing issue, we will ask you to send a version of this template with the relevant billing data and your Microsoft billing file. This will help us to help you quicker by identifying the issue and assisting you in identifying future issues yourself.
Monthly Billing Check Routine – Walkthrough
Step One – Exporting your data from Cloudmore
Log in to Cloudmore and navigate to Billing>Billing Report
(Important – Only use this billing report for this routine)
- Select the period you want to process
- Select the services you want to process (Normally "All)
- Select Show Prices for organizations
- Select "Action" – "Send Export Once"
- Enter the email address for the report to be sent to
- Select "Run Report"
Figure 1 - Billing Check Routine Image 1
Depending on the volume of data, the report will typically arrive in the designated mailbox within a few minutes. And note the email may get focused to a non-focused mailbox if you use Outlook or your junk email folder if you do not usually receive emails from firstname.lastname@example.org.
Download the file. It will download to your download folder or other designated downloads folder and then open in Excel
Open the Excel Billing Check Template (Download from the link below if you don't already have the file)
Excel Billing Check Template Download (Monthly Billing Check Template )
Next, you need to copy columns A:U from the Cloudmore billing file into the first sheet named Excel Billing Check Template from Cloudmore.
You can do this by clicking on column "A" header and shift-clicking column "U" header. Once the selected columns are selected and highlighted, you can either
- edit>copy from the menu or,
- right-click on a column and select copy
- Or ctrl/c (PC) or Command C (Mac) on your keyboard
Now move to the Excel Billing Check Template and paste the copied data into the first sheet named Billing Import from Cloudmore.
Click into cell A1 on the 1st sheet and
- edit>paste from the menu or,
- right-click on a column and select paste
- Or ctrl/v (PC) or Command v (Mac) on your keyboard
You will now have your downloaded data in the correct place in the Excel template
Now move to Sheet 2 named "Service Invoice Reconciliation" and refresh the pivot table by clicking anywhere on the pivot table and clicking refresh or click on the pivot table and so to the pivot table menu and select refresh
Note – The pivot tables in the other sheets should have now refreshed automatically but if not, repeat the same process for each sheet
Service Invoice Reconciliation Analysis
There are two options to configure on this sheet before you start
- If you work in multiple currencies, select the currency you wish to analyze
- Select the Low Margin Check percentage value (Cell G2)
The low margin check value will highlight line items that are below the low margin value check value you enter.
The first check is your "Total Cost" column against your service supplier invoice. Ideally, these values should reconcile.
The following checks ensure your sales prices in Cloudmore are resulting in the Service Margin are at the expected level. If you are concerned about your service margin, move on to the next sheets to dive into more detail. In this example, there is a problem with the Microsoft O365 Services (Blue Box)
Customer Margin Report Analysis
In this report, it is important to check that each customer margin and the Service Invoice Reconciliation report create a list of concerns to check in the next two reports. This is because looking at the aggregated or total values can mask important details underneath. Please check the customers with ALERTS and Checks.
In Addition, in this report and the following reports, there are up to 6 configuration values to consider -
- Currency Code - Use this filter to look at one currency at a time if you invoice in multiple currencies
- Total Cost - Use this filter to remove zero cost items
- Quantity - Use this filter to remove zero quality items
- Low Margin Check - Use control which values are marked with the "check" flag
- High Margin Check - Use control which values are marked with the "check" flag
- Zero or negative Margin Alert - Use control which values are marked with the "ALERT" flag
We can see that in this demo data, several customers appear to have a negative margin, and three have the high margin check flag. Please note the Avancerys customer that appears in this sheet has a healthy margin.
Customer Services Margin Report
This report, along with the next report, allows you to drill into the details and using the list of services and customers of concern from the first two reports, you can focus on the areas that need attention.
Assuming that your cost prices are reconciling with your supplier invoice, the purpose of this report's review is to identify any issues with sales pricing either at a service or customer-specific pricing level. Review for ALERT and Check flags and identify any areas of further investigation.
As with any pivot table, clicking on a line item will open the underlying data for further investigation or review. Looking at the Avancery customer, we can now see that the overall margin across all services seems OK at 62.60%, but there is an issue with the margin on Microsoft O365 CSP Direct.
Customer Product Margin
The final level of detail is on this report is dropping down from service to product level. This allows you to solve a sales price issue by identifying the product price to be reviewed and potentially adjusted.
Billing Export Data
We have included this view, so those customers are not using the billing API to collect billing data from Cloudmore. It can also be used to check the data being passed to your ERP systems using the API.
Using the routine outlined in this document, you will have added more checks and balances to check your service cost prices and margin on a per customer or per-service basis. The detail will also give you the tools and access to the data to troubleshoot your pricing configuration in Cloudmore. Therefore, this routine should be run on your billing date every month.