As a best practice, we advise Cloudmore brokers to complete a monthly billing check to verify the billing items and subscription details before sending the monthly invoice to your customers. The Monthly Billing Checks helps you:
- To review and verify your margin at a customer and service/product level
- 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.
This guide will walk you through how to:
- Export the billing data
- Import the data into our Monthly Billing Check Tool
- Review and read the results
Step One – Exporting your data from Cloudmore
- Log into the Cloudmore platform.
- On the left navigation menu, click Billing and select General Billing Report.
- For the Billing Date field, select the the time period for which you want to review the billing data
- For the Services field, select All Services to view data for all the services you manage.
- Continue with the default values for the following fields: Organization Groups, Base report on, and Convert Currencies.
- Under Actions, select Send export once.
- Type your email address in the Send email to field.
- Click Send billing report to email.
You'll receive an email billing report in the excel format. 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 noreply@cloudmore.com. - Download the file. It will download to your download folder or other designated downloads folder and then open in Excel.
Export the billing data to the Monthly Billing Check Tool
- Download the tool in excel format by clicking here: Monthly Billing Check Template.
- Open the Billing Report you downloaded during Step 9 from the previous section.
- Press Ctrl A + Ctrl C to copy all the data from the report.
- Now open the Monthly Billing Check template and paste (Ctrl V) the data to the Billing Report from Cloudmore tab.
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.
Billing Troubleshooting
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.