Reference no: EM132360743

Excel Assignment and Case Study

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

Assessment Description

• Learning Outcome 5: Demonstrate data analysis, data manipulation and information presentation skills through the use of a commercially available spreadsheet package.
• This individual 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

QUESTION 1 Excel Case

The following employees work for Hi-Tech 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:

TABLE A:

The overtime hourly rate is $ 85 per hour for ALL employees, regardless of their classification level. Hi-Tech Contracts Ltd has the following employees:

TABLE B:

The hours worked for the week ended 30th June, 2017 are as follows:

TABLE C:

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

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

Employee Hours Regular Overtime Hourly Base Over- Total Name worked Hours Hours Pay Amount time Pay

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.

QUESTION 2 Business Intelligence Case

Making better decisions, improving operational efficiencies, growing revenues and increased competitive advantage are the top four Business Intelligence objectives organizations have today (Forbes, 2018).

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

1 Describe the growth of Business Intelligence in the past few decades. You can use graphs, tables, success stories, as you deem fit, to present your case.
2 List and briefly illustrate at least 2 features of commonly used Business Intelligence Tools that can support decision-making.

QUESTION 3 Business Ethics Case

Ethics is central to the study of all business disciplines, including accounting - as also discussed in the prescribed text book Accounting Information System 2016 by Considine B. et al. All types of business (including not-for-profit) and government entities need to consider the ethical implications of their actions. Businesses and governments have control of the world's resources, and managers make decisions every day that affect those resources and the lives of millions of people. It is important that those who operate in the business world have an understanding of ethical theories and philosophies to help guide their decision making.

It seems that, despite technological advances in business and communications, ethics is still a fundamental issue to be explored and debated. In fact, due to the business world becoming more globally focused and the community becoming more educated and aware, the subject of ethics and, in particular, business ethics, is gaining increasing attention. The changes in people's value systems over the past few hundred years have meant that old rules and expectations (e.g. how we deal with corporate governance, the environment or each other) cannot be relied upon to seek solutions; instead, thoughtful, reasoned consideration needs to be given to the facts at hand in each circumstance.

Select a case of reported and recorded breach of Code of Conduct or Business Ethics.

Prepare a brief report outlining:
- the nature of breach of Code of Conduct or Business Ethics, outlining the issues involved.
- changes introduced in the recent years in the organisations to mitigate the risk.

Few points regarding your report: -
• It should be professionally presented, in your own words. It's helpful to include diagrams and tables, as appropriate, to highlight your key points.
• Your research should include at least 2 references for each of Q 2 and 3, 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 and 3: 750 each (excluding references, diagrams)