1 Modelling steps and data checks

Read the background document that describes the scenarios that need to be modelled

and documented for this project.

Construct a spreadsheet model that produces the following calculations and charts.

You should ensure that your spreadsheet contains appropriate self-checks and that you

have performed reasonableness checks at each stage of your calculations.

(i) Carry out checks on the data provided to confirm that the data is

fit for use. This should include:

creating a graph showing the average solar electricity generation and

average electricity usage for each hour in a day, i.e. the average over all

amounts for 1 am, and each subsequent hour within a 24-hour period.

investigating any significant outliers.

making any corrections that are needed.

[6]

(ii) Calculate, for each hour in 2020, the amount of electricity that needed to be

bought from the electricity provider (measured in kWh and subject to a

minimum of zero). [1]

(iii) Calculate, for each hour in 2020, the excess solar electricity generated over

electricity used (measured in kWh and subject to a minimum of zero). [1]

(iv) Model the cumulative battery charge level (measured in kWh) for each hour

over 2020, assuming a battery had already been installed.

The battery charge level should:

begin at zero at 1 January 2020 00:00.

allow for the increase or decrease in charge level depending on the hourly

results of parts (ii) and (iii).

be subject to the cap on the maximum battery charge level.

[4]

(v) Calculate the amount of electricity for each hour in 2020 that would have been

bought from the electricity provider (measured in kWh and subject to a

minimum of zero), assuming a battery had already been installed. [2]

(vi) Calculate the saving over 2020 (in dollars ($), using 1 January 2022 electricity

prices and ignoring discounting) from installing a battery compared to using

the existing solar panels alone.

vii) Tabulate the data appropriately and then produce a chart to illustrate, on a

monthly basis for the calendar year and measured in kWh, the:

monthly solar generation.

monthly electricity usage.

monthly electricity purchased from the electricity provider (no battery).

monthly electricity purchased from the electricity provider (with battery).

[Hint: You may wish to use the Excel functions MONTH(date) and SUMIF.]

[4]

(viii) (a) Project forward for 20 years from 1 January 2022 the annual savings

from installing the battery for the two scenarios below.

(b) For the two scenarios, calculate the Net Present Value (NPV) of the

future annual savings.

[5]

The scenarios are as follows:

1. Electricity prices increase as expected by the government, 4% p.a.

2. Electricity price increases start at 4% p.a. and rise each year by an

additional 0.25% p.a., as estimated by Naomi.

(ix) Calculate the Internal Rate of Return (IRR) for the two scenarios in part (viii)

by determining the discount rate that equates the net present value of the

future annual savings to the initial cost of the battery for each scenario. [3]

2 Modelling technique and practice

(i) Auto checks on the modelling completed in (1). [2]

(ii) Demonstration of good modelling technique and practice.

3 Audit trail

Make an audit trail for your spreadsheet model that includes the following aspects:

purpose of the model

data, including checks, the results of the checks and any action taken as a

consequence

assumptions used

methodology, i.e. description of how each calculation stage in the model has been

produced

explanation of any further checks performed.

You should ensure that your audit trail is suitable for both a senior actuary, who has

been asked to approve your work, and a fellow student, who has been asked to peer

review and correct your model, or may be asked to continue to work on it or to use it

again for a similar purpose in the future.

Marks available for audit trail:

Audit approach

(i) Communication skills (the audit trail provides enough detail to be read as a

stand-alone document). [4]

(ii) Fellow student can review and check methods used in the model. [7]

(iii) Senior actuary can scrutinise and understand what has been done. [7]

(iv) Written in clear English. [4]

(v) Written in a logical order. [3]

Audit content

(vi) All steps clearly explained. [8]

(vii) Reasonableness checks included. [5]

(viii) Clear signposting included throughout. [4]

(ix) Statement of assumptions made. [5]

(x) All model steps accurately covered.