This is a follow-up to an earlier video on
practical uses to SUMPRODUCT in financial modelling.
This sixth example will aim to compute the total
and included EBITDA of the above data set.
First, we have to reference the Revenue data
range in column G.
Then the EBITDA % in column I.
And then the Include ? array in column M.
The total should be 27.
Here is the formula used.
For the seventh example, we will apply the
SUMPRODUCT to arrive at the Net Income
margin (% Revenue) for Beta over month 2 and 3.
First, we have to reference
the business unit array in column B.
Then the Month range in column F,
which we will need to reference twice in this
formula, given we want to capture months 2 and 3.
This formula is a little more challenging,
because we need to reference the Net
Income array in column J.
Then divide it by the Revenue range in column G,
and then by the number of months that we are
referencing, which is two months in this example.
You will return an amount of 10.59%; if you
correctly applied the SUMPRODUCT formula.
Once more, here is the formula to calculate
the Net Income margin for Beta
over month 2 and 3.
The eighth SUMPRODUCT example will derive
the Gross Margin for Echo over Month 1 and 3.
The first range referenced is
Business Units in column B.
The next array needed in the formula
is Month in column F.
Given we want to capture both Month 1 and 3,
we need to reference this range twice.
The third array in the SUMPRODUCT formula
is column G - Revenue, which the fourth array
(Cost of Goods Sold) is subtracted from,
in order to return 60.
In summary, here is the formula to compute
Gross Margin for Echo in Months 1 and 3.
The next example will seek to find the
aggregate EBITDA across Beta, Charlie
and Echo in Month 1.
First we reference column F or Month.
Then the Business Unit field in column B.
As we want to aggregate the EBITDA of three
Business Units, we must reference
the column B array three times.
Next, we reference the Revenue data in
column G, which we multiple by the
array in column I - the EBITDA % data set.
If the formula is correctly applied,
then you will return a value of 28.9.
The final SUMPRODUCT example will return
the total FTEs or full-time employees across
the business units of Alpha,
Beta and Delta for month 4.
First reference the Month array or column F.
Next, you need to reference the Business Unit
data, which we will need to reference three
times, because we want to capture the FTEs
across three Business Units.
Now we reference the Revenue array in
column G, which we will divide by the Revenue
per Full-Time range in column K.
You should return a value of 431.71 for total
FTEs across the three Business Units in month 4.
Feel free to comment, subscribe to our channel
or like this video.