Hi Excel gurus - I'm hoping to get some help from any smart brains out there on a modeling issue I'm facing.
I'm building a cash flow model that needs to plug into IS and BS later. For the cash flow, what's happening is that there are clients who renew yearly, but typically pay AFTER their new annual contract begins. So if their contract date is February 1, 2014, they may not pay until March, or even April. I have tried to model this out where the payment delay is an input. However, in my current model, I can't have December 2014 flow into January/February/etc. of 2015. For instance, a contract start date of December 1, 2014 that I expect to receive cash from in January or February 2015.
Please find a snippet of my existing model here on Google Drive. It is also attached.
After this logic issue is resolved, I was wondering if anyone knows of a good way to use this cash flow, in conjunction with the contract dates, to figure out how to build in A/R, Income, and Deferred Revenue. Income I figure will be the contract value for each year/12 months, but because there are prices increases annually to the contracts, I'm trying to figure out how to recognize that if this model date is June 30, 2014. For instance, a customer who renews in March 2014 would have paid January-March in 2013's contract price, and will pay 2014's contract price from March onward.
Deferred revenue is an issue as all the cash is collected upfront, but money is not necessarily received. I'm trying to figure out a way to have it perpetually automated in the model as the months "pass".
Any insight is greatly appreciated! I've been struggling with this for days now!
Bookmarks