{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Spreadsheet Updates and QA","OREG Calculator","Dropdowns and references","Version Notes"],"not_rendered":{"charts":0,"images":6,"macros":1,"external_links":0,"defined_names":9,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Spreadsheet Updates and QA","index":0,"n_rows":8,"n_cols":18,"rows_kept":8,"cut":false,"hidden":true,"merged":[[1,0,1,3],[1,7,1,13],[1,15,1,17]]}
["Spreadsheet updates and version control (for National Administrator ONLY)"]
["Version Control ",null,null,null,null,null,null,"QA Item",null,null,null,null,null,null,null,"After QA"]
["Version","Date","Change made ","Change made by","Requires QA","Overwritten",null,"Tab/Cell","What was checked","Pass / Fail","Notes","Action","QA/Check by","Date ",null,"Comments ","By ","Date "]
[1,"2022-08-18","OREG calculator was prepared ","Matt Hoogland ","Y",null,null,"OREG Calculator tab","Wording; Calculations","Wording - Fail\nCalculations - is in line with the Rules","Wording to be edited","Setup meeting with Loch and Ing","Navami Sunil ","2023-01-17"]
[2,"2023-01-24","Wording on the \"OREG Calculator\" tab of this spreadsheet was edited for more clarity and to ensure that it is in line with the latest MAC Rules.","Navami Sunil, Loch Tennekoon, Ingrid Errington in a meeting.","N"]
[2.1,"2023-02-06","Examples were included in a separate tab called \"Examples\"","Navami Sunil ","Y"]
[2.1,"2023-02-13","labelling changes -  \ni) to output field name to  'The total OREG consumption allocated to the rated premises is'\nii) made references to Examples tab ","Loch Tennekoon","N"]
[2.2999999999999998,"2023-04-17","Added dedicated/ shared example tabs","Duleesha R","Y"]

{"sheet":"OREG Calculator","index":1,"n_rows":130,"n_cols":14,"rows_kept":130,"cut":false,"hidden":false,"merged":[[117,1,117,9],[20,20,23,31],[87,20,90,31],[29,2,29,11],[91,10,91,11],[92,10,92,11],[80,2,80,9],[86,1,86,9],[49,2,49,9],[23,2,23,8],[76,2,76,9],[78,2,78,9],[52,5,52,9],[113,2,113,8],[114,2,114,8],[110,2,110,8],[129,10,129,11],[123,10,123,11],[121,2,121,8],[123,2,123,8],[128,2,128,8],[129,2,129,8],[122,2,122,8],[124,2,124,8],[121,10,121,11],[128,10,128,11],[110,9,110,11],[113,9,113,11],[114,9,114,11],[111,2,111,8],[112,2,112,9],[94,2,94,8],[53,5,53,9],[89,5,89,9],[109,1,109,9],[107,2,107,8],[108,2,108,11],[95,2,95,8],[95,10,95,11],[106,2,106,8],[104,2,104,3],[104,5,104,9],[96,10,96,11],[98,2,98,9],[100,1,100,9],[120,2,120,8],[14,3,14,7],[5,1,5,10],[45,2,45,9],[13,2,13,14],[7,2,9,11],[43,1,43,9],[19,1,19,9],[93,10,93,11],[96,2,96,8],[94,10,94,11],[15,3,15,7],[47,2,47,8],[91,2,91,8],[92,2,92,8],[93,2,93,8]]}
[]
[]
[]
[]
[]
[null,"T (02) 9995 5000\nE nabers@environment.nsw.gov.au\nnabers.gov.au\n4 Parramatta Square \nParramatta NSW 2150 "]
[]
[null,null,"NABERS\nOn-site Renewable Energy Generation Systems Calculator"]
[]
[]
[]
[null,null,"Version:",2.5,"Date:","2025-04-01"]
[]
[null,null,"This calculator helps you to quantify the distribution of renewable energy among shared users of an On-Site Renewable Energy Generation (OREG) system and instructs you how to account for the allocation in NABERS Perform.\n\nThe Rules for how this calculation is undertaken are detailed in the chapter 9, The Rules - Metering and Consumption (MAC) v2.5.  Please use this calculator in conjunction with current MAC Rules. \n\nThis calculation should be undertaken separately for each shared OREG system.\n"]
[null,null,"Blue","Calculator outputs for your information"]
[null,null,"Yellow","Requires data entry from you"]
[null,null,"Green","Figures and instructions for you to enter into NABERS Rate/NABERS Perform"]
[]
[]
[null,"1. Choose your connection type"]
[]
[]
[]
[null,null,"The OREG system connection type is:"]
[]
[null,null,"Definitions:"]
[null,null,"Dedicated Connection",null,null,"An OREG System connected to a single end use."]
[null,null,"Shared Connection",null,null,"An OREG system which is connected to multiple end uses."]
[]
[null,null,{"$f":"IF(J24='Dropdowns and references'!A3,\"The calculator is not required for a dedicated system. You may rate the building as per normal via utility metered consumption however you must ensure that grid export is distinguishable from grid consumption, as per Section 9.3.1 of the MAC Rules.\",IF(J24='Dropdowns and references'!A4, \"Please proceed below.\",\"\"))","v":""}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"2. Choose the metering configuration for the rated premises"]
[]
[]
[]
[null,null,"The consumption of the rated premises is:"]
[]
[null,null,"Tip:\nIf the consumption is determined via non-utility meter inclusion, then the rated premises is \"Physically metered\". \nIf the consumption of the rated premises is determined via exclusion of non-utility meters from a parent utility meter, then the rated premises is \"Virtually metered\". "]
[]
[null,null,"Definitions:"]
[null,null,"Physically metered",null,null,"A meter is physically installed to separately measure the energy consumption of an end use."]
[null,null,"Virtually metered ",null,null,"The energy consumption of an end use is calculated by subtracting data of one meter from another."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"3. Enter generation and consumption figures for the rating period"]
[]
[null,null,"Definitions:"]
[null,null,"Rated premises ",null,null,"The tenancy or building to be rated."]
[]
[null,null,"Main Utility Meter consumption (i.e. total grid consumption shared between all end users, billed via the Main Utility Meter):",null,null,null,null,null,null,0,"kWh"]
[null,null,"Total generation from the OREG system (including on-sold/ exports):",null,null,null,null,null,null,0,"kWh"]
[null,null,"Total export of generation from the OREG system to the grid:",null,null,null,null,null,null,0,"kWh"]
[null,null,"Total OREG consumption of premises (shared between all end users):",null,null,null,null,null,null,{"$f":"J93-J94","v":0},"kWh"]
[null,null,{"$f":"IF(MeterConfig='Dropdowns and references'!B3,\"Total metered consumption of the rated premises:\",\"\")","v":""},null,null,null,null,null,null,0,{"$f":"IF(J48='Dropdowns and references'!B3,\"kWh\",\"\")","v":""},null,{"$f":"IF(AND(MeterConfig='Dropdowns and references'!B4,Metered_premises_kWh>0),\"<Check data entry>\",\"\")","v":""}]
[null,null,{"$f":"IF(MeterConfig='Dropdowns and references'!B4,\"Total sum of non-utility meter exclusions:\",\"\")","v":""},null,null,null,null,null,null,0,{"$f":"IF(J48='Dropdowns and references'!B4,\"kWh\",\"\")","v":""},null,{"$f":"IF(AND(MeterConfig='Dropdowns and references'!B3,EN>0),\"<Check data entry>\",\"\")","v":""}]
[]
[null,null,{"$f":"IF(AND(MeterConfig='Dropdowns and references'!B3,Metered_premises_kWh>(GridImport+OREGconsumption)),\"Data error: Metered consumption of the rated premises should not be greater than sum of the main utility meter and OREG consumption\",IF(AND(MeterConfig='Dropdowns and references'!B4,EN>(GridImport+OREGconsumption)),\"Data error: Non-utility meter exclusions should not be greater than sum of the main utility meter and OREG consumption\",\"\"))","v":""}]
[]
[null,"4. Choose method of allocation for on-site renewable energy generation"]
[]
[]
[]
[]
[]
[null,null,"The OREG will be allocated by:"]
[null,null,"Are there any missing OREG data for the rating period:",null,null,null,null,null,null,""]
[null,null,"See section YYY of MAC rules, for treatment of OREG systems with missing data"]
[null,"5. Calculator outputs  "]
[null,null,{"$f":"HLOOKUP(Scenario, 'Dropdowns and references'!$A$35:$F$40,2,FALSE)","v":""},null,null,null,null,null,null,{"$f":"IF(ISNUMBER(HLOOKUP(Scenario,'Dropdowns and references'!$A$41:$F$45,2,FALSE)),HLOOKUP(Scenario,'Dropdowns and references'!$A$41:$F$45,2,FALSE),\"\")","v":""}]
[null,null,{"$f":"HLOOKUP(Scenario, 'Dropdowns and references'!$A$35:$F$40,3,FALSE)","v":""},null,null,null,null,null,null,{"$f":"IF(ISNUMBER(HLOOKUP(Scenario,'Dropdowns and references'!$A$41:$F$45,3,FALSE)),HLOOKUP(Scenario,'Dropdowns and references'!$A$41:$F$45,3,FALSE),\"\")","v":""}]
[null,null,"Total exlcusions are greater than total energy consumed by the building. This could be a scenario when solar data is missing. For guidance on handling missing solar data, please refer to Section 4.4.2 of the NABERS Metering and Consumption Rules."]
[null,null,{"$f":"HLOOKUP(Scenario, 'Dropdowns and references'!$A$35:$F$40,4,FALSE)","v":""},null,null,null,null,null,null,{"$f":"IF(ISNUMBER(HLOOKUP(Scenario,'Dropdowns and references'!$A$41:$F$45,4,FALSE)),HLOOKUP(Scenario,'Dropdowns and references'!$A$41:$F$45,4,FALSE),\"\")","v":""}]
[null,null,{"$f":"HLOOKUP(Scenario, 'Dropdowns and references'!$A$35:$F$40,5,FALSE)","v":""},null,null,null,null,null,null,{"$f":"IF(ISNUMBER(HLOOKUP(Scenario,'Dropdowns and references'!$A$41:$F$45,5,FALSE)),HLOOKUP(Scenario,'Dropdowns and references'!$A$41:$F$45,5,FALSE),\"\")","v":""}]
[]
[]
[null,"6. Data entry instructions: In Perform,"]
[null,null,"Under the grid electricity page, enter:"]
[]
[null,null,"The rated premises metered consumption as a utility meter inclusion:",null,null,null,null,null,null,{"$f":"J96 & \" kWh\"","v":"0 kWh"}]
[null,null,"The main utility acount consumption as: ",null,null,null,null,null,null,{"$f":"J92 & \" kWh\"","v":"0 kWh"}]
[null,null,"The solar consumption (generation minus exports) as a non-utility meter inclusion: ",null,null,null,null,null,null,{"$f":"J112 & \" kWh\"","v":" kWh"}]
[null,null,"The solar consumption (generation minus exports) as a utility meter inclusion:",null,null,null,null,null,null,{"$f":"IF(J48=\"Virtually metered\", IF(J97>=J92, J114&\" kWh\", \"n/a\"), \"\")","v":""}]
[null,null,"The total tenant consumption as non-utility meter exclusions:  ",null,null,null,null,null,null,{"$f":"EN & \" kWh\"","v":"0 kWh"}]
[null,null,"It's not compulsory to select the parent utility account for non-utility meter exclusions, in the NABERS rating input form",null,null,null,null,null,null,null,null,null,null," "]
[null,null,"Under the renewable electricity page, enter:"]
[]
[null,null,"The total onsite renewable electricity consumption of the rated premises (kWh):",null,null,null,null,null,null,{"$f":"J112","v":""}]
[null,null,"For the question 'has the onsite renewable consumption entered on this page been included within a parent meter’s consumption under Grid electricity':",null,null,null,null,null,null,{"$f":"IF(VALUE(J129)>0,\"Yes\",\"No\")","v":{"$error":"#VALUE!"}}]

{"sheet":"Dropdowns and references","index":2,"n_rows":51,"n_cols":6,"rows_kept":51,"cut":false,"hidden":true,"merged":[]}
["Connection type","Metering type","Allocation type","Solar Data"]
[null,null,null,null,"Meter Config format parameter:"]
["Dedicated","Physically metered","Claim","Yes",{"$f":"IF(MeterConfig='Dropdowns and references'!B3,1,IF(MeterConfig=B4,2,0))","v":0}]
["Shared","Virtually metered","Proportional consumption","No"]
[]
[]
["Calculator outputs and instructions"]
["There are four potential scenarios depending on the metering configuration and whether the allocation is via claim or proportion."]
[" The results output table is tailored for each scenario. Text and number outputs per scenario are as per below:"]
[null,"Text","Figure","Unit"]
["Physically metered by claim",null,null,"kWh"]
[null,"The maximum allowable allocation of OREG to the rated premises is:",{"$f":"MIN(Metered_premises_kWh,OREGconsumption)","v":0},"kWh"]
[null,"In NABERS Rate/NABERS Perform, enter the OREG allocation as a non-utility meter exclusion. The OREG allocation must be less than or equal to the maximum allowable allocation of:",{"$f":"C12","v":0},"kWh"]
["Virtually metered by claim","The total electricity consumption of the rated premises, including both grid and OREG consumption is:",{"$f":"GridImport+OREGconsumption-EN","v":0},"kWh"]
[null,"The maximum allowable allocation of OREG to the rated premises is:",{"$f":"MIN(C14,OREGconsumption)","v":0},"kWh"]
[null,"In NABERS Rate/NABERS Perform, enter the total shared OREG consumption as a non-utility meter inclusion equal to:",{"$f":"OREGconsumption","v":0},"kWh"]
[null,"In NABERS Rate/NABERS Perform, enter the OREG allocation as a non-utility meter exclusion. The OREG allocation must be less than or equal to the maximum allowable allocation of:",{"$f":"C15","v":0},"kWh"]
["Physically metered by proportion",null,null,"kWh"]
[null,"The total OREG consumption allocated to the rated premises is:",{"$f":"Metered_premises_kWh/SUM(GridImport,OREGconsumption)*OREGconsumption","v":{"$error":"#DIV/0!"}},"kWh"]
[null,"In NABERS Rate/NABERS Perform, enter the OREG allocation as a non-utility meter exclusion, equal to:",{"$f":"C19","v":{"$error":"#DIV/0!"}},"kWh"]
["Virtually metered by proportion","The total electricity consumption of the rated premises, including both grid and OREG consumption is:",{"$f":"GridImport+OREGconsumption-EN","v":0},"kWh"]
[null,"The total OREG consumption assigned to the rated premises is:",{"$f":"C21/SUM(GridImport,OREGconsumption)*OREGconsumption","v":{"$error":"#DIV/0!"}},"kWh"]
[null,"In NABERS Rate/NABERS Perform, enter the total shared OREG consumption as a non-utility meter inclusion equal to:",{"$f":"OREGconsumption","v":0},"kWh"]
[null,"In NABERS Rate/NABERS Perform, enter the OREG allocation as a non-utility meter exclusion, equal to:",{"$f":"C22","v":{"$error":"#DIV/0!"}},"kWh"]
[]
["Scenarios"]
[1,"Direct by claim"]
[2,"Virtual by claim"]
[3,"Direct by proportion"]
[4,"Virtual by proportion"]
[]
["The routine below determines which scenario we have, numbered 1-4 as above."]
["Scenario:",{"$f":"IF(OR(ISBLANK(MeterConfig),ISBLANK(AllocationMethod)),0,IF(AND(MeterConfig='Dropdowns and references'!B3,AllocationMethod='Dropdowns and references'!C3),1,IF(AND(MeterConfig='Dropdowns and references'!B4,AllocationMethod='Dropdowns and references'!C3),2,IF(AND(MeterConfig='Dropdowns and references'!B3,AllocationMethod='Dropdowns and references'!C4),3,4))))","v":0},{"$f":"IF(OR(Scenario=B35,Scenario=D35),\"A\",IF(OR(Scenario=C35,Scenario=E35),\"B\",\"C\"))","v":"C"}]
[]
["Text lookup table",1,2,3,4,0]
["Row 1",{"$f":"\"\"","v":""},{"$f":"B14","v":"The total electricity consumption of the rated premises, including both grid and OREG consumption is:"},{"$f":"\"\"","v":""},{"$f":"B21","v":"The total electricity consumption of the rated premises, including both grid and OREG consumption is:"},{"$f":"\"\"","v":""}]
["Row 2",{"$f":"B12","v":"The maximum allowable allocation of OREG to the rated premises is:"},{"$f":"B15","v":"The maximum allowable allocation of OREG to the rated premises is:"},{"$f":"B19","v":"The total OREG consumption allocated to the rated premises is:"},{"$f":"B22","v":"The total OREG consumption assigned to the rated premises is:"},{"$f":"\"\"","v":""}]
["Row 3",{"$f":"\"\"","v":""},"In NABERS Rate/NABERS Perform, enter the total shared OREG consumption as a non-utility meter inclusion equal to:",{"$f":"\"\"","v":""},"In NABERS Rate/NABERS Perform, enter the total shared OREG consumption as a non-utility meter inclusion equal to:",{"$f":"\"\"","v":""}]
["Row 4",{"$f":"B13","v":"In NABERS Rate/NABERS Perform, enter the OREG allocation as a non-utility meter exclusion. The OREG allocation must be less than or equal to the maximum allowable allocation of:"},"In NABERS Rate/NABERS Perform, enter the OREG allocation as a non-utility meter exclusion. The OREG allocation must be less than or equal to the maximum allowable allocation of:",{"$f":"B20","v":"In NABERS Rate/NABERS Perform, enter the OREG allocation as a non-utility meter exclusion, equal to:"},"In NABERS Rate/NABERS Perform, enter the OREG allocation as a non-utility meter exclusion, equal to:",{"$f":"\"\"","v":""}]
[]
["Figure lookup table",1,2,3,4,0]
["Row 1",{"$f":"\"\"","v":""},{"$f":"C14","v":0},{"$f":"\"\"","v":""},{"$f":"C21","v":0},{"$f":"\"\"","v":""}]
["Row 2",{"$f":"C12","v":0},{"$f":"C15","v":0},{"$f":"C19","v":{"$error":"#DIV/0!"}},{"$f":"C22","v":{"$error":"#DIV/0!"}},{"$f":"\"\"","v":""}]
["Row 3",{"$f":"\"\"","v":""},0,{"$f":"\"\"","v":""},0,{"$f":"\"\"","v":""}]
["Row 4",{"$f":"C13","v":0},0,{"$f":"C20","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},{"$f":"\"\"","v":""}]
[]
["Error cases"]
["Directly metered",{"$f":"IF(AND(MeterConfig='Dropdowns and references'!B3,Metered_premises_kWh>(GridImport+OREGconsumption)),1,0)","v":0}]
["Virturally metered",{"$f":"IF(AND(MeterConfig='Dropdowns and references'!B4,EN>(GridImport+OREGconsumption)),1,0)","v":0}]
["Dedicated OREG",{"$f":"IF('OREG Calculator'!J24='Dropdowns and references'!A3,1,0)","v":0}]
["Error",{"$f":"SUM(B48:B50)","v":0}]

{"sheet":"Version Notes","index":3,"n_rows":3,"n_cols":3,"rows_kept":3,"cut":false,"hidden":true,"merged":[]}
["v1 - draft version",null,"Only the OREG calculator (from Matt Hoogland)"]
["v2.3 - draft version","MAC 2.0 - section 9.3","Example tabs were added: Examples of metering arrangements, how to enter data into Perform and how to use the OREG calcualtor in each instance"]
["v2.4 - draft version",null,"Some formating in the example tabs"]