Perform Simple Mathematical Calculations: Weighted Averages (using SUMPRODUCT), Percentage Change and Forecasting using Growth Rates

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

In the Topic Example workbook, we have a list of sales with product, quantity sold and the price (looked up against a price table using INDEX/MATCH). We want to work out the total Revenue (Sum of Price * Quantity Sold for each sale), the weighted Average Price (Sum Of Revenue/Sum of Quantity Sold) and the total sales attributable to Andrew.

One way of doing this would be to add another column that calculates revenue by multiplying the quantity sold by the price for each sale, and then sum the revenue figures to give us a total. We could then perform a SUMIF analysis on the Revenue to find that attributable to Andrew. Alternatively, we could use a single function – SUMPRODUCT – to achieve the same thing.

This topic looks at using SUMPRODUCT to calculate weighted averages, as well as other formulas – how to grow a number by a certain percentage p.a. over a number of years, and how to calculate percentage change between values.

The SUMPRODUCT function takes two or more ranges of cells (e.g. quantity and prices), multiplies the corresponding values individually and then sums the total. The syntax of the SUMPRODUCT function is = SUMPRODUCT (Array1,Array2,Array3….) – with an array being a range of cells (e.g. “$D$2:$D$9”). Our quantity range is $D$2:$D$9 and our price range is $E$2:$E$9 so our SUMPRODUCT formula for total revenue becomes =SUMPRODUCT($D$2:$D$9, $E$2:$E$9) which returns £1,619,750. In the Topic Example Workbook we have also created a Revenue column and the total of this column does equal that produced by our SUMPRODUCT formula.

Note that in the SUMPRODUCT formula the size of the ranges have to be exactly the same (in this case both are from row 2 to row 9 – i.e. 8 rows) or the formula will evaluate to an error.

We can then use this to calculate weighted averages. For example to calculate the weighted average product price (weighted by quantity sold), we would need to take total revenue (i.e. the Sum of Quantity * price for each sale), divided by Total Sales (3700) – as a single SUMPRODUCT formula this could be written as =SUMPRODUCT($D$2:$D$9,$E$2:$E$9)/SUM($D$2:$D$9). Using a Named Range to refer to our Quantity/Price Ranges instead, this becomes even easier to read – written as =SUMPRODUCT(Quantity, Price)/SUM(Quantity).

To really see the power of SUMPRODUCT we can introduce conditional elements into it – for example using SUMPRODUCT to calculate Revenue but only where the Salesperson is equal to Andrew. To turn an element (one of the array arguments) in a SUMPRODUCT formula into a conditional one we insert a double dash — before it , put it as equal to our condition and put the entire array element in brackets. So our Array of salespeople is $A$2:$A$9. To put this as a conditional array element to be equal to “Andrew” we write it as –(A2:A9=”Andrew”). So our SUMPRODUCT formula that calculates total revenue attributable to Andrew is =SUMPRODUCT(- -(A2:A9=”Andrew”), $D$2:$D$9, $E$2:$E$9).

Percentage Growth between Value1 and Value2 is calculated as (Value2-Value1)/Value1, i.e. the difference between the second value and the first with that result expressed as a proportion of the first value.

In the ‘Percentage Growth’ tab of the Topic Example Workbook, we have a list of CD sales over a number of years and we want to work out the percentage growth rate in each year. So in Year 1 the percentage growth (From year 0) is calculated as (110-100)/100 = 10/100 = 10%. Written as a formula this becomes = (B3-B2)/B2.

Once we have typed this formula and dragged it down, we have to ensure that all our numbers are formatted as percentages – to do this we highlight our range of cells, right click, select Format Cells and select Category ‘Percentage’.

One thing to bear in mind is that for the initial year (cell C2 in this case), the percentage growth rate formula would give a #VALUE error because it would refer to the column heading in cell B1. Therefore we either need to have a separate formula for Year 0 (which isn’t very good practice!) or we use an IF formula to say in the event of it being Year 0, return 0% else return the percentage growth rate. Our formula in cell C2 then becomes =IF(A2=0,0,(B2-B1)/B1) which we can drag down across the range C2:C9.

Business/Financial modelling is one of the key applications of Excel and one of the main elements of many financial models is the functionality of growing a set of numbers by a growth or inflation rate each year.

In the ‘Forecasting Using Growth Rates’ tab of the Topic Example Workbook, we want to see what our forecast CD sales is each year based on an assumption of 3% growth p.a. To grow a number by 3% we need to multiply it by 1.03. Our initial sales (in year 0) is 100, so for year 0 we want to multiply it by 1, in Year 1 we want to multiply our initial sales by 1.03, in Year 2 multiply it by 1.03*1.03 (i.e. 1.03^2), In year 3 multiply it by 1.03^3 and so on.

So our any given year our formula is 100*(1.03^Year). Replacing the 100 with our cell reference C11 or our [Named Range] Initial_sales and the 1.03 with 1+Growth_Rate our formula becomes =Initial_Sales*((1+Growth_Rate)^A2). Fortunately this works even for Year 0 as anything to the power of 0 is 1, so we end up multiplying our initial sales by 1.

Finally a point to note on this is that the growth rate we have used here is annual, and sometimes we need to convert an annual rate into for example a monthly or quarterly rate. Converting an annual into a quarterly rate isn’t a case of dividing it by 4 – instead we take our annual rate and put it to the power of [no of months in new rate]/[no of months in old rate], i.e. 4/12 which is also equal to ¼. We have to firstly put our growth rate into a multiplier format (1+growth rate) and then take 1 off the end to get to our revised rate. So our formula for converting our annual growth rate (in cell C13) into a quarterly one is =((1+C13)^(1/4))-1.

In the Topic Example Workbook, In the Weighted Averages’ worksheet:

• In cell C12, write a SUMPRODUCT formula to work out the Total Revenue sold (sum of Price * Quantity sold for each individual item).
• In cell C14, calculate the weighted average Price (Total Revenue divided by Sum of Quantity sold) using a SUMPRODUCT formula
• In cell C16, write a conditional SUMPRODUCT formula to calculate the Total Revenue for sales sold by Andrew.

In the Percentage Growth’ worksheet, work out the percentage growth in sales for each year, with a formula that returns 0% in Year 1.

In the Forecasting Using Growth Rates’ sheet, use the initial sales figure in cell C11 and the annual growth rate in cell C13 to write a formula in cells B2:B9 that shows what sales are forecast to be in each year. Also based on the annual growth rate, work out the corresponding quarterly growth rate in cell C15. Note that references to the initial sales and growth rate values will default to be as [Named Ranges] as these have already been set up in the workbook.

A demonstration is shown in the video clips below. Resist the temptation to view the answers file below until you have tried this!

In the Topic Example Workbook, In the Weighted Averages’ worksheet:

• In cell C12, write a SUMPRODUCT formula to work out the Total Revenue sold (sum of Price * Quantity sold for each individual item).
• In cell C14, calculate the weighted average Price (Total Revenue divided by Sum of Quantity sold) using a SUMPRODUCT formula
• In cell C16, write a conditional SUMPRODUCT formula to calculate the Total Revenue for sales sold by Andrew.

In the Percentage Growth’ worksheet, work out the percentage growth in sales for each year, with a formula that returns 0% in Year 1.

In the Forecasting Using Growth Rates’ sheet, use the initial sales figure in cell C11 and the annual growth rate in cell C13 to write a formula in cells B2:B9 that shows what sales are forecast to be in each year. Also based on the annual growth rate, work out the corresponding quarterly growth rate in cell C15. Note that references to the initial sales and growth rate values will default to be as [Named Ranges] as these have already been set up in the workbook.

A demonstration is shown in the video clips below. Resist the temptation to view the answers file below until you have tried this!

Weighted Averages using SUMPRODUCT

Calculating Percentage Change

Calculating Growth Rates

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

YouTube Video: Excel Magic Trick 777: SUMPRODUCT Function — Basics To Advanced (14 Examples)