ACCT6001 Assessment 3– Excel Spreadsheet XXXXXXXXXXPage 1 of 14
ASSESSMENT BRIEF
Subject Code and Title ACCT6001 Accounting Information Systems
Assessment Assessment 3: Excel Spreadsheet
Individual/Group Individual
Length
Learning Outcomes 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.
Submission By 11:55pm AEST/AEDT Sunday end of week 7
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
usiness 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.
CASE STUDY
INFORMATION TECHNOLOGY PROPOSAL
Global Athletic Apparel Manufacturer is a manufacturing company. Its external consultant suggested
that the company should have an inventory and sales database management system to help the
company monitor its sales and products. GAAM’s Chief Information Officer, has decided to develop
the software in-house.
The project manager has assigned you to do conduct a feasibility study and cost estimates for the
software development project. You are also asked to evaluate the cost-benefit analysis for the
project.
ACCT6001 Assessment 3– Excel Spreadsheet XXXXXXXXXXPage 2 of 14
You need to present the results of your analysis and make recommendations on whether to
continue with the development or not.
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
enefits 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
esults in fuel and productivity benefits) and possibly the reduction of traffic on alternative
outes, 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.
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.
ACCT6001 Assessment 3– Excel Spreadsheet XXXXXXXXXXPage 3 of 14
Before determining the value (or NPV) of a proposal, the costs (C) and benefits (B) need to
e 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 occu
ing later
elative 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
y 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 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
e 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
e 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.
ACCT6001 Assessment 3– Excel Spreadsheet XXXXXXXXXXPage 4 of 14
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 results, as well as
esults 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).
ACCT6001 Assessment 3– Excel Spreadsheet XXXXXXXXXXPage 5 of 14
Detailed instructions:
1. Create an Excel workbook with the following tabs: development time, costs for in-house
development, benefits of in-house development, summary (in-house), payback period (in-
house), comparison and recommendation.
2. Create a development time spreadsheet
a) The expected value of the work time required for a project is calculated as a weighted
average of the optimistic (A), most likely (M) and pessimistic (B). For example, expected
value Ei is defined as:
Ei=(A+4M+B)/6
) The development time spreadsheet should contain the following data (note you need to
format the table and results to make it more professional looking):
Note: the 90% probability range is the probability that the estimation is co
ect
Now that you had the estimated work time (Ei) and also the 90% probability range, you can now
compute for the Expected time needed in the development. Here is an explanation on how you
compute for the expected time:
ACCT6001 Assessment 3– Excel Spreadsheet XXXXXXXXXXPage 6 of 14
• Person days/year = assuming 200 workdays available