Chandoo.org is all about simplifying life using Excel. When I first started visiting the site, I was amazed at the amount of information on the site, which made your life easier. My next few posts would be about excel functions which can make your life for Financial Modeling easier! These are simple examples that you might find relevant in your daily life as well.
Let’s say your organization had 10 business segments, each segment selling different products at different prices. You have data available for the segments, how do you consolidate the revenues in one shot?
How do you model this in excel fast? In this tutorial we understand how you can use SumProduct() formula to do that!
What is the SumProduct() function
SumProduct() formula in excel, would take a couple of arrays (Say A and B) as inputs. Multiple corresponding items in each Array – A[1] x B[1], A[2] x B[2], … and sum them together.
SumProduct would get you the total revenue from a segmental breakup
The function is simple, it does all the difficult calculations for you and gets you the solution!
Traditional way of calculating consolidated Revenues
Step I: Getting the Segmental Revenues
Step II: Summing up the revenues
Consolidated Revenues using SumProduct()
Using the SumProduct() the consolidated revenues can be calculated in a single cell! It’s like the calculation is on fire!
Where else can this function be useful in Finance?
The function would see a lot of usage in the financial models. You can use it to consolidate revenues (Segmental volumes and prices to total revenues), calculate depreciations (when you have various assets and depreciation rates), consolidate costs (different raw material prices and volumes), NPV (Discount Rates and Cash flows), etc.
Are you also using SumProduct() function in Finance?
I have used SumProduct in my models. I find them to be very useful and speed up the modelling process. But sometimes I find that they confuse the readers of my model. Have you used the function in your models?
Templates to download
I have created a template for you, where the subheadings are given and you have use the functions to get the right values for you! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
Also you can download this filled template and check, if the information you recorded, matches mine or not!
Join our Financial Modeling Classes
We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.
Please click here to learn more about the program & sign-up.
The article is written by Paramdeep from Pristine.
Chandoo.org has partnered with Pristine to launch a Financial Modeling Course. For details click here.
9 Responses to “Sumproduct function to Consolidate Revenues?”
There are several functions and techniques that I might leave out of a model for fear of being overcomplicated for my users to grasp, but Sumproduct is not one of them. It's an easy enough concept to grasp that it can be explained in one sentence (in most contexts), as such, I use it where it makes sense.
My spreadsheets are nothing like the powerful and "sexy" things we see on this forum... simply because most of the people here struggle with =SUM(...)
That doesn't mean that I cannot use them whilst trying to educate the masses. However, there are 2 very useful functions that I have struggled to get others to use. INDEX and SUMPRODUCT. I guess everyone prefers the helper columns.
On SUMPRODUCT, I love the ability to make an:
OR statement (e.g. =Sumproduct(((array1=Criteria1)+(array2=Criteria2)),Sumarray)
AND statement (e.g. =Sumproduct(((array1=Criteria1)*(array2=Criteria2)),Sumarray)
(I hope syntax is right as my as my typing isn't great!)
Very often we tend to study, using the example shown above, the details of the information. For reporting and consolidation only purposes, sumproduct is invaluable. However, when it come to meetings/discussion most often then not we have to show and explain the how much revenue was generated by product X, Y and Z individually(% of total, trends, CY vs PY, CM vs PM, etc), showing the readers step by step how each number was derived.
And explaining something like sumproduct to skeptical managers or colleagues who don't know sumproduct and other less utilized function took more time than using the traditional way.
@Cameron: I agree. SumProduct is a really nice and useful formula.
@Stephen: Again a nice usage of SumProduct by using conditions. It would be great, if you can share an experience, where you used it in financial modeling (Context).
@Fred: Yes. If the management is looking for product wise analysis, then this function would hide the details.
Depending on how the data's categorized, I'll just used the Subtotal feature in excel for quick and dirty calcs.
Now if I'm creating a model that will be widely used on in a group, well that's another story.
[...] time we had discussed the use of SumProduct() to ease your life for calculation of consolidated revenues and depreciation. This time we would be [...]
Hi, Friend, I can't open the archive... Par-SumProduct.xls
Please help me !!
(My English is so bad, but I can read it well !! )
Saludos
Nora
Hi
Can anyone help me out if there is any formula to find out the cell combinations out of the sum. For eg. i hav a ready made total- say 132453.. bt in that column i need to permute nd combine various no.s to find 132453. Is there any such formula so as to find dese permuted & combined sum of cells directly..
Thankyou
@Article
Can you post a sample file?
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook