# Price Volume Mix Analysis: how to do it in Excel and Power BI

A case of miscoding is illustrated by a contractor’s claim for \$200,000 for owner-caused productivity impact on installing large bore pipe. The contractor’s original bid estimate and control budget for the work was \$500,000. The contractor’s bid estimate and control budget for placing the pipe hangers was \$100,000. The actual cost report showed no cost for the pipe hangers. Review of the project history through https://business-accounting.net/ document analysis and interviews revealed that the foreman responsible for the pipe installation was also responsible for the pipe hangers. The foreman had not separated the pipe hangers effort but had recorded all efforts to the piping installation task. The result was the contractor’s claim was reduced to \$100,000 (\$200,000 piping installation overrun less \$100,000 adjustment for the pipe hangers).

Do you think standard cost variance analysis would be useful in a system designed around the concepts of just-in-time and the theory of constraints? Why can’t we calculate a variable overhead efficiency variance in normal historical costing? How does the standard cost method of recording and evaluating direct labor differ from the methods for direct material? In defense of standard costing, one can argue that it provides a powerful planning device and macro performance monitoring system that allows middle and upper level managers to see the big picture on a periodic basis. From this defense perspective, it is just a matter of developing a balanced system that does not overemphasize any particular aspect of performance. (See MAAW’s Balanced Scorecard topic for more information). Expando Company’s factory overhead costs and the resulting variances are recorded in T-account form in Exhibit 10-19.

## Revenue Variance Analysis

Referring back to the Expando Company budget in Chapter 9, recall that the planned production volume variance for March was \$19,000 unfavorable. Since the actual variance for March is \$40,000 unfavorable, the unplanned variance is \$21,000 unfavorable. Although the production volume variance is referred to as uncontrollable, a large unplanned variance may need to be investigated and explained. The unplanned variance above could have been caused by a decrease in the demand for the Company’s product, or by various production problems. The calculations are presented in Exhibit 10-18A as a more revealing alternative to the analysis in Exhibit 10-18. In attempting to achieve favorable price variances, purchasing agents may purchase larger quantities of materials than needed to obtain quantity discounts.

• Suggests that reduced planning variances yield a higher quality plan and a more harmonious operation.
• If actual hours worked are less than the standard hours, the variance is favourable and when actual hours are more than the standard hours, the variance is unfavourable.
• More specifically, actual net income before taxes was \$170,000 higher than budgeted and, after subtracting the variance for fixed costs, the total variance in contribution margin was \$171,600 higher than budgeted.
• This post concludes this series on the alternative parallel inventory valuation approach.
• If the cost of producing a unit of the final product is \$10 (5 widgets @ \$2 each), the budgeted cost in producing 5,000 units is \$50,000.

Instead of using revenue, you can use your contribution margins or your gross profit which will make the story even more powerful. Particularly using the profit makes this analysis 10 times or 20 times more valuable to your insight. Cost accounting is a form of managerial accounting that aims to capture a company’s total cost of production by assessing its variable and fixed costs.

## Formulas to Calculate Overhead Variances

A diagram approach may also be used for fixed overhead variance analysis, although a flexible budget is not involved. The following symbols are used to illustrate how direct labor costs are recorded and analyzed in standard costing. A conceptual view of direct material cost drivers is presented in Exhibit 10-9. The illustration shows that materials costs are driven by prices and quantities, which in turn are driven by many other factors. Of course random variations in these factors are likely to cause a large percentage of the price fluctuations.

In addition to the above considerations, which only mention costs, it is usually advantageous to provide for quantities of work, labor hours, and equipment types and hours. The sales volume variance, therefore, is unfavorable overall because the sales mix variance is significant. It helps businesses identify which products are performing better in the market.

The variance is unfavorable, as in this case, if standard fixed overhead costs are less than budgeted. Actual production was 800 hours below the average monthly denominator level.

The efficiency variance is essentially the difference between two point estimates on a regression line (see Figure 10-4). Sometimes these estimates are overstated and sometimes they are understated. This under, or overstatement might easily be misinterpreted, although it is not interpretable at all. Thus, the main point of this discussion is that the traditional analysis can only provide a rough estimate of the nature of the total variance for a particular type of variable overhead.

## What are the causes of an overhead variance?

It also includes the effects of sales mix differences. It is favorable if the actual units sold are greater than budgeted unit sales. Budgeted contribution margin per unit is used in the calculation to isolate the sales volume effects, i.e., to keep the price and cost effects out of the calculation. Budget or spending variance is the difference between the budget and the actual cost for the actual hours of operation. This variance can be compared to the price and quantity variance developed for direct materials and direct labor. No headers Factory overhead costs are also analyzed for variances from standards, but the process is a bit different than for direct materials or direct labor. The first step is to break out factory overhead costs into their fixed and variable components, as shown in the following factory overhead cost budget.

In Method 2, the price variance is only calculated for the material used. However, obtaining the best price for materials is a purchasing function, not a responsibility of the production manager. Therefore, it is logical to calculate the price variance on the basis of the entire quantity purchased.

## Price Volume Mix Analysis in Excel and Power BI

Calculate the following variances and note the status of each variance. Record the following transactions using general journal entries including the appropriate variances. Net cash flow can be calculated by adjusting the projected net income from a project for any non-cash revenues and expenses. An opportunity cost is the potential benefit lost by taking a specific action when two or more alternative choices are available. Return on investment is a useful measure to evaluate the performance of a cost center manager.

If actual hours worked are less than the standard hours, the variance is favourable and when actual hours are more than the standard hours, the variance is unfavourable. Labour rate variance is computed in the same manner as materials price variance. When actual direct labour hour rates differ from standard rates, the result is a labour rate variance.

## Visualizing Price Volume Mix data in Excel

As a fifth step, each activity is assessed by time frame for the impact and cost overrun caused by each specific claim problem. Cost overruns outside the time frame in which the problem or its effect occurred are excluded from the compensable cost distribution and, therefore, are noncompensable. As a fourth step, each activity and its cost elements are evaluated for noncompensable cost items. The actual cost for each activity is reduced by such noncompensable costs to derive an adjusted value.

Sales variances computed under these two methods show different amounts of variance. a cost variance can be further separated into the quantity variance and the price variance. It is not possible to calculate this variance without additional facts.