Matrix – Spreadsheet Approach to Variance Analysis
The methodology outlined in the Finkler text is a perfectly acceptable way of performing variance-analysis. However, it does not lend itself easily to spreadsheets especially when a large number of analyses need to be performed. What follows is an alternative approach to doing variance analysis that is widely used by practitioners and designed for spreadsheet applications.
Let’s start with a problem. When the Albany police department prepared its latest fiscal-year budget, it expected to respond to 7,300 911 emergency calls during the year with each call requiring an average of 30 minutes of police time at an average cost of $35 per-hour. At the end of the year, there had been 8,500 911-calls requiring 6,375 total hours of policing time at a total cost of $255,000.
This is a classic variance-analysis problem. We want to find out how the actual expenses differed from what was budgeted, ask if the difference was Favorable or Unfavorable, and identify the drivers behind that variance. The first step is to identify Volume, Quantity and Cost (or Price in the case of a revenue variance) the three determinants of variable costs (or revenues). To do the variance analysis, we need to identify the Cost per unit of Quantity per unit of Volume. Let’s look at all three factors in general and see how they play out in our example.
Volume is a measure of the total demand for services. With few exceptions, it is a factor outside the control of the organization charged with meeting the demand for services. That is the case here where the measure of Volume is the number of 911-calls. Clearly, police departments do not and cannot control the number of 911 emergency calls they receive.
Quantity is measured as units of resource per unit of Volume. In this case, policing hours is the unit of Quantity. The police respond to each call and use some number of hours of service for each call. In this case, Quantity is the number of policing hours per 911 emergency call.
Cost measures the value of a unit of Quantity used to service a unit of Volume. In this case, it is the Cost per hour of police time.
To start the problem, we need to get all of the measures of budgeted and actual Volume, Quantity and Cost in the correct units. For Volume, that will be the number of 911-calls. For Quantity, that will be the number of policing hours per 911-call. For Cost, that will be the Cost per hour of policing time. When we are finished setting the problem up in this way, we will have the Cost per hour of policing time (the Quantity) per 911call (the Volume) for both budget and actual. That is exactly what we need to do the variance analysis.
Finding all three numbers is a relatively straightforward task for budgeted Volume, Quantity and Cost. Budgeted Volume equals 7,300 911-emergency-calls as stated in the problem. Budgeted Quantity equals 30 minutes / 60 minutes per hour or .5 policing-hours per 911-call. Notice, we converted minutes to hours to match the units of Cost. Budgeted Cost equals $35 per hour of police time as stated in the problem
To find the actual numbers for Volume, Quantity and Cost, we need to do a little work. Actual Volume equals 8,500 911 emergency calls as stated in the problem. That is simple.
Finding the actual Quantity requires us to divide the total hours the policing time used responding to 911 emergency calls by the number of calls to get the measure of resource use (Quantity) stated in policing-hours per 911-call. Actual Quantity equals 6,375 hours / 8,500 911 calls or .75 policing hours per 911-call
Finding the actual Cost per 911-call also requires a simple calculation. We need to divide the total cost ($255,000) by the total number of police hours spent responding to calls (6,375). Actual Cost per hour of policing time equals $255,000 of total cost / 6,375 total hours of policing time or $40 per hour of policing time.
Now, we are ready to start the variance analysis calculations. The first step is to calculate the total budgeted and actual expenses. That is done by multiplying Volume * Quantity * Cost and calculating the difference between budgeted total cost and actual total cost and determining if that difference is favorable or unfavorable. Remember, budgeted expense variances are Favorable if the total actual costs are less than the total budgeted costs and Unfavorable when total actual expenses are greater than total budgeted expenses.
Here is how those calculations would look on a spreadsheet. Total budgeted and actual Cost was calculated by multiplying Volume * Quantity * Cost in each column. We will use that calculation for all of the total-cost figures throughout the rest of the example. In this case, the expense variance was an Unfavorable ($127,000). I elected to calculate the variance by subtracting actual total cost from budgeted total cost which makes Unfavorable variances negative numbers. The cell next to the total variance calculation contains an Excel logical-test function that makes determining whether the variance is Favorable or Unfavorable automatic. That function will be used throughout the example.
If you want to see the actual calculations or the logical-test function, click on the spreadsheet above. Notice, the total actual expense number we calculated is equal to the amount ($255,000) we were given in the problem. Notice too that we calculated total variance before we started the flexible (or partial) variance calculations. We will use that number a bit later to prove that the all of our flexible-variance calculations are correct.
At this point, we can start the flexible-variance calculations. We will begin by setting up a five-column worksheet with the budgeted numbers in the extreme right column, labeled Budget (Column D), and the actual numbers in the second column, labeled Actual (Column A), and verify that total expenses and the total variance match what we calculated above.
Now, let’s calculate the portion of the variance due to higher than expected Volume. To do that, we will change the budgeted Volume to actual (in BOLD type below in Column C) and calculate the total expense that would have occurred if only the volume had differed from what we budgeted. Note, the order of the variance calculations is a matter of convention. The standard order of the calculations is to find the Volume variance first followed by the Quantity variance and then the Cost or Price variance. To find the Volume variance, we subtract the “more actual” total expenses in Column C from the budgeted Total Expenses in Column D. This gives us the portion of the total variance caused by the difference between budgeted and actual Volume. We now know that the portion of the total variance due to higher than expected Volume is an Unfavorable ($21,000).
Next, we’ll isolate the impact of the difference between budgeted and actual Quantity on total variance. We will do that by changing the budgeted Quantity to actual (in BOLD below in Column B) and calculating the total expense given actual Volume, actual Quantity and budgeted Cost. Notice, we left the Volume in Column B equal to actual. Once a factor has been changed from budget to actual, it remains “actual” for all of the remaining calculations. To find the portion of the variance due to Quantity, we subtract the total cost in the “more actual” Column B (Change in Quantity) from the “closer to budget” total cost in Column C. This tells us that the portion of the variance due to Quantity is an Unfavorable ($74,375).
All that’s left is to determine the portion of the variance due to the difference between the budgeted and actual Cost. At this point, all of the hard work has been done. The Actual column (A) already has Cost set equal to actual (in BOLD below in Column A). To find the variance, we subtract the actual total expenses in Column A from the “closer to budget” amount in Column B. That calculation shows that the portion of the total variance due to higher than expected per-hour policing Cost was ($31,875).
Now, let’s make sure we did everything correctly by adding up all of the flexible variances (due to Volume, Quantity and Cost) and checking to make sure that total equals the total variance we calculated at the beginning of our analysis. If those totals are equal, the odds are that we have calculated each of the flexible variances correctly. If the totals are not equal, check to make sure that you have done all of the subtractions consistently. In this case, that means making sure you always subtracted the left column from the one to its immediate right. Inconsistently calculating the flexible variances is the most common error in variance analysis.
At that point, we are done with the mechanical part of the variance analysis. We know the total variance ($127,250) and have partitioned that variance into the amounts due to Volume ($21,000), Quantity ($74,375) and Cost ($31,875). All that is left is to interpret the numbers and determine what management action, if any, is required.
5