{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Main Menu","1 - Cash Flow Example","2 - Discount Table","3 - EAA Calculation","4 - Payback Function"],"not_rendered":{"charts":0,"images":9,"macros":1,"external_links":0,"defined_names":3,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Main Menu","index":0,"n_rows":27,"n_cols":11,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
[null,null,null,"Module 3 Course 1 - Financial Modeling "]
[null,null,null,"Lesson 1 - Cash Flow Modeling"]
[null,null,null,"Table of Contents"]
[]
["This workbook provides some examples of how to build a very simple financial model - cash flows. This is at "]
["the core of many financial models and it is a good place to start. Tab 1 provides a simple example "]
["of how to calculate the Net Present Value of an investment. Tab 2 provides three different approaches to"]
["how you would apply discount factors. Tab 3 provides an important calculation on how you should "]
["evaluate several investment alternatives that have positive Net Present Values. Finally, Tab 4 illustrates"]
["an advanced concept using Microsoft Excel - creating a financial function to calculate the payback period."]
[]
["Tab",null,"Title",null,null,"Description"]
[]
[1,null,"Simple Cash Flow Model","Example of how to build a discounted cash flow model"]
[2,null,"Discount Factors","Three approaches to discount factors"]
[3,null,"Equiv Annual Annuity","How to evaluate NPV's for several investments"]
[4,null,"Pay Back Function","How to add a function for calculating payback in Microsoft Excel"]
[]
[]
[null,"When you have studied this workbook, you should be able to apply four important"]
[null,"financial indicators to investments and know how to use Microsoft Excel:",null,null,null,null,null,null,null," ","Case"]
[null,null,null,null,null,null,null,null,null,"Excel","Study"]
[null,null,"Financial Indicator",null,null,"Critical Question Answered",null,null,null,"Function","Example"]
[null,"Net Present Value",null,null,"How much value does this investment generate?",null,null,null,null,"=NPV",{"$f":"'1 - Cash Flow Example'!G65","v":113785.00080371412}]
[null,"Rate of Return",null,null,"What return do we get with this investment?",null,null,null,null,"=MIRR",{"$f":"'1 - Cash Flow Example'!G78","v":0.17811268154548987}]
[null,"Equivalent Annual Annuity",null,null,"What annuity payment is equal to the investment's NPV?",null,null,null,null,"=PMT",{"$f":"'3 - EAA Calculation'!E31","v":32350.730544174239}]
[null,"Discounted Payback Period",null,null,"How long does it take to reach payback on the investment?",null,null,null,null,"=Payback",{"$f":"'4 - Payback Function'!J206","v":3.05}]

{"sheet":"1 - Cash Flow Example","index":1,"n_rows":113,"n_cols":10,"rows_kept":113,"cut":false,"hidden":false,"merged":[]}
[null,null,null,"Module 3 Course 1 - Financial Modeling "]
[null,null,null,"Lesson 1 - Cash Flow Modeling"]
[null,null,null,"Cash Flow Example"]
[]
["Listed below are some examples and explanations to help you understand the basics behind how you might create a cash flow "]
["type model. Cash flow type models are very common because this is the basis for calculating valuations. Therefore, it is very"]
["important to know how to estimate future cash flows when it comes to financial modeling. "]
[]
["Step 1 - Schedule all cash outflows that will take place over the life of the investment. These include initial down "]
[null,"payments, payments made to ship, install and place the asset into service. The initial down payment is often listed"]
[null,"under the column Year as 0. Payments made after the initial down payment are analyzed and reported for all"]
[null,"remaining periods.  All amounts are shown as outflows or negative amounts. See example below:"]
[]
[null,null,null,null,"Internal"," ",null,"TOTAL"]
[null," ","Supplier","Legal","Company","Insurance","Tax","CASH"]
[null,"Year","Payments","Fees","Payroll","Payments","Payments","OUTFLOWS"]
[null,0,-150000,-12500,-6500,0,0,{"$f":"SUM(C17:G17)","v":-169000}]
[null,1,-75000,-3500,-12700,-4000,-1500,{"$f":"SUM(C18:G18)","v":-96700}]
[null,2,-10000,-750,-8000,-3000,-500,-22250]
[null,3,0,0,-5500,-6500,-2000,-14000]
[null,4,0,0,-4300,-6500,-3500,-14300]
[null,5,0,0,-7200,-5000,-2800,-15000]
[]
[]
["Step 2 - Schedule the benefit stream over the life cycle of the investment. Benefits from an investment can take"]
[null,"many forms such as increased sales revenues, reductions to operating expenses, improved production outputs, etc."]
[null," ","Click Here","for examples of specific types of benefits with an investment"]
[]
[null,null," ","Lower ","Reductions","TOTAL"]
[null,null,"Additional","Operating ","to Direct","CASH"]
[null,"Year","Revenues","Cost","Labor","INFLOWS"]
[null,0,0,0,0,{"$f":"SUM(C32:E32)","v":0}]
[null,1,8000,60000,12000,{"$f":"SUM(C33:E33)","v":80000}]
[null,2,78000,40000,15000,133000]
[null,3,80000,45000,10000,135000]
[null,4,60000,48000,9000,117000]
[null,5,45000,51000,6000,102000]
[]
[]
["Step 3 - Determine what hurdle rate is appropriate for the investment. As a minimum this is the cost of financing the"]
[null,"investment. Since you want to recover more than your cost of funding the investment, you will add a risk premium to"]
[null,"your cost to arrive at the rate of return you want to earn. From a company wide standpoint, the minimum rate of return"]
[null,"would be your cost of capital - this would allow you to at least break even. However, you want to increase the value of"]
[null,"your business and thus, the hurdle rate is usually higher than the company's cost of capital. "]
[]
[null,"Let's assume that the Cost of Capital is 10% and management has determined that this project should carry a risk"]
[null,"premium of 3%. Therefore, the hurdle rate we will use will be 13%."]
[]
[]
["Step 4 - Final step is to discount your net cash flows for the investment based on your hurdle rate. Discount Tables on "]
[null,"Tab 2 - Discount Table are applied to each year to arrive at the Net Present Value of the investment. For accuracy, we"]
[null,"can show Net Present Value based on the mid point discount factor or for a more conservative estimate, we can"]
[null,"show Net Present Value based on the end of year discount factor (most commonly used approach):"]
[]
[null,null,null,null,null,"< - Mid Point Discounting - - >",null,"< - End of Year Discounting - >"]
[null,null,"TOTAL","TOTAL","NET","Discount","Present","Discount","Present"]
[null,null,"CASH","CASH","CASH","Factor","Value","Factor","Value"]
[null,"Year","OUTFLOWS","INFLOWS","FLOWS",0.13,"Cash Flow",0.13,"Cash Flow"]
[null,0,{"$f":"H17","v":-169000},{"$f":"F32","v":0},{"$f":"C59+D59","v":-169000},1,{"$f":"E59*F59","v":-169000},1,{"$f":"E59*H59","v":-169000}]
[null,1,-96700,80000,{"$f":"C60+D60","v":-16700},{"$f":"'2 - Discount Table'!F30","v":0.94072086838359736},{"$f":"E60*F60","v":-15710.038502006077},{"$f":"'2 - Discount Table'!F15","v":0.88495575221238942},{"$f":"E60*H60","v":-14778.761061946903}]
[null,2,-22250,133000,110750,{"$f":"'2 - Discount Table'!F31","v":0.83249634370229864},92198.970065029571,{"$f":"'2 - Discount Table'!F16","v":0.78314668337379612},86733.495183647916]
[null,3,-14000,135000,121000,{"$f":"'2 - Discount Table'!F32","v":0.73672242805513155},89143.413794670923,{"$f":"'2 - Discount Table'!F17","v":0.69305016227769578},83859.06963560119]
[null,4,-14300,117000,102700,{"$f":"'2 - Discount Table'!F33","v":0.65196675049126696},66956.985275453117,{"$f":"'2 - Discount Table'!F18","v":0.61331872767937679},62987.833332671995]
[null,5,-15000,102000,87000,{"$f":"'2 - Discount Table'!F34","v":0.57696172609846641},50195.670170566576,{"$f":"'2 - Discount Table'!F19","v":0.54275993599944861},47220.114431952032]
[null,null," "," ","NET PRESENT VALUE",null,{"$f":"SUM(G59:G64)","v":113785.00080371412},null,{"$f":"SUM(I59:I64)","v":97021.751521926242}]
[null,null,null," "," "]
[null,null,null," "]
[null,null,null," ",null,"Based on our cash flow "]
[null,null,null,null,null,"model, this investment"]
[null,null,null,null,null,"should add value"]
[]
[null,"You can also calculate the Rate of Return for the investment using the XIRR"]
[null,"function in Excel. The Rate of Return must be higher than the Hurdle Rate in"]
[null,"order for the investment to be acceptable. Also, you should use a re-"]
[null,"investment rate of 0% since each investment should stand on its own."]
[null,"No financing was required to fund this investment. "]
[null,null,null,null,"Reinvestment Rate",null,0,null,"Use the MIRR function in Excel to calculate the Rate of Return and use a very modest"]
[null,null,null,null,"RATE OF RETURN",null,{"$f":"MIRR(E59:E64,,G77)","v":0.17811268154548987}," ","reinvestment rate to provide a real acid test of what the investment is returning"]
[]
[]
[null,"The =NPV function in Microsoft Excel is a little flawed in that it does not realize that the",null,null,null,null,null,null,{"$f":"NPV(H58,E59:E64)","v":85859.957099049774},"< Incorrect application of the =NPV Function"]
[null,"initial Year 0 investment should not be discounted. So be careful not to include the initial",null,null,null,null,null,null,{"$f":"NPV(F58,E60:E64)+E59","v":97021.751521926257},"< Correct application of the =NPV Function"]
[null,"year in the cell range. Simply add the initial year to the calculated NPV. ",null,null,null,null,null,null," "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Examples of benefits from investments"]

{"sheet":"2 - Discount Table","index":2,"n_rows":45,"n_cols":10,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
[null,null,null,"Module 3 Course 1 - Financial Modeling "]
[null,null,null,"Lesson 1 - Cash Flow Modeling"]
[null,null,null,"Discount Factors - Present Value of Lump Sum Amounts"]
[]
["Once you have calculated your net benefit stream or cash flows and once you have calculated your"]
["hurdle rate, you will need to discount all of the future forecasted cash flows to a present value. There"]
["are three approaches to how you might discount your cash flows:"]
[]
["Approach 1 - Discount the Cash Flows as though they take place as a lump sum amount at the end of the period"]
[]
[null,"The formula for calculating the discount factor is (1 + Hurdle Rate)^p",null,null,null,null,null,null,"p: Period"]
[]
[null,"Year or","< - - - - - - - - - - - - - - - - -  Hurdle Rate - - - - - - - - - - - - - - - - - - > "]
[null,"Period",0.1,0.11,0.12,0.13,0.14000000000000001,0.15," "," "]
[null,1,{"$f":"1/((1+C14)^$B$15)","v":0.90909090909090906},{"$f":"1/((1+D14)^$B$15)","v":0.9009009009009008},0.89285714285714279,0.88495575221238942,0.8771929824561403,0.86956521739130443]
[null,2,{"$f":"1/((1+C14)^$B$16)","v":0.82644628099173545},{"$f":"1/((1+D14)^$B$16)","v":0.8116224332440547},0.79719387755102034,0.78314668337379612,0.76946752847029842,0.7561436672967865]
[null,3,{"$f":"1/((1+C14)^$B$17)","v":0.75131480090157754},{"$f":"1/((1+D14)^$B$17)","v":0.73119138130095018},0.71178024781341087,0.69305016227769578,0.67497151620201612,0.65751623243198831]
[null,4,{"$f":"1/((1+C14)^$B$18)","v":0.68301345536507052},{"$f":"1/((1+D14)^$B$18)","v":0.65873097414500015},0.63551807840483121,0.61331872767937679,0.59208027737018942,0.57175324559303342]
[null,5,{"$f":"1/((1+C14)^$B$19)","v":0.62092132305915493},{"$f":"1/((1+D14)^$B$19)","v":0.5934513280585586},0.56742685571859919,0.54275993599944861,0.51936866435981521,0.49717673529828987]
[]
[null,"Example: Your lump sum net cash flow at the end of year 4 is $ 10,000 and your "]
[null,"hurdle rate is 13%. Multiply $ 10,000 by .6133 = $ 6,133 present value."]
[]
["Approach 2 - Discount the Cash Flows as though they take place throughout the period"]
[]
[null,"The formula for calculating the discount factor is (1 + Hurdle Rate)^p-.5",null,null,null,null,null,null,0.5]
[]
[null,"Year or","< - - - - - - - - - - - - - - - - -  Hurdle Rate - - - - - - - - - - - - - - - - - - > "]
[null,"Period",0.1,0.11,0.12,0.13,0.14000000000000001,0.15]
[null,1,{"$f":"1/((1+C29)^($B$30-$I$26))","v":0.95346258924559224},{"$f":"1/((1+D29)^($B$30-$I$26))","v":0.94915799575249904},0.94491118252306794,0.94072086838359736,0.93658581158169396,0.93250480824031379]
[null,2,{"$f":"1/((1+C29)^($B$31-$I$26))","v":0.86678417204144742},{"$f":"1/((1+D29)^($B$31-$I$26))","v":0.85509729347071961},0.84367069868131062,0.83249634370229864,0.82156650138745069,0.81087374629592512]
[null,3,{"$f":"1/((1+C29)^($B$32-$I$26))","v":0.78798561094677033},{"$f":"1/((1+D29)^($B$32-$I$26))","v":0.77035792204569342},0.75327740953688449,0.73672242805513155,0.72067236963811454,0.70510760547471751]
[null,4,{"$f":"1/((1+C29)^($B$33-$I$26))","v":0.71635055540615489},{"$f":"1/((1+D29)^($B$33-$I$26))","v":0.69401614598711103},0.67256911565793243,0.65196675049126696,0.63216874529659162,0.61313704823888482]
[null,5,{"$f":"1/((1+C29)^($B$34-$I$26))","v":0.65122777764195883},{"$f":"1/((1+D29)^($B$34-$I$26))","v":0.62523977115955953},0.60050813898029676,0.57696172609846641,0.55453398710227331,0.53316265064250867]
[]
[]
["Approach 3 - Discount the Cash Flows as though they take place as a lump sum amount at the beginning of the year"]
[]
[null,"Year or","< - - - - - - - - - - - - - - - - -  Hurdle Rate - - - - - - - - - - - - - - - - - - > "]
[null,"Period",0.1,0.11,0.12,0.13,0.14000000000000001,0.15]
[null,1,1,1,1,1,1,1]
[null,2,{"$f":"1/((1+C40)^$B$41)","v":0.90909090909090906},{"$f":"1/((1+D40)^$B$41)","v":0.9009009009009008},0.89285714285714279,0.88495575221238942,0.8771929824561403,0.86956521739130443]
[null,3,{"$f":"1/((1+C40)^$B$42)","v":0.82644628099173545},{"$f":"1/((1+D40)^$B$42)","v":0.8116224332440547},0.79719387755102034,0.78314668337379612,0.76946752847029842,0.7561436672967865]
[null,4,{"$f":"1/((1+C40)^$B$43)","v":0.75131480090157754},{"$f":"1/((1+D40)^$B$43)","v":0.73119138130095018},0.71178024781341087,0.69305016227769578,0.67497151620201612,0.65751623243198831]
[null,5,{"$f":"1/((1+C40)^$B$44)","v":0.68301345536507052},{"$f":"1/((1+D40)^$B$44)","v":0.65873097414500015},0.63551807840483121,0.61331872767937679,0.59208027737018942,0.57175324559303342]

{"sheet":"3 - EAA Calculation","index":3,"n_rows":38,"n_cols":7,"rows_kept":38,"cut":false,"hidden":false,"merged":[]}
[null,null,null,"Module 3 Course 1 - Financial Modeling "]
[null,null,null,"Lesson 1 - Cash Flow Modeling"]
[null,null,null,"Using the Equivalent Annual Annuity Formula (EAA)"]
[]
["If you are evaluating several alternative investments, then you should consider using the Equivalent Annual"]
["Annuity (EAA) amount. This can be very important when you are dealing with investments that have different"]
["life cycles, many of which have positive Net Present Values. You do not want to simply choose the investment"]
["with the highest Net Present Value. The EAA puts all investments on an \"equal footing\" so you can better"]
["evaluate which investment is the best choice."]
[" "]
["The formula for calculating EAA is:"]
[]
[]
[]
[]
[" "]
[" "]
[]
[null," "]
[]
[]
[]
["If we go back to our example on Tab 1 within this workbook:"]
[]
["The hurdle rate for the investment was:",null,null,null,0.13,null,"You will need to know these three"]
["The number of years in the life cycle:",null,null,null,5,null,"factors in order to calculate the EAA"]
["Net Present Value of the investment:",null,null,null,{"$f":"'1 - Cash Flow Example'!G65","v":113785.00080371412}]
[]
["If we use the formula above >",null,null,null,{"$f":"(E25*E27)/((1-(1+E25)^-E26))","v":32350.730544174272}]
[]
["A much easier way is to use =PMT",null,null,null,{"$f":"PMT(E25,E26,-E27)","v":32350.730544174239}]
[]
[]
["Use this amount to evaluate the investment against other competing investments. The investment"]
["with the higher EAA is returning a higher amount sooner as opposed to another investment which may"]
["have a higher NPV, but it may not provide a return until much later. A lot of things can change over "]
["time, so you should always use EAA in conjunction with NPV, especially when you have competing"]
["projects that have different life cycles / time frames."]

{"sheet":"4 - Payback Function","index":4,"n_rows":209,"n_cols":12,"rows_kept":209,"cut":false,"hidden":false,"merged":[]}
[null,null,null,"Module 3 Course 1 - Financial Modeling "]
[null,null,null,"Lesson 1 - Cash Flow Modeling"]
[null,null,null,"Adding Function for Pay Back Calculation"]
[]
["In addition to calculating Net Present Value, you may want to evaluate the investment using"]
["additional criteria such as Payback Period. This worksheet documents how to add a function "]
["into Excel for calculating pay back. The code behind this function is:"]
[]
[null,null,null,null,null,null,null," ","You will need to copy and post this range of cells highlighted in blue"]
[null,"Function Payback(CumCashFlow)"]
[null,"Dim csum     'csum = cumulative cash flow"]
[null,"Dim i As Integer"]
[null,"If CumCashFlow(1) >= 0 Or Application.Sum(CumCashFlow) < 0 Then"]
[null,"Payback = -1"]
[null,"Else"]
[null,"csum = 0"]
[null,"For i = 1 To Application.Count(CumCashFlow)"]
[null,"csum = csum + CumCashFlow(i)"]
[null,"If csum > 0 Then"]
[null,"Exit For"]
[null,"End If"]
[null,"Next i"]
[null,"csum = csum - CumCashFlow(i)"]
[null,"Payback = Application.Round(i - 2 - csum / CumCashFlow(i),2)"]
[null,"End If"]
[null,"End Function"]
[]
[]
["STEPS FOR ADDING THE PAYBACK FUNCTION:"]
[]
[" ","Depending upon which version of Excel you have, you will need to use the Visual Basic editor. It is "]
[null,"located as part of your Developer tools within Excel. So you may need to enable Developer first. "]
[null,"This will allow you to access the Visual Basic editor. The screen shots below show how to enable the "]
[null,"Developer tools within Excel:"]
[]
[null,"NOTE: You can short cut the process by going directly to the Visual Basic editor by hitting Alt / F11"]
[]
[null,"From the main menu, select File > Options",null,null,null,null,null,null,null,null,null," "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"From Excel Options, select Customize Ribbon and make sure the Developer"]
[null,"tool is enabled - should appear in the right side panel with a check mark:"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"The Developer tab should now appear as part of your main menu. On the very left end "]
[null,"you will see the Visual Basic icon:"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"You should see a new Visual Basic for Applications window show up. Select Insert >"]
[null,"Module. We want to place our payback function within a module. "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Click on the Module icon to make sure you are working within the right location:"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"When you click on the Visual Basic icon, a new window will appear, allowing you to copy"]
[null,"and paste the function code. Select View > Code. "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Go back to the top of this worksheet and copy and paste the code into the right"]
[null,"panel:"]
[]
[null,null,"The cells that you want to copy are highlighted in light blue"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Test out the function. You should be able to enter =payback within a cell and highlight"]
[null,"the discounted cash flows"]
[null,null,null,null,"Let's go back to Tab 1 and calculate payback >",null,null,null,null,{"$f":"payback('1 - Cash Flow Example'!G59:G64)","v":3.05}]
[]
[null,"Save the your excel file as a \"Macro Enabled Workbook\" so that the new function will work."]
[null,"You can save the file as a new file or overwrite the file. "]