Collaborate on Progress Billing Template for Facilities with Ease Using airSlate SignNow

Progress billing template for Facilities

I'm making a little video about construction finance and financing of a construction company for a project finance of course it's not project finance at all it's a construction company so I'm going to work through of a little of the model and explain how it works and tell you about what the really key part of this is and the key part of this is to set up a nice little cash flow waterfall which is so common to so many other projects it's a little tiny bit different perhaps than other cash flow waterfalls because you have to see exactly when you're running out of cash when the cash balance runs out but I think it's a reasonable little exercise so that's why I decided to make a video somebody in my class said ok you know I have to do this so let me what I've done here is I've left most of the model intact but I've put some yellow little things so we'll show you how to work through an exercise on this okay and so here's a way we have some kind of project cost and my friend said I don't know what exactly how we're going to spend so I said here this is supposed to be medium and this is the standard deviation okay I said okay let's make a little s college you know and when you make an S curve we can make use a normal distribution this is just really say okay well it it's we got I should excuse me we have a date of course at the top and it's month by month and I gave it a start date and a this is the construction month I mean by the way it's early in the morning Saudi Arabia some banging outside so I'm sorry if you hear something was banging I'm not in a fancy hotel I mean a little-bitty hotel which is nicer okay now the the air the project cost and then to put some kind of s curves together at that we can just say okay let's take this period number and put in the average put in the standard deviation and then multiply it by the construction period switch and that's a standard kind of construction period switch that we and then when I do this and I sum it it doesn't sum to the whole thing because we have little different patterns so then we I just say okay let's normalize it let's take this curve and divide it by the total and then what you can do I'm getting into these gimmicks more and more which is kind of stupid let's put in a column and you know show what the s-curve really is and we can kind of see how it is and I've haven't we have a couple of little different patterns okay good and then then the next assumption says well what happens if it takes six months to collect and the government has been notably kind of a little bit late in paying sometimes this happens and that's really the risk what happens if we're late so this is how many months once you spend some money I should have maybe explained this a little bit better once you spend this money apply the s-curve to how much you spend this says when you're going to collect it now we're not quite finished they are going to charge a profit margin on this as well and they're also going to get an advance payment right at the beginning from the government they're going to give them twenty percent of the the funding then we have this to tattoo to figure out how we're going to finance this and they call it a PP effort I am totally embarrassed I can't remember what that stands for facility is the last one in project payment facility maybe yeah that's probably what it is and then we have a total loan commitment so this is classic loan terms with a commitment fee and a interest rate that I love to change now if there's a limit on how much they can bowl with this thing called what did I call it again a project payment facility or something like that there's a limit on this one we put some kind of limit and after that they after they the same bag gives them a short-term commitment and that interest rate I would think should be a little bit higher on the short-term commitment and then there's some kind of additional fee you have to pay so that's the assumptions and then you start with some some costs okay and all we do here is put the total amount of the costs nothing there just use f4 and I hope no matter what kind of mall you have you start with this little thing to the left and then we just plop our s curve in multiply our s curve by the amount and we get our expenditures I just needed to get this and then we're gonna add in this profit margin I said so that's the total amount you're allowed to collect but then we have to delay it and this time I had a 12-month delay now if you have a delay act it's people over use this offset command so much but this is where the offset would really be helpful you said okay let's go up to this total collection and then let's keep it on the same row that's what we do next and that's wait 12 months to get it okay and once we do that that's also no that's so that's it okay well it gives us a reference because it looks 12 months over here so when we do this kind of offset you almost always have to have a little if statement and say if the period is greater than or equal to this number then width and then if you don't like the faults because I like the false but most people don't so then we just put it here that's how much we collect so we're delaying our collections all right and then I have to multiply this not by one actually they collect 80% of this this is it maybe if you experienced this industry you can know what's going on but whatever so then we we compute this this fee the fee on top of that and then just like any model before you go to the P&L the cash flow or anything else please oh I had this class in this man after three days of a class he said no I put the balance sheet at the top oh my god the balance sheet has to be at the bottom the P&L is at late the cash flow is can second-to-last please Oh God you can do it otherwise but it gets horrible the mall should have a natural flow so I'm gonna see how much left how much money we can still borrow so to do that you go down to the opening balance to get the amount you can actually borrow and then you see well how much do I have left to borrow and you say this so this is the amount of the commitment the amount you've already taken and you need this for a minimum function below and then there was this this little thing that says 70% of the of the amount we charge up here 70% of this total amount up here is the only amount we're allowed to borrow I don't know why they have that in the term sheet but whatever they do and then here's the next key when you go to this borrowing schedule you should at the same time make a cash flow statement make all your titles in the cash flow statement and so what I'm going to do here is is just say well here are the loan drawers that we have now we only need to borrow money when the cash flow is negative and the cash flow gets negative here so there's a classic kind of thing you do and you put maximum and you open the bracket and you put a minus on this or zero okay that's how you start shift control R because my generic macros that nobody ever uses is open so that's how much meat we borrow but there's a little bit of a problem here let's let's start with this so now what you do is when you go to the borrowings you go downstairs to the thing we just did and collect the bollocks okay so far that's the big key is go down and get it now what happens here is eventually the the loan balance oh shoot come on oh no it didn't get up to 400 let's make it get up to 400 so then that means we can't borrow any more don't worry about the graph right now so I'm going to and let's put the interest rate a little bit higher maybe a lot higher that was and let's change the let's make the delay payments even more okay so that's all very automatic now I hope you see what we whatever okay where am i what did I not just put the bowings here maybe I did it in the wrong place oh excuse me longer so I'm gonna put I have to get the there are two facilities you remember this was the first debt facility and I just go and get that from the right cash flow statement I suppose I could say I made a mistake on purpose which would be a lie and I get that from this little maximum statement and then let's kind of see what happens ah it went up to four hundred and when it went up to four hundred that means we really can't borrow any more so that's pretty bad and so what we'd better do is we can never in let's look at this one in this case we have two standards three standards this is a limit on borrowing this is a limit on borrowing and of course the final limit on Boeing is the net cash flow that we have so when after we put this little maximum and we put a minimum in and this time a minimum is of two different things and this is kind of why I made the video most to show you how to kind of set up a nice little cash flow waterfall where we have this minimum and we also this minimum so we get the smallest of all those three things and that that determines how much you can borrow okay and now let's see if it worked all right let's go forward and we never get up to 400 that's that's good news okay I don't know why it doesn't get up to exactly that much but okay our in this case o our cash flow must be negative not be negative that's how much we need to borrow now they told me that when you repay the debt you repay it six months after you borrow it so let's use an offset again and this time we'll do it quickly if you go up to that number you say that number has to be greater than or equal to if that number is greater than or equal to the six press the f4 of course then put offset and you go up to the ball wings and you put coma zero comma - how long it takes to borrow and maybe I'm going to multiply that by one - okay so now we the bowings they get repaid here okay that's not so complicated is it and then we better put that downstairs in the cash flow statement so we go down to the repayment and we make in this cash flow statement we have all the cash that's coming in then the expenditures and then the repayment I'm going to come back to this in a minute and we put in the repayments from our this time I kind of went the other way because it's a fixed repayment and then let's look at the cash flow for a minute so the cash flow says you start with an opening balance and then you know they get this big payment right upfront and then they have all these expenses which include all the interest and commitment fees and I don't think I'm going to even bother going through all of that you I'll give you the file here the file will be I don't know where we'll be I'll put it on the little notes to the video where the file will be okay and then so what you do is you start with all the cash balance and then the opening cash balance is of course the closing balance balance in the very end right here okay whoops I went too far excuse me okay and then we have all our operating costs and we put in the repayment that came from upstairs and then we have the cash flow after the net cash flow after the repayment which is the receipts you get minus the costs and then we said well if that's that was our maximum and minimum and then we have some net cash flow and then I say oh well what happens and it's still negative that could be a default in some cases but in this case we have this other debt facility so we just can keep going on that facility and then eventually we have to repay so this has got a maximum that's how much we have to boil I said there was no limit I probably should have fixed that a little bit and I could have put the commitment in remember that okay it was a mistake and then we put the repayment and that's when the maximum is positive not negative it's positive not negative positive not negative and those two things with the minimum maximum and the minimum those two things go wait went right up here so we take for our short-term bonds we go downstairs and click on the amount and then we go downstairs and click on the next amount for the repayment and of course the closing balance is the opening balance plus this and I leave the positive number convention in these kind of things I cannot imagine doing it with switching sides the negative and positive I would just make too many mistakes and I don't really give a crap about this remember in a class we wasted an hour debating whether we should use the positive a number god Tim okay he's a nice guy okay and then we said well that's the remaining so that's the cash bounce we started with our opening cash bounce we moved through the end and then we can put our change in cash right at the end okay and now I think I've finished with filling in these things I wanted to show you so this was how to use the maximum and minimum and just kind of attach the buttons and when this man gave me this exercise and said can you do this thing I don't know how to do that and it wasn't you know if you apply the same kind of things now I also put a little cash balance here and a balance sheet a good little exercise to do that to do that we need to find how much income we have and figure out what's going on but I think that's a probably a worthwhile check to put in when you have all of these things and then you can finally go to the end and start playing with it so if we have 14 periods what happens is we didn't pay it all off so what we yeah we can see what kind of profit margin we need we can see what kind of delay we have and if we have a long enough delay oh that's ok hmm it just takes longer to get but if we don't get any profit there it is we can't pay off all the debt so we can kind of look and see how much cash we have at the very end from our balance sheet and how much debt so I'm gonna put this model somewhere on the disk and look on the website I have no idea where yet and it's corporate finance I suppose I'll put in one of the corporate finance ones and that's enough of this video