MATH0003 A2 Investigation (30%) Assessment 2: Investigation – Disease Modelling Academic Integrity in Mathematics: all your work should be your own and it should be underpinned by integrity, which means to act ethically, honestly and with fairness. Although no referencing is required in your investigation and correct answers between students will be similar, there is NO exception to this rule in mathematics. This is an individual assessment, and at no point should you discuss your answers or working out with other students. This assessment is submitted via Turnitin and it is very obvious to Curtin university tutors who mark assessments when students have colluded (worked together), especially when answers are incorrect. Students who are found to have colluded in this assessment risk receiving a Curtin Academic Integrity notice. The below document will guide you through your investigation. All answers and working to the questions within should be entered on this document. You will be required to copy and paste graphs and tables from your Excel workings into this document. Taking a screenshot or snip may be easier than copy and pasting. For information on how to take a screenshot or snip of your screen on a Mac, click here. For information on how to take a screenshot or snip of your screen on a Windows device, click, here. Once complete, you must submit your saved Word file to the Turnitin point in the Assessments folder. Make sure you read the submission instructions and upload this document to the correct point. You will be required to work with Excel to complete this assessment, as instructed in this document. You must save all of your work on Excel and submit that file to the Blackboard submission point, below the Turnitin submission point in the assessments folder. You will receive grades and feedback directly on the Word document that you submit in Turnitin, tutors will refer to the Excel file, but will not leave grades or feedback there. Note: your assessment will not be considered submitted until BOTH the Word and Excel files have been received in the system. Non-submission of the Excel file will result in the entire assessment being treated as not submitted. Some points to note: Show as much working as possible for every question, marks are attached to your working, not just your final answer. Refer to the supporting resources in the Assessments folder for assistance with the Excel components of this assessment. Some questions have multiple parts. Make sure that you thoroughly read and answer all parts of each question. Notation: * represents multiplication ^ represents an exponent (eg x^4 represents x4 or x to the power of 4) “d.p” stands for decimal places For the following questions you will be required to analyse disease trends using the data provided in the Excel file titled “Assessment 2 – disease_modelling_statistics”. Introduction: Mathematical models can project how communicable (infectious) diseases progress to show the likely outcome of an epidemic (like COVID-19) and help inform public health interventions. The modelling can help decide which intervention(s) to avoid, which to trial or it can help predict future growth patterns. Scientists also study the incidence of non-communicable (non-transferrable) diseases such as cancer and heart disease over time and over different populations to assist with the analysis of risk factors which increase the chances of developing these diseases. In this assessment you will investigate the trends in two diseases over time, using mathematical principles learned to date. Complete the following questions using the data in the Excel spreadsheet titled “Assessment 2 – disease_modelling_statistics”. It is recommended that you attempt the Excel based questions after the completion of Topic 7 – Excel Basics. Question 1 23 MARKS AVAILABLE Coronary heart disease (CHD) is Australia’s leading cause of death. It is the term given to heart problems caused by narrowed heart arteries that supply blood to the heart muscle. When the blood flow to the heart muscle is completely blocked, the heart muscle cells die, which is termed a heart attack. Australian death rates have fallen substantially over recent decades. Advances in treatment, improving the level of care, and controlling the risk factors of death in CHD patients (such as smoking, hypertension, and overweight) are the most important reasons for the reduction in the mortality rate of this disease. For this question you will use the data in the “Australian CHD Data by Sex” tab of the supplied Excel Spreadsheet. It outlines the number of deaths (by sex*) in Australia due to CHD between 2000 and 2019 as recorded by the World Health Organisation (WHO). * WHO defines “sex” as the biological characteristics that define humans as female or male. Use the Pivot Table function in Excel to produce a cross tabulation which organises the data with the year variable down the rows and the sex variable across the columns. The cells in the table should contain the number of deaths of a given sex each year. Include column and row grand totals in your final table. Ensure your table contains an appropriate title and column/row headings. Take a snip or copy and paste this table below. [1.5 marks] For the following question you will first need to copy and paste the values from the pivot table created in 1a) into a new tab of your Excel spreadsheet. Be sure to paste using the ‘values only” option, not the formatting as well. Name this tab “CHD by sex & year” In this new tab, add an additional column in between the “year” and “sex” columns and title it “years since 2000 (t)”. Fill this column with appropriate values e.g. 2000 t = 0, 2001 t =1 etc. Using Excel and the table, create a line graph which represents the relationship between the variables time since 2000 and the number of deaths from CHD between 2000 and 2019 for both sexes. Your final graph should have two lines on one set of axes. Take a snip or copy and paste your graph below. Hints: Determine the dependent and independent variables and which axis they should be situated on. Ensure your graph contains all relevant information and features. [2.5 marks] What type of function does the number of CHD deaths data appear to be? [ 0.5 mark] Explain what features of your graph support your answer in part b)ii. [1 mark] Describe the trend you can see in CHD death rates over time. Compare the similarities and differences between the sexes. [1.5 marks] Use Excel to add a linear trendline & trendline equations to both the male and female lines. Take a snip of your graph with trendlines and paste below. In addition to pasting your graph into this document, type the trendline equations here: Male: Female: [1.5 mark] Given y = mx + c is the standard slope-intercept format of a linear equation: what is the m value in your trendline equation identified above: for the male trend line? [0.5 mark] for the female trendline? [0.5 mark] Explain what the m values represent in the context of this scenario? In your explanation interpret the differences between the male and female values. [1 mark] what is the c value in your trendline equation identified above: for the male trend line? [0.5 mark] for the female trendline? [0.5 mark] Explain what the c values represent in the context of this scenario? In your explanation interpret the differences between the male and female values. [1 mark] In Excel, create two pie charts which display the proportion of male vs female Australian CHD deaths in the year 2000 (pie chart 1) and in the year 2019 (pie chart 2). You should have one pie chart for each year. Take a snip of the two pie charts and paste below. [2 marks] Discuss the differences between the two pie charts in the context of deaths due to CHD by sex in 2000 and 2019. [1 mark] Was the difference observed in your pie charts in part f expected based on the differences in the trendlines for both male & females – yes or no? Justify your response by linking the differences in the pie charts to the male and female trendlines. [2 marks] Write one linear equation which can be used to predict the total number of deaths from CHD in Australia in any given year since 2000. Hint: total number of deaths = # male deaths + # female deaths [1.5 marks] Use your trendline from part g)i to predict the total number of people (male and female sex) who will die from CHD in the year 2030. [1.5 marks] Use your trendline from part g)i to predict the year the number of annual deaths due to CHD will have been reduced to 10,000 people. [2.5 marks] Question 2. 19 MARKS AVAILABLE When comparing mortality trends over time between nations, it is important to also take into consideration the population size at any given time. The mortality rate is often expressed as the number of deaths per 100,000 people. For the following questions you will refer to the data in the “CHD mortality nation comparison” tab in the Excel spreadsheet. Using skills covered in Topic 7 – Excel Basics, format the data in the table to achieve the following: Alter the column width appropriately Bold the column headings Add the filters to the column headings, so that data can be filtered if required Freeze the top row so you can scroll down and always have the column headings in view Take a snip or copy and paste the top section of your table below. [1.5 marks] Insert a column to the right of the number of deaths column and title “deaths/100,000”. Using the values in the “number of deaths” column and the “nation population” column, determine the equivalent death rate per 100,000 for each year for each nation. To do this, use an Excel formula to calculate the appropriate “deaths/100,000” value for one cell in this new column (to 2 decimal places). Apply this formula to all cells in this column. Once done, click on any value in this new column, take a snip of the first 10 rows with the highlighted cell and the formula bar with the underlying calculation. Paste the image below. [ 2 marks] Use the Pivot Table function in Excel to organise the raw data in a table which displays the number of deaths per year for each nation. Place the year variable in rows and the nation variable in columns. Ensure your table contains an appropriate title and column/row headings. You do not need to include grand totals. Take a snip or copy and paste this table below. [ 1.5 marks] Using the pivot table created in part c)i, create line graphs which represents the relationship between the year and the number of deaths from CHD between 2000 and 2019. Your final product should be one graph with 4 lines - one representing each nation. Take a snip or copy and paste this graph below. [2.5 marks] In which nations, has the number of deaths from CHD each year increased over time? [1 mark] In which nations, has the number of deaths from CHD each year decreased over time? [1 mark] Use the Pivot Table function in Excel to organise the raw data in a table which displays the deaths per 100,000 people, per year for each nation. Place the year variable in rows and the nation variable in columns. Ensure your table contains an appropriate title and column/row headings. You do not need to include grand totals. Take a snip or copy and paste this table below. [ 1.5 marks] Using the pivot table created in part d)i, create line graphs which represents the relationship between the year and the number of deaths/100,000 from CHD between 2000 and 2019. Your final product should be one graph with 4 lines - one representing each nation. Take a snip or copy and paste this graph below. [ 2.5 marks] Which nation has had the most stable CHD deaths/100,000 rate between 2000 & 2019? What feature of the graph explains your response. [1 mark] Use your pivot tables and/or graphs in part c) & d) to complete the empty cells in the table below. You only need to enter the names of the nations. You do not need to include values. For example, in the red cell below you should type the name of the nation which had the greatest number of deaths due to CHD in 2020. [2 marks] Country Comparison of Mortality due to CHD in 2000 & 2019 Rank (1=highest; 4=lowest) Total deaths in 2000 Total deaths in 2019 Deaths/100,000 in 2000 Deaths/100,000 in 2019 1 2 3 4 Compare and contrast both the number of deaths due to CHD and the deaths/100,000 rates between Brazil and Australia. In your response discuss the changes over time, the trends (growth/decline) and compare the values. [2.5 marks] Question 3. 18 MARKS AVAILABLE The global emergence of the coronavirus (COVID-19) marks a significant worldwide outbreak of an infectious disease caused by the severe acute respiratory syndrome SARS-CoV-2. Cases were first detected in China in December 2019, with the virus spreading rapidly to other countries across the world. This rapid transmission prompted the World Health Organization (WHO) to declare it a pandemic (worldwide spread of a new disease) in March 2020. For the following questions you will refer to the data in the “US COVID Cases 20_21” tab in the Excel spreadsheet. a) Using Excel, create a line graph which represents the relationship between the variables time (the number of weeks) and the number of COVID cases in the US between 19/1/2020 (case #1) and 24/1/2021. For the time variable, t, use values which represent the number of weeks since case #1. i.e. on 19/1/2020 t=0; on 26/1/2020 t = 1; on 2/2/2020 t = 2 etc. For the number of COVID cases variable, format the axis to display the units in “millions”. Take a snip or copy and paste your graph below. Hints: Add an additional column to your spreadsheet called “weeks since case #1” and fill with appropriate values e.g. 20/1/2020 t=0; on 26/1/2020 t = 1 etc. Determine the dependent and independent variables and which axis they should be situated on. Ensure your graph contains all relevant information and features. [2.5 marks] b) i. What type of function does the number of COVID-19 cases in the US over this period of time appear to be? [0.5 mark] Explain what features of your graph support your answer in part 3b)i. [1 marks] c) Add a fourth column to your table in Excel which indicates the “growth factor” from the week before. To do this, divide each week’s number of cases by the number of cases of the week before. This first three values have been completed for you. You must perform the calculations in Excel (not manually, using a calculator). Extend this formula for the entire table to calculate the growth factor to 5 d.p. Click on one of the cells in your growth factor column, take a screen shot showing your table (minimum of the first 10 lines) with the highlighted cell and the formula bar with the underlying calculation. Take a snip or copy and paste that screen shot below. Weeks since case #1 Number of Cases Growth Factor 0 1 1 4 4.00000 2 9 2.25 3 20 2.22222 4 24 1.2 [2 marks] Use Excel to calculate the average of the growth factor values without the outlier values included in the calculation. The outlier values you are to exclude in the calculation of the average, are the two largest growth factor values. What is the average factor (5 d.p.)? Click on the average value in Excel, take a snip of the average cell and the formula bar to show how you used an Excel formula to find this average. Paste the image below. [1 mark] The general form of an exponential equation is y=ka^t. Consider the table above of the number of COVID-19 cases in the US and the average growth factor in relation to the general form of an exponential equation as you answer these next questions: What does the “y” in the general form “y=ka^t” represent in the context of the relationship between the number of COVID-19 cases and weeks since case #1? [1 mark] What does the “k” in the general form “y=ka^t” represent in the context of the relationship between the number of COVID-19 cases and weeks since case #1? Give its value and explain. [1.5 marks] What does the “a” in the general form “y=ka^t” represent in the context of the relationship between the number of COVID-19 cases and weeks since case #1? Give its value and explain. [1.5 marks] What does the “t” in the general form “y=ka^t” represent in the context of the relationship between the number of COVID-19 cases and weeks since case #1? Explain. [1 marks] Use your answers from parts d) i-iv to construct an exponential equation describing the relationship the number of COVID-19 cases and weeks since case #1. [1 mark] e) Use your formula from part Q3d)v to predict the number of COVID-19 cases in the US on 31/1/2021. Show all working. [2 marks] The exponential nature of communicable diseases like COVID-19, not only applies to the nature in which the disease can spread, but also in how the number of cases can reduce. Every case of COVID-19 that is prevented cuts off transmission chains, which prevents many more cases down the line. That means the same precautions that reduce transmission enough to cause a big drop in case numbers when cases are high, translate into a smaller decline when cases are low. Those changes add up over time. For example, reducing 1,000 cases by half each day would mean a reduction of 500 cases on Day 1, a reduction of 250 cases on Day 2, 125 cases on Day 3 but only 31 cases on Day 5. Below is a graph which demonstrates this. It displays the number of COVID-19 cases in the US between December 2020 and February 2022 and the following questions will refer to it. Dec 20 Jun 21 Jul 21 Aug21 Sept21 Oct 21 Nov 21 Dec 21 Feb 21 Feb 22 Jan 22 May 21 Jan 21 Mar 21 Apr 21 TOTAL COVID-19 CASES - USA f) i. Approximately what month/year recorded the greatest number of COVID-19 cases? [0.5 mark] ii. Approximately what month/year recorded the least number of COVID-19 cases? [0.5 mark] iii. Identify one period in which exponential growth of COVID-19 cases occurred & identify one period in which exponential decay of COVID-19 cases occurred. Don’t just give a month, provide the range of time in which growth/decay has been observed. Make it clear in your response which period of time represents growth and which represents decay. [1 mark] iv. What period experienced the greatest rate of growth? Don’t just give a month, provide the range of time in which the greatest rate of growth has been observed. [0.5 mark] v. What period were cases the most stable? Don’t just give a month, provide the range of time in which cases were stable. [0.5 mark] End of Assignment World Health Organization (2023) WHO COVID-19 Dashboard – United States of America. https://covid19.who.int/region/amro/country/us World Health Organization (2023) WHO Mortality Database - Ischaemic Heart Disease https://platform.who.int/mortality Total /60 Total /30 Late Penalty __ days late = __ % deduction = ____ mark deduction Final score /30