tag:blogger.com,1999:blog-21429514437370880352024-03-06T04:41:13.938-08:00Video Financial ModellingBrett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.comBlogger24125tag:blogger.com,1999:blog-2142951443737088035.post-43002491268033057212013-01-05T17:05:00.001-08:002013-01-05T17:05:50.393-08:00Modelling an Arrangement Fee Circularity<div class='posterous_autopost'><p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;">Ok, in project finance deals you’re going to come across a particularly circularity around calculating senior debt arrangement fees. This blog tutorial will focus on how to model this circularity.</p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><div class='p_embed p_image_embed'> <img alt="Modelling_arrangement_fee_circularities" height="336" src="http://getfile2.posterous.com/getfile/files.posterous.com/temp-2013-01-05/yIaAzyiibHcfkAzByhcbAFmnDIEEjdggvuGcmqlvoHiDIozwhlohlhAydBwn/Modelling_Arrangement_Fee_Circularities.png.scaled500.png" width="225" /> </div> </p> <h2><span>What is an Excel circularity?</span></h2> <p><span>In basic terms an Excel circularity is where a calculation refers to itself. In Excel you can allow circularities and the number of iterations which the calculation undergoes; however when you’re modelling and there are a few circularities it is best to break these up. </span></p> <h2><span>Why are arrangement fees circular?</span></h2> <p><span>Let’s think about this process. Firstly debt arrangement fees are calculated based on the total amount of debt. However debt arrangement fees are used to build up the total construction phase funding requirements, which are funded by debt. So basically as the arrangement fee amount goes up so too does the debt amount, which then causes the arrangement fee to increase. The arrangement fee and debt amount do converge though. Hopefully you see this circularity; however we will look at an example below. </span></p> <h2><span>An example of arrangement fees</span></h2> <p><span>For this example you will need to open up the pre-populated <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2012/12/ModellinganArrangementFeeCircularity.xlsx">spreadsheet</a> and <a href="http://youtu.be/9ogcWL5gyTA">youtube video</a><a name="_GoBack"></a> attached to this blog tutorial. Now in this example we assume that all construction costs are fully debt funded. Arrangement fees are 2% of the debt amount and the other construction phase costs are as per the <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2012/12/ModellinganArrangementFeeCircularity.xlsx">spreadsheet</a>. </span></p> <p><span>Now if we calculate the arrangement fees by multiplying the debt amount by the 2% arrangement fee, you’ll notice that we get a circularity as shown below.</span></p> <p><div class='p_embed p_image_embed'> <a href="http://getfile2.posterous.com/getfile/files.posterous.com/temp-2013-01-05/DtGsBcFFnsewqFykavanGHfirDijEcjJufqqEfEqcCvJkcjehHuhghDddbwF/Image1.png.scaled1000.png"><img alt="Image1" height="148" src="http://getfile0.posterous.com/getfile/files.posterous.com/temp-2013-01-05/DtGsBcFFnsewqFykavanGHfirDijEcjJufqqEfEqcCvJkcjehHuhghDddbwF/Image1.png.scaled500.png" width="500" /></a> </div> </p> <p>Now to break this circularity we need to hardcode a debt amount. You’ll see why this is important soon. Copy and paste the calculated debt amount into the hardcoded amount as shown below. You’ll need to paste the value so push Home, then the Paste dropdown, then values to do this.</p> <p><div class='p_embed p_image_embed'> <a href="http://getfile9.posterous.com/getfile/files.posterous.com/temp-2013-01-05/ssBsGFvqiBrypGtcqpyDCcHsgmsicehtCDBxnskfIvGfDFagGBfxdkCrohfI/Image2.png.scaled1000.png"><img alt="Image2" height="270" src="http://getfile4.posterous.com/getfile/files.posterous.com/temp-2013-01-05/ssBsGFvqiBrypGtcqpyDCcHsgmsicehtCDBxnskfIvGfDFagGBfxdkCrohfI/Image2.png.scaled500.png" width="500" /></a> </div> </p> <p>As shown above, create a check by subtracting the hardcoded debt amount from the calculated debt amount. Now when the model is solved this check amount will be zero. Link up the arrangement fee calculation to the hardcoded debt amount. Now, copy and paste values the calculated debt amount into the hardcoded debt amount, until the check amount is zero. This will occur when the arrangement fee converges and model is solved.</p> <p><div class='p_embed p_image_embed'> <a href="http://getfile0.posterous.com/getfile/files.posterous.com/temp-2013-01-05/AwoBtnDxHmonurDlfgBjafcwwtCDHcqhhjgcsIqDFiaEAFimFohpfmubGpvm/Image3.png.scaled1000.png"><img alt="Image3" height="206" src="http://getfile7.posterous.com/getfile/files.posterous.com/temp-2013-01-05/AwoBtnDxHmonurDlfgBjafcwwtCDHcqhhjgcsIqDFiaEAFimFohpfmubGpvm/Image3.png.scaled500.png" width="500" /></a> </div> </p> <p><span>If you liked this, check out our free project finance modelling training by <a href="http://www.videofinancialmodelling.com/subscribe-to-newsletter/">signing up to our newsletter</a>. </span></p> <p><span> </span></p> <p><span> </span></p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com2tag:blogger.com,1999:blog-2142951443737088035.post-84356943072818315372013-01-02T05:41:00.001-08:002013-01-02T05:41:05.428-08:00Naming Cells and Ranges in Excel<div class='posterous_autopost'><p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">At Video Financial Modelling we like to keep up to date with opinions of other financial modelling and Excel professionals. We had a quick look at an article<a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;">http://www.fimodo.com/2009/08/3-approaches-to-avoid-complex-off-sheet-references/</a> which fimodo produced regarding off-sheet references. Whilst fimodo is great and has a lot of fantastic articles, we tended to disagree with the extensive use of named cells and ranges. The remainder of the article was spot on, in terms of both dedicated import lines for each sheet and avoiding multi-level linking.</p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><div class='p_embed p_image_embed'> <img alt="Istock_000016556844xsmall" height="282" src="http://getfile2.posterous.com/getfile/files.posterous.com/temp-2013-01-02/tluCbfyjdcEglsFvadfhGdBJvxjuvffwmehtgkucHvFiliCrorraFvfFsrnu/iStock_000016556844XSmall.jpg.scaled500.jpg" width="425" /> </div> </p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">Now, whilst there are some best practice methods of developing a model which are wide spread within the industry, personal preference does come into financial model development. At Video Financial Modelling we like naming cells and ranges within our models. There are a number of reasons for this; however the main two are model audit and efficiency.</p> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font-size: 25px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;">Model Audit</h2> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">It is much easier to do a simple sense check of a financial model with names. For example if we saw a formula for operating expenditure which says, opex x inf_index x opsind. i.e. this says the operating index multiplied by the inflation index multiplied by the operations indicator. This is much easier than quickly sense checking a formula as follows assG4 x ts33:33 x ts 55:55. This is especially helpful when a third party is looking through your model.</p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">Of course, you have to be careful that the right cells and rows are named, however this should be relatively straightforward and no more onerous than looking up ordinary cell references.</p> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font-size: 25px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;">Efficiency</h2> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">If you start to use standard names regularly in your modelling then you can just type them in rather than going back to the page where the input is on. This is like a shortcut, and should be much quicker than navigating to the actual Excel sheet.</p> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font-size: 25px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;">How to name cells and ranges</h2> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">You can follow along with some of the below examples in the <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2012/12/NamingCellsandRangesinExcel.xlsx" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: initial; line-height: inherit;">Excel spreadsheet</a> and <a href="http://youtu.be/8j5BS-vEnYM" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: initial; line-height: inherit;">video tutorial</a> contained in this blog.</p> <h3 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font-size: 20px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;">Naming Cells</h3> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">Now this is easy, you can simply put the data in a cell, write a name to the left of the data cell, select the data cell and push CTRL +F3. Follow and accept the prompts and the cell will be named. We also have some other tricks for naming numerous cells at one time which you can find in our <a href="http://www.videofinancialmodelling.com/training/excel-training/excel-functions/" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: initial; line-height: inherit;">Excel Functions</a> training course.</p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">Ok, let’s do an example. Put 3% into a cell, then label it Inf (for inflation) to the left of the data. Click on the data (3%) again and press CTRL+F3. Follow the prompts until you’ve named the cell.</p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/NamingCellsandRanges/Image1.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="700" /></a></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">Now if you go onto it, there will be a name in the top left hand of the Excel screen as per below.</p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/NamingCellsandRanges/Image2.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="350" /></a></p> <h3 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font-size: 20px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;">Naming Ranges</h3> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">Put your data in a row. Type a name in say column E of row. Then select the name, press SHIFT+spacebar, to select the row. Once selected press CTRL+F3 and follow the prompts.<br />Now let’s do an example by naming the inflation index below.</p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/NamingCellsandRanges/Image3.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="700" /></a></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">Write InfIndex, select the name, then select the whole row by pushing SHIFT+spacebar</p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/NamingCellsandRanges/Image4.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="700" /></a></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">Push CTRL+F3 and follow the prompts to name the row.</p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/NamingCellsandRanges/Image5.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="700" /></a></p> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font-size: 25px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;">User beware – Naming Ranges</h2> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;">Now we do acknowledge that the use of named ranges can end in disaster, if they are not setup correctly. Here are a couple of tips to ensure that you setup named ranges in financial models correctly.</p> <ol style=""> <li style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;">You should consistently setup time series or data pages starting in the same column, say G. This will allow labels and totals to the left of the first date.</li> <li style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;">Make sure that you name time data ranges and use the named ranges on other pages. This will ensure the other pages time data will be aligned correctly.</li> <li style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;">Split your time data pages into monthly and quarterly/semi-annual data. Project finance models are usually monthly during construction and quarterly/semi/annual thereafter when debt starts to be paid. Now from Excel 2007,(which has extra columns all the way out to XFD or 16,384 columns!) you could get away with an all monthly financial model, however that is a topic for another day.</li> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline;"><p />Like this video tutorial. Check out our <a href="http://www.videofinancialmodelling.com/training" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: initial; line-height: inherit;">Excel and financial modelling training courses</a>.<div class='p_embed p_image_embed'> <img alt="Istock_000016556844xsmall" height="282" src="http://getfile4.posterous.com/getfile/files.posterous.com/temp-2013-01-02/ntkCIsCJipFBurswznsluIDahmHGfhbfEGeGhqnkBjHtwflymjnBavkhwfax/iStock_000016556844XSmall.jpg.scaled500.jpg" width="425" /> </div> </p> </ol></p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com2tag:blogger.com,1999:blog-2142951443737088035.post-8872844978402833192013-01-02T05:22:00.001-08:002013-01-02T05:22:49.247-08:00Naming Cells and Ranges in Excel <div class='posterous_autopost'><p> <h1><span>Naming cells and ranges in Excel</span></h1> </p> <table class="MsoTableGrid" border="1" align="left" style="border-collapse: collapse; border: none; margin-left: .1in; margin-right: .1in;" width="60%"> <tr style="height: 169.45pt;"> <td valign="top" style="border-right: none; padding: 0in 5.4pt 0in 5.4pt; height: 169.45pt;" width="96%"> <p style="text-align: left;"><span><br /><div class='p_embed p_image_embed'> <img alt="Istock_000016556844xsmall" height="282" src="http://getfile1.posterous.com/getfile/files.posterous.com/temp-2013-01-02/mBhhbJbytwGmzvhidoihClFymFzaqfgsbggklFiGIgsAmjybxbmcfveGnkFH/iStock_000016556844XSmall.jpg.scaled500.jpg" width="425" /> </div> </span></p> </td> <td valign="top" style="border-left: none; padding: 0in 5.4pt 0in 5.4pt; height: 169.45pt;" width="3%"> <p style="text-align: center;"><span> </span></p> <p style="text-align: center;"><span> </span></p> </td> </tr> </table> <p> <p><span> </span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><strong><em><span> </span></em></strong></p> <p style="text-align: left;"><span>At Video Financial Modelling we like to keep up to date with opinions of other financial modelling and Excel professionals. We had a quick look at an article (<a href="http://www.fimodo.com/2009/08/3-approaches-to-avoid-complex-off-sheet-references/">http://www.fimodo.com/2009/08/3-approaches-to-avoid-complex-off-sheet-references/</a>) which fimodo produced regarding off-sheet references. Whilst fimodo is great and has a lot of fantastic articles, we tended to disagree with the extensive use of named cells and ranges. The remainder of the article was spot on, in terms of both dedicated import lines for each sheet and avoiding multi-level linking. </span></p> <p><span>Now, whilst there are some best practice methods of developing a model which are wide spread within the industry, personal preference does come into financial model development. At Video Financial Modelling we like naming cells and ranges within our models. There are a number of reasons for this; however the main two are model audit and efficiency. </span></p> <h2><span>Model Audit</span></h2> <p><span>It is much easier to do a simple sense check of a financial model with names. For example if we saw a formula for operating expenditure which says, opex x inf_index x opsind. i.e. this says the operating index multiplied by the inflation index multiplied by the operations indicator. This is much easier than quickly sense checking a formula as follows assG4 x ts33:33 x ts 55:55. This is especially helpful when a third party is looking through your model. </span></p> <p><span>Of course, you have to be careful that the right cells and rows are named, however this should be relatively straightforward and no more onerous than looking up ordinary cell references. </span></p> <h2><span>Efficiency</span></h2> <p><span>If you start to use standard names regularly in your modelling then you can just type them in rather than going back to the page where the input is on. This is like a shortcut, and should be much quicker than navigating to the actual Excel sheet. </span></p> <h2><span>How to name cells and ranges</span></h2> <p><span>You can follow along with some of the below examples in the Excel <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2012/12/NamingCellsandRangesinExcel.xlsx">spreadsheet</a> and <a href="http://youtu.be/8j5BS-vEnYM">video tutorial</a> contained in this blog.</span></p> <h3><span>Naming Cells</span></h3> <p><span>Now this is easy, you can simply put the data in a cell, write a name to the left of the data cell, select the data cell and push CTRL +F3. Follow and accept the prompts and the cell will be named. We also have some other tricks for naming numerous cells at one time which you can find in our <a href="http://www.videofinancialmodelling.com/training/excel-training/excel-functions/">Excel Functions</a> training course. </span></p> <p><span>Ok, let’s do an example. Put 3% into a cell, then label it Inf (for inflation) to the left of the data. Click on the data (3%) again and press CTRL+F3. Follow the prompts until you’ve named the cell. </span></p> <p><strong> </strong></p> <p><span>Now if you go onto it, there will be a name in the top left hand of the Excel screen as per below. </span></p> <p><strong><span> </span></strong></p> <p><strong><span> </span></strong></p> <p><strong><span> </span></strong></p> <p><strong> </strong></p> <h3><span>Naming Ranges</span></h3> <p><span>Put your data in a row. Type a name in say column E of row. Then select the name, press SHIFT+spacebar, to select the row. Once selected press CTRL+F3 and follow the prompts. </span></p> <p><span>Now let’s do an example by naming the inflation index below.</span></p> <p> </p> <p><span>Write InfIndex, select the name, then select the whole row by pushing SHIFT+spacebar</span></p> <p><strong> <span> </span></strong></p> <p><span>Push CTRL+F3 and follow the prompts to name the row. </span></p> <p><strong> </strong></p> <h2><span>User beware – Naming Ranges</span></h2> <p><span>Now we do acknowledge that the use of named ranges can end in disaster, if they are not setup correctly. Here are a couple of tips to ensure that you setup named ranges in financial models correctly. </span></p> <p class="MsoListParagraphCxSpFirst" style=""><span>1)<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span>You should consistently setup time series or data pages starting in the same column, say G. This will allow labels and totals to the left of the first date. </span></p> <p class="MsoListParagraphCxSpMiddle" style=""><span>2)<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span>Make sure that you name time data ranges and use the named ranges on other pages. This will ensure the other pages time data will be aligned correctly. </span></p> <p class="MsoListParagraphCxSpLast" style=""><span>3)<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span>Split your time data pages into monthly and quarterly/semi-annual data. Project finance models are usually monthly during construction and quarterly/semi/annual thereafter when debt starts to be paid. Now from Excel 2007,(which has extra columns all the way out to XFD or 16,384 columns!) you could get away with an all monthly financial model, however that is a topic for another day. </span></p> <p><span>Like this video tutorial. Check out our <a href="http://www.videofinancialmodelling.com/training/">Excel and financial modelling training courses</a>.</span></p> <p><span> </span></p> <p><span> </span></p> <p><span> </span></p> <p><span> </span></p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com2tag:blogger.com,1999:blog-2142951443737088035.post-35222285638409467702012-12-31T07:34:00.001-08:002012-12-31T07:34:25.734-08:00Modelling a Major Maintenance Reserve Account (MMRA)<div class='posterous_autopost'><p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;">In project finance deals, major maintenance is usually very costly and infrequent in nature. For example in a toll road the road surface may need to be overhauled every 10 years. This major maintenance or lifecycle capex is expensive and usually cannot be funded sufficiently with operational cash flows. To get around this problem a reserve called a major maintenance reserve account (MMRA) or lifecycle reserve account (LRA) is usually used. This MMRA reserves cash progressively up until a forecast major capex spend.</span></p> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font-size: 25px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">Modelling a MMRA</span></h2> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;">Ok, so how do we model a MMRA by reserving cash up to forecast capex spends? Let’s assume that we have a:</span></p> <ul style=""> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: large;">CFADS of $20m pa (assume this is flat);</span></li> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: large;">Replacement capex of $25m every 10 years (assume two of these); and</span></li> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: large;">assume no interest is received on the MMRA.</span></li> </ul> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;">You can download the <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2012/12/ModellingaMMRA.xlsx" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: initial; line-height: inherit;">Excel spreadsheet</a> and <a href="http://youtu.be/UYQeW5P-eQU" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: initial; line-height: inherit;">video tutorial</a> related to the example above.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;">Now we are going to use a simple corkscrew account to model cash inflows (reserving for capex) and cash outflows (major capex). We could add in interest, however we will keep it simple in this example. Link up the opening balance to the closing balance of the previous period as per below.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/ModellingaMMRA/Image1.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="600" /></a></span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;">Now let’s put in our major capex amount of $25m every 10 years. This is a cash outflow from the reserve.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/ModellingaMMRA/Image2.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="800" /></a></span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;">Let us now put in the final part of the equation. We need to reserve $25m every 10 years. Now, we can specify how many periods (years in this case) we want to reserve this over. Let’s say we do it over the 10 years. i.e. $2.5m per annum. Now we are going to use an offset formula as follows to get the right amount to reserve per period. This is shown below.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/ModellingaMMRA/Image3.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="700" /></a></span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;">As you can see above there is a maximum of 15 years, which replacement capex can be reserved over. Hence we should put a note on the assumptions page that we cannot enter any number over 15 and the number should be an integer.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;">Finally we sum the total reserve amount up, and link it into the corkscrew account. If you have done everything correctly you should end up with something like the below.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/ModellingaMMRA/Image4.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="800" /></a></span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: large;">Like this video tutorial. Check out our <a href="http://www.videofinancialmodelling.com/training/project-finance/" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: initial; line-height: inherit;">project finance training courses</a>.</span></p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com1tag:blogger.com,1999:blog-2142951443737088035.post-28374774362690500492012-12-27T00:08:00.001-08:002012-12-27T00:08:47.702-08:005 Must Know Excel Charting Tips<div class='posterous_autopost'><p> <p><span>This is a quick video blog tutorial showing you some of the must know Excel charting tips. You can follow on with some examples by opening both the above Excel <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2012/12/5MustKnowExcelChartingTips.xlsx">spreadsheet</a> and the <a href="http://youtu.be/zkYSFOi6R1I">video tutorial</a>.</span></p> <p><span><div class='p_embed p_image_embed'> <img alt="Istock_000004878226xsmall" height="282" src="http://getfile6.posterous.com/getfile/files.posterous.com/temp-2012-12-27/BCsrvbwrbtgtlrFntbkyJBpalBIlbvqjzzwHHFDwmopErIhyCoyfhcgdhnAq/iStock_000004878226XSmall.jpg.scaled500.jpg" width="425" /> </div> </span></p> <h2><span>Tip 1: F11 to quickly chart</span></h2> <p><span>Simply select data and then press F11 to chart the data. You can also select multiple rows by holding down the CTRL key down. </span></p> <h2><span>Tip 2: F4 redo</span></h2> <p><span>How many times do you have to change the formatting such as font sizes on a chart? Well this is a handy tip. Simply change the size of the font on one axis, then select another the other axis font and press F4. This will redo the previous action and change the size of this font. </span></p> <h2><span>Tip 3: Putting another axis on a chart</span></h2> <p><span>This is an easy one. Simply select the data on the chart which you want to put on the second axis. Then select Layout, Format Selection and Secondary Axis.</span></p> <h2><span>Tip 4: Adding a dynamic title</span></h2> <p><span>Once you have a chart, simply select the chart, press Layout, Chart Title then = the cell with the data label. </span></p> <h2><span>Tip 5: Adding new data to a chart</span></h2> <p><span>Now this is a great little tip. To add new data to an existing chart, simply select the data, press CTRL+c to copy the data, then select the chart and push CTRL+v to paste. It is that simple. </span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>If you liked this blog tutorial check out our <a href="http://www.videofinancialmodelling.com/training/">online Excel and financial modelling training courses</a>. </span></p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-33540505099903143452012-12-24T03:46:00.001-08:002012-12-24T03:46:05.607-08:00Checks and Balances – Excel 2007 Conditional Formatting<div class='posterous_autopost'><p> <h1> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font-size: 25px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;"><span style="color: #3366ff; font-size: x-large;">What is conditional formatting?</span></h2> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">Conditional formatting in Excel ensures cells are formatted based on certain criteria. For example if we have a cell is equal to “No” and we want to colour all cells equal to “No” red, then we can do this using conditional formatting.</span></p> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font-size: 25px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;"><span style="color: #3366ff; font-size: x-large;">Why use it in financial modelling?</span></h2> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">There are a number of reasons you would use conditional formatting in in a financial model. One of the major reasons is to ensure model integrity through checks and balances. Examples of this include:</span></p> <ul style=""> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: large;">ensuring that your balance sheet balances; and</span></li> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: large;">there are no negative cash balances etc.</span></li> </ul> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">Conditional formatting gives the financial modeller a visual representation as to whether these checks are met and the financial model integrity is upheld.</span></p> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font-size: 25px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;"><span style="color: #3366ff; font-size: x-large;">Using conditional formatting for model checks and balances?</span></h2> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">Ok, the best way to learn conditional formatting is probably to look at a couple of examples. If you haven’t already done so open up the <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2012/12/Checks-and-Balances-Excel-2007-Conditional-Formatting.xlsx" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: initial; line-height: inherit;">spreadsheet</a> and the<a href="http://youtu.be/hd1F3bclPL8" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: initial; line-height: inherit;">youtube video</a> at the top of this blog tutorial.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">Ok first how do we find the conditional formatting button? Go to the:</span></p> <ul style=""> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: large;">Home menu; the</span></li> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: large;">go to Conditional Formatting; and then</span></li> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: large;">we are going to use the New Rule and the Manage Rules.</span></li> </ul> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">See below for a diagram showing the conditional formatting button.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/ConditionalFormatting/Image1.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="700" /></a></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">Now if we had a cell with Check and Ok and we wanted to change the cells:</span></p> <ul style=""> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: large;">so that when it equalled Check it would come up with a red background and white font; and</span></li> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: large;">when it equalled Ok it would come up with a green background and black font.</span></li> </ul> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">So, let’s select the Ok and Check by holding down the shift key and using your arrow keys (or your mouse) and holding down the left click.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">Once selected let’s go to Home, click the Conditional Formatting button and select the New Rule button. You should come up with the following screen. Enter in Format only cells that contain. Then select equal to and Check.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/ConditionalFormatting/Image2.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="700" /></a></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">Now select format and choose fill, select red and then go to font and select white under colour. Push Ok and then Ok again. The Check should now have a red background with white font as shown below.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/ConditionalFormatting/Image3.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="700" /></a></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">Whilst the Ok and Check cells are still selected go back to the conditional formatting button and select New Rule. Repeat the same procedure as above using equal to and Ok then select a green background with black font. You should come up with something like the below figure.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><a style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; line-height: inherit;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/12/ConditionalFormatting/Image4.png" border="0" alt="" style="margin: 0px; padding: 0px; font-size: 12px; font: inherit; vertical-align: baseline; display: block;" width="700" /></a></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">Now have a play around by changing the Check to Ok and vice versa. You’ll notice that the cells change formatting based on the value.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; font-weight: normal; line-height: 18.5px;"><span style="font-size: large;">If you like this article, check out our training course <a href="http://www.videofinancialmodelling.com/training/excel-training/excel-shortcuts/" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: initial; line-height: inherit;">Excel Shortcuts</a> which has plenty more tips and tricks.</span></p> </h1> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-15126450018978731492012-10-25T04:48:00.001-07:002012-10-25T04:48:31.065-07:005 Must Know Excel Shortcuts <div class='posterous_autopost'><p> <p style="margin: 5pt 0in; text-align: justify;"> <p style="margin: 5pt 0in;"><span style="font-size: 12pt;"><div class='p_embed p_image_embed'> <img alt="Shortcuts" height="227" src="http://getfile7.posterous.com/getfile/files.posterous.com/temp-2012-10-25/wfearADJCoejuDvkFphaJgHnymAzkojrcyqjuBCcFovgDogbciAfqdofqilu/shortcuts.png.scaled500.png" width="345" /> </div> There is one word for improving your efficiency in Excel. Shortcuts. In laymen terms shortcuts allow you to perform tasks quickly using your keyboard. This blog looks at 5 Must Know Excel Shortcuts which will help you improve your efficiency in Excel.</span></p> <p><span style="font-size: 12.0pt;"> </span><strong style="font-size: 12pt;"><span style="font-size: 12pt;">NOTE: </span></strong><span style="font-size: 12pt;">You can follow see how the shortcuts work yourself by downloading the accompanying Excel spreadsheet and YouTube video in the above Blog Downloads area.</span><span style="font-size: 12pt;"> </span></p> <p><span style="font-size: 12.0pt;"> </span><strong><span style="font-size: 12.0pt; color: #365f91;">Shortcut 1 - The Grandfather</span></strong></p> <p><span style="font-size: 12.0pt;">The first must know shortcut is quite literally the grandfather of all shortcuts. It sits at the top of the family tree and gives you access to numerous 1<sup>st</sup> and 2<sup>nd</sup> generation shortcuts.</span></p> <p><span style="font-size: 12.0pt;">What is it?</span></p> <p><span style="font-size: 12.0pt;"><span style=""> </span><strong>ALT + LETTER – gives you access to the Menu Ribbon – See Figure 1</strong></span></p> <p><div class='p_embed p_image_embed'> <a href="http://getfile5.posterous.com/getfile/files.posterous.com/temp-2012-10-25/CGaIarlGtahFjbevekmsJmAkufrqeBhrIHnycBHHjCFurxjIdFslthiCcHFJ/Figure1.png.scaled1000.png"><img alt="Figure1" height="65" src="http://getfile2.posterous.com/getfile/files.posterous.com/temp-2012-10-25/CGaIarlGtahFjbevekmsJmAkufrqeBhrIHnycBHHjCFurxjIdFslthiCcHFJ/Figure1.png.scaled500.png" width="500" /></a> </div> </p> <p><strong><em><span style="font-size: 12.0pt;">Figure 1: ALT+LETTER</span></em></strong></p> <p><span style="font-size: 12.0pt;">Notice the letters which pop-up in Figure 1, once the ALT key is pushed. If you push anyone of these letters then you will go into that menu. For example pushing “N” will take you into INSERT menu. </span></p> <p><span style="font-size: 12.0pt;">If you follow on the initial letter with another letter corresponding to an action, then that action will be performed. </span></p> <p><strong><span style="font-size: 12.0pt; color: #365f91;">Shortcut 2 – Charting at the Touch of a Button</span></strong></p> <p><span style="font-size: 12.0pt;">Want to insert an Excel chart at the touch of a button? Well, all you need to know is:</span></p> <p><span style="font-size: 12.0pt;"> <p><span><span style=""> </span><strong>F11 – Charts the selected cells</strong></span></p> </span><span style="font-size: 12pt;">Select the cells you want to chart and then press F11. It really is that simple.</span></p> <p><strong><span style="font-size: 12.0pt; color: #365f91;">Shortcut 3 - Finding your way</span></strong></p> <p><span style="font-size: 12.0pt;">Navigating through Excel sheets is made easy with this shortcut. Simply press:</span></p> <p><span style="font-size: 12.0pt;"> <strong> </strong></span><strong><span style="">-<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span style="">CTRL+PAGE DOWN – move one sheet to the right</span></strong></p> <p class="MsoListParagraphCxSpLast" style=""><strong><span><span style="font-size: 12pt;"> </span><span style="">-<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span style="font-size: 12pt;"> </span><span style="">-<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span style=""><span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span style="">CTRL+PAGE UP –move one sheet to the left</span></strong></p> <p><span style="font-size: 12.0pt;"> </span><span style="font-size: 12pt;">No more having to click from sheet to sheet, ensures you’re saving time.</span></p> <p><strong><span style="font-size: 12.0pt; color: #365f91;">Shortcut 4 – The Go to Guy for Auditing</span></strong></p> <p><span style="font-size: 12.0pt;">This is one of the best shortcuts for flying around the Excel layout and helping you audit your model or spreadsheet. </span></p> <p><strong><span style="font-size: 12.0pt;"> </span><span style="font-size: small;">F5 - (1) takes you to a highlighted formula; or (2) take you to a named range or cell</span></strong></p> <p><span></span></p> <p><span style="font-size: 12.0pt;"> </span><span style="font-size: 12pt;">For example if you have highlighted a particular input in a formula, you can press F5+ENTER and it will take you to that input. Also you can just press F5, select any named range or cell and press ENTER. Excel will navigate to that particular named range or cell.</span></p> <p><span style="font-size: 12.0pt;">You can also use F5 to perform another nifty trick. When tracing precedents or dependents you can press F5+ENTER and you will go back to the previous traced cell. </span></p> <p><strong><span style="font-size: 12.0pt; color: #365f91;">Shortcut 5 - We’re not sure if this is technically keyboard shortcut</span></strong></p> <p><span style="font-size: 12.0pt;">Some might say that shortcut 5 is not technically a shortcut. They may be right; however it can cut down the time taken to perform tasks such as formatting. Enter the:</span></p> <p><span style="font-size: 12pt;"><strong>APPLICATION KEY (although we like to call it the LIST key) – basically</strong></span><strong style="font-size: 12pt;">acts like your right mouse button</strong></p> <p><span style="font-size: 12pt;"></span></p> <p><span style="font-size: 12pt;">Having the LIST key means no more having to take your hand off the keyboard. The list key is shown in Figure 2.</span></p> <p><div class='p_embed p_image_embed'> <img alt="Application_key" height="287" src="http://getfile1.posterous.com/getfile/files.posterous.com/temp-2012-10-25/mpvyhIyuixqkpItapxeqHDgfqoqydaeJamtddAyksfBkIGmeEBtDExkegndk/application_key.GIF.scaled500.gif" width="335" /> </div> </p> <p><strong><em><span style="font-size: 12.0pt;">Figure 2: Picture of the Application Key (doesn’t it look like a LIST with a mouse cursor on it?)</span></em></strong></p> <p><span style="font-size: 12.0pt;">Figure 3 shows the display after the LIST key is pushed. As you can see, there are a number of items which can be performed. </span></p> <p><div class='p_embed p_image_embed'> <img alt="Figure3" height="410" src="http://getfile1.posterous.com/getfile/files.posterous.com/temp-2012-10-25/HDIngwEeBnjijeznEtojDhbsyzIegemCjpChflyzwCFyJpqwhcczcofzGuez/Figure3.png.scaled500.png" width="348" /> </div> </p> <p><strong><em><span style="font-size: 12.0pt;">Figure 3: Display after LIST key is pushed</span></em></strong></p> <p><span style="font-size: 12.0pt;">If you like this article, check out our </span><span><a href="http://www.videofinancialmodelling.com/training/excel-training/excel-shortcuts/"><span style="font-size: 12.0pt;">Excel </span>Shortcuts</a><a name="_GoBack"></a> </span><span style="font-size: 12.0pt;">training course which has plenty more tips and tricks.</span></p> <p><span> </span></p> </p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-6156909346354444102012-10-23T03:22:00.001-07:002012-10-23T03:22:16.679-07:00Untitled<div class='posterous_autopost'><p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"> <h1> <h2 class="blog-thumbnail-title post-title-color gdl-title" style="margin: 0px; padding: 0px 0px 5px; border: 0px; font: inherit; vertical-align: baseline; color: #338db7 !important; font-family: Merriweather;"><a href="http://www.videofinancialmodelling.com/carindicators_howtheycanhelpyoumodelinexcel/" style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #338db7 !important; text-decoration: none;">Car Indicators – How they can help you model in Excel</a></h2> </h1> </p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;"><div class='p_embed p_image_embed'> <img alt="Car-indicator" height="230" src="http://getfile5.posterous.com/getfile/files.posterous.com/temp-2012-10-23/lkdrufECoivlAeDAvqccaEvzkdkDnHGqFGvJqiGEwjptszbJyqocdAuaGnnE/car-indicator.jpg.scaled500.jpg" width="345" /> </div> How could flickering car indicators possibly help you model in Excel. All will be revealed below.</span></p> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; line-height: 18.5px;"><span style="font-size: x-large;"><strong>On, off, on</strong></span></h2> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">Think about what happens when you put your car indicator on. The light flicks on, then off, then on and so on, right? A similar concept can be used in Excel modelling to simplify often quite complex problems.</span><br /><span style="font-size: medium;">Introducing binary indicators. Similar to car indicators, binary indicators turn on and off. If you’re not familiar with the world of binary operators then below is a brief description. When a binary operator is equal to:</span></p> <ul style=""> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: medium;">1 it is on – an illuminated car indicator</span></li> <li style="margin: 0px 0px 6px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: medium;">0 it is off – non-illuminated car indicator</span></li> </ul> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">Yeah, yeah, that is great, but how can these binary indicators help me in Excel? Well let’s take a look at a couple of examples.</span></p> <div class="blognote" style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;"><strong style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">NOTE:</strong> You can follow these examples yourself by downloading the Excel spreadsheet and watching the YouTube video.</span></div> <p /> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; line-height: 18.5px;"><span style="font-size: x-large;"><strong>Example 1</strong></span></h2> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">Imagine you’re asked to model construction costs for a project based on the following assumptions:</span></p> <ol style=""> <li style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: medium;">Construction costs are $500,000 per month</span></li> <li style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: medium;">The construction period runs for 2 years from 1 January 2011 to 31 December 2012</span></li> </ol> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">How would you go about modelling this in Excel? Some of you may say that’s easy… I would just do the following formula:</span></p> <p class="blogequation" style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">=500,000 x <a href="http://office.microsoft.com/en-us/excel-help/if-function-HP010342586.aspx?CTT=1" target="_blank" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none; line-height: inherit;">IF</a>(<a href="http://office.microsoft.com/en-us/excel-help/and-HP005208986.aspx?CTT=1" target="_blank" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none; line-height: inherit;">AND</a>(1 January 2011<=31 December 2012),1,0) or IF(AND(1 January 2011<=31 December 2012),500000,0) – see Figure 1</span></p> <div class="blogfigure" style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;"><a style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #2a84ae;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2011/01/Blog%20Figures/Blog%202/Figure1.png" border="0" alt="" style="margin: 0px; padding: 0px; font: inherit; vertical-align: baseline; display: block;" /></a></span> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline;"> </p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline;"><span style="font-size: medium;">Figure 1: Long and cumbersome formula for Example 1</span></p> </div> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">Whilst this works, the formula is long and cumbersome. This can cause troubles for external parties such as model auditors, may cause you to lose your mind trying to work out what you did previously and the construction IF statement cannot be used again in other formulas (you will see what we mean in a second). Don’t despair we can break this down into much more manageable components as follows:</span></p> <ol style=""> <li style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: medium;"><strong style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">Input the assumptions</strong> Construction Cost per month, Construction Start Date and Construction End Date</span></li> <li style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: medium;"><strong style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">Create a Construction Indicator line:</strong> =(1 January 2011<=31 December 2012). Notice that we have got rid of the <a href="http://office.microsoft.com/en-us/excel-help/and-HP005208986.aspx?CTT=1" target="_blank" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none;">AND</a> logical function. The new formula will create a one when the formula is true for both of legs and a 0 when at least one of the legs is false. Note that this Construction Indicator can be used in other construction period formulas (hence this construction indicator can be referred to over and over again)</span></li> <li style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; line-height: 18px;"><span style="font-size: medium;"><strong style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">Construction Costs:</strong> Multiply the Construction Cost per month by the Construction Indicator created in (2)</span></li> </ol> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">See Figure 2 for the above workings.</span></p> <div class="blogfigure" style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;"><a style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #2a84ae;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2011/01/Blog%20Figures/Blog%202/Figure2.png" border="0" alt="" style="margin: 0px; padding: 0px; font: inherit; vertical-align: baseline; display: block;" /></a></span> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline;"> </p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline;"><span style="font-size: medium;">Figure 2: Simpler and easier to understand solution for Example 1</span></p> </div> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">What do you think? Is this a lot easier to understand than the original formula? Let’s take a look at another example.</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather;"><span style="font-size: x-large;"><strong>Example 2</strong></span></h2> </p> <p> <h2 style="margin: 0px 0px 15px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #494949; font-family: Merriweather; font-weight: normal; line-height: 18.5px;"><span style="font-size: medium; color: #666666; font-family: Droid Sans;">Our next example asks us to sum the number print media sources from an online survey. See Figure 3.</span></h2> </p> <div class="blogfigure" style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;"><a style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #2a84ae;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2011/01/Blog%20Figures/Blog%202/Figure3.png" border="0" alt="" style="margin: 0px; padding: 0px; font: inherit; vertical-align: baseline; display: block;" /></a></span> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline;"> </p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline;"><span style="font-size: medium;">Figure 3: How’d you find out about us categories</span></p> </div> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">How do we solve this problem? Let’s use our binary indicators (aka car indicators) in conjunction with an <a href="http://office.microsoft.com/en-us/excel-help/or-function-HP010062403.aspx?CTT=1" target="_blank" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none; line-height: inherit;">OR</a> function. Noticing that there are only two print media sources we would input the following formula:</span></p> <p class="blogequation" style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">= <a href="http://office.microsoft.com/en-us/excel-help/or-function-HP010062403.aspx?CTT=1" target="_blank" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none; line-height: inherit;">OR</a>(How’d you find out about us data=”Newspaper”,How’d you find out about us data=”Magazine”)*1 – see Figure 4</span></p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">You’ll note that we have multiplied the <a href="http://office.microsoft.com/en-us/excel-help/or-function-HP010062403.aspx?CTT=1" target="_blank" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none; line-height: inherit;">OR</a> function by one. This is because the <a href="http://office.microsoft.com/en-us/excel-help/or-function-HP010062403.aspx?CTT=1" target="_blank" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none; line-height: inherit;">OR</a> function alone will only return TRUE or FALSE. If we multiply the <a href="http://office.microsoft.com/en-us/excel-help/or-function-HP010062403.aspx?CTT=1" target="_blank" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none; line-height: inherit;">OR</a> function by 1 then we will get 1 for TRUE and 0 for FALSE.</span><br /><span style="font-size: medium;">Now copy and paste this formula down. Add all the 1’s using the <a href="http://office.microsoft.com/en-us/excel-help/sum-HP005209290.aspx" target="_blank" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none; line-height: inherit;">SUM</a> function and you’re done. You should end up with something similar to Figure 4.</span></p> <div class="blogfigure" style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;"><a style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #2a84ae;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2011/01/Blog%20Figures/Blog%202/Figure4.png" border="0" alt="" style="margin: 0px; padding: 0px; font: inherit; vertical-align: baseline; display: block;" /></a></span> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline;"> </p> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline;"><span style="font-size: medium;">Figure 4: Solution to Example 2 along with formula for OR function</span></p> </div> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">You now know how many print media sources are included in the survey.</span></p> <div class="blognote" style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline;"><span style="font-size: medium;">NOTE: Example 2 could have been solved using a <a href="http://office.microsoft.com/en-us/excel-help/countif-function-HP010342346.aspx?CTT=1" target="_blank" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none; line-height: inherit;">COUNTIF</a> function; however that’s a topic for another day.</span></p> </div> <p style="margin: 0px 0px 20px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline; color: #666666; font-family: Droid Sans; line-height: 18.5px;"><span style="font-size: medium;">So now you see. Those flashing things called car indicators really do help you to model in Excel.</span><br /><span style="font-size: medium;">If you like this Blog, check out our <a href="http://www.videofinancialmodelling.com/training/excel-training/excel-functions" style="margin: 0px; padding: 0px; border: 0px; font-size: 12px; font: inherit; vertical-align: baseline; color: #2a84ae; text-decoration: none; line-height: inherit;">Excel Functions</a> training course which has plenty more tips and tricks.</span></p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-80558299554758897832012-10-04T07:07:00.001-07:002012-10-04T07:07:34.359-07:00Mini-Perms in Project Finance<div class='posterous_autopost'><p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>In this blog tutorial the Video Financial Modelling team dives into the world of mini-perms. And no we are talking about those 80’s hairstyles. </span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span><br /></span></p> <h2><span>What are Mini-Perms?</span></h2> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>Mini-perms are lending instruments which are intended to be refinanced out after a short timeframe usually 5-7 years. A number of project finance and PFI deals have been done using a mini-perm financing structure given the lack of liquidity in the long term lending market post the GFC. In this tutorial we will look at why mini-perms are being taken up, the different types of mini-perms and the issues related to these instruments.<br /> </span><strong><span style="font-size: 13.0pt; line-height: 115%; font-family: Cambria,serif; color: #4f81bd;">Why are mini-perms being taken up?</span></strong></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>Mini-perms have been promoted by banks, in a large number of financial markets, including in the UK PFI market. The main reasons for their use include:</span></p> <p class="MsoListParagraphCxSpFirst" style=""><span><span style="font-size: 7pt; font-family: Times New Roman;"> </span>i.<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span> concerns over long-term liquidity – an example of this is the introduction of Basel III outlining a global regulatory standard for capital requirements;</span></p> <p class="MsoListParagraphCxSpMiddle" style=""><span><span style="font-size: 7pt; font-family: Times New Roman;"> </span>ii.<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span>the banks’ inability to underwrite and syndicate deals, with the resultant dependence on bank clubs for funding. i.e. banks are being forced to lend and hold; and</span></p> <p class="MsoListParagraphCxSpLast" style=""><span><span style="font-size: 7pt; font-family: Times New Roman;"> </span>iii.<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span>a certain degree of opportunism among those banks still in the market.</span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>Where there is limited liquidity for deals, banks prefer mini-perms given the short term over which they can amortise their arrangement fees. In addition there are fewer active lending banks in the post GFC world (i.e. less competition), hence there where a process isn’t competitive or there is limited appetite banks can dictate there terms.<p /> </span><strong><span style="font-size: 13.0pt; line-height: 115%; font-family: Cambria,serif; color: #4f81bd;"></span></strong></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><strong><span style="font-size: 13.0pt; line-height: 115%; font-family: Cambria,serif; color: #4f81bd;"> </span></strong></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><strong><span style="font-size: 13.0pt; line-height: 115%; font-family: Cambria,serif; color: #4f81bd;">Types of mini perms</span></strong></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>There are typically two types of mini-perms a hard mini-perm and a soft mini-perm. We will look at each in turn. </span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span class="Heading3Char"><span>Hard Mini-Perm</span></span><span><br /> A hard mini-perm is a short term loan that mimics a longer term amortisation profile, but with a bullet repayment at the end of the tenor. Legal maturity of this instrument is typically around 5-7 years, forcing the borrower to refinance before maturity or face default. The main disadvantage is the default and refinancing risk for all stakeholders (funders, borrower and Government), which in a PFI deal may mean the termination of the Concession.<p /> </span><span class="Heading3Char"><span>Soft Mini-Perm</span></span><span><br /> A soft mini-perm is a long term loan with a mechanism to incentivise the borrower to refinance after an initial short period usually 5-7 years. Two methods for incentivising the borrower include:</span></p> <p class="MsoListParagraphCxSpFirst" style=""><span>1)<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span>ratcheting up margins post the initial period; and</span></p> <p class="MsoListParagraphCxSpLast" style=""><span>2)<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><span>using a cash sweep post the initial tenor</span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>A soft mini-perm is a structure without the default risk of a hard mini-perm, where the loan maturity remains long-term. The soft mini-perm has been used in UK PFI and continues to be promoted by banks on a number of projects.<p /> </span><span class="Heading2Char"><span style="font-size: 13.0pt; line-height: 115%;">Mini-Perm Financial Modelling<br /> </span></span><span><br /> Let’s look at some examples of mini-perms assuming the following inputs. You can follow along by downloading the spreadsheets and youtube video. Also note that this analysis assumes that there are no refinancing fees etc. <br /> For all scenario below we use LIBOR of 400bps, and an ongoing CFADS of 1,300 per month. <br /> <strong>Hard mini-perm:</strong> 5 year initial tenor, bullet at maturity, amortisation profile mimics 20 year tenor, margins 250bps and a credit foncier repayment profile. Note that a credit foncier repayment profile is like a home loan mortgage with fixed instalments. See our tutorial, <a href="http://www.videofinancialmodelling.com/cfads-and-dscr-sculpting/">CFADS and DSCR sculpting</a>, which goes into the credit foncier repayment profile in depth. <p /> </span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>Using the actual margins for the hard mini-perm we get the following.</span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><div class='p_embed p_image_embed'> <a href="http://getfile8.posterous.com/getfile/files.posterous.com/temp-2012-10-04/GvIecJkhasigoFmjmohvkDsiGncndAeIqpcrgdegzHhCdkutooIbduhDzDjp/Image1.png.scaled1000.png"><img alt="Image1" height="327" src="http://getfile0.posterous.com/getfile/files.posterous.com/temp-2012-10-04/GvIecJkhasigoFmjmohvkDsiGncndAeIqpcrgdegzHhCdkutooIbduhDzDjp/Image1.png.scaled500.png" width="500" /></a> </div> </p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span><br /></span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"> </p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>As mentioned the hard mini-perm is only short term debt and hence has no margins post the initial tenor. Borrowers usually have to estimate the margins post this short term debt tenor. Note that given the short legal tenor of the facility, the borrower must refinance the loan at the end of the initial tenor.</span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>If the borrowers were to assume that the margins continue at 250bps we would get a normal credit foncier profile as per the below. Given the constant CFADS we would get a DSCR that is constant. </span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"> </p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><strong><span>Soft mini-perm:</span></strong><span> 20 years, margins 250bps then ratcheting up to 500bps post 5 years </span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>As you can see in the below the rachet up in margins after the initial tenor of 5 years causes the debt service to increase in mid 2016. With a constant CFADS this margin rachet would decrease the DSCR and eat into potential equity distributions.</span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"> </p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span>Given the high debt service post margin rachet (and the likelihood of these rates occurring) borrowers usually assume different margins post the margin rachet. If we assume that the margins continued at 250bps then we would have the exact same profile as in the hard mini-perm assumed rates case. </span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"> </p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span><br /> As you can see from the above although the dynamics of soft and hard mini-perms are quite different it could be that borrowers could assume the same financial forecasts, particularly with respect to debt service modelling. One should note however, that the hard mini-perm is inherently more risky given the refinance/default risk. Now let’s look at a number of issues which occur with both hard and soft mini-perms. </span></p> <p style="margin-top: 10.0pt; margin-right: 0in; margin-left: 0in;"><span class="Heading2Char"><span style="font-size: 13.0pt; line-height: 115%;">Issues with mini-perm structures<br /> </span></span><span> <br /> There are a number of issues which borrowers face with mini-perm structures. These include:</span></p> <p class="MsoListParagraphCxSpFirst" style=""><span>i)<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><strong><span>Hedging:</span></strong><span> do you put a short term swap in or long term swap? Short term swaps will face refinancing risk on the underlying rate, usually LIBOR. Long term swaps assume a certain repayment profile which may change over time; </span></p> <p class="MsoListParagraphCxSpMiddle" style=""><span>ii)<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><strong><span>Affordability:</span></strong><span> what assumptions do the stakeholder (predominantly the borrower and the Concession grantor) make about margins, underlying interest rates, tenor and amortisation profile;</span></p> <p class="MsoListParagraphCxSpLast" style=""><span>iii)<span style="font-size: 7pt; font-family: Times New Roman;"> </span></span><strong><span>Sharing of risks:</span></strong><span> who takes the risk on margins, underlying interest rates, tenor and amortisation profile?</span></p> <span style="font-size: 11.0pt; line-height: 115%; font-family: Calibri,sans-serif;">The above issues are resolved on a project by project basis.<p /> </span></p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-41361620898195384182012-09-24T06:39:00.001-07:002012-09-24T06:39:55.717-07:00Why you don't have to be a VBA macro master to build a financial model - Part 2<div class='posterous_autopost'><h1><span><br /><img height="255" align="left" alt="" width="385" /></span></h1> <p> </p> <h1><span><p /></span></h1> <p><span>In part 2 of our “Why you don’t need to be a macro master to build a Financial Model” we are going to look at the main types of macros you’ll need to produce an advanced financial model. The three types of macros we are going to look at are:</span></p> <p> </p> <p><span>1) </span><span>A goal seek macro – can be used to break a circularity</span></p> <p><span>2) </span><span>A single cell copy and paste macro – we covered this in part 1 of this series </span></p> <p><span>3) </span><span>A multiple cell copy and paste macro</span></p> <p><span>Goal Seek Macro </span></p> <p><span>Ok, this is probably the easiest macro to implement. It i</span></p> <h1><span><div class='p_embed p_image_embed'> <a href="http://getfile6.posterous.com/getfile/files.posterous.com/temp-2012-09-24/flpiciGbwupGDcmosjruuvGeudCwjnfjnciwusmFhDoeFenGkppIGsbbJhAb/pic1.png.scaled1000.png"><img alt="Pic1" height="168" src="http://getfile2.posterous.com/getfile/files.posterous.com/temp-2012-09-24/flpiciGbwupGDcmosjruuvGeudCwjnfjnciwusmFhDoeFenGkppIGsbbJhAb/pic1.png.scaled500.png" width="500" /></a> </div> </span></h1> <p><span>s very similar to the single cell copy and paste macro. If you are not aware of what a goal seek is we will try and sum it up in a line or two. </span></p> <p><span>A goal seek will try and solve a certain cell to a specified value by changing another. If you look at the below figure we have 50 sales, each with a profit margin of $3.5/unit giving a total profit of $175. We want to know what the profit margin would have to be to get a total profit of $200. Obviously the profit margin has to be $4 per unit. But look how we found it. We goal seeked it by setting the total profit cell to 200 by changing the profit margin per unit. </span></p> <p><img height="262" alt="" width="579" /> </p> <p><span>Ok, now let’s cheat by going back to our spreadsheet in part 1. As per part 1 we are going to record a macro. Go to View, Macros, Record Macro. Let’s call the macro GoalSeek. Go to the DebtCheck value and select Data, What-if Analysis and then Goal Seek. We want to select to value 0, by changing cell DebtHard value as per the below figure. </span></p> <p><img height="284" alt="" width="630" /> </p> <p><span>Stop the recording by pressing the stop button in the bottom left hand corner.</span></p> <p><span>Now let’s edit the macro. Go to View then Macros, View Macros. Select the GoalSeek macro and press edit. </span></p> <p><span>We are going to delete the Range("D13").Select at the top. And we are going to replace the cells with their actual names as per below. </span></p> <p><img height="226" alt="" width="593" /> </p> <p><span>Now go back to the spreadsheet. Put 12,500 into the DebtHard cell (G12). Go to Data, Macro and View Macro. Select GoalSeek and press Run. This should solve the macro and set the DebtCheck to 0. </span></p> <p><span>Single Cell Copy and Paste Macro</span></p> <p><span>Now we looked at a single cell copy and paste macro in the first part of this series so we are not going to cover it here. If you want to check out that article, <a href="http://www.videofinancialmodelling.com/why-you-dont-need-to-be-a-vba-macro-master-to-build-a-financial-model-part-1/">click here</a>. </span></p> <p><span>Multiple Cell Copy and Paste Macro</span></p> <p><span>Ok, we’re going to leave you to do a bit of homework on this one. Depending on how you go we might look at doing a third part to this course. Go to the spreadsheet and scroll down to question two. See how you go with it. If you’d like us to do a third part to this macros series, leave us a comment or send us an email. Hint: In this question you might find that the countif function will come in handy.</span></p> <p><span>We cover macros in detail in our advanced toll road training course. <a href="http://www.videofinancialmodelling.com/training/project-finance/greenfield-toll-road-model-advanced/">Click here</a> to check it out. </span></p> <p><span> </span></p> <p> </p> <p> </p> <p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-77234904734598065682012-09-13T04:01:00.001-07:002012-09-13T04:01:00.375-07:00Why you don't need to be a macro master to build a financial model - Part 1<div class='posterous_autopost'><p> Normal 0 false false false false EN-GB X-NONE X-NONE </p> <p>Many self-proclaimed financial modeling experts will tell you that you need to have a good grasp of Visual Basic for Applications (VBA) and macros. Well at Video Financial Modelling we are here to tell you that you don’t. Well not if you’ve read this article.</p> <p><strong>Resources</strong></p> <p><a href="http://www.youtube.com/watch?v=5jEtX_R8yBw&feature=share&list=UUmUvEKxr_LTYQ51mZlPq4SA" title="YouTube Video" target="_blank">YouTube Video</a></p> <p><a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2012/09/WhyyoudontneedtobeaVBAmacromastertobuildafinancialmodel-Part-1-Starter.xlsm" title="Starter Spreadsheet" target="_blank">Starter Spreadsheet</a></p> <p><a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2012/09/WhyyoudontneedMacros_Part1Final.xlsm" title="Final Spreadsheet" target="_blank">Final Spreadsheet</a></p> <p><strong style="">Why do we need macros?</strong></p> <p>In simplistic terms a macro is a program that implements a task. VBA macros are often used to automate tedious financial modeling tasks or to break circularities.</p> <p><strong style="">Why don’t you need to be a macro master?</strong></p> <p>Well there are a number of reasons why you don’t need to be a VBA macro master.</p> <p class="MsoListParagraphCxSpFirst" style=""><span style=""><span style="">1)<span style="font: 7.0pt Times New Roman;"> </span></span></span>Firstly, we’re going to give you some common macros that you can simply copy and paste into VBA in your financial model</p> <p class="MsoListParagraphCxSpMiddle" style=""><span style=""><span style="">2)<span style="font: 7.0pt Times New Roman;"> </span></span></span>Secondly, it is really easy to record your own macros and then manipulate them to suit your particular situation. We’ll look at an example a bit later</p> <p class="MsoListParagraphCxSpLast" style=""><span style=""><span style="">3)<span style="font: 7.0pt Times New Roman;"> </span></span></span>Thirdly and finally in all the financial models we have seen (and we’ve seen a lot) there are only a few situations that require a macro. We will run through each below.</p> <p>Firstly let’s look at an example on how to record a macro.</p> <p><strong style="">Example – Manually recording a macro and then manipulating it</strong></p> <p>In this example we are going to look at Firstly open the accompanying blog workbook. Also remember you can watch the blog youtube video to follow this example.</p> <p>To start recording a macro, press View on the menu and then select Macros. Select Record Macro on the popdown menu. Let’s call the macro DebtSolve.</p> <p><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/09/Macros1/Pic1.png" border="0" alt="" style="" width="700" /></p> <p><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/09/Macros1/Pic2.png" border="0" alt="" style="" width="700" /></p> <p><span style=""></span></p> <p>Select the Debt Amount – Calculated value and then copy the cell (CTRL+c) and paste special values (Home, Paste, Values) in the Debt Amount – Hardcoded cell. Press enter.</p> <p style="line-height: normal;"><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/09/Macros1/Pic3.png" border="0" alt="" style="" width="700" /><span style="color: black;"> </span></p> <p> </p> <p>Now press the stop button in the bottom left hand corner.</p> <p><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/09/Macros1/Pic4.png" border="0" alt="" style="" width="700" /></p> <p> </p> <p> </p> <p>Go to Data, Macro and push View Macro. Then select the DebtSolve macro and press edit. We are now going to put the named cells into the macro and add in a loop.</p> <p><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/09/Macros1/Pic5.png" border="0" alt="" style="" width="700" /></p> <p>Ok, let’s firstly put the relevant named ranges in. Just copy both the DebtCalc and DebtHard and put them in place of the relevant cells in the VBA file.</p> <p>Now let’s add the loop.</p> <p>Simply copy and paste the following code:</p> <p>Do While Range("DebtCheck") <> 0.</p> <p>We’ll also have to put in:</p> <p>Range(“DebtCalc”) to replace the Selection before copy.</p> <p>Place it at the start of the code after the Sub Debt Solve() and the green commentary.</p> <p>And now put a Loop before the End Sub. The final macro should look something like this.</p> <p><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2012/09/Macros1/Pic6.png" border="0" alt="" style="" width="700" /></p> <p>This loop will keep copying and pasting the calculated debt amount into the hardcoded debt amount, until these values converge and the debt check becomes zero.</p> <p>Now let’s go and select Insert on the menu, select Shape and then a rectangle. Draw it on the sheet. Then right click on the rectangle and select Assign Macro and press DebtSolve.</p> <p>Now press the button you just created and voila you’ve created a macro that solves a common financial modeling circularity. Change the hardcoded value or the arrangement fee and press the button again. It should solve.</p> <p>In the next part to this macro series we are going to look at the main types of macros you’re going to need when you’re financial modeling.<span style=""> </span></p> <p> </p> <p> </p> <p><span style=""> </span></p> <p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-48830840910658751142011-12-19T13:49:00.001-08:002011-12-19T13:49:29.744-08:00Self-Study Financial Modeling Training – The Way of the Future<div class='posterous_autopost'><p>If you work in the finance industry, with:</p> <ul> <li><span style="">an unknown economic outlook;</span></li> <li><span style="">companies budgets stretched to the limits; and</span></li> <li><span style="">unemployment at all-time highs</span></li> </ul> <p><span>it is important to stand out from the rest. </span></p> <p><span>Learning and improving your financial modeling skills can help you to do this. At <a href="http://www.videofinancialmodelling.com" title="Video Financial Modelling" target="_self">Video Financial Modelling</a> we understand that not everyone can afford training sessions ranging from $500-$2,000 each. </span></p> <p><span>To cater to this <a href="http://www.videofinancialmodelling.com" title="Video Financial Modelling" target="_self">Video Financial Modelling</a> has setup a number of self-study <a href="http://www.videofinancialmodelling.com/products" title="Financial Modeling Training" target="_self">financial modeling training</a> courses to cater for people who want the best possible training at a fraction of the cost. We have training courses to suit all levels of financial modeling expertise. </span></p> <p><span>What differentiates a self-study financial modeling training course from a seminar? Yes, the price, but there are also a number of other factors.</span></p> <ol> <li><span style=""><strong>Flexibility</strong> – given that the training is pre-recorded you can learn at your own pace. If you don’t catch a concept you can easily just rewind the video and watch it again.</span></li> <li><span style=""><strong>Quality</strong> – since we only do each training course once, we only pick the best instructors to deliver you content. </span><span style=""> </span></li> <li><span style=""><strong>Ongoing customer support</strong> – because we have lowered our cost base, we supply excellent ongoing customer support and can answer your training course questions promptly.</span></li> <li><span style=""><strong>Limited risk</strong> – we are so confident in our products that we have “</span><span style="text-decoration: underline;"><a href="http://www.videofinancialmodelling.com" title="Video Financial Modelling - Try before you buy" target="_self">try before you buy options</a></span><span style="">” (at a nominal cost) for many of our training courses. If you proceed to purchase the full version the nominal cost will be deducted from the full purchase price.</span></li> </ol> <p><span>In addition to the above, the self-study courses are hands-on giving you the best possible practical experience. </span></p> <p><span>Check the self-study <span style="text-decoration: underline;"><a href="http://www.videofinancialmodelling.com/products" title="Financial Modeling Training" target="_self">financial modeling training</a></span> at <a href="http://www.videofinancialmodelling.com" title="Video Financial Modelling" target="_self">Video Financial Modelling</a> today. </span></p> <p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-81649768408106835252011-12-13T13:06:00.001-08:002011-12-13T13:06:43.754-08:00What are Financial Statements?<div class='posterous_autopost'><p> <p><span>Financial statements commonly refer to formal records of the financial actions of businesses, entities and in some instances individuals. The financial statements are used by various stakeholders in making economic decisions. </span></p> <p><span>Businesses which are listed are usually required to prepare and issue their financial statements to their shareholders. In most instances these financial statements are prepared in accordance with International Financial Reporting Standards (IFRS), US Generally Accepted Accounting Principles (GAAP) or UK GAAP. In recent times the accounting bodies have been working together to converge these reporting standards. </span></p> <p><span>There are generally four major financial statements which are reported by businesses. </span></p> <p style=""><span>1)<span style="font: 7.0pt Times New Roman;"> </span></span><span>Balance Sheet </span></p> <p style=""><span>2)<span style="font: 7.0pt Times New Roman;"> </span></span><span>Income Statement</span></p> <p style=""><span>3)<span style="font: 7.0pt Times New Roman;"> </span></span><span>Statement of Owner’s Equity</span></p> <p style=""><span>4)<span style="font: 7.0pt Times New Roman;"> </span></span><span>Cash Flow Statement</span></p> <p><span>The Balance Sheet shows an accurate representation of a business’s financial position at a certain fixed point in time. The Balance Sheet has three broad categories, assets, liabilities and owner’s equity. For example a bank loan would be shown as a liability on the Balance Sheet. </span></p> <p><span>The Income Statement can be simply thought of as total revenues minus total expenses (including financing costs) over a period of time, usually a year. The Income Statement is usually based on an accrual basis – meaning that the revenues and expenses are recorded when incurred, not necessarily paid. In general an Income Statement is meant to show the performance of a company. i.e. higher Net Income generally means better performance (all else being equal).</span></p> <p><span>The statement of owner’s equity shows movements in the Owner’s Equity component of the Balance Sheet. You can use a <a href="http://www.videofinancialmodelling.com/corkscrew-accounts-what-the/" title="Corkscrew Accounts" target="_blank">corkscrew</a> account to calculate the movements from the start of the period to the end of the period. In general these movements are usually attributable to: </span></p> <ul type="disc" style="margin-top: 0in;"> <li><span>total comprehensive income;</span></li> <li><span>owners' investments;</span></li> <li><span>dividends;</span></li> <li><span>owners' withdrawals of capital; and</span></li> <li><span>treasury share transactions.</span></li> </ul> <p><span>The Cash Flow Statement represents the flow of cash in and out of the business. The statement is usually divided into three categories:</span></p> <p style=""><span>1)<span style="font: 7.0pt Times New Roman;"> </span></span><span>operating activities;</span></p> <p style=""><span>2)<span style="font: 7.0pt Times New Roman;"> </span></span><span>investing activities; and</span></p> <p style=""><span>3)<span style="font: 7.0pt Times New Roman;"> </span></span><span>financing activities. </span></p> <p><span>If you liked this article, check out the <a href="http://www.videofinancialmodelling.com/financial-modelling-training/" title="Financial Modelling Training" target="_blank">financial modelling training</a> or <a href="http://www.videofinancialmodelling.com/products/#inter" title="Financial Modelling Self-Study Products" target="_blank">products</a> at <a href="http://www.videofinancialmodelling.com/" title="Video Financial Modelling" target="_blank">Video Financial Modelling</a>. </span></p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-38260187483131281712011-12-05T14:16:00.001-08:002011-12-05T14:16:54.075-08:00Working Capital - Debtors and Creditors<div class='posterous_autopost'><p> <p>If you've been working or studying in finance you have probably heard of working capital before. If not then you might be wondering what the flip we are talking about. Well working capital is usually defined as current assets less current liabilities. If working capital is positive, the company has enough current assets to meet its current liabilities. If not then the company may have short term liquidity problems. </p> <p>In this blog tutorial we are going to look at two components of working capital, debtors or accounts receivable and creditors or accounts payable. </p> <p><strong><span style="font-size: medium;">What are Debtors and Creditors?</span></strong></p> <p>Ok, let's first start with Debtors. Debtors are current assets which arise when revenues are accrued but not paid. For example you book a sale of $100 on account, however you receive the cash 30 days from that date. The two entries you would make are:</p> <ol> <li>when you book the sale on account - debit debtors/accounts receivable (Balance Sheet item) and credit revenue (P&L item)</li> <li>when you receive the cash - debit cash (Balance Sheet item) and credit debtors/accounts receivable (Balance Sheet item reversing the account entry in (1))</li> </ol> <p>Creditors are very similar to the above. </p> <p><strong><span style="font-size: medium;">Modelling Debtors and Creditors</span></strong> </p> <p>Ok, so you should have a good feeling of how this works now, so let's look modelling this. </p> <p>In 99% of the cases we can calculate debtor and creditor balances using the following formulae:</p> <p><span style=""> </span><strong><em>Debtor Balance = Revenue Accrual x Debtor Days/Days in Period</em></strong></p> <p><span style=""> </span><strong><em>Creditor Balance = Expense Accrual x Creditor Days/Days in Period</em></strong></p> <p>For a yearly timescale the Days in Period would be equal to 365 days (we'll ignore leap years). </p> <p>From here you can find the total cash received or paid from revenue and expenses respectively. Let's look at this from a debtor perspective and utilising a corkscrew account (if you don't know what this is see our <a href="http://www.videofinancialmodelling.com/corkscrew-accounts-what-the/" title="Corkscrew Accounts blog tutorial" target="_blank">Corkscrew Account. What the?</a> blog).</p> <p><strong>Debtor Account</strong></p> <p>Opening Balance 50</p> <p>Add: Revenue Accrual 50 </p> <p><span style="text-decoration: underline;">Less: Cash Received [x]</span></p> <p>Closing Balance [y]</p> <p>Firstly let's find y. If we are looking at a year timescale and 30 day debtor days, then our closing balance (y) would be 50 x 30/365 = 4.1. </p> <p>Now we need to find x. Rearranging the formula we get: </p> <p>Cash Received (x) = Opening Balance + Revenue Accrual - Closing Balance (y)</p> <p><span style=""> </span> = 50 +50 - 4.1</p> <p><span style=""> </span> = 95.9<span style=""> </span></p> <p>Ok, that might be a bit of brain dump.... so let's look at some examples. </p> <p><span style="font-size: medium;"><strong>Debtor and Creditor Examples</strong></span></p> <p>You can follow along with the examples by downloading the Working Capital - Debtors and Creditors <a href="http://youtu.be/xpcCli2902I" title="Working Capital - Debtors and Creditors YouTube video" target="_blank">YouTube</a> video and the <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/12/WorkingCapital-DebtorsandCreditors.xlsx" title="Working Capital - Debtors and Creditors Excel spreadsheet" target="_blank">Excel spreadsheet</a>. </p> <p><em><strong>Example 1</strong></em></p> <p>You forecast $2,000,000 of sales on credit each year from 2011-2015. Your credit terms are 30 days. What is the size of your debtor/accounts receivable in each of 2011-2015?</p> <p>Ok, firstly and as always you need to put in a timescale as below:</p> <p><div class='p_embed p_image_embed'> <a href="http://getfile9.posterous.com/getfile/files.posterous.com/temp-2011-12-05/zgkdrGhCskiuiwIICJwtdcycexCFxDjDkbByelxwtteonIykkBbrwvdBgAtG/Picture1.png.scaled1000.png"><img alt="Picture1" height="29" src="http://getfile3.posterous.com/getfile/files.posterous.com/temp-2011-12-05/zgkdrGhCskiuiwIICJwtdcycexCFxDjDkbByelxwtteonIykkBbrwvdBgAtG/Picture1.png.scaled500.png" width="500" /></a> </div> </p> <p>Next let's put in the Revenue - Accrual. </p> <p><div class='p_embed p_image_embed'> <a href="http://getfile8.posterous.com/getfile/files.posterous.com/temp-2011-12-05/tliDFhqdjIibHJuoCrikEHmcbGrowsFIEdvxesrEFJqFgrEFpwAwroDyptkw/Picture2.png.scaled1000.png"><img alt="Picture2" height="60" src="http://getfile7.posterous.com/getfile/files.posterous.com/temp-2011-12-05/tliDFhqdjIibHJuoCrikEHmcbGrowsFIEdvxesrEFJqFgrEFpwAwroDyptkw/Picture2.png.scaled500.png" width="500" /></a> </div> </p> <p>Now using the formula Revenue Accrual x 30/365 calculate the debtor balance.</p> <p><div class='p_embed p_image_embed'> <a href="http://getfile6.posterous.com/getfile/files.posterous.com/temp-2011-12-05/aBBguwulczdHdtCGahzzItflImFbAlvtGcnqnCIfDatAbyyzyerxhndoFukv/Picture3.png.scaled1000.png"><img alt="Picture3" height="215" src="http://getfile8.posterous.com/getfile/files.posterous.com/temp-2011-12-05/aBBguwulczdHdtCGahzzItflImFbAlvtGcnqnCIfDatAbyyzyerxhndoFukv/Picture3.png.scaled500.png" width="500" /></a> </div> </p> <p>Now you could calculate the cash received, but we will put in one more step. We will find the movements in debtors (i.e. debtor opening balance - debtor closing balance).</p> <p><div class='p_embed p_image_embed'> <a href="http://getfile3.posterous.com/getfile/files.posterous.com/temp-2011-12-05/GpiFGsayocsvleDcsmnIwrrBahydpHczncucAqqtuskzgxdvahgiHkxjItnI/Picture4.png.scaled1000.png"><img alt="Picture4" height="131" src="http://getfile0.posterous.com/getfile/files.posterous.com/temp-2011-12-05/GpiFGsayocsvleDcsmnIwrrBahydpHczncucAqqtuskzgxdvahgiHkxjItnI/Picture4.png.scaled500.png" width="500" /></a> </div> </p> <p>Now if we add the above debtor balance with the revenue - accruals we should get the cash received. </p> <p><div class='p_embed p_image_embed'> <a href="http://getfile7.posterous.com/getfile/files.posterous.com/temp-2011-12-05/HehAanDEGEsdBDrysHBpnjgIdyfJuGxdvlscrFkqdHDfiExqDAxIrlrHfwdg/Picture5.png.scaled1000.png"><img alt="Picture5" height="154" src="http://getfile6.posterous.com/getfile/files.posterous.com/temp-2011-12-05/HehAanDEGEsdBDrysHBpnjgIdyfJuGxdvlscrFkqdHDfiExqDAxIrlrHfwdg/Picture5.png.scaled500.png" width="500" /></a> </div> </p> <p><em><strong>Example 2 - Homework</strong></em></p> <p> <p>Now we are not going to go through this example, but we run through the solution on <a href="http://youtu.be/xpcCli2902I" title="Working Capital - Debtors and Creditors YouTube" target="_blank">YouTube</a> and you can find the answers in the <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/12/WorkingCapital-DebtorsandCreditors.xlsx" title="Working Capital - Debtors and Creditors Excel spreadsheet" target="_blank">Excel spreadsheet</a>. So why don't you give the problem a go?</p> </p> <p><strong><em>Question:</em></strong> You buy services worth $1,000 every year from 2008 to 2011. You have credit terms of 90 days and you start with a credit balance of $250 at the start of 2008. What is your creditor/account payable balance at the end of 2011?</p> <p>Take your Excel and financial modelling skills to the next level. Try our <a href="http://www.videofinancialmodelling.com/products/" title="Excel and Financial Modelling Training Courses" target="_blank">Excel and Financial Modelling training courses</a> today. </p> <p> </p> <p> </p> <p> </p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-24165710757654260302011-11-19T05:35:00.001-08:002011-11-19T05:35:09.037-08:00Financial Modelling - Sensitivity and Scenario Analysis<div class='posterous_autopost'><div>If a financial model is setup using best practice modelling techniques then putting sensitivities and scenarios should be simple. What is the difference between a sensitivity and scenario you may be asking. Well a sensitivity only flexes of one input, whilst a scenario flexes multiple inputs.</div> <p /> <div><span style="font-size: medium;">About the Inputs/Assumptions</span></div> <p /> <div>At Video Financial Modelling we usually have one assumption sheet for a simple financial model and two assumptions sheets for complex financial models. The two assumptions sheets for a complex financial model are:</div> <div>1) A static assumptions sheet – inputs that don’t change over time. For example a scheduled construction end date.</div> <div>2) A time-series assumptions sheet – inputs that can change over time. For example inflation indicies.</div> <p /> <div><span style="font-size: medium;">Setting up a sensitivity or scenario analysis</span></div> <p /> <div>Below are brief instructions to setup a sensitivity or scenario analysis manager. </div> <p /> <div><strong>Step 1:</strong> The first step in setting up a sensitivity or scenario analysis is to incorporate, what we like to call a scenario selector. The scenario selector is simply a number between 1 and the “maximum number of scenarios you want to run”. For example say you want to run ten sensitivities/scenarios, then the maximum number you’d put in your scenario selector would be ten.</div> <p /> <div>A simple scenario selector would just look like this.</div> <p /> <div><div class='p_embed p_image_embed'> <a href="http://getfile4.posterous.com/getfile/files.posterous.com/temp-2011-11-19/GbFwkHAAidHdoiDuoDHoGrreDhpyikHdmqAgqDkhgBHtsklapolAGHhrsjue/Picture1.png.scaled1000.png"><img alt="Picture1" height="33" src="http://getfile0.posterous.com/getfile/files.posterous.com/temp-2011-11-19/GbFwkHAAidHdoiDuoDHoGrreDhpyikHdmqAgqDkhgBHtsklapolAGHhrsjue/Picture1.png.scaled500.png" width="500" /></a> </div> </div> <p /> <div>Not much to it is there? Bear with us though. </div> <p /> <div><strong>Step 2: </strong>Now that you’ve got a scenario selector let’s move onto incorporating a simple offset formula. The basis of this formula is to grab the relevant data from the selected scenario. The form of the formula we usually use for this is:</div> <div style="padding-left: 30px;"><em>= offset(current cell reference, ,scenario selector) for static pages</em></div> <div style="padding-left: 30px;"><em>= offset(current cell reference, scenario selector,) for time-series pages</em></div> <p /> <div><div class='p_embed p_image_embed'> <a href="http://getfile8.posterous.com/getfile/files.posterous.com/temp-2011-11-19/kqenhAvdyhinnydsmbEuszBsvrFthwvxbrAenqlyvJbvqgoJaAshtfnpFEsE/Picture2.png.scaled1000.png"><img alt="Picture2" height="162" src="http://getfile1.posterous.com/getfile/files.posterous.com/temp-2011-11-19/kqenhAvdyhinnydsmbEuszBsvrFthwvxbrAenqlyvJbvqgoJaAshtfnpFEsE/Picture2.png.scaled500.png" width="500" /></a> </div> </div> <p /> <div>Obviously if you have rows/columns in between the actual assumption you use in your model and your input, then you may need to adjust the above formula.</div> <p /> <div><strong>Step 3: </strong>So what happens if you want to use scenario 1’s input if you don’t have any data in the selected scenario? Well that is easy, we just add an IF statement in as shown below.</div> <div style="padding-left: 30px;"><em>=if(offset(current cell reference, ,scenario selector)=””,scenario 1 cell, offset(current cell reference, ,scenario selector)) – for a static sheet.</em></div> <p /> <div><div class='p_embed p_image_embed'> <a href="http://getfile9.posterous.com/getfile/files.posterous.com/temp-2011-11-19/yjuskfpuDDzjzbBCijyGEjffphsonmltudeCtrtgtcwgushmwifrgwnmnsns/Picture3.png.scaled1000.png"><img alt="Picture3" height="160" src="http://getfile0.posterous.com/getfile/files.posterous.com/temp-2011-11-19/yjuskfpuDDzjzbBCijyGEjffphsonmltudeCtrtgtcwgushmwifrgwnmnsns/Picture3.png.scaled500.png" width="500" /></a> </div> </div> <p /> <div>Obviously it is nearly the same for a time-series sheet.</div> <p /> <div>The logic behind this is that we don’t have to put all inputs in for every different scenario we do. We just put in the inputs we want to change from the scenario 1 (usually called the base case).</div> <p /> <div><span style="font-size: medium;">Sensitivity and Scenario Example</span></div> <p /> <div>You can follow along with the below example by downloading the <a href="http://youtu.be/1vfZxF-GXOg" title="Financial Modelling - Sensitivity and Scenario Analysis" target="_blank">YouTube</a> video and <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/11/SensitivityandScenarioAnalysis.xlsx" title="Financial Modelling - Sensitivity and Scenario Analysis" target="_blank">spreadsheet</a>.</div> <p /> <div>Ok, this might be confusing right now, but once we look at the below example our methodology should become apparent.</div> <p /> <div>Say we had the following inputs and we wanted to find the NPV of equity distributions. Let's assume there are no taxes and our profit is fully distributed to shareholders as there is no debt in the company. </div> <div><div class='p_embed p_image_embed'> <a href="http://getfile2.posterous.com/getfile/files.posterous.com/temp-2011-11-19/appGqgawoclhmgzsIhgjDEHzuEwxhChFCBvFacntlpHcFBpiGFagxdAnhcHD/Picture4.png.scaled1000.png"><img alt="Picture4" height="73" src="http://getfile4.posterous.com/getfile/files.posterous.com/temp-2011-11-19/appGqgawoclhmgzsIhgjDEHzuEwxhChFCBvFacntlpHcFBpiGFagxdAnhcHD/Picture4.png.scaled500.png" width="500" /></a> </div> </div> <p /> <div>Now let’s put in a scenario selector (Step 1 above). Obviously this scenario selector will not work on its own, so let’s now put in a an offset formula as per Step 2 above. The result is shown below. Now all our inputs will be moved to the 1st scenario and the formula will be placed where we previously had inputs.</div> <p /> <div><div class='p_embed p_image_embed'> <a href="http://getfile5.posterous.com/getfile/files.posterous.com/temp-2011-11-19/gGysHutnpgAxDmimodiudlbGrsIeeqAydsDboEAwdqdnzoypFwpBEveGFeGg/Picture5.png.scaled1000.png"><img alt="Picture5" height="190" src="http://getfile9.posterous.com/getfile/files.posterous.com/temp-2011-11-19/gGysHutnpgAxDmimodiudlbGrsIeeqAydsDboEAwdqdnzoypFwpBEveGFeGg/Picture5.png.scaled500.png" width="500" /></a> </div> </div> <p /> <div>Ok let’s now run a scenario, say scenario 2. Let’s put in rev of 55 and ops of 40. What happens? Because we don’t have inflation (and haven’t incorporated Step 3 above) in we won’t have any inflation.</div> <div><div class='p_embed p_image_embed'> <a href="http://getfile4.posterous.com/getfile/files.posterous.com/temp-2011-11-19/FdljIxrtEzpjeaHtyfsmgwmHieiJAurgxAxJpsgHwxqJiHDoAyGfwiaxaDac/Picture6.png.scaled1000.png"><img alt="Picture6" height="262" src="http://getfile0.posterous.com/getfile/files.posterous.com/temp-2011-11-19/FdljIxrtEzpjeaHtyfsmgwmHieiJAurgxAxJpsgHwxqJiHDoAyGfwiaxaDac/Picture6.png.scaled500.png" width="500" /></a> </div> </div> <p /> <div>Let’s change the formula as per Step 3 above to get:</div> <p /> <div><div class='p_embed p_image_embed'> <a href="http://getfile5.posterous.com/getfile/files.posterous.com/temp-2011-11-19/tqyHGlnpDuxFsjoGEJbGGtgAmHqBvmpGjwExipyjCjmDdkDDhaHlCByrurtu/Picture7.png.scaled1000.png"><img alt="Picture7" height="299" src="http://getfile0.posterous.com/getfile/files.posterous.com/temp-2011-11-19/tqyHGlnpDuxFsjoGEJbGGtgAmHqBvmpGjwExipyjCjmDdkDDhaHlCByrurtu/Picture7.png.scaled500.png" width="500" /></a> </div> </div> <p /> <div>Now we have an active scenario manager which we can change and run scenarios on. Putting it all together you should get NPV of equity distributions of 135 for the base case and 101 for scenario 2. See the below picture or download the spreadsheet to see the workings. </div> <p /> <div><div class='p_embed p_image_embed'> <a href="http://getfile5.posterous.com/getfile/files.posterous.com/temp-2011-11-19/GIGwoGgHgblJsofpvipcvpgfdqzAuswmowcDIfberaJeHmBmoHltIdtsJjpg/Picture8.png.scaled1000.png"><img alt="Picture8" height="191" src="http://getfile9.posterous.com/getfile/files.posterous.com/temp-2011-11-19/GIGwoGgHgblJsofpvipcvpgfdqzAuswmowcDIfberaJeHmBmoHltIdtsJjpg/Picture8.png.scaled500.png" width="500" /></a> </div> </div> <p /> <div>Now for all of you out there who think you’ve got it why don’t you try this one. Find the NPV of the equity distributions if the inflation is 2.5% for the first five years and 7% for the next five years. Hint: Your scenarios will need to go down the page as this is now a time-series assumption (i.e. changes over time). The NPV of the equity distributions you should come out to be 137. </div> <p /> <div>If you liked this tutorial, try one of our <a href="http://www.videofinancialmodelling.com/products/" title="Video Financial Modelling training courses" target="_blank">Financial Modelling or Excel training courses</a> today. </div></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-19292394797787417452011-11-14T09:55:00.001-08:002011-11-14T09:55:35.178-08:00MOD Function - Bridging the Time Gap in Excel<div class='posterous_autopost'><p> <p>Say you had quarterly data and you want to collect this data onto a yearly page? How would you go about it? Probably a hundred different ideas came to your head when I raised that question. Video Financial Modelling has seen all of these. One sticks out ahead of the rest, the MOD function. </p> <p><span style="font-size: medium;">Using the MOD function</span></p> <p>The MOD function is quite simply awesome. In simple terms it takes a number and a divisor and spits out a remainder. The equation is shown below.</p> <p><em><strong>= MOD(number,divisor)</strong></em></p> <p>Does that bring back memories of school maths? Well for all of those people who can't remember, here is a couple of examples to help the mental juices:</p> <ul> <li>mod(11,3) - 11/3 is 3, with a remainder of 2. So MOD gives us 2. </li> <li>mod(12,3) - yes you guessed it. 0.</li> <li>mod(-11,3) - ok so this is getting a bit harder. The answer is 1. Why? Well because the MOD function gives the amount by which a number exceeds the largest integer multiple of the divisor that is not greater than that number. In this case the largest integer multiple that doesn't exceed -11 is -12. Still confused. Have a play around with the function. </li> </ul> <p><span style="font-size: medium;">MOD function Example</span></p> <p>Ok, so I promised you that I would show you how to grab data from a quarterly page and aggregate it on a yearly page so let's get started. Download the associated <a href="http://youtu.be/xbP8rZ-mJWs" title="MOD Function - YouTube Video" target="_blank">YouTube</a> video and <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/11/MODFunction-BridgingtheTimeGap.xlsx" title="MOD Function Excel Spreadsheet " target="_blank">Excel spreadsheet</a>. </p> <p>We have the following quarterly dates and associated revenue numbers. </p> <p><div class='p_embed p_image_embed'> <a href="http://getfile0.posterous.com/getfile/files.posterous.com/temp-2011-11-14/jBkwjpFllkocEwgbsyJnpezJHtghlBpGqFgqfajpzrFybIuFqxnjscoijDqA/figure1.png"><img alt="Figure1" height="100.234925606891" src="http://getfile0.posterous.com/getfile/files.posterous.com/temp-2011-11-14/jBkwjpFllkocEwgbsyJnpezJHtghlBpGqFgqfajpzrFybIuFqxnjscoijDqA/figure1.png" width="500" /></a> </div> </p> <p>We want to aggregate these numbers on an annual page. Let's firstly put in annual dates in. You can put these on a seperate page or keep it simple and have it on the same page. This is shown below.</p> <p><div class='p_embed p_image_embed'> <a href="http://getfile8.posterous.com/getfile/files.posterous.com/temp-2011-11-14/CgumoCjinFdqAgsirJyhqDJHbFfuqnkEoFmdtlrJqfueJehFzdlGyywxlach/figure2.png"><img alt="Figure2" height="65.9203980099502" src="http://getfile8.posterous.com/getfile/files.posterous.com/temp-2011-11-14/CgumoCjinFdqAgsirJyhqDJHbFfuqnkEoFmdtlrJqfueJehFzdlGyywxlach/figure2.png" width="500" /></a> </div> </p> <p>Now the magic. Below the quartlery dates, let's put in the following formula. Don't worry we will explain the components soon. </p> <p><strong><em>= EOMONTH(quarter end date, MOD(month(first period ending date)-month(quarter end date),12)</em></strong></p> <p>This should look something like this:</p> <p><div class='p_embed p_image_embed'> <a href="http://getfile1.posterous.com/getfile/files.posterous.com/temp-2011-11-14/GrydnCHsmABxgexjHzHfBqycHGJkiCcbezsDrtdbaFFIknAqpoqEueyDCfad/figure3.png.scaled1000.png"><img alt="Figure3" height="242" src="http://getfile2.posterous.com/getfile/files.posterous.com/temp-2011-11-14/GrydnCHsmABxgexjHzHfBqycHGJkiCcbezsDrtdbaFFIknAqpoqEueyDCfad/figure3.png.scaled500.png" width="500" /></a> </div> </p> <p>Copy this fomrula across. Wow magic... hopefully this formula gives you the year ending for each of the dates? It sure does, now how does it work. Let's break it down:</p> <p><em><strong>= EOMONTH(Component 1,Component 2)</strong></em></p> <ul> <li>Component 1: quarter end date</li> <li>Component 2: MOD(month(first period ending date)-month(quarter end date),12)</li> </ul> <p>The first component should be pretty self explanatory, so let's look at the second component by looking at some brief examples.</p> <p>Ok given that the first period ending date is always 31 Dec 2012, the month(first period ending date) will always equal 12. Simplifying the formula we get:</p> <p><strong><em>= MOD(12-month(quarter end date),12)</em></strong></p> <p>There are 4 possible options for the month(quarter end date). 3, 6, 9 and 12. i.e. corresponding to March, June, September and December. Let's look at each in turn.</p> <ul> <li>March - MOD(12-3,12)=9 </li> <li>June - MOD(12-6,12)=6</li> <li>Sep - MOD(12-9,12)=3</li> <li>Dec - MOD(12-12,12)=0</li> </ul> <p>If you're switched on you should see a pattern above. The result is equivalent to the number of months to the year end. So if we combine this with an EOMONTH formula we will get to the year end. </p> <p>Now if we do a sumif formula (as below) we can aggregate the revenue numbers into yearly results.<div class='p_embed p_image_embed'> <a href="http://getfile2.posterous.com/getfile/files.posterous.com/temp-2011-11-14/DfemgnbFwwevJAHnlpsbcrgthDaBFcsCzhDaIuxoqkzjzcyurodoEfntDgbI/figure4.png.scaled1000.png"><img alt="Figure4" height="303" src="http://getfile9.posterous.com/getfile/files.posterous.com/temp-2011-11-14/DfemgnbFwwevJAHnlpsbcrgthDaBFcsCzhDaIuxoqkzjzcyurodoEfntDgbI/figure4.png.scaled500.png" width="500" /></a> </div> </p> <p>We should get the following:</p> <p><div class='p_embed p_image_embed'> <a href="http://getfile7.posterous.com/getfile/files.posterous.com/temp-2011-11-14/mudetjjHfIfFqAbEGqDpfcfepCwEquxFnEBvJCtGIHboHuvtHqutgyvGmzfs/figure5.png"><img alt="Figure5" height="114.575214341387" src="http://getfile7.posterous.com/getfile/files.posterous.com/temp-2011-11-14/mudetjjHfIfFqAbEGqDpfcfepCwEquxFnEBvJCtGIHboHuvtHqutgyvGmzfs/figure5.png" width="500" /></a> </div> </p> <p>Now you may be wondering why we didn't just use a Year formula and then a sumif. Well that is definitely another option, but say you now had a monthly construction page and you want to aggregate or lookup this data in a quarterly page. Trust me this happens all the time in project finance deals. Well the answer.... use the above methodology and you can't go wrong. </p> <p>If you liked this blog tutorial check out our new advanced training course, <a href="http://www.videofinancialmodelling.com/products/#adv" title="Advanced Toll Road Model Training Course" target="_self">Advanced Toll Road Model Training Course</a>. </p> <p>For more free blog tutorials go to <a href="http://www.videofinancialmodelling.com/blog" title="Video Financial Modelling's Blog" target="_self">Video Financial Modelling's Blog</a>.</p> <p> </p> <p> </p> <p> </p> <p> </p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-54927168487107107362011-10-07T13:36:00.001-07:002011-10-07T13:36:25.849-07:00Project Finance - Interest during Construction and Funding Requirements<div class='posterous_autopost'><p><strong>Introduction</strong></p> <p>Greenfield (think of new build) project finance deals consist of both construction and operations period. In this blog tutorial we will focus on the construction phase of a greenfield project and find out how to calculate interest during construction, funding requirements and ultimately our required debt and equity drawdowns. </p> <p><strong>Required funding during construction</strong></p> <p>You may be wondering what project costs may be incurred during a typical project finance construction phase. Here are a few of the most common costs:</p> <ul> <li><strong>Construction costs</strong> - amounts which are usually paid from the Concessionaire (SPV) to the EPC contractor</li> <li><strong>SPC costs during construction</strong> - administration costs, including management staff'ssalary, advisory costs, ongoing insurances etc.</li> <li><strong>Upfront bid costs</strong> - cost incurred prior to financial close and to be recovered at financial close - advisory costs, consultants, insurances </li> <li><strong>Financing costs</strong> - arrangement fees, commitment fees, interest costs, equity yield (often in the form of sub-debt payments), Debt Service Reserve Funding</li> </ul> <p><strong>But how do we fund these costs?</strong></p> <p>As the majority of project finance deals have no revenue during construction, construction funding is required. </p> <p>In the most basic form funding for the above costs comes in the form of both debt and equity. The above costs are aggregated together usually on a monthly basis, with drawdowns on financing facilities assumed to occur at the end of each month period. </p> <p>Assuming that the drawdowns occur at the end of the month eliminates any circular reference related to interest during construction. There are however other gremlins (otherwise known as circularities) that we need to look out for such as commitment fees and arrangement fees. We can break these circularities quite easily, but we will save that topic for another day. </p> <p><strong>Putting it all together- an example</strong></p> <p>Download the <a href="http://youtu.be/Oh2loQkDlt8" title="Project Finance - Interest during Construction YouTube" target="_self">youtube</a> video and <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/10/ProjectFinance-InterestduringConstruction1.xlsx" title="Project Finance - Interest during Construction " target="_blank">spreadsheet</a> to follow along. </p> <p>Ok, so we now know where our funding requirement is coming from and how it is funded. </p> <p>Now let's take a look at an example. Say we had the following assumptions:</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/tbvddBjIvhDDInurIzrgHftdifqaisobDzAEmdkEbfGojjkBrzxraBrJHAet/picture1.png.scaled1000.png"><img alt="Picture1" height="116" src="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/tbvddBjIvhDDInurIzrgHftdifqaisobDzAEmdkEbfGojjkBrzxraBrJHAet/picture1.png.scaled500.png" width="500" /></a> </div> </p> <p>Note: Let's also assumes there are no commitment fees or arrangement fees (i.e. we'll leave out the gremlins). </p> <p>Firstly let's work out our funding requirements. To do this we need to add the construction costs, SPV costs, upfront bid costs and the financing costs together. </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/GhemGdwnnhxgataEChtvpnaJqvCnBuyrAFHGBrtDpukkigssabklDubtBFCd/picture2.png.scaled1000.png"><img alt="Picture2" height="95" src="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/GhemGdwnnhxgataEChtvpnaJqvCnBuyrAFHGBrtDpukkigssabklDubtBFCd/picture2.png.scaled500.png" width="500" /></a> </div> </p> <p>You'll note that we've missed out the financing costs for the moment. </p> <p>Now let's setup a corkscrew account for the debt as shown below. Remember in a corkscrew account the opening balance in the current period is equal to the closing balance for the previous period. Because we have a gearing of 80%, the debt drawdown will simply be 80% x the funding requirement. </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/xDhIlmkjgDozhhhdIdHJDeGfCFkwxqfvzGrxfmhvbHgaqdypGcypbsbtofge/picture3.png.scaled1000.png"><img alt="Picture3" height="108" src="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/xDhIlmkjgDozhhhdIdHJDeGfCFkwxqfvzGrxfmhvbHgaqdypGcypbsbtofge/picture3.png.scaled500.png" width="500" /></a> </div> </p> <p>Given the debt balances we can now calculate the debt interest payments for each month. This will simply be 6% x opening balance x 1/12 (remember we are dealing in months). </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/nnjcqrjxhDjkeppEErhmnrppBzhnItIyFjIskzEvlguwksuFssdyauGrJGxb/picture4.png.scaled1000.png"><img alt="Picture4" height="151" src="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/nnjcqrjxhDjkeppEErhmnrppBzhnItIyFjIskzEvlguwksuFssdyauGrJGxb/picture4.png.scaled500.png" width="500" /></a> </div> </p> <p>We can now feed this interest cost back in the funding requirement. </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/cCCnqhcamhexJDJuaqmsdlvtoglAHkmoyFGdBeBGnCmiqkHnihFpdHEbyimj/picture5.png.scaled1000.png"><img alt="Picture5" height="172" src="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/cCCnqhcamhexJDJuaqmsdlvtoglAHkmoyFGdBeBGnCmiqkHnihFpdHEbyimj/picture5.png.scaled500.png" width="500" /></a> </div> </p> <p>The equity capital injected is then simply equal to (1-gearing) x funding requirement or equivalently the funding requirement minus debt.</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/rjcrnfugslCdulejuxIJnJFlmxyemCbEajlBfufEjdkbkxFfHGBClgvjcAse/picture6.png.scaled1000.png"><img alt="Picture6" height="147" src="http://posterous.com/getfile/files.posterous.com/temp-2011-10-07/rjcrnfugslCdulejuxIJnJFlmxyemCbEajlBfufEjdkbkxFfHGBClgvjcAse/picture6.png.scaled500.png" width="500" /></a> </div> </p> <p>If all has went smoothly you should end up with a debt amount of 121 and an equity amount of 30. </p> <p><strong>A quick side note</strong></p> <p>In most instances, during construction there will be no free cashflow left over after utilising debt and equity. i.e. funding requirements should exactly offset debt and equity drawdowns.</p> <p>If you like this tutorial you you'll love our <a href="http://www.videofinancialmodelling.com/products" title="Financial Modelling Training" target="_blank">financial modelling and excel training courses</a>. Want more free blog tutorials? Check out <a href="http://www.videofinancialmodelling.com" title="Video Financial Modelling's blog" target="_blank">Video Financial Modelling's blog</a>. </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com2tag:blogger.com,1999:blog-2142951443737088035.post-61854262627670787212011-09-28T12:32:00.001-07:002011-09-28T12:32:40.412-07:00Free Bonus Excel Shortcuts<div class='posterous_autopost'><p> <p><strong><span style="font-size: medium;">Free Bonus Excel Shortcuts</span></strong></p> <p><strong>Blog Downloads:</strong> <a href="http://youtu.be/NSjMZ6zDBFw" title="Bonus Excel Shortcuts - YouTube" target="_blank">Youtube Video</a> <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/09/Free-Bonus-Excel-Shortcuts.xlsx" title="Spreadsheet - Bonus Excel Shortcuts" target="_blank">Excel Spreadsheet</a> <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/09/MacroStarter.xlsm" title="Shortcut Macro " target="_blank">Macrostarter</a></p> <p><em>Instructions for downloading your free shortcuts:</em></p> <ul> <li>download and copy the Macrostarter.xlsm to your desktop</li> <li>go to the windows button in the bottom left hand corner of your computer screen</li> <li>search for the folder XLSTART</li> <li>drag and drop or copy and paste the Macrostarter.xlsm file from your desktop into the XLSTART folder</li> </ul> <p>Now you're ready. Open up a blank Excel sheet, then open the file you want to work with. Your Macrostarter.xlsm file is hidden in the background. </p> <p><strong><span style="font-size: medium;">Cool Free Excel Shortcuts</span></strong></p> <p>So what is contained in the Macrostarter.xlsm file???? Well to give you a clue, we think that these are two of the coolest Excel shortcuts around....</p> <p>Ok, so maybe you won't be able to guess. </p> <p>Well they are....</p> <p><strong>Number formatting -</strong> puts a comma to separate thousands and also puts dashes for zeros - simply push CTRL+SHIFT+C on any number to apply</p> <p><strong>Date formatting - </strong>puts any numbers which are meant to be dates into the following format dd-mmm-yy. i.e. 31-Jan-11 - simply push CTRL+SHIFT+D</p> <p>These two shortcuts are sure to save you plenty of time formatting your Excel spreadsheet or financial model. </p> <p>Do you want to become a Excel shortcut pro? If yes, then check out our <a href="http://www.videofinancialmodelling.com/products/intermediate-tutorials/excel-shortcuts/" title="Excel Shortcuts" target="_blank">Excel Shortcuts</a> training course.</p> <p>Want other great Excel and financial modelling tutorials for free? Check out the <a href="http://www.videofinancialmodelling.com" title="Video Financial Modelling home page" target="_blank">Video Financial Modelling</a> home page. </p> <p> </p> <p> </p> <p> </p> <p> </p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-87325371683918740662011-09-20T11:42:00.001-07:002011-09-20T11:42:42.460-07:00Excel and Financial Modelling Training Courses<div class='posterous_autopost'><p> <p>For those of you who don’t know <a href="http://www.videofinancialmodelling.com" title="Excel and Financial Modelling Training Courses">Video Financial Modelling</a> has recently launched their face-to-face group Excel and financial modelling training in Dubai and London. </p> <p>This is an exciting time for the team and we hope it is equally exciting for you. If you or your business needs quality:</p> <p>-<span style=""> </span>Financial modelling training, or</p> <p>-<span style=""> </span>Excel training</p> <p>contact us for further details or a comprehensive course guide at <a href="mailto: training@videofinancialmodelling.com" title="Video Financial Modelling Training Email">training@videofinancialmodelling.com</a>. </p> <p>As part of our early birds promotion, individuals or businesses registering for the Excel or financial modelling training courses up to the end of October will receive a 20% discount. </p> <p>Take a look at our Excel and <a href="http://www.videofinancialmodelling.com/financial-modelling-training/" title="Financial Modeling Training" target="_blank">financial modelling training courses</a> today.</p> <p /> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-14351061292391703662011-09-10T05:20:00.001-07:002011-09-10T05:20:29.965-07:00Corkscrew accounts. Why they are used in financial modelling<div class='posterous_autopost'><p> <p> <p><strong>Introduction </strong></p> <p>Now if you have been following <a href="http://www.videofinancialmodelling.com" title="Video Financial Modelling" target="_self">Video Financial Modelling</a> for a while, you should know what a corkscrew account is. For those of you who haven’t been following us you’re about to find out how corkscrew accounts can help you to model everything from financing facilities, ledgers all the way through to depreciation accounts.</p> <p><strong>So what is a corkscrew account?</strong></p> <p>A corkscrew account is simply a method by which the opening balance of the current period is equal to the closing balance of the previous period. This is probably best illustrated by an example. </p> <p>Say we had a financing facility with a closing balance of $50 at the quarter end 31 Dec 2011. What would be the balance for the period starting 1 Jan 2012? If you guessed $50 then you’d be right. Now let’s ramp it up a notch. </p> <p>Carrying on from the above example say that you drew $30 from the quarter starting 1 Jan 2012 and ending 31 Mar 2012. What would be the closing balance of the facility if no amortisation or interest capitalisation took place? If you answered $80 ($50 for the opening balance and $30 for the drawdown) you’d be correct.</p> <p>Now imagine we had the $30 drawdown as per the last paragraph but we also had a repayment of $20 (Note: that it is very unusual to drawdown on a financing facility and amortise it at the same time). What would be the closing balance? It would be $50+$30-$20 = $60. This is represented as corkscrew account in figure 1 below.</p> <p><div class='p_embed p_image_embed'> <img alt="Figure1" height="135" src="http://posterous.com/getfile/files.posterous.com/temp-2011-09-10/InuiDqFBFeoJDoifHpvvfkAslyluqGcDBFvubjIhmliprgHnkyDaqJncvvlJ/Figure1.png.scaled500.png" width="263" /> </div> </p> <p><strong><em>Figure 1 – Example of a corkscrew account</em></strong></p> <p><strong>Keeping convention to make your life easy</strong></p> <p>As you can see in Figure 1, Video Financial Modelling has a simple convention when modelling corkscrew accounts. All additions are positive and all deductions are negative. Simple I know, but we have seen many different styles of doing this. Doesn’t it seem logical to have inflows as positive numbers and outflows as negative numbers? </p> <p><strong>Let’s look at an example</strong></p> <p>Given the below company net profits and dividends, find the retained earnings opening and closing balances for the period if the retained earnings balance at period 0 is zero.</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-09-10/CJwjzkfADJamqAvswsopjGqCrbalxwiCmDfvxwBrxEkuvurAuIBxokupAdpz/Figure2.png.scaled1000.png"><img alt="Figure2" height="45" src="http://posterous.com/getfile/files.posterous.com/temp-2011-09-10/CJwjzkfADJamqAvswsopjGqCrbalxwiCmDfvxwBrxEkuvurAuIBxokupAdpz/Figure2.png.scaled500.png" width="500" /></a> </div> </p> <p>Ok, so firstly we need to setup a corkscrew account. As with any good corkscrew you need to link the opening balance of the current period with the closing balance of the previous period. </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-09-10/mAybflmqzprEqxvmcFGoaBgjaJExylvGAvnrvubCGrtduECBwtmzHvcxBmuj/Figure3.png.scaled1000.png"><img alt="Figure3" height="123" src="http://posterous.com/getfile/files.posterous.com/temp-2011-09-10/mAybflmqzprEqxvmcFGoaBgjaJExylvGAvnrvubCGrtduECBwtmzHvcxBmuj/Figure3.png.scaled500.png" width="500" /></a> </div> </p> <p>Secondly you can find the closing balance by summing all the rows above. Don’t worry if there are no numbers for the net profits or dividends. They will come soon. The important thing is that you stick to convention and make sure that corkscrew outflows are negative numbers and corkscrew inflows are positive numbers. It is very hard to make a mistake if you follow this convention.</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-09-10/cdlHwAvEmIDreyyJvlclsCceqnClfetgDAvsulwbHzkgdHvHjamgrJxycwwC/Figure4.png.scaled1000.png"><img alt="Figure4" height="130" src="http://posterous.com/getfile/files.posterous.com/temp-2011-09-10/cdlHwAvEmIDreyyJvlclsCceqnClfetgDAvsulwbHzkgdHvHjamgrJxycwwC/Figure4.png.scaled500.png" width="500" /></a> </div> </p> <p>Finally add the net profits and positive numbers and the dividends as negative numbers. Remember the formula for Closing Retained Earnings?</p> <p>Closing Retained Earnings = Opening Retained Earnings + Net Profits - Dividends </p> <p>You should come out with the following where the period 10 closing balance is 93.</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-09-10/AdmCHvvxIIkiwwlHFlibJGrofbBntIDndeAFviBtBnGegIGassxDcvfsGeFh/Figure5.png.scaled1000.png"><img alt="Figure5" height="120" src="http://posterous.com/getfile/files.posterous.com/temp-2011-09-10/AdmCHvvxIIkiwwlHFlibJGrofbBntIDndeAFviBtBnGegIGassxDcvfsGeFh/Figure5.png.scaled500.png" width="500" /></a> </div> </p> <p><strong>What else can you apply this concept too?</strong></p> <p>As mentioned previously you can apply this to many different situations. A few examples come to mind including common ledgers, financing facilities, depreciation accounts. The list goes on and on. </p> </p> <p>Like this article? Check out the <a href="http://www.videofinancialmodelling.com/products/" title="Financial Modelling Training" target="_self">Excel and Financial Modelling Training Courses</a> at <a href="http://www.videofinancialmodelling.com" title="Video Financial Modelling" target="_self">Video Financial Modelling</a>.</p> <p> </p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-7365740431078862442011-08-15T14:01:00.001-07:002011-08-15T14:01:12.507-07:00CFADS and DSCR - Sculpting<div class='posterous_autopost'><p> <p>OK, so you have grasped the project finance definition of CFADS and DSCR from our previous blog tutorial, <a href="http://www.videofinancialmodelling.com/cfads-and-dscr-words-from-a-foreign-language/" title="CFADS and DSCR - Words from a Foreign Language" target="_blank">CFADS and DSCR - Words from a Foreign Language?</a> But what can we do with these concepts? </p> <p>Besides calculating the DSCR, for the purposes of meeting loan documentation covenants such as the lock-up DSCR or default DSCR, we can also use a target DSCR to sculpt debt repayments. </p> <p>This is probably best illustrated by example. Say we had the following CFADS for Company A over a five year period.</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/dcymeCDwcbpqCwetDffpGqvdJcFDkEkzykIsqIJjjgxhAhechCmGmoiFoBdt/Figure_0.png.scaled1000.png"><img alt="Figure_0" height="38" src="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/dcymeCDwcbpqCwetDffpGqvdJcFDkEkzykIsqIJjjgxhAhechCmGmoiFoBdt/Figure_0.png.scaled500.png" width="500" /></a> </div> </p> <p>To follow along with the examples by downloading the <a href="http://youtu.be/g0FBeo70x0s" title="CFADS and DSCR - Sculpting YouTube Video" target="_blank">YouTube</a> video and the <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/08/CFADS-and-DSCR-Sculpting.xlsx" title="CFADS and DSCR - Sculpting Spreadsheet" target="_blank">Excel spreadsheet</a>. </p> <p>We would like to know how much project finance debt we could raise for Company A in each of the following three scenarios.</p> <ol> <li>Using a credit foncier repayment profile (don’t worry we’ll talk you through the concept in a second)</li> <li>Using a target DSCR of 1.30x</li> <li>Using a target DSCR of 1.10x</li> </ol> <p>For each of the cases we have assumed that interest on the debt is at 6% and all debt is repaid by the end of year 5. Also assume that Company A is currently all equity funded. </p> <p><strong>Case 1 – Credit Foncier Profile</strong></p> <p>You can think of a credit foncier repayment profile like a common fixed rate household mortgage. The key here is that you pay an amount which is constant each period (usually a month) to the bank. </p> <p>Ok, great you say, but how much debt can we insert into Company A if we are using a credit foncier repayment profile? One way we could find this value is by trial and error. Let’s proceed this way so that you can get a feel for the process.</p> <p><strong>Step 1:</strong> Let’s setup a corkscrew account, by utilising the fact that the closing balance in the previous period equals the opening balance in the current period. You can see this in Figure 1.</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/gikqqyxryDkGzEeefmsjHqjJnmfIAJaBwcerxvftHjcndqosGtDhddiuzkfj/Figure_1.png.scaled1000.png"><img alt="Figure_1" height="212" src="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/gikqqyxryDkGzEeefmsjHqjJnmfIAJaBwcerxvftHjcndqosGtDhddiuzkfj/Figure_1.png.scaled500.png" width="500" /></a> </div> </p> <p><strong><em>Figure 1 – Corkscrew Account</em></strong></p> <p><strong>Step 2:</strong> Let’s put a plug figure of 5 into the cell highlighted in Figure 1. See Figure 2 for the result. </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/IgsJAarswwlowuyophJcodGfCpxxmzEnnbkwDfamkBCIEtmCxnvvkhsFtpvp/Figure_2.png.scaled1000.png"><img alt="Figure_2" height="215" src="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/IgsJAarswwlowuyophJcodGfCpxxmzEnnbkwDfamkBCIEtmCxnvvkhsFtpvp/Figure_2.png.scaled500.png" width="500" /></a> </div> </p> <p><strong><em>Figure 2 – Corkscrew Account with plug figure</em></strong></p> <p><strong>Step 3:</strong> Now let’s calculate the interest and principal repayments for the plugged debt amount. Interest is straight forward and can be calculated by:</p> <p style="padding-left: 30px;"><strong>= Interest Rate x Opening Debt Balance</strong></p> <p>The principal repayments are calculated using an Excel function called the PPMT. The function for our purposes is:</p> <p style="padding-left: 30px;"> <strong>= PPMT(rate, per, nper, pv)</strong> where</p> <p style="padding-left: 60px;"><em>rate is the interest rate in this case 6% pa</em></p> <p style="padding-left: 60px;"><em>per is a number between 1 and nper, in this case 1</em></p> <p style="padding-left: 60px;"><em>nper is the number of periods remaining</em></p> <p style="padding-left: 60px;"><em>pv is the opening balance of the account</em></p> <p>You can see the PPMT formula in action below.</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/zGpweaaIevBFupiAwanerqFtGjGIixCEkCsvEzlzrnpmidsGilFcIlHhqyIe/Figure_3.png.scaled1000.png"><img alt="Figure_3" height="260" src="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/zGpweaaIevBFupiAwanerqFtGjGIixCEkCsvEzlzrnpmidsGilFcIlHhqyIe/Figure_3.png.scaled500.png" width="500" /></a> </div> </p> <p><strong><em>Figure 3 – PPMT formula for calculating amortisation/principal for a credit foncier repayment profile </em></strong></p> <p>Sum the interest and principal, to find the total debt service. This is also shown in Figure 3 above.</p> <p><strong>Step 4:</strong> Compare the debt service amount to the CFADS. If CFADS is above debt service change the plug figure in Step 2. Repeat Step 4 until CFADS = Debt Service for at least one of the periods. </p> <p>If you come to a debt amount of 54.76, you’d be correct. Hopefully you’ll notice that this is a fairly aggressive assumption. Imagine if the period 1 CFADS wasn’t 13, but instead, was 8. You wouldn’t have enough cash to meet your debt obligations. For this reason the debt amount of 13, may be considered as the maximum debt amount you could get into this credit foncier structure, given the above assumptions. </p> <p>One last figure, which should give you a good picture of what a credit foncier repayment profile looks like. </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/yFGxAwaiqnojloasjjfIBBrbbdtJEtglCEBlwGFJnntDehveCgdHxAFImykh/Figure_4.png.scaled1000.png"><img alt="Figure_4" height="351" src="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/yFGxAwaiqnojloasjjfIBBrbbdtJEtglCEBlwGFJnntDehveCgdHxAFImykh/Figure_4.png.scaled500.png" width="500" /></a> </div> </p> <p><strong><em>Figure 4 – CFADS and Debt Service for a Credit Foncier profile</em></strong></p> <p>As you can see in Figure 4, principal payments for credit foncier profiles increase over the period of the loan. This is due to the fact that interest is calculated on lower principal balances over the life of the loan, and hence interest paid decreases over the life of the loan. As mentioned the sum of the principal and interest in a credit foncier repayment profile stay constant over the period. </p> <p>You can also see in Figure 4 there is a large portion of excess CFADS (coloured in green) that is not utilised by debt. In most cases this probably means that the debt amount using a credit foncier repayment profile would be lower than a sculpted debt repayment profile. </p> <p><strong>Case 2 – Target DSCR of 1.30x</strong></p> <p>Now let’s look a utilising a target DSCR of 1.30x. </p> <p>Firstly we look at calculating the target debt service for each period. We do this by rearranging the DSCR calculation to:</p> <p style="padding-left: 30px;"><strong>Target Debt Service = CFADS divided by target DSCR</strong></p> <p>Based on this formula and the cash flows we come up with a target debt service as shown in Figure 5 below.</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/vEfssuzAngstHDccyHbdynvmGhCtxGrkjoabvuuxzlzthokedAlpBlGpvFEE/Figure_5.png.scaled1000.png"><img alt="Figure_5" height="168" src="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/vEfssuzAngstHDccyHbdynvmGhCtxGrkjoabvuuxzlzthokedAlpBlGpvFEE/Figure_5.png.scaled500.png" width="500" /></a> </div> </p> <p><strong><em>Figure 5 – Calculating Target Debt Service</em></strong></p> <p>Now that we know what the Target Debt Service is in each period, let’s put in a corkscrew account This is exactly the same process as we did with the credit foncier repayment profile above. Let’s also put in a plug figure of 55.</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/rwiumqdliHqElpjppwdeswojvEpnoAzrbpDCvsEscGFIxuGtGaaDyyuCxyuh/Figure_6.png.scaled1000.png"><img alt="Figure_6" height="216" src="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/rwiumqdliHqElpjppwdeswojvEpnoAzrbpDCvsEscGFIxuGtGaaDyyuCxyuh/Figure_6.png.scaled500.png" width="500" /></a> </div> </p> <p><strong><em>Figure 6 – Corkscrew account with a plug figure</em></strong></p> <p>Based on this plug figure we can work out our interest payments for each period. We can then find our amortisation or principal payment by using the following formula.</p> <p style="padding-left: 30px;"><strong>Target Amortisation = Target Debt Service – Interest</strong> (ensure that the amortisation is not positive i.e. doesn't add to the account balance)</p> <p>Figure 7 shows the calculation for target amortisation. </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/zHtocDADyHFgdiagktlpaFczJaisrraDmwzwchlztmiyrxAriiowhdoeCGDu/Figure_7.png.scaled1000.png"><img alt="Figure_7" height="191" src="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/zHtocDADyHFgdiagktlpaFczJaisrraDmwzwchlztmiyrxAriiowhdoeCGDu/Figure_7.png.scaled500.png" width="500" /></a> </div> </p> <p><strong><em>Figure 7 – Calculation of target amortisation</em></strong></p> <p>Change the plug figure until the final balance of the corkscrew account in year 5 is 0. You should get a debt amount of 187.38.</p> <p>Now let’s look at Figure 8. </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/IkbcEzofpvwEIGiCCCklugmAuFiefthIxbaEGBHCFjfcphpGfCcBhzEdqsnb/Figure_8.png.scaled1000.png"><img alt="Figure_8" height="344" src="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/IkbcEzofpvwEIGiCCCklugmAuFiefthIxbaEGBHCFjfcphpGfCcBhzEdqsnb/Figure_8.png.scaled500.png" width="500" /></a> </div> </p> <p><strong><em>Figure 8 - CFADS and Debt Service for a Sculpted Repayment profile with target DSCR of 1.30x</em></strong></p> <p>Can you see the difference between Figure 4 and Figure 8? There is less excess cash after debt service when using a sculpted repayment profile compared to a credit foncier repayment profile. </p> <p><strong>Case 3 – Target DSCR of 1.10x</strong></p> <p>We are going to let you do the last question. Simply repeat the steps that we performed in Case 2 and you should come up with an answer for the debt amount of 221. Notice that we get more debt in Company A the lower the target DSCR? </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/pcjfjicdHfGocikhgGvhEFdqxxybFuEdqkrbJdHcgtDjzoauudobFlhtfkbc/Figure_9.png.scaled1000.png"><img alt="Figure_9" height="351" src="http://posterous.com/getfile/files.posterous.com/temp-2011-08-15/pcjfjicdHfGocikhgGvhEFdqxxybFuEdqkrbJdHcgtDjzoauudobFlhtfkbc/Figure_9.png.scaled500.png" width="500" /></a> </div> </p> <p><strong><em>Figure 9 - CFADS and Debt Service for a Sculpted Repayment profile with target DSCR of 1.10x</em></strong></p> <p>In the majority of cases, higher credit risk projects will require a higher target DSCR. This will mean that the resultant debt size will be much lower. i.e. lower debt service</p> <p>Like this article? Check out the <a href="http://www.videofinancialmodelling.com/products/#inter" title="Excel and Financial Modelling Training Courses" target="_blank">Excel and Financial Modelling Training Courses</a> at <a href="http://www.videofinancialmodelling.com/" title="Video Financial Modelling home page" target="_blank">Video Financial Modelling</a>.</p> <p /> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-49061734496186872572011-07-28T11:32:00.001-07:002011-07-28T11:32:29.647-07:00CFADS and DSCR - Words from a Foreign Language?<div class='posterous_autopost'><p> <p>Some of you might be wondering whether CFADS and DSCR are words from another language. Well we’re here to explain to you that they’re not. CFADS and DSCR are acronyms which are common place in project finance deals. We’ll look at each in turn. </p> <p>NOTE: You can follow the examples by downloading the accompanying <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/07/CFADS-and-DSCR-Words-from-a-Foreign-Language.xlsx" title="CFADS and DSCR excel file" target="_blank">Excel spreadsheet</a> and <a href="http://youtu.be/FI6KgNT6TDg" title="CFADS and DSCR YouTube video" target="_blank">YouTube</a> video in the above Blog Downloads area. </p> <p><span style="font-size: medium;"><strong>CFADS </strong></span></p> <p>Cash is KING in project finance deals and CFADS is well, the heir to the throne. </p> <p>CFADS stands for cash flow available for debt service and is usually defined in the project finance loan documentation. It is used by financiers to calculate ratios and debt sizing. </p> <p>Although it can be defined in many different ways, the simplest definition is:</p> <ul> <li> <ul> <li><em><strong>Revenues</strong></em></li> <li><em><strong>less: Expenses </strong></em></li> <li><em><strong>less: Capital Expenditure during Operations(1)</strong></em></li> <li><em><strong>less: Tax</strong></em></li> <li><em><strong>plus/minus: Net Working Capital Movements</strong></em></li> </ul> </li> </ul> <p>(1)<span style=""> </span>In some instances Video Financial Modelling has seen growth capital expenditure, excluded from CFADS. The reason for this is that it is a discretionary expenditure. </p> <p>In a large number of cases project finance deals exhibit:</p> <ol> <li><strong>A ramp-up period post construction completion.</strong> For example think of a toll road. As soon as it is completed it does not get the predicted baseline traffic. People don’t automatically switch to using the toll road from alternate routes, it takes time. </li> <li><strong>CFADS usually grows over the life of the project.</strong> This is why project finance debt is often sculpted as opposed to repayments via a credit foncier profile. We will look at sculpted and credit foncier repayment profiles in the next blog tutorial. </li> </ol> <p>In the meantime let’s take a look at an example of CFADS. </p> <p><strong>Example</strong></p> <p>Find the CFADS given the following information:</p> <ul> <li><em>Revenue = 50</em></li> <li><em>Expenses = (10)</em></li> <li><em>Capital Expenditure = (15)</em></li> <li><em>Tax = (5)</em></li> <li><em>Working Capital Movements = (2) </em></li> <li><em>Depreciation = (10)</em></li> </ul> <p>If you got a CFADS of 18, then you’d be correct. Don’t get fooled by the depreciation, which is a non-cash movement. </p> <p>Now let’s take a look at what the DSCR is. </p> <p><strong><span style="font-size: medium;">DSCR</span></strong></p> <p>DSCR stands for Debt Service Coverage Ratio and is defined in the project finance loan documentation. In most cases it is equal to the:</p> <p style="padding-left: 30px;"><strong><em>CFADS divided by Debt Service</em></strong> </p> <p>The DSCR can be taken over a forward or backward looking period, say 6 months, or simply during one individual period. More often than not the DSCR is calculated during the operations phase of the project only. </p> <p>Debt Service is usually defined as the aggregate of senior debt interest, repayments/principal and in some circumstances may also include other fees.</p> <p>As you probably already guessed the DSCR is a measure of the amount cash flows can cover debt. The higher the DSCR, the more cash flow is available for debt financiers as a safety net. </p> <p>There are usually two benchmarks DSCR’s, defined in the project loan documentation, which are compared to the calculated DSCR. The first is the lock-up DSCR and the second is the default DSCR. </p> <p>When the calculated DSCR is below the lock-up DSCR all cash is locked up and may not be distributed to equity, until such time as the calculated DSCR is above the lock-up DSCR again.</p> <p>If the calculated DSCR goes below the default DSCR, then the company would be in default and there may be many mechanisms to deal with such default, such as the lenders stepping in to perform the company’s duties, selling the project etc. The default provision is done on a project by project basis. </p> <p>Let’s take a look at an example for calculating the DSCR. </p> <p><strong>Example</strong></p> <p>If we have the following CFADS, Interest and Principal payments what is the DSCR for each period individually and also using a look-back of two periods. </p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-07-28/FpfiGwFCsyimdsDewFjgegpkdshqkuzJenAGJhjnjlCkAuwabvhGJzHollkr/Figure1.jpg.scaled1000.jpg"><img alt="Figure1" height="74" src="http://posterous.com/getfile/files.posterous.com/temp-2011-07-28/FpfiGwFCsyimdsDewFjgegpkdshqkuzJenAGJhjnjlCkAuwabvhGJzHollkr/Figure1.jpg.scaled500.jpg" width="500" /></a> </div> </p> <p><strong><em>Figure 1: CFADS, Interest and Principal</em></strong></p> <p>Firstly let’s work out the Debt Service:</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-07-28/GxjqjjmvxjzIFwErmxpobmDwcaGywdmiGaknsEkDrfdGgfpoGizdDkEvbkix/Figure2.jpg.scaled1000.jpg"><img alt="Figure2" height="144" src="http://posterous.com/getfile/files.posterous.com/temp-2011-07-28/GxjqjjmvxjzIFwErmxpobmDwcaGywdmiGaknsEkDrfdGgfpoGizdDkEvbkix/Figure2.jpg.scaled500.jpg" width="500" /></a> </div> </p> <p><strong><em>Figure 2: Calculating Debt Service</em></strong></p> <p>Now let’s look at finding the DSCR for each individual period and also the DSCR for a two period look-back.</p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-07-28/JbjplzrqIHkczHsraiBFcCIvjDzlsqplDohoacqFEbHisapjldHbulGjavDj/Figure3.jpg.scaled1000.jpg"><img alt="Figure3" height="168" src="http://posterous.com/getfile/files.posterous.com/temp-2011-07-28/JbjplzrqIHkczHsraiBFcCIvjDzlsqplDohoacqFEbHisapjldHbulGjavDj/Figure3.jpg.scaled500.jpg" width="500" /></a> </div> </p> <p><strong><em>Figure 3: Individual Period DSCR</em></strong></p> <p><div class='p_embed p_image_embed'> <a href="http://posterous.com/getfile/files.posterous.com/temp-2011-07-28/JxxChweltrAafrCagjsdlalHfBmGqtaGupqblycgJvsqlHIhlJgpzyenEsjc/Figure4.jpg.scaled1000.jpg"><img alt="Figure4" height="180" src="http://posterous.com/getfile/files.posterous.com/temp-2011-07-28/JxxChweltrAafrCagjsdlalHfBmGqtaGupqblycgJvsqlHIhlJgpzyenEsjc/Figure4.jpg.scaled500.jpg" width="500" /></a> </div> </p> <p><strong><em>Figure 4: Two Period Look-Back DSCR</em></strong></p> <p>You should notice something here. There isn’t sufficient cash in period 3 despite the look-back DSCR ratio being above one. Be careful of this and make sure your financial model has a check for cash flow shortfalls. </p> <p>Hopefully this blog clears up the fact that CFADS and DSCR are not words from foreign languages. </p> <p>If you like this blog tutorial, take a look at our <a href="http://www.videofinancialmodelling.com/products/#inter" title="Video Financial Modelling Intermediate Training Courses" target="_blank">Intermediate financial modelling courses</a>.</p> <p>As always we’d love your <a href="mailto:feedback@videofinancialmodelling.com" title="Video Financial Modelling Feedback Email" target="_blank">feedback</a>, so that we can continue to provide content that is relevant to you. Also check out <a href="http://www.videofinancialmodelling.com" title="Video Financial Modelling" target="_blank">Video Financial Modelling's website</a>, which has plenty more free resources and great training courses. </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> </p></div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-55386554770971230252011-07-20T05:51:00.001-07:002011-07-20T06:06:53.426-07:00Calling all Excel Logical Operators<div class="posterous_autopost">Forget telephone operators, who almost always put you on hold, today we’re talking about Excel logical operators (also called Excel logical functions).<br />
A logical operator can simply be defined as a function that returns certain values if a logical test is true or false.<br />
We will look at three of the key Excel logical operators; IF, AND and OR.<br />
<blockquote>Note: You can follow the examples by downloading the accompanying <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/07/CallingallExcelLogicalOperators.xlsx" target="_blank" title="Excel Logical Operators Spreadsheet">Excel spreadshset</a> and <a href="http://youtu.be/wgIj92Fkh3A" target="_blank" title="Excel Logical Operators YouTube Video">YouTube video</a>.</blockquote><b>IF </b><br />
The format of the IF statement is as follows:<br />
<blockquote class="posterous_short_quote">= IF (Logical Test, Value if logical test is true, Value if logical test is false)</blockquote>The IF statement checks if a logical test is true or false and returns a certain value based on the outcome. Let’s look at an example.<br />
<i>Example:</i> Below is a table with people’s dates of birth. We want to find out which of the people are over 21. Let’s assume that today’s date is 31 December 2011.<br />
Normal 0 false false false EN-GB X-NONE X-NONE <br />
<table border="1" class="MsoTableGrid" style="border-collapse: collapse; border: medium none;"><tbody>
<tr> <td style="padding: 0in 5.4pt;" valign="top" width="308"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">Person</span></div></td> <td valign="top" width="308"><div style="line-height: normal; text-align: center;"><span style="font-size: small;">Date of Birth</span></div></td> </tr>
<tr> <td valign="top" width="308"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">Bob</span></div></td> <td valign="bottom" width="308"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">31 Aug 1990</span></div></td> </tr>
<tr> <td valign="top" width="308"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">John</span></div></td> <td valign="bottom" width="308"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">30 Sep 1995</span><span style="color: black; font-size: small;"></span></div></td> </tr>
<tr> <td valign="top" width="308"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">Bob</span></div></td> <td valign="bottom" width="308"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">31 Dec 1992</span></div></td> </tr>
<tr> <td valign="top" width="308"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">Jane</span></div></td> <td valign="bottom" width="308"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">30 Jun 1984</span></div></td> </tr>
<tr> <td valign="top" width="308"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">Sally</span></div></td> <td valign="bottom" width="308"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">31 Dec 1991</span></div></td> </tr>
</tbody></table>See the formulas for the calculation in Figure 1 below.<br />
<div class="p_embed p_image_embed"><a href="http://posterous.com/getfile/files.posterous.com/temp-2011-07-20/hhpfwCrkaDExbAwBCEEGFrbqhtxlhCexHqnDapbcHisqCkjhmndxkbqifADv/Figure1.jpg.scaled1000.jpg"><img alt="Figure1" height="218" src="http://posterous.com/getfile/files.posterous.com/temp-2011-07-20/hhpfwCrkaDExbAwBCEEGFrbqhtxlhCexHqnDapbcHisqCkjhmndxkbqifADv/Figure1.jpg.scaled500.jpg" width="500" /></a> </div><i><b>Figure 1: Calculation for IF Function Example</b></i><br />
If you found that Bob (the first Bob that is) and John are the only people over 21 then you’d be correct.<br />
<b>AND </b><br />
The format of the AND statement is as follows:<br />
<blockquote class="posterous_short_quote">= AND(Logical Test 1, Logical Test 2...)</blockquote>The AND statement returns TRUE if all the logical tests are true and FALSE if any one of the logical tests is false. Let’s look at an example.<br />
<i>Example: </i>We want to find whether the date 31 May 2011 is in between 30 June 2011 and 31 December 2011.<br />
We can see the calculation for the above in Figure 2 below.<br />
<div class="p_embed p_image_embed"><a href="http://posterous.com/getfile/files.posterous.com/temp-2011-07-20/JddeweborHnHrlkBdfveughsbAIIvyAciGxdsmeEuAjyerhJdAwnqIwdmrBA/Figure2.jpg.scaled1000.jpg"><img alt="Figure2" height="135" src="http://posterous.com/getfile/files.posterous.com/temp-2011-07-20/JddeweborHnHrlkBdfveughsbAIIvyAciGxdsmeEuAjyerhJdAwnqIwdmrBA/Figure2.jpg.scaled500.jpg" width="500" /></a> </div><b><i>Figure 2: Calculation for AND Function Example</i></b><br />
If you got FALSE you’d be correct.<br />
This may seem like an easy example but its implications are far reaching. Imagine if you had lots of dates and you wanted to find which dates are between two particular dates, a start date and an end date. I bet you could copy this formula across or down a row to find which dates meet the AND criteria.<br />
<br />
Remember our Blog tutorial on <a href="http://www.videofinancialmodelling.com/carindicators_howtheycanhelpyoumodelinexcel/" target="_blank" title="Excel Binary Indicator Blog">Car Indicators – How they can help you model in Excel</a>? We did a similar thing in that Blog tutorial.<br />
<b>OR</b><br />
The format of the OR statement is as follows:<br />
<blockquote class="posterous_short_quote">= OR(Logical Test 1, Logical Test 2...)</blockquote>The OR statement returns TRUE if any of the logical tests are true and FALSE if all of the logical tests are false. Let’s look at an example.<br />
<i>Example: </i>The below is a table with test results for two tests recently taken by students. We want to find the students that either scored over 75% in one test or had an average of more than 65%.<br />
<table border="1" class="MsoTableGrid" style="border-collapse: collapse; border: medium none;"><tbody>
<tr> <td style="padding: 0in 5.4pt;" valign="top" width="213"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">Person</span></div></td> <td valign="top" width="208"><div style="line-height: normal; text-align: center;"><span style="font-size: small;">Test 1 (%)</span></div></td> <td valign="top" width="196"><div style="line-height: normal; text-align: center;"><span style="font-size: small;">Test 2 (%)</span></div></td> </tr>
<tr> <td valign="top" width="213"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">Bob</span></div></td> <td valign="bottom" width="208"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">76.00%</span></div></td> <td valign="bottom" width="196"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">65.00%</span></div></td> </tr>
<tr> <td valign="top" width="213"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">John</span></div></td> <td valign="bottom" width="208"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">53.00%</span></div></td> <td valign="bottom" width="196"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">74.00%</span></div></td> </tr>
<tr> <td valign="top" width="213"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">Bob</span></div></td> <td valign="bottom" width="208"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">67.00%</span></div></td> <td valign="bottom" width="196"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">65.00%</span></div></td> </tr>
<tr> <td valign="top" width="213"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">Jane</span></div></td> <td valign="bottom" width="208"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">45.00%</span></div></td> <td valign="bottom" width="196"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">65.00%</span></div></td> </tr>
<tr> <td valign="top" width="213"><div style="line-height: normal; text-align: justify;"><span style="font-size: small;">Sally</span></div></td> <td valign="bottom" width="208"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">65.00%</span></div></td> <td valign="bottom" width="196"><div style="line-height: normal; text-align: center;"><span style="color: black; font-size: small;">60.00%</span></div></td> </tr>
</tbody></table>See Figure 3 for the OR calculation.<br />
<div class="p_embed p_image_embed"><a href="http://posterous.com/getfile/files.posterous.com/temp-2011-07-20/DtIAvuxjydbyBvcHBtgryciCfFwbyfdoiEzauFjbndeqjlIinyIziaHmcyhj/Figure3.jpg.scaled1000.jpg"><img alt="Figure3" height="245" src="http://posterous.com/getfile/files.posterous.com/temp-2011-07-20/DtIAvuxjydbyBvcHBtgryciCfFwbyfdoiEzauFjbndeqjlIinyIziaHmcyhj/Figure3.jpg.scaled500.jpg" width="500" /></a> </div><i><b>Figure 3: Calculation for OR Function Example</b></i><br />
You'll notice that both the Bob's met the criteria.<br />
If you like this article, there are plenty more tips, tricks and worked examples in our <a href="http://www.videofinancialmodelling.com/products/intermediate-tutorials/excel-functions/" target="_self" title="Excel Functions Training Course">Excel Functions</a> training course.<br />
Check out our <a href="http://www.videofinancialmodelling.com/products" target="_self" title="Video Financial Modelling Products">Excel and Financial Modelling training courses</a>.</div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0tag:blogger.com,1999:blog-2142951443737088035.post-67263755425260583092011-07-15T05:43:00.001-07:002011-07-15T08:59:10.761-07:00Excel NPV Function<div class="posterous_autopost"><strong>What is an NPV?</strong><br />
Simply put an NPV or net present value is a way of determining an investment or projects value. It is probably the most often used formula for financial decision making. The below tutorial takes you through:<br />
<ul><li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>An introduction to present value </li>
<li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>The decision to accept or reject a project based on NPV</li>
<li> The Excel XNPV function</li>
<li> Putting it all together – some examples</li>
</ul><strong>Present Value Introduction</strong><br />
Before we delve into the depths of NPV, we should first understand what a present value or PV is. Imagine that you were to receive $100 from the bank in five years time (unlikely I know). You know that the interest rate from the bank during that period of time is going to be constant at 5%. What is that $100 value today?<br />
Think of investing an unknown amount y, for 5 years at 5%. We know that the first year we would get y x 5% in interest plus still have our principal y. i.e. we would have y x (1+5%). If we re-invest our interest and principal of y x (1+5%) for another year we would get y x (1+5%) x (1+5%) or y x (1+5%)<sup>2</sup>.<br />
As you can imagine the pattern repeats itself until we get to year 5, where we have y x (1+5%)<sup>5</sup>. Hence if we want $100 in five years time by investing y then the following relationship would have to hold.<br />
<div style="padding-left: 30px;">100 = y x (1+5%)<sup>5</sup></div>Rearranging this formula would bring about y = 100/(1+5%)<sup>5</sup>. Wow, look what just happened, we derived the <a href="http://en.wikipedia.org/wiki/Present_value" target="_blank">PV</a> formula.<br />
Type the equation in the calculator and you get y = $78.3<br />
NPV is simply an extension of the PV, taking into account your initial investment. Say for example we had an initial outlay of $50 and in return we got $100 in five years time. The NPV would be the PV of future cash flows (i.e. the PV of $100 or $78.3) less the initial investment at time zero ($50), or $28.3.<br />
So what does that value mean?<br />
<strong>The decision to accept or reject a project</strong><br />
Basically put if a project has a positive NPV then we should accept the project. The below outlines the accept reject decision.<br />
<ul><li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>NPV >0 – accept the project</li>
<li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>NPV = 0 – indifferent between accepting and rejecting the project</li>
<li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>NPV <0 – reject the project</li>
</ul>In summary we should accept a positive NPV project because it adds value to our company.<br />
<strong>NOTE:</strong> Some of you sharp tacks may be thinking. What happens if you have multiple projects with positive NPVs but only have a restricted budget? Well that is a topic for another week and relates to capital budgeting restrictions. <br />
<strong>The Excel XNPV function</strong><br />
As pointed out by <a href="http://chandoo.org/wp/2011/07/05/using-npv-in-excel/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+PointyHairedDilbert+%28Chandoo.org+-+Learn+Excel+%26+Charting+Online%29&utm_content=Google+Reader" target="_blank">Chandoo.org</a> and <a href="http://www.navigatorpf.com/tutorials/tutorial-formula-calculate-npv-excel" target="_blank">Navigator Project Finance</a> there are some limitations to the NPV function in Excel. Some of the main limitations include:<br />
<div class="MsoListParagraphCxSpFirst">1)<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>The cash flows are at the end of the year (even your initial investment which is meant to occur at t=0); and</div><div class="MsoListParagraphCxSpLast">2)<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>cash flows must be equally spaced apart.</div>As a result of these limitations <a href="http://www.videofinancialmodelling.com/" target="_self" title="Video Financial Modelling">Video Financial Modelling</a> prefers to use Excel’s XNPV function. Don’t be too worried, the Excel XNPV function is similar NPV function, but with one extra input, dates. The XNPV function looks like:<br />
<strong>XNPV (rate, values, dates) </strong><br />
Let’s look at some examples for the Excel XNPV function.<br />
<strong>Putting it all together – Some Examples</strong><br />
You can follow the examples by downloading the <a href="http://www.videofinancialmodelling.com/wp-content/uploads/downloads/2011/07/ExcelNPVFunction.xlsx" target="_blank" title="Excel NPV Spreadsheet">Excel spreadsheet</a> and watching the following <a href="http://youtu.be/QAhzLtPBdyM" target="_blank" title="Excel NPV Function YouTube video">YouTube</a> video.<br />
<strong><em>Example 1 </em></strong><br />
Let’s firstly look at the example we introduced in the Present Value Introduction. Imagine that the current date is 31 December 2011 and we want to find the present value of $100 given to us in 5 years time if we use a discount rate of 5%.<br />
See Figure 1 for the XNPV calculation.<br />
<div class="p_embed p_image_embed"><a href="http://posterous.com/getfile/files.posterous.com/temp-2011-07-15/AzdwvofCkIJwuubeoxCkBnDakxHGjxfpsIahmbqnbhHkyEndHhAbjaAlFmtg/Figure1.png.scaled1000.png"><img alt="Figure1" height="173" src="http://posterous.com/getfile/files.posterous.com/temp-2011-07-15/AzdwvofCkIJwuubeoxCkBnDakxHGjxfpsIahmbqnbhHkyEndHhAbjaAlFmtg/Figure1.png.scaled500.png" width="500" /></a></div><strong><em>Figure 1: XNPV formula for Example 1</em></strong><br />
<b><i><br />
</i></b><br />
If you've done this correctly you should get a value of $78.3.<br />
<strong><em>Example 2</em></strong><br />
You are thinking of investing in a machine. On 31 December 2011 the machine costs $150, and you require a return of 10%. If you have the following cash flows, would you invest in the machine?<br />
<ul><li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>31 December 2012: $50</li>
<li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>31 December 2013: $40</li>
<li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>31 December 2014: $70</li>
<li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>31 December 2015: $10</li>
</ul><div class="MsoNormal">Figure 2 shows the calculation for the above example.</div><img src="http://www.videofinancialmodelling.com/wp-content/uploads/2011/07/Figure2.png" width="500"/><br />
<br />
<strong><em>Figure 2: XNPV formula for Example 2</em></strong><br />
Using Excel we come to the following conclusion, the net present value of the cash flows is -$12. If we use the decision rules we established in the last section we would reject the project as it has a negative NPV.<br />
If you’re still struggling with the NPV concept check out some of these great links.<br />
<ul><li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span><a href="http://en.wikipedia.org/wiki/Present_value" target="_blank">Wikipedia</a></li>
<li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span><a href="http://chandoo.org/wp/2011/07/05/using-npv-in-excel/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+PointyHairedDilbert+%28Chandoo.org+-+Learn+Excel+%26+Charting+Online%29&utm_content=Google+Reader" target="_blank">Chandoo.org</a></li>
<li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span><a href="http://www.navigatorpf.com/tutorials/tutorial-formula-calculate-npv-excel" target="_blank">Navigator Project Finance</a></li>
<li><span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span><iframe allowfullscreen="" frameborder="0" height="417" src="http://www.youtube.com/embed/JOqEpxNGQjk" width="500"></iframe> (Note: don’t get bogged down with the nominal to real rate calculation in this video).</li>
</ul>Like this article? Check out the <a href="http://www.videofinancialmodelling.com/products/" target="_self" title="Excel and Financial Modelling Training Courses">Excel and Financial Modelling Training Courses</a> at Video Financial Modelling.</div>Brett Rankinehttp://www.blogger.com/profile/05077630843993263139noreply@blogger.com0