# Year 12 - Maths Apps

Modelling

World Record 100m Sprint and High Jump:

The dates and times (measured in seconds) for the world 100 m sprint records for men and women up to 1973 are shown below.  The aim of this exercise is to predict the year when the men's time and the women's time will be equal?  Of course to do this we will make many simplifying assumptions such as assuming that the improvement of times that has occurred with both men and women occurs in a linear fashion.

 Women Year Time Athlete 1934 11.7 S. Walasiewicz (POL) 1937 11.6 S. Walasiewicz (POL) 1948 11.5 F. Blabnkers-Koen (HOL) 1952 11.4 M. Jackson (AUS) 1955 11.3 S. de la Hunty (AUS) 1961 11.2 W Randolph (USA) 1965 11.1 I. Kirszenstein (POL) 1968 11.0 W. Tyus (USA) 1973 10.9 R. Meissner-Stecher (GDR) 1973 10.8 R. Meissner-Stecher (GDR) Men Year Time Athlete 1912 10.6 D. Lippincott (USA) 1921 10.4 C. Paddock (USA) 1930 10.3 P. Williams (Canada) 1936 10.2 J. Owens (USA) 1956 10.1 W. Williams (USA) 1968 9.9 J. Hines (USA)

Task #1: You will now need to copy this data to an Excel spreadsheet and create two scatter graphs appropriately labelled.  One for the women and the other for the men.  The year should be on the horizontal and the time on the vertical.  This is opposite to what we would normally do as in this case the time is NOT the independent variable but rather it depends on the year.  These graphs will need to be printed at some stage so ensure that they are appropriately set out and labelled.

Task #2: Add a trend line to both graphs and also display the equation of the line on each graph.  Also display the R Squared value on the chart.  As you may already know if the value of R^2 is one then the data is said to perfectly fit the straight line model.  The further this R^2 value is from one the less this model applies.  See below

Don't forget to also tick the DIsplay R^2 value on chart

Task #4:The equation that is now displayed on each graph will now need to be copied to Graphmatica.  Placing the equations in Graphmatica will allow us to see the point where these lines intersect.  This is the point when the women's and men's times are equal.

After you have pasted the equation from Excel into Graphmatica click the draw button.  Now delete the first equation and paste the second.  Draw this graph.

It is now important to alter the scale in Graphmatica so that you can better appreciate the nature of the graphs that you have drawn.  To do this open the dialog boxes as shown below,

1) If the lines intersect, which year might you expect the women's record will equal the men's.

2) State the slope and the y-intercepts for each equation.  What do you notice about the slope?

3) Comment as to how reasonable it is to use a straight line to predict future world record times.

4) Is the difference between the times of men and women improving?  Suggest why/why not.

5) Do some internet research and find the more recent times for both men and women.  Add them to your spreadsheet.  Redraw your graphs.  What prediction now exists for the year when the men's time and the women's time will be equal?

6) Your equations will look similar to the following, y = -0.0112x + 31.876. Of course the y is time and the x is year.  The equation can therefore be rewritten time = -0.0112(year) + 31.876.

To find the exact time when these equations will equal each other, that is when the time for both men and women will be the same, allow them to equal each other and solve for year.  How does the value calculated here compare to the point when the graphs intersect?

Men's and Women's High Jump Record:

The following table gives the world records for both men's and women's high jump measured in metres.  Copy this data to an Excel spreadsheet and analyse the data as described above for the running.  Comment on any differences that you observe with respect to the data you graphed for the 100m sprint previously.

 Men Year Height Athlete 1912 2.00 G. Horine (USA) 1934 2.06 W. Marty (USA) 1941 2.11 L. Steers (USA) 1956 2.15 C Dumas (USA) 1960 2.22 J. Thomas (USA) 1961 2.25 V. Brumel (USA) 1973 2.30 D. Stones (USA) 1980 2.35 J. Wszola (POL) 1984 2.39 Z. Jianhua (PRC) Women Year Height Athlete 1932 1.65 J. Shiley (USA) 1943 1.71 F. Blankers-Koen (HOL) 1956 1.75 I. Balas (ROM) 1958 1.80 I. Balas (ROM) 1960 1.85 I. Balas (ROM) 1961 1.90 I. Balas (ROM) 1974 1.95 R. Witschas (GDR) 1977 2.00 R. Witschas-Ackerman (GDR) 1984 2.07 L. Andonova (Bulgaria)

It is important to give children the correct dose of medicine.  But how can you convert a dose prescribed for adults to a safe dose for children.  There are three rules that calculate the child's dose from the given adult dose.

The rules are,

1) Young's Rule

Child's Dose = (Age of child in years / age of child + 12) x adult dose

2) Clarke's Rule

Child's Dose = (Weight of child in kg / 70) x adult dose

3) Cowling's Rule

Child's Dose = ((Age in years + 1) / 24) x adult dose

For Cowling's Rule the formula is read in the following way - Child's Dose = Age Next Birthday divided by 24 times Adult Dose.  The Age Next Birthday  is the same as Age in Years + 1.  Your task is to investigate these three formulas and what they predict for children from the age of 4  to the age of 15.  After 15 an adult dose can be given.

For this activity you will  use Excel.  Set up you spreadsheet as shown in the following graphics.  You will use a single worksheet for this.  Note that the numbers shown in these three graphics should not be included at this stage except for the Age of child in years column and the Adult Dose column.  The Child's Dose (Column A) and Adult Dose (Column D) are calculated using formulas.  You will need to convert the formulas written above to a form that can be used by Excel.

When you write a formula in Excel you need to start by writing an equals sign, The remainder of the formula then refers to the cells you wish the formula to operate on.  For example to add cells A1 and A2 together you could write a formula in A3.  The formula would be =A1+A2

For Young's Rule.  Remember ONLY type the headings and the the age of the child and the adult dose.  We will use Excel to calculate the other cells.

On row 18 type the information for Clarke's Rule.  Remember ONLY type the headings and the the age of the child and the adult dose.  We will use Excel to calculate the other cells.

Finally on row 32 type the information for Cowling's Rule. Remember ONLY type the headings and the the age of the child and the adult dose.  We will use Excel to calculate the other cells.

You will need to write formulae in appropriate cells to allow Excel to do the calculations for you.  The formulae for Young's Rule are shown in the graphic below.  Remember that all formulas must be written so they commence with an equals sign.

You will now need to write formulae for Clark's Rule and Cowling's RuleThe formulae to translate are shown again  below.

Clarke's Rule:

Child's Dose = (Weight of child in kg / 70) x adult dose

Cowling's Rule:

Child's Dose = ( (Age in years + 1) / 24) x adult dose

You will note that Clarke's Rule relies on knowledge of the child's weight rather than the age.  To find out the relationship between a child's weight and height, for an average population, click on the following link.

http://www.fpnotebook.com/END27.htm

Copy the appropriate data into your spreadsheet.   For Clarke's Rule the child's weight is important.  If you are male use the boy's data if you are female use the girl's data.  Don't forget to use kilograms and not pounds.   Remember that column B should increase in one year intervals to 15.  Copy the 5 mL adult dose down column D

Remember how to copy and paste formulae so as to make calculations very quickly.  Don't retype formulas into each cell.

Plotting the Data:

Plot the data for each formula using x-y scatter graphs.  Don't forget to fully label the axes and name each graph.  Here are a few hints in case that you have forgotten how to do these graphs.

1) Highlight the data you wish to plot. See diagram below.

2) Click the icon on the menu bar.  Then choose the options that are shown below.

3) Press Next

4) You will notice that the data is plotted with the independent variable, Age of Child,  on the vertical axis.  Although this is not the usual to plot the data we will leave it this way for this exercise.

5) Click the Next box and the following dialog box will appear,

6) Include the title shown, also label your X and Y axes.  Click on the Gridlines tab and check major gridline for the X-axis.  On the Legend box uncheck the Show Legend box.  Leave the other tabs in their default states.

7) After you press Finish the following graph will appear.

Repeat this procedure for Clarke's Rule and Cowling's Rule

To make your graphs full page right click on the thumbnail version.  The following menu box will appear.   If you don't get this menu box move the mouse pointer elsewhere on the graph and then right click.

Choose Location and then choose As new sheet.

Questions:

Print each graph. Look carefully at the prediction each graph makes.

1) Does each formula make similar predictions?  What prediction do the graphs make for a child of 4 10 and 15 years of age..

2) What differences are there between the predictions?

3) Might these differences be significant?

4) Does there appear to be any inconsistencies?

5) Does any formula appear any better for determining the child's dose?

Smoothing Time Series:

By now, you should appreciate the fact that fitting linear trend lines to time series that are not really linear is both bad mathematics and bad policy — it doesn’t work! So how can we have a method that generates trend lines for such time series? If the non-linear nature of the data is random we can use a technique called smoothing. If the non-linear nature is seasonal, we use a method called seasonal adjustment.

Moving-average smoothing:

This technique relies on the principle that averages of data can be used to represent the original data. When applied to time series, a number of data points are averaged, then we move on to another group of data points in a systematic fashion and average them, and so on. It is generally quite simple. Consider the following example:

Notice how the third column is computed from the first two.

1. Take the first three t points (1, 2, 3) and find their average (2); take the first three y points in the table (12, 10, 15) and find their average (12.3).

2. Take the next three t points (2, 3, 4) and find their average (3); take the next three y points in the table (10, 15, 13) and find their average (12.7).

3. Repeat until you reach the last three t points.

4. Take the last three t points (7, 8, 9) and find their average (8); take the last three y points in the table (18, 21, 19) and find their average (19.3).

As we use three points to average, moving along the table from left to right, this is called a 3-point moving average smoothing. Note: The calculation of the t values is quite simple. We are free to choose any number of points for our smoothed graph; we could have a 4-point smoothing, a 5-point smoothing or even an 11-point smoothing. Although it is preferable to choose an odd number, such as 3 or 5, it is possible to choose even numbers as well, with a slight change in the method. In either case it does not matter how many points are in our time series.

Moving average smoothing with odd numbers of points As seen above, the method for smoothing with an odd number (3, 5, . . .) is quite simple, and can be done in a vertical tabular form. It is crucial that the time values be equally spaced, but they don’t have to be in the sequence 1, 2, 3. Note: There are fewer smoothed points than original ones. For a 3-point smooth, 1 point at either end is ‘lost’, while for a 5-point smooth 2 points at either end are ‘lost’.  The main reason for using a smoothing technique is to remove irregularities or wild variations in our time series.

Moving average smoothing using a spreadsheet

A spreadsheet can be devised to calculate the average data values and then the new set of smoothed points plotted on a graph.

Below are the formulas used. Note the row and column numbers carefully. Add or delete rows from the middle of the sheet (around row 9) rather than at the ends. There is no need to calculate the first (E6) and last (E13) average, as these are the ‘lost’ values. It should be clear how to turn this into a 5-point, or 7-point smooth. Why wouldn’t we go any further?

Task#1:  Graph (scatter plot) the original data on the Excel spreadsheet as well as the smoothed 3-point moving average data.  Comment on the two sets of data.

Task#2: Carry out 5-point and 7-point moving average calculations on the spreadsheet.  Plot all this data on the one graph.  Comment on the data.

1) Consider the quarterly rainfall data below. Rainfall has been measured over a 3-year period. Since the data are seasonal, perform a 3-point moving average and comment on whether there is a trend other than the cyclical one.

2) The sales of a new car can vary due to the effect of advertising and promotion. The sales figures for Nassin Motor Company’s new sedan are shown in the table. Perform a 5-point moving average to smooth the data. Plot the data,

Smoothing with an even number of points:

As mentioned in the previous section, it is usually preferable to use an odd number of points. However, there are times when an even number of points can be used — that is, a 4-point, 6-point or even 10-point moving average. When we used an odd number of points, the result was automatically centred; that is, the y-data had the same t-values as the original (except at the first and last ‘lost’ points). This does not occur with an even-point smoothing, as shown in the following example of a 4-point moving average.

Task#4: Look carefully at the example shown above.  Your task is to devise a spreadsheet that will generate a 4-point moving average analysis of the example shown.  Take care to ensure that the averages that you calculate are associated with the appropriate time series data.  Plot the data.  Check that the data that you plot is similar to that shown above.

1) The price of oranges fluctuates from season to season. Data have been recorded for 3 years. Perform a 4-point centred moving average, plot the data and comment on any trends.  What is the increase in price over the time period as suggested from the moving average analysis.

2) The following table shows the share price index of Industrial Companies during an unstable fortnight’s trading. By calculating a 4-point centred moving average, determine if there seems to be an upward or downward trend.

Median smoothing:

An alternative to moving average smoothing is to replace the averaging of a group of points with the median of each group. Although no particular mathematical advantage is gained, it is a faster technique requiring no calculations (provided you use odd-point median smoothing). Often it can be done directly on a graph of a time series.  Generally, the effect of median smoothing is to remove some random fluctuations. It probably performs poorly on cyclical or seasonal fluctuations — unless the size of the range being used (3, 5, 7, . . . points) is chosen carefully. We will use the computer to carry out this process.

The following example shows how 3-point median smoothing can be done directly on the graph.

For this exercise instead of using the =Average() function as we did in the moving average situation we will use the =Median() function.

1) Perform a 3-point median smooth on the following data and plot the result. Comment on any trends that you find. Also perform a 3-point moving average smoothing.  Compare the graphs that are produced.  Which method appears to be better able to demonstrate any trend.

2) The maximum daily temperatures for a year were recorded as a monthly average. Perform a 3-point median smooth on the data. Also perform a 3-point moving average smoothing.  Comment on your result.

3) Perform a 5-point median smoothing on the data in the following table, which represents the share price of the Pear-Shaped Computer Company over an 8-week trading period.   Compare this with a 5 point moving average smoothing. Note that the table below can be paste directly into Excel.  You will need to convert it to two columns only, however.  One column for Day and the other for Price

 Day Price Day Price Day Price Day Price 1 0.87 11 1.04 21 1.01 31 1.89 2 1.34 12 1.19 22 0.98 32 1.75 3 1.14 13 1.09 23 1.12 33 1.55 4 1.08 14 1.10 24 1.07 34 1.35 5 0.89 15 1.04 25 1.23 35 1.15 6 0.67 16 1.02 26 1.32 36 1.30 7 0.98 17 0.94 27 1.45 37 1.20 8 1.23 18 0.98 28 1.56 38 1.17 9 1.06 19 0.89 29 1.67 39 1.07 10 1.08 20 1.00 30 1.78 40 0.87

As we have seen in the sections on fitting a straight line to a time series, it is difficult to find an effective linear equation for such data. As well, the sections on smoothing indicated that seasonal data may not lend themselves to the techniques of moving-average or median smoothing. We may just have to accept that the data vary from season to season and treat each record individually. For example, the unemployment rate in Australia is often quoted as ‘6.8% — seasonally adjusted’. The Government has accepted that each season has its own time series, more or less independent of the other seasons. How can we remove the effect of the season on our time series? The technique of seasonally adjusting, or ‘de-seasonalising’, will modify the original time series, hopefully removing the seasonal variation, and exposing any other trends (secular, cyclic, random) which may be ‘hidden’ by seasonal variation.

De-seasonalising time series

The method of de-seasonalising time series is best demonstrated with an example. Observe carefully the various steps, which must be performed in the order shown.

This process can be carried out using a spreadsheet.  The following example shows how the spreadsheet for the example shown can be arranged.

Step 1. Yearly averages are calculated just below the data table.

Step 2. Each term is divided by the appropriate yearly average.

Step 3. Seasonal indices are calculated (to the right of step 2).

Step 4. ‘De-seasonalised’ data are calculated (below step 2).

Task#7: Your task now is to devise appropriate formulae that will generate the numbers as shown in the example above.  Don't forget that your spreadsheet solution must be fully dynamic.  This means that apart from the actual data all cells contain a formula.  Remember that Absolute Referencing may be useful as well as the more frequently used relative referencing.  Plot the data.  Your graph should look similar to that shown above.

1) The price of sugar (\$/kilo) has been recorded over 3 years on a seasonal basis.

a) Compute the seasonal indices.

b) De-seasonalise the data using the seasonal indices.

c) Plot the original and de-seasonalised data.

2) Data on the total seasonal rainfall (in mm) have been accumulated over a 6-year period.

a) Compute the seasonal indices.

b) De-seasonalise the original time series.

c) Plot the original and de-seasonalised time series.

3) Sales (in thousands) of a new computer chip have been recorded for each quarter over 3 years.

a) Compute the seasonal indices.

b) De-seasonalise the time series.

c) Plot the original and de-seasonalised time series.

4) It is possible to seasonally adjust time series for other than the usual 4 seasons. Consider an expensive restaurant that wishes to study its customer patterns on a daily basis. In this case a ‘season’ is a single day and there are 7 seasons in a weekly cycle. Data are total revenue each day shown in the table which follows. Modify the spreadsheet solution to allow for these 7 seasons and de-seasonalise the following data over a 5-week period. Comment on your result, supporting your statements with mathematical evidence.  Don't forget to graph the original data and the seasonally adjusted data.