Accounting Information Systems

ACCT6001 Assessment 3– Excel Spreadsheet Page 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
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.
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 Page 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
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.
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
represented by the net present value (NPV) of the proposal.

ACCT6001 Assessment 3– Excel Spreadsheet Page 3 of 14
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.
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 (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.

ACCT6001 Assessment 3– Excel Spreadsheet Page 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 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.
Reference: Mishan’s Cost-Bene t Analysis (1982, pp 221-224) provides 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 Page 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 (inhouse), 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 E
i is defined as:
E
i=(A+4M+B)/6
b) 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 correct
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 Page 6 of 14
Person days/year = assuming 200 workdays available per year

Computation Expected time
Expected time = Ei/200 = 1800/200 9 person years
90% Prob. Range = (1484/200 … (2116/200)

 

7.4 person year .. 10.6 person years

3. Create the project team rate spreadsheet
It should look like this:
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 referencing for
the values in the per hour column. Per day is computed by multiplying the per hour to 8 (hours).

Cost Units
Units per hour per days
Architect/Designer 50 400
Developer (Blended rate for
Senior and Junior Developer)
30 240
Testing Lead 50 400
Tester 30 240
Technical Writing Cost 30 240
Manager 70 560
On-site Manager 150 1200
TOTAL 3280

ACCT6001 Assessment 3– Excel Spreadsheet Page 7 of 14
4. Next 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 except for the
project team salary; the project team salary will be based on the total cost from the project
team rate spreadsheet.
The value for the project team salary starts on the third year and
depreciates by 15% per year
.

Fiscal Year
Cost Items 2016 2017 2018 2019 2020
Hardware $300,000 $250,000 $250,000
Software $200,000 $150,000 $150,000
Project Team Salary $300,000 $250,000 $250,000
Telecommunications $400,000 $450,000 $500,000
Training $150,000 $150,000 $150,000
Operations and Contingencies $400,000 $400,000 $400,000
Project Total Costs By Year $700,000 $650,000 $1,700,000 $1,000,000 $1,050,000
PROJECT TOTAL COST $5,100,000

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, just make it look
more professional). Make sure that appropriate formats are used (e.g. date format for
dates, percentage formats or money formats)

ACCT6001 Assessment 3– Excel Spreadsheet Page 8 of 14
5. Next workbook contains the benefits of in-house development:

Fiscal Year
Benefit Sources 2016 2017 2018 2019 2020
Cost Reduction (courier and returned
goods)
$500,000 $525,000 $550,000
Enhanced Revenues $250,000 $350,000
Decreased Employee Overtime $100,000 $100,000
Decreased Overhead $50,000 $50,000 $50,000
Total Benefits Per Year $0 $0 $550,000 $925,000 $1,050,000
Confidence Factor 100% 100% 100% 100% 100%
Benefits Claimed for Analysis $0 $0 $550,000 $925,000 $1,050,000
Project Grand Total Benefit $2,525,000

Note:
You need to enter data for the benefit value. The values shown above are just examples.
Make sure you use formulas when appropriate.
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
2
nd to 5th year are automatically computed based on the year in the first fiscal year (one year
is added on the previous year).
Cost reduction will start having the benefit on the third year and increases 5% every year
Enhanced revenue can start on the third year (2018) and it increases 40% per year
Total Beneifts 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)

ACCT6001 Assessment 3– Excel Spreadsheet Page 9 of 14
6.
Create a workbook that contains the summary of the cost-benefit analysis for in-house

Fiscal Year
2016 2017 2018 2019 2020
Undiscounted Flows
Costs $700,000 $650,000 $1,700,000 $1,000,000 $1,050,000
Benefits $0 $0 $550,000 $925,000 $1,050,000
Net Cash Flow -$700,000 -$650,000 -$1,150,000 -$75,000 $0
Discount Factors
Discount Rate 7.0%
Base Year 2016
Year Index 0 1 2 3 4
Discount Factor 1.0000 0.9346 0.8734 0.8163 0.7629
Discounted Flows
Costs -$700,000 -$607,477 -$1,484,846 -$816,298 -$801,040
Benefits $0 $0 $480,391 $755,076 $801,040
Net -$700,000 -$607,477 -$1,004,455 -$61,222 $0
Cumulative -$700,000 -$1,307,477 -$2,311,931 -$2,373,154 -$2,373,154
Net Present Value ($2,373,154)
Internal Rate of Return 21%

Note:
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 2
nd to 5th
year are automatically computed based on the year in the first fiscal year (one year is added on the
previous year).

ACCT6001 Assessment 3– Excel Spreadsheet Page 10 of 14
Values for yearly cost and benefits
are referenced from the yearly cost and benefits (from cost table
and benefits table)
Net cash flow is computed by subtracting the cost from benefits.
Enter the value Discount rate – this is the cost of money that determines the time value of you costs
and benefits (example if you are working with an interest-free loan, this would be zero; a typical
value is around 8%).
Base year is the current year; the year you want the future perspectives is computed
Year index is computed using the following formula: fiscal year – base year
Discount factor for each year
is how much less the cash flows are worth because they are in the
future. It is computed using the following formula: 1/((1+Discount Rate)^year index)
Costs for discounted flows cash per year is computed using the following formula: (-1) *
undiscounted cost * discount factor
Benefits for discounted flows
per year is computed using the following formula: undiscounted
benefits * discount factor
Net
is the sum of cost for discount flows and benefits for discounted flows.
Cumulative net value is the cumulative net value so far (example 2016 value is the same as the net
value; 2017 value is cumulative value of 2016 +2017 net value and so on).
When the cumulative value becomes positive, you have completed your
payback period.
Net Present Value
is computed by using the NPV formula of following are the items for cost: =
NPV(discount rate, net cash flow from 2017-2020,) + net cash flow for 2016
Internal Rate of Return
is computed using the IRR formula of excel: = IRR(net cash flow from 2016-
2020, 0.1)
7. Create a worksheet that contains the graphs and recommendation:
Create different graphs from the worksheets you created. Select several graphs that you
think will be needed to make a valid analysis of the project.
Example graph shown below (
note that you can decide what is the best graph to use to
represent your data):

ACCT6001 Assessment 3– Excel Spreadsheet Page 11 of 14
payback graph – indicate when the payback period is going to be
Example:
(600,000.00)
(1,307,476.64)
(2,311,931.17) (2,373,153.51)
52,853.26
2016 2017 2018 2019 2020
Payback period (in-house)
ACCT6001 Assessment 3– Excel Spreadsheet Page 12 of 14
If you want to include the IRR as part of the comparison, either you create a separate graph or
combine it with the graph above.
Finally, make your recommendation. Make sure that you analyse all aspects of the project.
Analyse all the data in your different worksheet. Use pivot tables to make your analysis more
detailed.
Submission Requirement:
Students need to submit their Excel Spreadsheet.
Criteria
Formulae, formatting and cell references
Graphs and pivot tables
Cost-benefit analysis recommendation
$3,101,713
($2,250,909)
($3,000,000)
($2,000,000)
($1,000,000)
$0
$1,000,000
$2,000,000
$3,000,000
$4,000,000
Net Present Value Inhouse Net Present Value Outsourced
Net Present Value
ACCT6001 Assessment 3- Excel Spreadsheet Page 13 of 14
Learning Rubrics

Assessment Attributes Fail
(Unacceptable)
Pass
(Functional)
Credit
(Proficient)
Distinction
(Advanced)
High Distinction
(Exceptional)
Points
Formulae, formatting
and cell references
40%
Formulae, formatting
and cell references are
mostly incorrect and
incomplete.
Some data have been
added.
Points 0-14
More than 50% of the
formulae, formatting and
cell references are correct.
Most data have been
added.
Points 15-20
More than 75 % of the
formulae, formatting and
cell references are correct.
All data have been added.
Points 21-30
Almost all formulae, formatting
and cell references are correct.
Very few errors (around 1 -2
errors)
Cells are well formatted.
All data have been entered
correctly.
Points 31-35
All formulae, formatting and
cell references are correct
(No errors).
Cells are excellently formatted
and easy to understand.
All data have been entered
correctly.
Points 36-40
Graphs and pivot table
40%
No graphs nor pivot table
or very poor
implementation of
graphs and pivot table
Points 0-19
Graphs and/or pivot table
are created with some
errors (around 50%).
Graphs created are simple.
Points -20-25
Graphs and pivot table are
created with minor errors
(around 3-4 errors)
Graphs and pivot table
created are simple.
Points 26-30
Graphs and pivot table are
created with 1-2 errors
Graphs and pivot table created
have some design and
readable.
Points 31-35
Graphs and pivot table are
created with no errors.
Excellent presentation of
graphs and pivot table.
Points 36-40
Cost-benefit analysis
recommendation
20%
Recommendations and
analysis are not provided
or incorrect.
Points 0-9
Recommendations are
provided but analysis was
very shallow.
Points 10-12
Recommendations are
provided but and some
detailed analysis is provided.
Points : 12-15
Recommendations are provided
and analysis is detailed.
Points 16-18
Thorough explanation of the
recommendations are
provided and in-depth analysis
is presented.
Points 19-20

ACCT6001 Assessment 3- Excel Spreadsheet Page 14 of 14