How Technology Can Help Improve Gross Margin Analysis
Most ERP systems don’t natively provide much information relative to Gross Margin. The typical information provided consists of invoiced line items by a product ID of some sort, units sold/shipped, and the amount invoiced. On the cost side, assuming this is a product-based business, there will be an entry for the related cost of goods expense, which is generally a function of unit costs used to value your total inventory asset. Consequently, you will need to engage some technology, either on your own or with the help of your IT Department, to extract the following information:
Invoice #
Customer
Ship Date
Part ID
Units Shipped
Total Line-Item $ Invoiced
Total Line-Item $ Cost
Whether your business is running SAP or QuickBooks, this is the essential data you need to develop insightful gross margin analysis. The second item is a plan that is structured consistently with your actual reporting systems, summarized by product ID. When analyzing Plan vs. Actual Gross Margin, most businesses don’t go to the customer level since most businesses don’t plan Revenue and Gross Profit at a customer level. If your customers are relatively sparse, then feel free. But for most businesses, summarizing revenue, cost, and units at a product ID level will suffice.
In reality, assuming you have actual data summarized as described above, you can generate very meaningful gross margin analysis with Excel. You will just need a way to summarize the actual results at a product ID level. Here are some ideas for summarizing actual results, in increasing levels of complexity:
Utilizing SUMIF or SUMIFS: These two functions give you the ability to SUM based on either a single or multiple criteria. This would allow you to SUM your actuals based on Product ID and perhaps a month-end date using SUMIFS.
Power Pivot: If you add your actual results data to an Excel table, then you can create Power Pivot summaries based on Product ID and month. You’ll need to filter by Revenue, Cost, and Units to summarize by those dimensions.
Power Query M Formula Language: This is the most complex method, but it is also the most robust method. Typically starting with Excel tables, you can write custom queries to summarize data in any fashion desired. You can even generate gross margin analysis at a customer level, comparing, for example, this month’s shipments vs. last month’s shipments for all your major customers. But it is a bit complex.
With the goal being to parse gross margin/profit variances by Volume, Price, Cost, and Mix. Again, this can span from simple bridge charts all the way to utilizing Power BI to enable users to completely slice and dice gross margin results on whatever dimensions you decide to incorporate in your models.
Contact us if you’d like to learn more or check out this Gross Margin analysis primer on our website.
Contact me today to start transforming your financial performance with more robust analytics and reporting tools to set your business on the path to success.
Feel free to reach out with questions or if you need assistance! If you’d like to improve your gross margin analysis capabilities, click this link.
