Guide to the HiBIS Finacial Plan Template by alllona

VIEWS: 16 PAGES: 10

									Guide to the HiBIS Financial Plan Template
What is the HiBIS Financial Plan Template?
The HiBIS Financial Plan Template is a financial model that complements an
Applicant’s business plan, for those Applicants required to complete these. The model
consists of five Microsoft Excel work sheets that generate customer numbers, revenue
figures and financial statements. These are generated from information about an
Applicant’s HiBIS proposal that the Applicant is required to put into the relevant parts
of the model. These are clearly identified in the model.

Who has to complete the Financial Plan Template?
The Financial Plan Template, or Financial Model, has to be completed by Applicants
who fall into:
• Category C, that is, providers with revenues of $1 million to $50 million but less
   than 1,000 higher bandwidth customers; and
• Category D, that is, providers with revenues of less than $1 million and any
   number of higher bandwidth customers.

Using the model: an overview
In summary, the model takes the Applicant through the following process:
1.    Select area(s) for which you seek to provide a HIBIS service and source some
      demographic and other data that generates an addressable market size.
2.    Estimate your market share and hence customer base.
3.    Set some assumptions for input parameters such as market growth, inflation,
      interest rates, depreciation rates, timing of receipts and payments.
4.    Set pricing hence gross revenue is derived from customer numbers.
5.    Assess cost of sales against revenue.
6.    Assess and enter the amount of capital expenditure required for your type of
      business.
7.    Assess the other operating costs required to operate the business.
8.    Enter an opening balance sheet and a few other minor aspects of the financial
      statements and the financial statements are produced automatically including
      items such as depreciation, interest and tax.

The model requires Applicants to provide quarterly information for the first 2.5 years
commencing from 1 January 2004, followed by 3 years of annual data.
• Input is only required in Yellow coloured cells. All other cells are derived
   automatically.
• All input cells in Yellow are set to 0 in the model. If you do not enter anything or
   choose to over-write a cell’s formula, that should be clearly set out in the
   explanatory narrative required.

The Model provides some guidance to Applicants through helpful comments that have
been inserted against various cells. It also has reminders about some of the
considerations that should be taken into account when filling in information about
some items. (The explanatory notes/comments in the model are denoted by red
triangles in the top right hand corner of the relevant description cell.)



Higher Bandwidth Incentive Scheme (HiBIS) – Guide to the HiBIS Financial Plan Template
                                             2


The process has been kept as simple as possible but it is assumed that Applicant’s will
have a reasonable knowledge of financial models and statements, and Microsoft
Excel.

Applicants will have to undertake some research of their intended market to fill all of
the required information.

The Model has been built trying to balance the range of Applicants that may use it in
terms of their current size, level of ambition in their future business plan, and
difference between infrastructure-based operators as opposed to resellers. However, it
has been built with smaller sized Applicants in mind.

Putting information into the model: a detailed guide

Market, Revenue, Cost of Sales (COS) (Sheet 1)
This sheet predicts addressable market size, hence revenue and then the cost of sales
applicable to those sales.

Demographics
Row 3 starts with a requirement to enter the population of the area(s) within which the
HiBIS service is to be provided. It will then automatically increase from the
population growth assumption entered in row 2 of the assumptions sheet.
If an Applicant is seeking to serve a single area, for example a particular town, this
requirement will be obvious. However, two alternatives and suggested treatments are:

1. “Micro areas” within a broader catchment area eg. 8 settlements with a particular
   catchment area within a single State. In this case, Applicants could either just
   aggregate those populations and explain their strategy and identify the intended
   target areas in the text of their Business Plan, or, insert additional rows above the
   existing row 3 for each “micro area” and sum them to a single figure in the
   existing row 3.

2. Several dispersed or differing geographic areas or ones where different
   technologies might be used for access. In this case, we would strongly
   recommend that the Applicant copy the Market, Revenue, COS spreadsheet and
   complete a separate one for each area. They would then copy across the resultant
   revenue and COS totals in to the Profit & Loss Statement with the Financial
   Statements in row 8 and 21 respectively (if more than two areas, the third would
   be copied to rows 9 and 22 etc. with more being inserted as required).

The total population exercise described above is then repeated in row 5 for households
with the relevant assumption being in row 3 of the assumption sheet. It is also required
in rows 19 and 4 of the relevant sheets respectively for Eligible Businesses (EBs).

There are then three critical rows of data entry that are identical in format between
residential and EBs hence it is only described once. In each case, the data must be
entered quarterly from the quarter ending 31 March 2004 and ending on 30 June 2006,
then annually ending at 30 June 2009.



Higher Bandwidth Incentive Scheme (HiBIS) – Guide to the HiBIS Financial Plan Template
                                             3


•   Row 6 (EB = 20) requires a prediction of households by percentage that will be
    internet enabled (i.e. it is a sub-set of row 5);
•   Row 8 (EB = 22) requires a prediction of the households by percentage that will
    be internet enabled and connected by broadband (i.e. it is a sub-set of row 6); and
•   Row 11 (EB = 25) requires a prediction of the broadband enabled households by
    percentage that will receive their broadband service from the Applicant under
    HiBIS (i.e. it is a sub-set of row 8).

(Note - Rows 34-46 also provide the facility to repeat this exercise for non-eligible
businesses (NEBs) if desired.)

These are critical inputs that will be the primary determinant of the Applicant’s revenue
within this model. It will therefore determine the quantum of their forecast HiBIS
incentive funding. Given this, Applicants should include in the Business Plan narrative
the relevant information sources and any other relevant information supporting the
estimates made in the model.

The model automatically calculates the number of households (and EBs and NEBs) at
each level including the Applicant’s expected number of customers at rows 12, 26 and
42 respectively and overall customers total at row 48. All cells in pale blue are
automatically generated growth figures.

Technology and Services
Having established a customer forecast, the model considers the type of technology
Applicants intent to use and the level of service customers will be supplied. The type
of technology is important because it will help an Applicant to determine the cost
structure they need to enter to support their services.

Rows 51-55 must add to 100% in row 56. The default is that all services not specified
to the contrary will be serviced using ADSL hence an ADSL percentage is not
required in row 51. As an example, if 20% of customers are to be serviced by each of
satellite and wireless, the remaining 60% will automatically appear as 60% in the
ADSL cell (row 51). Whilst this entry must be made for every period, in reality, it is
expected that most Applicants will only use one, or perhaps two technology access
types and if they do, the mix will only change very slowly over time if at all.

In Rows 59-71, the service mix is defined. It has been assumed that no more than
three grades of service will be offered for any technology. The default is that all
services are “Basic” unless specified to the contrary by percentage in the yellow cells
under the service mix heading. If an Applicant proposes more than three levels of
service, the model can be manually amended to accommodate that. Also, if any of the
entered service descriptions are inaccurate, they can also be manually changed with an
explanation provided in the Business Plan.

Rows 76-89 automatically calculate the number of customers (shown in the model as
Services in Operation (SIOs)) by technology and grade of service. Rows 91-103 use
these numbers to calculate customer numbers by grade of service and type of
customer (i.e. new and pre-existing).




Higher Bandwidth Incentive Scheme (HiBIS) – Guide to the HiBIS Financial Plan Template
                                             4


Revenue Calculation
Revenues earned from customers
Having derived customer numbers, revenue projections are now made. Eights sets of
revenue calculations are completed and these are differentiated by grade of service
and whether these services are supplied to new or pre-existing customers. The
method of operation is the same for each set of revenue projections, so only the first
set, New Services-Basic Level Service Pricing, will be explained here.

The revenue calculations for New Services-Basic Level Service Pricing occur in rows
107-114. The Applicant inputs required here are price data. The price data entered
here are critical, particularly as the data are entered in once initially, and then changes
over time depending on the annual change assumptions entered in rows 6 & 7 of the
assumptions sheet. Applicants should note that prices proposed for HiBIS purposes
cannot be exceeded but will be subject to a mid-term review. (This does not prevent
providers offering prices at levels below proposed prices and gradually increasing
them to their proposed level.)

The pricing model allows for charging customers for customer premises equipment
(CPE) (eg. a modem or a satellite dish, initially, together with an installation fee.)
These are both driven from that point forward by the assumption sheet on rows 6 and
7 respectively.

The ongoing pricing model presumes a monthly access fee, with the provision for
excess download fees and other revenue that could be for anything else related to the
broadband service.

The components of this pricing model should be able to accommodate a wide
variation of other pricing models. The model can be manually amended to cater for
such alternate pricing if necessary, providing explanatory notes are provided. Also, if
the ongoing pricing model expected does not fit within the simple “inflation” based
structure, Applicants may over-ride the relevant cells, providing an explanation is
provided; for example, if it is expected that self-installation will occur in all cases
from mid-way through a year, say 2005. Rather than manipulate this outcome with
the inflation factor, the actual charge could be entered for the first two quarters and 0
for the second two.

In completing this section of the Financial Model, Applicants are reminded of the
pricing requirements in relation to comparable metropolitan charges that apply for any
services being provided with HiBIS support.

Revenues from HiBIS incentive payments
Total revenue from HiBIS payments is calculated in rows 177 to 200. The total
HiBIS payments calculated are dependent on Applicant input on incentive eligibility
of its customer base and the mix of incentive levels.

The only Applicant input required on HiBIS eligibility is the percentage of its
residential customers that a provider expects will not be eligible for HiBIS payments.
This is included to make allowance for any residential customers to whom a provider
may supply a service who are not eligible to receive a HiBIS incentive. Eligible
businesses are by definition eligible for HiBIS payments.

Higher Bandwidth Incentive Scheme (HiBIS) – Guide to the HiBIS Financial Plan Template
                                             5




Rows 186 to 191 have cells for Applicant input on the mix of HiBIS payments that a
provider expects in its eligible customer base. Rows 187-191 must add to 100% in
row 186. The default is that all eligible services not specified to the contrary will
attract the Standard Incentive 1 for New Customers, hence a percentage is not
required to be input in row 186. As an example, if 20% of customers attract each of
Standard Incentive 2 for New Customers and High Cost Area Incentive for New
Customers, the remaining 60% will automatically appear as 60% in the Standard
Incentive 1 for New Customers cell (row 186). The model accommodates a changing
mix of payments over time.

The incentive amounts for each class of HiBIS payment are calculated automatically
in rows 194-199. Total revenues from HiBIS payments are totalled automatically in
row 200.

Total revenue, including HiBIS payments is calculated automatically in row 202 and
copied across to row 7 of the Profit and Loss Statement (Financial Statements sheet).
Any subsequent sheets used for different areas would need to be manually linked
across to the subsequent lines of the Profit and Loss Statement by the Applicant.

Cost of Sales
Eights sets of revenue calculations are completed for cost of sales and these are
differentiated by grade of service and whether these services are supplied to new or
pre-existing customers. The method of operation is the same or very similar for each
set of cost of sales projections, so only the first set, New Services-Basic Level Service
Pricing, will be explained here.

The cost of sales calculations for New Services-Basic Level Service Pricing occur in
rows 206-216. Rows 207 and 208 require COS to be input as a percentage of revenue
for each relevant period for Installation and CPE respectively. All other components
of COS, require actual costs to be entered manually for every period in rows 209-215.
Whilst five common categories of cost have been pre-labelled, Applicants are free to
re-label them or add rows if they wish to best reflect the nature of their business. In
particular, Applicants are advised to consider the aspects of their business that will be
supported through their owned fixed asset infrastructure hence depreciation charges
through the Profit and Loss Statement (ie. depreciation should not be part of COS).
As an obvious example, the Applicant may intend to buy and retain ownership of
CPE, then the COS will be 0% and the assets will be reflected in the Balance Sheet
(Financial Statements sheet) with associated depreciation being charged in subsequent
periods, as well as any applicable funding costs. Also, Applicants should consider all
the cost categories included in the opex sheet (see below) to ensure correct
categorisation of costs and non-duplication.

Cost of sales for pre-existing customers does not require the percentage inputs for
Installation and CPE.

Applicants should ensure that the narrative provided in their Business Plan is
consistent with and adequately explains their approach to cost of sales as reflected in
the Financial Model. Further detail may be added within this model or even


Higher Bandwidth Incentive Scheme (HiBIS) – Guide to the HiBIS Financial Plan Template
                                             6


separately if it assists Applicants in this area, providing it is fully explained and does
not change the overall construct of this model.

Total cost of sales is automatically calculated in row 289 and copied across to row 20
of the Profit and Loss Statement. Any subsequent sheets used for different areas
would need to be manually linked across to the subsequent rows of the Profit and Loss
Statement by the Applicant so as to align with the relevant revenue rows as described
under the Revenue Calculation above.

Capex (Sheet 2)
It is clear on first view that this section of the Financial Model is almost totally non-
prescriptive. It requires input in just two categories for each of the five prescribed
technology categories. This is a deliberate decision that recognises the extremely
wide range of technological choices Applicants have in establishing their HiBIS
business. Rather than attempt to accommodate all technology types, scales of
business and models of operation (eg. wholly owned infrastructure, partly
owned/partly outsourced etc), the model has been left deliberately free of detail.

It is for Applicants to provide the appropriate level and form of detail to reflect their
intended business. Some Applicants may have minimal or even no fixed assets.

As with Cost of Sales, Applicants should ensure that the narrative provided in their
Business Plan response is consistent with and adequately explains their approach to
capital expenditure as reflected in the Financial Model. Further detail may be added
within this model or even separately if it assists Applicants in this area, providing it is
fully explained and does not change the overall construct of this Model.

Providing rows 26 and 27 are populated, depreciation will be automatically calculated
in the Profit and Loss Statement using the straight line method. It is assumed that
assets are purchased evenly through any given year, except for the six months ending
1 January 2004, when they are all presumed to have been purchased at the start of that
period.

Rows 10 and 11 in the assumptions sheet require depreciation rates to be entered for
CPE and network equipment respectively. If Applicants find having a single rate for
the latter too limiting or inappropriate, they may either use a single “average” rate and
explain it in the narrative, or, if experienced financial modellers, may choose to
expand the model so as to more accurately reflect their particular business. Care
should be taken with the latter approach unless proposing a fairly large scale, heavily
infrastructure-based business.

For those interested, rows 29-108 show how the depreciation charges are derived.
These are copied to rows 50 and 51 of the Profit and Loss Statement.

The total Net Book Values of both asset classes combined is also calculated in row
108 and copied to row 115 of the Balance Sheet (Financial Statements sheet).
Finally, the total capital expenditure in the period is copied across to the Cash Flow
Statement (Financial Statements sheet).



Higher Bandwidth Incentive Scheme (HiBIS) – Guide to the HiBIS Financial Plan Template
                                             7


Opex (Sheet 3)
The operating expenditure (Opex) sheet is very straightforward. It divides all the
operating costs of the business below Gross Margin level in to three categories with a
number of sub-categories (shown in brackets below):
• Marketing and sales [5];
• Operations[4]; and
• General and administration [3].

The categories used within each of these groupings are quite common within the
industry and explanatory notes are provided in most cases as to what should be
included. (The explanatory notes/comments in the model are denoted by red triangles
in the top right hand corner of the relevant description cell.)

If any particular sub-category is not relevant to the Applicant’s intended business, it
should be left blank.

There are three methods for completing any of these categories as described below. It
will be obvious from the model what applies in each case.

1.    Costs derived from staff numbers and cost with provision to add other non-staff
      costs. The first sub-category “Marketing” in rows 5-10 is such an example;
      a.    Average staff numbers are entered period by period in row 6;
      b.    Average annual staff cost, including on-costs is entered in cell E7;
      c.    The average staff cost is automatically increased annually by a wage and
            salary inflation factor entered in row 8 of the Assumption sheet.
            (However, as noted in the Assumption sheet, since all average salary cost
            calculations are derived from the initial amount set, if in the early periods
            especially, staffing mix is changing significantly, this should be reflected
            through this inflator or manually over-ridden in the relevant Opex sheet
            salary cost line. For example, if a business started off with 3 people who
            cost an average of $60,000 per annum each but in the second year, the
            business grew to have 10 people where the average had fallen to $40,000,
            the option is either to enter an inflation factor of -50% or to over-ride the
            amount calculated in row 7.);
      d.    The staff cost for the period is automatically calculated in row 8 (Note – if
            this is not deemed to be a relevant method for the Applicant’s business it
            may be ignored and all costs simply entered in accordance with point ‘e’
            below. An example may be where large parts of the business are to be
            outsourced (eg. billing) or the business is so small that effectively,
            fractions of individuals would be spread across several sub-categories,
            hence it is more useful to enter the actual cost only. In this case, the
            approach should be included in the Business Plan narrative);
      e.    Row 9 allows any other relevant costs for the sub-category to be added as
            an actual amount (most descriptor cells in column C include some
            guidance comments on costs to consider); and
      f.    The sub-category cost is automatically calculated in Row 10.

2.    Costs derived as a direct percentage of revenue. Advertising and Promotion in
      rows 12-14 is an example. The relevant percentage must be entered for every
      period in row 13 and the total appears in row 14.

Higher Bandwidth Incentive Scheme (HiBIS) – Guide to the HiBIS Financial Plan Template
                                             8




3.     A blank row is provided in each of the three cost categories to allow any other
       costs to be entered manually by period as shown in row 30 as Other Marketing
       and Sales Costs.

Totals for the three categories are automatically calculated in rows 32, 58 & 74
respectively. These are then copied to rows 41-43 respectively of the Profit and Loss
Statement. (An automatic sub-routine occurs in rows 77-86 to separate staff from
other costs to recognise cash flow timing differences in the Cash Flow statement.)

Assumptions (Sheet 4)
Items 1-3 in column A of this worksheet have already been explained in sections
above. The remaining assumption items are explained below:

•    Item 4 – The number of days on average taken to collect or pay the stated revenue
     or costs respectively. This allows for greater accuracy of actual cashflows in the
     Cash Flow Statement by recognising when funds flow rather than when they
     appear in the Profit and Loss Statement. Once entered, the model automatically
     adjusts for the timing of the relevant receipts and payments.

•    Item 5 – Interest rates for Income (expense) and Borrowings. These rates are
     automatically applied to the average balance in each period for cash as derived in
     the Cash Flow Statement, and borrowings as reflected in the Balance Sheet, and
     transferred to the Profit and Loss statement and Cash Flow Statement. (In the case
     of the interest income, there is a single rate irrespective of whether the cash
     balance in the period is positive or negative whereas in reality, these would almost
     always attract different rates. If a sophisticated user of such models felt it was
     material to their business case to differentiate, it would be possible to add a sub-
     routine in the relevant formula to add a differentiation rate if the balance was
     negative. This is permissible (though not necessarily encouraged) providing it is
     explained and does not disrupt the integrity of the model as presented.)

•    Item 6 – Corporate tax rate. The standard rate that currently prevails within
     Australia is presumed to continue for the period of this model and should not be
     changed. (More information on the calculation of tax is provided below in the
     section on profit and loss statements.)

•    Item 7 – The percentage of premises that will be receiving a new service, not a
     pre-existing service.

Financial Statements (Sheet 5)
The Financial Statements sheet automatically generates Profit and Loss Statements,
Cash Flow Statements and Balance Sheets. Each is described below.
Profit and Loss Statements
The format produced is quite simple yet produces the major items one would expect to
see in such a Statement. The only items that have not been covered within the narrative
above are Other Products, the calculation of income tax expense and dividends.




Higher Bandwidth Incentive Scheme (HiBIS) – Guide to the HiBIS Financial Plan Template
                                             9


Other products are allowed for in terms of both revenue and COS in rows 12-14 and
25-27 respectively. In general, it is preferred that Applicant’s businesses are
presented in the Financial Model in as “clean” a way as possible in relation to HiBIS.
However, it is recognised that in some circumstances, Applicants may have a
particular business plan that is predicated very heavily on selling bundled products to
a particular customer set, such that without it, the business plan is not viable. In such
cases, the relevant revenue and costs should be entered directly in to the relevant rows
for each period and full narrative provided in the Business Plan.

The calculation of income tax expense is an automatic function that occurs in rows
64-70. Its only purpose is to recognise the impact of tax losses which may be
applicable to some Applicants in start up mode with their HiBIS business.

If it is anticipated that dividends will be paid, they must be manually entered in row
61. They will be assumed to be paid in the period declared and will be automatically
copied to the Cash Flow Statement.

Cash Flow Statements
All operating cash inflows and outflows are lagged by the period shown in the
Assumptions sheet as discussed in the relevant previous sections. Within outflows,
separate lag periods are permitted for COS and non-staff costs. Staff costs are
presumed to be paid at the point of incurrence.
Interest income (expense) is automatically calculated. The payment is presumed to be
paid or received within the relevant period. (Interest on borrowings in row 91 is the
same.)
Row 87 has provision for receipt of proceeds from the sale of fixed assets. It is
expected that this will not normally be presented within financial models. It has not
been factored in to the automated fixed asset calculations. Consequently, any
Applicant intending to use this would need to consider its impact on other parts of the
Model. Applicants should not to use this except in extreme circumstances. Income
tax paid is automatically entered from the previous year’s income tax calculation and
is assumed to be paid in the second quarter of the relevant year.

Row 96 requires manual entry if used. It relates to capital and debt raising and
repayment. To the extent that share capital is raised or borrowings are drawn down or
repaid, the Balance Sheet will be automatically updated. “Other” in row 99 is not
connected to any other cells and we would advise against using it in any but the most
exceptional circumstances. If it is used, it must be fully explained and Applicants
must ensure that the integrity of the remainder of the model is preserved.

Closing cash balance in row 104 is calculated automatically.

Balance Sheets
The Balance Sheet format is also quite simple and is only required at year ends up to
30 June 2006.
Column B starts with provision to enter an opening Balance Sheet. It is not necessary
to enter items in all the yellow cells shown. It would be permissible to presume an
absolute start up and have matching entries for cash and share capital only.


Higher Bandwidth Incentive Scheme (HiBIS) – Guide to the HiBIS Financial Plan Template
                                             10


Important note: Whenever entering a Balance Sheet, always ensure row 143 is
checked and that it equals zero. If you have difficulty reconciling, ensure that after
you have made any changes, you re-enter the formula in row 55 of the Profit & Loss
Statement. (Since it is calculated from a cash balance, it is an iterative formula.)

Following the initial balance sheet at 31 December 2003, unlike the Profit and Loss
and Cash Flow Statements, Balance Sheets need only be prepared at the end of the
year for the next three years.

Significantly fewer cells are auto-populated in the Balance Sheet than for the other
two statements. It is clear from the colour coding of the cells which are auto-
populated and which are not. There is no requirement to complete all cells shown in
yellow – only those that are relevant to explaining each Applicant’s intended business.
The only permanent requirement is for the Balance Sheet to balance. Indeed, the
whole Financial Model can be comprehensively completed and a Balance Sheet
generated automatically without having to use any of the available inputs within it.

To the extent that available rows are used (eg. Prepayments), a double entry will need
to be made to keep the Balance Sheet in balance – none of them are linked to any
other parts of the model. For that reason, they are not used unless absolutely
necessary and then the integrity of the Model needs to be rigorously tested.




Higher Bandwidth Incentive Scheme (HiBIS) – Guide to the HiBIS Financial Plan Template

								
To top