Mastering financial modelling in microsoft excel ebook


    Editorial Reviews. From the Back Cover. A practitioner's guide to applied corporate finance Modelling in Microsoft Excel 3rd edn: A Practitioner's Guide to Applied Corporate Finance (The Mastering Series) eBook: Alastair Day: Kindle Store. Read "Mastering Financial Modelling in Microsoft Excel 3rd edn A Practitioner's Guide to Applied Corporate Finance" by Alastair Day available from Rakuten. If you are downloading the ebook, companion files can be downloaded from the digital Mastering Financial Modelling in Microsoft Excel: A Practitioner's Guide to.

    Language:English, Spanish, Arabic
    Genre:Fiction & Literature
    Published (Last):27.09.2016
    Distribution:Free* [*Register to download]
    Uploaded by: LUVENIA

    72868 downloads 133267 Views 26.44MB ePub Size Report

    Mastering Financial Modelling In Microsoft Excel Ebook

    Book: Mastering Financial Modelling in Microsoft Excel and available on a CD- ROM (or as downloads for those who download the e-book). What is. Mastering Financial Modelling in Microsoft Excel 3rd edn: A Practitioner's Guide to Applied Corporate Finance, 3rd Edition Order the eBook version instead. Mastering Financial Modelling in Microsoft Excel 3rd edn ePub eBook, 3rd Edition. By Alastair Day. $ In stock. We'll put in a special international order .

    Comprehensive tools and methods to help you build, develop and apply financial models using Microsoft Excel, enabling you to get better, more accurate results, faster. The new edition of this bestselling title begins by explaining basic modelling techniques before moving through to more complex models. The book is divided into two parts: The second part of the book shows how to build corporate financial models in Excel. The accompanying CD allows you to use and adapt templates and models. This new edition includes a reworking of the book in Excel but with older material still included , inclusion of Apple Mac, addition of specific features and end of chapter exercises. If you are downloading the ebook, companion files can be downloaded from the digital downloads section of http: Alastair Day has worked in the finance industry for 20 years in treasury and marketing functions. In he established Systematic Finance as a consultancy and financial lessor concentrating on the computer and communications industries. Mastering Financial Modelling in Microsoft Excel: Alastair L. A Practitioner's Mastering Financial Modelling in Microsoft Excel 3rd edn:

    People often grasp complex ideas more easily through pictures. For example, a cash flow model could include a cover- age graph of the cash coverage above a minimum limit. TE STING Testing is required to ensure that there are no mathematical errors and that the information flow through the model is correct.

    The calculator in Figure 2. Test data is required, which makes uses of all the buttons, inputs, frequencies and payment types. A later chapter out- lines a number of techniques for reviewing the accuracy of model output. This is simple if the author clusters all the inputs together and colour codes them. For example, if a budget model were given out to a user who then overwrote cell formulas with numbers, then the integrity of the model is threatened and one would have to begin by checking every cell for possible changes.

    This is risky since either they or their colleagues may have difficulty at some point in the future in maintaining the code. Ideally, notes should be in the model rather than on scraps of paper in a file and show: Users involved in the design process and asked for their opinions may be more enthusiastic users. The main factors are: The above 13 points will help you produce more organized work. Review some of your own models and see how many of these points you include regularly in your applications.

    Obviously, the degree of complexity affects how much you need to do. However, this represents good practice which the author has developed over a number of years. The next chapter discusses a number of features to make your models more powerful and the following chapter applies the design methodology to the original example in Chapter 1. The objective is to show how applied Excel leads to more powerful and error-free models.

    It is important to be consistent and follow a clear methodology. The stages discussed in this chapter are not exhaustive and include the following: This is not an exhaustive list, but aims to show the difference between the original and the finished model.

    The features in this chapter are: The model is Features. Each of the sections in this chapter is covered by a sheet in the model. Open the file and click along the bottom to see the progression of sheets. Fig 3. The net present value in cell C14 is gained by adding up the discounted cash flows. As you can see this is only producing a net present value based on the cash flows using the formula: This involves: The title, inputs, summary and answer are now clear in a bold typeface and the model follows a defined layout as shown in Figure 3.

    You can experiment with different custom formats where positive, neg- ative and zero are separated by semi-colons.

    Colours are in square brackets. Text is enclosed in inverted commas, e. You insert your custom format in the Type box or amend an existing format see Figure 3. Zero is a dash.

    This type of format is easy to read on laser printers whereas a minus is often hard to read on negative numbers. Accounting style format: It is best to keep the Formatting toolbar visible. Go to View, Toolbars, Formatting to show this toolbar see Figure 3. This saves always going to Format, Cells, Borders etc. Thick lines are placed around the main sections and double lines to indicate a total.

    In Figure 3. These colours are personal, but it is important to be consistent in the use of colours and formats. The author always uses blue for inputs, green or black for totals and red or black for calculated results see Figure 3.

    Colour should be used sparingly as the effect can be too garish for most tastes. The model is now organized and easier for user input than the original model. This is accessed using Data, Validation on the main menu bar see Figure 3. You can choose not to show the Input Message by deselecting the box see Figure 3. The Error Alert shows if you enter a wrong figure and will not let you proceed until you comply with the validation terms see Figure 3.

    This means that the capital value should always be a positive figure. The effect is to narrow the inputs and hopefully ensure that a user will get the correct answers.

    These are the same controls, which you also find in Access or Visual Basic. This cannot be done by validation and a different approach is needed. Validation will only permit an upper or lower value. The first stage is to insert a workings area at the bottom of the sheet and to cut and paste the discount rate into it see Figure 3. This is to ensure that the model continues to function when a control is placed at cell C7.

    While these are variables, most users do not need this detail and so these items are placed in the workings area and clearly marked. The combo box control returns a number for the index of the selec- tion. Here there are eight possible selections and the index number will be placed in cell C If you click on the Combo Box in the toolbar, you can draw a combo box in cell C7. B35 and the result should be placed in cell C The final stage is to link the discount rate cell C28 with the index cell C Since C28 will now be calculated, the colour has been changed to red to avoid confusion.

    This requires a simple function called Offset from the Lookup group, accessed by selecting Insert, Function see Figure 3. Here the exam- ple should start at cell B27 and go down by the number of rows returned by the control. You start at B27 and go down by C27 and no columns see Figure 3. This should return the discount rate to be used in the present value calculations. Note that a user could still send data to cells B27, C26 and C The combo box runs a macro or routine to update the cell, but does not protect it.

    There are other controls in the toolbox that you could use to make the inputs more intuitive. For example, spinners and scroll bars allow you to increment a value by one click and provide an input variable for specify- ing the click value. Here you select an upper and lower value and an incre- mental value. The solution is slightly more complex since the control does not accept fractions. You therefore have to calculate the eventual discount rate from the position of the scroll bar.

    The scroll bar in Figure 3. The cell link is cell C26 and the Offset function in cell C27 uses this index number. This means fonts, borders and patterns. In this example, it could be useful to introduce a management test to show if the project succeeds or fails and then display the result accordingly.

    Top 10 Best Financial Modeling Books | Wallstreetmojo

    The formatting is set, using the Format button, to pink when the value is greater than or equal to the value in cell C7. The result is shown in Figure 3. You can add further formats by clicking on Add and also copy them using Edit, Paste Special, Formats. At present, there is code in cells C17 to H19, which means there are poten- tially twelve mistakes. The goal should be to reduce code in order to reduce the potential for errors.

    The solution at present is equivalent to using Excel instead of a set of discount tables. You can use Insert, Function from the Menu Bar or the Standard toolbar and functions are divided into sections for easy reference.

    You then add the cash flow at period 0. You can always obtain help on the functions by press- ing the Question Mark as in Figure 3. Within the Help for the selected function, you can view a listing of alternative functions by selecting See Also. However, more functions are available.

    For example, NPV assumes that each period contains the same number of days. XNPV allows you to enter dates when the cash flows are received. The Valuation file discussed in Chapter 19 uses this function. Tick this item and press OK to install it. The toolpak will then be available every time you open Excel. If it is not available as an add-in, you will need to re-install Excel. The next sheet shown in Figure 3. You provide a start date and then the number of months to be advanced.

    Since the interval is a variable, there is a new control in the inputs area which points towards a set of workings to derive the number of months for the EDATE function in cells D13 to H Again you add the initial cash flow and the result is 7, If the net present value is above the limit, then you could have a label informing the user. The Text sheet in Features. Cell B20 is now an updated label. The Text function converts numbers to text following the number formats.

    This will display the percentage to two decimal places. The ampersand is used to join or concatenate the text strings: In order to reduce the code, the IF statement substitutes above or below depending on the net present value. Excel takes the deci- sion rather than the user having to spend time reviewing the result. As detailed in the previous chapter, there should be some documenta- tion as part of the model.

    With complex models, it is a good practice to record version numbers, author name and contact details together with notes on how the model works. As a model develops over time, you can record the changes between one version and another. This is particu- larly important if you find a major error.

    In addition, it means that a version reference is at the top of every sheet that you print out see Figure 3. It is of course better to put the notes in the model and you can always hide a sheet by selecting Format, Sheet, Hide.

    The standard cells above such as Version, Author, etc. The files with this book use several standard names such as Author, Company, Version and Product. This creates the names in the left-hand column, e. H13 If you copy a sheet containing names, then the new sheet will continue to refer to the original sheet. Similarly, if you copy a sheet to a new work- book, Excel creates a link between the two workbooks. You can always check for links by selecting Edit, Links.

    If this is the case, you have to remove them manually and reinsert the cell formulas. Go to Insert Comment or right mouse click on a cell. Enter a text message and then format the font size and colours see Figure 3. You can turn them off, show the indicator or have the comment permanently visible. In the second case, the cell displays a red triangle at its top right-hand corner.

    Again, comments can assist in explain- ing important formulas or telling the user what to do. For example, some people use numbers for percentages and then divide by in code. A com- ment could inform a user to insert a number rather than a percentage.

    The example now adds a cumulative cash flow and graphs the pattern. On the second step, click the Series rather than the Data Range tab see Figure 3. Then click Add Series to add the name of the series, values and labels. This will plot the cumulative cash flow values with the dates as the X labels across the chart see Figure 3. The name is also in code as Graphics! If you click on Next, the chart title and legend titles are displayed.

    Excel will not allow you to enter a cell reference against the name, but you can do this when you have finished the Wizard.

    Payback is a non time value of money method of investment appraisal. Essentially, you review how long it takes to get your money back. The fin- ished chart in Figure 3. This is a simple example, however, this approach would be useful for examining individual lines in a cash flow or company analysis. The steps are as follows: The name of the series is cell B27 to ensure that it updates.

    The formula in cell B27 is: The model would be more powerful if you could display the net present values for a range of discount rates simultaneously on the same sheet. The steps are: The grid consists of an interval and then a row of discount rates in line The 9. The cells on either side are plus or minus the interval. Cell B30 looks up the answer in cell C When complete the data table will show the net present value at each of these interest rates. The next stage is to highlight the grid area and enter the data table see Figure 3.

    Excel inserts the figures in the grid and the answer of 7, This shows the sensitivity of the final answer to changes in the discount rate. Table is an array function, which means that you cannot alter individ- ual cells within the group.

    If you try to alter any of cells C31 to H31, you will get an error message. Similarly, if you copy a data table from one sheet to another, only the values will be pasted. You have to highlight the grid and re-input the table on the new sheet. Line 31 is simply a variance to the original answer.

    The Offset function merely requires the rows to index down by and so no other programming changes are necessary.

    Data tables can be single dimensional as above or two dimensional. It is important to use a grid to set out the table and best not to hard code the interval. This means that you can always change the interval quickly and see on any printouts the interval used. In addition, it is best practice to input the current value for the vari- able in the middle so you can see the values on either side. Some applications with the book then use a macro to update the input values on the table by copying down the values from the inputs area.

    Similar spreadsheets tend to diverge over time and be more diffi- cult to maintain. As an added bonus, Excel will pro- duce a management report based on the scenarios.

    Scenarios are accessed using Tools, Scenarios, Add see Figure 3. There are saved cases on the Scenarios sheet. When you have selected them, Excel allows you to review the values in each of the cells before saving them. Press Show to display the scenario. There are further examples on the sheet, named best and worst case. These vary only the capital value and periodic cash flow.

    If you press Tools, Scenarios, Summary and select cells C22 and E22 as the result cells, Excel produces the management report shown in Figure 3. It is always best to start from a Base Case and vary these inputs rather than developing further scenarios.

    Here Worst and Best Case vary only two cells from the original scenario. It is therefore clearer what changes from the initial estimate. This is a static or values only report which will not change if the underlying values change.

    If the model changes, you have to run the report again. It also acts like an audit trail since you could print this out and keep it in a file to show what inputs produce the range of results. Suppose you wanted to know what periodic cash flow produces a net present value of 8, Rather than entering numbers into cell C6, you could go to Tools, Goalseek see Figure 3.

    The parameters are set cell X to Y by changing Z. This is changing only one parameter at a time by working backward from the answer and converging on the correct input. In this example on the Goalseek sheet, Excel will set the answer to 8, by varying the periodic cash flow. The answer is There is no possibility also of using constraints such as forcing a positive answer in the variable. For problems with rules and constraints, you need Solver.

    SOLVER Solver is a more advanced form of Goalseek since you can optimize or find values by changing multiple cells subject to constraints. Solver is an add-in to Excel, which has to be installed at the time of installation. If you still cannot find Solver, re-install Excel with this option.

    Solver makes it possible to work back from an answer, which can be: In this example, management wants to know if a net present value of 8, is possible if: Select the Solver sheet and the example can be accessed through Tools, Solver see Figure 3.

    If it cannot, then an error message will be displayed. Here, Solver finds a solution as Usually it is best to get a problem to work and then tighten the parameters. This allows you to see which of your constraints are not allowing Solver to converge on a solution. Solver also produces a management report as shown in Figure 3.

    It is also beneficial to save each of the answers as a scenario since there are now five answers with differing inputs. You can show all the scenarios on the Solver sheet see Figure 3.

    It con- sists of the following sheets: There is also a series of built-in macros; examples are listed in Table 3. GetScenarioNames Lists names of current scenarios on the Model sheet. Goalseek Specimen Goalseek program on the Model sheet. OpenExportForm Open the Export form where you select a sheet. It exports values only as a copy. NoticeShow Displays Notice form. FormShow Displays Export form.

    CopytoNewBook Copies sheet values to a new workbook. CopySheet Utility routine to copy values. SetupMenu Sets up a Menu sheet. FullScreen Displays full screen.

    AutoCalculation Sets calculation to automatic. ManualCalculation Sets calculation to manual. This also prepares a sheet for printing together with custom headers and footers. Using templates speeds up the development process and reduces the prospect of errors by using a standardized design. It contains all the features discussed in this chapter. Similarly, if you have broken down your calculations into segments, you will have generated batches of code that could be used.

    For example, you produce a grid for calculating UK or US tax allowances. The next time you need this, you can copy and paste the code rather than starting from scratch. You need only update the references and test it with known data. Over time, you can build up code and formulas that can be used in new applications. The schedule is now much more comprehensive than the original spreadsheet shown in Figure 3.

    For distribution, a password could be added to the macro using this syntax. Protect Password: SUMMARY This chapter has concentrated on features within Excel to make financial models clearer and more maintainable and the ways in which different features can be used together.

    While the NPV example is simplistic, it acts as a vehicle to show the layering and development of this type of model. The features discussed were: As already stated, it is imperative to use a consistent approach to reduce uncertainty and errors and make your work more accessible to others. The original model is shown in Figure 4. Fig 4. The aims are: This was to save time and ensure the adoption of a structure.

    This macro: There is room on the Menu to write notes about the application. The user can then select a sheet using the control and a macro is attached to it to display the selected sheet. The workings for the control are at row 50 out of sight. Selecting the Model sheet presents the standard layout of inputs, calculations, answer, management summary and workings.

    This is consistent with other models and the user knows what to expect. The inputs are in the top left as the most logical place.

    As the inputs change, the summary updates and this saves scrolling to the bottom of the page. The interface uses a selection of the features such as borders, colours, etc.

    The input cells are in bold blue to inform the user where data is required and go down the page. This is more logi- cal than scrolling across to enter data. Similarly the saving in production time reduces in year 3 and this is hard coded on the original model. These items are placed together so that the user knows that a change in this area will ripple through the model.

    For simplicity, the tax delay is assumed as one year, but strictly this is also a variable. The tax depreciation for equipment cost of , starts in year 2.

    This is calculated as per UK methodology in the workings at the bottom see Figure 4. Depreciation methods are discussed in more detail in Chapter The formula in cell B84 is: No tax depreciation errors" This is an IF statement to ensure that the model is self-checking and Excel takes as many decisions as possible see Figure 4.

    Another feature is the sign for the cash flows. You make fewer errors if you consider money out to be negative and money in to be positive. In this example, the depreciation is negative in the workings; however, the expenditure saves tax so this is positive. In this example, why do the savings and overheads reduction fall in the later years and are there any other factors that are not included?

    The initial example simply coded the numbers without expla- nation. With this group of inputs, there is more scope for examining the sensitivity of the model or running a series of scenarios. The calculations area gains its data from the input cells and the informa- tion flows logically through the model to the answers at the bottom.

    Colours, formats and borders are used sparingly to ensure that the print- out will be readable on a black and white laser printer. Here they can be audited and checked with a calculator for mathematical errors. Similarly, the author does not try to calculate the complete tax cal- culation on one line.

    The equipment is on line 29 and the tax payment on the maintenance and cost savings on line Again, this is trying to make the model as flexible as possible and providing a total that can be checked using a calculator. Cell B33 contains the NPV function, which discounts the cash flows in years 1—6 and adds the initial expenditure.

    The code is provided in the Application Template so that you can use it in your own work. This macro below: Select Range "A2". Select Sheets IndexNumber. Select End Sub 7.

    Top 10 Best Financial Modeling Books

    This is not intended to be a programming manual for Visual Basic, although you can record simple macros: The steps in recording the PrintPreview macro see Figure 4. You can update the button at any time by right clicking it. The button should then run the macro every time you press it. While this com- mand is available on the main toolbar, it is usually easier for a user simply to click the button.

    There are further examples of macros in later chapters. Comments and instructions are in the inputs area in Figure 4. Nevertheless, it is important that a summary is immediately visible to the user. The summary at the top displays instant feedback to the user as he changes the inputs see Figure 4. Conditional formatting is used to check that the result is acceptable to man- agement.

    Text strings are used to update the labels depending on the inputs. Where the test fails, the cells are crimson to show the user immediately. For example, in accounts analysis, the summary would consol- idate the profit and loss, balance sheet, cash flow statement and ratio analysis. The printing of the schedule places the detail in the sensitivity tables on page 2, with the title rows on page 1 repeated at the top of page 2.

    Cell D has to be updated from cell D6. As discussed in Chapter 3, it is important to set the grid for the data table correctly: The row input is D17 and since it is a one-dimensional table, there is no column input. Excel inserts the net present value for each of the discount rates. The table shows the sensitivity of the result to changes in the discount rate. A graph assists since the slope of the graph shows the degree of change.

    There is also a two-dimensional table with the discount rate across and the saving in production time down. The format is slightly different, as shown in Figure 4. Again, the items at the top and left depend on the interval rather than hard coding in the table.

    Note that one input on each axis has to be an absolute otherwise the table will not function and you will get the same figures either across or down. The two inputs are cell D17 for the row and cell D11 for the column. Again, a graph helps to show the pattern of the results. In Figure 4. The series names are the left-hand labels. If the answer is visible, one can be more confident that the table is function- Fig 4.

    This is achieved by highlighting the results cells C71 to J75 and accessing Format, Conditional Formatting see Figure 4.

    If the value is equal to the answer in cell B70, then the format changes as above in the table. With the answer in the middle, you could consider the answer to be the cell and those clustered around it. Given that the model is only a considered view of the possible cash flows, sensitivity testing shows in part how likely the end result will be within a range. Here you could consider cells E72 to G74 to be the likely range. Alternatively, you could rephrase it as the risk factors in the model.

    In order to keep the answer in the middle there is a button attached to a macro called UpdateTable at the top. This takes the value in cell D17 and paste specials the value into F42 and F It then copies cell D11 and paste specials it into B Chapter 16 on risk in the Part B describes other techniques for review- ing the degree of uncertainty or risk within the application.

    Those noted so far: Another example would be making sure a balance sheet adds up on both sides. Other methods detailed below are possible involving other features in Excel. With the current example, there is data in each of the inputs; however, it would always be a good idea to see what happens if unusual data were entered.

    Users can always be relied on not to follow instructions. If the series were curved then this could point to an error in the calculations. Several errors were noted in the model, which could have been found more easily using simple techniques. The tax row 10 underlines the errors in cells G10 and H10 since there are no precedents. The cell formulas have been overwritten with numbers. Cell F10 obtains its data from the costs and savings above and the tax depreciation in the lower table. In the above example, this would highlight the errors in the calcula- tions.

    You would expect to see formulas all across row 10 since it purports to compute the annual tax position see Figure 4. Using the above example, the errors again become apparent see Figure 4.

    The errors in cells G10 and H10 show up against cell F10, which contains the correct code. These are attached to buttons on the Explanation sheet.

    The code also includes passwords with the lines remarked out. Note that passwords are case sensitive. If you lose a pass- word, you will not be able to unprotect them. Below is the specimen code for protecting the application, which counts the sheets and accesses them in turn on a loop to protect them.

    Finally, the macro protects the workbook. Activate ActiveWindow. Protect 'ActiveSheet. LargeScroll Up: LargeScroll ToLeft: Select Next Counter Error: Worksheets 1. Select ActiveWorkbook. Protect 'ActiveWorkbook. Protecting the workbook does not automatically lock the Visual Basic code.

    If you lose your passwords, you will not be able to access your code. This sheet contains a listings: There could also be background on the methodology, formulas, reasons for using a particular structure or techniques. It is of course better to place notes in the application as these could assist a user in understanding the methodology. If you do not want others to see your notes, you can always hide the sheet and then protect the workbook only. If you look at work you did more than a year ago, the reasons for a particular course of action may not always be obvious.

    The common example is a budget model, which is updated every year. In the interim, you forget how it works and have to spend time understanding the methodology.

    It is rare that anyone can think of all angles. Particularly with regard to usability, it is important to get a second opinion. Similarly, a user may see an error just by scanning, or alternatively suggest further enhancements to the design.

    If the design is modular, developments should be possible to the initial design. Often putting techniques together makes a much more powerful application, for example using data tables, macros to update the table, conditional format- ting the answer in the table and a chart.

    For this reason, the Application Template can be modified with new features over time. The stages listed were: The case outlines some of the cash flows in an outsourcing model where govern- ment or business can contract with a third party to provide a service. In the UK, this is termed by government Public Private Partnership PPP or Private Finance Initiative PFI , where the government awards a contract in return for private sector investment and commitment and seeks a transfer of risk away from the government.

    The charges for the service are based on the contractual payment schedules including any penalty charges for non-performance by the contractor. The following information is given: This is the basic management test to be determined by the model.

    Summary of design methodology Table 5. Stage Comments Reply 1 Objectives What are they? This means: The rules for each of the cash flows are in cells C23 to C32 to aid under- standing. For example, staff costs comprise the number of staff multiplied by the individual cost multiplied by the forecast percentage savings. The model now exhibits further features. Names On the Menu sheet, there are several names assigned for clarity. The author is consistent in the use of names such as Version, Contact, Product, etc.

    These are common to all models, since it is important that version numbers where possible appear on the printed schedules. You define Names using Insert, Name see Figure 5. It is useful to paste a list of names on a separate sheet as part of the doc- umentation using Insert Name, Paste, Paste List see Figure 5. There are specific formats up as custom formats such as in cell E23 see Figure 5. Functions The model needs to calculate the answer to the discounted cash flows as: Fig 5.

    The formatting is green if the project passes the test and pink if it fails see Figure 5. Go to File, Page Setup. The tabs are: The custom headers and footers are set as follows: File Name Sheet Name: Data Time inserted as: Sheet Name: In particular, there is: PPP 4 Further improvements are needed to turn the model into a more rounded application for management purposes, for example: The cell link is cell C50 see Figure 5.

    This is the same methodology as the Investment Model in the pre- vious chapter and shows a consistency of approach. The GetSheetNames macro is assigned to the control so that every time you click the control, the macro counts the sheets in the workbook and populates the cells B51 to B70 with the names.

    It then displays the sheet number corresponding to the index number in cell C Excel uses an index number for each sheet, so the Menu is Sheet 1 and the Model Sheet 2.

    Here you populate the workings for the combo box manually with the sheet names. The macro selects the sheet number returned by the combo box. There is a macro in the file called PrintSetUp, which is a utility macro for setting up the printing with custom headers and footers. To access this quickly, this is assigned to a button on the Spare sheet.

    You can also view macros by accessing: The PrintSetUp macro is in Module 3. PPP 5 Combo boxes for defined entries The discount rate on the Model sheet is better served by a combo box help to control inputs. This again uses a standard layout with work- ings at the bottom of the schedule. The steps to insert the control are as follows: The Combo box populates itself with B B and updates C89 see Figure 5. The index number is five so the result is five cells down from cell B92, which is 6.

    However, you may want to store other views of the future as scenarios. In order to reduce code, you would not want to produce multiple spreadsheets which simply repeat the same programming. Enter scenarios using: If you have more than one Scenario, you can click Summary and Excel produces a management report as in Figure 5.

    If cells are named, then names rather than cell references are shown. The results of the other scenarios demonstrate that the net present value of 12,, is easily turned into a negative figure.

    In particular, the model seems sensitive to changes in the anticipated cost savings. This may illuminate risk factors or other considerations. PPP 4 has the templates set up at the bottom of the schedule to insert data tables for staff savings and the discount rate as one-dimensional tables. The data tables are inserted using the method in the previous chapter. The layout is standard with the interval input marked in blue.

    One of the row headers has to be an input cell and not a formula, and here this is cell C In the example, the net present value falls sharply if the planned savings are not achieved. The difference is far more than the reduction due to an increase in the discount rate. The charts show the effect of the changes clearly see Figure 5. Data tables provide a greater understanding of what can go wrong by testing the inputs and providing multiple answers.

    By testing assumptions, you can try to understand the behav- iour of variables. There are various techniques, covered later in the book, which also cover risk: Some questions are: This is on the Spare sheet. The row and column headers and grid lines are removed using Tools, Options, View. This makes the interface look less cluttered and, on com- pletion, you do not need this information. The straightforward methods of testing the model are using test data and checking the answer with a business or financial calculator.

    Other meth- ods include: When the model is finished, use protection to avoid unnecessary over- writing or changes to the structure of the workbook see Figure 5. Use Format, Cells, Protection. This is one of the reasons for colour coding the inputs and changing the colour if the status changes. Finally, show your model to others and take advice.

    For example, if others use the model or you want to use it again as a template, are the inputs intuitive and do they get the same results? Other questions are: Inflation is an important vari- able which has been omitted to simplify the model. The features included in the model are shown in Figure 5. SUMMARY The chapter has reviewed a case study of an outsourcing example as a vehicle to demonstrate the stages in layering Excel features and tech- niques. The seven stages illustrate: Each model follows the design procedure outlined in Part A to provide templates for study and further development.

    The chapters in Part B are: The starting point is the publicly available information organizations produce in the form of annual reports. Annual reports for public companies consist of: The level of detail depends on corporate governance and legal require- ments in the country where the company resides. Private companies often produce very little information and indeed the trend in the UK is to demand less information and even dispense with a third-party audit.

    The report also details the accounting standards and conventions used by the company in drafting the report and accounts. This is important since it is often difficult to compare companies across borders due to differing standards. For example, accounting profit can be enhanced by increasing the depreciation period for assets or changing the method of valuing stock. Analysing performance depends on standardizing the information available so that the raw data can be processed to provide information on the performance of the company.

    Unfortunately, the information usually lies in a number of sections. To understand the figures, you have to continually flip from one section to another. The figures tend to overpower the reader with the wealth of detail.

    Most people are not proficient at handling large quantities of numbers and immediately understanding the relationships between them. If you are going to lend money to the company or understand its performance, you need to decide if the organization is becoming more or less risky.

    The process is: The accounts in a standardized form allow you to: The base information will also provide the data for further information on cash flow, forecasting and company valuation. The company is the fic- titious organization Technology Sales Limited. The first stage of the exercise is to produce a model which contains sched- ules for the income statement and balance sheet.

    The model allows up to five years of results in order to ascertain the trends. The cash flow and some ratios can be derived from this information. The model also tries to build on the good practice discussed in the first five chapters. However, it could be made more complex with the addi- tion of more lines to display an increased level of detail. Fig 6. The input cells are marked blue and the totals are in bold green. The latter cells only add up the cells above, which all adhere to the cash flow rule.

    Cash received is positive while cash outflows are entered as negative numbers. It can be a problem understanding the source of the cell results. The levels of profit are clearly marked as in Table 6. Table 6. Again the format corresponds to international notation and is split into current and fixed assets see Figure 6. Again, you could increase the detail by adding columns for more share types such as preference shares.

    There is no real programming here and Excel is used to ensure that the columns add correctly. The formatting of the numbers, colours, columns and general appearance is common throughout the application.

    Similarly, all reports are formatted ready for printing with custom head- ers and footers inserted using a macro called SetUpSheet. If they do not, then cell E43 displays a warning message: In this form, you can look along the rows and try to pick out trends.

    The percentage views assist, but eventually you need to look more closely by calculating ratios which can be tedious with a pocket or financial calculator. However, this process is straightforward using Excel. RATIOS The purpose of this section is to outline some of the ratios used by analysts and their purpose rather than an in-depth analysis of the accounts in the example.

    Financial analysts tend to dis- count luck and concentrate on the results through ratio analysis. Figure 6. The first stage is to define the purpose of the investigation. The problem is to decide if the company can survive and generate earn- ings, so next one would study the industry and its competitive position. Some industries can build barriers around them that ensure that they can generate profits over a long period. Other industries possess advantages such as patents or brands which enable them to produce superior profits over time.

    Companies such as Microsoft would fall into this category, where its position, as a dominant player, has allowed it to grow quickly over the last decade and protect many of its markets from competition.

    Ratios help this process, but bear in mind the following: There are three main issues to be considered: The three areas of risk determine the quality and quantity generated by the organization.

    The decisions of what and how much to produce are underpinned by the management. Operating cycle. The company delivers the goods, invoices its customers and has to wait for the customer to pay. If it cannot sell its goods, then they will sit in the warehouse and have to be funded from the operating cycle, bank finance or new equity. This is termed business risk. Performance risk means profitability, which in turn usually equates to cash flow.

    In the long term, a lack of profits usually means that a company will be uncompetitive, go bust or will be taken over by businesses that are more aggressive. Where you have profits, but no cash, then you need to examine the accounting standards used or investigate creative accounting practices. For example, costs moved to the balance sheet such as research and development mean that cash has been spent, but is being recorded in the profit and loss account over a number of periods.

    This increases profits in the short term at the expense of the long term. Financial risk is concerned with the structure of the balance sheet together with who provides the finance facilities to the company. The essential difference between debt and equity is that dividends do not have to be paid, while bankers require payment.

    As the proportion of debt increases, so does the interest burden and therefore the amount of finan- cial risk. Ratios in this section illustrate the proportion of the company owned by the shareholders and degree of financial burden. The ratios considered in this book are listed in Figure 6.

    Mastering Financial Modelling In Microsoft Excel 3rd Edn Pdf Ebook

    This is considered the most impor- tant since this ratio computes the return to shareholders who risk their capital in the enterprise.

    This directs the user at the lines that need investigation or further atten- tion. The formula in cell L11 is: If the number of days or the gap increases, this means that the com- pany has to find more resources to fund the cycle. John Charnes. The Misbehavior of Markets. Benoit Mandelbrot. How to Understand Business Finance.

    Bob Cinnamon. How They Blew It. Jamie Oliver. Guide to Financial Management. The Economist. Brilliant Budgets and Forecasts. Malcolm Secrett. Key Management Ratios. Ciaran Walsh. Market-Neutral Trading: Thomas K.

    Internet Banking. Robert Young. The Chief Financial Officer. Financial Accounting. David Kolitz. Finding Your Way: Rebecca Harmon. Building Winning Trading Systems with Tradestation. George Pruitt. Stock Charts For Dummies. Greg Schnell. Finance for IT Decision Makers. Michael Blackstaff. Thomas Michael. Garrett Sutton. Developing Profitable Trading Strategies: Patrick Grattan.

    Inside Silicon Valley. Marc Phillips. Issa Bass. An Introduction to the Mathematics of Finance. Stephen Garrett. Trading Options at Expiration: Strategies and Models for Winning the Endgame.

    Approaches to Enterprise Risk Management. Bloomsbury Publishing. John Tjia. Jane E. An Introduction to High-Frequency Finance. Michel Dacorogna. Effective Budgeting using Microsoft Excel. Quicken The Missing Manual.

    Principles of Financial Engineering. Robert Kosowski. Stuart Warner. William Preinitz. Argus Developer in Practice. Tim M. A step-by-step program to end your money problems and grow your wealth. David Donohue. Guide to Business Modelling. John Tennent. Shareholder Value - A Business Experience. How to write a great review. The review must be at least 50 characters long.

    The title should be at least 4 characters long. Your display name should be at least 2 characters long. At Kobo, we try to ensure that published reviews do not contain rude or profane language, spoilers, or any of our reviewer's personal information.

    You submitted the following rating and review. We'll publish them on our site once we've reviewed them. Continue shopping. Item s unavailable for download. Please review your cart. You can remove the unavailable item s now or we'll automatically remove it at Checkout.

    Remove FREE. Unavailable for download. Continue shopping Checkout Continue shopping. Chi ama i libri sceglie Kobo e inMondadori. Choose Store. Or, get it for Kobo Super Points!

    In this series. Skip this list. Ratings and Book Reviews 0 0 star ratings 0 reviews. Overall rating No ratings yet 0. How to write a great review Do Say what you liked best and least Describe the author's style Explain the rating you gave Don't Use rude and profane language Include any personal information Mention spoilers or the book's price Recap the plot.

    Close Report a review At Kobo, we try to ensure that published reviews do not contain rude or profane language, spoilers, or any of our reviewer's personal information. Would you like us to take another look at this review?

    Similar posts:

    Copyright © 2019