ASSESSMENT BRIEF
Subject Code and Title ACCT6001 Accounting Information Systems
Assessment Assessment 3: Case Study – Excel-based
Individual/Group Individual
Length
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
Submission By 11:55pm AEST/AEDT Sunday end of week
7/Module4.1
For intensive mode: By 11:55pm AEST/AEDT Sunday
end of week 4/Module4.2
Weighting 25%
Total Marks 100 marks
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.
Submission Requirement:
Students need to submit their Excel Spreadsheet. The analysis and recommendation can be
placed in the Excel worksheet. Note that your lecturer will provide you the case study for
this assessment by week 4.
Criteria
Formulae, formatting and cell references
Graphs and pivot tables
Cost-benefit analysis recommendation
ASSESSMENT 3 – Excel Case Study
Background:
To
ens 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 cu
ent 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
eplaced 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.
16
Step 2: Calculate the Net Present Value
In CBA, the net social benefit (NSB), or the excess of total benefit over total cost, is
epresented 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
y 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
occu
ing later relative to those occu
ing 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.
The NPV is calculated as follows:
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 (cu
ent price) values are used for projected costs and benefits, they should
e 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
y 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 wa
anted.
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 occu
ing). 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 XXXXXXXXXXx $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
esults, as well as results in possible best and worst case scenarios.
Reference: Mishan’s Cost-Bene t Analysis (1982, pp XXXXXXXXXXprovides a detailed explanation of the IRR, describes how
to measure it, and provides an example to illustrate. See also Department of Finance and Administration the Handbook
of CBA (2005).
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
inco
ect 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
It should look like this:
Project Team Rate
Units Low Medium High Chosen (Low)
Analysts XXXXXXXXXX
Software Arthictect XXXXXXXXXX
Junioe Developer XXXXXXXXXX
Senior Developer XXXXXXXXXX
Testing Lead XXXXXXXXXX
Tester XXXXXXXXXX
On-site Manager XXXXXXXXXX
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)
Now you need to compute for the cost per hour and per day.
Note that the value for the per hour is based on the selected values column. Used
eferencing for the values in the per hour column. Per day is computed by multiplying the
per hour to 8 (hours).
Project Team Rate
Units per hour per days
Analysts 25 200
Software Arthictect 25 200
Junioe Developer 30 240
Senior Developer 50 400
Testing Lead 30 240
Tester 30 200
On-site Manager XXXXXXXXXX
TOTAL TEAM RATE PER DAY 2400
TOTAL TEAM RATE (YEAR XXXXXXXXXX
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):
Note:
You need