Question 1

The Singapore Department of Statistics (DOS) is a government agency that maintains all

kinds of statistics relating to the country.

Table 1 below shows the population in 3 public housing estates over a 12-year period. All

figures shown are in thousands.

Model for Housing Estate Population Analytics ('000 thousands) over 12 years

Estates Year

1

Town A

Town B

Town €

Total

Average

Lowest

260

100

140

261

107

135

210

254

125

120

259

122

138

RE

REB

****

ABE

BARR

HARR

ARRK

Tearly Population by Estates

Average Highest Lowest Medan Se

You are required to create appropriate models to analyse the population data to support

decision-making.

(a) 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.

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.

(1) 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./n(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)

(ii) 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%.

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)/nA 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)

(b)

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 each 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%./nstarts 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

each household has to co-pay 15% of the device costs each year over 3 years? Explain

how you derive your answer.

(6 marks)