LAS CARACTERÍSTICAS DE LA EDUCACION DE LA COMPAÑÍA DE JESUS

An add-in, as the name implies, is not a standard part of Excel but is a separate component de- signed to seamlessly extend the capabilities of Excel. Excel comes with its own built-in library of add-ins including Solver, the Analysis ToolPak, and Internet Assistant. Add-in files, which always have an .XLA extension, can be installed or removed using the Tools|Add-Ins. . . com- mand. The four major vendors of add-in software are Palisade (www.palisade-europe.com), Decisioneering (www.decisioneering.com), Frontline Systems (www.solver.com), and LINDO Systems (www.lindo.com). The first two companies offer risk and decision analysis tools while the latter two specialize in spreadsheet optimization.

The DecisionTools Suite of programs from Palisade contains seven products – @RISK, PrecisionTree, TopRank, RISKOptimizer, BestFit, RISKview, and @RISKAccelerator – that work together in an integrated environment to provide combined analyses and maximum functionality. The @RISK add-in for Excel provides risk analysis and Monte Carlo simulation, while PrecisionTree creates influence diagrams and decision trees in existing spreadsheets. Decisioneering’s most popular product is Crystal Ball (CB) which performs risk analysis and Monte Carlo simulation. The software suite includes CB Predictor for analysing historical data to build models using time-series forecasting and multiple linear regression. The CB Tools feature is used to automate model building tasks, simulate variability, define correlations, and perform additional functions.

Frontline Systems claims to be a world leader in spreadsheet optimization, having developed the Solvers/Optimizers used in Excel, Lotus 1-2-3, and Quattro Pro. Its software products can handle the full range of optimization problems from linear, quadratic and mixed-integer programming to global, non-smooth, conic and convex optimization. LINDO systems offer three software products, namely LINDO API (optimization engine), LINGO (solver with its own modelling language) and What’s Best! (spreadsheet solver).

EXCEL FUNCTIONS USED IN MODEL-BUILDING

The models developed in this chapter use the following ten Excel functions, each of which is explained below. The user should remember that a comprehensive on-line help facility is also provided by Excel.

1. ABS: ABS (Numb)

returns absolute value of a real number Numb. The absolute value of a number is the number without its sign.

Example: ABS(–3.6) returns the value 3.6. 2. AVERAGE: AVERAGE (array)

returns the average (i.e., arithmetic mean) for cell range array.

array= the range of cells for which the average is required. If the array contains text, logical values, or blanks, then all these cells will be ignored; however, cells with value zero are included

1 2 3 4 5 6 7 10 25 8 40 1 2 3 4 −300 150 180 100 Gizmo Gadget Widget Sprocket £10.00 £12.50 £20.00 £4.50 8 9 A B C D E F G H Product name Product price Sample Figure Cash flow Year No. in stock

Figure 3.13 Sample figure.

3. COVAR: COVAR(ref1:ref2, ref3:ref4)

returns the covariance between two data sets, each set having the same number of data values. One set is contained in the cell range ref1:ref2 and the second set in the range ref3:ref4. Covariance measures the correlation, i.e., the strength of relationship, between two data sets. A positive covariance indicates that the two sets tend to go in the same direction. A negative covariance indicates that the two data sets diverge, i.e., tend to go in opposite directions. A covariance of zero indicates that there is no relationship between the two data sets.

Examples: COVAR({1, 2, 3, 4}, {13, 24, 51, 78}) returns a value of 27.75 COVAR({1, 2, 3, 4}, {–99, 122, –1, –58}) returns a value of 0 COVAR({13, 24, 51, 78} {–99, 122, –1, –58}) returns a value of –360 4. IRR: IRR(ref1:ref2)

returns the internal rate of return (IRR) – also called the yield – of a range of cell values ref1:ref2.

Example: IRR(G4:G7) in Figure 3.13 returns a value of 22%. 5. MMULT: MMULT(array1, array2)

returns the matrix product of two arrays. The result is a matrix (array) with the same number of rows as array1 and the same number of columns as array2. Note that the number of columns in array1 must be the same as the number of rows in array2. The MMULT function must be entered as an array formula. An array formula contains cell ranges, each range being treated as a single entity. This means that individual cells within the output matrix I3:K6 cannot be changed or deleted. A matrix is simply a cell range, with the notation xi j representing the value of the matrix cell in row i, column j. Consider

the two matrices in Figure 3.14.

The value in cell I3= (row 1 of array 1) × (col. 1 of array 2) = {2, 3} × {–2, 2} = (2 × –2) + (3× 2) = 2 The value in cell J3= (row 1 of array 1) × (col. 2 of array 2)

1

2 array1 array2 array1 array2

3 4 5 6 2 −1 1 4 3 0 −2 × 2 −3 5 3 1 −3 2 9 3 −18 −12 9 −3 0 14 2 2 −8 −4 7 9 10 12 11 13 14 15 A B C D E F G H I J K L

The result of multiplying array1 by array2 is a (4 row × 3 col.) matrix as shown by cell range I3:K6. Perform the following steps to obtain the required answer:- 8

[1] Highlight the cell range I3:K6.

[2] Type =MMULT(B3:C6,E3:G4) into the formula bar at the top of the screen. [3] Press the three keys Ctrl+Shift+Enter at the same time. Curly brackets will automatically appear around the formulae in cells I3:K6, indicating that an array formula has been entered, i.e. each cell contains {=MMULT(B3:C6,E3:G4)}

Figure 3.14 MMULT diagram.

6. NPV: NPV(discount, ref1:ref2)

returns the net present value (NPV) of an investment based on a series of periodic cash flows in the range ref1:ref2 and a discount rate of ‘discount’. The NPV of an investment is today’s value of a series of future payments (negative values) and income (positive values). All cash flows occur at the end of each period. If the first value in a series, e.g., B1:B6, occurs at the beginning of period 1, the NPV is given by B1+NPV (discount, B2:B6).

Example: NPV(12%,40,45,50) returns a value of £107.18, assuming all flows occur at the end of each period. If a payment (i.e., outflow) occurs at the beginning of the first period – as in Figure 3.13 – then the formula is G4+ NPV(10%, G5:G7) = £60.26.

7. PMT: PMT(rate, nper, pv, fv, type)

returns the periodic payment for an annuity based on fixed payments and a fixed interest rate. PMT is closely associated with the PV function below.

rate = The interest rate period.

nper = The total number of payments in the annuity.

pv = The present value, i.e., the total amount that a series of future payments is worth now.

fv = The future value that is required after the last payment is made. If fv is omitted, it is assumed to be 0, e.g. the fv of a loan is zero.

type = The number 0 or 1 and indicates when payments are due. If type = 0 or is omitted, payments are made at the end of the period. If type= 1 then payments are made at the beginning of the period.

Example: If a car loan of £3,000 is taken out for a period of two years at an annual interest rate of 7%, then the monthly payments are PMT(7%/12, 24, 3000), i.e.,−£134.32. Note that units for ‘nper’ and ‘rate’ must be consistent, thus the monthly interest rate is 7%/12. The parameters ‘fv’ and ‘type’ have been omitted.

8. PV: PV(rate, nper, pmt, fv, type)

returns the present value (PV) of an investment. The PV represents the time value of money, i.e., the total amount that a series of future payments is worth now.

rate = The interest rate per period. For example, if payments are made monthly and the annual interest rate is 12%, then interest is entered as 12%/12, i.e., 1%.

nper = The total number of payments in the annuity. For example, if monthly payments are made on a four-year car loan, then nper= 4*12 = 48. pmt = The fixed payment that is made each period.

fv = The future value that is required after the last payment is made. If fv is omitted, it is assumed to be 0, e.g., the fv of a loan is zero.

type = The number 0 or 1 and indicates when payments are due. If type = 0 or is omitted, payments are made at the end of the period. If type= 1 then payments are made at the beginning of the period.

Example: PV(10%, 3, 1000) returns a value of –£751.31, i.e., in order to receive £1000 in three years time, £751.31 must be paid out today, assuming an annual interest rate of 10%. Note that PV shows future cash received as positive while cash payouts are negative. The parameters ‘pmt’ and ‘type’ have been omitted.

9. SUMIF: SUMIF(range, criteria, sum range) adds the cells specified by given criteria. range = Range of cells to be examined.

criteria = Specified criteria in the form of a number, text, or an expression. For example, criteria can be specified in the form 32, “32”, “>32”, “apples”. sum range = Cell range being summed – only cells satisfying criteria are summed. If

sum rangeis omitted then range is examined.

Example: SUMIF(C4:C7, “<12”) in Figure 3.13 returns 18, i.e., 10+ 8.

SUMIF(B4:B7, “=Gadget”, C4:C7) in the Figure 3.13 returns 25, i.e., the sum of the values in column C which correspond to ‘Gadget’ in column B.

10. VAR: VAR(ref1, ref2)

returns the variance between the values in a data set contained in the cell range ref1:ref2. Variance is a measure of dispersion, indicating the spread of individual data values. Note that the square root of the variance is the well-known standard deviation.

Example: Let C2:C6 contain the values 3, 3.1, 3.2, 3.3, 3.4 and D5:D9 contain the values –20, 0, 14, 45, 88. Then VAR(C2:C6) returns 0.025, a small number because the data is close together. VAR(D5:D9) returns 1784.8, a large number because the data is spread out.

EXERCISES

3.1 The net cash flows for a new project are estimated to be as follows:

Year 0 1 2 3 4

Net cash flow (£’000s) –40 16 24 18 14

Use Excel’s IRR function to calculate the yield on the investment. If the cost of capital is 14%, calculate the net present value of the investment using Excel’s NPV function.

(Answers: 29%, £12,941.)

3.2 Fred Flint owns 1000 shares in the Bedrock Company. The shares are currently worth £2.80 each. Fred is considering whether to (i) hold the shares for one year and then sell, or (ii) sell the shares now and buy stock at £80 per stock in the MightyBig Corporation. He estimates that the price of the MightyBig stock in a year’s time will be £87, £80, or £70 with probabilities of 0.5, 0.3, and 0.2 respectively. At the end of one year, Bedrock’s share price and dividend depend upon Table 3.9.

Table 3.9

Share price Probability Share dividend

Up 20% 0.1 £0.5

Same 0.5 £0.2

Down 10% 0.4 None

Use Excel to draw a decision tree for Fred Flint’s investment problem, and hence determine his best policy.

(Answer: best policy is to buy the MightyBig stock.)

3.3 Referring to Barney Briggs’s investment portfolio of Example 3.5, show that you understand the concept of matrix multiplication by manually verifying the spreadsheet answer of Figure 3.4. Suggest another way in which the objective function might be calculated. (Note that details on Excel’s MMULT function are given at the end of the chapter.)

3.4 Barney’s cousin, Bette Briggs, has been impressed by his bank’s portfolio management and has decided to avail herself of their expertise. Bette has informed the bank that she wants a return of 13% on her investment and she has been given details of three stocks X, Y and Z with average annual returns of 13.2%, 17.5%, and 9.7% respectively. Statistical information on these stocks is as follows: variances for X, Y, Z are 0.0012, 0.0023, and 0.00047, while covariances are XY= –0.00019, XZ= 0.0009, and YZ = 0.000125. Using the investment model for Barney Briggs’s port- folio, find the percentage investments for X, Y and Z that will give Bette an overall return of 13%.

3.5 Willie Wong has recently purchased a microcomputer system and has decided to set up a cash flow spreadsheet which will allow him to analyse his personal finances over the next six months (January–June). His only source of income is his annual net salary of £18,000. After examining his bank statements for the past year, Willie has worked out average expenses. These include monthly payments for rent (£250), food (£150), car expenses (£90), and sundries (£100) as well as two- monthly bills for electricity (£100) and telephone calls (£70). Because of his recent microcomputer purchase, Willie has estimated that he will have a bank overdraft of £2000 on 1 January. Willie Wong intends to take a holiday in July and he would like to know how much money will be available. Set up a cash flow model showing net cash flows and balances at the end of each month. How much will Willie have for his holiday?

(Answer: Willie Wong will have £2,950 available for his July holiday.)

3.6 A company is considering the following five investment proposals for acceptance. The capital required for each project over the next five years is shown in Table 3.10, along with the amount of budgeted capital. Management wants to maximise the rate of return, i.e., maximise the total NPV of the selected proposals. Using Excel’s linear programming tool – Solver – find the optimal set of investments and the resulting NPV of the investments.

Table 3.10

Capital (in £’000s) required in

Year 1 Year 2 Year 3 Year 4 Year 5 Expected Proposals NPV (£’000s) 1 40 10 5 20 10 0 2 70 30 20 10 10 10 3 80 10 20 27 20 10 4 90 25 20 20 15 15 5 100 20 10 40 20 20

Capital available for each year₌ 50 45 70 40 30

(Answer: Accept proposals 1,2, and 3 which give a maximum NPV value of £190,000)

3.7 The Interstate Bank is planning its funds portfolio for next year. The bank has £20 million to invest and is considering five different funds as shown in Table 3.11. All of the funds are secured except the signature loans.

Table 3.11

Fund Rate of return (%)

Signature loans 14

Vehicle instalment loans 13

Home improvement loans 13

Miscellaneous instalment loans 12.5

Interstate wants to maximise the return on its investment portfolio while complying with the following banking regulations: