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 Module 4.1 (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.
INFORMATION TECHNOLOGY PROPOSAL
Global Athletic Apparel Manufacturer’s (GAAM) 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, Mr. John Norton, is yet to decide whether to have the system developed in-house or outsource the development. A cost-benefit analysis is needed to assist him in making this decision.
A cost-benefit analysis used to evaluate the total anticipated cost of a project compared to the total expected benefits in order to determine whether the proposed implementation is worthwhile for the company or project team. (brighthubpm.com)
GAAM needs a recommendation whether to develop the system in-house or outsourced based on the results of the cost-benefit analysis for 5 years.
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.
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.
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.
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.
ASSESSMENT 3 – EXCEL SPREADSHEET
General Instructions:
1. Create a cost-benefit 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:
1. Create an Excel workbook with 9 worksheets (tabs): costs for in-house development, benefits of in-house development, summary (in-house), payback period (in-house), costs for outsource development, benefits of outsource development, summary (outsource), payback period
(outsource), comparison and recommendation
a. First 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):
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). Make sure that appropriate formats are used (e.g. date format for dates, percentage formats or money formats)
b. Second 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.
• 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 Benefits 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)
c. Third workbook 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 2nd to 5th year are automatically computed based on the year in the first fiscal year (one year is added on the previous year).
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)
d. Your outsourced cost, benefits and summary sheets should follow the same steps and as steps a-c. The only difference is the items for costs and benefits are different.
The following are the items for cost and benefits:
Costs Benefit Sources
Hardware
Software
Software Development
Telecommunications
Training
Maintenance and Consulting Fee
Cost Reduction (courier and returned goods)
Enhanced Revenues
Cost reduction on employees salary
Expertise outsourced
e. 9th worksheet contains the 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):
• Create the payback graph for both in-house and outsourced (you can choose any chart you want), Example:
• Last graph is to compare the cost-benefit of in-house vs outsourced (choose any representation you want). Identify the data that you want to compare. For example, if you want to just compare the net present value then you can show a graph like this:
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.
Note that in the recommendation part using graphs, it is up to you to decide what data you want to present to make the recommendation. Once you have created all your graphs, write your recommendation on the worksheet and justify.
Note:
In assessment 1, students were asked to analyse the organisation’s business processes and propose an improved business process.
Using the same organisation’s client brief, students will be asked to create a cost-benefit analysis of the proposed Information Systems project using the spreadsheet.
Students are required to input their own unique data.
Outcome to be submitted:
Students need to submit their Excel Spreadsheet.
Criteria
• Formulae, formatting and cell references
• Graphs and pivot tables
• Cost-benefit analysis recommendation
Learning Rubrics
Assessment Attributes Fail
0-49% Pass 50- 64% Credit
65-74% Distinction 75-84% High Distinction 85-100%
Formulae, formatting and cell references
40% Formulae, formatting and cell references are mostly incorrect and incomplete.
Some data have been added.
More than 50% of the formulae, formatting and cell references are correct.
Most data have been added.
More than 75 % of the formulae, formatting and cell references are correct.
All data have been added.
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.
All formulae, formatting and cell references are correct (No errors).
Cells are excellently formatted and easy to understand.
All data have been entered correctly.
Graphs and pivot table
40% No graphs nor pivot table or very poor implementation of
graphs and pivot table
Graphs and/or pivot table are created with some errors (around 50%).
Graphs created are simple.
Graphs and pivot table are created with minor errors
(around 3-4 errors)
Graphs and pivot table created are simple.
Graphs and pivot table are
created with 1-2 errors
Graphs and pivot table created have some design and readable.
Graphs and pivot table are created with no errors.
Excellent presentation of graphs and pivot table.
Cost-benefit analysis recommendation
20% Recommendations and analysis are not provided or incorrect.
Recommendations are provided but analysis was very shallow.
Recommendations are provided but and some detailed analysis is provided.
Recommendations are provided and analysis is detailed.
Thorough explanation of the recommendations are provided and in-depth analysis is presented.
Excel Spreadsheetv2.docx Page 16 of