Reference no: EM131220733

1. Apply technical knowledge and skills in creating information for the workplace using spreadsheets and relational databases.

2. Communicate with IT professionals, stakeholders and user groups of information systems.

Cost-Benefit Analysis Overview:

Conducting a Cost-Benefit Analysis

While it is important to provide decision-makers with a range of options, the process of developing and analysing these can be expensive and time consuming. For major investments, it may be necessary to outline various potential options and then to have decision-makers select, after a preliminary screening, a smaller number for detailed appraisal. In any case, an appropriate level of consultation should be undertaken as best practice, either formally or informally, in creating a set of alternatives.

Step 1: Identify, quantify and value the costs and benefits of each alternative

A critical step in the CBA process involves identifying, quantifying and valuing the costs and benefits of each alternative. The types of benefits and costs will depend on the project. To illustrate, consider the construction of a toll motorway to relieve traffic congestion. Relevant costs would include the labour, capital and material costs to construct the road and the value of the land as reflected in the loss of the use of the land for alternative purposes. Benefits of the motorway would include lives saved, reduced travel time (which generally results in fuel and productivity benefits) and possibly the reduction of traffic on alternative routes, including the impact on inlet and outlet roads.

Typical costs of a proposal would include:

- Initial capital costs;
- capital costs of any buildings, equipment, or facilities that need to be replaced during the life of the project;
- operating and maintenance costs over the period of a programme or project; and
- costs which cannot be valued in money terms (often described as 'intangibles').

Typical benefits of a proposal would include:

- benefits which can be valued in money terms, in the form of revenues, cost savings or non-market outputs; and
- benefits which cannot be valued in money terms (also described as ‘intangibles').

Estimating the magnitude of costs can be difficult and will normally involve input from accountants, economists and other specialists.

Step 2: Calculate the Net Present Value

In CBA, the net social benefit (NSB), or the excess of total benefit over total cost, is represented by the net present value (NPV) of the proposal.

Before determining the value (or NPV) of a proposal, the costs (C) and benefits (B) need to be quantified for the expected duration of the project. The NSB is calculated by subtracting the cost stream from the benefit stream and is represented as follows:

NSB = B - C

The NPV of a proposal is determined by applying a ‘discount rate' (discussed below) to the identified costs and benefits. It is necessary to ‘discount' costs and benefits occurring later relative to those occurring sooner. This is because money received now can be invested and converted into a larger future amount and because people generally prefer to receive income now rather than in the future.

Step 3: Sensitivity analysis and dealing with uncertainty

The values of future costs and benefits on which the NPV is based are forecasts that cannot be known with certainty. While they should be forecast expected values, it is important to test the NPV for ‘optimistic' and ‘pessimistic' scenarios. This is achieved by changing the values of key variables in the analysis, such as the discount rate, costs and benefits, and measuring the impact of the changes on the NPV. This is known as sensitivity analysis and is a critical component of any CBA.

Where the NPV is shown to be very sensitive to changes in a variable, the analyst should check on the appropriateness and impact of this variable, and whether any changes to the design of the programme or underlying assumptions are warranted.

ASSESSMENT 3 - EXCEL SPREADSHEET
General Instructions:

1. Create a cost-benefit analysis spreadsheet for both in-house and outsourced development:
- Create s spreadsheet, format and use formulas to identify the cost-benefit analysis for alternatives.
- Visually show comparison by using graphs and charts.
- Give recommendations on which alternative is more beneficial to the organisation.

Detailed instructions:

1. Create an Excel workbook with 9 worksheets (tabs): costs for in-house development, benefits of in-house development, summary (in-house), payback period (in-house), costs for outsource development, benefits of outsource development, summary (outsource), payback period (outsource), comparison and recommendation
a. First workbook contains all the costs for in-house development. Your spreadsheet should look like this (note that students are required to input their own cost data):

Note:

- You need to enter data for the cost of each item. The values shown above are just examples.
- The first fiscal year is entered - rest of the fiscal year is computed by adding a year (for example, if year 2016 is entered in the first fiscal year, the rest of the four years will automatically be computer by adding a year from the previous year).
- Project total costs by year is the sum of all cost items per year (you need to use the formula to compute for this).
- Project total cost is the total money you need to spend on your project.
- You need to format your tables (you can design it the way you want). Make sure that appropriate formats are used (e.g. date format for dates, percentage formats or money formats)

b. Second workbook contains the benefits of in-house development:

Note:

- You need to enter data for the benefit value. The values shown above are just examples.
- Fiscal year for Benefit sources is referenced to the first fiscal year in cost (if the year in the cost changes, the fiscal years in benefit sources automatically change too). Fiscal year for the 2nd to 5th year are automatically computed based on the year in the first fiscal year (one year is added on the previous year).
- Total Benefits by year is the sum of all benefit sources per year (you need to use the formula to compute for this).
- Benefits Claimed for Analysis is computed using the following formula: total benefits per year * confidence factor.
- Project Grand Total Benefit is the total benefits for 5 years.
- You need to format your tables (you can design it the way you want). Make sure that appropriate formats are used (e.g. date format for dates, percentage formats and money formats)

c. Third workbook contains the summary of the cost-benefit analysis for in-house