Quarterly to annual conversion of 3 statement model

I have a created 3 statement models in both the quarterly format and in the annual format. Obviously the quarterly format reflects the most current info. And I want to have the quarterly model roll right into an annual model. So I have the Q1 Q2 Q3 actual numbers inputted, and the Q4 numbers are forecasted. What should I do to convert those quarterly numbers into annual numbers (Q1A+Q2A+Q3A+Q4E = Annual) and still have the model balancing and flowing dynamically?

 

Thanks! I understand that the balance sheet numbers are a snapshot in time. But the income statement is not so it must be summed. The difference between the 4 quarters of the balance sheet items must be captured in the cash flow statement along with some of the income statement items subtracted or added back. So I sum the income statement numbers, and difference the balance sheet numbers for the 4 quarters capture those differences in the CF statement; put it all together the model does not balance. I am not getting the right cash balance to go back to the balance sheet.

 

Good - that’s a start. Are you sweeping your cash to pay down debt each quarter? Check NWC calc and minimum cash too. I’d build your debt schedule quarterly as well.

Ignore this is your model is set up differently - but if your Q3 is actual and balances (which is should). Then you just need to do the Q4 CF and cash sweep correctly for it to balance.

Third option assuming this is for an LBO. You can just make the transaction as of FYE 2019. Use your Q4 balance sheet to start and then just project annuals. You don’t really need a quarterly model unless the business is cyclical or you are building an operating model.

 

Yes i have a quarterly debt and interest schedule with a commercial paper revolver that maintains a minimum cash balance. I also have the same company modeled annually in almost an identical way. Both models balance and are stable. But I can not figure out how to combine them.

 

If you have a row with the year or whatever above the quarter, you can SUMIFS to get the cumulative sum of the year, half year, quarter, week, month, or whatever you want based on your column groupings. For BS, obviously its best to link to CFS which will drive your end of period values.

 

Yes that worked; summing the CF numbers. A little counter intuitive. In accounting we are trained to build the CF from the balance sheet (or directly from the accounts). But this worked. When I built the model I differenced out the the quarterly CF numbers from the cumulative CF numbers presented in the 10Qs because I want the model to display what happened within the quarter. So to go to annual I guess summing the CF is really just sort of reversing that.

 
Most Helpful

Hi. Am facing the same problem with my model. Was wondering if you could please elaborate a bit more on how you managed to get the BS to balance. Here is where I am (and stuck).

  1. Have 10-Qs for 3 quarters along with 10-Ks for previous years.

  2. Have forecasted IS, CFS and BS for 4th quarter and BS balances

  3. Have converted the IS to an annual IS and it is fine.
  4. BS of 4th quarter end should be the BS for the annual BS too.
  5. Have tried a couple of things - 1) Aggregated CFS for all 4 quarters and tried to see if the cash balance is equal to the cash balance from CFS. It is not. 2) Tried to reverse engineer the annual cash flow from the annual IS and BS, That too isn't showing the right cash balance.

Where am I going wrong? I'd expect this to be a challenge that anybody would face when modeling in real life because you'd typically have something more than just the last 10-K.

Thanks for your help in advance. If you could share the model that you worked on and balanced, that would be much appreciated. (Fully understand if you can't - confidentiality and such).

 

Placeat ex exercitationem fugiat officia. Quia et autem doloremque ea. Neque similique id voluptatem id. Sunt cum adipisci eaque aut.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Betsy Massar's picture
Betsy Massar
99.0
3
BankonBanking's picture
BankonBanking
99.0
4
Secyh62's picture
Secyh62
99.0
5
dosk17's picture
dosk17
98.9
6
DrApeman's picture
DrApeman
98.9
7
GameTheory's picture
GameTheory
98.9
8
kanon's picture
kanon
98.9
9
CompBanker's picture
CompBanker
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”