Budgeting of international operations must address foreign exchange (FX) rates. Variance analysis also must address FX variances. Read the FM magazine article at

1. How does this change the analysis of international operations?
2. Do you have a different suggestion to address FX variances?

For any well-managed company, a comprehensive analysis and profound understanding of its sales and price variance over time are critical. Driven by multidimensional factors, these variances repeatedly perplex management teams and can derail the overall business performance.

A bottom-up sales and price analysis I developed for a large global company to help the leadership gain valuable insights and facilitate the decision-making process is discussed step by step in this article. The analysis can be integrated into business intelligence reporting tools to render accurate and effective insights.

Key management concerns

The sales and price analysis is designed to address the most important management concerns:

• What's the root cause of the sales increase or decrease between two periods?
• Which factors primarily drive the sales up or down? Is the uptrend or downtrend going to persist?
• What's the indication under the surface of the average price change between the two periods?
• To what extent does a shift in customer mix affect the average price change?
• How do you effectively adjust thecustomer-specificpricing to optimise the gain or to minimise the loss?

Unlike the traditional top-down analysis model, the sales and price analysis employs a bottom-up approach to address the above concerns by first slicing the sales and price data into layers and then drilling down to identify other drivers of the variances at a granular (bottom) level. These specifics allow management to identify where the significant impacts occur and make optimal decisions. If you are building the sales and price analysis model for a multinational company, it would make sense to apply the standard approach across the board after grouping the sales by region.

The components of the sales and price analysis

The sales and price analysis incorporates six vital components that can be analysed and calculated at the individual-customer level for a company with numerous customers embracing different pricing structures. As shown in the graphic "Sales and Price Analysis Components", the six components form a hierarchy that includes sales variance, average price variance, volume variance, customer mix, foreign currency exchange rate changes, and price increases/decreases.

Sales and price analysis components

Let's start with the sales variance and then dive into the price variance. The sales variance is simply the difference in the values between the two periods, which comes from the impact of volume and average price variances.

The volume impact is relatively intuitive for most analysts. However, explaining the change in average price between the two periods can be an intricate job, especially when changes from dynamic aspects of customer mix shift, foreign currency exchange rates, and price increases/decreases are mixed together. More on this later.

Sales variance analysis

As illustrated, the sales and price analysis breaks sales variance into two parts: volume variance and average price variance. To quantify the volume variance impact, we use the Q1 price to multiply the volume difference between Q2 and Q1. The purpose is to isolate the volume change impact to the sales from the average price change impact. This calculation allows us to explain how significant the sales changes are that have originated from the volume increases or decreases. A similar idea is used to quantify the average price variance impact.

Let's use a numerical example to demonstrate how this works. (See the graphic "Sales Details for Q1 and Q2".) Our fictitious company, Taylor Brothers, sells one product to five customers under different contracts at various prices. Its customers, at a range of volume-based discount rates that result in price fluctuations, purchase different quantities of the products in Q1 and Q2:

Sales variance = Q2 sales Q1 sales =

$1,027,750 $1,370,000 = $342,250

Sales details for Q1 and Q2

Sales decreased by $342,250 in Q2 compared with Q1. As we just discussed, the total sales variance can be better understood after calculating the average price variance impact and the volume variance impact. Here is how we codify:

Total sales variance = Volume variance impact + Average price variance impact = Q1 average price (Q2 total volume Q1 total volume) + (Q2 average price Q1 average price) Q2 volume

In our example, volume variance impact is ($1,370,0004,400)(3,225 4,400) = $365,852, which indicates the volume decreases had an adverse impact on sales in the amount of $365,852. Meanwhile, average price variance impact is ([$1,027,7503,225] [$1,370,0004,400])3,225 = $23,602, which indicates the increased average price had a positive impact on sales in the amount of $23,602. In such a way, we simply identify how changes in volume and average price contribute to the sales variance. Apparently, the volume decrease has a greater effect leading the total sales down in Q2.

Average price variance analysis

The above average price variance impact doesn't help management understand the root causes that explain the variance at a more granular level. The exact impacts from three components customer mix shift, foreign currency exchange rate, and price changes need to be thoroughly examined one by one at a specific level. (In contrast to reconciling amounts produced by the top-down approach to fill gaps in the equation, the calculated impacts of these three components play important roles in explaining the underlying root causes.)

For the same company, let's continue our average price variance analysis by addressing the impact from customer mix shift:

Section 1: Customer mix shift impact

To offer the flexibility to quantify the magnitude from an individual customer, the sales and price analysis calculates the customer mix impact at the customer level. The sales and price analysis calculates the difference in volume for each customer from the Q2 volume and the volume as if each customer would purchase at the Q1 mix in Q2. And then we take the difference of the customer's Q1 price and Q1 average price to multiply the calculated difference in volume:

Customer mix impact =

(Customer's Q1 price Q1 average price)(Customer's Q2 volume Customer's Q2 volume at Q1 mix)

CustomerChas the most adverse impact from customer mix shift. (See the graphic "Customer Mix Impact Between Q1 and Q2".) With this information, the sales team should immediately start investigations to initiate an action plan and gain customerC's business back. This differentiates the sales and price analysis from the traditional top-down approach analysis by showing the impact of each customer. That helps management pinpoint the issues and engage the sales team to follow up with specific customers.

Customer mix impact between Q1 and Q2

Section 2: FX rate impact

Now, let's take a look at the second component: foreign currency exchange rate impact. Our example illustrates a US-based multinational with transactions in euros; therefore, it is required to convert to US dollars for reporting purposes. Embedded in the average price variance, the impact of the foreign currency exchange rate must be quantified and isolated from the price change effect. Again, by performing the sales and price analysis at the customer level, we are able to explain the foreign currency exchange rate impact on sales for each customer. In our example, one single foreign currency is used for simplification. The same approach can be applied to transactions with multiple currencies:

Foreign currency exchange rate impact =

(Customer's Q2 price in USD [Customer's Q2 price in USDQ2 FX rate Q1 FX rate])Q2 volume

Our analysis shows that our company benefited from the sales transactions in euros due to a weaker dollar (the FX rates are more favourable in Q2 than in Q1). (See the graphic "FX Rate Impact Between Q1 and Q2".)

FX rate impact between Q1 and Q2

Section 3: Price change impact

Let's proceed to calculate the price change impact by using the difference of the converted Q2 price at the Q1 FX rate and Q1 price to multiply Q2 volume. The formula is as follows:

Price change impact =

([Q2 priceQ2 FX rateQ1 FX rate] Q1 price)Q2 volume

This analysis uncovered the real price change without other interference. In our example, customerEhas an adverse price impact with a relatively higher dollar-denominated price in Q2. (See the graphic "Price Change Impact Between Q1 and Q2".) Without this analysis, management could miss the falling price, which is blended with and covered by the positive FX effect. This information helps management optimise pricing decisions.

Price change impact between Q1 and Q2

Two-dimensional analysis

At this point, we have discussed the sales and price analysis model and how to calculate all three bottom-level components that serve as drivers of the average price variance at the customer level. Putting all results together, as you can see in the graphic "Bottom-Level Overall Impact", we focus on impacts from both the individual customers and the underlying drivers.

The sales and price analysis establishes a robust two-dimensional model to explain the variance thoroughly.

Bottom-level overall impact

Step by step vertically, we broke down the average price impact ($23,602) by customers to show each customer's contribution to the sales variance caused by average price changes. Management can comfortably pinpoint that customerChas a negative impact.

Horizontally, the analysis provides the insights into each driver (ie, customer mix, FX, and price change) and drills down to individual customers. Information extracted from both dimensions can greatly help management understand that customerC's negative impact was primarily driven by customer mix shift.

To integrate this into your business intelligence report, you can add a waterfall analysis to help management visualise the average price change and its underlying drivers. For management at a company with hundreds and thousands of customers wanting to know what happened to average price, you can convert the results to unit price level by dividing the component impacts by the Q2 volume in the graphic "Price Change Impact between Q1 and Q2":

Customer mix impact per unit = Customer mix impactQ2 volume

FX impact per unit = FX impactQ2 volume

Price change impact per unit = Price change impactQ2 volume

The impact on the average price is driven by the three components customer mix, FX, and price change. (See the graphic "Waterfall Analysis for Impact of Average Price".) Weighted in between the two periods' average prices, these drivers visually explain how significant they are. In our example, the favourable FX rates help offset the adverse impact from customer mix shift.

Waterfall analysis for impact of average price

To forecast next quarter, if the FX rates are not sustainable, management should not expect the increase in average price to continue. In this case, management needs to have an action plan to improve the customer mix.

Use modern visualization tools to make the sales and price analysis more impactful

Leadership should review the sales and price analysis at least every quarter to facilitate intelligent business decision and effective planning processes. To make this more interesting, the sales and price analysis can be integrated into other modern data visualization tools, such as Power BI and Tableau. Management can dynamically track the business performance using the sales and price analysis model by exploring the results in multidimensional and interactive charts on these platforms.