Monthly 3-statement modeling?
I have a 3-statement modeling assessment coming up, and it sounds like it will be a monthly model with seasonality involved, and I'll need to come up with certain assumptions like revenue growth and WC (DSO, DIO, DPO etc.). I'm familiar with annual models from my work but this is my first time building one on a monthly basis.
Could anyone direct me to good resources to read on, or courses to take? So far, I was only able to find a short summary from asimplemodel on monthly 3-statement models.
Any tips or things to watch out on would be welcome as well - i.e. how to forecast revenues and WC etc.
Thanks.
You could use DSO/DPO to calculate change in NWC. Thus if you start with a WC balance you can determine the +/- on a forward looking basis. You can also peg WC needs to something that makes sense in that industry if you won’t have the data to do the bottoms up DPO/DSO approach.
Some other potential differences will be in your financing. Will need to identify when interest payments are due for CF purposes.
For monthly revenue forecast, if you can do YoY monthly growth. Basically you need a reason why revenue would grow vs the same month last year. If you don’t have that info available to you, make a general assessment for the business on an annual basis and review the resulting revenues of a flat growth assumption. Best to make sure you are not growing relative to prior month as you might in an annual model.
All of these factors depend on how bottom up these forecasts really are. Ie. If you are forecasting a 5% growth in expenses, it’s pretty reasonable to assume a higher revenue growth rate unless there is something preventing a pass through of the cost (ie. Contracts, regulation, extreme price pressure from competition, etc.)
Not the most comprehensive answer but it gets you started.
Thanks for the response - this is great.
Quick follow up question - let's say if cash goes negative during a month due to an external factor (i.e. unexpected seasonality etc.), is it realistic for the company to tap its LoC to cover that month only, or would they moreso cut costs in other areas temporarily?
Yes. Operationally, the cash manager/treasurer would borrow on the revolver/auto borrow facility would kick in. If I were you, have a min formula for cash balance at some reasonable level and have any excess need feed onto the revolver/AB. Cash balance is kind of arbitrary. Either min $0 or come up with some reasonable assumption. If you know cash needed for ops you can start there.
Appreciate the answer man! Very helpful
Voluptas ea voluptatibus sit repudiandae molestiae. Qui quo et consectetur libero praesentium et. Earum cupiditate veritatis sint. Et sit sunt unde. Repellat est sed dolore. At quia dolorem est aperiam repellat.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...