This video will present some practical uses
of the SUMPRODUCT function for financial or
business modelling.
We have financial data for various
business units across a number of months.
Even with the advent of the SUMIF
and SUMIFS functions, the SUMPRODUCT
function is still a viable alternative to
aggregating and computing data
because it is incredibly flexible.
In this example, we will calculate Spot Gold
Revenue in euros based on price,
exchange rate and volume
In summary, here is the formula that
computes the €3.3 million revenue amount.
For the second illustration, SUMPRODUCT
will be used to return the
total EBITDA for the entire data set.
The two ranges of Revenue and EBITDA % will
effectively compute the total EBITDA amount.
Here is the formula for the
calculation of the total EBITDA figure.
The third example will
aggregate Net Income in month 2.
If the SUMPRODUCT function is applied
correctly, then the answer shall be 84.
To reiterate, here is the formula version of
the SUMPRODUCT used to compute
Net Income for month 2.
The fourth installation of SUMPRODUCT will
derive total Net Income
for the Charlie business unit in month 1 and 4.
The double hyphen in front of the first array,
which in this case is the business unit column,
is pivotal to the successful application
of the SUMPRODUCT function in this instance.
The second array references the
month range of column F.
Given we need to reference two values
in the month range - months 1 and 4, we need
to reference the month column a second time,
as we are doing here.
Finally, we need to reference column J or the
Net Income array of data, in order to return the
correct amount of 22 for Charlie in months 1 and 4.
To confirm, here is the formula again.
The fifth illustration of SUMPRODUCT will seek
the total revenue for the business units, Alpha,
Delta and Echo for month 3.
Again remember to use the double hyphen
when referencing the first array;
the Month range in column F.
Similar to the previous formula in example 4,
we must reference the business unit range
or column B array more than once.
Given we want to sum the total revenue for
three business units, we have to insert the
business unit array three times,
as we are doing here.
Finally,
we reference the Revenue array in column G.
The correct amount will be 241.
To recap, here is the formula again.
We will continue this demonstration in a
follow-up video, where we will look into
further uses of SUMPRODUCT
in financial modelling.
Feel free to send us a comment, like us
or subscribe to our channel.