Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

ACCT6001 Assessment 3– Excel Spreadsheet 22/03/18 XXXXXXXXXXPage 1 of 14 ASSESSMENT BRIEF Subject Code and Title ACCT6001 Accounting Information Systems Assessment Assessment 3: Case Study –...

1 answer below »

ACCT6001 Assessment 3– Excel Spreadsheet 22/03/18 XXXXXXXXXXPage 1 of 14

Subject Code and Title ACCT6001 Accounting Information Systems
Assessment Assessment 3: Case Study – Excel-based
Individual/Group Individual
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
For intensive mode: By 11:55pm AEST/AEDT Sunday
end of week 4/Module4.2
Weighting 25%
Total Marks 100 marks
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.
Submission Requirement:
Students need to submit their Excel Spreadsheet. The analysis and recommendation can be placed
in the Excel worksheet. You will be provided a case study with detailed instructions.
• Formulae, formatting and cell references
• Graphs and pivot tables
• Cost-benefit analysis recommendation

ACCT6001 Assessment 3– Excel Spreadsheet 22/03/18 XXXXXXXXXXPage 2 of 14
ASSESSMENT 3 – Excel Case Study
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 buy off-the-shelf software.
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
enefits 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’).

ACCT6001 Assessment 3– Excel Spreadsheet 22/03/18 XXXXXXXXXXPage 3 of 14

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
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 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 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 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

ACCT6001 Assessment 3– Excel Spreadsheet 22/03/18 XXXXXXXXXXPage 4 of 14

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.
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
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).
General Instructions:
1) Create a cost-benefit analysis spreadsheet for both in-house development and buying off-
the-shelf package software:
• Create a spreadsheet, format and use formulas to identify the cost-benefit analysis for
• 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
values if formula is applied.

ACCT6001 Assessment 3– Excel Spreadsheet 22/03/18 XXXXXXXXXXPage 5 of 14

1. Create an Excel workbook with 8 worksheets (tabs): costs for in-house development,
enefits of in-house development, costs for buying off-the-shelf package, 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:
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
Answered Same Day Jul 23, 2020 ACCT6001 Torrens University Australia


Uk answered on Jul 24 2020
127 Votes
Cost of in-house decelopment-1
    Units    Low    Medium    High    Selected
    Architect/Designer    15    35    50    50
    Developer (Blended rate for Senior and Junior Developer)     10    20    35    35
    Testing Lead    25    30    45    30
    Tester    10    20    25    25
    Tachnical Writing Cost    15    25    35    25
    Manager    30    55    75    55
    On-site Manager    55    110    145    110
    Units    Per Hour    Per Days
    Architect/Designer    50    400
    Developer (Blended rate for Senior and Junior Developer)     35    280
    Testing Lead    30    240
    Tester    25    200
    Tachnical Writing Cost    25    200
    Manager    55    440
    On-site Manager    110    880
    Total Team Rate Per Day        2640
    Number of working hours in a day=    8
Cost for in-house development-2
    Cost for in-house development    2018    2019    2020    2021    2022
    Hardware    100,000.00
    Software    450,000.00
    Project Team Salary    528,000.00    475,200.00    426,900.00    384,210.00    345,789.00
    Telecommunications    16,300.00    19,800.00    21,000.00    25,690.00    28,900.00
    Training    15,000.00    10,000.00    7,500.00    5,800.00    2,400.00
    Overhead (Rent & others) & contingencies    15,000.00    14,000.00    13,800.00    13,600.00    13,500.00
    Project Total Costs by year    1,124,300.00    519,000.00    469,200.00    429,300.00    390,589.00
    PROJECT TOTAL COST ($)    2,932,389.00
    Number of working hours=    200
    Project team salary=     Total Team rate per day * 200 working hours
Benefits of in-house developmnt
    Benefit of in-house development    2018    2019    2020    2021    2022
    Cost reduction (outsourcing)    450,000.00    430,000.00    420,000.00    380,000.00    340,000.00
    Cost reduction (improvement of supply chain)        120,000.00    145,000.00    168,000.00    200,000.00
    Enhanced revenues                350,000.00    385,000.00
    Decreased extra fees (consulting)    20,000.00    18,500.00    17,000.00    15,300.00    12,000.00
    Projects Total Benefit by year    470,000.00    568,500.00    582,000.00    913,300.00    937,000.00
    Confidence factor    100%    80%    85%    95%    100%
    Benefits claimed for analysis    470,000.00    454,800.00    494,700.00    867,635.00    937,000.00
    PROJECT TOTAL BENEFIT ($)    3,224,135.00
Cost of buying off-the-shelf
    Cost of buying software    2018    2019    2020    2021    2022
    Initial buying cost    750,000.00    550,000.00    350,000.00    280,000.00    150,000.00
    Maintenance cost    35,000.00    40,000.00    43,000.00    48,000.00    48,000.00
    Customisation cost    125,000.00    155,000.00    135,000.00    140,000.00    150,000.00
    Total Cost    910,000.00    745,000.00    528,000.00    468,000.00    348,000.00
Benefits of outsource
    Benefit of outsource    2018    2019    2020    2021    2022
    Cost reduction (improvement of supply chain)        110,000.00    150,000.00    160,000.00    250,000.00
    Cost reduction on employees...

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here