While building out my hotel development model (currently underway), I decided to take a break and record a video about how I roll up the monthly cash flow line items on my Monthly Cash Flow sheet into annual cash flow line items on a separate sheet using only a single formula and simply copying and pasting that formula across the sheet. If done correctly, this formula can save you a tremendous amount of time.
More often than not, but especially for development models, we need to build out cash flow projections in monthly periods to allow for a more granular analysis and manipulation of cash flows over an investment hold period. However, it is also useful for both yourself and third parties looking at your model to be able to review the rolled up cash flow projections in annual periods as well in order to get a quick snapshot that is more easily digestible and takes up minimal space on a sheet.
Quick Formula for Rolling up Monthly Cash Flows into Annual Periods
There are many ways and formulas that can be used to go about rolling up monthly or even quarterly cash flows into annual periods, but I believe that the formula shared below in the video will be among the easiest and fastest methods out there. In the quick ~7 minute video below, I will walk you through the prerequisite setup needed in your model so that the formula can be implemented, we will then build out the formula, and then finally, copy and paste the formula in the relevant cells in the sheet.
The video recorded below is using my ‘currently underway’ hotel development model, but at the bottom of this post you can find an excel template that has been recreated from the model. You can download that file below by clicking on the link and use it to follow along with the video.
Video – Roll Up Your Monthly Cash Flow Line Items Into Annual Periods
Download The Model
To make this model accessible to everyone, it is offered on a "Pay What You're Able" basis with no minimum (enter $0 if you'd like) or maximum (your support helps keep the content coming - typical real estate models sell for $100 - $300 each). Just enter a price together with an email address to send the download link to, and then click 'Continue'. If you have any questions about our "Pay What You're Able" program or why we offer our models on this basis, please reach out to either Mike or Spencer.
Note (especially for Gmail users): The model is sent via email and occasionally is blocked by spam filters. If you don't see the email arrive within five minutes, check your spam folder.
This Excel file (the "software") is distributed as is, completely without warranty or service support. www.AdventuresinCRE.com and its authors are not liable for the condition or performance of the software. www.AdventuresinCRE.com owns the copyright and grants users a perpetual, irrevocable, worldwide, non-exclusive, royalty-free license with respect to the software as set forth below.
www.AdventuresinCRE.com and its authors hereby disclaim all implied warranties. www.AdventuresinCRE.com grants the users the right to modify, copy, and redistribute the software and documentation, both within the user's organization and externally, subject to the following restrictions:
1. The users agree not to charge others for use of the software, regardless of whether users have altered and/or improved the software in anyway.
2. In any use of the software, the users agree to acknowledge the www.AdventuresinCRE.com authors that developed the software.
3. The users agree to obey all government restrictions governing redistribution or export of the software.
4. The users agree to reproduce any copyright notice which appears on the software and documentation on any copy or modification of such made available to others.
About the Author: Michael Belasco has over nine years of real estate and construction experience. He currently works for a global real estate investment, development, and asset management firm in San Francisco managing large scale development projects in the city. Michael has both an MBA and Master in Real Estate with a concentration in Real Estate Finance from Cornell University.