Reference no: EM132301539

Assessment: Case Study - Excel-based

Learning Outcomes

d) Apply technical knowledge and skills in creating information for the workplace using spreadsheets and relational databases
e) Communicate with IT professionals, stakeholders and user groups of information systems

Context:
The aim of this assessment is to assess the student's ability to create spreadsheets that can aid business problem solving and analysing results.

The spreadsheet is a powerful tool that has become entrenched in business processes worldwide. A working knowledge of Excel is vital for most office based professionals today.

Criteria
• Formulae, formatting and cell references
• Graphs and pivot tables
• Cost-benefit analysis recommendation

ASSESSMENT - Excel Case Study

Background:

Torrens Consulting Company is a privately owned, independent, wholly Australian operated leading specialised consultancy, providing a full range of management consulting services specialising in Human Resource Management, Executive Recruitment, Organisational Development, Organisational Psychology and Training and Development Services.

They are looking at changing their current consulting decision-making system to a new technology and would like to call it consulting business intelligence system. They are deciding whether to develop the system in-house or outsource the development.

Students need to create a cost-benefit analysis of the proposed new system using the spreadsheet.

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.

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.

Valuing each alternative by calculating NPVs facilitates comparison between proposals that exhibit different timing of their benefits and costs. Programmes with positive NPVs generally indicate an efficient use of the community's resources.

Where all projected costs and benefits are valued in real terms, they should be discounted by a real discount rate. This can be estimated approximately by subtracting the expected (or actual) inflation rate from the nominal discount rate. If nominal (current price) values are used for projected costs and benefits, they should be discounted by a nominal discount rate.

The discount rate can also be varied to test the sensitivity of the proposal to changes in this variable and, implicitly, to the phasing of costs and benefits. Sensitivity analysis is discussed in STEP 3 below.

The Internal Rate of Return (IRR) is typically presented as supplementary information to the NPV. The IRR is the discount rate that will result in a NPV of zero. The project's IRR needs to be above the benchmark discount rate for the project to be considered viable (financially or economically, depending on the nature of the analysis).

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.

Uncertainties, or situations with unknown probabilities, that could have a significant impact on the project outcome should be clearly detailed in the report and, if necessary, monitored during implementation. When dealing with uncertain data, the expected value should be used. The expected value is the weighted sum of the likely outcomes (each outcome having its own probability of occurring). In order to attempt to quantify the likely impact, a probability may be assigned to a particular variable where dealing with uncertain data. These probabilities are then used as weightings in order to derive an expected value.

For example, assume a proposal that has two possible outcomes. The probability of producing an NPV of $5 million is 60% and the probability of producing an NPV of $3 million is 40%. We can now work out the expected NPV (ENPV) as follows:

ENPV = (0.6 x $5m) + (0.4 x $3m) = $4.2m

The expected NPV in this situation is $4.2 million. However, such a single value may not fully convey the uncertainty associated with forecasting the outcome. Hence, it is generally appropriate to present the results as a range that includes the most likely results, as well as results in possible best and worst case scenarios.

General Instructions:
1) Create a cost-beneift 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.

Note: students are required to input their own data.

Detailed instructions:

Note that the values in the tables provided are randomly added and may show incorrect values if formula is applied.
1. Create an Excel workbook with 8 worksheets (tabs): costs for in-house development, benefits of in-house development, costs for outsource development, benefits of outsource development, summary (inhouse and outsource), pivot table, and graphs, comparison and recommendation.

2. First workbook contains all the costs for in-house development. You will have two tables: First table computes the team rate, second table computes for the project total cost.

a. Create the project team rate table

Note: You have to enter values for the low, medium, high and selected (for the selected, you can choose from the values you entered for low, medium or high - does not have to be the same as high values from the high column)

Project Team Rate

3. Second Worksheet- Create the cost of in-house development table. Your spreadsheet should look like this (note that students are required to input their own cost data except for the project team salary which is based on the total team rate per day * 200 working days):

4. Third Worksheet- 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 Beneifts by year is the sum of all benefit sources per year (you need to use the formula to compute for this).
• Enhanced Revenues starts on year 4 and increases by 10% every year.
• 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)

5. The fourth worksheet contains outsourced cost and it follows the same steps and as Step 3. The only difference is that there is no project team rate computation table and the items for the costs are different. The fourth workbook contains the outsourced benefits and it follows the same step as step 3. In addition, the items for costs and benefits are different.

6. The fifth worksheet contains the benefits table for outsourced. The step is the same as step 4 except the items are different.

7. The sixth worksheet contains the summary of the cost-benefit analysis for in-house and the cost benefit for outsourced. You need to create two tables (one for inhouse and one for outsourced:

8. The last worksheet contains graphs and recommendation:
• Create the graphs for the in-house and outsourced discounted cash flows:
Example graph shown below (note that you can decide what is the best graph to use to represent your data, use appropriate graphs):

• Create the payback graph for both in-house and outsourced (you can choose any chart you want, choose the most appropriate chart)

Attachment:- Accounting Information Systems.rar