Notes to accompany spreadsheet applications
The electronic spreadsheets that accompany this document have been developed to support teachers in the initial use of spreadsheets in teaching elements of General Mathematics. They are examples only, and their use is not mandatory. Teachers are encouraged to experiment with them in order to determine how they may be best used. They are designed for use with Microsoft Excel 97 and should be compatible with later versions of Excel (e.g. Excel 98). They are not compatible with earlier versions of Excel such as Excel 5.0 or with other spreadsheets such as Lotus 123.
NOTE: It is recommended that you do not change the filenames,
as some of the macros within them rely on writing to the existing
filename.
The five spreadsheets are:
-
BOS_Investing_Money.xls – this spreadsheet models the value of two ordinary annuities both as a table and graphically. In addition, simple and compound interest investments are compared both as a table and graphically.
Syllabus link: FM2, FM6.
-
BOS_Mortgage_Analysis.xls – this spreadsheet allows the user to model home loan repayments. Principal, repayments, interest rate and period can be varied. Changes can be viewed graphically as well as in table form.
Syllabus link: FM5 and FM6.
-
BOS_Probability_Sim.xls – this spreadsheet simulates the rolling of 36 dice. The concept of the long run proportion can be demonstrated.
Syllabus link: PB4.
-
BOS_Income_Tax_Calc.xls – this model can be used to calculate income tax and the basic Medicare levy for the 2000 financial year and for the tax scales to be introduced for the 2001 financial year.
Syllabus link: FM3.
-
BOS_Correlation_Model.xls – this spreadsheet allows the user to vary the y-coordinate(s) of some data points displayed on a scatter plot and to observe how the changes affect the line of best fit and the value of the correlation coefficient.
Syllabus link: DA7
Download the package of all
five as a ZIP archive (300k)
Using the spreadsheets
When the spreadsheets are first loaded the “Disable/Enable
Macros” window appears:
Make sure that the Enable Macros option is selected. These
spreadsheets function using macro programs.
The spreadsheets will work best if only one is open at a time
i.e. Select File | Close before opening a new spreadsheet.
In particular the spreadsheet “BOS_ProbSim.xls” employs
manual calculation whereas the others employ automatic calculation.
The user does not need to select these options as the spreadsheets
will select the appropriate settings when loaded.
Suggested teaching/learning ideas
BOS_Int.xls
|
Sheet1 |
Compare the difference in growth over ten periods for
a principal earning simple interest and compound interest.
View the growth on the chart. Simple interest is linear
whereas compound interest is exponential. Also create
a table showing the value for each period. |
|
Sheet2 |
Compare two ordinary annuities. For example, “Does a 30-year annuity of $500 per annum at 15 % per annum catch up in value to a 30-year annuity of $1000 per annum at 10 % per annum and what is the final difference in value? If yes, when does it catch up?”. A table and graph is shown. |
BOS_Mortgage_Analysis.xls
|
Sheet1 |
Vary the amount borrowed, the interest rate, term of loan and monthly repayment. Create a table showing the principal |
|
Sheet2 |
Compare two levels of accelerated repayment with the minimum repayment. |
|
Sheet3 |
Graphically compare two levels of accelerated repayment with the minimum repayment. For example, “What difference does it make to the term of a 30 year loan of $80 000 at a rate of 9 % pa if the minimum repayment is increased by $50 and $100 per month?”. |
BOS_ProbSim.xls
|
Sheet1 |
Simulate the rolling of 36 dice. Model the long-run proportion. Compare theoretical results with experimental results for n trials. |
BOS_Income_Tax_Calculator.xls
|
Sheet1 |
Calculate income tax and basic Medicare levy for the 2000 financial year. |
|
Sheet2 |
Calculate income tax and basic Medicare levy for the 2001 financial year. |
|
Sheet3 |
Compare the income tax payable for the 2000 financial
year tax scales with the tax payable for the 2001 financial
year tax scales. Create and compare your own tax scales. |
BOS_Correlation_Model.xls
|
Sheet1 |
Observe changes to the correlation co-efficient and the gradient/y-intercept of the line of best fit as the y-coordinate of a single data point is varied. |
|
Sheet2 |
Observe changes to the correlation co-efficient and the
gradient/y-intercept of the line of best fit as the y-coordinate
of a single data point is varied. Demonstrate the effect of an outlier on the line of best fit and the removal of outliers from the data (given the cause of the outlying value has been determined). |
|
Sheet3 |
Observe changes to the correlation co-efficient and the
gradient/y-intercept of the line of best fit as the y-coordinate
of a several data points are varied. Examine the pattern of the scatter plot when the line
of best fit has a negative gradient, zero gradient or
positive gradient. Graphically demonstrate the line of best fit when r = 1. |