ACC 202 Accounting Information Systems: Excel Assignment And Case Study Assessment Answer

pages Pages: 4word Words: 890

Question :

Assessment Information

Subject Code: ACC 202
Subject Name: Accounting Information Systems
Assessment Title: Excel Assignment and Case Study
Weighting: 20%
Total Marks: 80

Assessment Description

Learning Outcome 3: Demonstrate data analysis, data manipulation and information presentation skills through the use of a commercially available spreadsheet package.

Introduction to the individual assignment:

This assignment must be completed using Microsoft Excel. These introductory excel skills you learn will be used in later units and in your professional life. Employers expect students to have good worksheet skills.

Assignment Information:

The assignment consists of two questions:

  • Students are to formulate possible solutions in practical scenarios using appropriate software like Excel, with a range of formulae and functions for basic income, expense and profitability analysis.
  • A brief research report to explore the impact that emerging technologies like Business Intelligence, Cloud Computing can have on accounting and business decisions.

You should suitably embed the research report into the Excel file on a separate sheet. You will be submitting only ONE Excel file for this assignment as an electronic assignment via the ACC 202 subject page on the student portal. Instructions are on the front page of the portal. Ensure that your work can be clearly identified with your student ID and FULL student name. Late penalties will apply for all late submissions.

Any work which has been copied or shared between students will result in a Fail grade.

QUESTION 1 (60 marks)

The following employees work for Shy-Fi Contracts Ltd, a public company specialising in contract labour force for different projects in construction industry. The employees are paid an hourly rate, based on their Job Classification level. If an employee works more than 35 hours, they will be paid the overtime rate for the additional hours.

The following table summarises the classifications and level of pay per classification:


Classification Levels
LevelHourly pay

The overtime hourly rate is $ 80 per hour for ALL employees, regardless of their classification level.

Sky-Fi Contracts Ltd has the following employees:


Employee nameClassification Level
Paris Holton5
Ricky Mortini4
Jennifer Leepoz2
Selina Geemak2
Willard Smith1
Russell Creak6
Rafael Nooderly4
Novak Djoker1
Lara Bangle3
Kath Hudson6


Employee nameHours worked
Novak Djoker37
Willard Smith39
Lara Bangle40
Kath Hudson52
Selina Geemak30
Paris Holton45
Russell Creak34
Jennifer Leepoz41
Ricky Mortini52
Rafael Nooderly44

REQUIRED: One worksheet must be used to provide answers to this question.

  • No marks will be awarded if the correct function or formula is not used or if data has been manually entered.

  • Take care regarding professional presentation of your work at every step. All dollar amounts must be provided in currency format and 2 decimal places.


  1. Copy Table A into your worksheet. Set up a ‘range’ for the data. Name the range ‘classification level’
  2. Copy Table B to the same worksheet.
    • Add a third column titled ‘hourly pay’. Use the VLOOKUP function to pick up the correct hourly pay rate for each employee from the ‘classification level’ range set up above.
    • Create a ‘range’ for the data in these 3 columns. Name the range ‘pay rate table’. This range will be used in a question below.
(10 marks)

Using Table C data and keeping employee order unchanged, set up a payroll table to calculate e total payroll for each employee. Your main payroll table should have the following headings:EmployeeHoursRegularOvertimeHourlyBaseOver-Total NameworkedHoursHoursPayAmounttimePay
  1. Use the IF function and absolute referencing to calculate the Regular Hours column. Use a formula to calculate the Overtime hours.
  2. Use the VLOOKUP function with reference to the range created earlier to determine the
Hourly Pay for each employee.

  1. Use suitable formulae to calculate the Base and Overtime amount and Total pay for each employee.
  2. Sort the table in order of employee names. All columns must be totalled as required.
  3. Using another formula, identify the employee(s) that earned maximum overtime
  4. Ensure that your work is professionally presented, with suitable formatting, borders and labels.
(30 marks)
3Discuss, justify and illustrate two more functions that can help with decision making regarding (Choose any 2 points). You can use suitable sample data to illustrate the functions:
  • Customers who are likely to default
  • Employees who are putting in long hours
  • Items that are providing outstanding returns
  • A model to forecast demand for the next quarter.
(20 marks)

Q 2 (20 marks)

Research a few commonly used Business Intelligence Tools that help extract intelligence from the business data to support decision-making through modelling and analysis.

List and briefly illustrate 2 features of commonly used Business Intelligence Tools that can support decision-making.(20 marks)

Your report need be:-

  • Professionally presented, in your own words. Your research should include at least 3 references, such as the professional body articles as well as text books and peer reviewed academic journals. Vendor websites are not appropriate sources of information for this task. Use appropriate referencing in your report.
  • Word limit for Q 2 : 600 ( excluding references, diagrams)
Show More

Answer :


1. Customers who are likely to default.

Mass Defaults - Mass defaults apply to all customers of a product or service, without taking customers’ individual characteristics or preferences into account. A common example would be an online retailer’s using standard shipping unless the customer actively chooses rush delivery. Mass defaults by their very nature give some customers a version of the offering that wouldn’t be their first choice. Still they are very useful when most customers can reliably be expected to prefer one basic configuration or to benefit from the seller’s recommendations. 

2. A model to forecast demand of the next quarter.

A simple moving average (SMA) is the simplest type of technique of forecasting. Basically, a simple moving average is calculated by adding up the last ‘n’ period’s values and then dividing that number by ‘n’. So, the moving average value is considering as the forecast for next period. Moving averages can be used to quickly identify whether selling is moving in an uptrend or a downtrend depending on the pattern captured by the moving average.


1. Location Intelligence

For most business intelligence (BI) professionals, maps are becoming an increasingly common way to view data in a dashboard or report. However, most BI professionals have yet to be exposed to the full power of location intelligence.

  • Location Intelligence is the ability to map and visualize data in geographical formats. Exploring and visualizing data sets based on spatial elements enables organizations to understand their business operations from new perspectives, such as sales per region. 
  • LI is a business intelligence (BI) tool capability that relates geographic contexts to business data. Like BI, location intelligence software is designed to turn data into insight for a host of business purposes. Such tools draw on a variety of data sources, such as geographic information systems (GIS), aerial maps, demographic information and, in some cases, an organization's own databases.
  • LI is an asset management tool that allows an organization to track items of economic value that can be converted into cash.

It is most often used to describe the people, data and technology for geographical information. Often these are incorporated as spatial database and spatial OLAP tools. GIS tools helps to analyse and visualize the data given at a time.

Location intelligence is used by a broad range of industries to improve overall business results. Applications include: 

Why Location Intelligence Matters?

Datasets on their own are incredibly powerful and practical. With the right information, you can understand what your customers want, how your business or products are doing and how to make more-informed, almost predictive decisions in the future. Crucially to understand “where” and “when”, location intelligence is most important.

2.  “What if” analysis

Decision making is a crucial part of any business but when you can take decisions based on data then the outcome of any project or task is always more in control than earlier. What-if analysis directly helps us in this.


It is the process of changing the values in cell to see how those changes affect the outcome of formulas on the worksheet. In relation to this, you need to create scenarios to analyse.

· What is a SCENARIO? 

A scenario is a set of values that Microsoft Excel saves and can substitute automatically in the worksheet. Scenario is important because you can use them to forecast the outcome of a worksheet model.

Three kinds of What-If Analysis tools come with Excel: ScenariosGoal Seek, and Data Tables. Scenarios and Data tables take sets of input values and determine possible results. A Data Table works with only one or two variables, but it can accept many different values for those variables. A Scenario can have multiple variables, but it can only accommodate up to 32 values. Goal Seek works differently from Scenarios and Data Tables in that it takes a result and determines possible input values that produce that result.

  • Goal Seek – Reverse calculations
  • Data Table – Sensitivity analysis
  • Scenario Manager – Comparison of scenarios

“What if” analysis allows businesses to assess the potential effects of critical business decision before they’re made. Using existing data, users can formulate strategies to achieve business targets and avoid the default “hit and miss” approach. This helps management undertake accurate strategic planning.