I have been trying to model a power plant and got a sample PP model. However, seeing the model, I am not able to understand the following:
In my excel:
A1= Cash Available for Working Capital (Linked from CF statement) A2=Working Capital Requirement (Assumed)
A3 = Beginning Balance (initially 0) A4= Additions (Releases) (MIN(A1, A2-A3))
A5= Ending Balance = SUM(A3:A4)
Can anyone explain the logic behind the calculation of A4?
The same is being done for debt service reserve. My logic says: any shortfall after WC n DSR should be taken from reserve/revolver. Why are we modelling each item separately?
A4 is just the movement of the account balance: if your WC account is below your target requirement it will be topped up from your CF (A1) to meet the difference between requirement and how much you already have funded in the account (A2-A3). If your account balance is actually higher than your requirement, you will have a negative number (A2-A3) and this represents a release from this account.
You are modelling each item separately because each one of them may be a required covenant in your financing documents and you also want to use it to build a detailed cash flow waterfall.
If you're still confused on the mechanics of the calculations, the following is a more detailed tutorial: http://www.navigatorpf.com/tutorials/dsra-debt-service-reserve-account
Here we go:
The formula in A4 says: Minimum ( Cash available for WC in A1, Required level of WC in A2 - Beginning balance in A3)
The model needs to maintain a certain level of WC to operate. Lets assume the following: Required level of WC is 500 USD
Current WC of the company (Beginning balance of WC) is 100 USD Therefore, you need 400 USD so that you meet the required level of WC of 500 USD.
There are 2 options to get 400 USD; either a financing from third party or from cash flow generated in the project which is availabe in cell A1)
Here, the model is using its own cash (in A1) to fund the 400 USD shortfall in WC.
Hope the logic is clear!
Let me know in case it is not , and we can discuss further!
Regards Amar
Doesn't project finance pay peanuts
Dolore voluptatum amet vitae consequatur nesciunt. Quis quis impedit enim officiis tempore iure. Aut distinctio numquam voluptates a nobis eaque distinctio et. Odio sed nemo voluptatum cumque laborum. Tenetur blanditiis architecto tempora ut quod. Quidem praesentium unde vitae ad.
Deserunt occaecati dolorem dolore a ut exercitationem. Reprehenderit enim architecto ut cumque ea. Tempore expedita cumque tempore et voluptatem inventore sit dolorem. Voluptas facilis aut enim aut.
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...