Intercompany Business

Larger organizations are normally organized into multiple legal entities. These entities may be separated along product lines, countries, or other business attributes. Whenever this happens, you will have to deal with data crossing the boundaries between entities: the subsidiary in, say, Japan, may do business with Japanese customers, but may also provide products or services to the subsidiary in India. Invoices sent to India may be counted in sales numbers for Japan, but should be consolidated when reporting the worldwide sales numbers. That is, assuming that the India subsidiary booked the purchases from Japan correctly.

A similar challenge arises when business crosses other boundaries. For instance, a company that executes multi-year projects for customers, or sells multi-year support contracts, has to answer questions about what part of the total sales on each project or contract is counted as sales in a specific year. With this, you can provide more...

Modeling the QuantoBikes sales process

Many business systems record different stages in a business process. A typical enterprise resource planning (ERP) system allows us to define the stages, for an invoice to be sent, for example. It is important to note that recording these stages assumes that a business process works in that specific way. The reality is almost always different.

In fact, analyzing the difference between a designed process and the actual process is a field of its own, named process mining. When you consider a large number of invoices, you will commonly find many deviations from the process as it is designed. Optimizing the business process starts with knowing which variants of the process occur. Some odd variants may have a large impact on throughput or even profitability; eliminating these variants by specific corrective actions can significantly improve business process performance. Analysts who apply process mining can also apply machine learning to predict...

Business between subsidiaries

For the purpose of internal transactions, QuantoBikes subsidiaries are listed in the global supplier list and therefore appear in the Supplier table. A QMB Subsidiary column denotes these special suppliers with the value 1, whereas all normal suppliers have a value of 0 in this column:

Figure 2.5.3: QuantoBikes subsidiaries

Similarly, each subsidiary that sells to another subsidiary needs to add the subsidiary to its customer list. In the Customer table, an Internal column is used to distinguish between internal and external customers:

Figure 2.5.4: Internal customers have an Internal value of 1

We can now create DAX measures that do or do not include internal customers using the Internal column. In other words, we can either calculate a subsidiary view (including internal customers) or a consolidated view (excluding internal customers).

Subsidiary view versus consolidated view

When analyzing sales, it is important to...

Future sales

For our next analysis, let's turn back to the QuantoBikes sales process, focusing on two simple steps: booking a sales order, and sending an invoice based on the sales order.

You can calculate the actual sales (and purchases, for that matter) from the invoices in our model. A common question to ask is: how will sales develop through the rest of the year? One part of the answer to this question deals with sales forecasting and the sales pipeline. These are very useful things to analyze, although they come with inherent uncertainty.

A much more specific part of the answer comes from looking at sales orders that have not been (fully) invoiced yet. After all, a sales order is typically booked when the customer agrees to the sale, but the corresponding invoice is sometimes sent on a later date. So, what could be easier than determining what sales orders have not been invoiced yet, or at least, not completely?

Well, a lot of things are easier than that, but...

Summary

In this chapter, we discussed two main business challenges: intercompany business and consolidated views, and invoices to be sent on open sales orders.

In analyzing intercompany business, you have seen that thoroughly keeping track of context is the key to sophisticated results. In analyzing the balance between sales and purchases between two subsidiary companies, we needed to switch roles; the selling companies for one revenue stream were the purchasing companies for another revenue stream, and vice versa. DAX provides rich capabilities to perform the context transformations needed for these kinds of calculations.

In the intercompany business example, you have also seen that in some cases, DAX measures must be specifically tailored to the visualizations used to produce understandable results.

The second business challenge, specifically invoicing on long-term sales orders, provides a good example of how to approach this kind of advanced analysis: first, make sure...