Docstoc

Calculate Retail Margin

Document Sample
Calculate Retail Margin Powered By Docstoc
					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
Description: Calculate Retail Margin document sample