Task - Spreadsheet
EnviroCo offers a service to customers to advise them about the government Feed In Tariff (FIT) payments.
The file FIT_Rates.csv contains a table of rates set by the government for different types of domestic renewable systems. The values given are the amount in pence per unit for electricity generated that the government pays the owner of a system. Fig. 2.1 shows a suggested form design to enter the parameters that are required to calculate the FIT payment rate.
The parameters that must be entered are the EPC, Size of system (kWh), Renewable type, House type, Certification granted? And the Date of installation, A FIT is calculated that is based on these values.
Model requirements
A worksheet should be designed to calculate the FIT a system would be eligible for when the parameters are set.
• EPC. Houses are rated in terms of energy efficiency. Houses in bands A to D are rated high and houses in bands E to G are rated low. A dropdown list should be used to select the EPC band (A to G).
• FIT rate. Any Solar PV system fitted before 03/03/2012, or one fitted on a property in the high EPC band at any date, qualifies for the higher FIT rate. Any Solar PV system fitted on a property in the low EPC band on or after 03/03/2012 qualifies for the lower FIT rate.
• Size of system (kWh). Domestic systems can be up to a maximum size of 9.99 kWh. A scroll bar or spinner should be used to select the system capacity. The control should allow values between 0 and 9.99 kWh to be input with increments of 0.01 kWh.
• Renewable type. Option buttons should be used to allow either a Solar PV system or a Wind system to be selected.
• House type. House types can either be new build or retrofit. Option buttons should be used to select the type of house.
• Micro renewable certification. A system is either certified or it is not. Systems that have not been certified will not be eligible for any FIT payments. A check box should be used to enter the certification status.
• Date of installation. Systems installed before 01/04/2010 are not eligible. A validation rule should be set up to generate a customized error message if an invalid date is entered.
• The actual value of the FIT must be calculated.
• If the system is not eligible for payments the FIT rate should be set to zero. The message 'Your system DOES NOT qualify' should be displayed. The background to the cell containing the FIT rate should automatically be set to red.
• If the system is eligible for payments the FIT rate should be displayed. The message 'Your system DOES qualify' should be displayed. The background to the cell containing the FIT rate should automatically be set to green.
(a) (I) Print screenshot evidence of the completed interface for the FIT Tariff worksheet.
(II) Produce annotated evidence to explain how the interface was set up to meet the requirements. You should explain any values that the form controls use.
(III) Produce annotated evidence to explain how the print button was implemented.
(IV) Print the worksheet showing the formulae. The row and column headings must be displayed.
(b) (I) Set the values in the FIT_Tariff worksheet to those in Figure 2.1 and enter the following values for the variables in the Break Even worksheet:
• Export Rate = £0.0450
• Units Generated = 2300
• Installation Cost = £10,000
Print the Break_Even worksheet on one side of A4 making sure that you show row and column headings on the printout.
(II) Produce annotated evidence to explain the formula you have used in the Break Even worksheet.
All named ranges should be identified and row and column headings should be displayed.
(III) Choose a formula in your solution which uses both relative and absolute addressing Explain why you decided to use both forms of addressing in this formula, and how it helped you to create the solution.
(c) Produce a help sheet(s) to show how to protect cells and worksheets in the system.