Your task is to develop a spreadsheet, which will allow an investor to determine if the business plan for a new Internet Service Provider, ISP, is reasonable. Basically you are to develop a financial forecast spreadsheet based on the business plan.
Below you will find a list of costs and revenues associated with running an ISP. Some costs are fixed cost and some costs are variable costs. Fixed costs remain constant regardless of how many customers subscribe to the service. Variable cost will change with the number of subscribers.
Note that numerous simplifying assumptions have been build into the cost and revenue list. Some assumptions that are not explicitly stated in the cost list are:
- equipment and software will last forever and will not need to be upgraded or depreciated
- there is no inflation, costs will remain the same forever
- growth figures will remain constant
- capacity will be added as soon as it is needed, the service will under utilize equipment and personnel rather than run at over capacity
A main purpose of your spreadsheet is to allow the ISP to determine how much money the service should borrow each month to insure that it will have enough cash to meet its costs until it becomes profitable. Your spreadsheet must include a cash flow analysis that shows the ending cash for each period.
When calculating your cash flows keep in mind the following:
- Revenues add to profits and are positive cash flows
- Expenses decrease profits and are negative cash flows
- Loan proceeds do not change profits and are positive cash flows
- Loan payments do not change profits and are negative cash flows
- Loan interest expenses decrease profits and are negative cash flows
Note that all cost, revenue, and growth figures are estimates. The spreadsheet needs to be designed so that the user can easily run "what if" scenarios. This means that all assumed and estimated values must be clearly presented as user inputs. Calculated values must be protected from inadvertent alteration by the user.
The spreadsheet should include associated graphs that show:
- sales revenue over time
- loan value over time
- profit/loss over time
- You should have a separate graph for each series
- the graphs should be available in monthly, quarterly, and annual versions
- each version should show the required series on the y-axis and the time periods on the x-axis.
In order to make a valid decision the investor needs the forecast to cover a ten-year period.
Revenue considerations for the ISP include the following:
- all of the revenues will come from dial up customer subscription
- the plan is to provide unlimited access for an estimated $20/mo
- each customer is charged a startup fee, current estimate for this fee is $25
- one time startup costs associated with adding a new customer are estimated at $30
- the service will have an estimated 400 customers in the first month, startup costs and fees will apply to these customers
- customer growth is estimated to grow at a rate of 8% per month
- it is estimated that the market share of the target population area will never exceed 5%.
- The currently target population size is estimated at 75,000.
- The size of the target population is estimated to be growing at a rate of 3% per year.
Cost considerations for the ISP include the following:
Manager/owner salary
- the owner of the business will need to give up his or her current job and devote their full attention to running the business
- the owner will handle all sales activities
- starting estimate is $36,000/yr
Technical specialist salary
- the technical specialist will handle hardware and software problems that occur during the initial installation and when adding new equipment during expansion
- develop standard operating practices for technical support personal
- starting estimate is $24,000/yr
Technical support salary
- success of an ISP often depends on the quality of the technical support lines available to the customers
- the initial plan is to provide 24/hr support lines 7 days per week, this coverage may need to be reduced
- coverage will always be indicated in hours per day and days per week
- for example the user may want to try 12/hr support 5 days per week.
- lines will be staffed by college students
- initial estimate of starting pay are $8.50/hr
- initial estimate is that the service will need one (1) support person coverage for every 500 customers
- this means that for every 500 customers you will need one (1) person during the times that the technical support lines are in operation.
- your spreadsheet will need to use the correct number of days for each month.
- the user will specify the starting month and year.
-
the initial plan is to begin
operations on
- this start date may need to change
- you may assume that operations will always begin on the first day of the month
Advertising costs
- to attract customers the service will need to advertise
- initial estimates for advertising costs are $600/mo
Equipment costs
- Telecommunications costs
- Internet access
- the system will need to connect to a national ISP or to a larger local ISP
- initial estimates are for one (1) T1 line for each 500 customers
- initial estimates for a T1 connection are $1500/connection/mo
- assume this charge includes both channel carrier charges and the national ISP access charge
- the plan is to start with a single T1 line and add lines as soon as they are needed
- Customer access
- enough modems will be needed so that customers can dial in without experiencing frequent busy signals
- initial estimates are one (1) modem for every 10 customers
- the plan is to buy modems and their associated equipment in banks (groups) of 30 modems
- initial cost estimates for a bank of 30 modems is $2,700
- a phone line for each modem is necessary
- initial cost estimate for a business phone line is $20/mo
- there is no installation fee for new phone lines
- 30 phone lines are needed for each bank
- modems will be added in groups of 30 but phone lines will be added one at a time
- Server costs
- the service will need several computers, their networking hardware, and associated software to set up the ISP
- initial equipment cost is estimated at $25,000
- initial estimates are that the equipment can support 5,000 customers
- after 5,000 customers it will need to add additional servers to the system
- each additional server is estimated to cost $3,000
- each additional server is estimated to be able to support 1,000 additional customers
Rent
- a secure location for equipment and offices is necessary
- the office should locate near the phone company (or the channel provider) to insure availability of phone lines
- the space rented should be sufficient to house the business regardless of the number of customers
- initial estimate is that the rent will be $1,500/mo
Utilities
- utility cost will remain constant regardless of how the number of customers
- estimated base utility cost is $350/mo, excluding costs listed below
- each server add $40/mo to the utility bill
- each modem bank adds $35/mo to the utility bill
Loan costs
- the plan is to borrow enough money each month to cover any cash shortages
- the initial estimate is that money can be borrowed at a fixed interest rate of 10%/year
- an open line of credit has been secured
- any cash available at the end of a month should be used to pay off any outstanding loan balance.
Note that the above figures may or may not result in profitable business. You spreadsheet must be designed so that it is easy to change the estimated figures and obvious where and how the changes should be made. You are free to develop your own design for your workbook provided it meets the following criteria:
- Your worksheets need to be laid out so that the time periods are represented in columns and the revenue and cost figures are represent in the rows.
- Each graph should appear on its own separate worksheet.
Keep in mind the following:
- You are developing this workbook for someone else to use.
- Workbook navigation should be obvious. The user should not have to “hunt” for items or feel lost.
- You cannot count on the user’s spreadsheet knowledge. The user will know how to move about a workbook and how to enter values into cells. The user will NOT know how to adjust a formula.
- Part of your grade will be based on your spreadsheet design.
Terms
of the assignment:
- This is a group project.
- Maximum group size is three (3) members. The minimum size is two (2) members.
- You will turn in the following:
o Your Excel spreadsheet file.
o Your Excel file must be named using the filename conventions, see below.
- The assignment prefix is ss2
- Your spreadsheet file must be sent to me as an email attachment.
- The subject line on the email message must be: ss2 submission
- The assignment is due by Wednesday, April 21, 2004.
- A 10% late penalty will be applied for each day the assignment is late.
Filename
Conventions:
The filename conventions are the same as those used in previous assignments.