Graduates’ use of spreadsheet tools
in learning and applying financial mathematics
T Kyng & P Taylor, Macquarie University
In industrial practice, many financial and statistical calculations are done using
spreadsheets.
At university the traditional approach used in teaching is for students to solve
problems using pen and paper and then to do calculations using a calculator.
The traditional approach may be a barrier to the learning of financial theory for
many students.
The traditional approach may not provide graduates with all the skills needed for the
workplace
Graduates’ use of spreadsheet tools
in learning and applying financial mathematics
Our project investigates:
The use of spreadsheets and other financial software in the workplace by recent
graduates, and
The extent to which these skills were learnt at university or on the job,
The type of software skills required by employers of recent graduates,
The opinions and attitudes of postgraduate coursework students regarding the use
of spreadsheets and financial software in the learning of actuarial / financial maths.
The nexus between learning and work in order to determine whether the
university curriculum should be revised – both in order to improve the learning
of financial theory and better to equip graduates with applicable skills for use in
the workplace.
Graduates’ use of spreadsheet tools
in learning and applying financial mathematics
Financial mathematics / theory is used for:
Financial decision making (buy / sell / hold, exercise option / don't exercise)
Valuation of financial contracts / assets / liabilities.
Measurement of & reporting on financial condition
Mathematics / theory was developed before computers were in widespread use.
Its teaching reflects this.
In current industrial practice computers and spreadsheets are usually used to
perform these calculations.
Calculations vary from the straightforward to the very complex.
Can be very tedious and error prone to do by hand.
Graduates’ use of spreadsheet tools
in learning and applying financial mathematics
Ex1 - Simple calculation: amortizing loan
Payment = principal x [i/(1-(1+i)^-n)]
Debt time t = payment x [(1-(1+i)^-(n-t)]/[(1-(1+i)^-n)]]
Ex2- complex calculation: valuation of a chooser option:
the right to choose at time S whether or not we have a call option or a
put option that matures at time T, to buy or sell an asset V for fixed price X
value
= [V.exp(-y.S)N(d1)-X.exp(-r.S)N(d2)]
- [X.exp(-r.S)N(d2')-S.exp(-y.S)N(d1')]
d1(T) = (1/(s*T^0.5))*[ln(V/X)+(r-y+0.5*s^2)T]
d2(T) = (1/(s*T^0.5))*[ln(V/X)+(r-y-0.5*s^2)T]
N(x) = standard normal cdf
Graduates’ use of spreadsheet tools
in learning and applying financial mathematics
Spreadsheet programs:
Ideally suited to performing most of these types of calculations.
Suited for creating tables / graphs / reports and presenting the results.
Are used for these purposes in the financial services industry.
Spreadsheets for teaching financial theory is probably a better way of
preparing students for the workplace than traditional approach to
teaching.
Graduates’ use of spreadsheet tools
in learning and applying financial mathematics
Used surveys to seek out the attitudes / opinions of three groups –
1) postgraduate finance and actuarial students,
2) recent graduates working in the financial services industry,
3) managers who supervise these graduates.
we report on the attitudes of the 3 groups
Graduates’ use of spreadsheet tools
in learning and applying financial mathematics
Methodology
We designed questionnaires to obtain the opinions of three groups:
1) postgraduate commerce students
2) recent graduates working in the financial services industry
3) managers/supervisors of those recent graduates.
The questionnaires were distributed partly by hand (on paper) and
partly by email.
We report on the results obtained from these questionnaires.
Participation in the surveys was both voluntary and anonymous.
Recently hired graduate's
survey
70 particpants: Survey asked questions about:
demographic characteristics of the respondents,
what software they use at work,
how much time they spend using spreadsheets,
what spreadsheet training they had,
the importance of spreadsheet skills and
the advantages and disadvantages of spreadsheets.
Recently hired graduates
GENDER: 27% FEMALE, 73% MALE
AGE DISTRIBUTION:
71% aged 18-25, 24% aged 25-30, 4% aged over 30
TYPE OF WORK THEY DO:
Life Insurance (24%), General Insurance (24%)
Investment / Finance / Banking (38%), Other (19%)
Degree majors:
(a) actuarial studies
(b) Accg / finance
(c) Maths / stats / computing
(g) Other
(90%)
(41%)
(16%)
(5%)
Recently hired graduates
TIME IN CURRENT JOB:
80% of sample in job for less than 3 years
(a) less than 1 year
(43%)
(b) 1 to 3 years
(44%)
(c) More than 3 years
(13%)
TYPE OF SOFTWARE USED AT WORK:
Spreadsheet Software
99%
Word Processing Software
90%
Statistical Software
47%
High Level Programming Languages
30%
Other Software
19%
NOTE THAT ALMOST ALL OF THEM USE SPREADSHEETS AT WORK
PROBABLY GOT NO TRAINING AT UNI IN SPREADSHEETS,
WORD PROCESSING OR STATISTICAL SOFTWARE
Recently hired graduates
TIME SPENT DOING SPREADSHEET WORK
1%
(a) nil
13% (b) 0% - 20%
24% (c) 20% - 50%
61% (d) more than 50%
more than 60% of them spend more than half of their time using spreadsheets
WHAT SPREADSHEETS ARE BEING USED FOR
77% (a) for ad-hoc straightforward calculations
70% (b) for regular / routine calculations
70% (c) for ad-hoc highly complex calculations
23% (d) for statistical modelling
14% (e) other
They’re used for a wide range of things ranging from routine and straightforward
to highly complex.
Recently hired graduates
IMPORTANCE OF SPREADSHEET SKILLS TO EMPLOYEE
(a) not at all
1%
(b) somewhat
29%
(c) fairly
15%
(d) very
18%
(e) essential
37%
IMPORTANCE OF SPREADSHEET SKILLS TO EMPLOYER
(a) not at all
1%
(b) somewhat
22%
(c) fairly
37%
(d) very
21%
(e) essential
19%
Graduate’s perceive spreadsheet skills to be important to have in the
workplace and perceive that employers regard it that way too.
Over 60% think they’re very important or essential.
Recently hired graduates
WHAT SPREADSHEET TRAINING DID THEY HAVE?
25%
24%
14%
0%
38%
None at all
training course by my current / former employer
I learned it from my colleagues / supervisor
I learned it in my university studies
I taught myself
SPREADSHEET TRAINING – WHERE SHOULD YOU GET IT?
24%
5%
57%
14%
0%
(a) in university education
(b) "on the job training"
(c) both (a) and (b) apply to some extent
(d) expect new staff to already know it
(e) other
Over 80% believe there should be some training in spreadsheets at university level.
Only 24% were provided a training course by employer.
Recently hired graduates
Graduates’ attitudes to spreadsheets:
The advantages
71%
(a) easy to use and easy to learn
95%
(b) can develop spreadsheet quickly for calculations, both simple and complex
86%
(c) widely used in industry practice and many staff use them
71%
(d) wide range of capabilities and built in functions and tools
24%
(e) tedious and repetitive tasks can be automated using "macros" or VB
The disadvantages
0%
(a) can be difficult to understand / modify
71%
(b) it can be difficult to verify / audit them.
76%
(c) often inadequately documented
38%
(d) user can modify the code and it is difficult to protect them from this risk
19%
(e) other
Overall it seems graduate attitudes are more positive than negative regarding
spreadsheets. Inadequate documentation was the major negative .
MANAGERS / SUPERVISORS OF RECENT GRADUATES
EMAILED SAMPLE OF 50 PEOPLE
GOT 10 RESPONSES – SMALL SAMPLE BUT RESULTS STILL SIGNIFICANT
Q) What type of graduates do you employ. Select all that apply
Actuarial Studies
100%
Accounting / Finance
30%
Economics
30%
Maths / Statistics / Econometrics
50%
Computer Science
20%
Other
10%
Q) What software do graduates use in their jobs?
Spreadsheet software
word processing software
statistical software (S+, SAS, etc)
high level languages (C, C++, Fortran)
Other
100%
80%
70%
30%
40%
All of them say the graduates use spreadsheets
MANAGERS / SUPERVISORS OF RECENT GRADUATES
Q5: Importance of spreadsheet skills for graduate staff:
Q6: Importance of visual basic skills for graduate staff:
(a) not important at all
(b) somewhat important
(c) fairly important
(d) very important
(e) essential
Q5
0%
0%
20%
20%
60%
Q6
20%
20%
20%
30%
10%
80% of the sample rated spreadsheet skills as at least very important.
VB skills are not seen as being as important as spreadsheet skills
MANAGERS / SUPERVISORS OF RECENT GRADUATES
Q) Does your organisation provide staff with any training in spreadsheets?
30%
30%
40%
0%
(a) We provide basic training course
(b) On the job training from their colleagues
(c) We expect staff to have skills before they join us
(d) Staff learn by self study in their spare time
Q) What is your view of the training needs of recent entrants into the workforce
regarding the use of spreadsheet software?
20%
20%
60%
0%
(a) should be included in university training
(b) it should be “on the job training”
(c) both (a) and (b) apply to some extent
(d) expect staff to already have a basic level of competence
80% of respondents believe university should provide at least some training in
spreadsheet skills
only 30% of respondents say their organisation provides a training course on
spreadsheets
Overall conclusions from graduate and employer surveys:
Both graduates and employers overwhelmingly think that university courses should
provide some training in spreadsheet skills.
Extent to which training courses are provided to new graduates in the workforce is low
100% of the graduate employees and 100% of the employers reported that excel is used
in the workplace by graduates.
Over 60% of the graduates use it more than 50% of the time at work
Spreadsheet skills are seen by both employees and managers as very important skills
Basic spreadsheet skills are more important than visual basic (VB) skills.
Spreadsheet, word processing and statistical software skills are required in the
workplace but not much training provided on this by university courses
Very Brief Literature Review
No previous studies of the use of spreadsheets as teaching tools in actuarial studies.
Research specifically on spreadsheets - Several studies point to their effectiveness for
student learning.
Johnson (2006) sets out to demonstrate the educational value for mathematics teaching
of spreadsheet software, Argues that it facilitates hypothesis testing, the investigation of
variants and algebraic reasoning.
Wagner (2006) concerned with demonstrating pedagogical uses of spreadsheets in the
context of engineering – Observes that across a number of relevant numerical tasks and
problems, “students show significant improvement in their skills”.
Kademan (2005) argues, similarly, that spreadsheet software is highly useful in science
teaching, because of its data manipulation capacity.
Various studies have been done on the use of software in teaching mathematics / science
at university level, both regarding the value of these as teaching tools, and the attitudes
of staff and students to their use.
From a pedagogical viewpoint, various investigators found value in using computers to
do complex calculations that have in the past been done by hand.
Overall Conclusions
The results of our study show that students do benefit from the use of spreadsheets in
their university training, both in terms of enhancing their learning and in terms of being
better prepared for the workforce.
In this context, it is worth mentioning that in industry practice, it is important to be able
to do the numerical calculations efficiently and accurately and this means using
computers and software to do it.
For example, in statistics most of the formulae used for model fitting and hypothesis
testing are too complex and tedious to do by hand. No practicing statistician does these
sort of calculations by hand. The focus is on using and interpreting the results and not
on the calculation process.
The same applies to actuarial science. It is important to understand the mathematical
theory but it is just as important to be able to apply it and this requires computer skills.
Both a theoretical understanding and ability to apply it are required.
University training in financial mathematics and actuarial mathematics should be
updated and provide students with training in spreadsheets and other software and how
to apply these in practical problem solving.
The student questionnaire:
One hundred postgraduate students participated in the survey. The
cohort of students was comprised of about equal proportions of
actuarial students and accounting / finance students.
The survey was conducted at the end of semester. By this time, the
students in the cohort had been exposed to the use of Excel for
performing financial calculations every week of the semester.
During their undergraduate studies, these students would have been
taught financial mathematics in the traditional way. At the start of the
semester the level of spreadsheet expertise in the cohort ranged from
nil to relatively experienced. Most students had little expertise in
spreadsheet programming.
The student questionnaire:
used a 5-point Likert scale.
All but two of the questions fall into one of the three following groups:
those inquiring into attitudes to the use of software programs in the
classroom, without comparing this approach with traditional teaching
methods (questions 1 and 10),
those asking the student to compare the merits of traditional and
computer-based learning methods as ways of learning and mastering
university work (questions 2, 3 & 4);
those inquiring into student perceptions of the value of using
computers as preparation for the workplace (questions 5, 6 and 7).
Student survey questions
Q1 complex formulae /tedious calculations can be a barrier to learning the material.
●Q2 In learning financial mathematics using spreadsheets is preferable to using a calculator .
●Q3 Using spreadsheets for the calculations makes it easier to learn the material.
●Q4) In the practical application of finance theory / financial mathematics / actuarial mathematics,
using a computer and spreadsheets is preferable to using a calculator for performing the
calculations involved.
●Q5) Using spreadsheets for problem solving/ learning of finance / financial mathematics / actuarial
mathematics makes me better prepared for the workforce.
●Q6) It is important to receive additional training by the university in the use of spreadsheets and
computer calculations so that I am better prepared for the workforce.
●Q7) Much of my training in the use of spreadsheets and Microsoft Excel that I received from my
university course will come in handy when I enter the workforce.
●Q8) Students should be allowed to use computers and spreadsheets in their exams instead of a
calculator if they wish to.
●Q9) Using spreadsheets and computers to solve financial / mathematical / actuarial problems is an
important aspect of this university course.
●Q10) It can be an intellectual challenge to solve financial / mathematical / actuarial problems
using computers and spreadsheets.
●
Discussion of the Findings: Student Survey
Figure 1 shows the average score by question.
Scores are 3 = “neutral” 4 = “agree” 5 = “strongly agree” etc
We see Q1 has lower level of agreement than other Q's
In figure 2 we aggregate the results into 3 categories:
agree / strongly agree, neutral, and disagree / strongly disagree.
This graph shows the relative proportions of responses in these 3
categories for each question.
This graph shows visually that for question 1, opinion was polarised
whereas for all the other questions the “agree” category clearly
dominated the other 2 categories of response.
Student survey
Figure 1: average score by question
s c o re b y q u e s tio n
5.0
4.5
4.0
3.5
score
3.0
2.5
2.0
1.5
1.0
0.5
0.0
Q1
Q2
Q3
Q4
Q5
Q6
q u e stio n
Q7
Q8
Q9
Q 10
Student survey
Figure 2: level of agreement by question
le v e l o f a g r e e m e n t b y q u e s t io n
100%
90%
80%
proportion
70%
60%
50%
40%
30%
20%
10%
0%
Q1
Q2
Q3
Q4
Q5
Q6
q u e sti o n
n e u tr a l
a g r e e / s tr o n g ly a g r e e
d is a g r e e / s tr o n g ly d is a g r e e
Q7
Q8
Q9
Q 10
Student survey
Discussion of results of student survey
Q 1 the only one of our questions that did not get a generally positive answer.
Q 10 – the other question in group (a) – shows that our cohort of students did not
view the classroom use of computers simply as a means of “letting the computer
do the work” but found using computers to solve complex problems to be an
intellectually interesting process..
The answers to Q2, 3 and 4 –group (b) were all positive and clearly show that our
students preferred computers to calculators for their studies.
Their answers to Q3 in particular supports what we have inferred from their
answers to question 10 – that computers are a valuable learning tool.
The students answers to Q5, 6 and 7 – group (c) questions – were also all
positive, showing that the cohort saw the classroom use of computers as an
important part of their professional training for the workplace.
Descargar

Graduates’ use of spreadsheet tools in learning and