course bg
EduPristine>Blog>How to optimize Financial models with the help of SUMIF

How to optimize Financial models with the help of SUMIF

May 9, 2014

Overview of the scenario

I was doing evaluation of Kings Digital IPO as part of my summer internship programme. While going through the S-1 form I found the balance sheet to be given in yearly basis. Also, when I went through the models of other IPOs ,I found that the general trend is to show Profit and Loss statements and Balance sheet in yearly basis while the Revenue build up and Cost build up are done on a quarterly basis. It makes more sense to forecast revenue growth drivers and cost drivers on a quarterly basis. I faced a dilemma how to bring the quarterly numbers on a yearly basis.

After some introspection I realized that the situation called for a conditional summing. I searched for the relevant function. SUMIF is the function which helps in conditional summing

Syntax

SUMIF (range, criteria,[sum range])

Parameters or Arguments

Range is the range of cells that you want to apply the criteria against.

Criteria are used to determine which cells to add.

Sum_range is optional. It is the cells to sum. If this parameter is omitted, the SUMIF function uses range as the sum_range.

Mapping Quarterly projections into yearly projections

The SUMIF formula sums a range of values that meet a certain criteria. I had initially taken the quarter endings as the time periods. To use the SUMIF effectively I needed matching criteria. I used the Year function to retrieve year from the quarterly periods. I then used Year as matching criteria and I used a SUMIF on the revenue.

Snapshot of getting yearly revenue from quarterly revenue in Revenue build-up sheet

sumif

As is evident from the image above I retrieved year from the quarterly periods. I then used the SUMIF on the quarterly revenue calculations to find yearly revenue

The YEAR function is used for retrieving the year from any date format

The SUMIF function used for calculating yearly revenue from quarterly revenue is

SUMIF (D4:AE4, D17, D14:AE14)

For year 2012 revenue =SUMIF (D4:AE4, D17, D14:AE14)

Similarly for 2013 revenue= SUMIF (E4:AF4, E17, E14:AF14)

In the columns D4 to AE4 the year of the corresponding quarter is mentioned. This is matched with the column where we have hardcoded year as in D17=2012, E17=2013.Upon matching the SUMIF functions add all rows corresponding to matching columns. The explanation will be more clear once you open the attached excel sheet

I have also used a similar approach for cost build up as cost build up is also done on a quarterly basis while the costs appear in Profit and Loss as yearly costs

Snapshot of getting yearly projections from quarterly projections for cost build-up

sumif

As is evident from the image above, I retrieved year from the quarterly periods. I then used the SUMIF on the quarterly cost calculations to find yearly cost

The YEAR function is used for retrieving the year from any date format

The SUMIF function used for calculating yearly costs from quarterly costs is

SUMIF (D4:AE4, D17, D5:AE5)

For year 2012 cost of revenues = SUMIF (D4:AE4, D17, D5:AE5)

Similarly for 2013 cost of revenues =SUMIF (E4:AF4, E17, E5:AF5)

For year 2012 Sales and marketing=SUMIF (D4:AE4, D17, D9:AE9)

Similarly for 2013 cost of revenues =SUMIF (E4:AF4, E17, E9:AF9)

In the columns D4 to AE4 the year of the corresponding quarter is mentioned. This is matched with the column where we have hardcoded year as in D17=2012, E17=2013.Upon matching the SUMIF functions add all rows corresponding to matching columns. The explanation will be more clear once you open the attached excel sheet

SUMIF Explanation

About Author

avatar EduPristine

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.

Comments

Interested in this topic?

Our counsellors will get in touch with you with more information about this topic.

* Mandatory Field

`````````````````````````````````````````````````` Post ID = 53653