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
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
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
Comments