Professional Excel Templates
This book is part a series of book CDs on Spreadsheet Modeling by Craig W. Holden, published by Prentice Hall. The series includes n Spreadsheet Modeling in Corporate Finance, n Spreadsheet Modeling in the Fundamentals of Corporate Finance, n Spreadsheet Modeling in Investments, and n Spreadsheet Modeling in the Fundamentals of Investments. Each book teaches value-added skills in constructing financial models in Excel. Complete information about the Spreadsheet Modeling series is available at my web site http www.spreadsheetmodeling.com Most of the Spreadsheet Modeling book CDs can be purchased any time at
You can access the worldwide spreadsheet modeling community by clicking on Community (Free Enhancements) at my web site http www.spreadsheetmodeling.com. You will find free additions, extensions, and problems that professors and practitioners from around the world have made available for you. I will post annual updates of the U.S. yield curve database and occasional new spreadsheet models. If you would like to make available your own addition, extension, or problem to the worldwide finance community, just e-mail it to me at cholden indiana.edu and I will post it on my web site. Your worldwide finance colleagues thank you.
Spreadsheets are probably the most common analytical program used in business economics. Spreadsheets have powerful advantages, including low costs, intuitive use, and an ability to easily print the data used in its calculations. For most accounting work and many other uses (including uses we will describe in later chapters), you cannot beat a spreadsheet package. However, for analytical work requiring dynamic interaction among variables meaning that one variable affects another, which affects two more, and the latter affects the results of the next-period variables spreadsheets fall far behind. Although you can trick a spreadsheet into performing some advanced analysis and even limited dynamic equations, such uses are beyond their intended scope.19 Limitations of Spreadsheet Models Spreadsheet software has serious limitations as an analytical tool. These limitations include 1. A spreadsheet is inherently a two-dimensional device. Many problems can be solved in a 2-D environment,...
Find the 3-month cash Libor rate and the interest rates corresponding to the prices of the first twelve Euro-dollar 3-month futures1. Keep track of each of these thirteen rates every day2 for two weeks3 using the spreadsheet program Excel4'1 and note the rate changes each day. 4 Spreadsheets If you don't know much about spreadsheets, regardless of what you know about programming or quantitative packages, this seemingly trivial exercise is
Using a Spreadsheet for Time Value of Money Calculations More and more, businesspeople from many different areas (and not just finance and accounting) rely on spreadsheets to do all the different types of calculations that come up in the real world. As a result, in this section, we will show you how to use a spreadsheet to handle the various time value of money problems we presented in this chapter. We will use Microsoft Excel , but the commands are similar for other types of software. We assume you are already familiar with basic spreadsheet operations. As we have seen, you can solve for any one of the following four potential unknowns future value, present value, the discount rate, or the number of periods. With a spreadsheet, there is a separate formula for each. In Excel, these are as follows There are two things that are a little tricky here. First, unlike a financial calculator, the spreadsheet requires that the rate be entered as a decimal. Second, as with most financial...
Start with the Net Present Value - Constant Discount Rate Spreadsheet, Insert Rows, And Move One Item. Open the spreadsheet that you created for Net Present Value - Constant Discount Rate and immediately save the spreadsheet under a new name using the File Save As command. Select the cell A5 and click on Insert Rows. Select the range A11 A13 and click on Insert Rows. Select the range A6 B6, click on Edit Cut, select the cell A12, and click on Edit Paste.
Now assume that the date is 10 25 2002. Assume further that our 12 percent, 10-year bond was issued on 7 1 2002, is callable on 7 1 2006 at 1,060, will mature on 6 30 2012, pays interest semiannually (January 1 and July 1), and sells for 1,100. Use your spreadsheet to find (1) the bond's yield to maturity and (2) its yield to call.
To create the Commission function in your spreadsheet, simply insert a Module sheet in the workbook. To do this, choose Tools then Macro then Visual Basic Editor to go to the VB window. With your workbook highlighted in the Project window, choose Insert then Module from the menu. Enter the VBA code for the function in the Code window (ensuring that Option Explicit is declared at the top of the Module sheet). To test the function out on the spreadsheet, enter a formula such as If your function does not work, you may see an error message in the cell (such as NAME or VALUE ) which usually indicates a mismatch between the function and input or that the function name is wrongly entered. Alternatively a Microsoft Visual Basic 'compile error' message may appear, indicating a mistake in your code. In this case, note the nature of the error (frequently 'Variable not defined'), click OK and correct the highlighted error. Then, most important, click the Reset button (with the black square) on...
If you're going to be tackling financial questions regularly, either as a career or for your own purposes, you should be using a spreadsheet program their flexibility and built-in functions and tools make it easy to construct even the most complicated financial models. Currently, the spreadsheet program of choice amongst business professionals is Microsoft Excel the introduction of enhanced access to external data sources seen in the most recent versions, as well as the continued abundance of available third-party add-ons, make it likely that Excel will continue to be the financial tool of choice for quite some time to come. Unfortunately, the academic world has lagged well behind the business world in adopting spreadsheets if you're currently taking finance classes, it's a pretty good bet that your instructors are either using a financial calculator or doing the necessary math by hand when they work out examples in class. And they probably want you to do your homework the same way,...
Bookkeeping and accounting software often has 'report generator' programs that crunch out ratios for you, sometimes with helpful suggestions on areas to be probed further. Biz ed (www.bized.co.uk Company information Financial ratio analysis) and the Harvard Business School have free tools that calculate financial ratios from your financial data. They also provide useful introductions to ratio analysis as well as defining each ratio and the formula used to calculate it. You need to register on the Harvard website to be able to download their spreadsheet.
Start with a Spreadsheet Containing the Yield Curve Database. Click on Ycdyndat.xls to open a spreadsheet containing the yield curve database (see Figure 2). Select the range A1 O1 and click on Insert Columns. Columns P, Q, and R contain three sets of titles for the dataset. Columns S, T, and U contain yield data for bond maturities of one month, three months, and six months (0.833, 0.25, and 0.50 years, respectively). Columns V through AE contain yield data for bond maturities of 1, 2, 3, 4, 5, 10, 15, 20, 25, and 30 years. Rows 2 through 9 contain examples of static features yield curve that can be observed from actual data in a particular month. For example, the yield curve is sometimes upward sloping (as it was in Nov 87) or downward sloping (in Nov 80) or flat (in Jan 70) or hump shaped (in Dec 78). Rows 10 through 376 contain monthly US zero-coupon, yield curve data from January 1970 through June 2000. For the period from January 1970 through December 1991, the database is based...
In the real world with reams of real historical rates of return data in a computer spreadsheet, spreadsheet. rates of return data into a column (range) into your spreadsheet, and invoke the following Table 12.3 shows a computer spreadsheet that computes everything that you did in this chapter.
How To Build Your Own Spreadsheet Model. 1. Open the Basics Spreadsheet and Add Rows. Open the spreadsheet that you created for Project NPV - Basics and immediately save the spreadsheet under a new name using the File Save As command. Select A16 A21 and click on Insert Row. Select A23 A29 and click on Insert Row. FIGURE 10.3 Spreadsheet for Cash Flow Forecasts.
How To Build Your Own Spreadsheet Model. 1. Open the Forecasting Cash Flows Spreadsheet and Add Rows. Open the spreadsheet that you created for Project NPV - Forecasting Cash Flows and immediately save the spreadsheet under a new name using the File Save As command. Select A32 A34 and click on Insert Row. Select A51 and click on Insert Row. Select A53 A54 and click on Insert Row. FIGURE 10.5 Spreadsheet for Cash Flow Forecasts.
Prosperous Pauline is about to make an offer to buy a home. The list price is 235,000 but Pauline will make an offer of 209,500. She plans to make a 21,500 down payment with the balance financed with a 30-year mortgage at 8.4 annual interest. Pauline wonders what her monthly principal and interest payment would be under these circumstances. Use an Excel spreadsheet (and the PMT function) to help you answer the questions below.
How To Build This Spreadsheet Model. 1. Open the Basics Spreadsheet and Add Rows. Open the spreadsheet that you created for Project NPV - Basics and immediately save the spreadsheet under a new name using the File Save As command. Select the cell A15 and click on Insert Row. Select the range A5 I5, click on Edit Cut, select the cell A15, and click on Edit Paste.
Start with the Project NPV - Basics spreadsheet. Move the Unit Sales line out of the Key Assumptions area, since that is what we are going to solve for. Restructure the Unit Sales forecast to depend on the Sales Growth Rate, which we be a key variable. Structure the Sales Grow Rate forecast over the entire to period to depend on how fast the growth rate is initially. This will make it easy to use Solver and to create a Data Table later on. Project the cash flows of the project and calculate the NPV. Use Solver to determine the amount of year 1 unit sales that will cause the NPV to equal zero, when the sales growth rate is at the base case level of 5 per year. Use Solver to determine the sales growth rate that will cause the NPV to equal zero, when the year 1 unit sales is at the base case level of 39,000. Create a two-variable data table using two input variables (year 1 unit sales and sales growth rate) and the output variable NPV. Use the data table to create a...
Standard spreadsheet packages such as Excel can perform multiple regression analysis and are sufficient for most routine tasks. A regression equation can be calculated via menus and dialogue boxes and no knowledge of the formulae is required. However, when problems such as autocorrelation (see below) are present, specialised packages such as TSP, Microfit or Stata are much easier to use and provide more comprehensive results.
Budgeting, financial forecasting, and planning can utilize a microcomputer with a powerful spreadsheet program, templates, or add-ins. In addition, more and more companies are developing computer-based models for planning and budgeting, with powerful yet easy-to-use modeling languages such as Budget Maestro.
Develop a spreadsheet model of investment and consumption on a year-by-year basis over an entire lifetime. You need to choose how to divide your salary between providing consumption now vs. savings (to provide for consumption in the future). Your savings are put in a tax-deferred retirement account and each year you need to decide what percentage to contribute to it (or withdraw from it). You avoid paying taxes on contributions to the retirement fund, but you suffer paying taxes when you withdraw from it. Salary less contributions plus withdraws gives you taxable income upon which you pay taxes. The after-tax income plus social security benefits provide for consumption each year. You need to choose what percentage of your retirement funds to invest in the risky diversified fund. The rest of your retirement funds will be invested in the riskfree money market fund and will grow at the riskfree rate. Investing in the risky diversified fund will give you a higher...
How To Build This Spreadsheet Model. 1. Collect Historical Stock Price Data. Go to Yahoo Finance (quote.yahoo.com), enter AMZN (the ticker symbol for Amazon.com) in the Get Quotes box, click on Chart, at the bottom of the page click on Other historical quotes, adjust the start date if you want more than three months of data, click on Download Spreadsheet Format, and save the csv file. Launch Excel and open the csv file. FIGURE 17.10 Spreadsheet Model of Binomial Option Pricing - Full-Scale Real Data -Call. 4. Start with the Risk Neutral Spreadsheet and Freeze Panes. Open the spreadsheet that you created for Binomial Option Pricing - Risk Neutral and immediately save the spreadsheet under a new name using the File Save As command. It will be helpful for navigation purposes to lock in both column titles and row titles. Select cell G16 and click on Window Freeze Panes. FIGURE 17.11 Spreadsheet of Binomial Option Pricing - Full-Scale Real Data - Call...
How To Build This Spreadsheet Model. 1. Start with the Basics Spreadsheet, Add A Row, and Enter The Dividend Yield. Open the spreadsheet that you created for Black Scholes Option Pricing - Basics and immediately save the spreadsheet under a new name using the File Save As command. Add a row by selecting the cell A9 and clicking on Insert Rows. Enter the dividend yield in cell A9.
Open the spreadsheet that you created for Black Scholes Option Pricing - Basics and immediately save the spreadsheet under a new name using the File Save As command. Add three rows by selecting the range A9 A11 and clicking on Insert Rows. Then delete five rows by selecting A20 A24, clicking on Edit Delete , selecting Entire Row, and click on OK. Relabel the input labels in the range A4 A11 and enter the new inputs values into the range B4 B11. Lock in the first eleven rows as titles by selecting cell A12 and clicking on Window Freeze Panes.
Having introduced the use of variables and control structures in programming, this section discusses how VBA macros can obtain inputs directly from the spreadsheet and how results can be returned. It concentrates on the communication between macros and the spreadsheet. In most cases, a subroutine consists of three parts input of data, calculations (or manipulation of inputs), then output of results. Writing the VBA code for the calculations usually involves conventional programming techniques. The novel aspect of VBA programming is the interaction with the spreadsheet. Taking the three parts separately Input can be from spreadsheet cells or directly from the user via dialog boxes, with the input stored in variables. In contrast, Factorial2 takes its input from a spreadsheet cell (B5) and returns the factorial answer to another cell (C5) 'gets number from spreadsheet, uses Excel Fact function, returns answer to spreadsheet Dim fac, num 'gets number from InputBox, calculates factorial...
Required Use a spreadsheet to prepare a report describing profit earned by The Book Wermz for September. The spreadsheet should contain the following heading The merge and center button E can be used to center the heading in the first three rows of the spreadsheet. Select the cells that will contain the heading, and then click the merge button to combine these cells.
For the accounting and finance executive, spreadsheet software has had the greatest impact on productivity. Imagine a company controller who has been asked to prepare the budget for the coming year. The company manufactures in over a thousand products with special pricing depending on volume. The controller not only has to make assumptions about material costs, which might change over time, but also has a history of expense levels that must be factored into the analysis. Using pencil and paper (usually a columnar pad), the controller calculates and prepares all of the schedules necessary to produce the final page of the report, which contains the income statement and cash flow. Confident that all calculations are complete, the controller presents the findings to management, only to be asked to modify some of the underlying assumptions to reflect an unexpected change in the business. As a result, the controller must go back over all of the sheets, erasing and recalculating, then...
Spreadsheet software is one of the most commonly used technologies for collecting, computing, and displaying data. Spreadsheets were developed as a way of organizing numeric data, by using an electronic table of rows and columns, and of creating business models, graphs and charts, and reports for financial, statistical, or other data. SPREADSHEET PACKAGES Spreadsheet packages are available for mainframes, minicomputers, and personal computers. Versions are available for various operating systems, including DOS, Windows (various versions), Macintosh, Unix, Java, Linux, and VMS. Spreadsheet capabilities are included in financial management packages as well as in integrated software packages. Dozens of spreadsheet software packages are available to users. The best-known packages are Microsoft Excel, Lotus 1-2-3, and Corel's Quattro Pro. These three packages are included as parts of integrated pack ages or suites from Microsoft Corporation, Lotus Development Corporation (owned by IBM...
Since their first appearance in the late 1970s spreadsheets gained a remarkable popularity in business as well as research and education. They are the most common software managers, researchers and traders utilize for data analysis, quantitative modelling and decision support. Przasnyski and Seal (1996) find that most of the time series modeling done in the business world is accomplished using spreadsheets. Further research work suggests that those users have become so proficient with spreadsheets that they are reluctant to adopt other software solutions. Not even a higher suitability for specific applications is appealing then (Chan and Storey, 1996). An analysis of XploRe download profiles conducted in a data mining framework confirmed our perception of the statistical software market. A stunning majority of users are using Excel for statistical analysis (Sofyan and Werwatz, 2001). A major difference between a spreadsheet application and statistical programming languages is the...
In this book I assume that you know the basics of finance and can solve by hand most of the problems for which you will be creating models. I also assume that you are familiar with the basics of Excel and have experience creating spreadsheet solutions to at least simple problems. You do not need to have knowledge of Excel's advanced features or of modeling I will cover both in detail. You also do not need to have any knowledge of VBA. A key objective of the book is to teach you VBA and modeling using VBA from scratch by way of an easy and effective method.
Chapter 7 on Discounted Cash Flow Analysis has been extensively rewritten with careful attention to improving and clarifying the computation of project cash flows. This material has been expanded and enhanced with several worked examples. We also provide a simple spreadsheet model that shows students how spreadsheets can enhance and simplify cash flow analysis and capital budgeting decisions. Part VI on Financial Planning contains significant new material. Chapter 17 on Financial Statement Analysis now discusses the measurement and interpretation of economic value added. An Excel spreadsheet with a long-term financial plan has been integrated into Chapter 18. Chapter 19 on Working Capital Management and Short-Term Planning similarly contains a cash management spreadsheet.
There are two aspects to designing a model. One is to sketch the steps that Excel or VBA will have to follow to solve the problem. For simple models, you may want to write down only the broad steps or perhaps even do it in your head. For more complex problems, however, you should work on paper and use a degree of detail that suits your level of experience and the complexity of the problem. The less experience you have, the more detailed the sketch should be. Once again, remember that this may seem like a waste of time, but ultimately it will save you time compared to plunging into your spreadsheet or VBA program without such a sketch of the model. The other aspect of design is planning how the model will be laid out in Excel or VBA. Are you going to do the entire model in one spreadsheet (or VBA module) or split it into several spreadsheets (or VBA modules or procedures) Editing an Excel or VBA model is easy. So you do not have to decide every detail ahead
Many financial analysis procedures involve sets of numbers for example, a portfolio of securities at various prices and yields. Matrices, matrix functions, and matrix algebra are the most efficient ways to analyze sets of numbers and their relationships. Spreadsheets focus on individual cells and the relationships between cells. While you can think of a set of spreadsheet cells (a range of rows and columns) as a matrix, a matrix-oriented tool like MATLAB manipulates sets of numbers more quickly, easily, and naturally.
The contents of one or more cells can be referenced in another cell. To reference a cell, enter the equal sign followed by the cell being referenced. For example, entering A1 in cell B1 will copy the contents of cell Al in cell B1. If the contents of cell A1 are changed, these changes also will appear in cell Bl. A common use of cell referencing is to calculate totals from data in a series of cells. For example, the following spreadsheet contains sales data for the first three months of a year. The total appears in cell B5. To calculate the total, you would enter a formula in cell B5. The formula would be B2+B3 + B4.
There are many ways of designing spreadsheets with no right or wrong answer. Whilst the previous section includes practices that many users would agree produce weak and error-prone models, there is no accepted method adopted by the majority of spreadsheet users. The basic method used on this book is one that I have developed over the last 15 years. I have found that it works so that models can be developed rapidly while reducing the incidence of errors. Some aspects are
It is easy to list examples of where technological innovation has likely reduced the cost of delivering transactions-based lending technologies. For instance, communications and software innovations have likely reduced the cost of monitoring accounts receivable, the essential collateral component of two important lending technologies - factoring and asset-based lending. Equipment lending offers another example. Some equipment liquidations are now conducted by liquidators who use online auctions. Many of these liquidators also act as equipment appraisers relying on information compiled in databases from their liquidation activities.6 And, of course, the canonical example cited in the academic literature is the introduction of spreadsheet software used to spread and analyze borrower financial statements. Somewhat surprisingly, however, there is virtually no direct evidence presented in the literature on the magnitude of the cost savings from these innovations nor is there evidence on...
What would you estimate to be the amount of data such that programming would be preferred over spreadsheets 19 Under which situations would you recommend replacing the files with a database Next, suppose you are ordered to take over either the spreadsheet or the source code from somebody who has left the company and has documented nothing20. Now which approach would you favor 19 Programs vs. Spreadsheets Portfolios can have hundreds of correlated variables and thousands of deals. On the other hand, you may need to provide an answer by 2 p.m. for a risk analysis depending on a few variables for a deal perhaps about to go live.
Make a list of the tabular, graphical, and other outputs the model needs to create. To some extent, these should be driven by the decisions that will be made based on them. One advantage of Excel is that a lot of the output can be just printouts of your spreadsheets, provided the spreadsheets have been laid out properly. If you plan ahead and lay out your spreadsheets with the outputs in mind, you will save yourself a lot of time later on.
With the use of a spreadsheet program, budgeting can be an effective tool to evaluate what-if scenarios. This way the manager should be able to move toward finding the best course of action among various alternatives through simulation. If the manager does not like the result, he or she may alter the contemplated decision and planning set. Specialized software that is solely devoted to budget preparation and analysis also exists.
To confirm the table's last row, which gives the perpetuity's net present value as 20, you can The Shortcut spend from here to eternity to add up the infinite number of terms. But if you use a spreadsheet Perpetuity Formula. to compute and add up the first 50 terms, you will get a PV of 19.83. If you add up the first 100 terms, you will get a PV of 19.9986. Trust me that the sum will converge to 20. This
Instead of the spreadsheet, write a program in your favorite computer language along with file inputs to perform the same steps as in Exercise Part 1. Document your source code16 by clearly writing at the top what it is you are doing, in good English with complete sentences. If you skipped the memo and verbal communication, it's time to bite the bullet17. Print out your report and graph18. 18 Graphs and Programmers Can you (ahem ) produce graphs using your compiled code It is hard to describe the frustration with systems groups that as a matter of principle only work with compiled code and hate spreadsheets, but have trouble producing reasonable reports and graphs. You have just carried out the same exercise in production mode , as opposed to the spreadsheet prototype mode .
If you've gotten this far in Chapter 1, you've probably put together a few basic Excel spreadsheets. You'll be doing lots more in the rest of this book, and you'll be amazed at the insights Excel gives you over even complicated financial problems. Put all the variables which are important (the fashionable jargon is value drivers ) at the top of your spreadsheet. In the Saving for College spreadsheet of page000, the three value drivers the interest rate, the annual deposit, and the annual cost of college are in the top left-hand corner of the spreadsheet the top left-hand corner of the spreadsheet
The ability to create formulas in many cells by copying them from one cell is one of the most powerful and timesaving features of Excel. It is no exaggeration to say that without this feature, spreadsheet programs like Excel would never have gained the popularity that they have. It is that important.
After leaving college, I joined the finance department of a large energy company. I spent my first year helping to analyze capital investment proposals. I then moved to the project finance group, which is responsible for analyzing independent power projects around the world. Recently, I have been involved in a proposal to set up a company that would build and operate a large new electricity plant in southeast Asia. We built a spreadsheet model of the project to make sure that it was viable and we had to check that the contracts with the
There is no single best way to use Spreadsheet Modeling in Corporate Finance. There are as many different techniques as there are different styles and philosophies of teaching. You need to discover what works best for you. Let me highlight several possibilities 1. Out-of-class individual projects with help. This is a technique that I have used and it works well. I require completion of several short spreadsheet modeling projects of every individual student in the class. To provide help, I schedule special help lab sessions in a computer lab during which time myself and my graduate assistant are available to answer questions while students do each assignment in about an hour. Typically about half the questions are spreadsheet questions and half are finance questions. I have always graded such projects, but an alternative approach would be to treat them as ungraded homework. 2. Out-of-class individual projects without help. Another technique is to assign spreadsheet modeling projects...
Unfortunately, using names in formulas is not as useful as it appears in the beginning. One of the reasons that spreadsheet programs like Excel have become so popular is that if you create a complex formula in a cell using the a proper mix of relative, mixed, and absolute cell references, you can copy it into hundreds of cells where it will work properly the program will make the necessary adjustments to the formula as it is pasted into the different cells. Because names can be used primarily in place of absolute references, they cannot help much to make these complex formulas easier to read or check. As you will find out, VBA can be of great help in situations where formulas get unwieldy.
Figure 2-1 shows how 1 (or any other lump sum) grows over time at various interest rates. We generated the data and then made the graph with a spreadsheet model in the file Ch 02 Tool Kit.xls. The higher the rate of interest, the faster the rate of growth. The interest rate is, in fact, a growth rate If a sum is deposited and earns 5 percent interest, then the funds on deposit will grow at a rate of 5 percent per period. Note also that time value concepts can be applied to anything that is growing sales, population, earnings per share, or your future salary.
Spreadsheet Exercise 844 Of course, practice is essential for students' learning of managerial finance concepts, tools, and techniques. To meet that need, the book offers a rich and varied menu of homework assignments short, numerical Warm-Up Exercises a comprehensive set of Problems, including more than one problem for each important concept or technique and now also including personal finance problems an Ethics Problem for each chapter a Chapter Case a Spreadsheet Exercise a Group Exercise an online Web Exercise and at the end of each part of the book, an Integrative Case. In addition, most of the end-of-chapter problems are available in algorithmic form in myfaancelab . These materials (see pages xi through xii for detailed descriptions) offer students solid learning opportunities, and they offer instructors opportunities to expand and enrich the classroom environment. Revised Spreadsheet Exercise and Group Exercise Shifted computational emphasis to calculator and spreadsheet use,...
Gral part of any fundraising program, but especially for a special event. This chapter has eight exhibits 3.1 is a sample revenue and expense budget 3.2 is an example of how to price a special event 3.3 describes the estimating attendance formula 3.3a is an input sheet for calculating the results of Exhibit 3.3 and is best utilized by loading into a spreadsheet application from the CD that comes with this book, inputting the numbers applicable to the special event, and then printing the results 3.4 is an underwriters and sponsors matrix 3.5 is an attendance matrix that uses the numbers from Exhibit 3.3a to generate a minimum reservation total 3.6 is a list of database fields that the nonprofit can use to build a simple database for the special event 3.7 is a list of revenue and expense budget line descriptions and 3.8 shows the results of the database fields in a report form. The conclusion shows a refinement of METT, a subset of specific tasks to do for week 1.
The rate r is often called the bond's yield to maturity. In our case r is 4.8 percent. If you discount the cash flows at 4.8 percent, you arrive at the bond's price of 1,095.78. The only general procedure for calculating the yield to maturity is trial and error, but spreadsheet programs or specially programmed electronic calculators will usually do the trick.
In the previous spreadsheet you saw that Sally and Dave's net income was 8,050. In this section you'll see that the cash flow produced by the condo is much more that this amount. It all has to do with depreciation Because the depreciation is an expense for tax purposes but not a cash expense, the cash flow from the condo rental is different. So even though the net income from the condo is 8,050, the annual cash flow is 18,050 you have to add back the depreciation to the net income to get the cash flow generated by the property.
Everyone peruses the list of potential leaders for the event and prepares a list of people that the event chairperson can recruit for the event team. The computer programming is set up a software program is selected, or the agency uses existing database or spreadsheet programs to design their own programs (see examples in Chapter 3).
Section has a number of financial calculators, spreadsheets, and descriptive materials that cover a wide range of personal finance issues. Another good place to look is Quicken's web site, http www.quicken.com. Here you will find several interesting sections that deal with a variety of personal finance issues. Within these sections you will find background articles plus spreadsheets and calculators that you can use to analyze your own situation.
Wilmott's newest columnist is no stranger to the website, where he is numbersix. Ayache's firm ITO33 produces cutting-edge convertible bond software. The equity-to-credit problem provided the focus of Ayache's talk, which provided a rigorous examination of traditional models, where equity level determines the intensity of default. Utilizing active spreadsheets and real contracts, Ayache demonstrated his approach to optimal hedging.
Q 2.47 At what interest rate would you be indifferent between the first and the second choice above (Hint Graph the NPV of the second project as a function of the interest rate. A spreadsheet would come in handy.) Q 2.60 Assume you are 25 years old. The IAW insurance company is offering you the following retirement contract (called an annuity) Pay in 2,000 per year for the next 40 years. When you reach 65 years of age, you will receive 30,000 per year for as long as you live. Assume that you believe that the chance that you will die is 10 per year after you will have reached 65 years of age. In other words, you will receive the first payment with probability 90 , the second payment with probability 81 , and so on. Assume the prevailing interest rate is 5 per year, all payments occur at year end, and it is January 1 now. Is this annuity a good deal (Use a spreadsheet.)
If you are doing regular economic research, we urge you to test drive a number of the sites to find the format that works best for you. Some sites allow data to be downloaded into an Excel spreadsheet, while others present the data only in a plaintext format. Your needs will dictate which site to choose among those with similar data.
Economagic gathers and manipulates statistics from government agencies in the United States, Canada, and Japan, as well as trade associations and private companies. The data can be displayed as plain numbers, in charts, or in spreadsheets, where the data can be analyzed and downloaded. Most of the data on Economagic is available at no charge, but some features, such as forecasting, are only available to subscribers. Most of the data, such as employment statistics from the Bureau of Labor Statistics, interest rates from the Federal Reserve System, and building permit data from the Census Bureau, are available on other sites discussed in this chapter. The advantages of the Economagic site are having easy access to more than 100,000 data files on a single site, the capability to view them in multiple formats, and the option to download information directly into a spreadsheet.
A little thought about the previous spreadsheet reveals that we've left out an important factor The value of the condo at the end of the 10-year horizon. In finance an asset's value at the end of the investment horizon is called the asset's salvage value or terminal value. In the above spreadsheet we've assumed that the terminal value of the condo is zero, but this assumption implausible.
Even if you use software such as Quicken by Intuit or Microsoft Money (covered in more detail in Chapter 2, Getting the Figures to Match ), you might find it helpful to keep a separate income and expense tracking document. You can run reports in your money management program that show you how you're doing with your financial goals, but the act of hand-writing or keying in the figures in a separate document can help you see the big picture more easily. I use QuickBooks (the business version of Quicken) to record all my transactions and balance all of my accounts electronically, but I also use another spreadsheet to list all of the data I want to see together, not all of which has a place in QuickBooks where it can be entered. I've tried to create just the right custom report in QuickBooks to eliminate the need for this spreadsheet, but so far nothing has worked as well as my trusty Excel document. You can create your spreadsheet by hand on graph paper, in a ledger book purchased from...
The availability of IT to support knowledge work exploded over the last two decades of the 20th century with the rise of the personal computer. Word processing, spreadsheet applications, personal databases and other personal management software tools were available for the first time to individuals and led to exponential growth in the productivity of knowledge work. The PC together with PC software provided the appropriate tools that enabled individuals to organize their personal work better and perform knowledge-based work and tasks much more efficiently without being limited by the restricted resources of a computer that is used by many people at the same time. From a PC it was then possible to access the corporate ERP system, which supported back-end integration of business processes - for example, in supply chain management - or integrated accounting and purchasing, or integrated selling and production planning processes. Employees were then able on their PCs - without using a...
My special thanks go to all members of my book team whose vision, creativity, and ongoing support helped me to engineer all elements of the Teaching Learning System to Michael J. Woodworth of Purdue University for the chapter-opening vignettes and the In Practice Focus on Practice, Ethics, and Global Focus boxes to Mehdi Salehizadeh of San Diego State University for help in revising the chapter on International Finance to Steven Lifland of High Point University for the new personal finance problems, and for updating the in-chapter spreadsheet examples, Spreadsheet Exercises, Warm-Up Exercises, Group Exercises, and Web Exercises to Daniel J. Borgia of Florida Gulf Coast University for revising the Test Banks and the PowerPoint Lecture Presentations to Thomas Kreuger of the University of Wisconsin at La Crosse for updating the Instructor's Manual and Study Guide to Michael Seiler of Hawaii Pacific University for updating the Case Studies in Finance on the book's website and to Nikhil...
It is relatively easy to solve for i numerically when the cash flows are lump sums or annuities. However, it is extremely difficult to solve for i if the cash flows are uneven, because then you would have to go through many tedious trial-and-error calculations. With a spreadsheet program or a financial calculator, though, it is easy to find the value of i. Simply input the CF values into the cash flow register and then press the IRR key. IRR stands for internal rate of return, which is the percentage return on an investment. We will defer further discussion of this calculation for now, but we will take it up later, in our discussion of capital budgeting methods in Chapter 7.8
5Equation 3-3a is built into all financial calculators, and it is very easy to use. We simply enter the rates of return and press the key marked S (or Sx) to get the standard deviation. Note, though, that calculators have no built-in formula for finding S where unequal probabilities are involved there you must go through the process outlined in Table 3-3 and Equation 3-3. The same situation holds for computer spreadsheet programs.
To solve problems such as these, we can use an equation that is built into financial calculators and spreadsheets Unless you use a financial calculator or a spreadsheet, the only way to solve for i is by trial-and-error. However, with a financial calculator, you simply enter the values for the four known variables (N 36, PV 1988.13, PMT 78, and FV 0), and then hit the key for the unknown fifth variable, in this case, I 2. Since this is an 6This is the equation for an ordinary annuity. Calculators and spreadsheets have a slightly different equation for an annuity due. It is worth pointing out that the left side of the equation can not equal zero if you put both the PV and PMT as positive numbers (assuming positive interest rates). If you do this by mistake, most financial calculators will make a rude beeping noise, while spreadsheets will display an error message. In an Excel spreadsheet, you would use the same RATE function that we discussed earlier. In this example, enter 36 for...
FIGURE 2.3 Spreadsheet for Annuity - System of Four Annuity Variables. How To Build Your Own Spreadsheet Model. 1. Start with the Present Value Spreadsheet, Then Insert and Delete Rows. Open the spreadsheet that you created for Annuity - Present Value and immediately save the spreadsheet under a new name using the File Save As command. Select the range A7 A17 and click on Insert Rows. Select the cell A25, click on Edit Delete, select the Entire Row radio button on the Delete dialog box, and click on OK. Select the range A26 A27, click on Edit Delete, select the Entire Row radio button on the Delete dialog box, and click on OK.
Macros form an important part of an experienced user's palette for two main reasons they are an excellent way to control repeated calculations, and they can be written to assist third-party users who might have less familiarity with spreadsheets. From our perspective, the main objective of mastering VBA is to be able to automate calculations using functions and macros. Spreadsheet models are more robust if complicated sequences of calculations are replaced by function entries. Excel provides an excellent range of functions and VBA can be used to extend that range. In addition, VBA macros are a useful way to produce charts and to automate repeated operations, such as simulation. In both instances, the resulting procedures are programs in VBA, but in the first case, they are user-defined functions and in the second, macros (or subroutines). Whilst the code is largely common to both types of procedure, this chapter focuses on writing macros, whereas Chapter 4 deals solely with...
We begin by calculating the (nominal) discount rate from the inflation rate and the real discount rate. The rest of the net present value calculation is the same as the Net Present Value -Constant Discount Rate spreadsheet. FIGURE 4.1 Spreadsheet for Real and Inflation - Constant Discount Rate. FIGURE 4.1 Spreadsheet for Real and Inflation - Constant Discount Rate.
Excel provides many worksheet functions, which are essentially calculation routines that have been coded up. They are useful for simplifying calculations performed in the spreadsheet, and also for combining into VBA macros and user-defined functions (topics covered in Chapters 3 and 4). On clicking OK, the Formula palette appears providing slots for entering the appropriate inputs, as in Figure 2.2. The required inputs can be keyed into the slots (as here) or 'selected' by referencing cells in the spreadsheet (by clicking the buttons to collapse the Formula palette). Note that the palette can be dragged away from its standard position. Clicking the OK button on the palette or the tick on the Edit line enters the formula in the spreadsheet.
Enter transactions 1 through 9 into the five columns. Total each column and verify that the balance sheet does balance. Note that FUND BALANCES can be used in the same manner as OWNERS' EQUITY for a commercial firm. Prepare a simple balance sheet from the totals of your spreadsheet.
Economists and all business analysts that use economic reasoning rely on a mental library of analytical tools. These tools are not well implemented in any single software environment. Therefore, economists typically use a range of tools, such as spreadsheets, statistical software, graphics utilities, and different modeling languages to complete their tasks. MATLAB does not provide a complete environment for all these tasks. However, it provides much more power to perform analysis than spreadsheets, statistical software, and modeling languages alone, and can often accomplish tasks in one software environment that might otherwise take two or three.
For model building and risk management, you need to know how to program fluently in at least one language (C, C++, or Fortran)6. No exceptions. Fluent means that you have had years of experience and you do not make trivial mistakes. Prototyping is important and extremely useful. Prototyping can be done with spreadsheets (also Visual Basic), or with packages like Mathematica, PV Wave, Matlab, etc. However, prototyping is not a replacement for serious compiled code. Knowledge of other aspects of computer science can also be useful (GUIs, databases and SQL, hardware, networking, operating systems, compilers, Internet, etc).
In general, a subroutine gets its input either from spreadsheet cells or from the user (via the screen) and its outputs are either pasted into the workbook or displayed to the user. Two useful display screens are generated by the VBA built-in functions, MsgBox() and InputBox(). The simpler of these, MsgBox(), displays a message on screen then pauses for user response, as is illustrated below
We begin by calculating the (nominal) discount rate for each period from the inflation rate in each period and corresponding real discount rate. The rest of the net present value calculation is the same as the Net Present Value - General Discount Rate spreadsheet. FIGURE 4.2 Spreadsheet for Real and Inflation - General Discount Rate. FIGURE 4.2 Spreadsheet for Real and Inflation - General Discount Rate.
We would use a high value of n and carry out the calculations on a spreadsheet or using appropriate software. However, to illustrate the procedure manually, let us work with a value of n 10. This value gives us nine (i.e., n - 1) tail VaRs, or VaRs at confidence levels in excess of 95 . These VaRs are shown in Table 3.1, and vary from 1.6954 (for VaR at a confidence level of 95.5 ) to 2.5758 (for VaR at a confidence level of 99.5 ). Our estimated ETL is the average of these VaRs, which is 1.9870.
Arrange columns in a spreadsheet, corresponding to the balance sheet equation using these balance sheet accounts Cash, Accounts Receivable, Prepaid Rent, Supplies, Property Plant and Equipment, Accumulated Depreciation, Accounts Payable, Interest Payable, Mortgage Payable, Common Stock, Retained Earnings. Enter transactions 1 through 10 into the columns. Total each column and verify that the balance sheet equation does indeed balance. b. Prepare a classified balance sheet, using the column totals from your spreadsheet.
The better organized a model is, the easier it is to follow. For example, the inputs for a model should be clustered (and possibly color-coded) in one section of the spreadsheet or in a separate input sheet (in large models). Similarly, wherever possible the outputs should be clustered as well. Modularize large models by putting different types of analysis or computations on different sheets. In large models it may also be useful to provide the key outputs in a highlighted summary section.
The first statement is Objects come in collections. For example, the Workbooks collection consists of all open workbooks, similarly the Worksheets (or Sheets) collection (all the sheets in a workbook), the Scenarios collection (all scenarios associated with a particular sheet), the Charts collection (all the charts on a sheet), etc. However, some objects come as singular objects (i.e. collections of one member only), for example, Excel has only one Application object (itself) and for any cell on the spreadsheet there is only one Font object (although this object has several properties, such as Name, Size, etc.). These are singular objects that are referenced directly, e.g. by simply writing Application. or Font. (the object followed by a 'fullstop'). Individual objects in collections are referenced by indexing the collection either by number (1, 2, 3 ) or by name, e.g. Workbooks(l). or Sheets( inputs ). The Range object is by definition a singular object, but notice that it is...
This book is aimed at the analyst who will adopt the second and more rewarding alternative, the relentless pursuit of accurate financial profiles of the entities being analyzed. Tenacity is essential because financial statements often conceal more than they reveal. To the analyst who pursues this proactive approach, producing a standard spreadsheet on a company is a means rather than an end. Investors derive but little satisfaction from the knowledge that an untimely stock purchase recommendation was supported by the longest row of figures available in the software package. Genuinely valuable analysis begins after all the usual questions have been answered. Indeed, a superior analyst adds value by raising questions that are not even on the checklist.
In the financial modelling context, much of the code you will want to write will have as its objective the control of numerical calculations. This involves assigning variables, applying proper control structures on the flow of processing and reporting results. These tasks are common to programming whatever the language. VBA programs differ only in that they often originate from spreadsheet models, where tasks are performed by menu commands and where crucial calculations are in cell formulas, many involving Excel functions. Fortunately, it is easy to incorporate Excel functions in VBA code. Rather than replace the spreadsheet model, VBA programs increment or enhance their functionality. We briefly review a few relevant aspects of programming concerning the use of variables, in particular array variables, structures that control the flow of processing and the use of Excel functions in VBA.
Microsoft's Excel spreadsheet is used here to illustrate the ease of calculating the present value on a computer, but there are other software programs that can also be used. Worksheet 8.3 on page 37 illustrates the determination of the present value with an example. Using Microsoft's Excel spreadsheet, click on f*, which is on the top row of the toolbar. A list of functions pops up. Highlight financial in the left-hand box and PV in the right-hand box and click OK. A box comes up with five rows, as shown below.
The ability to see the precedents of a cell can be useful in finding the source of the error in the cell. Similarly, if you are going to make a change in a cell, it is useful to first look at what cells will be affected by it. When using these tools it helps to zoom out to see more of the spreadsheet at a time. Remember that you can have your spreadsheet show the formulas instead of results by selecting Tools O Options, clicking the View tab, and then selecting Formula under Window options. (You can also switch between the regular view and formula view of the worksheet by pressing the shortcut key combination Ctrl+ ) Sometimes it is helpful to open a second window to see both the normal and formula view of the worksheet at the same time (one above the other).
Although most financial statements forecasting models are structurally similar, they have to be customized for each application. It is essential to understand up front why the model is being created, what outputs are expected from it, and what types of decisions will be based on those outputs. As with all models, it does not pay to get started unless you know where you are going and why. To take one simple example if you know what kind of outputs will be required, you can design your spreadsheets accordingly, and when the time comes to create reports you will not have to redo many things.
Solving such trees is a difficult problem, because your optimal strategy next year does not just depend on that year, but on future years. In fact, in our previous examples, I have cheated in making it too easy for you I had told you the strategy at each node. Real option problems are difficult to value, precisely because your optimal strategy at any node can depend both on the current state of your firm and on all future possible scenarios. The web chapter on real options explains how you can solve such problems more systematically. Decisions are often worked out backwards you start with the final year and work your way towards today. Another important tool explained in the web chapter is a form of automated scenario analysis called Monte-Carlo simulation, in which you can specify a whole range of possible future scenarios. The spreadsheet itself can then compute the expected outcome in many different scenarios using different decision strategies that you would specify.
Microsoft's Excel spreadsheet is used here to illustrate the ease of calculating the future value of an annuity on a computer. There are any number of other software programs that can also be used. Worksheet 9.4 illustrates the determination of the future value of both an ordinary annuity and an annuity due using the same example used in the financial calculator worksheet.
Build the model on a copy of the workbook from Model 3 and add to it the spreadsheet for the statements of cash flows. In the main spreadsheet (see Figure 6.8), set up the headings for 2003-2006 next to the columns for the historical data and a column for forecasting factor, the ratio, or growth rate you will use to forecast a particular line.
Click the F8 function key (or if visible, the small Step Into button) to move to the next line of code and so on. If you want to see the actions in the spreadsheet window, reduce the size of the VBE window so you can see the Excel sheet underneath or use the Alt+Tab combination to move between windows and watch the macro working line-byline in the Code window. Click F8 (or the Step Into button) repeatedly to step through the macro. There is a Step Out button to escape from step mode. Alternatively, the VBE Editor Window command bar Run menu has a Reset choice which gets you out of step or debug mode.
Presentation graphics software is used to create slide presentations. These presentations can include a variety of media through which information can be presented to an audience, such as text, graphs, pictures, video, and sound. Special effects are also available, meaning animation can be incorporated as the system transitions from one slide to the next. Slides can be printed, in black-and-white and color, for use on overhead projectors. Alternatively, the computer can be directly connected to a system for projection onto a screen or a television monitor, allowing the presenter to utilize the software's animation and sound features. Most of the software comes equipped with various prede-veloped background formats and clip art to help simplify the process of creating the presentation. Also, these software packages allow the user to import both graphs and text from other software packages, such as word processing and spreadsheets.
FIGURE 6.5 Spreadsheet Model of Bond Valuation - System of Five Bond Variables. How To Build This Spreadsheet Model. 1. Start with the Basics Spreadsheet and Delete Rows. Open the spreadsheet that you created for Bond Pricing - Basics and immediately save the spreadsheet under a new name using the File Save As command. Delete rows 27 through 29 by selecting the range A27 A29, clicking on Edit Delete, selecting the Entire Row radio button on the Delete dialog box, and clicking on OK. Then repeat this procedure to delete rows 14 through 25 and repeat this procedure again to delete rows 10 through 11. This places the five bond variables in rows 8 through 12, highlighted with purple labels above.
We discussed continuous compounding in Chapter 2. As explained there, the continuously-compounded return is calculated using the Ln function. For reasons that are beyond the purview of this book, the continuously-compounded return is the only consistent method of computing return statistics (by consistent we mean two things there's a theory behind the numbers, and this theory gives the same results whether you're computing the annual statistics from daily, weekly, or monthly data). In the spreadsheet below, we've computed the continuously-compounded return statistics for McDonald's.
Ratio Analysis Look under Valuation and download the Profitability spreadsheet for Southwest Airlines (LUV) and Continental Airlines (CAL). Find the ROA (Net ROA), ROE (Net ROE), PE ratio (P E-High and P E-low), and the market-to-book ratio (Price Book-high and Price Book-low) for each company. Since stock prices change daily, PE and market-to-book ratios are often reported as the highest and lowest values over the year, as is done in this instance. Look at these ratios for both companies over the past five years. Do you notice any trends in these ratios Which company appears to be operating at a more efficient level based on these four ratios If you were going to invest in an airline, which one (if either) of these companies would you choose based on this information Why
One nice thing about spreadsheets is that you can use long descriptive labels to describe most variables and their corresponding formulas. However, some finance formulas are complex enough that they really require mathematical notation. When this happens, I provide alternative notation versions that match the notation of all popular corporate finance textbooks. The spreadsheet below shows the symbols that are used in all notation versions. I have selected the notation to fill in any gaps.
Spreadsheets provide an ideal environment to calculate the excise tax due on a particular transaction or the income tax due on a particular firm in a particular year. In such cases, we simply assume that the transaction has already occurred (or the income has already been earned), and we are simply calculating the tax due.
A portfolio is a set of stocks or other financial assets. Most people don't just own one stock, they own portfolios of stocks, and the risks they bear relate to the riskiness of their portfolio. In the next chapter we'll start our economic analysis of portfolios. In this section we'll show you how to compute the mean and variance of a portfolio composed of two stocks. Suppose that between 1990-99 you held a portfolio invested 50 in GM and 50 in MSFT. Column E of the spreadsheet below shows what the annual returns would have been on this portfolio. In cells E17 E21 we calculate the portfolio return statistics in the same way we calculated the return statistics for the individual assets GM and MSFT.
Put a getmatrix command in a nearby cell of the Excel spreadsheet which includes the name of the variable in MATLAB and the range of cells allotted for the variables. Users of spreadsheets will find these data types easy to understand, display, and import into MATLAB. However, if any of the following situations exist, you may need more powerful data types Discussion Spreadsheets and Databases Spreadsheets are quite deficient in these attributes. They are inherently flat tables with no structure and with no direct connection between metadata and the data. While you can trick a spreadsheet into acting like a database, it is inherently a flat table. Relational databases are a large improvement over spreadsheets. They have structure, and a skilled user can document and enforce data integrity among related elements. However, the skill required is fairly high for such use there is a reason why DBA (database administrator) is a common acronym while SA (spreadsheet administrator) is 50 Just...
Microsoft's Excel spreadsheet is used here to illustrate the computation of the annuity amounts needed to fund the two example objectives in Worksheet 10.1, as shown in Worksheet 10.2. There are a few differences from the Excel examples in section 9. Instead of calculating the future value, in these examples you are looking for the payment amount annuity amount (PMT) that equals the future value at a particular interest rate per period for a specified number of periods. Instead of clicking FV in the right-hand box, you would highlight PMT. Thereafter, you would enter the variables as described in Worksheet 10.2. Using Microsoft's Excel spreadsheet, click on f*, which is on the top row of the toolbar. A list of functions pops up. Highlight financial in the left-hand box and PMT in the right-hand box and click OK. A box comes up with five rows, as shown below.
Using a spreadsheet, compute the minimum variance and tangency portfolios for the universe of three stocks described below. Assume the risk-free return is 5 percent. Hypothetical data necessary for this calculation are provided in the table below. See exercise 5.6 for detailed instructions. 5.6. Repeat exercises 5.2 and 5.3, but use a spreadsheet to solve for the tangency portfolio weights of AOL, Microsoft, and Intel in the three cases. The solution of the system of equations requires you to invert the matrix of covariances above, then post multiply the inverted covariance matrix by the column of risk premiums. The solution should be a column of cells, which needs to be rescaled so that the weights sum to 1. Hint See footnote 11. Using a spreadsheet, compute Exxon's beta. Then apply the Bloomberg adjustment to derive the adjusted beta.
Use the normal distribution table (Table A.5 in Appendix A) at the end of the book (or a spreadsheet function like NORMSDIST in Microsoft Excel) to calculate the fair market value of the Chrysler warrants described at the beginning of this chapter. Assume that at the time
However, as explained in the previous chapter, we can also estimate the HS VaR more directly (i.e., without bothering with the histogram) by using a spreadsheet function that gives us the sixth highest loss value (e.g., the 'Large' command in Excel), or we can sort our L P data with highest losses ranked first, and then obtain the VaR as the sixth observation in our sorted loss data.
Given the partial Obtaining It From Bond Listings spreadsheet YieldliZ.xls, do step 2 Time To Maturity. 4. Given the partial Using It To Price A Coupon Bond spreadsheet YieldcoZ.xls, complete step 4 Calculate the Price and Yield To Maturity of a Coupon Bond using the Cash Flows. 5. Given the partial Using It To Determine Forward Rates spreadsheet YieldfoZ.xls, do step 3 Forward Rates. I have made a major exception for this spreadsheet model and provided the model already built. To load the model, click on Ycdyndyn.xls. I will update this spreadsheet model each month with the latest yield curve data and make it available for free in the Free Samples section of http www.spreadsheetmodeling .com. The step-by-step instructions below explain how it you can build this model. The dynamic chart uses spinners, which are up-arrow down-arrow buttons, that allow you to advance the yield curve graph from month to month. This allows you to see a dynamic movie or animation of the yield curve over...
To perform this optimisation, Solver requires 'changing cells', a 'target cell' for minimisation (or maximisation) and the specification of 'constraints', which usually act as restrictions on feasible values for the changing cells. In Figure 3.5, we require the weights in cells I10 I12 to be proportions, so the formula in cell I10 of 1-SUM(I11 I12) takes care of this requirement. For the optimisation, the 'changing cells' are cells I11 I12, named 'change1' in the sheet. The target cell to be minimised is the standard deviation of return (I16) named 'portsd1'. There is one explicit constraint, namely that the expected return (cell I15) named 'portret1' equals the target level (in cell I5 named 'target1'). The range names are displayed on the spreadsheet extract to clarify the code subsequently developed for the macro. 'value taken from spreadsheet cell 'value taken from spreadsheet cell 'value taken from spreadsheet cell 'initial value for loop counter
Most projects last for more than four years, and, as you will see in Chapter 8, we must go through a number of steps to develop the estimated cash flows. Therefore, financial analysts generally use spreadsheets when dealing with capital budgeting projects. For Project S, this spreadsheet could be used (disregard for now the IRR on Row 6 we discuss it in the next section) In Excel, the formula in Cell B5 is B4+NPV(B2,C4 F4), and it results in a value of 78.82.4 For a simple problem such as this, setting up a spreadsheet may not seem worth the trouble. However, in real-world problems there will be a number of rows above our cash flow line, starting with expected sales, then deducting various costs and taxes, and ending up with the cash flows shown on Row 4. Moreover, once a spreadsheet has been set up, it is easy to change input values to see what would happen under different conditions. For example, we could see what would happen if lower sales caused all cash flows to decline by 15,...
Download Vertex42 The Excel Nexus Now
Free version of Vertex42 The Excel Nexus can not be found on the internet. And you can safely download your risk free copy of Vertex42 The Excel Nexus from the special discount link below.