CISM4000 Accounting Information Systems

Your Task

The Assignment consists of these questions:

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

2. A brief Research Report to explore the impact that emerging technologies like Business Intelligence, Cloud Computing can have on accounting and business decisions.

3. A brief Research Report outlining the steps taken by a selected organization to promote business ethics

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.

a) Copy Table A into your worksheet. Set up a ‘range’ for the data. Name the range ‘classification level’

b) 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.

Using Table C data and keeping employee order unchanged, set up a payroll table to calculate the total payroll for each employee. Your main payroll table should have the following headings:

a) Use the IF function and absolute referencing to calculate the Regular Hours column. Use a formula to calculate the Overtime hours.

b) Use the VLOOKUP function with reference to the range created earlier to determine the Hourly Pay for each employee.

c) Use suitable formulae to calculate the Base and Overtime amount and Total pay for each employee.

d) Sort the table in order of employee names. All columns must be totaled as required.

e) Using another formula, identify the employee(s) that earned maximum overtime

f) Ensure that your work is professionally presented, with suitable formatting, borders and labels.

3 Discuss, justify and illustrate two more functions that can be included, in a case like this, to help with decision making regarding (Choose any 2 points)

• 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. You can use any sample data for the purpose of illustration.