Question 1 The Singapore Department of Statistics (DOS) is a government agency that maintains all kinds...

Transcribed Image Text:

Question 1 The Singapore Department of Statistics (DOS) is a government agency that maintains all kinds of statistics relating to the country. Town A Town B Town C Total Average Hashon Lowest Table 1 below shows the population in 3 public housing estates over a 12-year period. All figures shown are in thousands. Model fer Housing Estate Population Analyves ro00 thousands) over 12 years EstatesYoer 1 4 5 160 264 100 115 100 120 2 201 207 IDS (a) 3 116 110 120 219 110 6 207 130 115 (ii) 7 143 110 8 140 155 104 9 269 170 101 10 256 100 100 11 254 193 12 263 210 X Table 1. Population Data over 12 years for 3 Housing Estates You are required to create appropriatc models to analyse the population data to support decision-making. Yeady Popdation by Zatatas Tonal Average Highest lowest Medion Develop a model to analyse population data by completing Table 1. You need to fill in the shaded cells shown in yellow. Note that these cells can only contain formulae and functions. Hardcoding of numerical answers is not allowed. Average and Median refer to the average and median population by month and by housing estate, respectively. Highest and Lowest refer to the highest and lowest population. Stdev is the standard deviation of Yearly Population by Estate. Total refers to the yearly total of population in each estate. The column headers 1, 2, 12 along the top row refer to each of the 12 years. Year 12 is the most recent year. Extend Table 1 to add a row of cells that will automatically show the name of the estate with the lowest population in each year. The names should dynamically change should the historical population data change. This row should be added at the bottom of the table just below "Lowest" row, (16 marks) Analyse the data in your table and answer the following questions quantitatively. (i) Which housing estate has the lowest yearly population in the last 12 years? State the lowest yearly population. Explain how you derive your answer without having to manually inspect all the individual population data. (5 marks) The Housing and Development Board (HDB) has a plan to pilot the next generation Sustainable Green Town Project (SGTP). This town has net zero carbon emission. Geographically, all the 3 estates in Table 1 are suitable. However, the HDB can only re-develop one of them. If selected, the HDB will need to pay compensation to acquire the existing flats. The average population per household ratios for Towns A, B and C are 3.0, 2.3 and 2.7, respectively. Each household lives in its own flat. The average compensation per flat for the 3 estates differs by no more than 30%, 10641 (b) (c) Using appropriate statistics, graphs and/or data analysis, recommend the most appropriate estate to re-develop. Justify your choice with at least two (2) quantitative reasons. (8 marks) (iii) A data analyst at DOS suspects that there is an error in the year 12 population value for Town B. The average population for Town B over 12 years should be 150,000. What should be the correct population value for Town B in year 12? (5 marks) The Ministry of Family and Social Affairs (MFSA) is concerned about the problem of senior citizens dying or getting seriously injured at home without anyone noticing. It would like to install Personal Alert Buttons (PAB) devices in public flats and has selected Town A for Phase 1 of this project. About 65% to 80% of the households in Town A are estimated to have at least one senior citizen. The average population to household (or flat) ratio is given in part (a). The PAB is a large button-like device developed by the Government Technology Agency that enables senior citizens in distress to call for emergency assistance simply by pressing a single large button. An alert message will be sent to a call centre, and emergency personnel will be quickly despatched to investigate. Each eligible flat will only be installed with one such device. Phase 1 of the PAB project will last 6 years. The PAB device will be produced and fully installed by an external vendor within the first year. Each device costs $280, inclusive of installation. MFSA will pay the vendor for all installed devices in equal yearly instalments over 5 years with no added interest penalty. Flat owners only have to co- pay 10% of the device cost cach year over 3 years for a total of just 30%. The remaining 70% of the costs will be funded by MFSA. In a large installation, approximately 12% of all installed devices can be expected to fail each year. The vendor will bear the cost of repairs in the first year. MFSA will be responsible for repair costs from the second year onwards till the end of the project. Average repair cost starts at $30 per device in year 2 and will increase at the annual general inflation rate of 9%. Using the most recent population data for Town A, create an appropriate NPV-based model to analyse how MFSA can fund the above project. State any two (2) assumption(s) used by your model. (10 marks) Using your model, examine whether a budget of $7 million is sufficient for MFSA to fund the project if the target is to install the PAB device for 65% of all households in Town A. Is it sufficient? Why? What budget would be required in order to fund 80% of all households in the same town if cach household has to co-pay 15% of the device costs cach year over 3 years? Explain how you derive your answer. (6 marks) Question 1 The Singapore Department of Statistics (DOS) is a government agency that maintains all kinds of statistics relating to the country. Town A Town B Town C Total Average Hashon Lowest Table 1 below shows the population in 3 public housing estates over a 12-year period. All figures shown are in thousands. Model fer Housing Estate Population Analyves ro00 thousands) over 12 years EstatesYoer 1 4 5 160 264 100 115 100 120 2 201 207 IDS (a) 3 116 110 120 219 110 6 207 130 115 (ii) 7 143 110 8 140 155 104 9 269 170 101 10 256 100 100 11 254 193 12 263 210 X Table 1. Population Data over 12 years for 3 Housing Estates You are required to create appropriatc models to analyse the population data to support decision-making. Yeady Popdation by Zatatas Tonal Average Highest lowest Medion Develop a model to analyse population data by completing Table 1. You need to fill in the shaded cells shown in yellow. Note that these cells can only contain formulae and functions. Hardcoding of numerical answers is not allowed. Average and Median refer to the average and median population by month and by housing estate, respectively. Highest and Lowest refer to the highest and lowest population. Stdev is the standard deviation of Yearly Population by Estate. Total refers to the yearly total of population in each estate. The column headers 1, 2, 12 along the top row refer to each of the 12 years. Year 12 is the most recent year. Extend Table 1 to add a row of cells that will automatically show the name of the estate with the lowest population in each year. The names should dynamically change should the historical population data change. This row should be added at the bottom of the table just below "Lowest" row, (16 marks) Analyse the data in your table and answer the following questions quantitatively. (i) Which housing estate has the lowest yearly population in the last 12 years? State the lowest yearly population. Explain how you derive your answer without having to manually inspect all the individual population data. (5 marks) The Housing and Development Board (HDB) has a plan to pilot the next generation Sustainable Green Town Project (SGTP). This town has net zero carbon emission. Geographically, all the 3 estates in Table 1 are suitable. However, the HDB can only re-develop one of them. If selected, the HDB will need to pay compensation to acquire the existing flats. The average population per household ratios for Towns A, B and C are 3.0, 2.3 and 2.7, respectively. Each household lives in its own flat. The average compensation per flat for the 3 estates differs by no more than 30%, 10641 (b) (c) Using appropriate statistics, graphs and/or data analysis, recommend the most appropriate estate to re-develop. Justify your choice with at least two (2) quantitative reasons. (8 marks) (iii) A data analyst at DOS suspects that there is an error in the year 12 population value for Town B. The average population for Town B over 12 years should be 150,000. What should be the correct population value for Town B in year 12? (5 marks) The Ministry of Family and Social Affairs (MFSA) is concerned about the problem of senior citizens dying or getting seriously injured at home without anyone noticing. It would like to install Personal Alert Buttons (PAB) devices in public flats and has selected Town A for Phase 1 of this project. About 65% to 80% of the households in Town A are estimated to have at least one senior citizen. The average population to household (or flat) ratio is given in part (a). The PAB is a large button-like device developed by the Government Technology Agency that enables senior citizens in distress to call for emergency assistance simply by pressing a single large button. An alert message will be sent to a call centre, and emergency personnel will be quickly despatched to investigate. Each eligible flat will only be installed with one such device. Phase 1 of the PAB project will last 6 years. The PAB device will be produced and fully installed by an external vendor within the first year. Each device costs $280, inclusive of installation. MFSA will pay the vendor for all installed devices in equal yearly instalments over 5 years with no added interest penalty. Flat owners only have to co- pay 10% of the device cost cach year over 3 years for a total of just 30%. The remaining 70% of the costs will be funded by MFSA. In a large installation, approximately 12% of all installed devices can be expected to fail each year. The vendor will bear the cost of repairs in the first year. MFSA will be responsible for repair costs from the second year onwards till the end of the project. Average repair cost starts at $30 per device in year 2 and will increase at the annual general inflation rate of 9%. Using the most recent population data for Town A, create an appropriate NPV-based model to analyse how MFSA can fund the above project. State any two (2) assumption(s) used by your model. (10 marks) Using your model, examine whether a budget of $7 million is sufficient for MFSA to fund the project if the target is to install the PAB device for 65% of all households in Town A. Is it sufficient? Why? What budget would be required in order to fund 80% of all households in the same town if cach household has to co-pay 15% of the device costs cach year over 3 years? Explain how you derive your answer. (6 marks)

Answer rating: 100% (QA)

Based on the image youve provided it appears that youre working on a complex statistical analysis and decisionmaking problem involving population data ... View the full answer