|Subject Code: ACC 202|
|Subject Name: Accounting Information Systems|
|Assessment Title: Excel Assignment and Case Study|
|Total Marks: 80|
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.
The assignment consists of two questions:
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:
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 name||Classification Level|
|Employee name||Hours worked|
REQUIRED: One worksheet must be used to provide answers to this question.
|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|
|3||Discuss, 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:|
Research a few commonly used Business Intelligence Tools that help extract intelligence from the business data to support decision-making through modelling and analysis.
|I||List and briefly illustrate 2 features of commonly used Business Intelligence Tools that can support decision-making.(20 marks)|
Your report need be:-
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.
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.
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.
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.
· What is WHAT-IF ANALYSIS?
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: Scenarios, Goal 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.
“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.