Six Mile Creek Telecentre Background: Pretend you live at Six Mile Creek rural community in the outback and you are planning to start up a telecentre as a small business to serve the local community. The proposed telecentre will provide Internet access as well as a photocopy service. You have done some research and found that there is demand for these services in the community and that the business can be profitable. But in order to determine how profitable the business will be, you need to carry out a cost-benefit analysis. Your cost-benefit analysis will be about comparing anticipated costs with anticipated benefits of the telecentre’s operation over a period of 7 years. In this assignment you will:
• carry out two cost-benefit analysis exercises using Microsoft Excel and then
• write a brief report using Miscrosoft Word to explain the results of your analysis.
Thus this assignment requires you to produce 2 files: an Excel spreadsheet and a Word document. PART A: Cost-Benefit Analysis You will carry out 2 cost-benefit analysis exercises:
• A Payback analysis
• A Return on Investment (ROI) analysis
The Payback analysis will determine how long it will take for the telecentre to pay for itself – this period is referred to as the payback period. For this, you will need to estimate the initial development costs, the annual operation costs and the annual benefits. The initial development costs, monthly operation costs and monthly benefits have been supplied for you below. The Return on Investment (ROI) analysis calculates profit as a percentage of the total costs over the analysis period. The formula for calculating ROI is: ROI = ((total benefits – total costs) / total costs) * 100 COSTS: The anticipated costs for the telecentre are grouped into 2 categories: Development Costs and Operating costs. 1. Development costs 1.1 One-off costs (these costs are incurred only once) Office Renovation @ \$40,000 Furniture costs @ \$15,000 System Installation costs @ \$10,000 1.2 Equipment and software costs You have decided to purchase:
• 20 desktop computers @ \$1,500 each
• 2 Servers @ \$4,000 each
• 1 system backup set @ \$3,000
• 2 printers @ \$800 each
• 2 heavy duty photocopiers @ \$3,000 each
• Microsoft Open Licenses (for operating system and Office suite) @ \$2,000
It is anticipated that the physical hardware equipment will be replaced every 4 years. After 4 years the price of these equipment items is expected to decrease by 20%. Software license fees remain the same price over the analysis period but they have to be renewed every 2 years. 2. Operating costs 2.1 Fixed costs (costs that are relatively constant and not dependent on level of use) Office Space Rental @ \$500/month Internet Service Provider (ISP) charges @ \$100/month Systems Administrator Salary @500/month Manager Salary @ \$800/month Salaries are expected to increase by \$50 every year 2.2 Variable costs (these vary depending on level of use but assume the following average values) Electricity @ \$200/month Maintenance costs @ \$400/month
3
Telephone costs @ \$300/month Supplies (paper, toner, etc) @ 300/month Assume that the monthly rates will remain the same over the period of the analysis
BENEFITS
Benefits of the telecentre operations will come from providing Internet Access to customers as well as providing a photocopying service. 1. Internet Access service Each desktop computer can fetch \$350/month 2. Photocopying Service Photocopying service is expected to fetch \$500 a month Your tasks: The Spreadsheet Both Payback analysis and ROI will be done on the same spreadsheet as they will use the same data set. Your spreadsheet should contain 4 sheets appropriately named: E.g., Development Costs, Operating costs, Benefits and Cost Benefit Analysis. The idea is to store sets of related data in one place (sheet) so that they can be manipulated independently. As well, you need to store hardware and software details, monthly rates for operating costs and benefit details (the data supplied to you above) in the appropriate sheets. Storing these details will enable you to:
• Use formulae to generate other data needed in the sheets.
• Make changes to details only in one place whenever there is a change.
For instance by storing the monthly Internet Service charge in one cell, if there is an increase in the rate then you need only change the rate in that cell and the change will cascade to all other cells whose values depend on this rate (because all sheets are linked by formulae). In this spreadsheet exercise, you should make use of the following Excel features:
• Use a spreadsheet containing more than one sheet
• Use appropriate formulae to generate needed data
• Know how to reference and copy a value from a cell in one sheet to a cell in another sheet