# Calculate Retail Margin

Document Sample

```					THE INDEPENDENT PRICING AND REGULATORY TRIBUNAL OF NSW

ELECTRICITY - REGULATED RETAIL TARIFFS
Purpose of the model
The purpose of the model is to
- to calculate the cost of the Retail (ie non-network) components of regulated retail tariffs, and
- to forecast indicative customer bills

How are the R values calculated?
The Retail (R) costs are calculated by adding together the following components:
- The electricity purchase cost allowance, which may be a long run margin cost (LRMC) or market based price. The cost of the Carbon Pollution Reduction Scheme (CPRS) is included.
- Additional Green electricity purchase cost allowances (including the cost of RET and GGAS/ESS schemes).
- An allowance for volatility in the costs of market based electricity purchases, based on the costs of sufficient working capital.
- Energy losses
- National Electricity Market (NEM) fees and ancillary charges
- Retail operating costs (in fixed \$ per customer and/or variable \$ per kWh)
- Customer acquisition and retention costs (in fixed \$ per customer and/or variable \$ per kWh)
- Retail margin, which is calculated on the total Network + Retail value (N+R)

The fixed R value is the per customer portion of retail operating cost (ROC) and customer acquisition and retention cost (CARC) (\$/customer per year).

The variable R value incorporates all the remaining costs (\$/MWh):

How is the electricity purchase cost allowance calculated?
The electricity purchase cost allowance is the main component of retail costs.
For each retailer,
-
-

The mechanics of determining electricity cost allowance in the model
On the 'Scenario' worksheet the model records:
-
-
-
-

The user then selects which wholesale electricity cost allowance to use to calculate the variable R value:
-

11/15/20107:17 AM                                                            4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                                                 Cover
-
-

The model calculates the total electricity cost allowance, including the additional Green costs and the working capital allowance for market based purchases.

General assumptions
-   Forecasts of costs and revenue are all in real 2009/2010 prices. Typical bills are shown in nominal and real prices.
The R values are shown in both 2009/2010 and 2010/2011 prices on the Scenario worksheet.

-   The model assumes a three year maximum determination period. If the determination period is less than three years, the NPV calculations need to be changed.

-   Total cost and revenue forecasts are calculated using constant 2008/2009 volumes and customer numbers. Percentage changes in total revenue are therefore equivalent
to changes in average tariffs (N, R and N+R).

Colour code
The model is colour coded, as follows:

Inputs (empty cell = 0)
Key outputs
Error check (check =0)
Error warnings, messages and unusual calculation assumptions
A double red line indicates a change in formula across row (or down a column)
Links from other files (green) (But use links as rarely as possible and very carefully)

VERY IMPORTANT: ONLY THE BLUE CELLS MAY BE CHANGED…
and any blue cell may be changed, even if it contains a formula because the function of that formula will be to calculate an input value.

Instructions for use

Inputs
This worksheet captures a range input data on customer numbers, sales volumes, costs, current R values etc.
In principle, once data have been entered into this worksheet they should not need to be changed to run scenarios.
The decisions that need to be made to run scenarios entered on the Scenario worksheet.

The following inputs are required:
Table 1 - Customers and sales volumes on regulated retail tariffs
-
-

11/15/20107:17 AM                                                            4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                                     Cover
Note: sales volumes are requested by tariff type (ie single rate/block; TOU peak; TOU shoulder; TOU off-peak; Controlled load A and Controlled load B) so that the model can calculate
revenue from the variable R values for 2009/2010

Table 2. Network (DUOS and TUOS) charges by customer category
The model uses a forecast of network charges to
-
-

Enter the following data:
-
-
-

The model records the DUOS and TUOS components separately, and calculates a forecast increase in the NUOS charge which is a weighted average of the two components.

Table 3 - Historical R values and expected cost recovery
-
-
(This information may be obtained from the annual pricing compliance information provided to IPART.)

Table 4 - Regulated Tariffs for calculation of bills (excluding GST)
Enter the main regulated tariffs for residential and business customers for 2009/2010
-
-
The other tariffs recorded on this worksheet are for information only.

Scenario
This worksheet asks the user to
-
-

The outputs of the model are displayed, so that the user can immediately see the impact of his/her decisions on the R values, average price changes, customer bills et.

INPUTS AND DECISIONS
Table 1 - Scenario Inputs for calculation of R values
Enter the following information:
General information
-
-
Energy purchase cost allowance
-

11/15/20107:17 AM                                                           4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                                                         Cover
-
-
-

Make to following decision for the electricity costs allowance:
-

Other components of cost
-
-
-
-
-
-

KEY OUTPUTS
Table 2 - R values
This table shows the R values in both 2009/2010\$ and in 2010/2011\$.

Table 3. Total R and N revenue - 2009/2010 \$'000
The first part of this table shows R revenue, in real 2009/2010\$, as well as the associated real and nominal % increases.
The second part of this table shows N+R revenue, in real 2009/2010\$, as well as the associated real and nominal % increases.

Note that the revenue is calculated for constant 2008/2009 sales volumes and customer numbers. This means that the percentage increases shown in the table (nominal and real)
are also percentage increases in the R and N+R values.

Table 4. Total costs - 2009/2010 \$'000
This table shows the components of total costs, in dollar and percentage terms, as follows:
- Energy costs
- Retail costs
- Retail margin
Total R costs
Network charges
Total costs

INDICATIVE BILLS
Table 5 - Indicative Bills (\$ pa excluding GST)
This table calculates indicative bills for three sets of residential and business customers, for three different levels of consumption.
-
-
-

Bills are shown in both nominal prices and real 2009/2010\$

11/15/20107:17 AM                                                              4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                                             Cover
Charts
There is currently only one chart on this worksheet, namely the components of average bills in the final year.
The user may add more charts on this worksheet.

Free
This sheet is not linked to the model calculations, and may be used to store raw data, calculate inputs, do additional analysis etc.

Calculation sheets
The remainder of the model consists of calculation worksheets.
Please DO NOT change any for the formulae in this section.
If any changes are made, they must be clearly marked and recorded on the Journal sheet

R values
This worksheet calculates:
-
-
-

Total costs
Total costs for the following components of the N+R value are calculated:
- Network charges
- Energy costs
- Retail costs
- Retail margin
These calculations provide the information for Table 4 on the Scenario sheet.

This model was developed by Bee Thompson, Independent Pricing and Regulatory Tribunal of NSW (IPART).
Date: October 2009

Contact details:      Bee Thompson             Tel 9290 8496, email Bee_Thompson@ipart.nsw.gov.au

11/15/20107:17 AM                                                             4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                 Cover
THE INDEPENDENT PRICING AND REGULATORY TRIBUNAL OF NSW

ELECTRICITY - REGULATED RETAIL TARIFFS
Purpose of the model
The purpose of the model is to
to calculate the cost of the Retail (ie non-network) components of regulated retail tariffs, and
to forecast indicative customer bills

How are the R values calculated?
The Retail (R) costs are calculated by adding together the following components:
The electricity purchase cost allowance, which may be a long run margin cost (LRMC) or market based price. The cost of the Carbon Pollution Reduction Scheme (CPRS) is included.
Additional Green electricity purchase cost allowances (including the cost of RET and GGAS/ESS schemes).
An allowance for volatility in the costs of market based electricity purchases, based on the costs of sufficient working capital.
Energy losses
National Electricity Market (NEM) fees and ancillary charges
Retail operating costs (in fixed \$ per customer and/or variable \$ per kWh)
Customer acquisition and retention costs (in fixed \$ per customer and/or variable \$ per kWh)
Retail margin, which is calculated on the total Network + Retail value (N+R)

The fixed R value is the per customer portion of retail operating cost (ROC) and customer acquisition and retention cost (CARC) (\$/customer per year).

The variable R value incorporates all the remaining costs (\$/MWh):

How is the electricity purchase cost allowance calculated?
The electricity purchase cost allowance is the main component of retail costs.
For each retailer,
the Tribunal decides what the wholesale cost allowance will be (including CPRS), and these are entered as inputs.
the Tribunal decides what the additional 'green' cost allowances are, and these are similarly entered as inputs.

The mechanics of determining electricity cost allowance in the model
On the 'Scenario' worksheet the model records:
The add-on Green costs (RET, GGAS/ESS)
The long run marginal costs (LRMC) from non-renewable sources + CPRS
The market based allowance for electricity from non-renewable sources + CPRS
For the market based allowance, an additional allowance for volatility in the costs of electricity purchases based on the cost of sufficient working capital.

The user then selects which wholesale electricity cost allowance to use to calculate the variable R value:
The greater of LRMC or market based including volatility allowance

11/15/20107:17 AM                                                              4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                                       Cover
LRMC only
Market only including volatility allowance

The model calculates the total electricity cost allowance, including the additional Green costs and the working capital allowance for market based purchases.

General assumptions
Forecasts of costs and revenue are all in real 2009/2010 prices. Typical bills are shown in nominal and real prices.
The R values are shown in both 2009/2010 and 2010/2011 prices on the Scenario worksheet.

The model assumes a three year maximum determination period. If the determination period is less than three years, the NPV calculations need to be changed.

Total cost and revenue forecasts are calculated using constant 2008/2009 volumes and customer numbers. Percentage changes in total revenue are therefore equivalent
to changes in average tariffs (N, R and N+R).

Colour code
The model is colour coded, as follows:

Inputs (empty cell = 0)
Key outputs
Error check (check =0)
Error warnings, messages and unusual calculation assumptions
A double red line indicates a change in formula across row (or down a column)
Links from other files (green) (But use links as rarely as possible and very carefully)

VERY IMPORTANT: ONLY THE BLUE CELLS MAY BE CHANGED…
and any blue cell may be changed, even if it contains a formula because the function of that formula will be to calculate an input value.

Instructions for use

This worksheet captures a range input data on customer numbers, sales volumes, costs, current R values etc.
In principle, once data have been entered into this worksheet they should not need to be changed to run scenarios.
The decisions that need to be made to run scenarios entered on the Scenario worksheet.

The following inputs are required:
Table 1 - Customers and sales volumes on regulated retail tariffs
Enter the total number of customers on regulated retail tariffs in 2008/2009
Enter actual electricity sales volumes to customers on regulated tariffs in 2008/2009

11/15/20107:17 AM                                                             4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                           Cover
Note: sales volumes are requested by tariff type (ie single rate/block; TOU peak; TOU shoulder; TOU off-peak; Controlled load A and Controlled load B) so that the model can calculate
revenue from the variable R values for 2009/2010

Table 2. Network (DUOS and TUOS) charges by customer category
The model uses a forecast of network charges to
Calculate the \$ value of the network margin, which is specified as an percentage of N+R
Calculate customer bills.

Enter the following data:
DUOS and TUOS components of the network charge for customers on regulated tariffs, by tariff type, in 2008/2009 (nominal \$'000).
The average nominal increases in DUOS and TUOS tariffs for 2009/2010
The expected average real increases in DUOS and TUOS tariffs to the end of the forecast period for 2010/2011 to 2012/2013

The model records the DUOS and TUOS components separately, and calculates a forecast increase in the NUOS charge which is a weighted average of the two components.

Table 3 - Historical R values and expected cost recovery
Enter the R values that apply in 2009/2010
Enter the target and expected revenue for the year. The purpose of this information is to establish the extent of under/over recovery.
(This information may be obtained from the annual pricing compliance information provided to IPART.)

Table 4 - Regulated Tariffs for calculation of bills (excluding GST)
Enter the main regulated tariffs for residential and business customers for 2009/2010
For residential customers, enter the main single rate or block tariff, Controlled load A tariff and Controlled load B tariff.
For business customers, enter the single rate or block tariff with the highest number of customers.
The other tariffs recorded on this worksheet are for information only.

Scenario
This worksheet asks the user to
enter the costs and make the decisions that are required to determine the R values
enter the volumes for calculating typical customer bills.

The outputs of the model are displayed, so that the user can immediately see the impact of his/her decisions on the R values, average price changes, customer bills et.

INPUTS AND DECISIONS
Table 1 - Scenario Inputs for calculation of R values
Enter the following information:
General information
The inflation rate that applies to tariffs (to calculate nominal bills)
A real pre-tax WACC (for NPV calculations)
Energy purchase cost allowance
The add-on Green costs (RET, GGAS/ESS)

11/15/20107:17 AM                                                                4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                                           Cover
The long run marginal costs (LRMC) from non-renewable sources + CPRS
The market based allowance for electricity from non-renewable sources + CPRS
For the market based allowance, an additional allowance for volatility in the costs of electricity purchases based on the cost of sufficient working capital.

Make to following decision for the electricity costs allowance:
Use the greater of LRMC or Market based costs, LRMC only or Market based only?

Other components of cost
Losses, expressed as a percentage of electricity sales volumes
NEM fees
Retail operating costs (ROC), and the percentage of this that is allocated to the fixed charge
Customer acquisition and retention costs (CARC), and the percentage of this that is allocated to the fixed charge
Average consumption per customer on a regulated tariff in NSW. (This is used to convert the variable proportion of the ROC and CARC to \$/MWh.)
A net retail margin, expressed as a percentage of the total tariff (N+R).

KEY OUTPUTS
Table 2 - R values
This table shows the R values in both 2009/2010\$ and in 2010/2011\$.

Table 3. Total R and N revenue - 2009/2010 \$'000
The first part of this table shows R revenue, in real 2009/2010\$, as well as the associated real and nominal % increases.
The second part of this table shows N+R revenue, in real 2009/2010\$, as well as the associated real and nominal % increases.

Note that the revenue is calculated for constant 2008/2009 sales volumes and customer numbers. This means that the percentage increases shown in the table (nominal and real)
are also percentage increases in the R and N+R values.

Table 4. Total costs - 2009/2010 \$'000
This table shows the components of total costs, in dollar and percentage terms, as follows:
- Energy costs
- Retail costs
- Retail margin
Total R costs
Network charges
Total costs

INDICATIVE BILLS
Table 5 - Indicative Bills (\$ pa excluding GST)
This table calculates indicative bills for three sets of residential and business customers, for three different levels of consumption.
Enter the consumption volumes directly into the blue input cells in this table
The bills for 2009/2010 are calculated from the tariffs entered in Table 4 on the Inputs worksheet
The model then calculates future bills by increasing the Retail component of the bills by the average increase in the R values and the Network component of the bill by the average increase
in network charges.
Bills are shown in both nominal prices and real 2009/2010\$

11/15/20107:17 AM                                                              4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                                                         Cover
There is currently only one chart on this worksheet, namely the components of average bills in the final year.
The user may add more charts on this worksheet.

This sheet is not linked to the model calculations, and may be used to store raw data, calculate inputs, do additional analysis etc.

Calculation sheets
The remainder of the model consists of calculation worksheets.
Please DO NOT change any for the formulae in this section.
If any changes are made, they must be clearly marked and recorded on the Journal sheet

R values
This worksheet calculates:
the units costs that are needed to calculate the R values, in \$/MWh or \$/customer
the retail margin, as a percentage of the total N+R value
the fixed and variable R values, by adding up the individual components that make up these values.

Total costs
Total costs for the following components of the N+R value are calculated:
- Network charges
- Energy costs
- Retail costs
- Retail margin
These calculations provide the information for Table 4 on the Scenario sheet.

This model was developed by Bee Thompson, Independent Pricing and Regulatory Tribunal of NSW (IPART).
Date: October 2009

Contact details:      Bee Thompson             Tel 9290 8496, email Bee_Thompson@ipart.nsw.gov.au

11/15/20107:17 AM                                                             4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls        Cover
THE INDEPENDENT PRICING AND REGULATORY TRIBUNAL OF NSW                                                    ELECTRICITY - REGULATED RETAIL TARIFFS

CONTENTS
Tab name
INPUTS, DECISIONS AND OUTPUTS

1.1 General Inputs                                                                                              Cost Inputs
Table 1 - Customers and sales volumes on regulated retail tariffs
Table 2. Network (DUOS and TUOS) charges by customer category
Table 3 - Historical R values and expected cost recovery
Table 4 - Regulated Tariffs for calculation of bills (excluding GST)

1.2 Scenario Inputs and Outputs                                                                                 Scenario
Table 1 - Scenario Inputs for calculation of R values
KEY OUTPUTS
Table 2 - R values
Table 3. Total R and N revenue - 2009/2010 \$'000
Table 5 - Indicative Bills (\$ pa excluding GST)

1.3 Charts

1.4 Raw data dump and other calculations                                                                        free

DETAILED CALCULATIONS

2.1 Calculation of R values                                                                                     R values
Table 1 - Costs per customer or MWh - 2009/2010 \$
Table 2 - Calculation of tariffs and retail margins in \$/MWh - real 2009/2010 \$
Table 3 - R values - real 2009/2010 \$

2.2 Calculation Total Costs with constant customer numbers and sales volumes                                    Total costs
Table 1 - Total costs with constant 2008/2009 customer numbers and sales volumes

11/15/20107:17 AM                                                                     4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                        Contents
THE INDEPENDENT PRICING AND REGULATORY TRIBUNAL OF NSW                                                ELECTRICITY - REGULATED RETAIL TARIFFS

1.1 General Inputs

Colour code:
Inputs (empty cell = 0)
Error check (check =0)
Error warnings, messages and unusual calculation assumptions
A double red line indicates a change in formula across row (or down a column)
Links from other files (green) (But use links as rarely as possible and very carefully)

Tables                                                                                                               row number
Table 1 - Customers and sales volumes on regulated retail tariffs                                                           24
Table 2. Network (DUOS and TUOS) charges by customer category                                                               55
Table 3 - Historical R values and expected cost recovery                                                                   116
Table 4 - Regulated Tariffs for calculation of bills (excluding GST)                                                       137

Agency:                                  A Retailer

11/15/20107:17 AM                                                                 4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                       Inputs
Table 1 - Customers and sales volumes on regulated retail tariffs
Financial year ending 30 June                               2008        2009          2010              2011          2012          2013
Financial year                                             2007/2008    2008/2009      2009/2010        2010/2011     2011/2012     2012/2013
Unit                Actual        Actual          Projected     Projected     Projected

Total customers                                     no.                   447,100

Sales volumes
Residential
single rate and block (excluding controlled load)   MWh                 1,657,800
peak                                                MWh                    93,300
shoulder                                            MWh                   217,600
off-peak                                            MWh                   118,000
controlled load A (off-peak only)                   MWh                   244,100
controlled load B (peak, shoulder and off-peak)     MWh                   104,800
Total residential                                   MWh                 2,435,600

Business
single rate and block (excluding controlled load)   MWh                   403,600
peak                                                MWh                   170,600
shoulder                                            MWh                   304,300
off-peak                                            MWh                   241,500
controlled load A (off-peak only)                   MWh                     2,300
controlled load B (peak, shoulder and off-peak)     MWh                     4,000
Total business                                      MWh                 1,126,300

Total sales                                         MWh                 3,561,900
%
Source:

11/15/20107:17 AM                                            4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                             Inputs
Table 2. Network (DUOS and TUOS) charges by customer category
Financial year ending 30 June                                                          2009            2010            2011            2012            2013

\$'000       2009/2010 \$'000 2009/2010 \$'000 2009/2010 \$'000 2009/2010 \$'000
Unit                 Actual      Forecast with constant customer numbers

nominal %          real %          real %          real %
Average increases in DUOS charges                                                                         20.00%          12.00%          12.00%           2.00%
Average increases in TUOS charges                                                                         10.00%           8.00%           8.00%           5.00%
Average increases in network charges for calculation of bills)                                             18.9%           11.6%           11.6%            2.3%

DUOS
Residential DUOS
fixed charges (single rate and controlled load)                  \$'000                   25,600           30,720          34,406          38,535          39,306
fixed charges (TOU )                                                                        100              120             134             151             154
single rate (excluding controlled load)                          \$'000                  128,000          153,600         172,032         192,676         196,529
peak                                                             \$'000                   17,500           21,000          23,520          26,342          26,869
shoulder                                                         \$'000                    6,200            7,440           8,333           9,333           9,519
off-peak                                                         \$'000                      900            1,080           1,210           1,355           1,382
controlled load A (off-peak only)                                \$'000                    1,100            1,320           1,478           1,656           1,689
controlled load B (shoulder and off-peak)                        \$'000                    2,700            3,240           3,629           4,064           4,146
Total residential                                                \$'000                  182,100          218,520         244,742         274,111         279,594
check = 0                                                                                                    -               -               -               -
Business DUOS
fixed charges (single rate and controlled load)                  \$'000                    6,230            7,476           8,373           9,378           9,565
fixed charges (TOU )                                                                        180              216             242             271             276
single rate (excluding controlled load)                          \$'000                   31,160           37,392          41,879          46,905          47,843
peak                                                             \$'000                   32,000           38,400          43,008          48,169          49,132
shoulder                                                         \$'000                    8,670           10,404          11,652          13,051          13,312
off-peak                                                         \$'000                    1,840            2,208           2,473           2,770           2,825
controlled load A (off-peak only)                                \$'000                       10               12              13              15              15
controlled load B (shoulder and off-peak)                        \$'000                      100              120             134             151             154
Total business                                                   \$'000                   80,190           96,228         107,775         120,708         123,123
Total DUOS charges                                               \$'000                  262,290          314,748         352,518         394,820         402,716
check = 0                                                                                                    -               -               -               -
TUOS

11/15/20107:17 AM                                                          4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                                   Inputs
Residential TUOS
fixed charges (single rate and controlled load)             \$'000                              3,070        3,377       3,647    3,939    4,136
fixed charges (TOU )                                        \$'000                                 10           11          12       13       13
single rate (excluding controlled load)                     \$'000                             15,360       16,896      18,248   19,707   20,693
peak                                                        \$'000                              2,100        2,310       2,495    2,694    2,829
shoulder                                                    \$'000                                740          814         879      949      997
off-peak                                                    \$'000                                110          121         131      141      148
controlled load A (off-peak only)                           \$'000                                130          143         154      167      175
controlled load B (shoulder and off-peak)                   \$'000                                320          352         380      411      431
Total residential                                           \$'000                             21,840       24,024      25,946   28,022   29,423
-           -        -        -
Business TUOS
fixed charges (single rate and controlled load)             \$'000                                750          825         891      962    1,010
fixed charges (TOU )                                        \$'000                                 20           22          24       26       27
single rate (excluding controlled load)                     \$'000                              3,740        4,114       4,443    4,799    5,038
peak                                                        \$'000                              3,840        4,224       4,562    4,927    5,173
shoulder                                                    \$'000                              1,040        1,144       1,236    1,334    1,401
off-peak                                                    \$'000                                220          242         261      282      296
controlled load A (off-peak only)                           \$'000                                 10           11          12       13       13
controlled load B (shoulder and off-peak)                   \$'000                                 10           11          12       13       13
Total business                                              \$'000                              9,630       10,593      11,440   12,356   12,973
Total TUOS charges                                          \$'000                             31,470       34,617      37,386   40,377   42,396

Source:

Table 3 - Historical R values and expected cost recovery
Financial year ending 30 June                                                                               2010
Nominal

Fixed 'R'                                 \$/customer                                                        94.60

Variable 'R'
-Single rate and TOU combined            \$/MWh                                                             93.46
-Controlled Load A                       \$/MWh                                                             50.00
-Controlled Load B                       \$/MWh                                                             72.00

Target and Expected R revenue (from Pricing Proposals, using 2008/09 volumes and customer numbers)
Target R revenue                         \$'000                                                            349,500
Expected R revenue                       \$'000                                                            349,500
Under/(over) recovery                    \$'000                                                                -
% increase (-decrease) in Expected to reach Target                                                          0.00%
check = 0                                                                                                       -

11/15/20107:17 AM                                                             4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                              Inputs
Source:

Table 4 - Regulated Tariffs for calculation of bills (excluding GST)
Tariffs from 1 July 2009

Residential Block and Single Rate Tariffs (excl controlled load)
Block 1     Block 1                    Block 1       Block 2                 Block 2
Total fixed                                                                                                  Network
Add. Group    charge        Network      Block up to (kWh                        Network charge                           charge
Tariff Name(s)                              Tariff number          code        (\$/day)    charge (\$/day)        pa)        Tariff (\$/kWh)           (\$/kWh)          Tariff (\$/kWh)       (\$/kWh)
Domestic                                                                           0.400         0.200             7,000           0.1580               0.0700                0.2300         0.1000

Residential Controlled load
Total fixed
Add. Group    charge           Network      Tariff (excl GST, Network charge
Tariff Name                                 Tariff number          code        (\$/day)       charge (\$/day)       \$/kWh)         (\$/kWh)
Residential Controlled Load - Off Peak 1                                                  -        0.0150             0.0650         0.0070
For information only - not used to calculate bills

Residential Time of Use
Peak                                Shoulder                                Off-peak
Total fixed
Add. Group    charge           Network      Tariff (excl GST,   Network charge Tariff (excl GST,    Network charge       Tariff (excl
Tariff Name                                 Tariff number          code        (\$/day)       charge (\$/day)       c/kWh)            (\$/day)          c/kWh)            (\$/kWh)          GST, c/kWh)

Business Block and Single Rate Tariffs (excl controlled load)
Block 1           Block 1         Block 1    Block 2                          Block 2
Total fixed                                                                                                  Network
Add. Group    charge       Network      Block up to (kWh Tariff (excl GST, Network charge Tariff (excl GST,               charge
Tariff Name                                 Tariff number          code        (\$/day)   charge (\$/day)        pa)             \$/kWh)         (\$/kWh)           \$/kWh)                    (\$/kWh)
General Supply                                                                   0.6800        0.6000            10,000           0.1600          0.0650            0.2300                   0.1000

11/15/20107:17 AM                                                               4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                                                                  Inputs
11/15/20107:17 AM   4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls   Inputs
11/15/20107:17 AM   4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls   Inputs
11/15/20107:17 AM   4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls   Inputs
11/15/20107:17 AM   4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls   Inputs
Network
charge
(\$/kWh)

11/15/20107:17 AM   4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls   Inputs
1.2 Scenario Inputs and Outputs                                                                       Agency:          A Retailer

Colour code:
Inputs (empty cell = 0)
Key outputs
Error check (check =0)
Error warnings, messages and unusual calculation assumptions
A double red line indicates a change in formula across row (or down a column)
Links from other files (green) (But use links as rarely as possible and very carefully)

Scenario                                                               Number                             1.0
Description                                        Demonstration model

Table 1 - Scenario Inputs for calculation of R values
Financial year ending 30 June                                                       2008      2009            2010              2011            2012         2013
Financial year                                                                 2007/2008 2008/2009       2009/2010         2010/2011       2011/2012    2012/2013
Actual          Actual          Projected       Projected    Projected

Inflation rate applied to tariffs                                                  3.10%      2.33%         4.35%                   2.4%        2.5%         2.5%
Index 1                                                                    1.00     1.03       1.06          1.10
Index 2                                                                                                      1.00                   1.02        1.05         1.08
Real WACC                                          %                                                                           8.80%          8.80%        8.80%

ELECTRICITY COST ALLOWANCE (real 2009/2010\$/MWh)
from previous determination
Green energy costs                                                     2006/07\$
RET                                                \$/MWh                     2.2                                 2.4                0.98        1.19         1.33
GGAS/ESS                                           \$/MWh                     3.4                                 3.7                0.70        1.05         1.40
Total Green costs                                  \$/MWh                     5.6                                 6.1                 1.7         2.2          2.7

Long Run Marginal Cost for energy from non-renewable sources (LRMC) + CPRS1
Average                                        \$/MWh                50.0                                        55.0           67.32           74.49        86.45

Market price options for energy from non-renewable sources + CPRS1
Average                                         \$/MWh                       57.0                                62.8           45.06           71.87        96.15
Volatility allowance                            \$/MWh                        1.0                                 1.1            0.40            0.90         0.80
Total market                                                                                                                   45.46           72.77        96.95
Greater of LRMC/Market
LRMC only
Market only

11/15/20107:17 AM                                                                  4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                          Scenario
Greater of LRMC/Market
Use LRMC only or Market only or greater of LRMC/Market price?            LRMC only
% of electricity cost allowance at Market costs  %                       Market only                  100%            0%      0%    100%

Electricity cost allowance                        \$/MWh                   63.6                         70.0          69.0    76.7    99.7
Cost allowance excluding Green add-on                                     58.0                         63.9          67.3    74.5    97.0
check = 0                                                                                                            -       -       -

OTHER COMPONENTS OF COST (real 2009/2010\$)
from previous determination
2006/07\$
Distribution losses                               %                     9.00%                         9.00%        9.00%    9.00%   9.00%
Transmission losses                               %                     0.50%                         0.50%        0.40%    0.40%   0.40%
Total losses                                      %                     9.55%                         9.55%        9.44%    9.44%   9.44%
NEM Fees (real 2009/2010\$)                        \$/MWh                   0.68 2006/07\$                 0.75        0.80     0.80    0.80

Retail costs (real 2009/2010\$)
Retail opex (ROC)                                 Real \$/customer        75.0 2006/07\$                82.57          72.5    73.9    75.3
Customer acquisition and retention costs (CARC)   Real \$/customer        29.7 2006/07\$                32.69          32.8    32.8    32.8
% ROC reflected as fixed charge in tariff                                75%                           75%           75%     75%     75%
% CARC reflected as fixed charge in tariff                              100%                          100%          100%    100%    100%
Average consumption per customer on a regulated
tariff (EA, IE & CE combined)                     MWh                    8.81 2006/07\$                 8.81         8.64     8.64    8.64
Net Retail Margin                                                        5.0%                          5.0%         5.4%     5.4%    5.4%

1. CPRS = carbon pollution reduction scheme

KEY OUTPUTS

11/15/20107:17 AM                                                                4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                   Scenario
Table 2 - R values
Financial year ending 30 June                                                                             2010         2011          2012          2013
2009/2010\$   2009/2010 \$   2009/2010 \$   2009/2010 \$
R values in 2009/2010 prices
Fixed 'R'                                             \$/customer                                         94.6          87.2          88.2          89.3
Variable 'R'                                          \$/MWh                                              89.8          89.8          99.6         126.3
Real % increase in R values
R values for Legal Determination - 2010/2011 \$/customer and c/kWh
Fixed 'R'                                        \$/customer                                                            89.3          90.3          91.4
Variable 'R'                                     c/kWh                                                                 9.20         10.19         12.93

Table 3. Total R and N revenue - 2009/2010 \$'000
Using constant 2009 customer numbers and sales volumes, and assuming Expected revenue = Target revenue
Financial year ending 30 June                                                                            2010          2011          2012          2013    NPV & av
2009/2010\$    2009/2010 \$   2009/2010 \$   2009/2010 \$   annual %
Revenue from R values
Fixed R                                               \$'000                                            42,296        38,976        39,445        39,915
Variable 'R'                                          \$'000                                           319,852       319,992       354,608       449,919
Total R revenue                                       \$'000                                           362,147       358,968       394,053       489,834    \$1,088,083
real % increase in revenue ( = av increase in R)                                                                      -0.9%          9.8%         24.3%      10.6%
cumulative real % increase                                                                                            -0.9%          8.8%         35.3%
nominal % increase in revenue ( = av increase in R)                                                                    1.5%         12.5%         27.4%      13.3%
cumulative nominal % increase                                                                                          1.5%         14.2%         45.5%
Revenue from N+R values
Total R revenue                                       \$'000                                           362,147       358,968       394,053       489,834
Total N revenue                                       \$'000                                           349,365       389,904       435,197       445,112
Total N+R revenue                                     \$'000                                           711,512       748,872       829,251       934,946    \$2,205,860
real % increase in total N+R revenue (=average increases in tariffs)                                                   5.3%         10.7%         12.7%      9.5%
Cumulative real increase                                                                                               5.3%         16.5%         31.4%
nominal % increase in total N+R revenue                                                                                7.8%         13.5%         15.6%      12.2%
Cumulative nominal increase                                                                                            7.8%         22.3%         41.4%

Table 4. Total costs - 2009/2010 \$'000
Total R costs = Total allowed revenue from R values when R values calculated for each year
Financial year ending 30 June                                                                             2010         2011          2012          2013    NPV & av
2009/2010\$   2009/2010 \$   2009/2010 \$   2009/2010 \$   annual %
R costs

11/15/20107:17 AM                                                                  4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                             Scenario
Energy costs                     \$'000                                   272,080    302,212   391,671
Retail costs                     \$'000                                    46,448     47,062    47,676
Retail margin                    \$'000                                    40,439     44,780    50,487
Total R costs                    \$'000                      362,147      358,968    394,053   489,834   \$1,088,083
Network charges                  \$'000                      349,365      389,904    435,197   445,112
Total costs                      \$'000                      711,512      748,872    829,251   934,946
check = 0                                                                    -          -         -

% Breakdown
energy as % total costs          %                                         36.3%      36.4%     41.9%
retail cost as % total costs     %                                          6.2%       5.7%      5.1%
retail margin as % total costs   %                                          5.4%       5.4%      5.4%
N as % total costs               %                             49%         52.1%      52.5%     47.6%
R as % total costs               %                             51%         47.9%      47.5%     52.4%
energy as % R costs              %                                         75.8%      76.7%     80.0%

11/15/20107:17 AM                        4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                    Scenario
Table 5 - Indicative Bills (\$ pa excluding GST)
Financial year ending 30 June                                                                     2010         2011      2012     2013    2011        2012    2013
% increases
Inflator                                                                                          1.00         1.02      1.05     1.08
Average nominal increase in network component                                                                  14%       14%        5%
Average nominal increase in retail component                                                                   1.5%     12.5%    27.4%
-         -        -
Typical N+R bills (nominal)
Residential
Low usage (without Controlled load)                    for kWh =    3,000                          620          665       755      878   7.3%       13.4%    16.3%
Typical usage (without Controlled load)                for kWh =    5,600                        1,031        1,106     1,254    1,461   7.3%       13.4%    16.5%
Typical usage with Controlled load A                   for kWh =   11,000                        1,842        1,970     2,233    2,614   6.9%       13.4%    17.1%
of which off-peak                                   for kWh =    2,000                          130          134       152      188   3.4%       12.8%    23.6%
standard                                 for kWh =    9,000                        1,712        1,835     2,081    2,427   7.2%       13.4%    16.6%
Business
20 MWh                                                 for kWh =   20,000                        4,148        4,449     5,047    5,878   7.3%       13.4%    16.5%
40 MWh                                                 for kWh =   40,000                        8,748        9,374    10,631   12,403   7.2%       13.4%    16.7%
80 MWh                                                 for kWh =   80,000                       17,948       19,223    21,800   25,453   7.1%       13.4%    16.8%

Typical N+R Bills in real 2009/2010\$
Residential
Low usage (without Controlled load)                    for kWh =    3,000                          620          650       719      816   4.8%       10.7%    13.5%
Typical usage (without Controlled load)                for kWh =    5,600                        1,031        1,080     1,195    1,358   4.8%       10.7%    13.6%
Typical usage with Controlled load A                   for kWh =   11,000                        1,842        1,923     2,128    2,430   4.4%       10.6%    14.2%
of which off-peak                                  for kWh =    2,000                          130          131       145      174   1.0%       10.1%    20.6%
standard                               for kWh =    9,000                        1,712        1,792     1,983    2,256   4.7%       10.6%    13.7%
Business
20 MWh                                                 for kWh =   20,000                        4,148        4,345     4,808    5,464   4.7%       10.7%    13.6%
40 MWh                                                 for kWh =   40,000                        8,748        9,154    10,129   11,529   4.6%       10.6%    13.8%
80 MWh                                                 for kWh =   80,000                       17,948       18,773    20,769   23,659   4.6%       10.6%    13.9%
Bills for average tariff (nominal \$/kWh)                                                                        -         -        -
Consumption for average bill                    kWh                 7,000
Total N+R bill (excluding GST)                 \$ pa                                             1,398        1,507     1,711    1,977    7.8%      13.5%    15.6%
network component of bills                   \$ pa                                               687          785       898      941   14.3%      14.4%     4.8%
retail component of bills                    \$ pa                                               712          722       813    1,036    1.5%      12.5%    27.4%

11/15/20107:17 AM                                                           4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                                 Scenario
THE INDEPENDENT PRICING AND REGULATORY TRIBUNAL OF NSW        ELECTRICITY - REGULATED RETAIL TARIFFS
Agency:       A Retailer

1.3 Charts

Components of average bills in 2012/2013

Energy costs, 41.9%
Network charges, 47.6%

Retail margin, 5.4%   Retail costs, 5.1%

11/15/20107:17 AM                                    4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls             charts
ELECTRICITY - REGULATED RETAIL TARIFFS
THE INDEPENDENT PRICING AND REGULATORY TRIBUNAL OF NSW
Agency:  A Retailer

1.4 Raw data dump and other calculations
This sheet is not linked to the model calculations, and may be used to store raw data, calculate inputs, do additional analysis etc.

11/15/20107:17 AM                                                      4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                  free28
CALCULATION SHEETS

PLEASE DO NOT CHANGE ANY FORMULAE ON THE CALCULATION SHEETS
THE INDEPENDENT PRICING AND REGULATORY TRIBUNAL OF NSW                                       ELECTRICITY - REGULATED RETAIL TARIFFS
Agency:     A Retailer
2.1 Calculation of R values

Tables                                                                                                                             row number
Table 1 - Costs per customer or MWh - 2009/2010 \$                                                                                           13
Table 2 - Calculation of tariffs and retail margins in \$/MWh - real 2009/2010 \$                                                             47
Table 3 - R values - real 2009/2010 \$                                                                                                       69

Table 1 - Costs per customer or MWh - 2009/2010 \$
Financial year ending 30 June                                                        2009           2010         2011        2012        2013
nominal        nominal 2009/2010 \$ 2009/2010 \$ 2009/2010 \$

Energy cost allowance                                 \$/MWh                                         70.0        69.0        76.7         99.7

Losses                                                \$/MWh                                          6.7         6.5         7.2          9.4

NEM fees (on sales plus losses)                       \$/MWh                                          0.8         0.9         0.9          0.9

Retail operating and depreciation costs
-fixed portion                                       Real \$/customer                               94.6        87.2        88.2         89.3
-variable portion (over all NSW regulated
customers)                                            \$/MWh                                          2.3         2.1         2.1          2.2
check = 0

11/15/20107:17 AM                                                  4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                        R values
THE INDEPENDENT PRICING AND REGULATORY TRIBUNAL OF NSW                                              ELECTRICITY - REGULATED RETAIL TARIFFS
Agency:     A Retailer
2.1 Calculation of R values
Network charges (to calculate retail margin)
DUOS
- fixed charges                                        Real \$/customer                       71.8          86.2      96.5      108.1         110.3
- variable charges                                     \$/MWh                                 64.6          77.5      86.9       97.3          99.2

TUOS
- fixed charges                                        Real \$/customer                        8.6           9.5      10.2       11.0          11.6
- variable charges                                     \$/MWh                                  7.8           8.5       9.2        9.9          10.4

NUOS
- fixed charges                                        Real \$/customer                       80.4          95.7     106.8      119.2         121.9
- variable charges                                     \$/MWh                                 72.4          86.1      96.1      107.2         109.7

Average consumption per customer
This is used to convert \$/customer (ie fixed charges) to \$/MWh costs in the retail margin calculation
Average consumption                                    MWh/customer                            8.0          8.0       8.0        8.0           8.0

11/15/20107:17 AM                                                   4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                           R values
THE INDEPENDENT PRICING AND REGULATORY TRIBUNAL OF NSW                              ELECTRICITY - REGULATED RETAIL TARIFFS
Agency:     A Retailer
2.1 Calculation of R values
Table 2 - Calculation of tariffs and retail margins in \$/MWh - real 2009/2010 \$
Financial year ending 30 June                                                              2010         2011       2012        2013
nominal 2009/2010 \$ 2009/2010 \$ 2009/2010 \$

Retail net margin                                                                          5.0%         5.4%        5.4%        5.4%

- fixed network charge                       \$/customer                                    96          107         119         122
- fixed retail costs                         \$/customer                                    95           87          88          89
- fixed network charge                       \$/MWh                                        12.0         13.4        15.0        15.3
- fixed retail costs                         \$/MWh                                        11.9         10.9        11.1        11.2
- variable network charge                    \$/MWh                                        86.1         96.1       107.2       109.7
- electricity sold                           \$/MWh                                        70.0         69.0        76.7        99.7
- electricity losses                         \$/MWh                                         6.7          6.5         7.2         9.4
- NEM fees (on purchase volumes)             \$/MWh                                         0.8          0.9         0.9         0.9
- variable retail costs                      \$/MWh                                         2.3          2.1         2.1         2.2
- retail margin                              \$/MWh                                        10.0         11.4        12.6        14.2
Total                                         \$/MWh                                       199.8        210.2       232.8       262.5
Net margin                                    %                                            5.0%         5.4%        5.4%        5.4%
check net margin = 0                                                                     0.000%       0.000%      0.000%      0.000%

Table 3 - R values - real 2009/2010 \$
Financial year ending 30 June                                                              2010         2011        2012        2013
2009/2010 \$ 2009/2010 \$ 2009/2010 \$
Combined residential and Business Rs (weighted averages, weighted by consumption)
Fixed 'R'                                      \$/customer                                               87.2        88.2        89.3
Variable 'R'                                   \$/MWh                                                    89.8        99.6       126.3
check = 0                                                                                                -           -           -

11/15/20107:17 AM                                         4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                                        R values
THE INDEPENDENT PRICING AND REGULATORY TRIBUNAL OF NSW               ELECTRICITY - REGULATED RETAIL TARIFFS
Agency:   A Retailer

2.2 Calculation Total Costs with constant customer numbers and sales volumes

Table 1 - Total costs with constant 2008/2009 customer numbers and sales volumes
Financial year ending 30 June                                 2009             2010          2011      2012      2013
2010 for info only
Total sales                            MWh               3,561,900

Network charges                        \$'000                              349,365         389,904   435,197   445,112
Energy costs                           \$'000                              275,909         272,080   302,212   391,671
Retail costs                           \$'000                               50,648          46,448    47,062    47,676
Retail margin                          \$'000                               35,575          40,439    44,780    50,487
Total costs                            \$'000                              711,498         748,872   829,251   934,946
Margin %                               %                                     5.0%            5.4%      5.4%      5.4%
check = 0                                                                     -               -         -         -

Total
Network charges                        \$'000                              349,365         389,904   435,197   445,112
Energy costs                           \$'000                              275,909         272,080   302,212   391,671
Retail costs                           \$'000                               50,648          46,448    47,062    47,676
Retail margin                          \$'000                               35,575          40,439    44,780    50,487
Total costs                            \$'000                              711,498         748,872   829,251   934,946
Margin %                               %                                     5.0%            5.4%      5.4%      5.4%
check = 0                                                                     -               -         -         -
check network = 0                                                             -               -         -         -
check margin = 0                                                                0               0       -           0
check R revenue = 0                                                            na             -         -         -

% Distribution of R value components
Energy costs                           %                                     76.2%         75.8%     76.7%     80.0%
Retail costs                           %                                     14.0%         12.9%     11.9%      9.7%
Retail margin                          %                                      9.8%         11.3%     11.4%     10.3%
Total costs                            %                                    100.0%        100.0%    100.0%    100.0%
check = 0                                                                      -             -         -         -

11/15/20107:17 AM                                            4f91f207-4c01-45cd-a313-284e2a9cb7e3.xls                      Total costs

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 75 posted: 11/14/2010 language: English pages: 33
Description: Calculate Retail Margin document sample