1

Advanced Applications in Excel Office 2007 3/25/2011 UWI Open Campus (Cayman) Robert Geofroy

2

Contents Introduction .................................................................................................................................... 5

Referencing ................................................................................................................................. 6

Relative referencing ................................................................................................................. 6

Absolute ................................................................................................................................... 6

Lookup Tables .............................................................................................................................. 7

Pivot Tables .............................................................................................................................. 8

Educational Applications ................................................................................................................. 9

Grade Book ................................................................................................................................ 10

Transcript template ................................................................................................................... 11

Financial Applications ................................................................................................................... 12

Annuities ................................................................................................................................... 13

3

Loan Computations ................................................................................................................... 14

Compute the Payment ........................................................................................................... 14

Protecting the Worksheet ..................................................................................................... 15

More Loan Functions ............................................................................................................. 17

Final Exercise ......................................................................................................................... 18

Amortized Loan Schedule.......................................................................................................... 19

Cash Flow .................................................................................................................................. 22

Forecasting ................................................................................................................................ 23

Three Month Moving Average .............................................................................................. 23

Statistical Analyses ........................................................................................................................ 24

Grade Analysis ....................................................................................................................... 24

4

Add-ins....................................................................................................................................... 25

Descriptive Statistics .............................................................................................................. 27

Correlation ............................................................................................................................. 28

Regression.............................................................................................................................. 30

VBA in Excel ................................................................................................................................... 31

References .................................................................................................................................... 32

5

Introduction This workshop will introduce participants to the design of advanced applications in Excel. We shall look at

Educational applications, financial applications and engineering applications. In the financial applications, we

shall look at Loan Payments, Amortized loan schedules, cash flow, forecasting and much more.

Educational applications

Financial Applications

Statistical Analyses

Before you embark on this course, you may like to review some key concepts that will be in use in these

exercises:

Referencing

Lookup Tables

It may help to look at: http://www.cpearson.com/Excel/Topic.aspx. This exercise will help as well:

http://www.homeandlearn.co.uk/excel2007/excel2007s7p5.html

6

Referencing

It is important to understand the difference between relative and absolute cell referencing in Excel.

Relative referencing

In Relative referencing, cell addresses change in accordance with relative positions. Thus, if we have say a

formula in cell C1 [=A1+B1], then when the formula is copied into cell C2, the new formula becomes [=A2+B2]

to maintain the same relative positioning of the cells.

Absolute

In Absolute Referencing, we fix or anchor either the row or the column or both. Thus if as in the previous

example we had anchored both then copying the formula down will show [=$A$1+$B$1] and the value in c2

will be the sum of the values in A1 and B1.

A $ sign in front of the row or column indicates that it is fixed or anchored. Remember we can use the

function key F4 to change the absolute referencing.

7

Lookup Tables

In college, there is a grade scale

which maps scores to grades as in

the illustration shown.

Many a time you wish to lookup the

value of a score. For instance, a

score 0f 57 falls in the range 55-60

and would result in a grade of C-.

Set up the table as shown.

In this we would name the range

A1:B9 Grade_table.

Use the VLOOKUP function as

shown to return the result of the

input score in cell B13. Observe that the scores in the grade table are in ascending value.

8

Pivot Tables

A Pivot table is a way to extract data from a long list of

information and present it in readable form. Consider the table

al left. Now with the help of Excel, we can produce the

following:

Now we know for instance that Elisa scored 89 in Art in January.

Have a look at the original data in the table to confirm this.

You can follow the logic to produce this Pivot table by using this

link:

http://www.homeandlearn.co.uk/me/mes9p4.html

9

Educational Applications

As one of the most used applications in the Office suite, Excel has a host of applications for education. In this

session, we shall look at some simple educational applications of which the Grade Book and Transcript stand

out. Both of these applications use the lookup table, range naming and absolute and relative referencing.

No doubt, teachers will find other applications as well as games that may be useful in teaching of various

concepts in the classroom.

10

Grade Book

In this exercise, you are required to set up a

grade book.

The same workbook must serve to record

attendances and absences.

During the course of the semester, students

are given assignments. There are ten

assignments in all and the scores from these

are used to determine the coursework mark

which is out of 30. There is a mid-semester

examination which contributes 20% of the

overall mark and a final which counts for

50%.

The grade book must automatically find the

final coursework mark and final grade.

11

Transcript template

This template can be used to

produce a student transcript

including computation of the

Cumulative GPA. Students

need a CGPA of 2 in order to

graduate from college.

Typically, an Associate Degree is

awarded after 22 courses each

with either 3 or 4 credits.

You will need to use a lookup

table in order to convert the

grades into GPs which are

used in the CumGPA

computation.

12

Financial Applications Let us now turn our attention to some common financial related spreadsheets such as loan management,

amortized loan schedules and cash flows.

Annuities

Loan Payment

Amortized Loan

Cash Flows

Forecasting

Trend Analysis

Acounts

You will need to familiarize yourself with the methods used by financial institutions for loans and loan

payments.

You would also need to look at what features Excel has in terms of the Financial functions which can help in

this regard.

13

Annuities

This example is taken from:

http://www.tvmcalcs.com/calculators/excel_tvm_functions/excel_tvm_functions_page2

Suppose you are offered an annuity which will

pay you $1000 /annum for 10 years. If you can

earn 9% per year on a similar investment, how

much should you be willing to pay for this

annuity? How do you decide what is the Present

Value of this annuity?

Set up the spreadsheet as shown. If you want to

show the value in B5 as a positive number then

put a negative sign in front of the PV function.

Thus, you would have for B5 [=-PV(B3,B2,B1).

Use the URL shown above to explore other calculation options.

14

Loan Computations

Suppose that we want to borrow a sum of money at a fixed interest rate over a period of time. Lets take it

that we are borrowing $100, 000 over 3 years and at an interest rate of 10%. Excel can tell us exactly what the

monthly payment on this loan is.

Set up the following spreadsheet:

Clearly the principal is the amount being borrowed, the interest is 10% and

we want to repay over 3 year so that the number of payments is 36.

Compute the Payment

Now to compute the monthly payment we can use the PMT function.

Examine the description of the PMT function.

The rate is the value in cell B4/12 as normally the rate is quoted as an

annual rate of interest. The number of payments NPER is the value in

B5 and the Present value of the loan which is the total amount that a

series of future payments is worth now, is the value in B3.

15

If you wanted to arrange that there could be a number of payments

different from 12 per year as some people get paid bimonthly, then

you could have done the following:

In this case, the formula in B6 would be =PMT(B3/B4,B5,-B2).

Note that the bank uses the phrase Cost of Loan. If you work out

how much money you actually give the bank, it is $116,161.87. The

bank has imposed the sum of $ 16, 161.87 as the cost of

this loan. Of course, the bank has its own administrative

costs and so their own profit may be a fraction of this but

certainly you have paid a large sum over and above what

you wanted for the facility of having the loan and the use

of the goods at the present time.

Protecting the Worksheet

In this exercise, we shall protect the worksheet so that

16

data can only be entered in the cells B3, B4 and B5. In this way, you can prepare templates for which only the

cells that require data entry can be modified. The cells that contain formulae are safe.

To do this:

1. Select the range B3:B5.

2. Go to the Home ribbon and open the Font dialog box

as shown in the illustration above.

3. In the Protection tab, uncheck the Locked option.

4. Go to Review > protect Sheet and ensure that only the

Select Unlocked cells option is checked.

5. Click OK

By default, all cells in an Excel worksheet are locked. This

makes it easy to protect all the data in a single worksheet or

workbook by applying the Protect Sheet or Protect Workbook

option.

17

To arrange for only specific cells to be used for data entry, we need to unlock these cells before applying the

Protect Worksheet command. It is a matter of taste but you could have highlighted the input cells before

protecting the sheet to help the user understand that this is where input is expected.

More Loan Functions

Apart from PMT, there are other Excel functions that can be used to look at different aspects of the loan: The

payment can be calculated for a given principal, interest rate and payback period, or given the other

parameters the number of periods may be returned. We can also compute the rate or the Present value based

on the other data. In short, Excel makes it easy to manage loans.

Function Description

PMT Calculates the payment for a loan based on constant payments and a constant interest rate. PPMT Returns the payment on the principal based on periodic constant payments and a constant interest

rate. NPER Returns the number of periods for an investment based on periodic constant payments, and a

constant interest rate. PV Returns the Present value of an investment: the total amount that a series of future investments is

worth now. IPMT Returns the interest payment for a given period of investment, based on periodic constant

payments and a constant interest rate.

18

Final Exercise

Try putting it all together in the following manner:

The return values are shaded.

19

Amortized Loan Schedule

In the last exercise, we saw how to manage a loan. Amortized loans are common in finance circles. In this type

of loan payments are made which reduce the principal so that the interest is reduced and at the end of the

loan schedule when the final payment is made the principal is reduced to zero incurring no further payments.

Normally there are 12 payments per year but we can arrange our spreadsheet so as to deal with amortized

loans with other payment options such as say 4 payments per year.

In the last exercise, we had to compute the cost of the loan apart. In this case, we want to report on the total

interest paid which will be the cost of the loan. We also want the specific return dates for payments as well as

the interest and the principal at that date. An amortization schedule then is simply a listing of each payment,

a breakdown of interest, principal and remaining balance. Lets see how we can arrange this. Use the previous

worksheet to start. In this exercise, you will need to be able to use the absolute referencing which has been

described above. The following exercise is taken from TVMCalcs.com.

Set up the following:

20

The first principal for Month 0 is $ 100, 000 which is the value in cell B2 so set cell D9 as =B2.

Use the Data Fill to list the months 0..30 in

the Month column as shown:

21

Follow the suggestions on the page http://www.tvmcalcs.com/calculators/apps/excel_loan_amortization

to make your work look a bit more fancy.

22

Cash Flow

A Cash Flow shows money in and money out. This can have a marked effect on the success of the business.

You have to make sure that you never go broke!!! Read the article Manage Your cash Flow at:

http://office.microsoft.com/en-us/excel-help/manage-cash-flow-

HA010066238.aspx?CTT=5&origin=HA010066228

Browse to the URL;

http://office.microsoft.com/en-us/excel-help/cash-flow-management-HA010066228.aspx

Download the Small Business Cash flow template. This will save you the trouble of creating one and is really

helping with the efficiency of the operation as well.

Populate the cash flow template with sample data and examine the cash flow as well as the chart.

23

Forecasting

Create the spreadsheet as shown in the illustration. Use the Auto Fill handle to

compete the months. Input the demand figures:

Three Month Moving Average

Format the averages in column C so

that they appear with zero decimal

places as shown.

24

Statistical Analyses

Grade Analysis

Load the file CXC04 which contains the

grades obtained for the 2004 CXC

examinations. Create the pivot table as

shown in the illustration.

Looking at the table, which subject has the

best results?

Which subject has the poorest results?

What proportion of the students have

achieved a passing grade 1, 2 or 3?

25

Add-ins

In many statistical analyses, you will

find that you will need the Analysis

Toolpak. This is not installed by

default.

Load the file entrance-exam.xls from

the files folder. This file contains

scores in the entrance examination

from the University College. Scores

are reported for each case in two

areas: English and Mathematics. We

may be interested in doing some

analyses of these results.

26

From the Office button, launch Excel options> Go to Excel Add-ins.

Highlight the Analysis Toolpak and press GO. In the Add-ins dialog box,

check at least Analysis Tookpak. If you like you can check the others as

well as in the illustration shown. Press OK. Microsoft Office will install the

additional files necessary for the analyses.

Now you are ready to go!!

Set up a correlation between the two sets of data to

see whether it follows that a student who did well in

English would also do well in mathematics.

27

Descriptive Statistics

Load the file and select the English scores.

From the data tab, ask for the Descriptive

Statistics.

The Descriptive statistics provides you with

the measures of central tendency, standard

deviation and sample variance for the data.

Do the same for the Mathematics scores and

compare the results.

Finally, try filtering the data by district and

doing the same.

Comment on your results.

28

Correlation

Load the file entrance-exam which contains data on

the Entrance Examination for students in the

University College.

We want to see if there is any correlation

between the English and Mathematics scores for

this Entrance examination.

Select Data Analysis from the Data Ribbon and

then Correlation from the Tools.

As the input range select $B2:$C$223. Notice that

this range includes rows for which there is missing

data. Use $E$1 as the output range.

Compute the correlation based on this data. The

correlation for these data is 0.623548.

29

If the data file is cleaned by removing all the rows which

contain missing values then the correlation is 0.623574.

Now we are left wondering whether there is any

relationship. Clearly there is but it is not as strong as we

would have liked.

Use this page

http://faculty.vassar.edu/lowry/ch4apx.html

to compute the probability of the computed coefficient and comment on the result.

30

Regression

Use the cleaned data and

perform a regression.

Use mathematics as the Y

or dependent variable

and the English scores as

the independent or X

variable.

Observe that the Multiple

R at 62% compares well

with the correlation. R-

squared itself which

measures the amount of explained variation is very low at 38% and it appears that there is a significant

dependence. Still we are left wondering.

Try this for each district separately.

31

VBA in Excel We can program macros in Excel using VBA.

Browse to the URL: http://www.excel-vba.com/

32

References http://www.exceltip.com/excel_tips/Excel_Financial_Formulas/110.html

http://www.youtube.com/watch?v=3RXBQKLp4vk&feature=related

http://www.tvmcalcs.com/calculators/apps/excel_loan_amortization

http://www.homeandlearn.co.uk/excel2007/excel2007s8p3.html

33

Index

Analyses, 5, 24 Cash Flow, 3, 22 CXC, 9, 24 examinations, 9, 24

GPA, 2, 9, 11 interest, 14, 17, 19 Moving Average, 23 template, 11, 22