R=125, n=25*52 = 1300, i =j 52/52 = 0.104/52 = 0.002, we have a simple annuity.
FV = R((1+i)n‐1)/i = 125(1.002 1300‐1)/0.002 = 776,801.52 i.e. They will accumulate $776,801.52
Or in Excel: =FV(0.104/52,1300,‐125) => They will accumulate $776,801.52
c) Determine how much this will accumulate to by the time that BetterLifeCo need to make the payout.
[2 marks]
It will attract two more weeks interest so that FV(at payout) = 776801.52*(1.002) 2 = $779,911.83
Or in Excel: =FV(0.104/52,2,0,‐776801.52) => Accumulated amount is $779,911.83
d) Thus, explain whether under this scenario insuring JoJo would have been a profitable exercise for
BetterLifeCo. [1 marks]
By the time of the payout (week 1302) BetterLifeCo will have accumulated $779,911.83 from JoJo’s premiums but will have to pay out $1,000,000, thus leaving them $220,088.17 out of pocket on JoJo’s policy. We can conclude that this has not been a profitable exercise.
e) Determine the minimum time JoJo would have needed to live in order for BetterLifeCo to break even on the policy. [NB this question is a little more difficult and intended to provide a bit of stretch for stronger students because you need to take into account the two week delay to payout after JoJo’s death.]
[4 marks]
We can’t use the find n formula with FV=1000,000 because this assumes that there are premiums paid right up to the time of the payout, which is not true.
The easiest way to proceed is to discount the million dollars by two weeks (i.e. bring it back from period n+2 dollars to period n dollars) and then use this as the FV in the find n formula:
Value of payout in period n = 1000000 * 1.002‐2 = 996,011.97
Then n = ln(FVi/R+1)ln(1+i) = ln(996011.97*0.002/125+1)/ln(1.002) = 1416.14.
We need to round this up to the next integer, so we need JoJo to live at least 1417 weeks in order for
BetterLifeCo to at least break even.
OR for the algebraically minded, we have:
FV(premiums at payout) = FV(payout)
R((1+i) n‐1)/i * (1+i) 2 = 1000000
=> 125(1.002 n‐1)/0.002 * (1.002 2) = 1000000
=> 1.002 n‐1 = 1000000*0.002 / 1.0022/125
=> 1.002 n = 1000000*0.002/1.0022/125 + 1
=> n = ln(1000000*0.002 = 1416.14/1.002 2/125+1)/ln(1.002) = 1416.14 round up to 1417
OR we can do it by brute force by building a sinking fund table, adding in an extra column showing what
the accumulated amount would be with another two weeks interest..

From which we can also obtain the same answer of 1417 weeks.
There are probably other ways also.
Question 3 (16 marks)
Priya takes out a loan from the Deep Bay Bank for $30,000 to buy a new Prius. The interest rate changed is j 26 =
7.54% p.a. The loan is to be repaid over 4 years with the first payment due in a fortnight’s time. The terms of the
loan are that it is an interest only loan for the first year (first 26 payments), at which time it converts to a fully
amortized P&I loan.
a) Illustrate all the cash flows associated with this scenario as a fully labelled timeline diagram.
[3 marks]
PV=30 ($’000)
R R R R’ R’ R’ R’ R’
|______|______|__…__|______|______|__…___|______|______|
0 1 2 26 27 28 102 103 104 Weeks
<‐‐‐ interest only ‐‐‐><‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ P&I ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐> (optional extra detail)
b) Determine the size of the fortnightly payments made during the first year. Enter this value to the nearest cent as an answer to “Assignment Module 1 – Milestones” question 3b. You can have as many attempts at
this as you wish without penalty, but this question will close at 9:00pm on Wednesday 17 August. There will
be no marks available for this question part after then. NB You do NOT need to include your workings for this
question in your submitted assignment paper.
[1 mark]
PV=30000, i=j 26/26 = 0.0754/26 = 0.0029. R = 30000 * 0.0029 = $87.00 is the payment size.
c) Determine the size of the outstanding principal at the end of the first year. Enter this value to the nearest cent as an answer to “Assignment Module 1 – Milestones” question 3c. You can have as many attempts at this as you wish without penalty, but this question will close at 9:00pm on Wednesday 17 August. There will  be no marks available for this question part after then. NB You do NOT need to include your workings for this question in your submitted assignment paper.
[1 mark]
In an interest only loan the OP is unchanged, as the interest paid is enough to service the debt but not enough to make any impression on the amount owed. So the answer is $30,000.00
d) Determine the size of the fortnightly payments during the P&I phase of the loan. Enter this value to the
nearest cent as an answer to “Assignment Module 1 – Milestones” question 3d. You can have as many
attempts at this as you wish without penalty, but this question will close at 9:00pm on Wednesday 17
August. There will be no marks available for this question part after then. NB You do NOT need to include
your workings for this question in your submitted assignment paper.
[1 mark]
For the P&I phase we have PV = 30,000, n = 3*26 =78, and i = 0.0029. We have a simple annuity.
PV = R(1‐(1+i)‐n)/i => 30000 = R(1‐1.0029‐78)/0.0029 => 30000 = 69.71734368 R => R = 430.3089937
=> The payment size is $430.31
OR in Excel: =PMT(0.0754/26,78,‐30000) => The payment size is $430.31
At the same time that Priya takes out the loan for the car, she also takes out a second loan for $10,000 from the
Mountain River Bank in order to pay off her credit card debit. The Mountain River Bank change interest at a rate of
j 12 =10.08% p.a. Priya intends to pay off this loan through payments of $300 per month, starting in a month’s time,
until the loan is paid off.
e) Find the total Priya owes (i.e. across both loans) two years after she took out the loans.
[4 marks]
We need to find the OP on each loan and then add them together.
Car Loan:
We still have two years of payments to make (52 payments) of 430.31 each. This is a simple annuity with
i=0.0029, n=52, R = 430.31 => OP = R(1‐(1+i)‐52)/i = 430.31(1‐1.0029‐52)/0.0029 = $20724.81
Or in excel: =PV(0.0754/26,52,‐430.31) => $20724.81 is the car loan OP.
Credit Card:
The easiest way to proceed is to use the alternative definition of OP, FV(principle) – FV(payments made).
This saves us the trouble of having to work out n and the partial payment size. We have a simple annuity
with R=300, i=j 12/12 = 0.1008/12 = 0.0084, and we’ve made n=24 payments.
OP = FV(10000)‐FV(payments made) = 10000*1.0084 24 – 300(1.0084 24‐1)/0.0084 = $4282.97
OR In Excel: =FV(0.1008/12,24,0,‐10000) ‐ FV(0.1008/12,24,‐300) => $4282.97 is the credit card OP
So the total amount owed is 20724.81 + 4282.97 = $25,007.78
f) Given that most banks (including the Deep Bay Bank and the Mountain River Bank) have similar cost
structures and similar investment options, explain a plausible reason why the Deep Bay Bank may have been
able to offer a much lower interest rate on the car loan than the Mountain River Bank could for the credit
card loan. (50 to 100 words).
[2 marks]
We know that institutions need to charge a higher interest rate when there is a higher risk of the
customer defaulting. So the most likely reason for the difference in interest rates changed is related to
risk. Because the Deep Bay Bank loan is being used to buy an asset (a car) it is possible that this is a

secured loan… that is if Priya defaults the bank can take procession of the car. This significantly
reduces the risk that the bank faces on the loan, and allows it to offer a lower interest rate.
a) [Tricky bit for stronger students.] The Deep Bay Bank charges a fee of $600 to convert the loan from an
interest only loan to P&I loan, and this fee is paid at the same time as the first payment on the P&I loan. That
is, payment number 27 rather than being R’ is actually R’+600. Determine the j 26 interest rate that Priya is
effectively paying on the car loan.
[Hints: You could attempt this as a find i “price is right” approach as outlined in the notes, although it will be
a fair bit of work for four marks. Alternatively, you could do a little research on Internal Rate of Return, and
in particular on Excel’s IRR function. The IRR that the bank achieves on the loan will be the same as the
interest rate that Priya is effectively paying.]
[4 marks]
In Excel:
Set up the entire payment stream in column A of a spreadsheet, with A1 = ‐30000, A2:A27 = 87, A28 =
payment+fee = 1030.31, and A29:A105 = 430.31.
Then =IRR(A1:A105) gives 0.00320821, the per period rate of return. Multiplying this by 26 gives us
J 26=8.3414%, the rate that Priya is effectively paying.
Price is right:
PV(what you get) = PV(what you pay)
30000 = PV(26 period SA of $87) + PV($600 fee) + PV(78 P&I Payments of $430.31)
30000 = 87(1‐(1+i)‐26/i + 600(1+i)‐27 + 430.31(1‐(1+i)‐78)/i * (1+i)‐26
Start with a guess of i=0.0029, the value of i that we’ve been using so far. You can see below that the
right hand side (30554.94) is greater than the left hand side (30000.00) indicating that we need to use a
higher discount rate. In the second guess we try 0.0035 and find out that we’ve gone too far.
Guess number Value of i Principal Int only fee P&I total
1 0.0029 30000.00 2175.79 554.88 27824.27 30554.94
2 0.0035 30000.00 2158.53 545.99 26781.24 29485.76
3 0.0032 30000.00 2167.13 550.41 27297.08 30014.63
4 0.00321 30000.00 2166.85 550.26 27279.71 29996.82
5 0.003208 30000.00 2166.90 550.29 27283.18 30000.38
After five guesses we’ve honed in on i=0.003208, and after a few more will fine tune to 0.003208213.
Multiplying this by 26 gives us J 26=8.3414%, the rate that Priya is effectively paying.

MODULE 2 – T OPIC 1 (EXPLORING D ATA WITH PIVOT T ABLES)
Weight: This topic is worth 25 marks out of 100.
Tools required: You should use the PivotTable tools that you learned about in computer lab 2.
Scenario. Concerns have been raised about the effectiveness of this season’s flu vaccination after it was noticed that
a high proportion of people with severe illness have been vaccinated. Data has been collected on the following
attributes of the flu sufferers (see the file FluVac.xls).
ID An identification number for the individual with flu
A GE The person’s age in years
V AC S TATUS Whether or not the person has been vaccinated against the flu
S EVERITY The severity of the illness (mild or severe)
A GE C AT A categorical version of the age variable with 5 bins (0‐20, 21‐40, 41‐60, 61‐80, 80+)
Your role is to investigate the data to determine if vaccination is effective or not.
Auto‐graded questions (submit answers via MyLO Quiz: Assignment Milestones)
1. What percentage of vaccinated and unvaccinated people experience severe illness? [1 mark]
63.2% of vaccinated people experience severe illness compared to 53.5% of unvaccinated people.
2. What is the average age of patients that are severely ill compared to patients with mild illness? [1 mark]
The average age of people that are severely ill is 65 compared to an average age of 44 for people with mild
illness.
3. What is the average age of people that are vaccinated compared to people that are unvaccinated? [1 mark]
The average age of people that vaccinated is 71 compared to an average age of 37 for people that are
unvaccinated.
4. What proportion of people in the age category 41‐60 are vaccinated? [1 mark]
41.3% (0.413) of people in the age bracket 41‐60 are vaccinated.
5. Is there any age category where a higher proportion of vaccinated people are severely ill compared
unvaccinated people? [1 mark]
There is no age bracket where a higher proportion of vaccinated people are severely ill, despite the fact that
overall they are more likely to be severely ill (note this is an example of Simpson’s Paradox.)

Questions for the manually graded section of assignment
1. Based on your investigation of the data set create two well‐formatted tables that illustrate some important
features of the data. Your tables should have informative labels and a readable number of decimal places.
Each table should include an informative caption. [8 marks]
Marking scheme: (4 points per table)
 Informative Labels and no extraneous labels (e.g. get rid of Excel defaults) ‐ 1 mark
 Decimal place format (max 2 dp) ‐1 mark
 Informative Caption – 2 marks
There are a range of good tables to include, e.g. something that shows that older people are more likely to be
vaccinated, something that shows older people are more likely to get severe illness.
 Subtract 1 mark if no table present that illustrates any of this.
Examples
Table 1
Age bracket Unvaccinated Vaccinated
0‐20 91.7% 8.3%
21‐40 84.0% 16.0%
41‐60 58.7% 41.3%
61‐80 10.2% 89.8%
80+ 2.3% 97.7%
Overall 43.0% 57.0%
The proportion of people who are vaccinated increases with age. In the oldest age bracket almost
everyone is vaccinated whereas in age groups younger than 60 it is more common to be unvaccinated.
Table 2
Age bracket mild severe
0‐20 83.3% 16.7%
unvaccinated 81.8% 18.2%
vaccinated 100.0% 0.0%
21‐40 70.0% 30.0%
unvaccinated 64.3% 35.7%
vaccinated 100.0% 0.0%

41‐60 28.3% 71.7%
unvaccinated 11.1% 88.9%
vaccinated 52.6% 47.4%
61‐80 38.8% 61.2%
unvaccinated 20.0% 80.0%
vaccinated 40.9% 59.1%
80+ 11.6% 88.4%
unvaccinated 0.0% 100.0%
vaccinated 11.9% 88.1%
Across each of 5 age brackets the proportion of people with severe illness is lower for vaccinated people. In
general, younger people are less likely to get severely ill.
2. With reference to the tables you have made, is there evidence that the vaccine is effective or not? [4 marks]
Key points (~2 marks each)
 There is strong evidence that the vaccinate is effective. In each age bracket people that are
vaccinated are less likely to be severely ill (Table 2).
 The reason that more vaccinated people are severely ill when considered over the whole data
set is that older people are more likely to be vaccinated (Table 1) as well as more likely to
become severely ill.
3. One reason why it is difficult to ascertain cause and effect based on association is that there may be
confounding variables, these are variables which are related to both the explanatory (independent) and
response (dependent) variable. In the data set presented here which variable is explanatory, which is
response, and which is confounding? [3 marks]
Explanatory – Vaccination Status
Response – Severity of illness
Confounding ‐ Age
4. Imagine that the data is this study has been collected by surveying people who present at hospital with flu
symptoms. Can you identify any potential biases in this form of data collection that might influence our
ability to answer our key question? How else could data be collected to avoid this bias? [5 marks]
Key points (1‐2 marks for each reasonable point – they don’t have to be identical to below)
 This method of data collection would suffer from selection bias as people with mild illness may be
less likely to present at the hospital, whereas people with severe illness would be more likely to
appear at the hospital.
 For this reason we expect that the sample would not be representative of the whole population of
people that get the illness
 It is also possible that different age groups may find it easier or more difficult to get to hospital. For
example, people of working age may have pressure not to take time off work when mildly ill.

 An alternative would be to try and get a simple random sample from the population, or a stratified
random sample where you try to get a fixed number of people from different subpopulations of
interest (e.g. vaccination status, age bracket). This might be difficult to do in practice.

MODULE 2 – T OPIC 2 (LINEAR REGRESSION)
Weight: This topic is worth 25 marks out of 100
Tools required: You are encouraged to complete the calculations for the regression analysis using built in Excel
functions (as covered in the lectures and in computer lab 3).
Scenario. You run an online business selling cosplay outfits based on popular TV series.
This season you have primarily relied on Facebook to advertise your different product
lines. For 30 different similarly priced products you have kept records of the amount you
spent (in bitcents) on marketing that item versus the quantity sold. In addition to your
usual Facebook advertising you also tried a different approach to marketing one item
(pictured right) where you hired an Instagram influencer. This cost 500 bitcents and you
sold 263 items. Your task is to develop a linear model that can predict sales based on
marketing spend. Subsequently, you want to evaluate if Instagram represents good value
for money compared to your usual advertising approach.
Auto‐graded questions
1. What is the explanatory variable (x‐axis) and what is the response variable (y‐axis)? [1 mark]
Explanatory – Marketing, Response – Sales
2. What is the best linear equation for predicting sales based on marketing spend? I.e. what are the intercept
and slope terms? [1 mark]
Expected sales = 4.3129 + 0.3215*marketing
3. What is the correlation between marketing spend and sales? [1 mark]
0.9263
4. How much of the variation in sales is explained by marketing spend? I.e. what is the coefficient of
determination, r 2
? [1 mark]
85.80%
5. What is the standard error, s e? [1 mark]
28.2674

Questions for the manually‐graded section of assignment
1. Create an appropriately formatted figure of the data, paying attention to labels and number formats. Include
the line of best fit. The figure should also include an informative caption. [6 marks]
Marks:
Correct variables on x and y axes – 1 mark
Good axis labels – 1 mark (including units i.e. bitcents 0.5 if no units)
Line of best fit shown – 1 mark
Sensible number formats on axes – 1 mark
Caption – 2 marks ST Comment: I am OK for the chart not to have a title on the chart if the caption is
sufficiently informative. In this example a title or a caption that just said something like “Facebook Spend vs Units
Sold” would qualify for 1 or the two marks available. To get two marks there should be some reference to evidence
of a relationship or correlation.
Figure 1: There is a strong correlation the amount spent marketing different products on Facebook and the number of
units sold. For example, spending 100 bitcents more results in an expected increase of 32 more units sold.
2. About how many points would you expect to see more than 2×s e from the line? How many are there
actually? [2 marks]
You’d expect about 5% of points to fall further than 2 std errors from the prediction line, i.e. in a dataset this
size 1‐2 points. In this case there are 2 points further than 2×s e from the line. A good way of illustrating this is
with at chart with +/‐ 2Se lines included.
y = 0.3215x + 4.3129
R² = 0.858
0
50
100
150
200
250
300
350
0 100 200 300 400 500 600 700 800 900
Units sold
Facebook marketing spend (bitcents)